Details
-
Technical task
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
None
-
None
-
None
Description
The following test case
SET optimizer_switch = 'exists_to_in=on'; |
|
|
CREATE TABLE t1 (a1 INT, b1 CHAR(1)) ENGINE=MyISAM; |
INSERT INTO t1 VALUES (3,'y'),(6,'w'); |
|
|
CREATE TABLE t2 (a2 INT, b2 CHAR(1)) ENGINE=MyISAM; |
INSERT INTO t2 VALUES (3,'y'),(6,'d'); |
|
|
SELECT * FROM t1 |
WHERE NOT EXISTS ( SELECT * FROM t2 WHERE b2 = b1 AND a2 = a1 ); |
returns 2 rows and throws a warning:
a1 b1
|
------------
|
3 y
|
6 w
|
Warnings:
|
Warning 1292 Truncated incorrect DOUBLE value: 'y'
|
Without exists_to_in switch there is only one row and no warning, which is the expected result:
a1 b1
|
------------
|
6 w
|
branch: 10.0-exists2in
|
revision-id: psergey@askmonty.org-20121123063639-1jqbkjuxevqe1vb6
|
date: 2012-11-23 10:36:39 +0400
|
revno: 3482
|
Minimal optimizer_switch: exists_to_in=on,in_to_exists=on or exists_to_in=on,materialization=on
Full optimizer_switch (default + exists_to_in=on):
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=on
|
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 2 100.00 Using where
|
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 100.00
|
Warnings:
|
Note 1276 Field or reference 'test.t1.b1' of SELECT #2 was resolved in SELECT #1
|
Note 1276 Field or reference 'test.t1.a1' of SELECT #2 was resolved in SELECT #1
|
Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1` from `test`.`t1` where (not((<expr_cache><`test`.`t1`.`b1`,`test`.`t1`.`a1`>(<in_optimizer>((`test`.`t1`.`b1`,`test`.`t1`.`a1`),(`test`.`t1`.`b1`,`test`.`t1`.`a1`) in ( <materialize> (select `test`.`t2`.`b2`,`test`.`t2`.`a2` from `test`.`t2` where 1 ), <primary_index_lookup>(`test`.`t1`.`b1` in <temporary table> on distinct_key where ((`test`.`t1`.`b1` = `<subquery2>`.`b2`) and (`test`.`t1`.`a1` = `<subquery2>`.`a2`)))))) and `test`.`t1`.`a1` and `test`.`t1`.`b1`)))
|
|
|