When using db_select's condition member function I was receiving errors when passing an empty array with the NOT IN operator. The MySQL was properly generating NOT IN (), but this is not legal syntax. I personally think it would be a good idea to take advantage of the database abstraction layer and simply not include the condition if the array passed in is empty and the operator is NOT IN (this likely has the same results with just the IN operator). At the very least a descriptive error message could be displayed.

    $query = db_select('node', 'n')->fields('n');
    $query->join('field_data_field_other_related_programs', 'fs', 'n.nid = fs.entity_id');
    $query->join('field_data_field_scad_degree_types_vocref_1', 'dt', 'n.nid = dt.entity_id');
    $query->join('taxonomy_term_data', 't', 'fs.field_other_related_programs_tid = t.tid');
    $query->addField('t', 'tid');
    $query
      ->condition('fs.field_other_related_programs_tid', $tid, '=')
      ->condition('n.type', 'scad_degree', '=')
      ->condition('dt.field_scad_degree_types_vocref_1_tid', $minor_tid, '=')
      ->condition('n.nid', $nodes_in_use, 'NOT IN');
      ->range(0, 4 - $count);

The solution being to wrap the NOT IN condition in an IF-THEN statement:

      if (isset($nodes_in_use) && !empty($nodes_in_use)) {
        $query->condition('n.nid', $nodes_in_use, 'NOT IN');
      }

Comments

Paul B’s picture

Component: database system » mysql database
Priority: Minor » Normal

IN has the same problem:

$ drush ev '
$query = new EntityFieldQuery();
$query->entityCondition("entity_type", "node")
->fieldCondition("body", "value", [], "in");
print_r($query->execute());

This gives me an empty array with SQLite, but with MySQL I get an SQL syntax error in field_sql_storage_field_storage_query().

For 'in' conditions removing the condition will not work of course, that would give me all nodes.

Paul B’s picture

Issue summary: View changes

I just realized this is db_select and not db_query.

Paul B’s picture

liam morland’s picture

Title: db_query condition function doesn't guard against empty set conditions » db_select()->condition() doesn't guard against empty set conditions
Version: 7.22 » 7.x-dev
Component: mysql database » ajax system
Related issues: +#2177247: EntityFieldQuery condition functions don't guard against empty set conditions
liam morland’s picture

Component: ajax system » database system

Status: Active » Closed (outdated)

Automatically closed because Drupal 7 security and bugfix support has ended as of 5 January 2025. If the issue verifiably applies to later versions, please reopen with details and update the version.