Details
Major
Description
Hello and thank you for mariadb-5.3.11-MariaDB-linux-x86_64,
This query:
SELECT count(*) FROM wives, cats WHERE cats.cat_id = wives.cat_id AND wives.cat_id IN (SELECT cat_id FROM kits) AND wives.sack_id = 33479 AND wives.kit_id = 6;
with materialization on returns 0, with it off returns 94.
Explain plan with materialization on:
+----+--------------+-------------+--------+---------------+--------------+---------+-------------------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------+-------------+--------+---------------+--------------+---------+-------------------+------+--------------------------+ | 1 | PRIMARY | wives | index | PRIMARY | PRIMARY | 9 | NULL | 3690 | Using where; Using index | | 1 | PRIMARY | <subquery2> | eq_ref | distinct_key | distinct_key | 4 | func | 1 | Using where | | 1 | PRIMARY | cats | eq_ref | PRIMARY | PRIMARY | 4 | test2.kits.cat_id | 1 | Using where; Using index | | 2 | MATERIALIZED | kits | index | cat_id | cat_id | 4 | NULL | 7578 | Using index | +----+--------------+-------------+--------+---------------+--------------+---------+-------------------+------+--------------------------+
Schema and data attached (4MB). I tested with 5.3.11 and the HEAD of lp:maria/5.3
Thank you.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
I've changed the subquery to use table "kits2" instead of "kits" (so that it can be easily distinguished from the reference to "kits" in the top select)
Excerpts from .trace file:
WHERE:(WHERE in setup_conds) 0x7fff2c009d20
((cats.cat_id = wives.cat_id) and wives.cat_id in (select cat_id from kits2) and (wives.sack_id = 33479) and (wives.kit_id = 6))
WHERE:(original) 0x7fff2c00c0f8
(cats.cat_id = wives.cat_id) and 1 and (wives.sack_id = 33479) and (wives.kit_id = 6) and (wives.cat_id = kits2.cat_id)
WHERE:(after equal_items) 0x7fff2c00c0f8 (1 and (wives.cat_id = kits2.cat_id) and multiple equal(cats.cat_id, wives.cat_id) and multiple equal(33479, wives.sack_id) and multiple equal(6, wives.kit_id))
WHERE:(after const change) 0x7fff2c00c0f8 (1 and (wives.cat_id = kits2.cat_id) and multiple equal(cats.cat_id, wives.cat_id) and multiple equal(33479, wives.sack_id) and multiple equal(6, wives.kit_id))
WHERE:(after remove) 0x7fff2c00c0f8 ((wives.cat_id = kits2.cat_id) and multiple equal(cats.cat_id, wives.cat_id) and multiple equal(33479, wives.sack_id) and multiple equal(6, wives.kit_id))
Note that "after remove" we get:
((wives.cat_id = kits2.cat_id) and multiple equal(cats.cat_id, wives.cat_id)
The equality "wives.cat_id=kits2.cat_id" should be part of the "multiple equal", but for some reason it is not.
"wives.cat_id = kits2.cat_id" is the equality that is made from IN-equality
.. and this IN-equality does not join the multiple-equality.
check_simple_equality("wives.cat_id = kits2.cat_id") returns FALSE.
this happens because wives.cat_id is defined as INT(10) UNSIGNED, while kits.cat_id is INT(10).
we need to handle this case, somehow.
(the above explains what is so peculiar about the testcase of this bug. We've had lots of tests with x=y and y IN (SELECT z ...). The problem is that in these tests y and z had exactly the same datatype. Or, datatypes were so different that Materialization strategy was not applicable. Here, datatypes differ only slightly)
The fix for this bug was pushed into the 5.3 tree.
It will appear in 5.3.13 and 5.5.30.
Thank you.
I don't have a test setup to formally test what versions are affected, but a cursory (not minimal or identical my.cnf) run against some existing mysqld instances suggests this is not a recent regression because I see the same incorrect behaviour for mariadb 5.3.8 and 5.3.3-rc instances, against a 5.3.6 instance zero is incorrectly returned with materialization on and off.