From: Tor Didriksen Date: October 22 2010 1:33pm Subject: bzr push into mysql-next-mr-bugfixing branch (tor.didriksen:3330 to 3331) List-Archive: http://lists.mysql.com/commits/121701 Message-Id: <20101022133350.917573723@atum07.norway.sun.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 3331 Tor Didriksen 2010-10-22 [merge] Automerge from next-mr-opt-team. modified: mysql-test/include/subquery_sj.inc mysql-test/r/optimizer_switch.result mysql-test/r/subquery_sj_all.result mysql-test/r/subquery_sj_all_jcl6.result mysql-test/r/subquery_sj_all_jcl7.result mysql-test/r/subquery_sj_dupsweed.result mysql-test/r/subquery_sj_dupsweed_jcl6.result mysql-test/r/subquery_sj_dupsweed_jcl7.result mysql-test/r/subquery_sj_firstmatch.result mysql-test/r/subquery_sj_firstmatch_jcl6.result mysql-test/r/subquery_sj_firstmatch_jcl7.result mysql-test/r/subquery_sj_loosescan.result mysql-test/r/subquery_sj_loosescan_jcl6.result mysql-test/r/subquery_sj_loosescan_jcl7.result mysql-test/r/subquery_sj_mat.result mysql-test/r/subquery_sj_mat_jcl6.result mysql-test/r/subquery_sj_mat_jcl7.result mysql-test/r/subquery_sj_mat_nosj.result mysql-test/r/subquery_sj_none.result mysql-test/r/subquery_sj_none_jcl6.result mysql-test/r/subquery_sj_none_jcl7.result mysql-test/t/optimizer_switch.test sql/item.cc sql/item.h sql/item_cmpfunc.cc sql/item_cmpfunc.h sql/item_func.cc sql/item_func.h sql/item_row.cc sql/item_row.h sql/item_subselect.cc sql/item_subselect.h sql/sql_select.cc 3330 Anitha Gopi 2010-10-22 [merge] due to merge added: mysql-test/extra/rpl_tests/rpl_row_empty_imgs.test mysql-test/extra/rpl_tests/rpl_row_img.test mysql-test/extra/rpl_tests/rpl_row_img_blobs.test mysql-test/extra/rpl_tests/rpl_row_img_diff_indexes.test mysql-test/include/rpl_chained_3_hosts.inc mysql-test/include/rpl_chained_3_hosts_sync.inc mysql-test/include/rpl_row_img_general_loop.inc mysql-test/include/rpl_row_img_parts_assertion.inc mysql-test/include/rpl_row_img_parts_master_slave.inc mysql-test/include/rpl_row_img_set.inc mysql-test/suite/rpl/r/rpl_row_img_blobs.result mysql-test/suite/rpl/r/rpl_row_img_eng_full.result mysql-test/suite/rpl/r/rpl_row_img_eng_min.result mysql-test/suite/rpl/r/rpl_row_img_eng_noblob.result mysql-test/suite/rpl/r/rpl_row_img_idx_full.result mysql-test/suite/rpl/r/rpl_row_img_idx_min.result mysql-test/suite/rpl/r/rpl_row_img_idx_noblob.result mysql-test/suite/rpl/r/rpl_row_img_misc.result mysql-test/suite/rpl/r/rpl_row_img_sanity.result mysql-test/suite/rpl/r/rpl_row_record_find_myisam.result mysql-test/suite/rpl/t/rpl_row_img_blobs.cnf mysql-test/suite/rpl/t/rpl_row_img_blobs.test mysql-test/suite/rpl/t/rpl_row_img_eng_full.cnf mysql-test/suite/rpl/t/rpl_row_img_eng_full.test mysql-test/suite/rpl/t/rpl_row_img_eng_min.cnf mysql-test/suite/rpl/t/rpl_row_img_eng_min.test mysql-test/suite/rpl/t/rpl_row_img_eng_noblob.cnf mysql-test/suite/rpl/t/rpl_row_img_eng_noblob.test mysql-test/suite/rpl/t/rpl_row_img_idx_full.cnf mysql-test/suite/rpl/t/rpl_row_img_idx_full.test mysql-test/suite/rpl/t/rpl_row_img_idx_min.cnf mysql-test/suite/rpl/t/rpl_row_img_idx_min.test mysql-test/suite/rpl/t/rpl_row_img_idx_noblob.cnf mysql-test/suite/rpl/t/rpl_row_img_idx_noblob.test mysql-test/suite/rpl/t/rpl_row_img_misc.test mysql-test/suite/rpl/t/rpl_row_img_sanity.test mysql-test/suite/rpl/t/rpl_row_record_find_myisam.test mysql-test/suite/rpl_ndb/r/rpl_ndb_row_img.result mysql-test/suite/rpl_ndb/t/rpl_ndb_row_img.cnf mysql-test/suite/rpl_ndb/t/rpl_ndb_row_img.test mysql-test/suite/sys_vars/r/binlog_row_image_basic.result mysql-test/suite/sys_vars/r/master_info_repository_basic.result mysql-test/suite/sys_vars/r/relay_log_info_repository_basic.result mysql-test/suite/sys_vars/t/binlog_row_image_basic.test mysql-test/suite/sys_vars/t/master_info_repository_basic.test mysql-test/suite/sys_vars/t/relay_log_info_repository_basic.test sql/rpl_info.cc sql/rpl_info.h sql/rpl_info_factory.cc sql/rpl_info_factory.h sql/rpl_info_file.cc sql/rpl_info_file.h sql/rpl_info_handler.cc sql/rpl_info_handler.h sql/server_ids.h modified: client/mysql.cc client/mysqladmin.cc client/mysqlbinlog.cc client/mysqlcheck.c client/mysqldump.c client/mysqlimport.c client/mysqlshow.c include/mysql.h include/mysql.h.pp mysql-test/collections/default.experimental mysql-test/extra/rpl_tests/rpl_auto_increment.test mysql-test/include/diff_tables.inc mysql-test/include/show_binlog_using_logname.inc mysql-test/r/mysql.result mysql-test/r/mysqladmin.result mysql-test/r/mysqld--help-notwin.result mysql-test/r/mysqld--help-win.result mysql-test/r/mysqldump.result mysql-test/r/mysqlshow.result mysql-test/suite/binlog/r/binlog_row_binlog.result mysql-test/suite/binlog/t/binlog_row_binlog.test mysql-test/suite/funcs_1/r/is_columns_mysql_embedded.result mysql-test/suite/funcs_1/r/is_statistics_mysql_embedded.result mysql-test/suite/funcs_1/r/is_table_constraints_mysql_embedded.result mysql-test/suite/funcs_1/r/is_tables_mysql_embedded.result mysql-test/suite/ndb_team/r/rpl_ndb_mix_innodb.result mysql-test/suite/perfschema/r/binlog_mix.result mysql-test/suite/perfschema/r/binlog_row.result mysql-test/suite/rpl/r/rpl_flushlog_loop.result mysql-test/suite/rpl/r/rpl_rotate_logs.result mysql-test/suite/rpl/r/rpl_row_find_row.result mysql-test/suite/rpl/r/rpl_stm_auto_increment_bug33029.result mysql-test/suite/rpl/t/rpl_conditional_comments.test mysql-test/suite/rpl/t/rpl_loadfile.test mysql-test/suite/rpl/t/rpl_nondeterministic_functions.test mysql-test/suite/rpl/t/rpl_rotate_logs.test mysql-test/suite/rpl/t/rpl_row_find_row.test mysql-test/suite/rpl/t/rpl_row_tbl_metadata.test mysql-test/suite/rpl/t/rpl_row_utf16.test mysql-test/suite/rpl/t/rpl_stm_auto_increment_bug33029.test mysql-test/suite/rpl_ndb/r/rpl_ndb_extraCol.result mysql-test/suite/rpl_ndb/r/rpl_ndb_stm_innodb.result mysql-test/suite/rpl_ndb/t/disabled.def mysql-test/t/mysql.test mysql-test/t/mysqladmin.test mysql-test/t/mysqlbinlog.test mysql-test/t/mysqlcheck.test mysql-test/t/mysqldump.test mysql-test/t/mysqlshow.test mysys/mf_iocache2.c sql-common/client.c sql/CMakeLists.txt sql/Makefile.am sql/binlog.cc sql/binlog.h sql/ha_ndbcluster.cc sql/handler.cc sql/handler.h sql/item_func.cc sql/lock.cc sql/log.cc sql/log.h sql/log_event.cc sql/log_event.h sql/log_event_old.cc sql/log_event_old.h sql/mysqld.cc sql/rpl_handler.cc sql/rpl_injector.cc sql/rpl_injector.h sql/rpl_mi.cc sql/rpl_mi.h sql/rpl_record.cc sql/rpl_record.h sql/rpl_reporting.h sql/rpl_rli.cc sql/rpl_rli.h sql/rpl_slave.cc sql/rpl_slave.h sql/sql_binlog.cc sql/sql_class.h sql/sql_insert.cc sql/sql_load.cc sql/sql_update.cc sql/sys_vars.cc sql/table.cc sql/table.h storage/perfschema/ha_perfschema.cc === modified file 'mysql-test/include/subquery_sj.inc' --- a/mysql-test/include/subquery_sj.inc 2010-09-20 14:06:02 +0000 +++ b/mysql-test/include/subquery_sj.inc 2010-10-13 13:27:36 +0000 @@ -3326,3 +3326,48 @@ eval explain $query; eval $query; DROP TABLE t1,t2,t3; + +--echo # +--echo # BUG#52329 - Wrong result: subquery materialization, IN, +--echo # non-null field followed by nullable +--echo # + +CREATE TABLE t1 (a1 CHAR(8) NOT NULL, a2 char(8) NOT NULL); + +CREATE TABLE t2a (b1 char(8), b2 char(8)); +CREATE TABLE t2b (b1 CHAR(8), b2 char(8) NOT NULL); +CREATE TABLE t2c (b1 CHAR(8) NOT NULL, b2 char(8)); + +INSERT INTO t1 VALUES ('1 - 12', '2 - 22'); + +INSERT INTO t2a VALUES ('1 - 11', '2 - 21'), + ('1 - 11', '2 - 21'), + ('1 - 12', '2 - 22'), + ('1 - 12', '2 - 22'), + ('1 - 13', '2 - 23'); + +INSERT INTO t2b SELECT * FROM t2a; +INSERT INTO t2c SELECT * FROM t2a; + +SELECT * FROM t1 +WHERE (a1, a2) IN ( + SELECT b1, b2 FROM t2c WHERE b1 > '0' GROUP BY b1, b2); + +SELECT * FROM t1 +WHERE (a1, a2) IN ( + SELECT b1, b2 FROM t2a WHERE b1 > '0'); + + +SELECT * FROM t1 +WHERE (a1, a2) IN ( + SELECT b1, b2 FROM t2b WHERE b1 > '0'); + + +SELECT * FROM t1 +WHERE (a1, a2) IN ( + SELECT b1, b2 FROM t2c WHERE b1 > '0'); + + +DROP TABLE t1,t2a,t2b,t2c; + +--echo # End BUG#52329 === modified file 'mysql-test/r/optimizer_switch.result' --- a/mysql-test/r/optimizer_switch.result 2010-08-19 07:10:58 +0000 +++ b/mysql-test/r/optimizer_switch.result 2010-10-15 10:32:50 +0000 @@ -204,3 +204,345 @@ SET optimizer_switch="default"; call run_n_times(1); DROP PROCEDURE run_n_times; DROP TABLE it, ot; +# +# BUG#31480: Incorrect result for nested subquery when executed via semijoin +# +CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL); +CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL); +CREATE TABLE t3 (e INT NOT NULL); +CREATE TABLE t4 (f INT NOT NULL, g INT NOT NULL); +INSERT INTO t1 VALUES (1,10); +INSERT INTO t1 VALUES (2,10); +INSERT INTO t1 VALUES (1,20); +INSERT INTO t1 VALUES (2,20); +INSERT INTO t1 VALUES (3,20); +INSERT INTO t1 VALUES (2,30); +INSERT INTO t1 VALUES (4,40); +INSERT INTO t2 VALUES (2,10); +INSERT INTO t2 VALUES (2,20); +INSERT INTO t2 VALUES (4,10); +INSERT INTO t2 VALUES (5,10); +INSERT INTO t2 VALUES (3,20); +INSERT INTO t2 VALUES (2,40); +INSERT INTO t3 VALUES (10); +INSERT INTO t3 VALUES (30); +INSERT INTO t3 VALUES (10); +INSERT INTO t3 VALUES (20); +INSERT INTO t4 VALUES (2,10); +INSERT INTO t4 VALUES (2,10); +INSERT INTO t4 VALUES (3,10); +INSERT INTO t4 VALUES (4,10); +INSERT INTO t4 VALUES (4,20); +INSERT INTO t4 VALUES (4,20); +# Reference to the parent query block (used tables was wrong) +set @@optimizer_switch='materialization=off,semijoin=off'; +EXPLAIN SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb +WHERE tb.d >= SOME(SELECT e FROM t3 as tc +WHERE ta.b=tc.e)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY ta ALL NULL NULL NULL NULL 7 Using where +2 DEPENDENT SUBQUERY tb ALL NULL NULL NULL NULL 6 Using where +3 DEPENDENT SUBQUERY tc ALL NULL NULL NULL NULL 4 Using where +SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb +WHERE tb.d >= SOME(SELECT e FROM t3 as tc +WHERE ta.b=tc.e)); +a b +2 10 +2 20 +3 20 +2 30 +set @@optimizer_switch='materialization=off,semijoin=on'; +EXPLAIN SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb +WHERE tb.d >= SOME(SELECT e FROM t3 as tc +WHERE ta.b=tc.e)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY tb ALL NULL NULL NULL NULL 6 Start temporary +1 PRIMARY ta ALL NULL NULL NULL NULL 7 Using where; End temporary; Using join buffer (BNL, regular buffers) +3 DEPENDENT SUBQUERY tc ALL NULL NULL NULL NULL 4 Using where +SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb +WHERE tb.d >= SOME(SELECT e FROM t3 as tc +WHERE ta.b=tc.e)); +a b +2 10 +2 20 +3 20 +2 30 +# Subquery with GROUP BY and HAVING +set @@optimizer_switch='materialization=off,semijoin=off'; +EXPLAIN SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb +WHERE tb.d >= SOME(SELECT SUM(g) FROM t4 as tc +GROUP BY f +HAVING ta.a=tc.f)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY ta ALL NULL NULL NULL NULL 7 Using where +2 DEPENDENT SUBQUERY tb ALL NULL NULL NULL NULL 6 Using where +3 DEPENDENT SUBQUERY tc ALL NULL NULL NULL NULL 6 Using temporary; Using filesort +SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb +WHERE tb.d >= SOME(SELECT SUM(g) FROM t4 as tc +GROUP BY f +HAVING ta.a=tc.f)); +a b +2 10 +2 20 +3 20 +2 30 +set @@optimizer_switch='materialization=off,semijoin=on'; +# The query result with semijoin is WRONG +EXPLAIN SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb +WHERE tb.d >= SOME(SELECT SUM(g) FROM t4 as tc +GROUP BY f +HAVING ta.a=tc.f)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY tb ALL NULL NULL NULL NULL 6 Using where; Start temporary +1 PRIMARY ta ALL NULL NULL NULL NULL 7 Using where; End temporary; Using join buffer (BNL, regular buffers) +3 DEPENDENT SUBQUERY tc ALL NULL NULL NULL NULL 6 Using temporary; Using filesort +SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb +WHERE tb.d >= SOME(SELECT SUM(g) FROM t4 as tc +GROUP BY f +HAVING ta.a=tc.f)); +a b +# Subquery with ORDER BY and LIMIT +set @@optimizer_switch='materialization=off,semijoin=off'; +# NOTE: The ordered subquery should have a LIMIT clause to make sense +EXPLAIN SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb +WHERE tb.d IN (SELECT g FROM t4 as tc +WHERE ta.a=tc.f +ORDER BY tc.f)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY ta ALL NULL NULL NULL NULL 7 Using where +2 DEPENDENT SUBQUERY tb ALL NULL NULL NULL NULL 6 Using where +3 DEPENDENT SUBQUERY tc ALL NULL NULL NULL NULL 6 Using where +SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb +WHERE tb.d IN (SELECT g FROM t4 as tc +WHERE ta.a=tc.f +ORDER BY tc.f)); +a b +2 10 +2 20 +2 30 +4 40 +set @@optimizer_switch='materialization=off,semijoin=on'; +# The query result with semijoin is WRONG +EXPLAIN SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb +WHERE tb.d IN (SELECT g FROM t4 as tc +WHERE ta.a=tc.f +ORDER BY tc.f)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY tb ALL NULL NULL NULL NULL 6 Start temporary +1 PRIMARY ta ALL NULL NULL NULL NULL 7 Using where; End temporary; Using join buffer (BNL, regular buffers) +3 DEPENDENT SUBQUERY tc ALL NULL NULL NULL NULL 6 Using where +SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb +WHERE tb.d IN (SELECT g FROM t4 as tc +WHERE ta.a=tc.f +ORDER BY tc.f)); +a b +2 10 +2 20 +2 30 +4 40 +# Reference to the transformed-away query block (dependency was wrong) +set @@optimizer_switch='materialization=off,semijoin=off'; +EXPLAIN SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb +WHERE tb.d >= SOME(SELECT e FROM t3 as tc +WHERE tb.d=tc.e)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY ta ALL NULL NULL NULL NULL 7 Using where +2 DEPENDENT SUBQUERY tb ALL NULL NULL NULL NULL 6 Using where +3 DEPENDENT SUBQUERY tc ALL NULL NULL NULL NULL 4 Using where +SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb +WHERE tb.d >= SOME(SELECT e FROM t3 as tc +WHERE tb.d=tc.e)); +a b +2 10 +2 20 +3 20 +2 30 +4 40 +set @@optimizer_switch='materialization=off,semijoin=on'; +EXPLAIN SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb +WHERE tb.d >= SOME(SELECT e FROM t3 as tc +WHERE tb.d=tc.e)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY tb ALL NULL NULL NULL NULL 6 Using where; Start temporary +1 PRIMARY ta ALL NULL NULL NULL NULL 7 Using where; End temporary; Using join buffer (BNL, regular buffers) +3 DEPENDENT SUBQUERY tc ALL NULL NULL NULL NULL 4 Using where +SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb +WHERE tb.d >= SOME(SELECT e FROM t3 as tc +WHERE tb.d=tc.e)); +a b +2 10 +2 20 +3 20 +2 30 +4 40 +# Reference above the parent query block (should not be affected) +set @@optimizer_switch='materialization=off,semijoin=off'; +EXPLAIN SELECT * FROM t1 AS t +WHERE t.a NOT IN (SELECT a FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb +WHERE tb.d >= SOME(SELECT e FROM t3 as tc +WHERE t.b=tc.e))); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t ALL NULL NULL NULL NULL 7 Using where +2 DEPENDENT SUBQUERY ta ALL NULL NULL NULL NULL 7 Using where +3 DEPENDENT SUBQUERY tb ALL NULL NULL NULL NULL 6 Using where +4 DEPENDENT SUBQUERY tc ALL NULL NULL NULL NULL 4 Using where +SELECT * FROM t1 AS t +WHERE t.a NOT IN (SELECT a FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb +WHERE tb.d >= SOME(SELECT e FROM t3 as tc +WHERE t.b=tc.e))); +a b +1 10 +1 20 +4 40 +set @@optimizer_switch='materialization=off,semijoin=on'; +EXPLAIN SELECT * FROM t1 AS t +WHERE t.a NOT IN (SELECT a FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb +WHERE tb.d >= SOME(SELECT e FROM t3 as tc +WHERE t.b=tc.e))); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t ALL NULL NULL NULL NULL 7 Using where +2 DEPENDENT SUBQUERY tb ALL NULL NULL NULL NULL 6 Using where; Start temporary +2 DEPENDENT SUBQUERY ta ALL NULL NULL NULL NULL 7 Using where; End temporary; Using join buffer (BNL, regular buffers) +4 DEPENDENT SUBQUERY tc ALL NULL NULL NULL NULL 4 Using where +SELECT * FROM t1 AS t +WHERE t.a NOT IN (SELECT a FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb +WHERE tb.d >= SOME(SELECT e FROM t3 as tc +WHERE t.b=tc.e))); +a b +1 10 +1 20 +4 40 +# EXISTS with reference to the parent query block +set @@optimizer_switch='materialization=off,semijoin=off'; +EXPLAIN SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb +WHERE EXISTS (SELECT * FROM t3 as tc +WHERE ta.b=tc.e)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY ta ALL NULL NULL NULL NULL 7 Using where +2 DEPENDENT SUBQUERY tb ALL NULL NULL NULL NULL 6 Using where +3 DEPENDENT SUBQUERY tc ALL NULL NULL NULL NULL 4 Using where +SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb +WHERE EXISTS (SELECT * FROM t3 as tc +WHERE ta.b=tc.e)); +a b +2 10 +2 20 +3 20 +2 30 +set @@optimizer_switch='materialization=off,semijoin=on'; +EXPLAIN SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb +WHERE EXISTS (SELECT * FROM t3 as tc +WHERE ta.b=tc.e)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY tb ALL NULL NULL NULL NULL 6 Start temporary +1 PRIMARY ta ALL NULL NULL NULL NULL 7 Using where; End temporary; Using join buffer (BNL, regular buffers) +3 DEPENDENT SUBQUERY tc ALL NULL NULL NULL NULL 4 Using where +SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb +WHERE EXISTS (SELECT * FROM t3 as tc +WHERE ta.b=tc.e)); +a b +2 10 +2 20 +3 20 +2 30 +# Scalar subquery with reference to the parent query block +set @@optimizer_switch='materialization=off,semijoin=off'; +EXPLAIN SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb +WHERE tb.d = (SELECT MIN(e) FROM t3 as tc +WHERE ta.b=tc.e)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY ta ALL NULL NULL NULL NULL 7 Using where +2 DEPENDENT SUBQUERY tb ALL NULL NULL NULL NULL 6 Using where +3 DEPENDENT SUBQUERY tc ALL NULL NULL NULL NULL 4 Using where +SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb +WHERE tb.d = (SELECT MIN(e) FROM t3 as tc +WHERE ta.b=tc.e)); +a b +2 10 +2 20 +3 20 +set @@optimizer_switch='materialization=off,semijoin=on'; +EXPLAIN SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb +WHERE tb.d = (SELECT MIN(e) FROM t3 as tc +WHERE ta.b=tc.e)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY tb ALL NULL NULL NULL NULL 6 Start temporary +1 PRIMARY ta ALL NULL NULL NULL NULL 7 Using where; End temporary; Using join buffer (BNL, regular buffers) +3 DEPENDENT SUBQUERY tc ALL NULL NULL NULL NULL 4 Using where +SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb +WHERE tb.d = (SELECT MIN(e) FROM t3 as tc +WHERE ta.b=tc.e)); +a b +2 10 +2 20 +3 20 +# Combine scalar subquery with quantified comparison subquery +set @@optimizer_switch='materialization=off,semijoin=off'; +EXPLAIN SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb +WHERE (SELECT MIN(e) FROM t3 as tc +WHERE tb.d=tc.e) < SOME(SELECT e FROM t3 as tc +WHERE ta.b=tc.e)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY ta ALL NULL NULL NULL NULL 7 Using where +2 DEPENDENT SUBQUERY tb ALL NULL NULL NULL NULL 6 Using where +4 DEPENDENT SUBQUERY tc ALL NULL NULL NULL NULL 4 Using where +3 DEPENDENT SUBQUERY tc ALL NULL NULL NULL NULL 4 Using where +SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb +WHERE (SELECT MIN(e) FROM t3 as tc +WHERE tb.d=tc.e) < SOME(SELECT e FROM t3 as tc +WHERE ta.b=tc.e)); +a b +2 20 +2 30 +set @@optimizer_switch='materialization=off,semijoin=on'; +EXPLAIN SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb +WHERE (SELECT MIN(e) FROM t3 as tc +WHERE tb.d=tc.e) < SOME(SELECT e FROM t3 as tc +WHERE ta.b=tc.e)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY tb ALL NULL NULL NULL NULL 6 Start temporary +1 PRIMARY ta ALL NULL NULL NULL NULL 7 Using where; End temporary; Using join buffer (BNL, regular buffers) +4 DEPENDENT SUBQUERY tc ALL NULL NULL NULL NULL 4 Using where +3 DEPENDENT SUBQUERY tc ALL NULL NULL NULL NULL 4 Using where +SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb +WHERE (SELECT MIN(e) FROM t3 as tc +WHERE tb.d=tc.e) < SOME(SELECT e FROM t3 as tc +WHERE ta.b=tc.e)); +a b +2 20 +2 30 +DROP TABLE t1, t2, t3, t4; +set @@optimizer_switch='default'; +# End of BUG#31480 === modified file 'mysql-test/r/subquery_sj_all.result' --- a/mysql-test/r/subquery_sj_all.result 2010-09-20 14:06:02 +0000 +++ b/mysql-test/r/subquery_sj_all.result 2010-10-15 10:32:50 +0000 @@ -3784,8 +3784,8 @@ SELECT t1field FROM t1 WHERE t1field IN (SELECT v1field FROM v1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using where; Using index -1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index +1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using index +1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using where; Using index 4 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 Using index CREATE PROCEDURE p1() BEGIN @@ -5148,4 +5148,42 @@ a 1 1 DROP TABLE t1,t2,t3; +# +# BUG#52329 - Wrong result: subquery materialization, IN, +# non-null field followed by nullable +# +CREATE TABLE t1 (a1 CHAR(8) NOT NULL, a2 char(8) NOT NULL); +CREATE TABLE t2a (b1 char(8), b2 char(8)); +CREATE TABLE t2b (b1 CHAR(8), b2 char(8) NOT NULL); +CREATE TABLE t2c (b1 CHAR(8) NOT NULL, b2 char(8)); +INSERT INTO t1 VALUES ('1 - 12', '2 - 22'); +INSERT INTO t2a VALUES ('1 - 11', '2 - 21'), +('1 - 11', '2 - 21'), +('1 - 12', '2 - 22'), +('1 - 12', '2 - 22'), +('1 - 13', '2 - 23'); +INSERT INTO t2b SELECT * FROM t2a; +INSERT INTO t2c SELECT * FROM t2a; +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2c WHERE b1 > '0' GROUP BY b1, b2); +a1 a2 +1 - 12 2 - 22 +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2a WHERE b1 > '0'); +a1 a2 +1 - 12 2 - 22 +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2b WHERE b1 > '0'); +a1 a2 +1 - 12 2 - 22 +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2c WHERE b1 > '0'); +a1 a2 +1 - 12 2 - 22 +DROP TABLE t1,t2a,t2b,t2c; +# End BUG#52329 set optimizer_switch=default; === modified file 'mysql-test/r/subquery_sj_all_jcl6.result' --- a/mysql-test/r/subquery_sj_all_jcl6.result 2010-09-20 14:06:02 +0000 +++ b/mysql-test/r/subquery_sj_all_jcl6.result 2010-10-15 10:32:50 +0000 @@ -3788,8 +3788,8 @@ SELECT t1field FROM t1 WHERE t1field IN (SELECT v1field FROM v1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using where; Using index -1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index +1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using index +1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using where; Using index 4 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 Using index CREATE PROCEDURE p1() BEGIN @@ -5152,6 +5152,44 @@ a 1 1 DROP TABLE t1,t2,t3; +# +# BUG#52329 - Wrong result: subquery materialization, IN, +# non-null field followed by nullable +# +CREATE TABLE t1 (a1 CHAR(8) NOT NULL, a2 char(8) NOT NULL); +CREATE TABLE t2a (b1 char(8), b2 char(8)); +CREATE TABLE t2b (b1 CHAR(8), b2 char(8) NOT NULL); +CREATE TABLE t2c (b1 CHAR(8) NOT NULL, b2 char(8)); +INSERT INTO t1 VALUES ('1 - 12', '2 - 22'); +INSERT INTO t2a VALUES ('1 - 11', '2 - 21'), +('1 - 11', '2 - 21'), +('1 - 12', '2 - 22'), +('1 - 12', '2 - 22'), +('1 - 13', '2 - 23'); +INSERT INTO t2b SELECT * FROM t2a; +INSERT INTO t2c SELECT * FROM t2a; +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2c WHERE b1 > '0' GROUP BY b1, b2); +a1 a2 +1 - 12 2 - 22 +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2a WHERE b1 > '0'); +a1 a2 +1 - 12 2 - 22 +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2b WHERE b1 > '0'); +a1 a2 +1 - 12 2 - 22 +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2c WHERE b1 > '0'); +a1 a2 +1 - 12 2 - 22 +DROP TABLE t1,t2a,t2b,t2c; +# End BUG#52329 set optimizer_switch=default; set optimizer_join_cache_level=default; show variables like 'optimizer_join_cache_level'; === modified file 'mysql-test/r/subquery_sj_all_jcl7.result' --- a/mysql-test/r/subquery_sj_all_jcl7.result 2010-09-20 14:06:02 +0000 +++ b/mysql-test/r/subquery_sj_all_jcl7.result 2010-10-15 10:32:50 +0000 @@ -3788,8 +3788,8 @@ SELECT t1field FROM t1 WHERE t1field IN (SELECT v1field FROM v1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using where; Using index -1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index +1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using index +1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using where; Using index 4 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 Using index CREATE PROCEDURE p1() BEGIN @@ -5152,6 +5152,44 @@ a 1 1 DROP TABLE t1,t2,t3; +# +# BUG#52329 - Wrong result: subquery materialization, IN, +# non-null field followed by nullable +# +CREATE TABLE t1 (a1 CHAR(8) NOT NULL, a2 char(8) NOT NULL); +CREATE TABLE t2a (b1 char(8), b2 char(8)); +CREATE TABLE t2b (b1 CHAR(8), b2 char(8) NOT NULL); +CREATE TABLE t2c (b1 CHAR(8) NOT NULL, b2 char(8)); +INSERT INTO t1 VALUES ('1 - 12', '2 - 22'); +INSERT INTO t2a VALUES ('1 - 11', '2 - 21'), +('1 - 11', '2 - 21'), +('1 - 12', '2 - 22'), +('1 - 12', '2 - 22'), +('1 - 13', '2 - 23'); +INSERT INTO t2b SELECT * FROM t2a; +INSERT INTO t2c SELECT * FROM t2a; +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2c WHERE b1 > '0' GROUP BY b1, b2); +a1 a2 +1 - 12 2 - 22 +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2a WHERE b1 > '0'); +a1 a2 +1 - 12 2 - 22 +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2b WHERE b1 > '0'); +a1 a2 +1 - 12 2 - 22 +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2c WHERE b1 > '0'); +a1 a2 +1 - 12 2 - 22 +DROP TABLE t1,t2a,t2b,t2c; +# End BUG#52329 set optimizer_switch=default; set optimizer_join_cache_level=default; show variables like 'optimizer_join_cache_level'; === modified file 'mysql-test/r/subquery_sj_dupsweed.result' --- a/mysql-test/r/subquery_sj_dupsweed.result 2010-09-20 14:06:02 +0000 +++ b/mysql-test/r/subquery_sj_dupsweed.result 2010-10-15 10:32:50 +0000 @@ -3783,8 +3783,8 @@ SELECT t1field FROM t1 WHERE t1field IN (SELECT v1field FROM v1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using where; Using index -1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index +1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using index +1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using where; Using index 4 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 Using index CREATE PROCEDURE p1() BEGIN @@ -5146,4 +5146,42 @@ a 1 1 DROP TABLE t1,t2,t3; +# +# BUG#52329 - Wrong result: subquery materialization, IN, +# non-null field followed by nullable +# +CREATE TABLE t1 (a1 CHAR(8) NOT NULL, a2 char(8) NOT NULL); +CREATE TABLE t2a (b1 char(8), b2 char(8)); +CREATE TABLE t2b (b1 CHAR(8), b2 char(8) NOT NULL); +CREATE TABLE t2c (b1 CHAR(8) NOT NULL, b2 char(8)); +INSERT INTO t1 VALUES ('1 - 12', '2 - 22'); +INSERT INTO t2a VALUES ('1 - 11', '2 - 21'), +('1 - 11', '2 - 21'), +('1 - 12', '2 - 22'), +('1 - 12', '2 - 22'), +('1 - 13', '2 - 23'); +INSERT INTO t2b SELECT * FROM t2a; +INSERT INTO t2c SELECT * FROM t2a; +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2c WHERE b1 > '0' GROUP BY b1, b2); +a1 a2 +1 - 12 2 - 22 +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2a WHERE b1 > '0'); +a1 a2 +1 - 12 2 - 22 +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2b WHERE b1 > '0'); +a1 a2 +1 - 12 2 - 22 +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2c WHERE b1 > '0'); +a1 a2 +1 - 12 2 - 22 +DROP TABLE t1,t2a,t2b,t2c; +# End BUG#52329 set optimizer_switch=default; === modified file 'mysql-test/r/subquery_sj_dupsweed_jcl6.result' --- a/mysql-test/r/subquery_sj_dupsweed_jcl6.result 2010-09-20 14:06:02 +0000 +++ b/mysql-test/r/subquery_sj_dupsweed_jcl6.result 2010-10-15 10:32:50 +0000 @@ -3787,8 +3787,8 @@ SELECT t1field FROM t1 WHERE t1field IN (SELECT v1field FROM v1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using where; Using index -1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index +1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using index +1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using where; Using index 4 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 Using index CREATE PROCEDURE p1() BEGIN @@ -5150,6 +5150,44 @@ a 1 1 DROP TABLE t1,t2,t3; +# +# BUG#52329 - Wrong result: subquery materialization, IN, +# non-null field followed by nullable +# +CREATE TABLE t1 (a1 CHAR(8) NOT NULL, a2 char(8) NOT NULL); +CREATE TABLE t2a (b1 char(8), b2 char(8)); +CREATE TABLE t2b (b1 CHAR(8), b2 char(8) NOT NULL); +CREATE TABLE t2c (b1 CHAR(8) NOT NULL, b2 char(8)); +INSERT INTO t1 VALUES ('1 - 12', '2 - 22'); +INSERT INTO t2a VALUES ('1 - 11', '2 - 21'), +('1 - 11', '2 - 21'), +('1 - 12', '2 - 22'), +('1 - 12', '2 - 22'), +('1 - 13', '2 - 23'); +INSERT INTO t2b SELECT * FROM t2a; +INSERT INTO t2c SELECT * FROM t2a; +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2c WHERE b1 > '0' GROUP BY b1, b2); +a1 a2 +1 - 12 2 - 22 +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2a WHERE b1 > '0'); +a1 a2 +1 - 12 2 - 22 +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2b WHERE b1 > '0'); +a1 a2 +1 - 12 2 - 22 +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2c WHERE b1 > '0'); +a1 a2 +1 - 12 2 - 22 +DROP TABLE t1,t2a,t2b,t2c; +# End BUG#52329 set optimizer_switch=default; set optimizer_join_cache_level=default; show variables like 'optimizer_join_cache_level'; === modified file 'mysql-test/r/subquery_sj_dupsweed_jcl7.result' --- a/mysql-test/r/subquery_sj_dupsweed_jcl7.result 2010-09-20 14:06:02 +0000 +++ b/mysql-test/r/subquery_sj_dupsweed_jcl7.result 2010-10-15 10:32:50 +0000 @@ -3787,8 +3787,8 @@ SELECT t1field FROM t1 WHERE t1field IN (SELECT v1field FROM v1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using where; Using index -1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index +1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using index +1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using where; Using index 4 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 Using index CREATE PROCEDURE p1() BEGIN @@ -5150,6 +5150,44 @@ a 1 1 DROP TABLE t1,t2,t3; +# +# BUG#52329 - Wrong result: subquery materialization, IN, +# non-null field followed by nullable +# +CREATE TABLE t1 (a1 CHAR(8) NOT NULL, a2 char(8) NOT NULL); +CREATE TABLE t2a (b1 char(8), b2 char(8)); +CREATE TABLE t2b (b1 CHAR(8), b2 char(8) NOT NULL); +CREATE TABLE t2c (b1 CHAR(8) NOT NULL, b2 char(8)); +INSERT INTO t1 VALUES ('1 - 12', '2 - 22'); +INSERT INTO t2a VALUES ('1 - 11', '2 - 21'), +('1 - 11', '2 - 21'), +('1 - 12', '2 - 22'), +('1 - 12', '2 - 22'), +('1 - 13', '2 - 23'); +INSERT INTO t2b SELECT * FROM t2a; +INSERT INTO t2c SELECT * FROM t2a; +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2c WHERE b1 > '0' GROUP BY b1, b2); +a1 a2 +1 - 12 2 - 22 +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2a WHERE b1 > '0'); +a1 a2 +1 - 12 2 - 22 +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2b WHERE b1 > '0'); +a1 a2 +1 - 12 2 - 22 +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2c WHERE b1 > '0'); +a1 a2 +1 - 12 2 - 22 +DROP TABLE t1,t2a,t2b,t2c; +# End BUG#52329 set optimizer_switch=default; set optimizer_join_cache_level=default; show variables like 'optimizer_join_cache_level'; === modified file 'mysql-test/r/subquery_sj_firstmatch.result' --- a/mysql-test/r/subquery_sj_firstmatch.result 2010-09-20 14:06:02 +0000 +++ b/mysql-test/r/subquery_sj_firstmatch.result 2010-10-15 10:32:50 +0000 @@ -3784,8 +3784,8 @@ SELECT t1field FROM t1 WHERE t1field IN (SELECT v1field FROM v1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using where; Using index -1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index +1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using index +1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using where; Using index 4 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 Using index CREATE PROCEDURE p1() BEGIN @@ -5148,6 +5148,44 @@ a 1 DROP TABLE t1,t2,t3; # +# BUG#52329 - Wrong result: subquery materialization, IN, +# non-null field followed by nullable +# +CREATE TABLE t1 (a1 CHAR(8) NOT NULL, a2 char(8) NOT NULL); +CREATE TABLE t2a (b1 char(8), b2 char(8)); +CREATE TABLE t2b (b1 CHAR(8), b2 char(8) NOT NULL); +CREATE TABLE t2c (b1 CHAR(8) NOT NULL, b2 char(8)); +INSERT INTO t1 VALUES ('1 - 12', '2 - 22'); +INSERT INTO t2a VALUES ('1 - 11', '2 - 21'), +('1 - 11', '2 - 21'), +('1 - 12', '2 - 22'), +('1 - 12', '2 - 22'), +('1 - 13', '2 - 23'); +INSERT INTO t2b SELECT * FROM t2a; +INSERT INTO t2c SELECT * FROM t2a; +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2c WHERE b1 > '0' GROUP BY b1, b2); +a1 a2 +1 - 12 2 - 22 +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2a WHERE b1 > '0'); +a1 a2 +1 - 12 2 - 22 +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2b WHERE b1 > '0'); +a1 a2 +1 - 12 2 - 22 +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2c WHERE b1 > '0'); +a1 a2 +1 - 12 2 - 22 +DROP TABLE t1,t2a,t2b,t2c; +# End BUG#52329 +# # Bug#51457 Firstmatch semijoin strategy gives wrong results for # certain query plans # === modified file 'mysql-test/r/subquery_sj_firstmatch_jcl6.result' --- a/mysql-test/r/subquery_sj_firstmatch_jcl6.result 2010-09-20 14:06:02 +0000 +++ b/mysql-test/r/subquery_sj_firstmatch_jcl6.result 2010-10-15 10:32:50 +0000 @@ -3788,8 +3788,8 @@ SELECT t1field FROM t1 WHERE t1field IN (SELECT v1field FROM v1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using where; Using index -1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index +1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using index +1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using where; Using index 4 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 Using index CREATE PROCEDURE p1() BEGIN @@ -5152,6 +5152,44 @@ a 1 DROP TABLE t1,t2,t3; # +# BUG#52329 - Wrong result: subquery materialization, IN, +# non-null field followed by nullable +# +CREATE TABLE t1 (a1 CHAR(8) NOT NULL, a2 char(8) NOT NULL); +CREATE TABLE t2a (b1 char(8), b2 char(8)); +CREATE TABLE t2b (b1 CHAR(8), b2 char(8) NOT NULL); +CREATE TABLE t2c (b1 CHAR(8) NOT NULL, b2 char(8)); +INSERT INTO t1 VALUES ('1 - 12', '2 - 22'); +INSERT INTO t2a VALUES ('1 - 11', '2 - 21'), +('1 - 11', '2 - 21'), +('1 - 12', '2 - 22'), +('1 - 12', '2 - 22'), +('1 - 13', '2 - 23'); +INSERT INTO t2b SELECT * FROM t2a; +INSERT INTO t2c SELECT * FROM t2a; +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2c WHERE b1 > '0' GROUP BY b1, b2); +a1 a2 +1 - 12 2 - 22 +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2a WHERE b1 > '0'); +a1 a2 +1 - 12 2 - 22 +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2b WHERE b1 > '0'); +a1 a2 +1 - 12 2 - 22 +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2c WHERE b1 > '0'); +a1 a2 +1 - 12 2 - 22 +DROP TABLE t1,t2a,t2b,t2c; +# End BUG#52329 +# # Bug#51457 Firstmatch semijoin strategy gives wrong results for # certain query plans # === modified file 'mysql-test/r/subquery_sj_firstmatch_jcl7.result' --- a/mysql-test/r/subquery_sj_firstmatch_jcl7.result 2010-09-20 14:06:02 +0000 +++ b/mysql-test/r/subquery_sj_firstmatch_jcl7.result 2010-10-15 10:32:50 +0000 @@ -3788,8 +3788,8 @@ SELECT t1field FROM t1 WHERE t1field IN (SELECT v1field FROM v1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using where; Using index -1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index +1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using index +1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using where; Using index 4 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 Using index CREATE PROCEDURE p1() BEGIN @@ -5152,6 +5152,44 @@ a 1 DROP TABLE t1,t2,t3; # +# BUG#52329 - Wrong result: subquery materialization, IN, +# non-null field followed by nullable +# +CREATE TABLE t1 (a1 CHAR(8) NOT NULL, a2 char(8) NOT NULL); +CREATE TABLE t2a (b1 char(8), b2 char(8)); +CREATE TABLE t2b (b1 CHAR(8), b2 char(8) NOT NULL); +CREATE TABLE t2c (b1 CHAR(8) NOT NULL, b2 char(8)); +INSERT INTO t1 VALUES ('1 - 12', '2 - 22'); +INSERT INTO t2a VALUES ('1 - 11', '2 - 21'), +('1 - 11', '2 - 21'), +('1 - 12', '2 - 22'), +('1 - 12', '2 - 22'), +('1 - 13', '2 - 23'); +INSERT INTO t2b SELECT * FROM t2a; +INSERT INTO t2c SELECT * FROM t2a; +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2c WHERE b1 > '0' GROUP BY b1, b2); +a1 a2 +1 - 12 2 - 22 +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2a WHERE b1 > '0'); +a1 a2 +1 - 12 2 - 22 +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2b WHERE b1 > '0'); +a1 a2 +1 - 12 2 - 22 +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2c WHERE b1 > '0'); +a1 a2 +1 - 12 2 - 22 +DROP TABLE t1,t2a,t2b,t2c; +# End BUG#52329 +# # Bug#51457 Firstmatch semijoin strategy gives wrong results for # certain query plans # === modified file 'mysql-test/r/subquery_sj_loosescan.result' --- a/mysql-test/r/subquery_sj_loosescan.result 2010-09-20 14:06:02 +0000 +++ b/mysql-test/r/subquery_sj_loosescan.result 2010-10-15 10:32:50 +0000 @@ -3788,8 +3788,8 @@ SELECT t1field FROM t1 WHERE t1field IN (SELECT v1field FROM v1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using where; Using index -1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index +1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using index +1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using where; Using index 4 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 Using index CREATE PROCEDURE p1() BEGIN @@ -5151,4 +5151,42 @@ a 1 1 DROP TABLE t1,t2,t3; +# +# BUG#52329 - Wrong result: subquery materialization, IN, +# non-null field followed by nullable +# +CREATE TABLE t1 (a1 CHAR(8) NOT NULL, a2 char(8) NOT NULL); +CREATE TABLE t2a (b1 char(8), b2 char(8)); +CREATE TABLE t2b (b1 CHAR(8), b2 char(8) NOT NULL); +CREATE TABLE t2c (b1 CHAR(8) NOT NULL, b2 char(8)); +INSERT INTO t1 VALUES ('1 - 12', '2 - 22'); +INSERT INTO t2a VALUES ('1 - 11', '2 - 21'), +('1 - 11', '2 - 21'), +('1 - 12', '2 - 22'), +('1 - 12', '2 - 22'), +('1 - 13', '2 - 23'); +INSERT INTO t2b SELECT * FROM t2a; +INSERT INTO t2c SELECT * FROM t2a; +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2c WHERE b1 > '0' GROUP BY b1, b2); +a1 a2 +1 - 12 2 - 22 +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2a WHERE b1 > '0'); +a1 a2 +1 - 12 2 - 22 +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2b WHERE b1 > '0'); +a1 a2 +1 - 12 2 - 22 +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2c WHERE b1 > '0'); +a1 a2 +1 - 12 2 - 22 +DROP TABLE t1,t2a,t2b,t2c; +# End BUG#52329 set optimizer_switch=default; === modified file 'mysql-test/r/subquery_sj_loosescan_jcl6.result' --- a/mysql-test/r/subquery_sj_loosescan_jcl6.result 2010-09-20 14:06:02 +0000 +++ b/mysql-test/r/subquery_sj_loosescan_jcl6.result 2010-10-15 10:32:50 +0000 @@ -3792,8 +3792,8 @@ SELECT t1field FROM t1 WHERE t1field IN (SELECT v1field FROM v1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using where; Using index -1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index +1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using index +1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using where; Using index 4 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 Using index CREATE PROCEDURE p1() BEGIN @@ -5155,6 +5155,44 @@ a 1 1 DROP TABLE t1,t2,t3; +# +# BUG#52329 - Wrong result: subquery materialization, IN, +# non-null field followed by nullable +# +CREATE TABLE t1 (a1 CHAR(8) NOT NULL, a2 char(8) NOT NULL); +CREATE TABLE t2a (b1 char(8), b2 char(8)); +CREATE TABLE t2b (b1 CHAR(8), b2 char(8) NOT NULL); +CREATE TABLE t2c (b1 CHAR(8) NOT NULL, b2 char(8)); +INSERT INTO t1 VALUES ('1 - 12', '2 - 22'); +INSERT INTO t2a VALUES ('1 - 11', '2 - 21'), +('1 - 11', '2 - 21'), +('1 - 12', '2 - 22'), +('1 - 12', '2 - 22'), +('1 - 13', '2 - 23'); +INSERT INTO t2b SELECT * FROM t2a; +INSERT INTO t2c SELECT * FROM t2a; +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2c WHERE b1 > '0' GROUP BY b1, b2); +a1 a2 +1 - 12 2 - 22 +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2a WHERE b1 > '0'); +a1 a2 +1 - 12 2 - 22 +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2b WHERE b1 > '0'); +a1 a2 +1 - 12 2 - 22 +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2c WHERE b1 > '0'); +a1 a2 +1 - 12 2 - 22 +DROP TABLE t1,t2a,t2b,t2c; +# End BUG#52329 set optimizer_switch=default; set optimizer_join_cache_level=default; show variables like 'optimizer_join_cache_level'; === modified file 'mysql-test/r/subquery_sj_loosescan_jcl7.result' --- a/mysql-test/r/subquery_sj_loosescan_jcl7.result 2010-09-20 14:06:02 +0000 +++ b/mysql-test/r/subquery_sj_loosescan_jcl7.result 2010-10-15 10:32:50 +0000 @@ -3792,8 +3792,8 @@ SELECT t1field FROM t1 WHERE t1field IN (SELECT v1field FROM v1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using where; Using index -1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index +1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using index +1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using where; Using index 4 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 Using index CREATE PROCEDURE p1() BEGIN @@ -5155,6 +5155,44 @@ a 1 1 DROP TABLE t1,t2,t3; +# +# BUG#52329 - Wrong result: subquery materialization, IN, +# non-null field followed by nullable +# +CREATE TABLE t1 (a1 CHAR(8) NOT NULL, a2 char(8) NOT NULL); +CREATE TABLE t2a (b1 char(8), b2 char(8)); +CREATE TABLE t2b (b1 CHAR(8), b2 char(8) NOT NULL); +CREATE TABLE t2c (b1 CHAR(8) NOT NULL, b2 char(8)); +INSERT INTO t1 VALUES ('1 - 12', '2 - 22'); +INSERT INTO t2a VALUES ('1 - 11', '2 - 21'), +('1 - 11', '2 - 21'), +('1 - 12', '2 - 22'), +('1 - 12', '2 - 22'), +('1 - 13', '2 - 23'); +INSERT INTO t2b SELECT * FROM t2a; +INSERT INTO t2c SELECT * FROM t2a; +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2c WHERE b1 > '0' GROUP BY b1, b2); +a1 a2 +1 - 12 2 - 22 +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2a WHERE b1 > '0'); +a1 a2 +1 - 12 2 - 22 +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2b WHERE b1 > '0'); +a1 a2 +1 - 12 2 - 22 +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2c WHERE b1 > '0'); +a1 a2 +1 - 12 2 - 22 +DROP TABLE t1,t2a,t2b,t2c; +# End BUG#52329 set optimizer_switch=default; set optimizer_join_cache_level=default; show variables like 'optimizer_join_cache_level'; === modified file 'mysql-test/r/subquery_sj_mat.result' --- a/mysql-test/r/subquery_sj_mat.result 2010-09-20 14:06:02 +0000 +++ b/mysql-test/r/subquery_sj_mat.result 2010-10-15 10:32:50 +0000 @@ -3794,8 +3794,8 @@ SELECT t1field FROM t1 WHERE t1field IN (SELECT v1field FROM v1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using where; Using index -1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index +1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using index +1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using where; Using index 4 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 Using index CREATE PROCEDURE p1() BEGIN @@ -5158,4 +5158,42 @@ a 1 1 DROP TABLE t1,t2,t3; +# +# BUG#52329 - Wrong result: subquery materialization, IN, +# non-null field followed by nullable +# +CREATE TABLE t1 (a1 CHAR(8) NOT NULL, a2 char(8) NOT NULL); +CREATE TABLE t2a (b1 char(8), b2 char(8)); +CREATE TABLE t2b (b1 CHAR(8), b2 char(8) NOT NULL); +CREATE TABLE t2c (b1 CHAR(8) NOT NULL, b2 char(8)); +INSERT INTO t1 VALUES ('1 - 12', '2 - 22'); +INSERT INTO t2a VALUES ('1 - 11', '2 - 21'), +('1 - 11', '2 - 21'), +('1 - 12', '2 - 22'), +('1 - 12', '2 - 22'), +('1 - 13', '2 - 23'); +INSERT INTO t2b SELECT * FROM t2a; +INSERT INTO t2c SELECT * FROM t2a; +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2c WHERE b1 > '0' GROUP BY b1, b2); +a1 a2 +1 - 12 2 - 22 +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2a WHERE b1 > '0'); +a1 a2 +1 - 12 2 - 22 +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2b WHERE b1 > '0'); +a1 a2 +1 - 12 2 - 22 +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2c WHERE b1 > '0'); +a1 a2 +1 - 12 2 - 22 +DROP TABLE t1,t2a,t2b,t2c; +# End BUG#52329 set optimizer_switch=default; === modified file 'mysql-test/r/subquery_sj_mat_jcl6.result' --- a/mysql-test/r/subquery_sj_mat_jcl6.result 2010-09-20 14:06:02 +0000 +++ b/mysql-test/r/subquery_sj_mat_jcl6.result 2010-10-15 10:32:50 +0000 @@ -3798,8 +3798,8 @@ SELECT t1field FROM t1 WHERE t1field IN (SELECT v1field FROM v1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using where; Using index -1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index +1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using index +1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using where; Using index 4 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 Using index CREATE PROCEDURE p1() BEGIN @@ -5162,6 +5162,44 @@ a 1 1 DROP TABLE t1,t2,t3; +# +# BUG#52329 - Wrong result: subquery materialization, IN, +# non-null field followed by nullable +# +CREATE TABLE t1 (a1 CHAR(8) NOT NULL, a2 char(8) NOT NULL); +CREATE TABLE t2a (b1 char(8), b2 char(8)); +CREATE TABLE t2b (b1 CHAR(8), b2 char(8) NOT NULL); +CREATE TABLE t2c (b1 CHAR(8) NOT NULL, b2 char(8)); +INSERT INTO t1 VALUES ('1 - 12', '2 - 22'); +INSERT INTO t2a VALUES ('1 - 11', '2 - 21'), +('1 - 11', '2 - 21'), +('1 - 12', '2 - 22'), +('1 - 12', '2 - 22'), +('1 - 13', '2 - 23'); +INSERT INTO t2b SELECT * FROM t2a; +INSERT INTO t2c SELECT * FROM t2a; +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2c WHERE b1 > '0' GROUP BY b1, b2); +a1 a2 +1 - 12 2 - 22 +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2a WHERE b1 > '0'); +a1 a2 +1 - 12 2 - 22 +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2b WHERE b1 > '0'); +a1 a2 +1 - 12 2 - 22 +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2c WHERE b1 > '0'); +a1 a2 +1 - 12 2 - 22 +DROP TABLE t1,t2a,t2b,t2c; +# End BUG#52329 set optimizer_switch=default; set optimizer_join_cache_level=default; show variables like 'optimizer_join_cache_level'; === modified file 'mysql-test/r/subquery_sj_mat_jcl7.result' --- a/mysql-test/r/subquery_sj_mat_jcl7.result 2010-09-20 14:06:02 +0000 +++ b/mysql-test/r/subquery_sj_mat_jcl7.result 2010-10-15 10:32:50 +0000 @@ -3798,8 +3798,8 @@ SELECT t1field FROM t1 WHERE t1field IN (SELECT v1field FROM v1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using where; Using index -1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index +1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using index +1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using where; Using index 4 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 Using index CREATE PROCEDURE p1() BEGIN @@ -5162,6 +5162,44 @@ a 1 1 DROP TABLE t1,t2,t3; +# +# BUG#52329 - Wrong result: subquery materialization, IN, +# non-null field followed by nullable +# +CREATE TABLE t1 (a1 CHAR(8) NOT NULL, a2 char(8) NOT NULL); +CREATE TABLE t2a (b1 char(8), b2 char(8)); +CREATE TABLE t2b (b1 CHAR(8), b2 char(8) NOT NULL); +CREATE TABLE t2c (b1 CHAR(8) NOT NULL, b2 char(8)); +INSERT INTO t1 VALUES ('1 - 12', '2 - 22'); +INSERT INTO t2a VALUES ('1 - 11', '2 - 21'), +('1 - 11', '2 - 21'), +('1 - 12', '2 - 22'), +('1 - 12', '2 - 22'), +('1 - 13', '2 - 23'); +INSERT INTO t2b SELECT * FROM t2a; +INSERT INTO t2c SELECT * FROM t2a; +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2c WHERE b1 > '0' GROUP BY b1, b2); +a1 a2 +1 - 12 2 - 22 +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2a WHERE b1 > '0'); +a1 a2 +1 - 12 2 - 22 +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2b WHERE b1 > '0'); +a1 a2 +1 - 12 2 - 22 +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2c WHERE b1 > '0'); +a1 a2 +1 - 12 2 - 22 +DROP TABLE t1,t2a,t2b,t2c; +# End BUG#52329 set optimizer_switch=default; set optimizer_join_cache_level=default; show variables like 'optimizer_join_cache_level'; === modified file 'mysql-test/r/subquery_sj_mat_nosj.result' --- a/mysql-test/r/subquery_sj_mat_nosj.result 2010-09-20 14:06:02 +0000 +++ b/mysql-test/r/subquery_sj_mat_nosj.result 2010-10-13 13:27:36 +0000 @@ -5369,4 +5369,42 @@ a 1 1 DROP TABLE t1,t2,t3; +# +# BUG#52329 - Wrong result: subquery materialization, IN, +# non-null field followed by nullable +# +CREATE TABLE t1 (a1 CHAR(8) NOT NULL, a2 char(8) NOT NULL); +CREATE TABLE t2a (b1 char(8), b2 char(8)); +CREATE TABLE t2b (b1 CHAR(8), b2 char(8) NOT NULL); +CREATE TABLE t2c (b1 CHAR(8) NOT NULL, b2 char(8)); +INSERT INTO t1 VALUES ('1 - 12', '2 - 22'); +INSERT INTO t2a VALUES ('1 - 11', '2 - 21'), +('1 - 11', '2 - 21'), +('1 - 12', '2 - 22'), +('1 - 12', '2 - 22'), +('1 - 13', '2 - 23'); +INSERT INTO t2b SELECT * FROM t2a; +INSERT INTO t2c SELECT * FROM t2a; +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2c WHERE b1 > '0' GROUP BY b1, b2); +a1 a2 +1 - 12 2 - 22 +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2a WHERE b1 > '0'); +a1 a2 +1 - 12 2 - 22 +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2b WHERE b1 > '0'); +a1 a2 +1 - 12 2 - 22 +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2c WHERE b1 > '0'); +a1 a2 +1 - 12 2 - 22 +DROP TABLE t1,t2a,t2b,t2c; +# End BUG#52329 set optimizer_switch=default; === modified file 'mysql-test/r/subquery_sj_none.result' --- a/mysql-test/r/subquery_sj_none.result 2010-09-20 14:06:02 +0000 +++ b/mysql-test/r/subquery_sj_none.result 2010-10-13 13:27:36 +0000 @@ -5295,4 +5295,42 @@ a 1 1 DROP TABLE t1,t2,t3; +# +# BUG#52329 - Wrong result: subquery materialization, IN, +# non-null field followed by nullable +# +CREATE TABLE t1 (a1 CHAR(8) NOT NULL, a2 char(8) NOT NULL); +CREATE TABLE t2a (b1 char(8), b2 char(8)); +CREATE TABLE t2b (b1 CHAR(8), b2 char(8) NOT NULL); +CREATE TABLE t2c (b1 CHAR(8) NOT NULL, b2 char(8)); +INSERT INTO t1 VALUES ('1 - 12', '2 - 22'); +INSERT INTO t2a VALUES ('1 - 11', '2 - 21'), +('1 - 11', '2 - 21'), +('1 - 12', '2 - 22'), +('1 - 12', '2 - 22'), +('1 - 13', '2 - 23'); +INSERT INTO t2b SELECT * FROM t2a; +INSERT INTO t2c SELECT * FROM t2a; +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2c WHERE b1 > '0' GROUP BY b1, b2); +a1 a2 +1 - 12 2 - 22 +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2a WHERE b1 > '0'); +a1 a2 +1 - 12 2 - 22 +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2b WHERE b1 > '0'); +a1 a2 +1 - 12 2 - 22 +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2c WHERE b1 > '0'); +a1 a2 +1 - 12 2 - 22 +DROP TABLE t1,t2a,t2b,t2c; +# End BUG#52329 set optimizer_switch=default; === modified file 'mysql-test/r/subquery_sj_none_jcl6.result' --- a/mysql-test/r/subquery_sj_none_jcl6.result 2010-09-20 14:06:02 +0000 +++ b/mysql-test/r/subquery_sj_none_jcl6.result 2010-10-13 13:27:36 +0000 @@ -5299,6 +5299,44 @@ a 1 1 DROP TABLE t1,t2,t3; +# +# BUG#52329 - Wrong result: subquery materialization, IN, +# non-null field followed by nullable +# +CREATE TABLE t1 (a1 CHAR(8) NOT NULL, a2 char(8) NOT NULL); +CREATE TABLE t2a (b1 char(8), b2 char(8)); +CREATE TABLE t2b (b1 CHAR(8), b2 char(8) NOT NULL); +CREATE TABLE t2c (b1 CHAR(8) NOT NULL, b2 char(8)); +INSERT INTO t1 VALUES ('1 - 12', '2 - 22'); +INSERT INTO t2a VALUES ('1 - 11', '2 - 21'), +('1 - 11', '2 - 21'), +('1 - 12', '2 - 22'), +('1 - 12', '2 - 22'), +('1 - 13', '2 - 23'); +INSERT INTO t2b SELECT * FROM t2a; +INSERT INTO t2c SELECT * FROM t2a; +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2c WHERE b1 > '0' GROUP BY b1, b2); +a1 a2 +1 - 12 2 - 22 +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2a WHERE b1 > '0'); +a1 a2 +1 - 12 2 - 22 +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2b WHERE b1 > '0'); +a1 a2 +1 - 12 2 - 22 +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2c WHERE b1 > '0'); +a1 a2 +1 - 12 2 - 22 +DROP TABLE t1,t2a,t2b,t2c; +# End BUG#52329 set optimizer_switch=default; set optimizer_join_cache_level=default; show variables like 'optimizer_join_cache_level'; === modified file 'mysql-test/r/subquery_sj_none_jcl7.result' --- a/mysql-test/r/subquery_sj_none_jcl7.result 2010-09-20 14:06:02 +0000 +++ b/mysql-test/r/subquery_sj_none_jcl7.result 2010-10-13 13:27:36 +0000 @@ -5299,6 +5299,44 @@ a 1 1 DROP TABLE t1,t2,t3; +# +# BUG#52329 - Wrong result: subquery materialization, IN, +# non-null field followed by nullable +# +CREATE TABLE t1 (a1 CHAR(8) NOT NULL, a2 char(8) NOT NULL); +CREATE TABLE t2a (b1 char(8), b2 char(8)); +CREATE TABLE t2b (b1 CHAR(8), b2 char(8) NOT NULL); +CREATE TABLE t2c (b1 CHAR(8) NOT NULL, b2 char(8)); +INSERT INTO t1 VALUES ('1 - 12', '2 - 22'); +INSERT INTO t2a VALUES ('1 - 11', '2 - 21'), +('1 - 11', '2 - 21'), +('1 - 12', '2 - 22'), +('1 - 12', '2 - 22'), +('1 - 13', '2 - 23'); +INSERT INTO t2b SELECT * FROM t2a; +INSERT INTO t2c SELECT * FROM t2a; +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2c WHERE b1 > '0' GROUP BY b1, b2); +a1 a2 +1 - 12 2 - 22 +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2a WHERE b1 > '0'); +a1 a2 +1 - 12 2 - 22 +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2b WHERE b1 > '0'); +a1 a2 +1 - 12 2 - 22 +SELECT * FROM t1 +WHERE (a1, a2) IN ( +SELECT b1, b2 FROM t2c WHERE b1 > '0'); +a1 a2 +1 - 12 2 - 22 +DROP TABLE t1,t2a,t2b,t2c; +# End BUG#52329 set optimizer_switch=default; set optimizer_join_cache_level=default; show variables like 'optimizer_join_cache_level'; === modified file 'mysql-test/t/optimizer_switch.test' --- a/mysql-test/t/optimizer_switch.test 2010-08-19 07:10:58 +0000 +++ b/mysql-test/t/optimizer_switch.test 2010-10-15 10:32:50 +0000 @@ -209,3 +209,199 @@ DROP PROCEDURE run_n_times; DROP TABLE it, ot; # End of Bug#50489 + +--echo # +--echo # BUG#31480: Incorrect result for nested subquery when executed via semijoin +--echo # + +CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL); +CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL); +CREATE TABLE t3 (e INT NOT NULL); +CREATE TABLE t4 (f INT NOT NULL, g INT NOT NULL); + +INSERT INTO t1 VALUES (1,10); +INSERT INTO t1 VALUES (2,10); +INSERT INTO t1 VALUES (1,20); +INSERT INTO t1 VALUES (2,20); +INSERT INTO t1 VALUES (3,20); +INSERT INTO t1 VALUES (2,30); +INSERT INTO t1 VALUES (4,40); + +INSERT INTO t2 VALUES (2,10); +INSERT INTO t2 VALUES (2,20); +INSERT INTO t2 VALUES (4,10); +INSERT INTO t2 VALUES (5,10); +INSERT INTO t2 VALUES (3,20); +INSERT INTO t2 VALUES (2,40); + +INSERT INTO t3 VALUES (10); +INSERT INTO t3 VALUES (30); +INSERT INTO t3 VALUES (10); +INSERT INTO t3 VALUES (20); + +INSERT INTO t4 VALUES (2,10); +INSERT INTO t4 VALUES (2,10); +INSERT INTO t4 VALUES (3,10); +INSERT INTO t4 VALUES (4,10); +INSERT INTO t4 VALUES (4,20); +INSERT INTO t4 VALUES (4,20); + +--echo # Reference to the parent query block (used tables was wrong) + +set @@optimizer_switch='materialization=off,semijoin=off'; + +let query= +SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb + WHERE tb.d >= SOME(SELECT e FROM t3 as tc + WHERE ta.b=tc.e)); + +eval EXPLAIN $query; +eval $query; + +set @@optimizer_switch='materialization=off,semijoin=on'; + +eval EXPLAIN $query; +eval $query; + +--echo # Subquery with GROUP BY and HAVING + +set @@optimizer_switch='materialization=off,semijoin=off'; + +let query= +SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb + WHERE tb.d >= SOME(SELECT SUM(g) FROM t4 as tc + GROUP BY f + HAVING ta.a=tc.f)); + +eval EXPLAIN $query; +eval $query; + +set @@optimizer_switch='materialization=off,semijoin=on'; + +--echo # The query result with semijoin is WRONG + +eval EXPLAIN $query; +eval $query; + +--echo # Subquery with ORDER BY and LIMIT + +set @@optimizer_switch='materialization=off,semijoin=off'; + +--echo # NOTE: The ordered subquery should have a LIMIT clause to make sense + +let query= +SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb + WHERE tb.d IN (SELECT g FROM t4 as tc + WHERE ta.a=tc.f + ORDER BY tc.f)); + +eval EXPLAIN $query; +eval $query; + +set @@optimizer_switch='materialization=off,semijoin=on'; + +--echo # The query result with semijoin is WRONG + +eval EXPLAIN $query; +eval $query; + +--echo # Reference to the transformed-away query block (dependency was wrong) + +set @@optimizer_switch='materialization=off,semijoin=off'; + +let query= +SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb + WHERE tb.d >= SOME(SELECT e FROM t3 as tc + WHERE tb.d=tc.e)); + +eval EXPLAIN $query; +eval $query; + +set @@optimizer_switch='materialization=off,semijoin=on'; + +eval EXPLAIN $query; +eval $query; + +--echo # Reference above the parent query block (should not be affected) + +set @@optimizer_switch='materialization=off,semijoin=off'; + +let query= +SELECT * FROM t1 AS t +WHERE t.a NOT IN (SELECT a FROM t1 AS ta + WHERE ta.a IN (SELECT c FROM t2 AS tb + WHERE tb.d >= SOME(SELECT e FROM t3 as tc + WHERE t.b=tc.e))); + +eval EXPLAIN $query; +eval $query; + +set @@optimizer_switch='materialization=off,semijoin=on'; + +eval EXPLAIN $query; +eval $query; + +--echo # EXISTS with reference to the parent query block + +set @@optimizer_switch='materialization=off,semijoin=off'; + +let query= +SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb + WHERE EXISTS (SELECT * FROM t3 as tc + WHERE ta.b=tc.e)); + +eval EXPLAIN $query; +eval $query; + +set @@optimizer_switch='materialization=off,semijoin=on'; + +eval EXPLAIN $query; +eval $query; + +--echo # Scalar subquery with reference to the parent query block + +set @@optimizer_switch='materialization=off,semijoin=off'; + +let query= +SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb + WHERE tb.d = (SELECT MIN(e) FROM t3 as tc + WHERE ta.b=tc.e)); + +eval EXPLAIN $query; +eval $query; + +set @@optimizer_switch='materialization=off,semijoin=on'; + +eval EXPLAIN $query; +eval $query; + +--echo # Combine scalar subquery with quantified comparison subquery + +set @@optimizer_switch='materialization=off,semijoin=off'; + +let query= +SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb + WHERE (SELECT MIN(e) FROM t3 as tc + WHERE tb.d=tc.e) < SOME(SELECT e FROM t3 as tc + WHERE ta.b=tc.e)); + +eval EXPLAIN $query; +eval $query; + +set @@optimizer_switch='materialization=off,semijoin=on'; + +eval EXPLAIN $query; +eval $query; + +DROP TABLE t1, t2, t3, t4; + +set @@optimizer_switch='default'; + +--echo # End of BUG#31480 === modified file 'sql/item.cc' --- a/sql/item.cc 2010-09-28 15:17:29 +0000 +++ b/sql/item.cc 2010-10-15 10:32:50 +0000 @@ -2314,21 +2314,75 @@ table_map Item_field::used_tables() cons } -void Item_field::fix_after_pullout(st_select_lex *new_parent, Item **ref) +table_map Item_field::resolved_used_tables() const { - if (new_parent == depended_from) - depended_from= NULL; - Name_resolution_context *ctx= new Name_resolution_context(); - ctx->outer_context= NULL; // We don't build a complete name resolver - ctx->table_list= NULL; // We rely on first_name_resolution_table instead - ctx->select_lex= new_parent; - ctx->first_name_resolution_table= context->first_name_resolution_table; - ctx->last_name_resolution_table= context->last_name_resolution_table; - ctx->error_processor= context->error_processor; - ctx->error_processor_data= context->error_processor_data; - ctx->resolve_in_select_list= context->resolve_in_select_list; - ctx->security_ctx= context->security_ctx; - this->context=ctx; + if (field->table->const_table) + return 0; // const item + return field->table->map; +} + +void Item_field::fix_after_pullout(st_select_lex *parent_select, + st_select_lex *removed_select, + Item **ref) +{ + if (context->select_lex == removed_select || + context->select_lex == parent_select) + { + if (parent_select == depended_from) + depended_from= NULL; + Name_resolution_context *ctx= new Name_resolution_context(); + ctx->outer_context= NULL; // We don't build a complete name resolver + ctx->table_list= NULL; // We rely on first_name_resolution_table instead + ctx->select_lex= parent_select; + ctx->first_name_resolution_table= context->first_name_resolution_table; + ctx->last_name_resolution_table= context->last_name_resolution_table; + ctx->error_processor= context->error_processor; + ctx->error_processor_data= context->error_processor_data; + ctx->resolve_in_select_list= context->resolve_in_select_list; + ctx->security_ctx= context->security_ctx; + this->context=ctx; + } + else + { + /* + The definition scope of this field item reference is inner to the removed + select_lex object. + No new resolution is needed, but we may need to update the dependency. + */ + if (removed_select == depended_from) + depended_from= parent_select; + } + + if (depended_from) + { + /* + Refresh used_tables information for subqueries between the definition + scope and resolution scope of the field item reference. + */ + st_select_lex *child_select= context->select_lex; + + if (child_select->outer_select() != depended_from) + { + /* + The subquery on this level is outer-correlated with respect to the field + */ + Item_subselect *subq_predicate= child_select->master_unit()->item; + subq_predicate->used_tables_cache|= OUTER_REF_TABLE_BIT; + } + + while (child_select->outer_select() != depended_from) + child_select= child_select->outer_select(); + + /* + child_select is select_lex immediately inner to the depended_from level. + Now, locate the subquery predicate that contains this select_lex and + update used tables information. + */ + Item_subselect *subq_predicate= child_select->master_unit()->item; + + subq_predicate->used_tables_cache|= this->resolved_used_tables(); + subq_predicate->const_item_cache&= this->const_item(); + } } @@ -6804,26 +6858,31 @@ bool Item_outer_ref::fix_fields(THD *thd return err; } -void Item_outer_ref::fix_after_pullout(st_select_lex *new_parent, Item **ref) +void Item_outer_ref::fix_after_pullout(st_select_lex *parent_select, + st_select_lex *removed_select, + Item **ref_arg) { - if (depended_from == new_parent) + if (depended_from == parent_select) { - *ref= outer_ref; - outer_ref->fix_after_pullout(new_parent, ref); + *ref_arg= outer_ref; + outer_ref->fix_after_pullout(parent_select, removed_select, ref_arg); } // @todo: Find an actual test case for this funcion. DBUG_ASSERT(false); } -void Item_ref::fix_after_pullout(st_select_lex *new_parent, Item **refptr) +void Item_ref::fix_after_pullout(st_select_lex *parent_select, + st_select_lex *removed_select, + Item **ref_arg) { // @todo: Find an actual test case where depended_from == new_parent. - DBUG_ASSERT(depended_from != new_parent); - if (depended_from == new_parent) + DBUG_ASSERT(depended_from != parent_select); + if (depended_from == parent_select) depended_from= NULL; } -void Item_direct_view_ref::fix_after_pullout(st_select_lex *new_parent, +void Item_direct_view_ref::fix_after_pullout(st_select_lex *parent_select, + st_select_lex *removed_select, Item **refptr) { DBUG_EXECUTE("where", @@ -6831,11 +6890,11 @@ void Item_direct_view_ref::fix_after_pul "Item_direct_view_ref::fix_after_pullout", QT_ORDINARY);); - (*ref)->fix_after_pullout(new_parent, ref); + (*ref)->fix_after_pullout(parent_select, removed_select, ref); - // @todo: Find an actual test case where depended_from == new_parent. - DBUG_ASSERT(depended_from != new_parent); - if (depended_from == new_parent) + // @todo: Find an actual test case where depended_from == parent_select. + DBUG_ASSERT(depended_from != parent_select); + if (depended_from == parent_select) depended_from= NULL; } === modified file 'sql/item.h' --- a/sql/item.h 2010-09-07 19:07:18 +0000 +++ b/sql/item.h 2010-10-15 10:32:50 +0000 @@ -592,12 +592,20 @@ public: virtual void make_field(Send_field *field); Field *make_string_field(TABLE *table); virtual bool fix_fields(THD *, Item **); - /* - Fix after some tables has been pulled out. Basically re-calculate all - attributes that are dependent on the tables. - */ - virtual void fix_after_pullout(st_select_lex *new_parent, Item **ref) {}; - + /** + Fix after tables have been moved from one select_lex level to the parent + level, e.g by semijoin conversion. + Basically re-calculate all attributes dependent on the tables. + + @param parent_select select_lex that tables are moved to. + @param removed_select select_lex that tables are moved away from, + child of parent_select. + @param ref updated with new ref whenever the function substitutes + this item with another. + */ + virtual void fix_after_pullout(st_select_lex *parent_select, + st_select_lex *removed_select, + Item **ref) {}; /* should be used in case where we are sure that we do not need complete fix_fields() procedure. @@ -1753,11 +1761,16 @@ public: bool send(Protocol *protocol, String *str_arg); void reset_field(Field *f); bool fix_fields(THD *, Item **); - void fix_after_pullout(st_select_lex *new_parent, Item **ref); + void fix_after_pullout(st_select_lex *parent_select, + st_select_lex *removed_select, Item **ref); void make_field(Send_field *tmp_field); int save_in_field(Field *field,bool no_conversions); void save_org_in_field(Field *field); table_map used_tables() const; + /* + Return used table information for the level on which this table is resolved. + */ + table_map resolved_used_tables() const; enum Item_result result_type () const { return field->result_type(); @@ -2573,7 +2586,8 @@ public: bool send(Protocol *prot, String *tmp); void make_field(Send_field *field); bool fix_fields(THD *, Item **); - void fix_after_pullout(st_select_lex *new_parent, Item **ref); + void fix_after_pullout(st_select_lex *parent_select, + st_select_lex *removed_select, Item **ref); int save_in_field(Field *field, bool no_conversions); void save_org_in_field(Field *field); enum Item_result result_type () const { return (*ref)->result_type(); } @@ -2712,7 +2726,8 @@ public: {} bool fix_fields(THD *, Item **); - void fix_after_pullout(st_select_lex *new_parent, Item **ref); + void fix_after_pullout(st_select_lex *parent_select, + st_select_lex *removed_select, Item **ref); bool eq(const Item *item, bool binary_cmp) const; Item *get_tmp_table_item(THD *thd) { @@ -2769,7 +2784,8 @@ public: outer_ref->save_org_in_field(result_field); } bool fix_fields(THD *, Item **); - void fix_after_pullout(st_select_lex *new_parent, Item **ref); + void fix_after_pullout(st_select_lex *parent_select, + st_select_lex *removed_select, Item **ref); table_map used_tables() const { return (*ref)->const_item() ? 0 : OUTER_REF_TABLE_BIT; === modified file 'sql/item_cmpfunc.cc' --- a/sql/item_cmpfunc.cc 2010-09-28 15:17:29 +0000 +++ b/sql/item_cmpfunc.cc 2010-10-15 10:32:50 +0000 @@ -1770,6 +1770,27 @@ bool Item_in_optimizer::fix_fields(THD * } +void Item_in_optimizer::fix_after_pullout(st_select_lex *parent_select, + st_select_lex *removed_select, + Item **ref) +{ + used_tables_cache=0; + not_null_tables_cache= 0; + const_item_cache= 1; + + /* + No need to call fix_after_pullout() on args[0] (ie left expression), + as Item_in_subselect::fix_after_pullout() will do this. + So, just forward the call to the Item_in_subselect object. + */ + + args[1]->fix_after_pullout(parent_select, removed_select, &args[1]); + + used_tables_cache|= args[1]->used_tables(); + not_null_tables_cache|= args[1]->not_null_tables(); + const_item_cache&= args[1]->const_item(); +} + /** The implementation of optimized \ [NOT] IN \ predicates. The implementation works as follows. @@ -1840,6 +1861,7 @@ bool Item_in_optimizer::fix_fields(THD * @see Item_in_subselect::val_bool() @see Item_is_not_null_test::val_int() */ + longlong Item_in_optimizer::val_int() { bool tmp; @@ -4337,7 +4359,9 @@ Item_cond::fix_fields(THD *thd, Item **r } -void Item_cond::fix_after_pullout(st_select_lex *new_parent, Item **ref) +void Item_cond::fix_after_pullout(st_select_lex *parent_select, + st_select_lex *removed_select, + Item **ref) { List_iterator li(list); Item *item; @@ -4351,7 +4375,7 @@ void Item_cond::fix_after_pullout(st_sel while ((item=li++)) { table_map tmp_table_map; - item->fix_after_pullout(new_parent, li.ref()); + item->fix_after_pullout(parent_select, removed_select, li.ref()); item= *li.ref(); used_tables_cache|= item->used_tables(); const_item_cache&= item->const_item(); === modified file 'sql/item_cmpfunc.h' --- a/sql/item_cmpfunc.h 2010-08-12 00:26:10 +0000 +++ b/sql/item_cmpfunc.h 2010-10-15 10:32:50 +0000 @@ -274,6 +274,8 @@ public: { with_subselect= TRUE; } bool fix_fields(THD *, Item **); bool fix_left(THD *thd, Item **ref); + void fix_after_pullout(st_select_lex *parent_select, + st_select_lex *removed_select, Item **ref); bool is_null(); longlong val_int(); void cleanup(); @@ -1513,7 +1515,8 @@ public: list.prepand(nlist); } bool fix_fields(THD *, Item **ref); - void fix_after_pullout(st_select_lex *new_parent, Item **ref); + void fix_after_pullout(st_select_lex *parent_select, + st_select_lex *removed_select, Item **ref); enum Type type() const { return COND_ITEM; } List* argument_list() { return &list; } === modified file 'sql/item_func.cc' --- a/sql/item_func.cc 2010-09-06 11:10:01 +0000 +++ b/sql/item_func.cc 2010-10-22 13:33:24 +0000 @@ -226,7 +226,9 @@ Item_func::fix_fields(THD *thd, Item **r } -void Item_func::fix_after_pullout(st_select_lex *new_parent, Item **ref) +void Item_func::fix_after_pullout(st_select_lex *parent_select, + st_select_lex *removed_select, + Item **ref) { Item **arg,**arg_end; @@ -237,7 +239,7 @@ void Item_func::fix_after_pullout(st_sel { for (arg=args, arg_end=args+arg_count; arg != arg_end ; arg++) { - (*arg)->fix_after_pullout(new_parent, arg); + (*arg)->fix_after_pullout(parent_select, removed_select, arg); Item *item= *arg; used_tables_cache|= item->used_tables(); === modified file 'sql/item_func.h' --- a/sql/item_func.h 2010-07-13 17:29:44 +0000 +++ b/sql/item_func.h 2010-10-15 10:32:50 +0000 @@ -120,7 +120,8 @@ public: // Constructor used for Item_cond_and/or (see Item comment) Item_func(THD *thd, Item_func *item); bool fix_fields(THD *, Item **ref); - void fix_after_pullout(st_select_lex *new_parent, Item **ref); + void fix_after_pullout(st_select_lex *parent_select, + st_select_lex *removed_select, Item **ref); table_map used_tables() const; table_map not_null_tables() const; void update_used_tables(); === modified file 'sql/item_row.cc' --- a/sql/item_row.cc 2010-07-13 17:29:44 +0000 +++ b/sql/item_row.cc 2010-10-15 10:32:50 +0000 @@ -138,13 +138,15 @@ void Item_row::update_used_tables() } } -void Item_row::fix_after_pullout(st_select_lex *new_parent, Item **ref) +void Item_row::fix_after_pullout(st_select_lex *parent_select, + st_select_lex *removed_select, + Item **ref) { used_tables_cache= 0; const_item_cache= 1; for (uint i= 0; i < arg_count; i++) { - items[i]->fix_after_pullout(new_parent, &items[i]); + items[i]->fix_after_pullout(parent_select, removed_select, &items[i]); used_tables_cache|= items[i]->used_tables(); const_item_cache&= items[i]->const_item(); } === modified file 'sql/item_row.h' --- a/sql/item_row.h 2010-07-13 17:29:44 +0000 +++ b/sql/item_row.h 2010-10-15 10:32:50 +0000 @@ -63,7 +63,8 @@ public: return 0; }; bool fix_fields(THD *thd, Item **ref); - void fix_after_pullout(st_select_lex *new_parent, Item **ref); + void fix_after_pullout(st_select_lex *parent_select, + st_select_lex *removed_select, Item **ref); void cleanup(); void split_sum_func(THD *thd, Item **ref_pointer_array, List &fields); table_map used_tables() const { return used_tables_cache; }; === modified file 'sql/item_subselect.cc' --- a/sql/item_subselect.cc 2010-09-28 15:17:29 +0000 +++ b/sql/item_subselect.cc 2010-10-15 10:32:50 +0000 @@ -319,6 +319,69 @@ bool Item_subselect::exec() } +/** + Fix used tables information for a subquery after query transformations. + Common actions for all predicates involving subqueries. + Most actions here involve re-resolving information for conditions + and items belonging to the subquery. + Notice that the usage information from underlying expressions is not + propagated to the subquery predicate, as it belongs to inner layers + of the query operator structure. + However, when underlying expressions contain outer references into + a select_lex on this level, the relevant information must be updated + when these expressions are resolved. +*/ + +void Item_subselect::fix_after_pullout(st_select_lex *parent_select, + st_select_lex *removed_select, + Item **ref) + +{ + /* Clear usage information for this subquery predicate object */ + used_tables_cache= 0; + const_item_cache= 1; + + /* + Go through all query specification objects of the subquery and re-resolve + all relevant expressions belonging to them. + */ + for (SELECT_LEX *sel= unit->first_select(); sel; sel= sel->next_select()) + { + if (sel->where) + sel->where->fix_after_pullout(parent_select, removed_select, + &sel->where); + + if (sel->having) + sel->having->fix_after_pullout(parent_select, removed_select, + &sel->having); + + List_iterator li(sel->item_list); + Item *item; + while ((item=li++)) + item->fix_after_pullout(parent_select, removed_select, li.ref()); + + /* + No need to call fix_after_pullout() for outer-join conditions, as these + cannot have outer references. + */ + + /* Re-resolve ORDER BY and GROUP BY fields */ + + for (ORDER *order= (ORDER*) sel->order_list.first; + order; + order= order->next) + (*order->item)->fix_after_pullout(parent_select, removed_select, + order->item); + + for (ORDER *group= (ORDER*) sel->group_list.first; + group; + group= group->next) + (*group->item)->fix_after_pullout(parent_select, removed_select, + group->item); + } +} + + /* Compute the IN predicate if the left operand's cache changed. */ @@ -1820,6 +1883,19 @@ bool Item_in_subselect::fix_fields(THD * } +void Item_in_subselect::fix_after_pullout(st_select_lex *parent_select, + st_select_lex *removed_select, + Item **ref) +{ + Item_subselect::fix_after_pullout(parent_select, removed_select, ref); + + left_expr->fix_after_pullout(parent_select, removed_select, &left_expr); + + used_tables_cache|= left_expr->used_tables(); + const_item_cache&= left_expr->const_item(); +} + + /** Try to create an engine to compute the subselect via materialization, and if this fails, revert to execution via the IN=>EXISTS transformation. @@ -3214,7 +3290,7 @@ bool subselect_hash_sj_engine::init_perm use that information instead. */ cur_ref_buff + null_count, - null_count ? tab->ref.key_buff : 0, + null_count ? cur_ref_buff : 0, cur_key_part->length, tab->ref.items[i]); cur_ref_buff+= cur_key_part->store_length; } === modified file 'sql/item_subselect.h' --- a/sql/item_subselect.h 2010-07-26 11:34:07 +0000 +++ b/sql/item_subselect.h 2010-10-15 10:32:50 +0000 @@ -124,6 +124,8 @@ public: return null_value; } bool fix_fields(THD *thd, Item **ref); + void fix_after_pullout(st_select_lex *parent_select, + st_select_lex *removed_select, Item **ref); virtual bool exec(); virtual void fix_length_and_dec(); table_map used_tables() const; @@ -170,6 +172,9 @@ public: friend bool Item_field::fix_fields(THD *, Item **); friend int Item_field::fix_outer_field(THD *, Field **, Item **); friend bool Item_ref::fix_fields(THD *, Item **); + friend void Item_field::fix_after_pullout(st_select_lex *parent_select, + st_select_lex *removed_select, + Item **ref); friend void mark_select_range_as_dependent(THD*, st_select_lex*, st_select_lex*, Field*, Item*, Item_ident*); @@ -403,6 +408,8 @@ public: bool test_limit(st_select_lex_unit *unit); virtual void print(String *str, enum_query_type query_type); bool fix_fields(THD *thd, Item **ref); + void fix_after_pullout(st_select_lex *parent_select, + st_select_lex *removed_select, Item **ref); bool setup_engine(); bool init_left_expr_cache(); bool is_expensive_processor(uchar *arg); === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2010-09-30 14:53:11 +0000 +++ b/sql/sql_select.cc 2010-10-21 15:44:35 +0000 @@ -3548,16 +3548,20 @@ static TABLE_LIST *alloc_join_nest(THD * } -void fix_list_after_tbl_changes(SELECT_LEX *new_parent, List *tlist) +void fix_list_after_tbl_changes(st_select_lex *parent_select, + st_select_lex *removed_select, + List *tlist) { List_iterator it(*tlist); TABLE_LIST *table; while ((table= it++)) { if (table->on_expr) - table->on_expr->fix_after_pullout(new_parent, &table->on_expr); + table->on_expr->fix_after_pullout(parent_select, removed_select, + &table->on_expr); if (table->nested_join) - fix_list_after_tbl_changes(new_parent, &table->nested_join->join_list); + fix_list_after_tbl_changes(parent_select, removed_select, + &table->nested_join->join_list); } } @@ -3870,15 +3874,16 @@ bool convert_subquery_to_semijoin(JOIN * sj_nest->sj_on_expr->fix_fields(thd, &sj_nest->sj_on_expr); } + /* Unlink the child select_lex: */ + subq_lex->master_unit()->exclude_level(); /* Walk through sj nest's WHERE and ON expressions and call item->fix_table_changes() for all items. */ - sj_nest->sj_on_expr->fix_after_pullout(parent_lex, &sj_nest->sj_on_expr); - fix_list_after_tbl_changes(parent_lex, &nested_join->join_list); - - /* Unlink the child select_lex so it doesn't show up in EXPLAIN: */ - subq_lex->master_unit()->exclude_level(); + sj_nest->sj_on_expr->fix_after_pullout(parent_lex, subq_lex, + &sj_nest->sj_on_expr); + fix_list_after_tbl_changes(parent_lex, subq_lex, + &sj_nest->nested_join->join_list); //TODO fix QT_ DBUG_EXECUTE("where", @@ -7627,6 +7632,12 @@ optimize_straight_join(JOIN *join, table for (JOIN_TAB **pos= join->best_ref + idx ; (s= *pos) ; pos++) { + /* + Dependency computation (make_join_statistics()) and proper ordering + based on them (join_tab_cmp*) guarantee that this order is compatible + with execution, check it: + */ + DBUG_ASSERT(!check_interleaving_with_nj(s)); /* Find the best access method from 's' to the current partial plan */ best_access_path(join, s, join_tables, idx, FALSE, record_count, join->positions + idx, &loose_scan_pos); @@ -10722,7 +10733,7 @@ bool setup_sj_materialization(JOIN_TAB * use that information instead. */ cur_ref_buff + null_count, - null_count ? tab_ref->key_buff : 0, + null_count ? cur_ref_buff : 0, cur_key_part->length, tab_ref->items[i]); cur_ref_buff+= cur_key_part->store_length; } No bundle (reason: useless for push emails).