3352 Marc Alff 2012-01-17 [merge]
Merge mysql-trunk --> mysql-trunk-wl5259
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/r/user_var.result
mysql-test/t/group_by.test
mysql-test/t/user_var.test
sql/item_func.h
sql/item_subselect.cc
sql/sql_base.cc
sql/sql_executor.cc
sql/sql_optimizer.cc
sql/sql_planner.cc
sql/sql_select.h
3351 Marc Alff 2012-01-17 [merge]
Merge + work in progress
added:
mysql-test/suite/perfschema/r/ddl_host_cache.result
mysql-test/suite/perfschema/r/dml_host_cache.result
mysql-test/suite/perfschema/t/ddl_host_cache.test
mysql-test/suite/perfschema/t/dml_host_cache.test
mysql-test/suite/sys_vars/r/innodb_change_buffering_debug_basic.result
mysql-test/suite/sys_vars/r/innodb_doublewrite_batch_size_basic.result
mysql-test/suite/sys_vars/r/innodb_page_hash_locks_basic.result
mysql-test/suite/sys_vars/r/innodb_trx_rseg_n_slots_debug_basic.result
mysql-test/suite/sys_vars/t/innodb_change_buffering_debug_basic.test
mysql-test/suite/sys_vars/t/innodb_doublewrite_batch_size_basic.test
mysql-test/suite/sys_vars/t/innodb_page_hash_locks_basic.test
mysql-test/suite/sys_vars/t/innodb_trx_rseg_n_slots_debug_basic.test
modified:
CMakeLists.txt
include/my_global.h
mysql-test/extra/rpl_tests/rpl_extra_col_master.test
mysql-test/r/ctype_many.result
mysql-test/r/func_time.result
mysql-test/suite/funcs_1/r/is_columns_mysql.result
mysql-test/suite/funcs_1/r/is_columns_mysql_embedded.result
mysql-test/suite/funcs_1/r/is_tables_mysql.result
mysql-test/suite/funcs_1/r/is_tables_mysql_embedded.result
mysql-test/suite/sys_vars/r/all_vars.result
mysql-test/suite/sys_vars/r/character_sets_dir_basic.result
mysql-test/suite/sys_vars/r/plugin_dir_basic.result
mysql-test/suite/sys_vars/t/all_vars.test
mysql-test/suite/sys_vars/t/character_sets_dir_basic.test
mysql-test/t/ctype_many.test
mysql-test/t/func_time.test
scripts/mysql_system_tables.sql
scripts/mysqld_safe.sh
scripts/mysqlhotcopy.sh
sql/item.cc
sql/item_cmpfunc.cc
sql/item_cmpfunc.h
sql/mysqld.cc
sql/sql_optimizer.cc
sql/sql_plugin.cc
sql/sql_udf.cc
storage/innobase/buf/buf0buf.cc
storage/innobase/dict/dict0stats.cc
storage/innobase/ibuf/ibuf0ibuf.cc
storage/perfschema/table_host_cache.cc
storage/perfschema/table_host_cache.h
=== 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 <derived3> 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/r/user_var.result'
--- a/mysql-test/r/user_var.result 2011-10-27 08:08:46 +0000
+++ b/mysql-test/r/user_var.result 2012-01-17 14:44:49 +0000
@@ -493,3 +493,26 @@ GROUP BY @b:=(SELECT COUNT(*) > t2.a);
@a:=MIN(t1.a)
1
DROP TABLE t1;
+CREATE TABLE t1(a INT) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (0);
+SELECT DISTINCT POW(COUNT(*), @a:=(SELECT 1 FROM t1 LEFT JOIN t1 AS t2 ON @a))
+AS b FROM t1 GROUP BY a;
+b
+1
+SELECT @a;
+@a
+1
+DROP TABLE t1;
+CREATE TABLE t1(f1 INT, f2 INT);
+INSERT INTO t1 VALUES (1,2),(2,3),(3,1);
+CREATE TABLE t2(a INT);
+INSERT INTO t2 VALUES (1);
+SET @var=NULL;
+SELECT @var:=(SELECT f2 FROM t2 WHERE @var) FROM t1 GROUP BY f1 ORDER BY f2 DESC
+LIMIT 1;
+@var:=(SELECT f2 FROM t2 WHERE @var)
+NULL
+SELECT @var;
+@var
+NULL
+DROP TABLE t1, t2;
=== 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 'mysql-test/t/user_var.test'
--- a/mysql-test/t/user_var.test 2011-10-27 08:08:46 +0000
+++ b/mysql-test/t/user_var.test 2012-01-17 14:44:49 +0000
@@ -415,3 +415,24 @@ INSERT INTO t1 VALUES (1), (2);
SELECT DISTINCT @a:=MIN(t1.a) FROM t1, t1 AS t2
GROUP BY @b:=(SELECT COUNT(*) > t2.a);
DROP TABLE t1;
+
+#
+# Bug #11764371 57196: MORE FUN WITH ASSERTION: !TABLE->FILE ||
+# TABLE->FILE->INITED == HANDLER::
+#
+
+CREATE TABLE t1(a INT) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (0);
+SELECT DISTINCT POW(COUNT(*), @a:=(SELECT 1 FROM t1 LEFT JOIN t1 AS t2 ON @a))
+AS b FROM t1 GROUP BY a;
+SELECT @a;
+DROP TABLE t1;
+CREATE TABLE t1(f1 INT, f2 INT);
+INSERT INTO t1 VALUES (1,2),(2,3),(3,1);
+CREATE TABLE t2(a INT);
+INSERT INTO t2 VALUES (1);
+SET @var=NULL;
+SELECT @var:=(SELECT f2 FROM t2 WHERE @var) FROM t1 GROUP BY f1 ORDER BY f2 DESC
+LIMIT 1;
+SELECT @var;
+DROP TABLE t1, t2;
=== modified file 'sql/item_func.h'
--- a/sql/item_func.h 2011-11-17 13:41:28 +0000
+++ b/sql/item_func.h 2012-01-17 14:44:49 +0000
@@ -1555,6 +1555,7 @@ class Item_var_func :public Item_func
{
public:
Item_var_func() :Item_func() { }
+ Item_var_func(THD *thd, Item_var_func *item) :Item_func(thd, item) { }
Item_var_func(Item *a) :Item_func(a) { }
bool get_date(MYSQL_TIME *ltime, uint fuzzydate)
{
@@ -1604,6 +1605,12 @@ public:
:Item_var_func(b), cached_result_type(INT_RESULT),
entry(NULL), entry_thread_id(0), name(a)
{}
+ Item_func_set_user_var(THD *thd, Item_func_set_user_var *item)
+ :Item_var_func(thd, item), cached_result_type(item->cached_result_type),
+ entry(item->entry), entry_thread_id(item->entry_thread_id),
+ value(item->value), decimal_buff(item->decimal_buff),
+ null_item(item->null_item), save_result(item->save_result), name(item->name)
+ {}
enum Functype functype() const { return SUSERVAR_FUNC; }
double val_real();
longlong val_int();
=== 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-13 09:33:13 +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<int>(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);
}
@@ -4670,64 +4680,88 @@ change_to_use_tmp_fields(THD *thd, Ref_p
res_selected_fields.empty();
res_all_fields.empty();
- uint i, border= all_fields.elements - elements;
- for (i= 0; (item= it++); i++)
+ uint border= all_fields.elements - elements;
+ for (uint i= 0; (item= it++); i++)
{
Field *field;
-
- if ((item->with_sum_func && item->type() != Item::SUM_FUNC_ITEM) ||
- (item->type() == Item::FUNC_ITEM &&
- ((Item_func*)item)->functype() == Item_func::SUSERVAR_FUNC))
+ if (item->with_sum_func && item->type() != Item::SUM_FUNC_ITEM)
item_field= item;
- else
+ else if (item->type() == Item::FIELD_ITEM)
+ item_field= item->get_tmp_table_item(thd);
+ else if (item->type() == Item::FUNC_ITEM &&
+ ((Item_func*)item)->functype() == Item_func::SUSERVAR_FUNC)
{
- if (item->type() == Item::FIELD_ITEM)
+ field= item->get_tmp_table_field();
+ if (field != NULL)
{
- item_field= item->get_tmp_table_item(thd);
+ /*
+ Replace "@:=<expression>" with "@:=<tmp table column>". Otherwise, we
+ would re-evaluate <expression>, and if expression were a subquery, this
+ would access already-unlocked tables.
+ */
+ Item_func_set_user_var* suv=
+ new Item_func_set_user_var(thd, (Item_func_set_user_var*) item);
+ Item_field *new_field= new Item_field(field);
+ if (!suv || !new_field)
+ DBUG_RETURN(true); // Fatal error
+ /*
+ We are replacing the argument of Item_func_set_user_var after its value
+ has been read. The argument's null_value should be set by now, so we
+ must set it explicitly for the replacement argument since the
+ null_value may be read without any preceeding call to val_*().
+ */
+ new_field->update_null_value();
+ List<Item> list;
+ list.push_back(new_field);
+ suv->set_arguments(list);
+ item_field= suv;
+ }
+ else
+ item_field= item;
+ }
+ else if ((field= item->get_tmp_table_field()))
+ {
+ if (item->type() == Item::SUM_FUNC_ITEM && field->table->group)
+ item_field= ((Item_sum*) item)->result_item(field);
+ else
+ item_field= (Item*) new Item_field(field);
+ if (!item_field)
+ DBUG_RETURN(true); // Fatal error
+
+ if (item->real_item()->type() != Item::FIELD_ITEM)
+ field->orig_table= 0;
+ item_field->name= item->name;
+ if (item->type() == Item::REF_ITEM)
+ {
+ Item_field *ifield= (Item_field *) item_field;
+ Item_ref *iref= (Item_ref *) item;
+ ifield->table_name= iref->table_name;
+ ifield->db_name= iref->db_name;
}
- else if ((field= item->get_tmp_table_field()))
- {
- if (item->type() == Item::SUM_FUNC_ITEM && field->table->group)
- item_field= ((Item_sum*) item)->result_item(field);
- else
- item_field= (Item*) new Item_field(field);
- if (!item_field)
- DBUG_RETURN(TRUE); // Fatal error
-
- if (item->real_item()->type() != Item::FIELD_ITEM)
- field->orig_table= 0;
- item_field->name= item->name;
- if (item->type() == Item::REF_ITEM)
- {
- Item_field *ifield= (Item_field *) item_field;
- Item_ref *iref= (Item_ref *) item;
- ifield->table_name= iref->table_name;
- ifield->db_name= iref->db_name;
- }
#ifndef DBUG_OFF
- if (!item_field->name)
- {
- char buff[256];
- String str(buff,sizeof(buff),&my_charset_bin);
- str.length(0);
- item->print(&str, QT_ORDINARY);
- item_field->name= sql_strmake(str.ptr(),str.length());
- }
-#endif
+ if (!item_field->name)
+ {
+ char buff[256];
+ String str(buff,sizeof(buff),&my_charset_bin);
+ str.length(0);
+ item->print(&str, QT_ORDINARY);
+ item_field->name= sql_strmake(str.ptr(),str.length());
}
- else
- item_field= item;
+#endif
}
+ else
+ item_field= item;
+
res_all_fields.push_back(item_field);
ref_pointer_array[((i < border)? all_fields.elements-i-1 : i-border)]=
item_field;
}
List_iterator_fast<Item> itr(res_all_fields);
- for (i= 0; i < border; i++)
+ for (uint i= 0; i < border; i++)
itr++;
itr.sublist(res_selected_fields, elements);
- DBUG_RETURN(FALSE);
+ DBUG_RETURN(false);
}
=== 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).
| Thread |
|---|
| • bzr push into mysql-trunk-wl5259 branch (marc.alff:3351 to 3352) | Marc Alff | 18 Jan |
