Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5.28a, 5.3.11
-
None
-
RedHat EL6.3 x86_64 using 5.3.11 bintar and HEAD of lp:maria/5.3
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.