List:Commits« Previous MessageNext Message »
From:Roy Lyseng Date:August 25 2011 7:02am
Subject:bzr push into mysql-trunk branch (roy.lyseng:3428 to 3429) Bug#12818569
View as plain text  
 3429 Roy Lyseng	2011-08-25
      Bug#12818569: BNL/BKA: Diff nr of rows returned when using IN/ALL+subquery
      
      JOIN::exec() calls create_sort_index() under certain conditions.
      If there is a LIMIT clause on the query, and the tables following
      the one which create_sort_index() is called for do not have any
      conditions, one can apply the LIMIT clause when sorting.
      The function tests whether there is a condition or an active keyuse
      on these tables. Unfortunately, semi-join materialization adds a
      new way of specifying conditions for a table. The fix adds a check
      for semi-join materialization conditions.
      
      Notice that this patch fixes the two first test cases in the bug report.
      The remaining test cases provoke other bugs.
      
      mysql-test/include/subquery.inc
        Added test case for bug#12818569.
      
      mysql-test/r/subquery_sj_all.result
      mysql-test/r/subquery_sj_all_bka.result
      mysql-test/r/subquery_sj_all_bka_nobnl.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_nobnl.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_nobnl.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_nobnl.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_nobnl.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_nobnl.result
      mysql-test/r/subquery_sj_none_bkaunique.result
        Added test case results for bug#12818569.
      
      sql/sql_select.cc
        In JOIN::exec() missing check for materialize-lookup when determining
        whether to run filesort with possibly too low limit.

    modified:
      mysql-test/include/subquery_sj.inc
      mysql-test/r/subquery_sj_all.result
      mysql-test/r/subquery_sj_all_bka.result
      mysql-test/r/subquery_sj_all_bka_nobnl.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_nobnl.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_nobnl.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_nobnl.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_nobnl.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_nobnl.result
      mysql-test/r/subquery_sj_none_bkaunique.result
      sql/sql_select.cc
 3428 Jorgen Loland	2011-08-22
      Recorded result files after merge

    modified:
      mysql-test/suite/opt_trace/r/bugs_no_prot_all.result
      mysql-test/suite/opt_trace/r/bugs_ps_prot_all.result
      mysql-test/suite/opt_trace/r/general_no_prot_all.result
      mysql-test/suite/opt_trace/r/general_ps_prot_all.result
=== modified file 'mysql-test/include/subquery_sj.inc'
--- a/mysql-test/include/subquery_sj.inc	2011-06-27 07:17:26 +0000
+++ b/mysql-test/include/subquery_sj.inc	2011-08-25 06:59:49 +0000
@@ -3907,4 +3907,45 @@ eval $query;
 
 DROP TABLE t1, t2;
 
---echo # End of the test for bug#12603183.
+--echo # End of test for bug#12603183.
+
+--echo #
+--echo # Bug#12818569: Diff nr of rows returned when using IN/ALL+subquery
+--echo #
+
+CREATE TABLE t1 (
+ col_int_key INT NOT NULL,
+ col_datetime_key DATETIME NOT NULL,
+ col_varchar_key VARCHAR(1) NOT NULL,
+ KEY col_int_key (col_int_key),
+ KEY col_datetime_key(col_datetime_key),
+ KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+
+INSERT INTO t1 VALUES
+ (7,'2004-06-06 04:22:12','v'), (0,'2005-11-13 01:12:31','s'),
+ (9,'2002-05-04 01:50:00','l'), (3,'2004-10-27 10:28:45','y'),
+ (4,'2006-07-22 05:24:23','c'), (2,'2002-05-16 21:34:03','i'),
+ (5,'2008-04-17 10:45:30','h'), (3,'2009-04-21 02:58:02','q'),
+ (1,'2008-01-11 11:01:51','a'), (3,'1900-01-01 00:00:00','v'),
+ (6,'2007-05-17 18:24:57','u'), (7,'2007-08-07 00:00:00','s'),
+ (5,'2001-08-28 00:00:00','y'), (1,'2004-04-16 00:27:28','z'),
+ (204,'2005-05-03 07:06:22','h'), (224,'2009-03-11 17:09:50','p'),
+ (9,'2007-12-08 01:54:28','e'), (5,'2009-07-28 18:19:54','i'),
+ (0,'2008-06-08 00:00:00','y'), (3,'2005-02-09 09:20:26','w');
+
+CREATE TABLE t2 (
+ col_varchar_nokey VARCHAR(1) NOT NULL
+) ENGINE=InnoDB;
+
+INSERT INTO t2 VALUES ('v'), ('y'), ('j'), ('c'), ('d'), ('r');
+
+SELECT col_varchar_key
+FROM t1
+WHERE col_varchar_key IN (SELECT col_varchar_nokey
+                          FROM t2)
+ORDER BY col_datetime_key LIMIT 4;
+
+DROP TABLE t1, t2;
+
+--echo # End of test for bug#12818569.

=== modified file 'mysql-test/r/subquery_sj_all.result'
--- a/mysql-test/r/subquery_sj_all.result	2011-08-03 11:29:20 +0000
+++ b/mysql-test/r/subquery_sj_all.result	2011-08-25 06:59:49 +0000
@@ -6594,5 +6594,43 @@ w
 -- Notice that Materialize-scan algorithm reports wrong result for this query.
 -- This problem will be filed as a separate bug and dealt with in WL#5561.
 DROP TABLE t1, t2;
-# End of the test for bug#12603183.
+# End of test for bug#12603183.
+#
+# Bug#12818569: Diff nr of rows returned when using IN/ALL+subquery
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key),
+KEY col_datetime_key(col_datetime_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'2004-06-06 04:22:12','v'), (0,'2005-11-13 01:12:31','s'),
+(9,'2002-05-04 01:50:00','l'), (3,'2004-10-27 10:28:45','y'),
+(4,'2006-07-22 05:24:23','c'), (2,'2002-05-16 21:34:03','i'),
+(5,'2008-04-17 10:45:30','h'), (3,'2009-04-21 02:58:02','q'),
+(1,'2008-01-11 11:01:51','a'), (3,'1900-01-01 00:00:00','v'),
+(6,'2007-05-17 18:24:57','u'), (7,'2007-08-07 00:00:00','s'),
+(5,'2001-08-28 00:00:00','y'), (1,'2004-04-16 00:27:28','z'),
+(204,'2005-05-03 07:06:22','h'), (224,'2009-03-11 17:09:50','p'),
+(9,'2007-12-08 01:54:28','e'), (5,'2009-07-28 18:19:54','i'),
+(0,'2008-06-08 00:00:00','y'), (3,'2005-02-09 09:20:26','w');
+CREATE TABLE t2 (
+col_varchar_nokey VARCHAR(1) NOT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES ('v'), ('y'), ('j'), ('c'), ('d'), ('r');
+SELECT col_varchar_key
+FROM t1
+WHERE col_varchar_key IN (SELECT col_varchar_nokey
+FROM t2)
+ORDER BY col_datetime_key LIMIT 4;
+col_varchar_key
+v
+y
+v
+y
+DROP TABLE t1, t2;
+# End of test for bug#12818569.
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_all_bka.result'
--- a/mysql-test/r/subquery_sj_all_bka.result	2011-08-18 09:21:45 +0000
+++ b/mysql-test/r/subquery_sj_all_bka.result	2011-08-25 06:59:49 +0000
@@ -6595,6 +6595,44 @@ w
 -- Notice that Materialize-scan algorithm reports wrong result for this query.
 -- This problem will be filed as a separate bug and dealt with in WL#5561.
 DROP TABLE t1, t2;
-# End of the test for bug#12603183.
+# End of test for bug#12603183.
+#
+# Bug#12818569: Diff nr of rows returned when using IN/ALL+subquery
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key),
+KEY col_datetime_key(col_datetime_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'2004-06-06 04:22:12','v'), (0,'2005-11-13 01:12:31','s'),
+(9,'2002-05-04 01:50:00','l'), (3,'2004-10-27 10:28:45','y'),
+(4,'2006-07-22 05:24:23','c'), (2,'2002-05-16 21:34:03','i'),
+(5,'2008-04-17 10:45:30','h'), (3,'2009-04-21 02:58:02','q'),
+(1,'2008-01-11 11:01:51','a'), (3,'1900-01-01 00:00:00','v'),
+(6,'2007-05-17 18:24:57','u'), (7,'2007-08-07 00:00:00','s'),
+(5,'2001-08-28 00:00:00','y'), (1,'2004-04-16 00:27:28','z'),
+(204,'2005-05-03 07:06:22','h'), (224,'2009-03-11 17:09:50','p'),
+(9,'2007-12-08 01:54:28','e'), (5,'2009-07-28 18:19:54','i'),
+(0,'2008-06-08 00:00:00','y'), (3,'2005-02-09 09:20:26','w');
+CREATE TABLE t2 (
+col_varchar_nokey VARCHAR(1) NOT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES ('v'), ('y'), ('j'), ('c'), ('d'), ('r');
+SELECT col_varchar_key
+FROM t1
+WHERE col_varchar_key IN (SELECT col_varchar_nokey
+FROM t2)
+ORDER BY col_datetime_key LIMIT 4;
+col_varchar_key
+v
+y
+v
+y
+DROP TABLE t1, t2;
+# End of test for bug#12818569.
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_all_bka_nobnl.result'
--- a/mysql-test/r/subquery_sj_all_bka_nobnl.result	2011-08-03 11:29:20 +0000
+++ b/mysql-test/r/subquery_sj_all_bka_nobnl.result	2011-08-25 06:59:49 +0000
@@ -6595,6 +6595,44 @@ w
 -- Notice that Materialize-scan algorithm reports wrong result for this query.
 -- This problem will be filed as a separate bug and dealt with in WL#5561.
 DROP TABLE t1, t2;
-# End of the test for bug#12603183.
+# End of test for bug#12603183.
+#
+# Bug#12818569: Diff nr of rows returned when using IN/ALL+subquery
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key),
+KEY col_datetime_key(col_datetime_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'2004-06-06 04:22:12','v'), (0,'2005-11-13 01:12:31','s'),
+(9,'2002-05-04 01:50:00','l'), (3,'2004-10-27 10:28:45','y'),
+(4,'2006-07-22 05:24:23','c'), (2,'2002-05-16 21:34:03','i'),
+(5,'2008-04-17 10:45:30','h'), (3,'2009-04-21 02:58:02','q'),
+(1,'2008-01-11 11:01:51','a'), (3,'1900-01-01 00:00:00','v'),
+(6,'2007-05-17 18:24:57','u'), (7,'2007-08-07 00:00:00','s'),
+(5,'2001-08-28 00:00:00','y'), (1,'2004-04-16 00:27:28','z'),
+(204,'2005-05-03 07:06:22','h'), (224,'2009-03-11 17:09:50','p'),
+(9,'2007-12-08 01:54:28','e'), (5,'2009-07-28 18:19:54','i'),
+(0,'2008-06-08 00:00:00','y'), (3,'2005-02-09 09:20:26','w');
+CREATE TABLE t2 (
+col_varchar_nokey VARCHAR(1) NOT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES ('v'), ('y'), ('j'), ('c'), ('d'), ('r');
+SELECT col_varchar_key
+FROM t1
+WHERE col_varchar_key IN (SELECT col_varchar_nokey
+FROM t2)
+ORDER BY col_datetime_key LIMIT 4;
+col_varchar_key
+v
+y
+v
+y
+DROP TABLE t1, t2;
+# End of test for bug#12818569.
 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	2011-08-18 09:21:45 +0000
+++ b/mysql-test/r/subquery_sj_all_bkaunique.result	2011-08-25 06:59:49 +0000
@@ -6596,6 +6596,44 @@ w
 -- Notice that Materialize-scan algorithm reports wrong result for this query.
 -- This problem will be filed as a separate bug and dealt with in WL#5561.
 DROP TABLE t1, t2;
-# End of the test for bug#12603183.
+# End of test for bug#12603183.
+#
+# Bug#12818569: Diff nr of rows returned when using IN/ALL+subquery
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key),
+KEY col_datetime_key(col_datetime_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'2004-06-06 04:22:12','v'), (0,'2005-11-13 01:12:31','s'),
+(9,'2002-05-04 01:50:00','l'), (3,'2004-10-27 10:28:45','y'),
+(4,'2006-07-22 05:24:23','c'), (2,'2002-05-16 21:34:03','i'),
+(5,'2008-04-17 10:45:30','h'), (3,'2009-04-21 02:58:02','q'),
+(1,'2008-01-11 11:01:51','a'), (3,'1900-01-01 00:00:00','v'),
+(6,'2007-05-17 18:24:57','u'), (7,'2007-08-07 00:00:00','s'),
+(5,'2001-08-28 00:00:00','y'), (1,'2004-04-16 00:27:28','z'),
+(204,'2005-05-03 07:06:22','h'), (224,'2009-03-11 17:09:50','p'),
+(9,'2007-12-08 01:54:28','e'), (5,'2009-07-28 18:19:54','i'),
+(0,'2008-06-08 00:00:00','y'), (3,'2005-02-09 09:20:26','w');
+CREATE TABLE t2 (
+col_varchar_nokey VARCHAR(1) NOT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES ('v'), ('y'), ('j'), ('c'), ('d'), ('r');
+SELECT col_varchar_key
+FROM t1
+WHERE col_varchar_key IN (SELECT col_varchar_nokey
+FROM t2)
+ORDER BY col_datetime_key LIMIT 4;
+col_varchar_key
+v
+y
+v
+y
+DROP TABLE t1, t2;
+# End of test for bug#12818569.
 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	2011-08-19 13:51:50 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed.result	2011-08-25 06:59:49 +0000
@@ -6594,5 +6594,43 @@ w
 -- Notice that Materialize-scan algorithm reports wrong result for this query.
 -- This problem will be filed as a separate bug and dealt with in WL#5561.
 DROP TABLE t1, t2;
-# End of the test for bug#12603183.
+# End of test for bug#12603183.
+#
+# Bug#12818569: Diff nr of rows returned when using IN/ALL+subquery
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key),
+KEY col_datetime_key(col_datetime_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'2004-06-06 04:22:12','v'), (0,'2005-11-13 01:12:31','s'),
+(9,'2002-05-04 01:50:00','l'), (3,'2004-10-27 10:28:45','y'),
+(4,'2006-07-22 05:24:23','c'), (2,'2002-05-16 21:34:03','i'),
+(5,'2008-04-17 10:45:30','h'), (3,'2009-04-21 02:58:02','q'),
+(1,'2008-01-11 11:01:51','a'), (3,'1900-01-01 00:00:00','v'),
+(6,'2007-05-17 18:24:57','u'), (7,'2007-08-07 00:00:00','s'),
+(5,'2001-08-28 00:00:00','y'), (1,'2004-04-16 00:27:28','z'),
+(204,'2005-05-03 07:06:22','h'), (224,'2009-03-11 17:09:50','p'),
+(9,'2007-12-08 01:54:28','e'), (5,'2009-07-28 18:19:54','i'),
+(0,'2008-06-08 00:00:00','y'), (3,'2005-02-09 09:20:26','w');
+CREATE TABLE t2 (
+col_varchar_nokey VARCHAR(1) NOT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES ('v'), ('y'), ('j'), ('c'), ('d'), ('r');
+SELECT col_varchar_key
+FROM t1
+WHERE col_varchar_key IN (SELECT col_varchar_nokey
+FROM t2)
+ORDER BY col_datetime_key LIMIT 4;
+col_varchar_key
+v
+y
+v
+y
+DROP TABLE t1, t2;
+# End of test for bug#12818569.
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_dupsweed_bka.result'
--- a/mysql-test/r/subquery_sj_dupsweed_bka.result	2011-08-19 13:51:50 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bka.result	2011-08-25 06:59:49 +0000
@@ -6595,6 +6595,44 @@ w
 -- Notice that Materialize-scan algorithm reports wrong result for this query.
 -- This problem will be filed as a separate bug and dealt with in WL#5561.
 DROP TABLE t1, t2;
-# End of the test for bug#12603183.
+# End of test for bug#12603183.
+#
+# Bug#12818569: Diff nr of rows returned when using IN/ALL+subquery
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key),
+KEY col_datetime_key(col_datetime_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'2004-06-06 04:22:12','v'), (0,'2005-11-13 01:12:31','s'),
+(9,'2002-05-04 01:50:00','l'), (3,'2004-10-27 10:28:45','y'),
+(4,'2006-07-22 05:24:23','c'), (2,'2002-05-16 21:34:03','i'),
+(5,'2008-04-17 10:45:30','h'), (3,'2009-04-21 02:58:02','q'),
+(1,'2008-01-11 11:01:51','a'), (3,'1900-01-01 00:00:00','v'),
+(6,'2007-05-17 18:24:57','u'), (7,'2007-08-07 00:00:00','s'),
+(5,'2001-08-28 00:00:00','y'), (1,'2004-04-16 00:27:28','z'),
+(204,'2005-05-03 07:06:22','h'), (224,'2009-03-11 17:09:50','p'),
+(9,'2007-12-08 01:54:28','e'), (5,'2009-07-28 18:19:54','i'),
+(0,'2008-06-08 00:00:00','y'), (3,'2005-02-09 09:20:26','w');
+CREATE TABLE t2 (
+col_varchar_nokey VARCHAR(1) NOT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES ('v'), ('y'), ('j'), ('c'), ('d'), ('r');
+SELECT col_varchar_key
+FROM t1
+WHERE col_varchar_key IN (SELECT col_varchar_nokey
+FROM t2)
+ORDER BY col_datetime_key LIMIT 4;
+col_varchar_key
+v
+y
+v
+y
+DROP TABLE t1, t2;
+# End of test for bug#12818569.
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_dupsweed_bka_nobnl.result'
--- a/mysql-test/r/subquery_sj_dupsweed_bka_nobnl.result	2011-08-19 13:51:50 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bka_nobnl.result	2011-08-25 06:59:49 +0000
@@ -6595,6 +6595,44 @@ w
 -- Notice that Materialize-scan algorithm reports wrong result for this query.
 -- This problem will be filed as a separate bug and dealt with in WL#5561.
 DROP TABLE t1, t2;
-# End of the test for bug#12603183.
+# End of test for bug#12603183.
+#
+# Bug#12818569: Diff nr of rows returned when using IN/ALL+subquery
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key),
+KEY col_datetime_key(col_datetime_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'2004-06-06 04:22:12','v'), (0,'2005-11-13 01:12:31','s'),
+(9,'2002-05-04 01:50:00','l'), (3,'2004-10-27 10:28:45','y'),
+(4,'2006-07-22 05:24:23','c'), (2,'2002-05-16 21:34:03','i'),
+(5,'2008-04-17 10:45:30','h'), (3,'2009-04-21 02:58:02','q'),
+(1,'2008-01-11 11:01:51','a'), (3,'1900-01-01 00:00:00','v'),
+(6,'2007-05-17 18:24:57','u'), (7,'2007-08-07 00:00:00','s'),
+(5,'2001-08-28 00:00:00','y'), (1,'2004-04-16 00:27:28','z'),
+(204,'2005-05-03 07:06:22','h'), (224,'2009-03-11 17:09:50','p'),
+(9,'2007-12-08 01:54:28','e'), (5,'2009-07-28 18:19:54','i'),
+(0,'2008-06-08 00:00:00','y'), (3,'2005-02-09 09:20:26','w');
+CREATE TABLE t2 (
+col_varchar_nokey VARCHAR(1) NOT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES ('v'), ('y'), ('j'), ('c'), ('d'), ('r');
+SELECT col_varchar_key
+FROM t1
+WHERE col_varchar_key IN (SELECT col_varchar_nokey
+FROM t2)
+ORDER BY col_datetime_key LIMIT 4;
+col_varchar_key
+v
+y
+v
+y
+DROP TABLE t1, t2;
+# End of test for bug#12818569.
 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	2011-08-19 13:51:50 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bkaunique.result	2011-08-25 06:59:49 +0000
@@ -6596,6 +6596,44 @@ w
 -- Notice that Materialize-scan algorithm reports wrong result for this query.
 -- This problem will be filed as a separate bug and dealt with in WL#5561.
 DROP TABLE t1, t2;
-# End of the test for bug#12603183.
+# End of test for bug#12603183.
+#
+# Bug#12818569: Diff nr of rows returned when using IN/ALL+subquery
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key),
+KEY col_datetime_key(col_datetime_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'2004-06-06 04:22:12','v'), (0,'2005-11-13 01:12:31','s'),
+(9,'2002-05-04 01:50:00','l'), (3,'2004-10-27 10:28:45','y'),
+(4,'2006-07-22 05:24:23','c'), (2,'2002-05-16 21:34:03','i'),
+(5,'2008-04-17 10:45:30','h'), (3,'2009-04-21 02:58:02','q'),
+(1,'2008-01-11 11:01:51','a'), (3,'1900-01-01 00:00:00','v'),
+(6,'2007-05-17 18:24:57','u'), (7,'2007-08-07 00:00:00','s'),
+(5,'2001-08-28 00:00:00','y'), (1,'2004-04-16 00:27:28','z'),
+(204,'2005-05-03 07:06:22','h'), (224,'2009-03-11 17:09:50','p'),
+(9,'2007-12-08 01:54:28','e'), (5,'2009-07-28 18:19:54','i'),
+(0,'2008-06-08 00:00:00','y'), (3,'2005-02-09 09:20:26','w');
+CREATE TABLE t2 (
+col_varchar_nokey VARCHAR(1) NOT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES ('v'), ('y'), ('j'), ('c'), ('d'), ('r');
+SELECT col_varchar_key
+FROM t1
+WHERE col_varchar_key IN (SELECT col_varchar_nokey
+FROM t2)
+ORDER BY col_datetime_key LIMIT 4;
+col_varchar_key
+v
+y
+v
+y
+DROP TABLE t1, t2;
+# End of test for bug#12818569.
 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	2011-08-03 11:29:20 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch.result	2011-08-25 06:59:49 +0000
@@ -6595,7 +6595,45 @@ w
 -- Notice that Materialize-scan algorithm reports wrong result for this query.
 -- This problem will be filed as a separate bug and dealt with in WL#5561.
 DROP TABLE t1, t2;
-# End of the test for bug#12603183.
+# End of test for bug#12603183.
+#
+# Bug#12818569: Diff nr of rows returned when using IN/ALL+subquery
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key),
+KEY col_datetime_key(col_datetime_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'2004-06-06 04:22:12','v'), (0,'2005-11-13 01:12:31','s'),
+(9,'2002-05-04 01:50:00','l'), (3,'2004-10-27 10:28:45','y'),
+(4,'2006-07-22 05:24:23','c'), (2,'2002-05-16 21:34:03','i'),
+(5,'2008-04-17 10:45:30','h'), (3,'2009-04-21 02:58:02','q'),
+(1,'2008-01-11 11:01:51','a'), (3,'1900-01-01 00:00:00','v'),
+(6,'2007-05-17 18:24:57','u'), (7,'2007-08-07 00:00:00','s'),
+(5,'2001-08-28 00:00:00','y'), (1,'2004-04-16 00:27:28','z'),
+(204,'2005-05-03 07:06:22','h'), (224,'2009-03-11 17:09:50','p'),
+(9,'2007-12-08 01:54:28','e'), (5,'2009-07-28 18:19:54','i'),
+(0,'2008-06-08 00:00:00','y'), (3,'2005-02-09 09:20:26','w');
+CREATE TABLE t2 (
+col_varchar_nokey VARCHAR(1) NOT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES ('v'), ('y'), ('j'), ('c'), ('d'), ('r');
+SELECT col_varchar_key
+FROM t1
+WHERE col_varchar_key IN (SELECT col_varchar_nokey
+FROM t2)
+ORDER BY col_datetime_key LIMIT 4;
+col_varchar_key
+v
+y
+v
+y
+DROP TABLE t1, t2;
+# End of test for bug#12818569.
 #
 # Bug#51457 Firstmatch semijoin strategy gives wrong results for
 #           certain query plans

=== modified file 'mysql-test/r/subquery_sj_firstmatch_bka.result'
--- a/mysql-test/r/subquery_sj_firstmatch_bka.result	2011-08-18 09:21:45 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bka.result	2011-08-25 06:59:49 +0000
@@ -6596,7 +6596,45 @@ w
 -- Notice that Materialize-scan algorithm reports wrong result for this query.
 -- This problem will be filed as a separate bug and dealt with in WL#5561.
 DROP TABLE t1, t2;
-# End of the test for bug#12603183.
+# End of test for bug#12603183.
+#
+# Bug#12818569: Diff nr of rows returned when using IN/ALL+subquery
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key),
+KEY col_datetime_key(col_datetime_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'2004-06-06 04:22:12','v'), (0,'2005-11-13 01:12:31','s'),
+(9,'2002-05-04 01:50:00','l'), (3,'2004-10-27 10:28:45','y'),
+(4,'2006-07-22 05:24:23','c'), (2,'2002-05-16 21:34:03','i'),
+(5,'2008-04-17 10:45:30','h'), (3,'2009-04-21 02:58:02','q'),
+(1,'2008-01-11 11:01:51','a'), (3,'1900-01-01 00:00:00','v'),
+(6,'2007-05-17 18:24:57','u'), (7,'2007-08-07 00:00:00','s'),
+(5,'2001-08-28 00:00:00','y'), (1,'2004-04-16 00:27:28','z'),
+(204,'2005-05-03 07:06:22','h'), (224,'2009-03-11 17:09:50','p'),
+(9,'2007-12-08 01:54:28','e'), (5,'2009-07-28 18:19:54','i'),
+(0,'2008-06-08 00:00:00','y'), (3,'2005-02-09 09:20:26','w');
+CREATE TABLE t2 (
+col_varchar_nokey VARCHAR(1) NOT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES ('v'), ('y'), ('j'), ('c'), ('d'), ('r');
+SELECT col_varchar_key
+FROM t1
+WHERE col_varchar_key IN (SELECT col_varchar_nokey
+FROM t2)
+ORDER BY col_datetime_key LIMIT 4;
+col_varchar_key
+v
+y
+v
+y
+DROP TABLE t1, t2;
+# End of test for bug#12818569.
 #
 # Bug#51457 Firstmatch semijoin strategy gives wrong results for
 #           certain query plans

=== modified file 'mysql-test/r/subquery_sj_firstmatch_bka_nobnl.result'
--- a/mysql-test/r/subquery_sj_firstmatch_bka_nobnl.result	2011-08-03 11:29:20 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bka_nobnl.result	2011-08-25 06:59:49 +0000
@@ -6596,7 +6596,45 @@ w
 -- Notice that Materialize-scan algorithm reports wrong result for this query.
 -- This problem will be filed as a separate bug and dealt with in WL#5561.
 DROP TABLE t1, t2;
-# End of the test for bug#12603183.
+# End of test for bug#12603183.
+#
+# Bug#12818569: Diff nr of rows returned when using IN/ALL+subquery
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key),
+KEY col_datetime_key(col_datetime_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'2004-06-06 04:22:12','v'), (0,'2005-11-13 01:12:31','s'),
+(9,'2002-05-04 01:50:00','l'), (3,'2004-10-27 10:28:45','y'),
+(4,'2006-07-22 05:24:23','c'), (2,'2002-05-16 21:34:03','i'),
+(5,'2008-04-17 10:45:30','h'), (3,'2009-04-21 02:58:02','q'),
+(1,'2008-01-11 11:01:51','a'), (3,'1900-01-01 00:00:00','v'),
+(6,'2007-05-17 18:24:57','u'), (7,'2007-08-07 00:00:00','s'),
+(5,'2001-08-28 00:00:00','y'), (1,'2004-04-16 00:27:28','z'),
+(204,'2005-05-03 07:06:22','h'), (224,'2009-03-11 17:09:50','p'),
+(9,'2007-12-08 01:54:28','e'), (5,'2009-07-28 18:19:54','i'),
+(0,'2008-06-08 00:00:00','y'), (3,'2005-02-09 09:20:26','w');
+CREATE TABLE t2 (
+col_varchar_nokey VARCHAR(1) NOT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES ('v'), ('y'), ('j'), ('c'), ('d'), ('r');
+SELECT col_varchar_key
+FROM t1
+WHERE col_varchar_key IN (SELECT col_varchar_nokey
+FROM t2)
+ORDER BY col_datetime_key LIMIT 4;
+col_varchar_key
+v
+y
+v
+y
+DROP TABLE t1, t2;
+# End of test for bug#12818569.
 #
 # Bug#51457 Firstmatch semijoin strategy gives wrong results for
 #           certain query plans

=== modified file 'mysql-test/r/subquery_sj_firstmatch_bkaunique.result'
--- a/mysql-test/r/subquery_sj_firstmatch_bkaunique.result	2011-08-18 09:21:45 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bkaunique.result	2011-08-25 06:59:49 +0000
@@ -6597,7 +6597,45 @@ w
 -- Notice that Materialize-scan algorithm reports wrong result for this query.
 -- This problem will be filed as a separate bug and dealt with in WL#5561.
 DROP TABLE t1, t2;
-# End of the test for bug#12603183.
+# End of test for bug#12603183.
+#
+# Bug#12818569: Diff nr of rows returned when using IN/ALL+subquery
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key),
+KEY col_datetime_key(col_datetime_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'2004-06-06 04:22:12','v'), (0,'2005-11-13 01:12:31','s'),
+(9,'2002-05-04 01:50:00','l'), (3,'2004-10-27 10:28:45','y'),
+(4,'2006-07-22 05:24:23','c'), (2,'2002-05-16 21:34:03','i'),
+(5,'2008-04-17 10:45:30','h'), (3,'2009-04-21 02:58:02','q'),
+(1,'2008-01-11 11:01:51','a'), (3,'1900-01-01 00:00:00','v'),
+(6,'2007-05-17 18:24:57','u'), (7,'2007-08-07 00:00:00','s'),
+(5,'2001-08-28 00:00:00','y'), (1,'2004-04-16 00:27:28','z'),
+(204,'2005-05-03 07:06:22','h'), (224,'2009-03-11 17:09:50','p'),
+(9,'2007-12-08 01:54:28','e'), (5,'2009-07-28 18:19:54','i'),
+(0,'2008-06-08 00:00:00','y'), (3,'2005-02-09 09:20:26','w');
+CREATE TABLE t2 (
+col_varchar_nokey VARCHAR(1) NOT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES ('v'), ('y'), ('j'), ('c'), ('d'), ('r');
+SELECT col_varchar_key
+FROM t1
+WHERE col_varchar_key IN (SELECT col_varchar_nokey
+FROM t2)
+ORDER BY col_datetime_key LIMIT 4;
+col_varchar_key
+v
+y
+v
+y
+DROP TABLE t1, t2;
+# End of test for bug#12818569.
 #
 # Bug#51457 Firstmatch semijoin strategy gives wrong results for
 #           certain query plans

=== modified file 'mysql-test/r/subquery_sj_loosescan.result'
--- a/mysql-test/r/subquery_sj_loosescan.result	2011-08-19 13:51:50 +0000
+++ b/mysql-test/r/subquery_sj_loosescan.result	2011-08-25 06:59:49 +0000
@@ -6595,5 +6595,43 @@ w
 -- Notice that Materialize-scan algorithm reports wrong result for this query.
 -- This problem will be filed as a separate bug and dealt with in WL#5561.
 DROP TABLE t1, t2;
-# End of the test for bug#12603183.
+# End of test for bug#12603183.
+#
+# Bug#12818569: Diff nr of rows returned when using IN/ALL+subquery
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key),
+KEY col_datetime_key(col_datetime_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'2004-06-06 04:22:12','v'), (0,'2005-11-13 01:12:31','s'),
+(9,'2002-05-04 01:50:00','l'), (3,'2004-10-27 10:28:45','y'),
+(4,'2006-07-22 05:24:23','c'), (2,'2002-05-16 21:34:03','i'),
+(5,'2008-04-17 10:45:30','h'), (3,'2009-04-21 02:58:02','q'),
+(1,'2008-01-11 11:01:51','a'), (3,'1900-01-01 00:00:00','v'),
+(6,'2007-05-17 18:24:57','u'), (7,'2007-08-07 00:00:00','s'),
+(5,'2001-08-28 00:00:00','y'), (1,'2004-04-16 00:27:28','z'),
+(204,'2005-05-03 07:06:22','h'), (224,'2009-03-11 17:09:50','p'),
+(9,'2007-12-08 01:54:28','e'), (5,'2009-07-28 18:19:54','i'),
+(0,'2008-06-08 00:00:00','y'), (3,'2005-02-09 09:20:26','w');
+CREATE TABLE t2 (
+col_varchar_nokey VARCHAR(1) NOT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES ('v'), ('y'), ('j'), ('c'), ('d'), ('r');
+SELECT col_varchar_key
+FROM t1
+WHERE col_varchar_key IN (SELECT col_varchar_nokey
+FROM t2)
+ORDER BY col_datetime_key LIMIT 4;
+col_varchar_key
+v
+y
+v
+y
+DROP TABLE t1, t2;
+# End of test for bug#12818569.
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_loosescan_bka.result'
--- a/mysql-test/r/subquery_sj_loosescan_bka.result	2011-08-19 13:51:50 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bka.result	2011-08-25 06:59:49 +0000
@@ -6596,6 +6596,44 @@ w
 -- Notice that Materialize-scan algorithm reports wrong result for this query.
 -- This problem will be filed as a separate bug and dealt with in WL#5561.
 DROP TABLE t1, t2;
-# End of the test for bug#12603183.
+# End of test for bug#12603183.
+#
+# Bug#12818569: Diff nr of rows returned when using IN/ALL+subquery
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key),
+KEY col_datetime_key(col_datetime_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'2004-06-06 04:22:12','v'), (0,'2005-11-13 01:12:31','s'),
+(9,'2002-05-04 01:50:00','l'), (3,'2004-10-27 10:28:45','y'),
+(4,'2006-07-22 05:24:23','c'), (2,'2002-05-16 21:34:03','i'),
+(5,'2008-04-17 10:45:30','h'), (3,'2009-04-21 02:58:02','q'),
+(1,'2008-01-11 11:01:51','a'), (3,'1900-01-01 00:00:00','v'),
+(6,'2007-05-17 18:24:57','u'), (7,'2007-08-07 00:00:00','s'),
+(5,'2001-08-28 00:00:00','y'), (1,'2004-04-16 00:27:28','z'),
+(204,'2005-05-03 07:06:22','h'), (224,'2009-03-11 17:09:50','p'),
+(9,'2007-12-08 01:54:28','e'), (5,'2009-07-28 18:19:54','i'),
+(0,'2008-06-08 00:00:00','y'), (3,'2005-02-09 09:20:26','w');
+CREATE TABLE t2 (
+col_varchar_nokey VARCHAR(1) NOT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES ('v'), ('y'), ('j'), ('c'), ('d'), ('r');
+SELECT col_varchar_key
+FROM t1
+WHERE col_varchar_key IN (SELECT col_varchar_nokey
+FROM t2)
+ORDER BY col_datetime_key LIMIT 4;
+col_varchar_key
+v
+y
+v
+y
+DROP TABLE t1, t2;
+# End of test for bug#12818569.
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_loosescan_bka_nobnl.result'
--- a/mysql-test/r/subquery_sj_loosescan_bka_nobnl.result	2011-08-19 13:51:50 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bka_nobnl.result	2011-08-25 06:59:49 +0000
@@ -6596,6 +6596,44 @@ w
 -- Notice that Materialize-scan algorithm reports wrong result for this query.
 -- This problem will be filed as a separate bug and dealt with in WL#5561.
 DROP TABLE t1, t2;
-# End of the test for bug#12603183.
+# End of test for bug#12603183.
+#
+# Bug#12818569: Diff nr of rows returned when using IN/ALL+subquery
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key),
+KEY col_datetime_key(col_datetime_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'2004-06-06 04:22:12','v'), (0,'2005-11-13 01:12:31','s'),
+(9,'2002-05-04 01:50:00','l'), (3,'2004-10-27 10:28:45','y'),
+(4,'2006-07-22 05:24:23','c'), (2,'2002-05-16 21:34:03','i'),
+(5,'2008-04-17 10:45:30','h'), (3,'2009-04-21 02:58:02','q'),
+(1,'2008-01-11 11:01:51','a'), (3,'1900-01-01 00:00:00','v'),
+(6,'2007-05-17 18:24:57','u'), (7,'2007-08-07 00:00:00','s'),
+(5,'2001-08-28 00:00:00','y'), (1,'2004-04-16 00:27:28','z'),
+(204,'2005-05-03 07:06:22','h'), (224,'2009-03-11 17:09:50','p'),
+(9,'2007-12-08 01:54:28','e'), (5,'2009-07-28 18:19:54','i'),
+(0,'2008-06-08 00:00:00','y'), (3,'2005-02-09 09:20:26','w');
+CREATE TABLE t2 (
+col_varchar_nokey VARCHAR(1) NOT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES ('v'), ('y'), ('j'), ('c'), ('d'), ('r');
+SELECT col_varchar_key
+FROM t1
+WHERE col_varchar_key IN (SELECT col_varchar_nokey
+FROM t2)
+ORDER BY col_datetime_key LIMIT 4;
+col_varchar_key
+v
+y
+v
+y
+DROP TABLE t1, t2;
+# End of test for bug#12818569.
 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	2011-08-19 13:51:50 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bkaunique.result	2011-08-25 06:59:49 +0000
@@ -6597,6 +6597,44 @@ w
 -- Notice that Materialize-scan algorithm reports wrong result for this query.
 -- This problem will be filed as a separate bug and dealt with in WL#5561.
 DROP TABLE t1, t2;
-# End of the test for bug#12603183.
+# End of test for bug#12603183.
+#
+# Bug#12818569: Diff nr of rows returned when using IN/ALL+subquery
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key),
+KEY col_datetime_key(col_datetime_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'2004-06-06 04:22:12','v'), (0,'2005-11-13 01:12:31','s'),
+(9,'2002-05-04 01:50:00','l'), (3,'2004-10-27 10:28:45','y'),
+(4,'2006-07-22 05:24:23','c'), (2,'2002-05-16 21:34:03','i'),
+(5,'2008-04-17 10:45:30','h'), (3,'2009-04-21 02:58:02','q'),
+(1,'2008-01-11 11:01:51','a'), (3,'1900-01-01 00:00:00','v'),
+(6,'2007-05-17 18:24:57','u'), (7,'2007-08-07 00:00:00','s'),
+(5,'2001-08-28 00:00:00','y'), (1,'2004-04-16 00:27:28','z'),
+(204,'2005-05-03 07:06:22','h'), (224,'2009-03-11 17:09:50','p'),
+(9,'2007-12-08 01:54:28','e'), (5,'2009-07-28 18:19:54','i'),
+(0,'2008-06-08 00:00:00','y'), (3,'2005-02-09 09:20:26','w');
+CREATE TABLE t2 (
+col_varchar_nokey VARCHAR(1) NOT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES ('v'), ('y'), ('j'), ('c'), ('d'), ('r');
+SELECT col_varchar_key
+FROM t1
+WHERE col_varchar_key IN (SELECT col_varchar_nokey
+FROM t2)
+ORDER BY col_datetime_key LIMIT 4;
+col_varchar_key
+v
+y
+v
+y
+DROP TABLE t1, t2;
+# End of test for bug#12818569.
 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	2011-08-19 13:51:50 +0000
+++ b/mysql-test/r/subquery_sj_mat.result	2011-08-25 06:59:49 +0000
@@ -6594,5 +6594,43 @@ w
 -- Notice that Materialize-scan algorithm reports wrong result for this query.
 -- This problem will be filed as a separate bug and dealt with in WL#5561.
 DROP TABLE t1, t2;
-# End of the test for bug#12603183.
+# End of test for bug#12603183.
+#
+# Bug#12818569: Diff nr of rows returned when using IN/ALL+subquery
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key),
+KEY col_datetime_key(col_datetime_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'2004-06-06 04:22:12','v'), (0,'2005-11-13 01:12:31','s'),
+(9,'2002-05-04 01:50:00','l'), (3,'2004-10-27 10:28:45','y'),
+(4,'2006-07-22 05:24:23','c'), (2,'2002-05-16 21:34:03','i'),
+(5,'2008-04-17 10:45:30','h'), (3,'2009-04-21 02:58:02','q'),
+(1,'2008-01-11 11:01:51','a'), (3,'1900-01-01 00:00:00','v'),
+(6,'2007-05-17 18:24:57','u'), (7,'2007-08-07 00:00:00','s'),
+(5,'2001-08-28 00:00:00','y'), (1,'2004-04-16 00:27:28','z'),
+(204,'2005-05-03 07:06:22','h'), (224,'2009-03-11 17:09:50','p'),
+(9,'2007-12-08 01:54:28','e'), (5,'2009-07-28 18:19:54','i'),
+(0,'2008-06-08 00:00:00','y'), (3,'2005-02-09 09:20:26','w');
+CREATE TABLE t2 (
+col_varchar_nokey VARCHAR(1) NOT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES ('v'), ('y'), ('j'), ('c'), ('d'), ('r');
+SELECT col_varchar_key
+FROM t1
+WHERE col_varchar_key IN (SELECT col_varchar_nokey
+FROM t2)
+ORDER BY col_datetime_key LIMIT 4;
+col_varchar_key
+v
+y
+v
+y
+DROP TABLE t1, t2;
+# End of test for bug#12818569.
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_mat_bka.result'
--- a/mysql-test/r/subquery_sj_mat_bka.result	2011-08-19 13:51:50 +0000
+++ b/mysql-test/r/subquery_sj_mat_bka.result	2011-08-25 06:59:49 +0000
@@ -6595,6 +6595,44 @@ w
 -- Notice that Materialize-scan algorithm reports wrong result for this query.
 -- This problem will be filed as a separate bug and dealt with in WL#5561.
 DROP TABLE t1, t2;
-# End of the test for bug#12603183.
+# End of test for bug#12603183.
+#
+# Bug#12818569: Diff nr of rows returned when using IN/ALL+subquery
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key),
+KEY col_datetime_key(col_datetime_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'2004-06-06 04:22:12','v'), (0,'2005-11-13 01:12:31','s'),
+(9,'2002-05-04 01:50:00','l'), (3,'2004-10-27 10:28:45','y'),
+(4,'2006-07-22 05:24:23','c'), (2,'2002-05-16 21:34:03','i'),
+(5,'2008-04-17 10:45:30','h'), (3,'2009-04-21 02:58:02','q'),
+(1,'2008-01-11 11:01:51','a'), (3,'1900-01-01 00:00:00','v'),
+(6,'2007-05-17 18:24:57','u'), (7,'2007-08-07 00:00:00','s'),
+(5,'2001-08-28 00:00:00','y'), (1,'2004-04-16 00:27:28','z'),
+(204,'2005-05-03 07:06:22','h'), (224,'2009-03-11 17:09:50','p'),
+(9,'2007-12-08 01:54:28','e'), (5,'2009-07-28 18:19:54','i'),
+(0,'2008-06-08 00:00:00','y'), (3,'2005-02-09 09:20:26','w');
+CREATE TABLE t2 (
+col_varchar_nokey VARCHAR(1) NOT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES ('v'), ('y'), ('j'), ('c'), ('d'), ('r');
+SELECT col_varchar_key
+FROM t1
+WHERE col_varchar_key IN (SELECT col_varchar_nokey
+FROM t2)
+ORDER BY col_datetime_key LIMIT 4;
+col_varchar_key
+v
+y
+v
+y
+DROP TABLE t1, t2;
+# End of test for bug#12818569.
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_mat_bka_nobnl.result'
--- a/mysql-test/r/subquery_sj_mat_bka_nobnl.result	2011-08-03 11:29:20 +0000
+++ b/mysql-test/r/subquery_sj_mat_bka_nobnl.result	2011-08-25 06:59:49 +0000
@@ -6595,6 +6595,44 @@ w
 -- Notice that Materialize-scan algorithm reports wrong result for this query.
 -- This problem will be filed as a separate bug and dealt with in WL#5561.
 DROP TABLE t1, t2;
-# End of the test for bug#12603183.
+# End of test for bug#12603183.
+#
+# Bug#12818569: Diff nr of rows returned when using IN/ALL+subquery
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key),
+KEY col_datetime_key(col_datetime_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'2004-06-06 04:22:12','v'), (0,'2005-11-13 01:12:31','s'),
+(9,'2002-05-04 01:50:00','l'), (3,'2004-10-27 10:28:45','y'),
+(4,'2006-07-22 05:24:23','c'), (2,'2002-05-16 21:34:03','i'),
+(5,'2008-04-17 10:45:30','h'), (3,'2009-04-21 02:58:02','q'),
+(1,'2008-01-11 11:01:51','a'), (3,'1900-01-01 00:00:00','v'),
+(6,'2007-05-17 18:24:57','u'), (7,'2007-08-07 00:00:00','s'),
+(5,'2001-08-28 00:00:00','y'), (1,'2004-04-16 00:27:28','z'),
+(204,'2005-05-03 07:06:22','h'), (224,'2009-03-11 17:09:50','p'),
+(9,'2007-12-08 01:54:28','e'), (5,'2009-07-28 18:19:54','i'),
+(0,'2008-06-08 00:00:00','y'), (3,'2005-02-09 09:20:26','w');
+CREATE TABLE t2 (
+col_varchar_nokey VARCHAR(1) NOT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES ('v'), ('y'), ('j'), ('c'), ('d'), ('r');
+SELECT col_varchar_key
+FROM t1
+WHERE col_varchar_key IN (SELECT col_varchar_nokey
+FROM t2)
+ORDER BY col_datetime_key LIMIT 4;
+col_varchar_key
+v
+y
+v
+y
+DROP TABLE t1, t2;
+# End of test for bug#12818569.
 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	2011-08-19 13:51:50 +0000
+++ b/mysql-test/r/subquery_sj_mat_bkaunique.result	2011-08-25 06:59:49 +0000
@@ -6596,6 +6596,44 @@ w
 -- Notice that Materialize-scan algorithm reports wrong result for this query.
 -- This problem will be filed as a separate bug and dealt with in WL#5561.
 DROP TABLE t1, t2;
-# End of the test for bug#12603183.
+# End of test for bug#12603183.
+#
+# Bug#12818569: Diff nr of rows returned when using IN/ALL+subquery
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key),
+KEY col_datetime_key(col_datetime_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'2004-06-06 04:22:12','v'), (0,'2005-11-13 01:12:31','s'),
+(9,'2002-05-04 01:50:00','l'), (3,'2004-10-27 10:28:45','y'),
+(4,'2006-07-22 05:24:23','c'), (2,'2002-05-16 21:34:03','i'),
+(5,'2008-04-17 10:45:30','h'), (3,'2009-04-21 02:58:02','q'),
+(1,'2008-01-11 11:01:51','a'), (3,'1900-01-01 00:00:00','v'),
+(6,'2007-05-17 18:24:57','u'), (7,'2007-08-07 00:00:00','s'),
+(5,'2001-08-28 00:00:00','y'), (1,'2004-04-16 00:27:28','z'),
+(204,'2005-05-03 07:06:22','h'), (224,'2009-03-11 17:09:50','p'),
+(9,'2007-12-08 01:54:28','e'), (5,'2009-07-28 18:19:54','i'),
+(0,'2008-06-08 00:00:00','y'), (3,'2005-02-09 09:20:26','w');
+CREATE TABLE t2 (
+col_varchar_nokey VARCHAR(1) NOT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES ('v'), ('y'), ('j'), ('c'), ('d'), ('r');
+SELECT col_varchar_key
+FROM t1
+WHERE col_varchar_key IN (SELECT col_varchar_nokey
+FROM t2)
+ORDER BY col_datetime_key LIMIT 4;
+col_varchar_key
+v
+y
+v
+y
+DROP TABLE t1, t2;
+# End of test for bug#12818569.
 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	2011-08-03 11:29:20 +0000
+++ b/mysql-test/r/subquery_sj_mat_nosj.result	2011-08-25 06:59:49 +0000
@@ -6671,5 +6671,43 @@ w
 -- Notice that Materialize-scan algorithm reports wrong result for this query.
 -- This problem will be filed as a separate bug and dealt with in WL#5561.
 DROP TABLE t1, t2;
-# End of the test for bug#12603183.
+# End of test for bug#12603183.
+#
+# Bug#12818569: Diff nr of rows returned when using IN/ALL+subquery
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key),
+KEY col_datetime_key(col_datetime_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'2004-06-06 04:22:12','v'), (0,'2005-11-13 01:12:31','s'),
+(9,'2002-05-04 01:50:00','l'), (3,'2004-10-27 10:28:45','y'),
+(4,'2006-07-22 05:24:23','c'), (2,'2002-05-16 21:34:03','i'),
+(5,'2008-04-17 10:45:30','h'), (3,'2009-04-21 02:58:02','q'),
+(1,'2008-01-11 11:01:51','a'), (3,'1900-01-01 00:00:00','v'),
+(6,'2007-05-17 18:24:57','u'), (7,'2007-08-07 00:00:00','s'),
+(5,'2001-08-28 00:00:00','y'), (1,'2004-04-16 00:27:28','z'),
+(204,'2005-05-03 07:06:22','h'), (224,'2009-03-11 17:09:50','p'),
+(9,'2007-12-08 01:54:28','e'), (5,'2009-07-28 18:19:54','i'),
+(0,'2008-06-08 00:00:00','y'), (3,'2005-02-09 09:20:26','w');
+CREATE TABLE t2 (
+col_varchar_nokey VARCHAR(1) NOT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES ('v'), ('y'), ('j'), ('c'), ('d'), ('r');
+SELECT col_varchar_key
+FROM t1
+WHERE col_varchar_key IN (SELECT col_varchar_nokey
+FROM t2)
+ORDER BY col_datetime_key LIMIT 4;
+col_varchar_key
+v
+y
+v
+y
+DROP TABLE t1, t2;
+# End of test for bug#12818569.
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_none.result'
--- a/mysql-test/r/subquery_sj_none.result	2011-08-03 11:29:20 +0000
+++ b/mysql-test/r/subquery_sj_none.result	2011-08-25 06:59:49 +0000
@@ -6606,5 +6606,43 @@ w
 -- Notice that Materialize-scan algorithm reports wrong result for this query.
 -- This problem will be filed as a separate bug and dealt with in WL#5561.
 DROP TABLE t1, t2;
-# End of the test for bug#12603183.
+# End of test for bug#12603183.
+#
+# Bug#12818569: Diff nr of rows returned when using IN/ALL+subquery
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key),
+KEY col_datetime_key(col_datetime_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'2004-06-06 04:22:12','v'), (0,'2005-11-13 01:12:31','s'),
+(9,'2002-05-04 01:50:00','l'), (3,'2004-10-27 10:28:45','y'),
+(4,'2006-07-22 05:24:23','c'), (2,'2002-05-16 21:34:03','i'),
+(5,'2008-04-17 10:45:30','h'), (3,'2009-04-21 02:58:02','q'),
+(1,'2008-01-11 11:01:51','a'), (3,'1900-01-01 00:00:00','v'),
+(6,'2007-05-17 18:24:57','u'), (7,'2007-08-07 00:00:00','s'),
+(5,'2001-08-28 00:00:00','y'), (1,'2004-04-16 00:27:28','z'),
+(204,'2005-05-03 07:06:22','h'), (224,'2009-03-11 17:09:50','p'),
+(9,'2007-12-08 01:54:28','e'), (5,'2009-07-28 18:19:54','i'),
+(0,'2008-06-08 00:00:00','y'), (3,'2005-02-09 09:20:26','w');
+CREATE TABLE t2 (
+col_varchar_nokey VARCHAR(1) NOT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES ('v'), ('y'), ('j'), ('c'), ('d'), ('r');
+SELECT col_varchar_key
+FROM t1
+WHERE col_varchar_key IN (SELECT col_varchar_nokey
+FROM t2)
+ORDER BY col_datetime_key LIMIT 4;
+col_varchar_key
+v
+y
+v
+y
+DROP TABLE t1, t2;
+# End of test for bug#12818569.
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_none_bka.result'
--- a/mysql-test/r/subquery_sj_none_bka.result	2011-08-18 09:21:45 +0000
+++ b/mysql-test/r/subquery_sj_none_bka.result	2011-08-25 06:59:49 +0000
@@ -6607,6 +6607,44 @@ w
 -- Notice that Materialize-scan algorithm reports wrong result for this query.
 -- This problem will be filed as a separate bug and dealt with in WL#5561.
 DROP TABLE t1, t2;
-# End of the test for bug#12603183.
+# End of test for bug#12603183.
+#
+# Bug#12818569: Diff nr of rows returned when using IN/ALL+subquery
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key),
+KEY col_datetime_key(col_datetime_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'2004-06-06 04:22:12','v'), (0,'2005-11-13 01:12:31','s'),
+(9,'2002-05-04 01:50:00','l'), (3,'2004-10-27 10:28:45','y'),
+(4,'2006-07-22 05:24:23','c'), (2,'2002-05-16 21:34:03','i'),
+(5,'2008-04-17 10:45:30','h'), (3,'2009-04-21 02:58:02','q'),
+(1,'2008-01-11 11:01:51','a'), (3,'1900-01-01 00:00:00','v'),
+(6,'2007-05-17 18:24:57','u'), (7,'2007-08-07 00:00:00','s'),
+(5,'2001-08-28 00:00:00','y'), (1,'2004-04-16 00:27:28','z'),
+(204,'2005-05-03 07:06:22','h'), (224,'2009-03-11 17:09:50','p'),
+(9,'2007-12-08 01:54:28','e'), (5,'2009-07-28 18:19:54','i'),
+(0,'2008-06-08 00:00:00','y'), (3,'2005-02-09 09:20:26','w');
+CREATE TABLE t2 (
+col_varchar_nokey VARCHAR(1) NOT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES ('v'), ('y'), ('j'), ('c'), ('d'), ('r');
+SELECT col_varchar_key
+FROM t1
+WHERE col_varchar_key IN (SELECT col_varchar_nokey
+FROM t2)
+ORDER BY col_datetime_key LIMIT 4;
+col_varchar_key
+v
+y
+v
+y
+DROP TABLE t1, t2;
+# End of test for bug#12818569.
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_none_bka_nobnl.result'
--- a/mysql-test/r/subquery_sj_none_bka_nobnl.result	2011-08-03 11:29:20 +0000
+++ b/mysql-test/r/subquery_sj_none_bka_nobnl.result	2011-08-25 06:59:49 +0000
@@ -6607,6 +6607,44 @@ w
 -- Notice that Materialize-scan algorithm reports wrong result for this query.
 -- This problem will be filed as a separate bug and dealt with in WL#5561.
 DROP TABLE t1, t2;
-# End of the test for bug#12603183.
+# End of test for bug#12603183.
+#
+# Bug#12818569: Diff nr of rows returned when using IN/ALL+subquery
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key),
+KEY col_datetime_key(col_datetime_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'2004-06-06 04:22:12','v'), (0,'2005-11-13 01:12:31','s'),
+(9,'2002-05-04 01:50:00','l'), (3,'2004-10-27 10:28:45','y'),
+(4,'2006-07-22 05:24:23','c'), (2,'2002-05-16 21:34:03','i'),
+(5,'2008-04-17 10:45:30','h'), (3,'2009-04-21 02:58:02','q'),
+(1,'2008-01-11 11:01:51','a'), (3,'1900-01-01 00:00:00','v'),
+(6,'2007-05-17 18:24:57','u'), (7,'2007-08-07 00:00:00','s'),
+(5,'2001-08-28 00:00:00','y'), (1,'2004-04-16 00:27:28','z'),
+(204,'2005-05-03 07:06:22','h'), (224,'2009-03-11 17:09:50','p'),
+(9,'2007-12-08 01:54:28','e'), (5,'2009-07-28 18:19:54','i'),
+(0,'2008-06-08 00:00:00','y'), (3,'2005-02-09 09:20:26','w');
+CREATE TABLE t2 (
+col_varchar_nokey VARCHAR(1) NOT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES ('v'), ('y'), ('j'), ('c'), ('d'), ('r');
+SELECT col_varchar_key
+FROM t1
+WHERE col_varchar_key IN (SELECT col_varchar_nokey
+FROM t2)
+ORDER BY col_datetime_key LIMIT 4;
+col_varchar_key
+v
+y
+v
+y
+DROP TABLE t1, t2;
+# End of test for bug#12818569.
 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	2011-08-18 09:21:45 +0000
+++ b/mysql-test/r/subquery_sj_none_bkaunique.result	2011-08-25 06:59:49 +0000
@@ -6608,6 +6608,44 @@ w
 -- Notice that Materialize-scan algorithm reports wrong result for this query.
 -- This problem will be filed as a separate bug and dealt with in WL#5561.
 DROP TABLE t1, t2;
-# End of the test for bug#12603183.
+# End of test for bug#12603183.
+#
+# Bug#12818569: Diff nr of rows returned when using IN/ALL+subquery
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key),
+KEY col_datetime_key(col_datetime_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'2004-06-06 04:22:12','v'), (0,'2005-11-13 01:12:31','s'),
+(9,'2002-05-04 01:50:00','l'), (3,'2004-10-27 10:28:45','y'),
+(4,'2006-07-22 05:24:23','c'), (2,'2002-05-16 21:34:03','i'),
+(5,'2008-04-17 10:45:30','h'), (3,'2009-04-21 02:58:02','q'),
+(1,'2008-01-11 11:01:51','a'), (3,'1900-01-01 00:00:00','v'),
+(6,'2007-05-17 18:24:57','u'), (7,'2007-08-07 00:00:00','s'),
+(5,'2001-08-28 00:00:00','y'), (1,'2004-04-16 00:27:28','z'),
+(204,'2005-05-03 07:06:22','h'), (224,'2009-03-11 17:09:50','p'),
+(9,'2007-12-08 01:54:28','e'), (5,'2009-07-28 18:19:54','i'),
+(0,'2008-06-08 00:00:00','y'), (3,'2005-02-09 09:20:26','w');
+CREATE TABLE t2 (
+col_varchar_nokey VARCHAR(1) NOT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES ('v'), ('y'), ('j'), ('c'), ('d'), ('r');
+SELECT col_varchar_key
+FROM t1
+WHERE col_varchar_key IN (SELECT col_varchar_nokey
+FROM t2)
+ORDER BY col_datetime_key LIMIT 4;
+col_varchar_key
+v
+y
+v
+y
+DROP TABLE t1, t2;
+# End of test for bug#12818569.
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2011-08-19 13:33:56 +0000
+++ b/sql/sql_select.cc	2011-08-25 06:59:49 +0000
@@ -3460,29 +3460,30 @@ JOIN::exec()
     }
     {
       if (group)
-	curr_join->m_select_limit= HA_POS_ERROR;
+        curr_join->m_select_limit= HA_POS_ERROR;
       else
       {
-	/*
-	  We can abort sorting after thd->select_limit rows if we there is no
-	  WHERE clause for any tables after the sorted one.
-	*/
-	JOIN_TAB *curr_table= &curr_join->join_tab[curr_join->const_tables+1];
-	JOIN_TAB *end_table= &curr_join->join_tab[curr_join->tables];
-	for (; curr_table < end_table ; curr_table++)
-	{
-	  /*
-	    table->keyuse is set in the case there was an original WHERE clause
-	    on the table that was optimized away.
-	  */
-	  if (curr_table->condition() ||
-	      (curr_table->keyuse && !curr_table->first_inner))
-	  {
-	    /* We have to sort all rows */
-	    curr_join->m_select_limit= HA_POS_ERROR;
-	    break;
-	  }
-	}
+        /*
+          We can abort sorting after thd->select_limit rows if there are no
+          filter conditions for any tables after the sorted one.
+          Filter conditions come in several forms:
+           - as a condition item attached to the join_tab,
+           - as a keyuse attached to the join_tab (ref access),
+           - as a semi-join equality attached to materialization semi-join nest.
+        */
+        JOIN_TAB *curr_table= &curr_join->join_tab[curr_join->const_tables+1];
+        JOIN_TAB *end_table= &curr_join->join_tab[curr_join->tables];
+        for (; curr_table < end_table ; curr_table++)
+        {
+          if (curr_table->condition() ||
+              (curr_table->keyuse && !curr_table->first_inner) ||
+              curr_table->get_sj_strategy() == SJ_OPT_MATERIALIZE_LOOKUP)
+          {
+            /* We have to sort all rows */
+            curr_join->m_select_limit= HA_POS_ERROR;
+            break;
+          }
+        }
       }
       if (curr_join->join_tab == join_tab && save_join_tab())
       {

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-trunk branch (roy.lyseng:3428 to 3429) Bug#12818569Roy Lyseng25 Aug