DBAL cardinality violation error

Question

I am getting the 'Cardinality Violation' 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 params ["%big%", "%light%"]: SQLSTATE[21000]: Cardinality violation: 1241 Operand should contain 1 column(s).

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

I am using executeQuery(), and passing the array as Connection::PARAM_STR_ARRAY. In the original statement I am defining the trouble point as:

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

It seems it doesn't like getting an array passed as partial_names. Any ideas on what is causing this, and how to avoid it?

Answer 1

MySQL LIKE is a "string comparison function" and as such compares one string to another, using "simple pattern matching".

If you check the SQL standard, you'll notice that the BNF grammar for LIKE accepts only "character-like" and "octet-like" arguments, both of which are essentially what we'd call strings. (There is some detail around the fact that LIKE performs a binary, character-for-character match on the RHS, which is different than how = operates: foo LIKE 'bar' and foo='bar' may produce different results.)

All this means you can't do LIKE ('a', 'b') because the columnar expression ('a', 'b') is not string-like. Or in geeky standard language, it's cardinality (2) differs from the expected cardinality (1). However, you can do this in MySQL and SQLite (maybe other engines):

WHERE foo LIKE ('%bar')

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

You're wanting something effectively similar to foo LIKE IN ('a', 'b'), but that doesn't exist either (for the SQL standard reason mentioned above). This Q&A shows some workarounds for that behavior, REGEXP based being the accepted answer.

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

Answer 2

Change

(p.name LIKE (?, ?))

to

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

and

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

to

"%big%", "%light%"