Details
-
Bug
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
10.0.4, 5.5.33, 5.3.12
-
None
-
None
Description
The following test case returns different results for the same query when it's executed with and without index_merge:
CREATE TABLE t1 (i INT, d DATE, c1 CHAR(16), INDEX(i), INDEX(d), INDEX(c1)) ENGINE=MyISAM; |
INSERT INTO t1 VALUES |
(4,'2002-12-21','John'),(2,'2002-03-18','Leon'),(1,'0000-00-00','Daniel'), |
(2,'2006-09-12','Tom'),(194,'2003-06-05','Sam'),(2,'2000-07-19','Ivan'), |
(3,'1900-01-01','Julia'); |
|
|
CREATE TABLE t2 (c2 CHAR(16)) ENGINE=MyISAM; |
INSERT INTO t2 VALUES ('John'),('Veronica'); |
|
|
SET optimizer_switch='index_merge=on'; |
|
|
SELECT * FROM t1 LEFT JOIN t2 ON ( c2 = c1 ) |
WHERE c1 = 'Alan' OR i > 254 OR d = 'wrong value' ; |
|
|
SET optimizer_switch='index_merge=off'; |
|
|
SELECT * FROM t1 LEFT JOIN t2 ON ( c2 = c1 ) |
WHERE c1 = 'Alan' OR i > 254 OR d = 'wrong value' ; |
Result with index_merge=on (empty set):
i d c1 c2
|
Result with index_merge=off:
i d c1 c2
|
1 0000-00-00 Daniel NULL
|
I cannot say for sure which one is correct, since there is a comparison to an incorrect date value. I suppose it should return the empty result since '0000-00-00' does not look equal to 'wrong value'; but more importantly, I expect the query return the same result set, regardless of the optimizer_switch value.
Reproducible on 5.3 (down to 5.3.5), 5.5, 10.0.
See also MDEV-4837 (Comparing a TIME value with an illegal time representation returns TRUE), it might be related.
EXPLAIN with index_merge=on:
id select_type table type possible_keys key key_len ref rows filtered Extra
|
1 SIMPLE t1 index_merge i,d,c1 c1,i 17,5 NULL 2 100.00 Using sort_union(c1,i); Using where
|
1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join)
|
Warnings:
|
Note 1003 select `test`.`t1`.`i` AS `i`,`test`.`t1`.`d` AS `d`,`test`.`t1`.`c1` AS `c1`,`test`.`t2`.`c2` AS `c2` from `test`.`t1` left join `test`.`t2` on((`test`.`t2`.`c2` = `test`.`t1`.`c1`)) where ((`test`.`t1`.`c1` = 'Alan') or (`test`.`t1`.`i` > 254) or (`test`.`t1`.`d` = 'wrong value'))
|
EXPLAIN with index_merge=off:
id select_type table type possible_keys key key_len ref rows filtered Extra
|
1 SIMPLE t1 ALL i,d,c1 NULL NULL NULL 7 100.00 Using where
|
1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join)
|
Warnings:
|
Note 1003 select `test`.`t1`.`i` AS `i`,`test`.`t1`.`d` AS `d`,`test`.`t1`.`c1` AS `c1`,`test`.`t2`.`c2` AS `c2` from `test`.`t1` left join `test`.`t2` on((`test`.`t2`.`c2` = `test`.`t1`.`c1`)) where ((`test`.`t1`.`c1` = 'Alan') or (`test`.`t1`.`i` > 254) or (`test`.`t1`.`d` = 'wrong value'))
|
Attachments
Issue Links
- relates to
-
-
- Confirmed
-