[MDEV-3995] Wrong result with materialization, IN subquery, MyISAM. Created: 2013-01-04  Updated: 2013-02-14  Resolved: 2013-02-13

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 5.5.28a, 5.3.11
Fix Version/s: 5.5.30, 5.3.13

Type: Bug Priority: Major
Reporter: Peter (Stig) Edwards Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: optimizer
Environment:

RedHat EL6.3 x86_64 using 5.3.11 bintar and HEAD of lp:maria/5.3

Attachments: File st_ives.mysqldump    

 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.

 Comments   
Comment by Peter (Stig) Edwards [ 2013-01-04 ]

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.

Comment by Sergei Petrunia [ 2013-01-08 ]

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.

Comment by Sergei Petrunia [ 2013-01-08 ]

"wives.cat_id = kits2.cat_id" is the equality that is made from IN-equality

Comment by Sergei Petrunia [ 2013-01-09 ]

.. 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)

Comment by Igor Babaev [ 2013-02-13 ]

The fix for this bug was pushed into the 5.3 tree.
It will appear in 5.3.13 and 5.5.30.

Comment by Peter (Stig) Edwards [ 2013-02-14 ]

Thank you.

Generated at Thu Feb 08 06:52:55 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.