From: Roy Lyseng Date: August 25 2011 7:02am Subject: bzr push into mysql-trunk branch (roy.lyseng:3428 to 3429) Bug#12818569 List-Archive: http://lists.mysql.com/commits/140789 X-Bug: 12818569 Message-Id: <20110825070222.50EB5200@tyr67.norway.sun.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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).