From: Tor Didriksen Date: January 17 2012 3:44pm Subject: bzr push into mysql-trunk branch (tor.didriksen:3741 to 3742) List-Archive: http://lists.mysql.com/commits/142438 Message-Id: <201201171544.q0HFi3Uw025983@acsmt356.oracle.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 3742 Tor Didriksen 2012-01-17 [merge] merge opt-team => trunk modified: mysql-test/include/subquery_mat.inc mysql-test/include/subquery_sj.inc mysql-test/r/group_by.result mysql-test/r/subquery_mat.result mysql-test/r/subquery_mat_all.result mysql-test/r/subquery_mat_none.result mysql-test/r/subquery_sj_all.result mysql-test/r/subquery_sj_all_bka.result mysql-test/r/subquery_sj_all_bka_nixbnl.result mysql-test/r/subquery_sj_all_bkaunique.result mysql-test/r/subquery_sj_dupsweed.result mysql-test/r/subquery_sj_dupsweed_bka.result mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result mysql-test/r/subquery_sj_dupsweed_bkaunique.result mysql-test/r/subquery_sj_firstmatch.result mysql-test/r/subquery_sj_firstmatch_bka.result mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result mysql-test/r/subquery_sj_firstmatch_bkaunique.result mysql-test/r/subquery_sj_loosescan.result mysql-test/r/subquery_sj_loosescan_bka.result mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result mysql-test/r/subquery_sj_loosescan_bkaunique.result mysql-test/r/subquery_sj_mat.result mysql-test/r/subquery_sj_mat_bka.result mysql-test/r/subquery_sj_mat_bka_nixbnl.result mysql-test/r/subquery_sj_mat_bkaunique.result mysql-test/r/subquery_sj_mat_nosj.result mysql-test/r/subquery_sj_none.result mysql-test/r/subquery_sj_none_bka.result mysql-test/r/subquery_sj_none_bka_nixbnl.result mysql-test/r/subquery_sj_none_bkaunique.result mysql-test/t/group_by.test sql/item_subselect.cc sql/sql_base.cc sql/sql_executor.cc sql/sql_optimizer.cc sql/sql_planner.cc sql/sql_select.h 3741 Norvald H. Ryeng 2012-01-17 Bug#11764371 57196: MORE FUN WITH ASSERTION: !TABLE->FILE || TABLE->FILE->INITED == HANDLER:: Problem: An assertion is triggered by queries that assign the result of a subquery to a user variable when the outer query is doing DISTINCT and GROUP BY. Because of the DISTINCT and GROUP BY, the query is executed using two temporary tables. The first temporary table is filled by executing the query over the base tables. The subquery is also executed, and the result is stored in the user variable. After this execution, join_free() is called to free data from the old join and release all read locks since the next execution is based on the temporary table. The second round of execution is over the first temporary table, but because of the user variable assignment, the subquery is re-executed over the base tables. Since these tables are now unlocked, an assertion guarding handler::ha_rnd_next() against reading from unlocked tables is triggered. Fix: In change_to_use_tmp_field(), if the item is an Item_func_set_user_var that stores the value of a subquery, replace it with a cloned Item_func_set_user_var that stores the result of an Item_field that contains the result of the subquery. This makes the next execution round use the stored value instead of re-executing the subquery. This patch also fixes bug #13260504. No additional testcase required. @ mysql-test/r/user_var.result Test case for bug #11764371. @ mysql-test/t/user_var.test Test case for bug #11764371. @ sql/item_func.h Add constructors. @ sql/sql_executor.cc Change user variable assignment functions to read from fields after tables have been unlocked. modified: mysql-test/r/user_var.result mysql-test/t/user_var.test sql/item_func.h sql/sql_executor.cc === modified file 'mysql-test/include/subquery_mat.inc' --- a/mysql-test/include/subquery_mat.inc 2012-01-09 14:19:49 +0000 +++ b/mysql-test/include/subquery_mat.inc 2012-01-16 14:00:28 +0000 @@ -961,3 +961,23 @@ eval $query; DROP TABLE t1, t2, t3; --echo # End of test for bug#13552968 + +--echo # +--echo # Bug#13591383: Assertion !(*tab->on_expr_ref && .. && is_expensive()) +--echo # in join_read_const_table() +--echo # + +CREATE TABLE t1 (v INTEGER) ENGINE=MyISAM; +INSERT INTO t1 VALUES(1); + +CREATE TABLE t2 (v INTEGER) ENGINE=MyISAM; + +SELECT * +FROM t1 LEFT JOIN t2 + ON t2.v IN(SELECT v FROM t1); + +DROP TABLE t1, t2; + +--echo # End of test for bug#13591383. + +--echo # End of 5.6 tests === modified file 'mysql-test/include/subquery_sj.inc' --- a/mysql-test/include/subquery_sj.inc 2012-01-12 14:53:51 +0000 +++ b/mysql-test/include/subquery_sj.inc 2012-01-16 12:51:06 +0000 @@ -4772,4 +4772,51 @@ DROP TABLE t1, t2; --echo # End of test for bug#13576391. +--echo # +--echo # Bug #13589848 "MISSING ROW ON SELECT WITH NESTED IN CLAUSES WHEN LOOSESCAN=ON" +--echo # +CREATE TABLE t1 ( + id INT, + col_varchar_key VARCHAR(1), + col_varchar_nokey VARCHAR(1), + KEY (col_varchar_key) +); + +INSERT INTO t1 VALUES (100,'m','m'), +(200,'b','b'), (300,'x','x'); + +CREATE TABLE t2 ( + col_varchar_key VARCHAR(1), + col_varchar_nokey VARCHAR(1), + KEY (col_varchar_key) +); + +INSERT INTO t2 VALUES ('b','b'); + +CREATE TABLE t3 ( + col_varchar_key VARCHAR(1), + col_varchar_nokey VARCHAR(1), + KEY (col_varchar_key) +); + +INSERT INTO t3 VALUES ('k','k'); + +let $query=SELECT GP1.id +FROM t1 AS GP1 JOIN t3 AS GP2 + ON GP2.col_varchar_key <> GP1.col_varchar_nokey +WHERE (GP1.col_varchar_nokey, GP1.col_varchar_nokey) + IN ( + SELECT col_varchar_nokey, col_varchar_nokey + FROM t1 + WHERE col_varchar_nokey + IN ( SELECT col_varchar_key + FROM t2 LEFT JOIN t3 USING (col_varchar_key) ) + ) +; + +eval EXPLAIN $query; +eval $query; + +DROP TABLE t1,t2,t3; + --echo # End of 5.6 tests === modified file 'mysql-test/r/group_by.result' --- a/mysql-test/r/group_by.result 2012-01-13 09:33:13 +0000 +++ b/mysql-test/r/group_by.result 2012-01-17 11:35:06 +0000 @@ -2402,3 +2402,61 @@ Note 1276 Field or reference 'test.t1.pk Note 1276 Field or reference 'test.t1.pk' of SELECT #3 was resolved in SELECT #1 Note 1003 /* select#1 */ select `t1`.`pk` AS `foo`,`t1`.`col_int_key` AS `foo`,(/* select#2 */ select `test`.`t2`.`a` from `test`.`t2` where (`test`.`t2`.`a` = `t1`.`pk`)) AS `foo` from `test`.`t1` group by `t1`.`pk`,`t1`.`col_int_key`,(/* select#3 */ select `test`.`t2`.`a` from `test`.`t2` where (`test`.`t2`.`a` = `t1`.`pk`)) order by `t1`.`pk`,`t1`.`col_int_key`,(/* select#4 */ select `test`.`t2`.`a` from `test`.`t2` where (`test`.`t2`.`a` = `t1`.`pk`)) DROP TABLE t1,t2; +# +# Bug#13591138 - ASSERTION NAME && !IS_AUTOGENERATED_NAME IN +# ITEM::PRINT_FOR_ORDER ON EXPLAIN EXT +# +CREATE TABLE t1 ( +pk int(11) NOT NULL AUTO_INCREMENT, +col_datetime_key datetime NOT NULL, +col_varchar_key varchar(1) NOT NULL, +PRIMARY KEY (pk), +KEY col_datetime_key (col_datetime_key), +KEY col_varchar_key (col_varchar_key) +); +CREATE TABLE t2 ( +pk int(11) NOT NULL AUTO_INCREMENT, +PRIMARY KEY (pk) +); +CREATE TABLE t3 ( +pk int(11) NOT NULL AUTO_INCREMENT, +col_varchar_key varchar(1) NOT NULL, +PRIMARY KEY (pk), +KEY col_varchar_key (col_varchar_key) +); +CREATE VIEW view1 AS SELECT * FROM t1; +EXPLAIN EXTENDED +SELECT +alias1.col_datetime_key AS field1 +FROM ( +view1 AS alias1, +t3 AS alias2 +) +WHERE ( +(SELECT MIN(SQ1_alias1.pk) +FROM t2 AS SQ1_alias1 +) +) OR (alias1.col_varchar_key = alias2.col_varchar_key +AND alias1.col_varchar_key = 'j' +) AND alias1.pk IS NULL +GROUP BY +field1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No matching min/max row +Warnings: +Note 1003 /* select#1 */ select NULL AS `field1` from `test`.`t1` join `test`.`t3` `alias2` where 0 group by `field1` +DROP TABLE t1,t2,t3; +DROP VIEW view1; +CREATE TABLE t1 (col_varchar_nokey varchar(1) DEFAULT NULL); +INSERT INTO t1 VALUES ('v'),('c'); +EXPLAIN EXTENDED SELECT (SELECT 150) AS field5 +FROM (SELECT * FROM t1) AS alias1 +GROUP BY field5; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY ALL NULL NULL NULL NULL 2 100.00 +3 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 +Warnings: +Note 1249 Select 2 was reduced during optimization +Note 1003 /* select#1 */ select 150 AS `field5` from (/* select#3 */ select `test`.`t1`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t1`) `alias1` group by `field5` +DROP TABLE t1; === modified file 'mysql-test/r/subquery_mat.result' --- a/mysql-test/r/subquery_mat.result 2012-01-09 14:19:49 +0000 +++ b/mysql-test/r/subquery_mat.result 2012-01-16 14:00:28 +0000 @@ -1232,4 +1232,19 @@ col_varchar_nokey col_varchar_nokey b NULL DROP TABLE t1, t2, t3; # End of test for bug#13552968 +# +# Bug#13591383: Assertion !(*tab->on_expr_ref && .. && is_expensive()) +# in join_read_const_table() +# +CREATE TABLE t1 (v INTEGER) ENGINE=MyISAM; +INSERT INTO t1 VALUES(1); +CREATE TABLE t2 (v INTEGER) ENGINE=MyISAM; +SELECT * +FROM t1 LEFT JOIN t2 +ON t2.v IN(SELECT v FROM t1); +v v +1 NULL +DROP TABLE t1, t2; +# End of test for bug#13591383. +# End of 5.6 tests set optimizer_switch=default; === modified file 'mysql-test/r/subquery_mat_all.result' --- a/mysql-test/r/subquery_mat_all.result 2012-01-09 14:19:49 +0000 +++ b/mysql-test/r/subquery_mat_all.result 2012-01-16 14:00:28 +0000 @@ -1233,4 +1233,19 @@ col_varchar_nokey col_varchar_nokey b NULL DROP TABLE t1, t2, t3; # End of test for bug#13552968 +# +# Bug#13591383: Assertion !(*tab->on_expr_ref && .. && is_expensive()) +# in join_read_const_table() +# +CREATE TABLE t1 (v INTEGER) ENGINE=MyISAM; +INSERT INTO t1 VALUES(1); +CREATE TABLE t2 (v INTEGER) ENGINE=MyISAM; +SELECT * +FROM t1 LEFT JOIN t2 +ON t2.v IN(SELECT v FROM t1); +v v +1 NULL +DROP TABLE t1, t2; +# End of test for bug#13591383. +# End of 5.6 tests set optimizer_switch=default; === modified file 'mysql-test/r/subquery_mat_none.result' --- a/mysql-test/r/subquery_mat_none.result 2012-01-09 14:19:49 +0000 +++ b/mysql-test/r/subquery_mat_none.result 2012-01-16 14:00:28 +0000 @@ -1230,4 +1230,19 @@ col_varchar_nokey col_varchar_nokey b NULL DROP TABLE t1, t2, t3; # End of test for bug#13552968 +# +# Bug#13591383: Assertion !(*tab->on_expr_ref && .. && is_expensive()) +# in join_read_const_table() +# +CREATE TABLE t1 (v INTEGER) ENGINE=MyISAM; +INSERT INTO t1 VALUES(1); +CREATE TABLE t2 (v INTEGER) ENGINE=MyISAM; +SELECT * +FROM t1 LEFT JOIN t2 +ON t2.v IN(SELECT v FROM t1); +v v +1 NULL +DROP TABLE t1, t2; +# End of test for bug#13591383. +# End of 5.6 tests set optimizer_switch=default; === modified file 'mysql-test/r/subquery_sj_all.result' --- a/mysql-test/r/subquery_sj_all.result 2012-01-12 14:53:51 +0000 +++ b/mysql-test/r/subquery_sj_all.result 2012-01-16 12:51:06 +0000 @@ -7570,5 +7570,61 @@ w y DROP TABLE t1, t2; # End of test for bug#13576391. +# +# Bug #13589848 "MISSING ROW ON SELECT WITH NESTED IN CLAUSES WHEN LOOSESCAN=ON" +# +CREATE TABLE t1 ( +id INT, +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t1 VALUES (100,'m','m'), +(200,'b','b'), (300,'x','x'); +CREATE TABLE t2 ( +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t2 VALUES ('b','b'); +CREATE TABLE t3 ( +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t3 VALUES ('k','k'); +EXPLAIN SELECT GP1.id +FROM t1 AS GP1 JOIN t3 AS GP2 +ON GP2.col_varchar_key <> GP1.col_varchar_nokey +WHERE (GP1.col_varchar_nokey, GP1.col_varchar_nokey) +IN ( +SELECT col_varchar_nokey, col_varchar_nokey +FROM t1 +WHERE col_varchar_nokey +IN ( SELECT col_varchar_key +FROM t2 LEFT JOIN t3 USING (col_varchar_key) ) +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY GP2 system NULL NULL NULL NULL 1 +1 PRIMARY GP1 ALL NULL NULL NULL NULL 3 Using where +2 DEPENDENT SUBQUERY t2 index col_varchar_key col_varchar_key 4 NULL 1 Using index; LooseScan +2 DEPENDENT SUBQUERY t3 ref col_varchar_key col_varchar_key 4 test.t2.col_varchar_key 2 Using index; FirstMatch(t2) +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (Block Nested Loop) +SELECT GP1.id +FROM t1 AS GP1 JOIN t3 AS GP2 +ON GP2.col_varchar_key <> GP1.col_varchar_nokey +WHERE (GP1.col_varchar_nokey, GP1.col_varchar_nokey) +IN ( +SELECT col_varchar_nokey, col_varchar_nokey +FROM t1 +WHERE col_varchar_nokey +IN ( SELECT col_varchar_key +FROM t2 LEFT JOIN t3 USING (col_varchar_key) ) +) +; +id +200 +DROP TABLE t1,t2,t3; # End of 5.6 tests set optimizer_switch=default; === modified file 'mysql-test/r/subquery_sj_all_bka.result' --- a/mysql-test/r/subquery_sj_all_bka.result 2012-01-12 14:53:51 +0000 +++ b/mysql-test/r/subquery_sj_all_bka.result 2012-01-16 12:51:06 +0000 @@ -7571,6 +7571,62 @@ w y DROP TABLE t1, t2; # End of test for bug#13576391. +# +# Bug #13589848 "MISSING ROW ON SELECT WITH NESTED IN CLAUSES WHEN LOOSESCAN=ON" +# +CREATE TABLE t1 ( +id INT, +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t1 VALUES (100,'m','m'), +(200,'b','b'), (300,'x','x'); +CREATE TABLE t2 ( +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t2 VALUES ('b','b'); +CREATE TABLE t3 ( +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t3 VALUES ('k','k'); +EXPLAIN SELECT GP1.id +FROM t1 AS GP1 JOIN t3 AS GP2 +ON GP2.col_varchar_key <> GP1.col_varchar_nokey +WHERE (GP1.col_varchar_nokey, GP1.col_varchar_nokey) +IN ( +SELECT col_varchar_nokey, col_varchar_nokey +FROM t1 +WHERE col_varchar_nokey +IN ( SELECT col_varchar_key +FROM t2 LEFT JOIN t3 USING (col_varchar_key) ) +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY GP2 system NULL NULL NULL NULL 1 +1 PRIMARY GP1 ALL NULL NULL NULL NULL 3 Using where +2 DEPENDENT SUBQUERY t2 index col_varchar_key col_varchar_key 4 NULL 1 Using index; LooseScan +2 DEPENDENT SUBQUERY t3 ref col_varchar_key col_varchar_key 4 test.t2.col_varchar_key 2 Using index; FirstMatch(t2) +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (Block Nested Loop) +SELECT GP1.id +FROM t1 AS GP1 JOIN t3 AS GP2 +ON GP2.col_varchar_key <> GP1.col_varchar_nokey +WHERE (GP1.col_varchar_nokey, GP1.col_varchar_nokey) +IN ( +SELECT col_varchar_nokey, col_varchar_nokey +FROM t1 +WHERE col_varchar_nokey +IN ( SELECT col_varchar_key +FROM t2 LEFT JOIN t3 USING (col_varchar_key) ) +) +; +id +200 +DROP TABLE t1,t2,t3; # End of 5.6 tests set optimizer_switch=default; set optimizer_switch=default; === modified file 'mysql-test/r/subquery_sj_all_bka_nixbnl.result' --- a/mysql-test/r/subquery_sj_all_bka_nixbnl.result 2012-01-12 14:53:51 +0000 +++ b/mysql-test/r/subquery_sj_all_bka_nixbnl.result 2012-01-16 12:51:06 +0000 @@ -7571,6 +7571,62 @@ w y DROP TABLE t1, t2; # End of test for bug#13576391. +# +# Bug #13589848 "MISSING ROW ON SELECT WITH NESTED IN CLAUSES WHEN LOOSESCAN=ON" +# +CREATE TABLE t1 ( +id INT, +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t1 VALUES (100,'m','m'), +(200,'b','b'), (300,'x','x'); +CREATE TABLE t2 ( +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t2 VALUES ('b','b'); +CREATE TABLE t3 ( +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t3 VALUES ('k','k'); +EXPLAIN SELECT GP1.id +FROM t1 AS GP1 JOIN t3 AS GP2 +ON GP2.col_varchar_key <> GP1.col_varchar_nokey +WHERE (GP1.col_varchar_nokey, GP1.col_varchar_nokey) +IN ( +SELECT col_varchar_nokey, col_varchar_nokey +FROM t1 +WHERE col_varchar_nokey +IN ( SELECT col_varchar_key +FROM t2 LEFT JOIN t3 USING (col_varchar_key) ) +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY GP2 system NULL NULL NULL NULL 1 +1 PRIMARY GP1 ALL NULL NULL NULL NULL 3 Using where +2 DEPENDENT SUBQUERY t2 index col_varchar_key col_varchar_key 4 NULL 1 Using index; LooseScan +2 DEPENDENT SUBQUERY t3 ref col_varchar_key col_varchar_key 4 test.t2.col_varchar_key 2 Using index; FirstMatch(t2) +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where +SELECT GP1.id +FROM t1 AS GP1 JOIN t3 AS GP2 +ON GP2.col_varchar_key <> GP1.col_varchar_nokey +WHERE (GP1.col_varchar_nokey, GP1.col_varchar_nokey) +IN ( +SELECT col_varchar_nokey, col_varchar_nokey +FROM t1 +WHERE col_varchar_nokey +IN ( SELECT col_varchar_key +FROM t2 LEFT JOIN t3 USING (col_varchar_key) ) +) +; +id +200 +DROP TABLE t1,t2,t3; # End of 5.6 tests set optimizer_switch=default; set optimizer_switch=default; === modified file 'mysql-test/r/subquery_sj_all_bkaunique.result' --- a/mysql-test/r/subquery_sj_all_bkaunique.result 2012-01-12 14:53:51 +0000 +++ b/mysql-test/r/subquery_sj_all_bkaunique.result 2012-01-16 12:51:06 +0000 @@ -7572,6 +7572,62 @@ w y DROP TABLE t1, t2; # End of test for bug#13576391. +# +# Bug #13589848 "MISSING ROW ON SELECT WITH NESTED IN CLAUSES WHEN LOOSESCAN=ON" +# +CREATE TABLE t1 ( +id INT, +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t1 VALUES (100,'m','m'), +(200,'b','b'), (300,'x','x'); +CREATE TABLE t2 ( +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t2 VALUES ('b','b'); +CREATE TABLE t3 ( +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t3 VALUES ('k','k'); +EXPLAIN SELECT GP1.id +FROM t1 AS GP1 JOIN t3 AS GP2 +ON GP2.col_varchar_key <> GP1.col_varchar_nokey +WHERE (GP1.col_varchar_nokey, GP1.col_varchar_nokey) +IN ( +SELECT col_varchar_nokey, col_varchar_nokey +FROM t1 +WHERE col_varchar_nokey +IN ( SELECT col_varchar_key +FROM t2 LEFT JOIN t3 USING (col_varchar_key) ) +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY GP2 system NULL NULL NULL NULL 1 +1 PRIMARY GP1 ALL NULL NULL NULL NULL 3 Using where +2 DEPENDENT SUBQUERY t2 index col_varchar_key col_varchar_key 4 NULL 1 Using index; LooseScan +2 DEPENDENT SUBQUERY t3 ref col_varchar_key col_varchar_key 4 test.t2.col_varchar_key 2 Using index; FirstMatch(t2) +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (Block Nested Loop) +SELECT GP1.id +FROM t1 AS GP1 JOIN t3 AS GP2 +ON GP2.col_varchar_key <> GP1.col_varchar_nokey +WHERE (GP1.col_varchar_nokey, GP1.col_varchar_nokey) +IN ( +SELECT col_varchar_nokey, col_varchar_nokey +FROM t1 +WHERE col_varchar_nokey +IN ( SELECT col_varchar_key +FROM t2 LEFT JOIN t3 USING (col_varchar_key) ) +) +; +id +200 +DROP TABLE t1,t2,t3; # End of 5.6 tests set optimizer_switch=default; set optimizer_switch=default; === modified file 'mysql-test/r/subquery_sj_dupsweed.result' --- a/mysql-test/r/subquery_sj_dupsweed.result 2012-01-12 14:53:51 +0000 +++ b/mysql-test/r/subquery_sj_dupsweed.result 2012-01-16 12:51:06 +0000 @@ -7569,5 +7569,61 @@ w y DROP TABLE t1, t2; # End of test for bug#13576391. +# +# Bug #13589848 "MISSING ROW ON SELECT WITH NESTED IN CLAUSES WHEN LOOSESCAN=ON" +# +CREATE TABLE t1 ( +id INT, +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t1 VALUES (100,'m','m'), +(200,'b','b'), (300,'x','x'); +CREATE TABLE t2 ( +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t2 VALUES ('b','b'); +CREATE TABLE t3 ( +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t3 VALUES ('k','k'); +EXPLAIN SELECT GP1.id +FROM t1 AS GP1 JOIN t3 AS GP2 +ON GP2.col_varchar_key <> GP1.col_varchar_nokey +WHERE (GP1.col_varchar_nokey, GP1.col_varchar_nokey) +IN ( +SELECT col_varchar_nokey, col_varchar_nokey +FROM t1 +WHERE col_varchar_nokey +IN ( SELECT col_varchar_key +FROM t2 LEFT JOIN t3 USING (col_varchar_key) ) +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY GP2 system NULL NULL NULL NULL 1 +1 PRIMARY GP1 ALL NULL NULL NULL NULL 3 Using where +2 DEPENDENT SUBQUERY t2 index col_varchar_key col_varchar_key 4 NULL 1 Using index; Start temporary +2 DEPENDENT SUBQUERY t3 ref col_varchar_key col_varchar_key 4 test.t2.col_varchar_key 2 Using index +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where; End temporary; Using join buffer (Block Nested Loop) +SELECT GP1.id +FROM t1 AS GP1 JOIN t3 AS GP2 +ON GP2.col_varchar_key <> GP1.col_varchar_nokey +WHERE (GP1.col_varchar_nokey, GP1.col_varchar_nokey) +IN ( +SELECT col_varchar_nokey, col_varchar_nokey +FROM t1 +WHERE col_varchar_nokey +IN ( SELECT col_varchar_key +FROM t2 LEFT JOIN t3 USING (col_varchar_key) ) +) +; +id +200 +DROP TABLE t1,t2,t3; # End of 5.6 tests set optimizer_switch=default; === modified file 'mysql-test/r/subquery_sj_dupsweed_bka.result' --- a/mysql-test/r/subquery_sj_dupsweed_bka.result 2012-01-12 14:53:51 +0000 +++ b/mysql-test/r/subquery_sj_dupsweed_bka.result 2012-01-16 12:51:06 +0000 @@ -7570,6 +7570,62 @@ w y DROP TABLE t1, t2; # End of test for bug#13576391. +# +# Bug #13589848 "MISSING ROW ON SELECT WITH NESTED IN CLAUSES WHEN LOOSESCAN=ON" +# +CREATE TABLE t1 ( +id INT, +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t1 VALUES (100,'m','m'), +(200,'b','b'), (300,'x','x'); +CREATE TABLE t2 ( +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t2 VALUES ('b','b'); +CREATE TABLE t3 ( +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t3 VALUES ('k','k'); +EXPLAIN SELECT GP1.id +FROM t1 AS GP1 JOIN t3 AS GP2 +ON GP2.col_varchar_key <> GP1.col_varchar_nokey +WHERE (GP1.col_varchar_nokey, GP1.col_varchar_nokey) +IN ( +SELECT col_varchar_nokey, col_varchar_nokey +FROM t1 +WHERE col_varchar_nokey +IN ( SELECT col_varchar_key +FROM t2 LEFT JOIN t3 USING (col_varchar_key) ) +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY GP2 system NULL NULL NULL NULL 1 +1 PRIMARY GP1 ALL NULL NULL NULL NULL 3 Using where +2 DEPENDENT SUBQUERY t2 index col_varchar_key col_varchar_key 4 NULL 1 Using index; Start temporary +2 DEPENDENT SUBQUERY t3 ref col_varchar_key col_varchar_key 4 test.t2.col_varchar_key 2 Using index +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where; End temporary; Using join buffer (Block Nested Loop) +SELECT GP1.id +FROM t1 AS GP1 JOIN t3 AS GP2 +ON GP2.col_varchar_key <> GP1.col_varchar_nokey +WHERE (GP1.col_varchar_nokey, GP1.col_varchar_nokey) +IN ( +SELECT col_varchar_nokey, col_varchar_nokey +FROM t1 +WHERE col_varchar_nokey +IN ( SELECT col_varchar_key +FROM t2 LEFT JOIN t3 USING (col_varchar_key) ) +) +; +id +200 +DROP TABLE t1,t2,t3; # End of 5.6 tests set optimizer_switch=default; set optimizer_switch=default; === modified file 'mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result' --- a/mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result 2012-01-12 14:53:51 +0000 +++ b/mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result 2012-01-16 12:51:06 +0000 @@ -7570,6 +7570,62 @@ w y DROP TABLE t1, t2; # End of test for bug#13576391. +# +# Bug #13589848 "MISSING ROW ON SELECT WITH NESTED IN CLAUSES WHEN LOOSESCAN=ON" +# +CREATE TABLE t1 ( +id INT, +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t1 VALUES (100,'m','m'), +(200,'b','b'), (300,'x','x'); +CREATE TABLE t2 ( +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t2 VALUES ('b','b'); +CREATE TABLE t3 ( +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t3 VALUES ('k','k'); +EXPLAIN SELECT GP1.id +FROM t1 AS GP1 JOIN t3 AS GP2 +ON GP2.col_varchar_key <> GP1.col_varchar_nokey +WHERE (GP1.col_varchar_nokey, GP1.col_varchar_nokey) +IN ( +SELECT col_varchar_nokey, col_varchar_nokey +FROM t1 +WHERE col_varchar_nokey +IN ( SELECT col_varchar_key +FROM t2 LEFT JOIN t3 USING (col_varchar_key) ) +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY GP2 system NULL NULL NULL NULL 1 +1 PRIMARY GP1 ALL NULL NULL NULL NULL 3 Using where +2 DEPENDENT SUBQUERY t2 index col_varchar_key col_varchar_key 4 NULL 1 Using index; Start temporary +2 DEPENDENT SUBQUERY t3 ref col_varchar_key col_varchar_key 4 test.t2.col_varchar_key 2 Using index +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where; End temporary +SELECT GP1.id +FROM t1 AS GP1 JOIN t3 AS GP2 +ON GP2.col_varchar_key <> GP1.col_varchar_nokey +WHERE (GP1.col_varchar_nokey, GP1.col_varchar_nokey) +IN ( +SELECT col_varchar_nokey, col_varchar_nokey +FROM t1 +WHERE col_varchar_nokey +IN ( SELECT col_varchar_key +FROM t2 LEFT JOIN t3 USING (col_varchar_key) ) +) +; +id +200 +DROP TABLE t1,t2,t3; # End of 5.6 tests set optimizer_switch=default; set optimizer_switch=default; === modified file 'mysql-test/r/subquery_sj_dupsweed_bkaunique.result' --- a/mysql-test/r/subquery_sj_dupsweed_bkaunique.result 2012-01-12 14:53:51 +0000 +++ b/mysql-test/r/subquery_sj_dupsweed_bkaunique.result 2012-01-16 12:51:06 +0000 @@ -7571,6 +7571,62 @@ w y DROP TABLE t1, t2; # End of test for bug#13576391. +# +# Bug #13589848 "MISSING ROW ON SELECT WITH NESTED IN CLAUSES WHEN LOOSESCAN=ON" +# +CREATE TABLE t1 ( +id INT, +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t1 VALUES (100,'m','m'), +(200,'b','b'), (300,'x','x'); +CREATE TABLE t2 ( +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t2 VALUES ('b','b'); +CREATE TABLE t3 ( +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t3 VALUES ('k','k'); +EXPLAIN SELECT GP1.id +FROM t1 AS GP1 JOIN t3 AS GP2 +ON GP2.col_varchar_key <> GP1.col_varchar_nokey +WHERE (GP1.col_varchar_nokey, GP1.col_varchar_nokey) +IN ( +SELECT col_varchar_nokey, col_varchar_nokey +FROM t1 +WHERE col_varchar_nokey +IN ( SELECT col_varchar_key +FROM t2 LEFT JOIN t3 USING (col_varchar_key) ) +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY GP2 system NULL NULL NULL NULL 1 +1 PRIMARY GP1 ALL NULL NULL NULL NULL 3 Using where +2 DEPENDENT SUBQUERY t2 index col_varchar_key col_varchar_key 4 NULL 1 Using index; Start temporary +2 DEPENDENT SUBQUERY t3 ref col_varchar_key col_varchar_key 4 test.t2.col_varchar_key 2 Using index +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where; End temporary; Using join buffer (Block Nested Loop) +SELECT GP1.id +FROM t1 AS GP1 JOIN t3 AS GP2 +ON GP2.col_varchar_key <> GP1.col_varchar_nokey +WHERE (GP1.col_varchar_nokey, GP1.col_varchar_nokey) +IN ( +SELECT col_varchar_nokey, col_varchar_nokey +FROM t1 +WHERE col_varchar_nokey +IN ( SELECT col_varchar_key +FROM t2 LEFT JOIN t3 USING (col_varchar_key) ) +) +; +id +200 +DROP TABLE t1,t2,t3; # End of 5.6 tests set optimizer_switch=default; set optimizer_switch=default; === modified file 'mysql-test/r/subquery_sj_firstmatch.result' --- a/mysql-test/r/subquery_sj_firstmatch.result 2012-01-12 14:53:51 +0000 +++ b/mysql-test/r/subquery_sj_firstmatch.result 2012-01-16 12:51:06 +0000 @@ -7570,6 +7570,62 @@ w y DROP TABLE t1, t2; # End of test for bug#13576391. +# +# Bug #13589848 "MISSING ROW ON SELECT WITH NESTED IN CLAUSES WHEN LOOSESCAN=ON" +# +CREATE TABLE t1 ( +id INT, +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t1 VALUES (100,'m','m'), +(200,'b','b'), (300,'x','x'); +CREATE TABLE t2 ( +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t2 VALUES ('b','b'); +CREATE TABLE t3 ( +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t3 VALUES ('k','k'); +EXPLAIN SELECT GP1.id +FROM t1 AS GP1 JOIN t3 AS GP2 +ON GP2.col_varchar_key <> GP1.col_varchar_nokey +WHERE (GP1.col_varchar_nokey, GP1.col_varchar_nokey) +IN ( +SELECT col_varchar_nokey, col_varchar_nokey +FROM t1 +WHERE col_varchar_nokey +IN ( SELECT col_varchar_key +FROM t2 LEFT JOIN t3 USING (col_varchar_key) ) +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY GP2 system NULL NULL NULL NULL 1 +1 PRIMARY GP1 ALL NULL NULL NULL NULL 3 Using where +2 DEPENDENT SUBQUERY t2 index col_varchar_key col_varchar_key 4 NULL 1 Using index; Start temporary +2 DEPENDENT SUBQUERY t3 ref col_varchar_key col_varchar_key 4 test.t2.col_varchar_key 2 Using index +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where; End temporary; Using join buffer (Block Nested Loop) +SELECT GP1.id +FROM t1 AS GP1 JOIN t3 AS GP2 +ON GP2.col_varchar_key <> GP1.col_varchar_nokey +WHERE (GP1.col_varchar_nokey, GP1.col_varchar_nokey) +IN ( +SELECT col_varchar_nokey, col_varchar_nokey +FROM t1 +WHERE col_varchar_nokey +IN ( SELECT col_varchar_key +FROM t2 LEFT JOIN t3 USING (col_varchar_key) ) +) +; +id +200 +DROP TABLE t1,t2,t3; # End of 5.6 tests # # Bug#51457 Firstmatch semijoin strategy gives wrong results for === modified file 'mysql-test/r/subquery_sj_firstmatch_bka.result' --- a/mysql-test/r/subquery_sj_firstmatch_bka.result 2012-01-12 14:53:51 +0000 +++ b/mysql-test/r/subquery_sj_firstmatch_bka.result 2012-01-16 12:51:06 +0000 @@ -7571,6 +7571,62 @@ w y DROP TABLE t1, t2; # End of test for bug#13576391. +# +# Bug #13589848 "MISSING ROW ON SELECT WITH NESTED IN CLAUSES WHEN LOOSESCAN=ON" +# +CREATE TABLE t1 ( +id INT, +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t1 VALUES (100,'m','m'), +(200,'b','b'), (300,'x','x'); +CREATE TABLE t2 ( +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t2 VALUES ('b','b'); +CREATE TABLE t3 ( +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t3 VALUES ('k','k'); +EXPLAIN SELECT GP1.id +FROM t1 AS GP1 JOIN t3 AS GP2 +ON GP2.col_varchar_key <> GP1.col_varchar_nokey +WHERE (GP1.col_varchar_nokey, GP1.col_varchar_nokey) +IN ( +SELECT col_varchar_nokey, col_varchar_nokey +FROM t1 +WHERE col_varchar_nokey +IN ( SELECT col_varchar_key +FROM t2 LEFT JOIN t3 USING (col_varchar_key) ) +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY GP2 system NULL NULL NULL NULL 1 +1 PRIMARY GP1 ALL NULL NULL NULL NULL 3 Using where +2 DEPENDENT SUBQUERY t2 index col_varchar_key col_varchar_key 4 NULL 1 Using index; Start temporary +2 DEPENDENT SUBQUERY t3 ref col_varchar_key col_varchar_key 4 test.t2.col_varchar_key 2 Using index +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where; End temporary; Using join buffer (Block Nested Loop) +SELECT GP1.id +FROM t1 AS GP1 JOIN t3 AS GP2 +ON GP2.col_varchar_key <> GP1.col_varchar_nokey +WHERE (GP1.col_varchar_nokey, GP1.col_varchar_nokey) +IN ( +SELECT col_varchar_nokey, col_varchar_nokey +FROM t1 +WHERE col_varchar_nokey +IN ( SELECT col_varchar_key +FROM t2 LEFT JOIN t3 USING (col_varchar_key) ) +) +; +id +200 +DROP TABLE t1,t2,t3; # End of 5.6 tests # # Bug#51457 Firstmatch semijoin strategy gives wrong results for === modified file 'mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result' --- a/mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result 2012-01-12 14:53:51 +0000 +++ b/mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result 2012-01-16 12:51:06 +0000 @@ -7571,6 +7571,62 @@ w y DROP TABLE t1, t2; # End of test for bug#13576391. +# +# Bug #13589848 "MISSING ROW ON SELECT WITH NESTED IN CLAUSES WHEN LOOSESCAN=ON" +# +CREATE TABLE t1 ( +id INT, +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t1 VALUES (100,'m','m'), +(200,'b','b'), (300,'x','x'); +CREATE TABLE t2 ( +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t2 VALUES ('b','b'); +CREATE TABLE t3 ( +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t3 VALUES ('k','k'); +EXPLAIN SELECT GP1.id +FROM t1 AS GP1 JOIN t3 AS GP2 +ON GP2.col_varchar_key <> GP1.col_varchar_nokey +WHERE (GP1.col_varchar_nokey, GP1.col_varchar_nokey) +IN ( +SELECT col_varchar_nokey, col_varchar_nokey +FROM t1 +WHERE col_varchar_nokey +IN ( SELECT col_varchar_key +FROM t2 LEFT JOIN t3 USING (col_varchar_key) ) +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY GP2 system NULL NULL NULL NULL 1 +1 PRIMARY GP1 ALL NULL NULL NULL NULL 3 Using where +2 DEPENDENT SUBQUERY t2 index col_varchar_key col_varchar_key 4 NULL 1 Using index; Start temporary +2 DEPENDENT SUBQUERY t3 ref col_varchar_key col_varchar_key 4 test.t2.col_varchar_key 2 Using index +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where; End temporary +SELECT GP1.id +FROM t1 AS GP1 JOIN t3 AS GP2 +ON GP2.col_varchar_key <> GP1.col_varchar_nokey +WHERE (GP1.col_varchar_nokey, GP1.col_varchar_nokey) +IN ( +SELECT col_varchar_nokey, col_varchar_nokey +FROM t1 +WHERE col_varchar_nokey +IN ( SELECT col_varchar_key +FROM t2 LEFT JOIN t3 USING (col_varchar_key) ) +) +; +id +200 +DROP TABLE t1,t2,t3; # End of 5.6 tests # # Bug#51457 Firstmatch semijoin strategy gives wrong results for === modified file 'mysql-test/r/subquery_sj_firstmatch_bkaunique.result' --- a/mysql-test/r/subquery_sj_firstmatch_bkaunique.result 2012-01-12 14:53:51 +0000 +++ b/mysql-test/r/subquery_sj_firstmatch_bkaunique.result 2012-01-16 12:51:06 +0000 @@ -7572,6 +7572,62 @@ w y DROP TABLE t1, t2; # End of test for bug#13576391. +# +# Bug #13589848 "MISSING ROW ON SELECT WITH NESTED IN CLAUSES WHEN LOOSESCAN=ON" +# +CREATE TABLE t1 ( +id INT, +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t1 VALUES (100,'m','m'), +(200,'b','b'), (300,'x','x'); +CREATE TABLE t2 ( +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t2 VALUES ('b','b'); +CREATE TABLE t3 ( +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t3 VALUES ('k','k'); +EXPLAIN SELECT GP1.id +FROM t1 AS GP1 JOIN t3 AS GP2 +ON GP2.col_varchar_key <> GP1.col_varchar_nokey +WHERE (GP1.col_varchar_nokey, GP1.col_varchar_nokey) +IN ( +SELECT col_varchar_nokey, col_varchar_nokey +FROM t1 +WHERE col_varchar_nokey +IN ( SELECT col_varchar_key +FROM t2 LEFT JOIN t3 USING (col_varchar_key) ) +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY GP2 system NULL NULL NULL NULL 1 +1 PRIMARY GP1 ALL NULL NULL NULL NULL 3 Using where +2 DEPENDENT SUBQUERY t2 index col_varchar_key col_varchar_key 4 NULL 1 Using index; Start temporary +2 DEPENDENT SUBQUERY t3 ref col_varchar_key col_varchar_key 4 test.t2.col_varchar_key 2 Using index +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where; End temporary; Using join buffer (Block Nested Loop) +SELECT GP1.id +FROM t1 AS GP1 JOIN t3 AS GP2 +ON GP2.col_varchar_key <> GP1.col_varchar_nokey +WHERE (GP1.col_varchar_nokey, GP1.col_varchar_nokey) +IN ( +SELECT col_varchar_nokey, col_varchar_nokey +FROM t1 +WHERE col_varchar_nokey +IN ( SELECT col_varchar_key +FROM t2 LEFT JOIN t3 USING (col_varchar_key) ) +) +; +id +200 +DROP TABLE t1,t2,t3; # End of 5.6 tests # # Bug#51457 Firstmatch semijoin strategy gives wrong results for === modified file 'mysql-test/r/subquery_sj_loosescan.result' --- a/mysql-test/r/subquery_sj_loosescan.result 2012-01-12 14:53:51 +0000 +++ b/mysql-test/r/subquery_sj_loosescan.result 2012-01-16 12:51:06 +0000 @@ -7570,5 +7570,61 @@ w y DROP TABLE t1, t2; # End of test for bug#13576391. +# +# Bug #13589848 "MISSING ROW ON SELECT WITH NESTED IN CLAUSES WHEN LOOSESCAN=ON" +# +CREATE TABLE t1 ( +id INT, +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t1 VALUES (100,'m','m'), +(200,'b','b'), (300,'x','x'); +CREATE TABLE t2 ( +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t2 VALUES ('b','b'); +CREATE TABLE t3 ( +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t3 VALUES ('k','k'); +EXPLAIN SELECT GP1.id +FROM t1 AS GP1 JOIN t3 AS GP2 +ON GP2.col_varchar_key <> GP1.col_varchar_nokey +WHERE (GP1.col_varchar_nokey, GP1.col_varchar_nokey) +IN ( +SELECT col_varchar_nokey, col_varchar_nokey +FROM t1 +WHERE col_varchar_nokey +IN ( SELECT col_varchar_key +FROM t2 LEFT JOIN t3 USING (col_varchar_key) ) +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY GP2 system NULL NULL NULL NULL 1 +1 PRIMARY GP1 ALL NULL NULL NULL NULL 3 Using where +2 DEPENDENT SUBQUERY t2 index col_varchar_key col_varchar_key 4 NULL 1 Using index; LooseScan +2 DEPENDENT SUBQUERY t3 ref col_varchar_key col_varchar_key 4 test.t2.col_varchar_key 2 Using index; FirstMatch(t2) +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (Block Nested Loop) +SELECT GP1.id +FROM t1 AS GP1 JOIN t3 AS GP2 +ON GP2.col_varchar_key <> GP1.col_varchar_nokey +WHERE (GP1.col_varchar_nokey, GP1.col_varchar_nokey) +IN ( +SELECT col_varchar_nokey, col_varchar_nokey +FROM t1 +WHERE col_varchar_nokey +IN ( SELECT col_varchar_key +FROM t2 LEFT JOIN t3 USING (col_varchar_key) ) +) +; +id +200 +DROP TABLE t1,t2,t3; # End of 5.6 tests set optimizer_switch=default; === modified file 'mysql-test/r/subquery_sj_loosescan_bka.result' --- a/mysql-test/r/subquery_sj_loosescan_bka.result 2012-01-12 14:53:51 +0000 +++ b/mysql-test/r/subquery_sj_loosescan_bka.result 2012-01-16 12:51:06 +0000 @@ -7571,6 +7571,62 @@ w y DROP TABLE t1, t2; # End of test for bug#13576391. +# +# Bug #13589848 "MISSING ROW ON SELECT WITH NESTED IN CLAUSES WHEN LOOSESCAN=ON" +# +CREATE TABLE t1 ( +id INT, +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t1 VALUES (100,'m','m'), +(200,'b','b'), (300,'x','x'); +CREATE TABLE t2 ( +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t2 VALUES ('b','b'); +CREATE TABLE t3 ( +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t3 VALUES ('k','k'); +EXPLAIN SELECT GP1.id +FROM t1 AS GP1 JOIN t3 AS GP2 +ON GP2.col_varchar_key <> GP1.col_varchar_nokey +WHERE (GP1.col_varchar_nokey, GP1.col_varchar_nokey) +IN ( +SELECT col_varchar_nokey, col_varchar_nokey +FROM t1 +WHERE col_varchar_nokey +IN ( SELECT col_varchar_key +FROM t2 LEFT JOIN t3 USING (col_varchar_key) ) +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY GP2 system NULL NULL NULL NULL 1 +1 PRIMARY GP1 ALL NULL NULL NULL NULL 3 Using where +2 DEPENDENT SUBQUERY t2 index col_varchar_key col_varchar_key 4 NULL 1 Using index; LooseScan +2 DEPENDENT SUBQUERY t3 ref col_varchar_key col_varchar_key 4 test.t2.col_varchar_key 2 Using index; FirstMatch(t2) +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (Block Nested Loop) +SELECT GP1.id +FROM t1 AS GP1 JOIN t3 AS GP2 +ON GP2.col_varchar_key <> GP1.col_varchar_nokey +WHERE (GP1.col_varchar_nokey, GP1.col_varchar_nokey) +IN ( +SELECT col_varchar_nokey, col_varchar_nokey +FROM t1 +WHERE col_varchar_nokey +IN ( SELECT col_varchar_key +FROM t2 LEFT JOIN t3 USING (col_varchar_key) ) +) +; +id +200 +DROP TABLE t1,t2,t3; # End of 5.6 tests set optimizer_switch=default; set optimizer_switch=default; === modified file 'mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result' --- a/mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result 2012-01-12 14:53:51 +0000 +++ b/mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result 2012-01-16 12:51:06 +0000 @@ -7571,6 +7571,62 @@ w y DROP TABLE t1, t2; # End of test for bug#13576391. +# +# Bug #13589848 "MISSING ROW ON SELECT WITH NESTED IN CLAUSES WHEN LOOSESCAN=ON" +# +CREATE TABLE t1 ( +id INT, +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t1 VALUES (100,'m','m'), +(200,'b','b'), (300,'x','x'); +CREATE TABLE t2 ( +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t2 VALUES ('b','b'); +CREATE TABLE t3 ( +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t3 VALUES ('k','k'); +EXPLAIN SELECT GP1.id +FROM t1 AS GP1 JOIN t3 AS GP2 +ON GP2.col_varchar_key <> GP1.col_varchar_nokey +WHERE (GP1.col_varchar_nokey, GP1.col_varchar_nokey) +IN ( +SELECT col_varchar_nokey, col_varchar_nokey +FROM t1 +WHERE col_varchar_nokey +IN ( SELECT col_varchar_key +FROM t2 LEFT JOIN t3 USING (col_varchar_key) ) +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY GP2 system NULL NULL NULL NULL 1 +1 PRIMARY GP1 ALL NULL NULL NULL NULL 3 Using where +2 DEPENDENT SUBQUERY t2 index col_varchar_key col_varchar_key 4 NULL 1 Using index; LooseScan +2 DEPENDENT SUBQUERY t3 ref col_varchar_key col_varchar_key 4 test.t2.col_varchar_key 2 Using index; FirstMatch(t2) +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where +SELECT GP1.id +FROM t1 AS GP1 JOIN t3 AS GP2 +ON GP2.col_varchar_key <> GP1.col_varchar_nokey +WHERE (GP1.col_varchar_nokey, GP1.col_varchar_nokey) +IN ( +SELECT col_varchar_nokey, col_varchar_nokey +FROM t1 +WHERE col_varchar_nokey +IN ( SELECT col_varchar_key +FROM t2 LEFT JOIN t3 USING (col_varchar_key) ) +) +; +id +200 +DROP TABLE t1,t2,t3; # End of 5.6 tests set optimizer_switch=default; set optimizer_switch=default; === modified file 'mysql-test/r/subquery_sj_loosescan_bkaunique.result' --- a/mysql-test/r/subquery_sj_loosescan_bkaunique.result 2012-01-12 14:53:51 +0000 +++ b/mysql-test/r/subquery_sj_loosescan_bkaunique.result 2012-01-16 12:51:06 +0000 @@ -7572,6 +7572,62 @@ w y DROP TABLE t1, t2; # End of test for bug#13576391. +# +# Bug #13589848 "MISSING ROW ON SELECT WITH NESTED IN CLAUSES WHEN LOOSESCAN=ON" +# +CREATE TABLE t1 ( +id INT, +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t1 VALUES (100,'m','m'), +(200,'b','b'), (300,'x','x'); +CREATE TABLE t2 ( +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t2 VALUES ('b','b'); +CREATE TABLE t3 ( +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t3 VALUES ('k','k'); +EXPLAIN SELECT GP1.id +FROM t1 AS GP1 JOIN t3 AS GP2 +ON GP2.col_varchar_key <> GP1.col_varchar_nokey +WHERE (GP1.col_varchar_nokey, GP1.col_varchar_nokey) +IN ( +SELECT col_varchar_nokey, col_varchar_nokey +FROM t1 +WHERE col_varchar_nokey +IN ( SELECT col_varchar_key +FROM t2 LEFT JOIN t3 USING (col_varchar_key) ) +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY GP2 system NULL NULL NULL NULL 1 +1 PRIMARY GP1 ALL NULL NULL NULL NULL 3 Using where +2 DEPENDENT SUBQUERY t2 index col_varchar_key col_varchar_key 4 NULL 1 Using index; LooseScan +2 DEPENDENT SUBQUERY t3 ref col_varchar_key col_varchar_key 4 test.t2.col_varchar_key 2 Using index; FirstMatch(t2) +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (Block Nested Loop) +SELECT GP1.id +FROM t1 AS GP1 JOIN t3 AS GP2 +ON GP2.col_varchar_key <> GP1.col_varchar_nokey +WHERE (GP1.col_varchar_nokey, GP1.col_varchar_nokey) +IN ( +SELECT col_varchar_nokey, col_varchar_nokey +FROM t1 +WHERE col_varchar_nokey +IN ( SELECT col_varchar_key +FROM t2 LEFT JOIN t3 USING (col_varchar_key) ) +) +; +id +200 +DROP TABLE t1,t2,t3; # End of 5.6 tests set optimizer_switch=default; set optimizer_switch=default; === modified file 'mysql-test/r/subquery_sj_mat.result' --- a/mysql-test/r/subquery_sj_mat.result 2012-01-12 14:53:51 +0000 +++ b/mysql-test/r/subquery_sj_mat.result 2012-01-16 12:51:06 +0000 @@ -7570,5 +7570,61 @@ w y DROP TABLE t1, t2; # End of test for bug#13576391. +# +# Bug #13589848 "MISSING ROW ON SELECT WITH NESTED IN CLAUSES WHEN LOOSESCAN=ON" +# +CREATE TABLE t1 ( +id INT, +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t1 VALUES (100,'m','m'), +(200,'b','b'), (300,'x','x'); +CREATE TABLE t2 ( +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t2 VALUES ('b','b'); +CREATE TABLE t3 ( +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t3 VALUES ('k','k'); +EXPLAIN SELECT GP1.id +FROM t1 AS GP1 JOIN t3 AS GP2 +ON GP2.col_varchar_key <> GP1.col_varchar_nokey +WHERE (GP1.col_varchar_nokey, GP1.col_varchar_nokey) +IN ( +SELECT col_varchar_nokey, col_varchar_nokey +FROM t1 +WHERE col_varchar_nokey +IN ( SELECT col_varchar_key +FROM t2 LEFT JOIN t3 USING (col_varchar_key) ) +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY GP2 system NULL NULL NULL NULL 1 +1 PRIMARY GP1 ALL NULL NULL NULL NULL 3 Using where +2 DEPENDENT SUBQUERY t2 index col_varchar_key col_varchar_key 4 NULL 1 Using index; Start materialize; Scan +2 DEPENDENT SUBQUERY t3 ref col_varchar_key col_varchar_key 4 test.t2.col_varchar_key 2 Using index; End materialize +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (Block Nested Loop) +SELECT GP1.id +FROM t1 AS GP1 JOIN t3 AS GP2 +ON GP2.col_varchar_key <> GP1.col_varchar_nokey +WHERE (GP1.col_varchar_nokey, GP1.col_varchar_nokey) +IN ( +SELECT col_varchar_nokey, col_varchar_nokey +FROM t1 +WHERE col_varchar_nokey +IN ( SELECT col_varchar_key +FROM t2 LEFT JOIN t3 USING (col_varchar_key) ) +) +; +id +200 +DROP TABLE t1,t2,t3; # End of 5.6 tests set optimizer_switch=default; === modified file 'mysql-test/r/subquery_sj_mat_bka.result' --- a/mysql-test/r/subquery_sj_mat_bka.result 2012-01-12 14:53:51 +0000 +++ b/mysql-test/r/subquery_sj_mat_bka.result 2012-01-16 12:51:06 +0000 @@ -7571,6 +7571,62 @@ w y DROP TABLE t1, t2; # End of test for bug#13576391. +# +# Bug #13589848 "MISSING ROW ON SELECT WITH NESTED IN CLAUSES WHEN LOOSESCAN=ON" +# +CREATE TABLE t1 ( +id INT, +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t1 VALUES (100,'m','m'), +(200,'b','b'), (300,'x','x'); +CREATE TABLE t2 ( +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t2 VALUES ('b','b'); +CREATE TABLE t3 ( +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t3 VALUES ('k','k'); +EXPLAIN SELECT GP1.id +FROM t1 AS GP1 JOIN t3 AS GP2 +ON GP2.col_varchar_key <> GP1.col_varchar_nokey +WHERE (GP1.col_varchar_nokey, GP1.col_varchar_nokey) +IN ( +SELECT col_varchar_nokey, col_varchar_nokey +FROM t1 +WHERE col_varchar_nokey +IN ( SELECT col_varchar_key +FROM t2 LEFT JOIN t3 USING (col_varchar_key) ) +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY GP2 system NULL NULL NULL NULL 1 +1 PRIMARY GP1 ALL NULL NULL NULL NULL 3 Using where +2 DEPENDENT SUBQUERY t2 index col_varchar_key col_varchar_key 4 NULL 1 Using index; Start materialize; Scan +2 DEPENDENT SUBQUERY t3 ref col_varchar_key col_varchar_key 4 test.t2.col_varchar_key 2 Using index; End materialize +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (Block Nested Loop) +SELECT GP1.id +FROM t1 AS GP1 JOIN t3 AS GP2 +ON GP2.col_varchar_key <> GP1.col_varchar_nokey +WHERE (GP1.col_varchar_nokey, GP1.col_varchar_nokey) +IN ( +SELECT col_varchar_nokey, col_varchar_nokey +FROM t1 +WHERE col_varchar_nokey +IN ( SELECT col_varchar_key +FROM t2 LEFT JOIN t3 USING (col_varchar_key) ) +) +; +id +200 +DROP TABLE t1,t2,t3; # End of 5.6 tests set optimizer_switch=default; set optimizer_switch=default; === modified file 'mysql-test/r/subquery_sj_mat_bka_nixbnl.result' --- a/mysql-test/r/subquery_sj_mat_bka_nixbnl.result 2012-01-12 14:53:51 +0000 +++ b/mysql-test/r/subquery_sj_mat_bka_nixbnl.result 2012-01-16 12:51:06 +0000 @@ -7571,6 +7571,62 @@ w y DROP TABLE t1, t2; # End of test for bug#13576391. +# +# Bug #13589848 "MISSING ROW ON SELECT WITH NESTED IN CLAUSES WHEN LOOSESCAN=ON" +# +CREATE TABLE t1 ( +id INT, +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t1 VALUES (100,'m','m'), +(200,'b','b'), (300,'x','x'); +CREATE TABLE t2 ( +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t2 VALUES ('b','b'); +CREATE TABLE t3 ( +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t3 VALUES ('k','k'); +EXPLAIN SELECT GP1.id +FROM t1 AS GP1 JOIN t3 AS GP2 +ON GP2.col_varchar_key <> GP1.col_varchar_nokey +WHERE (GP1.col_varchar_nokey, GP1.col_varchar_nokey) +IN ( +SELECT col_varchar_nokey, col_varchar_nokey +FROM t1 +WHERE col_varchar_nokey +IN ( SELECT col_varchar_key +FROM t2 LEFT JOIN t3 USING (col_varchar_key) ) +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY GP2 system NULL NULL NULL NULL 1 +1 PRIMARY GP1 ALL NULL NULL NULL NULL 3 Using where +2 DEPENDENT SUBQUERY t2 index col_varchar_key col_varchar_key 4 NULL 1 Using index; Start materialize; Scan +2 DEPENDENT SUBQUERY t3 ref col_varchar_key col_varchar_key 4 test.t2.col_varchar_key 2 Using index; End materialize +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where +SELECT GP1.id +FROM t1 AS GP1 JOIN t3 AS GP2 +ON GP2.col_varchar_key <> GP1.col_varchar_nokey +WHERE (GP1.col_varchar_nokey, GP1.col_varchar_nokey) +IN ( +SELECT col_varchar_nokey, col_varchar_nokey +FROM t1 +WHERE col_varchar_nokey +IN ( SELECT col_varchar_key +FROM t2 LEFT JOIN t3 USING (col_varchar_key) ) +) +; +id +200 +DROP TABLE t1,t2,t3; # End of 5.6 tests set optimizer_switch=default; set optimizer_switch=default; === modified file 'mysql-test/r/subquery_sj_mat_bkaunique.result' --- a/mysql-test/r/subquery_sj_mat_bkaunique.result 2012-01-12 14:53:51 +0000 +++ b/mysql-test/r/subquery_sj_mat_bkaunique.result 2012-01-16 12:51:06 +0000 @@ -7572,6 +7572,62 @@ w y DROP TABLE t1, t2; # End of test for bug#13576391. +# +# Bug #13589848 "MISSING ROW ON SELECT WITH NESTED IN CLAUSES WHEN LOOSESCAN=ON" +# +CREATE TABLE t1 ( +id INT, +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t1 VALUES (100,'m','m'), +(200,'b','b'), (300,'x','x'); +CREATE TABLE t2 ( +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t2 VALUES ('b','b'); +CREATE TABLE t3 ( +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t3 VALUES ('k','k'); +EXPLAIN SELECT GP1.id +FROM t1 AS GP1 JOIN t3 AS GP2 +ON GP2.col_varchar_key <> GP1.col_varchar_nokey +WHERE (GP1.col_varchar_nokey, GP1.col_varchar_nokey) +IN ( +SELECT col_varchar_nokey, col_varchar_nokey +FROM t1 +WHERE col_varchar_nokey +IN ( SELECT col_varchar_key +FROM t2 LEFT JOIN t3 USING (col_varchar_key) ) +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY GP2 system NULL NULL NULL NULL 1 +1 PRIMARY GP1 ALL NULL NULL NULL NULL 3 Using where +2 DEPENDENT SUBQUERY t2 index col_varchar_key col_varchar_key 4 NULL 1 Using index; Start materialize; Scan +2 DEPENDENT SUBQUERY t3 ref col_varchar_key col_varchar_key 4 test.t2.col_varchar_key 2 Using index; End materialize +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (Block Nested Loop) +SELECT GP1.id +FROM t1 AS GP1 JOIN t3 AS GP2 +ON GP2.col_varchar_key <> GP1.col_varchar_nokey +WHERE (GP1.col_varchar_nokey, GP1.col_varchar_nokey) +IN ( +SELECT col_varchar_nokey, col_varchar_nokey +FROM t1 +WHERE col_varchar_nokey +IN ( SELECT col_varchar_key +FROM t2 LEFT JOIN t3 USING (col_varchar_key) ) +) +; +id +200 +DROP TABLE t1,t2,t3; # End of 5.6 tests set optimizer_switch=default; set optimizer_switch=default; === modified file 'mysql-test/r/subquery_sj_mat_nosj.result' --- a/mysql-test/r/subquery_sj_mat_nosj.result 2012-01-12 14:53:51 +0000 +++ b/mysql-test/r/subquery_sj_mat_nosj.result 2012-01-16 12:51:06 +0000 @@ -7646,5 +7646,61 @@ w y DROP TABLE t1, t2; # End of test for bug#13576391. +# +# Bug #13589848 "MISSING ROW ON SELECT WITH NESTED IN CLAUSES WHEN LOOSESCAN=ON" +# +CREATE TABLE t1 ( +id INT, +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t1 VALUES (100,'m','m'), +(200,'b','b'), (300,'x','x'); +CREATE TABLE t2 ( +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t2 VALUES ('b','b'); +CREATE TABLE t3 ( +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t3 VALUES ('k','k'); +EXPLAIN SELECT GP1.id +FROM t1 AS GP1 JOIN t3 AS GP2 +ON GP2.col_varchar_key <> GP1.col_varchar_nokey +WHERE (GP1.col_varchar_nokey, GP1.col_varchar_nokey) +IN ( +SELECT col_varchar_nokey, col_varchar_nokey +FROM t1 +WHERE col_varchar_nokey +IN ( SELECT col_varchar_key +FROM t2 LEFT JOIN t3 USING (col_varchar_key) ) +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY GP2 system NULL NULL NULL NULL 1 +1 PRIMARY GP1 ALL NULL NULL NULL NULL 3 Using where +2 SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where +3 SUBQUERY t2 system NULL NULL NULL NULL 1 +3 SUBQUERY t3 system col_varchar_key NULL NULL NULL 1 +SELECT GP1.id +FROM t1 AS GP1 JOIN t3 AS GP2 +ON GP2.col_varchar_key <> GP1.col_varchar_nokey +WHERE (GP1.col_varchar_nokey, GP1.col_varchar_nokey) +IN ( +SELECT col_varchar_nokey, col_varchar_nokey +FROM t1 +WHERE col_varchar_nokey +IN ( SELECT col_varchar_key +FROM t2 LEFT JOIN t3 USING (col_varchar_key) ) +) +; +id +200 +DROP TABLE t1,t2,t3; # End of 5.6 tests set optimizer_switch=default; === modified file 'mysql-test/r/subquery_sj_none.result' --- a/mysql-test/r/subquery_sj_none.result 2012-01-12 14:53:51 +0000 +++ b/mysql-test/r/subquery_sj_none.result 2012-01-16 12:51:06 +0000 @@ -7581,5 +7581,61 @@ w y DROP TABLE t1, t2; # End of test for bug#13576391. +# +# Bug #13589848 "MISSING ROW ON SELECT WITH NESTED IN CLAUSES WHEN LOOSESCAN=ON" +# +CREATE TABLE t1 ( +id INT, +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t1 VALUES (100,'m','m'), +(200,'b','b'), (300,'x','x'); +CREATE TABLE t2 ( +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t2 VALUES ('b','b'); +CREATE TABLE t3 ( +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t3 VALUES ('k','k'); +EXPLAIN SELECT GP1.id +FROM t1 AS GP1 JOIN t3 AS GP2 +ON GP2.col_varchar_key <> GP1.col_varchar_nokey +WHERE (GP1.col_varchar_nokey, GP1.col_varchar_nokey) +IN ( +SELECT col_varchar_nokey, col_varchar_nokey +FROM t1 +WHERE col_varchar_nokey +IN ( SELECT col_varchar_key +FROM t2 LEFT JOIN t3 USING (col_varchar_key) ) +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY GP2 system NULL NULL NULL NULL 1 +1 PRIMARY GP1 ALL NULL NULL NULL NULL 3 Using where +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where +3 DEPENDENT SUBQUERY t2 system col_varchar_key NULL NULL NULL 1 +3 DEPENDENT SUBQUERY t3 system col_varchar_key NULL NULL NULL 1 +SELECT GP1.id +FROM t1 AS GP1 JOIN t3 AS GP2 +ON GP2.col_varchar_key <> GP1.col_varchar_nokey +WHERE (GP1.col_varchar_nokey, GP1.col_varchar_nokey) +IN ( +SELECT col_varchar_nokey, col_varchar_nokey +FROM t1 +WHERE col_varchar_nokey +IN ( SELECT col_varchar_key +FROM t2 LEFT JOIN t3 USING (col_varchar_key) ) +) +; +id +200 +DROP TABLE t1,t2,t3; # End of 5.6 tests set optimizer_switch=default; === modified file 'mysql-test/r/subquery_sj_none_bka.result' --- a/mysql-test/r/subquery_sj_none_bka.result 2012-01-12 14:53:51 +0000 +++ b/mysql-test/r/subquery_sj_none_bka.result 2012-01-16 12:51:06 +0000 @@ -7582,6 +7582,62 @@ w y DROP TABLE t1, t2; # End of test for bug#13576391. +# +# Bug #13589848 "MISSING ROW ON SELECT WITH NESTED IN CLAUSES WHEN LOOSESCAN=ON" +# +CREATE TABLE t1 ( +id INT, +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t1 VALUES (100,'m','m'), +(200,'b','b'), (300,'x','x'); +CREATE TABLE t2 ( +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t2 VALUES ('b','b'); +CREATE TABLE t3 ( +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t3 VALUES ('k','k'); +EXPLAIN SELECT GP1.id +FROM t1 AS GP1 JOIN t3 AS GP2 +ON GP2.col_varchar_key <> GP1.col_varchar_nokey +WHERE (GP1.col_varchar_nokey, GP1.col_varchar_nokey) +IN ( +SELECT col_varchar_nokey, col_varchar_nokey +FROM t1 +WHERE col_varchar_nokey +IN ( SELECT col_varchar_key +FROM t2 LEFT JOIN t3 USING (col_varchar_key) ) +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY GP2 system NULL NULL NULL NULL 1 +1 PRIMARY GP1 ALL NULL NULL NULL NULL 3 Using where +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where +3 DEPENDENT SUBQUERY t2 system col_varchar_key NULL NULL NULL 1 +3 DEPENDENT SUBQUERY t3 system col_varchar_key NULL NULL NULL 1 +SELECT GP1.id +FROM t1 AS GP1 JOIN t3 AS GP2 +ON GP2.col_varchar_key <> GP1.col_varchar_nokey +WHERE (GP1.col_varchar_nokey, GP1.col_varchar_nokey) +IN ( +SELECT col_varchar_nokey, col_varchar_nokey +FROM t1 +WHERE col_varchar_nokey +IN ( SELECT col_varchar_key +FROM t2 LEFT JOIN t3 USING (col_varchar_key) ) +) +; +id +200 +DROP TABLE t1,t2,t3; # End of 5.6 tests set optimizer_switch=default; set optimizer_switch=default; === modified file 'mysql-test/r/subquery_sj_none_bka_nixbnl.result' --- a/mysql-test/r/subquery_sj_none_bka_nixbnl.result 2012-01-12 14:53:51 +0000 +++ b/mysql-test/r/subquery_sj_none_bka_nixbnl.result 2012-01-16 12:51:06 +0000 @@ -7582,6 +7582,62 @@ w y DROP TABLE t1, t2; # End of test for bug#13576391. +# +# Bug #13589848 "MISSING ROW ON SELECT WITH NESTED IN CLAUSES WHEN LOOSESCAN=ON" +# +CREATE TABLE t1 ( +id INT, +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t1 VALUES (100,'m','m'), +(200,'b','b'), (300,'x','x'); +CREATE TABLE t2 ( +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t2 VALUES ('b','b'); +CREATE TABLE t3 ( +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t3 VALUES ('k','k'); +EXPLAIN SELECT GP1.id +FROM t1 AS GP1 JOIN t3 AS GP2 +ON GP2.col_varchar_key <> GP1.col_varchar_nokey +WHERE (GP1.col_varchar_nokey, GP1.col_varchar_nokey) +IN ( +SELECT col_varchar_nokey, col_varchar_nokey +FROM t1 +WHERE col_varchar_nokey +IN ( SELECT col_varchar_key +FROM t2 LEFT JOIN t3 USING (col_varchar_key) ) +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY GP2 system NULL NULL NULL NULL 1 +1 PRIMARY GP1 ALL NULL NULL NULL NULL 3 Using where +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where +3 DEPENDENT SUBQUERY t2 system col_varchar_key NULL NULL NULL 1 +3 DEPENDENT SUBQUERY t3 system col_varchar_key NULL NULL NULL 1 +SELECT GP1.id +FROM t1 AS GP1 JOIN t3 AS GP2 +ON GP2.col_varchar_key <> GP1.col_varchar_nokey +WHERE (GP1.col_varchar_nokey, GP1.col_varchar_nokey) +IN ( +SELECT col_varchar_nokey, col_varchar_nokey +FROM t1 +WHERE col_varchar_nokey +IN ( SELECT col_varchar_key +FROM t2 LEFT JOIN t3 USING (col_varchar_key) ) +) +; +id +200 +DROP TABLE t1,t2,t3; # End of 5.6 tests set optimizer_switch=default; set optimizer_switch=default; === modified file 'mysql-test/r/subquery_sj_none_bkaunique.result' --- a/mysql-test/r/subquery_sj_none_bkaunique.result 2012-01-12 14:53:51 +0000 +++ b/mysql-test/r/subquery_sj_none_bkaunique.result 2012-01-16 12:51:06 +0000 @@ -7583,6 +7583,62 @@ w y DROP TABLE t1, t2; # End of test for bug#13576391. +# +# Bug #13589848 "MISSING ROW ON SELECT WITH NESTED IN CLAUSES WHEN LOOSESCAN=ON" +# +CREATE TABLE t1 ( +id INT, +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t1 VALUES (100,'m','m'), +(200,'b','b'), (300,'x','x'); +CREATE TABLE t2 ( +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t2 VALUES ('b','b'); +CREATE TABLE t3 ( +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +KEY (col_varchar_key) +); +INSERT INTO t3 VALUES ('k','k'); +EXPLAIN SELECT GP1.id +FROM t1 AS GP1 JOIN t3 AS GP2 +ON GP2.col_varchar_key <> GP1.col_varchar_nokey +WHERE (GP1.col_varchar_nokey, GP1.col_varchar_nokey) +IN ( +SELECT col_varchar_nokey, col_varchar_nokey +FROM t1 +WHERE col_varchar_nokey +IN ( SELECT col_varchar_key +FROM t2 LEFT JOIN t3 USING (col_varchar_key) ) +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY GP2 system NULL NULL NULL NULL 1 +1 PRIMARY GP1 ALL NULL NULL NULL NULL 3 Using where +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where +3 DEPENDENT SUBQUERY t2 system col_varchar_key NULL NULL NULL 1 +3 DEPENDENT SUBQUERY t3 system col_varchar_key NULL NULL NULL 1 +SELECT GP1.id +FROM t1 AS GP1 JOIN t3 AS GP2 +ON GP2.col_varchar_key <> GP1.col_varchar_nokey +WHERE (GP1.col_varchar_nokey, GP1.col_varchar_nokey) +IN ( +SELECT col_varchar_nokey, col_varchar_nokey +FROM t1 +WHERE col_varchar_nokey +IN ( SELECT col_varchar_key +FROM t2 LEFT JOIN t3 USING (col_varchar_key) ) +) +; +id +200 +DROP TABLE t1,t2,t3; # End of 5.6 tests set optimizer_switch=default; set optimizer_switch=default; === modified file 'mysql-test/t/group_by.test' --- a/mysql-test/t/group_by.test 2012-01-10 18:58:10 +0000 +++ b/mysql-test/t/group_by.test 2012-01-17 09:45:08 +0000 @@ -1708,3 +1708,64 @@ GROUP BY pk, col_int_key, (SELECT a FROM ORDER BY pk, col_int_key, (SELECT a FROM t2 WHERE a=t1.pk); DROP TABLE t1,t2; + +--echo # +--echo # Bug#13591138 - ASSERTION NAME && !IS_AUTOGENERATED_NAME IN +--echo # ITEM::PRINT_FOR_ORDER ON EXPLAIN EXT +--echo # + +# There was a bug with Item_direct_view_ref + +CREATE TABLE t1 ( + pk int(11) NOT NULL AUTO_INCREMENT, + col_datetime_key datetime NOT NULL, + col_varchar_key varchar(1) NOT NULL, + PRIMARY KEY (pk), + KEY col_datetime_key (col_datetime_key), + KEY col_varchar_key (col_varchar_key) +); + +CREATE TABLE t2 ( + pk int(11) NOT NULL AUTO_INCREMENT, + PRIMARY KEY (pk) +); + +CREATE TABLE t3 ( + pk int(11) NOT NULL AUTO_INCREMENT, + col_varchar_key varchar(1) NOT NULL, + PRIMARY KEY (pk), + KEY col_varchar_key (col_varchar_key) +); + +CREATE VIEW view1 AS SELECT * FROM t1; + +EXPLAIN EXTENDED +SELECT + alias1.col_datetime_key AS field1 +FROM ( + view1 AS alias1, + t3 AS alias2 + ) +WHERE ( + (SELECT MIN(SQ1_alias1.pk) + FROM t2 AS SQ1_alias1 + ) +) OR (alias1.col_varchar_key = alias2.col_varchar_key + AND alias1.col_varchar_key = 'j' +) AND alias1.pk IS NULL +GROUP BY + field1; + +DROP TABLE t1,t2,t3; +DROP VIEW view1; + +# And a bug with Item_singlerow_subselect: + +CREATE TABLE t1 (col_varchar_nokey varchar(1) DEFAULT NULL); +INSERT INTO t1 VALUES ('v'),('c'); + +EXPLAIN EXTENDED SELECT (SELECT 150) AS field5 +FROM (SELECT * FROM t1) AS alias1 +GROUP BY field5; + +DROP TABLE t1; === modified file 'sql/item_subselect.cc' --- a/sql/item_subselect.cc 2011-12-15 15:15:37 +0000 +++ b/sql/item_subselect.cc 2012-01-17 09:45:08 +0000 @@ -217,6 +217,7 @@ bool Item_subselect::fix_fields(THD *thd unit->outer_select()->having= substitution; // correct HAVING for PS (*ref)= substitution; + substitution->is_autogenerated_name= is_autogenerated_name; substitution->name= name; if (have_to_be_excluded) engine->exclude(); === modified file 'sql/sql_base.cc' --- a/sql/sql_base.cc 2012-01-13 09:12:14 +0000 +++ b/sql/sql_base.cc 2012-01-17 09:27:34 +0000 @@ -6411,6 +6411,7 @@ find_field_in_view(THD *thd, TABLE_LIST */ if (*ref && !(*ref)->is_autogenerated_name) { + item->is_autogenerated_name= false; item->set_name((*ref)->name, (*ref)->name_length, system_charset_info); item->real_item()->set_name((*ref)->name, (*ref)->name_length, @@ -6510,6 +6511,7 @@ find_field_in_natural_join(THD *thd, TAB */ if (*ref && !(*ref)->is_autogenerated_name) { + item->is_autogenerated_name= false; item->set_name((*ref)->name, (*ref)->name_length, system_charset_info); item->real_item()->set_name((*ref)->name, (*ref)->name_length, === modified file 'sql/sql_executor.cc' --- a/sql/sql_executor.cc 2012-01-17 14:44:49 +0000 +++ b/sql/sql_executor.cc 2012-01-17 15:43:31 +0000 @@ -2050,6 +2050,15 @@ sub_select(JOIN *join,JOIN_TAB *join_tab /* Set first_unmatched for the last inner table of this group */ join_tab->last_inner->first_unmatched= join_tab; } + if (join_tab->loosescan_match_tab) + { + /* + join_tab is the first table of a LooseScan range. Reset the LooseScan + matching for this round of execution. + */ + join_tab->loosescan_match_tab->found_match= false; + } + join->thd->get_stmt_da()->reset_current_row_for_warning(); /* Materialize table prior reading it */ @@ -2234,8 +2243,9 @@ evaluate_join_record(JOIN *join, JOIN_TA DBUG_ENTER("evaluate_join_record"); DBUG_PRINT("enter", - ("evaluate_join_record join: %p join_tab: %p" - " cond: %p error: %d", join, join_tab, condition, error)); + ("join: %p join_tab index: %d table: %s cond: %p error: %d", + join, static_cast(join_tab - join_tab->join->join_tab), + join_tab->table->alias, condition, error)); if (error > 0 || (join->thd->is_error())) // Fatal error DBUG_RETURN(NESTED_LOOP_ERROR); if (error < 0) @@ -2642,10 +2652,10 @@ join_read_const_table(JOIN_TAB *tab, POS } } - // We cannot handle outer-joined tables with expensive join conditions here: - DBUG_ASSERT(!(*tab->on_expr_ref && (*tab->on_expr_ref)->is_expensive())); if (*tab->on_expr_ref && !table->null_row) { + // We cannot handle outer-joined tables with expensive join conditions here: + DBUG_ASSERT(!(*tab->on_expr_ref)->is_expensive()); if ((table->null_row= test((*tab->on_expr_ref)->val_int() == 0))) mark_as_null_row(table); } === modified file 'sql/sql_optimizer.cc' --- a/sql/sql_optimizer.cc 2012-01-16 09:01:53 +0000 +++ b/sql/sql_optimizer.cc 2012-01-17 15:43:31 +0000 @@ -3033,6 +3033,7 @@ const_table_extraction_done: has a real row or a null-extended row in the optimizer phase. We have no possibility to evaluate its join condition at execution time, when it is marked as a system table. + DontEvaluateMaterializedSubqueryTooEarly */ if (table->file->stats.records <= 1L && // 1 (table->file->ha_table_flags() & HA_STATS_RECORDS_IS_EXACT) && // 1 @@ -3970,7 +3971,7 @@ static bool find_eq_ref_candidate(TABLE keyuse++; } while (keyuse->key == key && keyuse->table == table); - if (bound_parts == PREV_BITS(uint, keyinfo->key_parts)) + if (bound_parts == LOWER_BITS(uint, keyinfo->key_parts)) return TRUE; if (keyuse->table != table) return FALSE; === modified file 'sql/sql_planner.cc' --- a/sql/sql_planner.cc 2012-01-11 13:29:29 +0000 +++ b/sql/sql_planner.cc 2012-01-16 12:51:06 +0000 @@ -176,6 +176,10 @@ private: /* Accumulated properties of ref access we're now considering: */ ulonglong handled_sj_equalities; key_part_map loose_scan_keyparts; + /** + Biggest index (starting at 0) of keyparts used for the "handled", not + "bound", equalities. + */ uint max_loose_keypart; bool part1_conds_met; @@ -305,9 +309,9 @@ public: */ if (try_loosescan && // (1) (handled_sj_equalities | bound_sj_equalities) == // (2) - PREV_BITS(ulonglong, - s->emb_sj_nest->nested_join->sj_inner_exprs.elements)&& // (2) - (PREV_BITS(key_part_map, max_loose_keypart+1) & // (3) + LOWER_BITS(ulonglong, + s->emb_sj_nest->nested_join->sj_inner_exprs.elements)&& // (2) + (LOWER_BITS(key_part_map, max_loose_keypart+1) & // (3) (found_part | loose_scan_keyparts)) == // (3) (found_part | loose_scan_keyparts) && // (3) !key_uses_partial_cols(s->table, key)) @@ -631,7 +635,7 @@ void Optimize_table_order::best_access_p loose_scan_opt.check_ref_access_part1(s, key, start_key, found_part); /* Check if we found full key */ - if (found_part == PREV_BITS(uint,keyinfo->key_parts) && + if (found_part == LOWER_BITS(uint,keyinfo->key_parts) && !ref_or_null_part) { /* use eq key */ max_key_part= (uint) ~0; @@ -721,7 +725,7 @@ void Optimize_table_order::best_access_p */ if ((found_part & 1) && (!(table->file->index_flags(key, 0, 0) & HA_ONLY_WHOLE_INDEX) || - found_part == PREV_BITS(uint,keyinfo->key_parts))) + found_part == LOWER_BITS(uint,keyinfo->key_parts))) { max_key_part= max_part_bit(found_part); /* === modified file 'sql/sql_select.h' --- a/sql/sql_select.h 2012-01-11 13:29:29 +0000 +++ b/sql/sql_select.h 2012-01-16 12:51:06 +0000 @@ -33,7 +33,12 @@ #include "mem_root_array.h" #include "sql_executor.h" -#define PREV_BITS(type,A) ((type) (((type) 1 << (A)) -1)) +/** + Returns a constant of type 'type' with the 'A' lowest-weight bits set. + Example: LOWER_BITS(uint, 3) == 7. + Requirement: A < sizeof(type) * 8. +*/ +#define LOWER_BITS(type,A) ((type) (((type) 1 << (A)) -1)) /* Values in optimize */ #define KEY_OPTIMIZE_EXISTS 1 No bundle (reason: useless for push emails).