DBAL database violation error

Question

I get a Cardinality error for the following SQL:

Doctrine \ DBAL \ Exception \ DriverException: An exception occurred while executing

SELECT p.* FROM mod_products_products p LEFT JOIN mod_products_products_categories c_link ON c_link.product_id = p.id LEFT JOIN mod_products_brands b ON p.brand_id = b.id LEFT JOIN mod_products_groups vg ON p.variation_id = vg.id LEFT JOIN mod_products_categories c ON c_link.category_id = c.id LEFT JOIN mod_products_group_options vg_o ON vg_o.group_id = vg.id LEFT JOIN mod_products_group_values vg_o_v ON vg_o_v.option_id = vg_o.id WHERE (p.name LIKE (?, ?)) AND (p.parent_id = 0) AND (vg_o.disabled=0) GROUP BY p.id ORDER BY p.name ASC LIMIT 18446744073709551615 OFFSET 0 

with parameters ["% big%", "% light%"]: SQLSTATE [21000]: cardinality violation: 1241 The operand must contain 1 column.

The error only occurs if more than one value is specified for the WHERE (p.name LIKE (?, ?)) in the parameter list WHERE (p.name LIKE (?, ?)) .

I use executeQuery() and pass the array as Connection::PARAM_STR_ARRAY . In the original statement, I define this problem as:

 $builder->andWhere('p.name LIKE (:partial_names)'); 

It seems like not getting the array passed as partial_names. Any ideas on what causes this, and how to avoid it?

Answer 1

MySQL LIKE is a "string comparison function" and thus compares one row to another using a "simple pattern matching".

If you check the SQL standard , you will notice that the BNF grammar for LIKE only accepts “characteristic” and “octet” arguments, which are essentially what we will call strings. (There are a few details that LIKE performs binary character matching for a character on RHS, which is different from how = works: foo LIKE 'bar' and foo='bar' can give different results.)

All this means that you cannot do LIKE ('a', 'b') , because the column expression ('a', 'b') not a string expression. Or in geeky standard language, its power (2) is different from the expected power (1). However, you can do this in MySQL and SQLite (possibly in other versions):

 WHERE foo LIKE ('%bar') 

because the RHS power is 1 (there is one column), which is what LIKE expects.

You want something effectively similar to foo LIKE IN ('a', 'b') , but that also doesn't exist (for the standard SQL problem mentioned above). This Q&A shows some workarounds for this behavior based on REGEXP .

So, to get around this error, you need to rewrite your request to use multiple LIKE or REGEXP , or maybe even something like FIND_IN_SET .

Answer 2

Edit

 (p.name LIKE (?, ?)) 

to

 (p.name LIKE ? OR p.name LIKE ?) 

and

 ["%big%", "%light%"] 

to

 "%big%", "%light%"