From: Marc Alff
Date: January 17 2012 4:31pm
Subject: bzr push into mysql-trunk-wl5259 branch (marc.alff:3351 to 3352)
List-Archive: http://lists.mysql.com/commits/142442
Message-Id: <201201171631.q0HGV80m002427@acsmt356.oracle.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit

 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).