Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: 10.0.1
-
Component/s: None
-
Labels:None
Description
The following test case
SET optimizer_switch = 'in_to_exists=on,exists_to_in=on'; CREATE TABLE t1 (a INT, b VARCHAR(1)) ENGINE=MyISAM; INSERT INTO t1 VALUES (4,'j'),(6,'v'),(3,'c'); CREATE TABLE t2 (c VARCHAR(1)) ENGINE=MyISAM; INSERT INTO t2 VALUES ('b'),('y'); SELECT a FROM t1 WHERE EXISTS ( SELECT 1 FROM t2 WHERE c = b ) OR b NOT IN ('U') ORDER BY a;
returns an unsorted result set, which is obviously wrong:
bzr a --- 4 6 3
Minimal optimizer_switch: in_to_exists=on,exists_to_in=on or materialization=on,exists_to_in=on
Also reproducible with the default optimizer_switch + exists_to_in=on.
Not reproducible without exists_to_in.
Reproducible with MyISAM, Aria, InnoDB.
EXPLAIN (with the default optimizer_switch + exists_to_in=on):
id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 100.00 Warnings: Note 1276 Field or reference 'test.t1.b' of SELECT #2 was resolved in SELECT #1 Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (<expr_cache><`test`.`t1`.`b`>(<in_optimizer>(`test`.`t1`.`b`,`test`.`t1`.`b` in ( <materialize> (select `test`.`t2`.`c` from `test`.`t2` where 1 ), <primary_index_lookup>(`test`.`t1`.`b` in <temporary table> on distinct_key where ((`test`.`t1`.`b` = `<subquery2>`.`c`)))))) or (`test`.`t1`.`b` <> 'U')) order by `test`.`t1`.`a`
bzr version-info (lp:~maria-captains/maria/5.5-exists2in)
revision-id: sanja@montyprogram.com-20121004114534-4vclmqpxvhxkd7ok date: 2012-10-04 14:45:34 +0300 build-date: 2012-10-05 23:05:07 +0400 revno: 3545
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Correct execution uses filesort.
curr_join->order for main qquery has no value for wrong case
problem is in incorrect current select pointer.
fixed, tested and pushed
Optimized (as shown by EXPLAIN) veriosn works correctly, so idea is to find difference in execution:
select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`b` in (select `test`.`t2`.`c` from `test`.`t2` where 1 ) or (`test`.`t1`.`b` <> 'U') order by `test`.`t1`.`a`;