"Undefined function: 7 ERROR: operator does not exist: bigint" error with a PostgreSQL database

Hi,
Even i got the SQL Error with view, when i added content queue in the relationship. Can anyone resolve this issue.

Hi all,

I have got the same error using Entityqueue 8.x-1.0-alpha6 and Drupal core 8.3.2.

Any idea to find a solution?

I ran into the same thing and wanted to add some detail to help debug.

The problem is in the first INNER JOIN. The tid field is stored in postgres as a number (bigint) and the items_target_id is stored as a string (character varying). Postgres can't join across different datatypes. You can confirm this issue by running the same query while typecasting the items_target_id to a bigint (after which the query will work):

SELECT
... 
INNER JOIN entity_subqueue__items entity_subqueue__items ON taxonomy_term_field_data.tid = CAST(entity_subqueue__items.items_target_id as int8) 
...;

So why is the items_target_id a string?

I'm not 100% sure, but I found some code in EntityQueue's field definitions that suggest that some settings in the items field are defaulting to strings:

$fields['items'] = BaseFieldDefinition::create('entity_reference')
   ->setLabel(t('Items'))
   ->setCardinality(FieldStorageDefinitionInterface::CARDINALITY_UNLIMITED)
   // This setting is overridden per bundle (queue) in
   // static::bundleFieldDefinitions(), but we need to default to a target
   // entity type that uses strings IDs, in order to allow both integers and
   // strings to be stored by the default entity reference field storage.
   ->setSetting('target_type', 'entity_subqueue')
   // ...

I'm not sure why we need to allow both integers and strings to be stored in this field, but I'll bet that's causing the issue for postgres.

I just came across this issue today on postgres as well. Just highlighting the relevant code from EntityReferenceItem which takes into account the target_type @ https://api.drupal.org/api/drupal/core%21lib%21Drupal%21Core%21Field%21P...

I'm not sure why we need to allow both integers and strings to be stored in this field, but I'll bet that's causing the issue for postgres.

That's because entity queue needs to be able to store references (through its entity reference field) to both content and config entities. Content entities mostly use integer IDs while config entities have string IDs.

It's very unfortunate that postgres doesn't allow joins on different data types but I can't really propose any solution for this.

https://dba.stackexchange.com/a/42991 suggests a functional index for the column that stores integer IDs, but that would have to be added to all the tables of every content entity type. Any other suggestions?

I'm also experiencing this issue. Does anyone have a solution or patch ?

I was also experiencing the error. I am working with a postgres db, and my entityqueues only contain nodes, so this hacky workaround worked for me:

use Drupal\views\ViewExecutable;
use Drupal\views\Plugin\views\query\QueryPluginBase;

/**
 * Implements hook_views_query_alter()
 */
function mymodule_views_query_alter(ViewExecutable $view, QueryPluginBase $query) {
  if (in_array($view->id(), ['myview1', 'myview2'])) {
    $query->getTableInfo('entity_subqueue__items')['join']->field .= '::bigint';
  }
}

basically just casting the target_type field to a bigint since I can be confident this is safe in my specific use case. Can someone more knowledgeable speak on the fragility of this workaround?

I hope this is helpful.

Thanks for the tip @mabdullah2010 I based my solution off of yours:

<?php

use Drupal\Core\Database\Database;
use Drupal\views\ViewExecutable;
use Drupal\views\Plugin\views\query\QueryPluginBase;

/**
 * Implements hook_views_query_alter()
 */
function hook_views_query_alter(ViewExecutable $view, QueryPluginBase $query) {

  $db = Database::getConnection()->driver();
  if ($db == 'pgsql') {
    if (isset($view->relationship['entityqueue_relationship'])) {
      $query->getTableInfo('entity_subqueue__items')['join']->field .= '::bigint';
    }
  }
}

I'm baffled that Drupal hasn't standardized the ID column type. That seems like the right way to resolve this issue, but that also seems like a pretty long road.

Content entities mostly use integer IDs while config entities have string IDs.

If the issue is resolved by typecasting the column, couldn't you test if the join is operating on a config entity and then write a different database query as suggested in #9 & #10 that typecasts the column if it's a content entity?

If a few modules allow entities to be created using an ID that does not conform to the rest of Drupal standards for entity IDs, it seems to me like it would be a good idea for them to make changes necessary to conform to standards.

My attempt to implement the views_query_alter in #9 & #10 has resulted in a different error that I don't quite understand:

Symfony\Component\DependencyInjection\Exception\ServiceNotFoundException: You have requested a non-existent service "plugin.manager.field_formatter_enhancer". Did you mean this: "plugin.manager.field.formatter"? in Drupal\Component\DependencyInjection\Container->get() (line 157 of core/lib/Drupal/Component/DependencyInjection/Container.php).

If all you are using is MySQL then you will not have this issue, however most other database engines are strict on the comparing of strings and numbers. MSSQL, ORACLE, IBM DB2, PostGreSQL and most database engines.

examples
https://stackoverflow.com/questions/1146928/numeric-comparisons-on-strin...
https://stackoverflow.com/questions/32051667/compare-number-column-with-...
https://stackoverflow.com/questions/10489703/db2-how-to-check-if-varchar...

The second example in #13 looks like it might be a very simple solution: always cast the integer column to a string before comparison to the string column.

It might be wise to cast both sides as string for future use and avoidance of other entity string types for an ID.

I have created a patch file from #10, because I have tried and works for me, but I thought that it could be useful have it as a patch directly.

The problem with the approach from #10 / #16 is that it will break as soon as there is an entity queue which references an entity type with a string ID..

Better to cast both as strings for MySQL and any other DB.

Looks like we have 2 options:

1) Cast both to strings
This will guarantee no type issues in all databases.

2) Make 2 different queries
One query for int to int matching for content entities, and another one for config entities.

I choose casting both to strings.

What if we reduced the functionality of Entityqueue to only have the ability to add content into queues. If we remove the ability to add configuration, we can set the entity_subqueue__items to an int instead of a varchar.

We would still have the ability to add the following into queues:

1. Content
2. Files
3. Terms
4. Users
5. Entity subqueues

I'm not sure how many users would need the entityqueue for configuration, so I'm not sure if this is a viable solution.

@sagesolutions, content entities are also allowed to have string identifiers :) In fact, there is a new entity type in Drupal 8.6 which does exactly that, the Workspace entity type.

That's just great...

Back to the drawing board on this one.

i was thinking adding another table...

What if we added another table entity_subqueue__items_target or similar, which has an id (int), and the items_target_id (string).
Then, the entity_subqueue__items table could reference the entity_subqueue__items_target id field.

Unfortunatley, I think this still wont work, because we would still want to (for example) join taxonomy tids to the items_target_id.

I guess casting everything to strings is still a solution.

Yep, it's the only viable solution in my opinion..

I spent some time today and looked into this and I found a nice way of solving this in our custom views relationship handler. We can look up the field type definition of the queue's target entity type, and if it's an integer we do a SQL standard CAST() on the items field, which should work on all DB engines :)

Committed the patch attached to 8.x-1.x Thanks everyone for all the useful conversation here!

I completely agree with Flag module issue comment 2929733-5, that this should be addressed in views and not in a contributed module. I have submitted a patch to core to address this.