Hi Roy,
Thanks for the patch. Code changes looks good. I am able to reproduce
the issue consistently on my Solaris desktop, and I have verified that
the patch fixes the issue. I have a attached a reduced test case that
still reproduces generates the seg fault. Also, please, add the analysis
about usages of save_read_first_record not being in conflict to commit
comments, and maybe something should be said in the header file about
this, too.
--
Øystein
On 01/06/2011 12:27, Roy Lyseng wrote:
> #At file:///home/rl136806/mysql/repo/mysql-review/ based on
> revid:tor.didriksen@stripped
>
> 3379 Roy Lyseng 2011-06-01
> Bug#12603183: Segfault in hp_movelink
>
> The bug manifests itself sometimes as a segmentation fault,
> sometimes as a Valgrind warning. Consistent faulting has been
> difficult to achieve.
>
> The problem is with the Materialization scan semi-join strategy.
> When materialization is done, the read_first_record function
> pointer is replaced with a function to read from the materialized
> table instead. This strategy works when the materialization is
> performed once per query, even when the materialized table is
> read multiple times. However, if the materialization is performed
> multiple times, such as when called from within another subquery,
> the original function pointer is never restored, and the wrong
> function is used to read from the subquery tables when materializing.
>
> The solution for the problem is to save the original function pointer
> in the save_read_first_record field of the join_tab and restore
> it for every new materialization.
>
> Notice that there are still some result differences, which would
> not be seen with the original "LIMIT 1" specification.
> It seems that Materialization scan is still slightly broken when
> used together with an outer join. This problem will be looked at
> in the context of WL#5561. Notice also that when semi-join
> transformation for outer joins is enabled, both subqueries of this
> query will be converted, and the materialization will be performed
> only once, avoiding the entire problem. Hence, to reproduce this
> problem in context of WL#5561, make sure that the outer subquery
> is not transformed.
>
> mysql-test/include/subquery_sj.inc
> Added test case for bug#12603183.
>
> mysql-test/r/subquery_sj_all.result
> mysql-test/r/subquery_sj_all_jcl6.result
> mysql-test/r/subquery_sj_all_jcl7.result
> mysql-test/r/subquery_sj_dupsweed.result
> mysql-test/r/subquery_sj_dupsweed_jcl6.result
> mysql-test/r/subquery_sj_dupsweed_jcl7.result
> mysql-test/r/subquery_sj_firstmatch.result
> mysql-test/r/subquery_sj_firstmatch_jcl6.result
> mysql-test/r/subquery_sj_firstmatch_jcl7.result
> mysql-test/r/subquery_sj_loosescan.result
> mysql-test/r/subquery_sj_loosescan_jcl6.result
> mysql-test/r/subquery_sj_loosescan_jcl7.result
> mysql-test/r/subquery_sj_mat.result
> mysql-test/r/subquery_sj_mat_jcl6.result
> mysql-test/r/subquery_sj_mat_jcl7.result
> mysql-test/r/subquery_sj_mat_nosj.result
> mysql-test/r/subquery_sj_none.result
> mysql-test/r/subquery_sj_none_jcl6.result
> mysql-test/r/subquery_sj_none_jcl7.result
> Updated with test results for bug#12603183.
>
> sql/sql_select.cc
> sub_select_sjm():
> Save read function pointer into save_read_first_record on first
> materialization, and restore it on subsequent materializations.
> Deleted a DBUG_ASSERT that seemed redundant, and moved setting
> of sjm->materialized to a better place.
>
> sql/sql_select.h
> Updated comment for join_tab field save_read_first_record.
>
> modified:
> mysql-test/include/subquery_sj.inc
> mysql-test/r/subquery_sj_all.result
> mysql-test/r/subquery_sj_all_jcl6.result
> mysql-test/r/subquery_sj_all_jcl7.result
> mysql-test/r/subquery_sj_dupsweed.result
> mysql-test/r/subquery_sj_dupsweed_jcl6.result
> mysql-test/r/subquery_sj_dupsweed_jcl7.result
> mysql-test/r/subquery_sj_firstmatch.result
> mysql-test/r/subquery_sj_firstmatch_jcl6.result
> mysql-test/r/subquery_sj_firstmatch_jcl7.result
> mysql-test/r/subquery_sj_loosescan.result
> mysql-test/r/subquery_sj_loosescan_jcl6.result
> mysql-test/r/subquery_sj_loosescan_jcl7.result
> mysql-test/r/subquery_sj_mat.result
> mysql-test/r/subquery_sj_mat_jcl6.result
> mysql-test/r/subquery_sj_mat_jcl7.result
> mysql-test/r/subquery_sj_mat_nosj.result
> mysql-test/r/subquery_sj_none.result
> mysql-test/r/subquery_sj_none_jcl6.result
> mysql-test/r/subquery_sj_none_jcl7.result
> sql/sql_select.cc
> sql/sql_select.h
> === modified file 'mysql-test/include/subquery_sj.inc'
> --- a/mysql-test/include/subquery_sj.inc 2011-03-17 08:00:10 +0000
> +++ b/mysql-test/include/subquery_sj.inc 2011-06-01 10:27:29 +0000
> @@ -3540,3 +3540,113 @@ ORDER BY a;
>
> DROP TABLE t1;
> DROP VIEW v1;
> +
> +--echo #
> +--echo # Bug#12603183: Segfault in hp_movelink
> +--echo #
> +
> +CREATE TABLE t1 (
> + pk int NOT NULL,
> + col_int_nokey int NOT NULL,
> + col_int_key int NOT NULL,
> + col_date_key date NOT NULL,
> + col_date_nokey date NOT NULL,
> + col_time_key time NOT NULL,
> + col_time_nokey time NOT NULL,
> + col_datetime_key datetime NOT NULL,
> + col_datetime_nokey datetime NOT NULL,
> + col_varchar_key varchar(1) NOT NULL,
> + col_varchar_nokey varchar(1) NOT NULL,
> + PRIMARY KEY (pk),
> + KEY col_int_key (col_int_key),
> + KEY col_date_key(col_date_key),
> + KEY col_time_key(col_time_key),
> + KEY col_datetime_key(col_datetime_key),
> + KEY col_varchar_key(col_varchar_key, col_int_key)
> +);
> +
> +INSERT INTO t1 VALUES
> +(10,1,7,'2007-04-28','2007-04-28','00:00:00','00:00:00','2004-06-06
> 04:22:12','2004-06-06 04:22:12','v','v'),
> +(11,7,0,'2006-04-05','2006-04-05','00:00:00','00:00:00','2005-11-13
> 01:12:31','2005-11-13 01:12:31','s','s'),
> +(12,4,9,'2002-02-02','2002-02-02','06:35:17','06:35:17','2002-05-04
> 01:50:00','2002-05-04 01:50:00','l','l'),
> +(13,7,3,'2002-06-07','2002-06-07','18:07:14','18:07:14','2004-10-27
> 10:28:45','2004-10-27 10:28:45','y','y'),
> +(14,0,4,'0000-00-00','0000-00-00','20:36:52','20:36:52','2006-07-22
> 05:24:23','2006-07-22 05:24:23','c','c'),
> +(15,2,2,'2003-01-13','2003-01-13','21:29:07','21:29:07','2002-05-16
> 21:34:03','2002-05-16 21:34:03','i','i'),
> +(16,9,5,'2006-07-07','2006-07-07','23:45:57','23:45:57','2008-04-17
> 10:45:30','2008-04-17 10:45:30','h','h'),
> +(17,4,3,'0000-00-00','0000-00-00','22:54:57','22:54:57','2009-04-21
> 02:58:02','2009-04-21 02:58:02','q','q'),
> +(18,0,1,'2002-06-15','2002-06-15','18:45:09','18:45:09','2008-01-11
> 11:01:51','2008-01-11 11:01:51','a','a'),
> +(19,9,3,'2006-07-09','2006-07-09','14:30:46','14:30:46','1900-01-01
> 00:00:00','1900-01-01 00:00:00','v','v'),
> +(20,1,6,'2001-04-17','2001-04-17','19:23:43','19:23:43','2007-05-17
> 18:24:57','2007-05-17 18:24:57','u','u'),
> +(21,3,7,'2005-12-22','2005-12-22','03:39:30','03:39:30','2007-08-07
> 00:00:00','2007-08-07 00:00:00','s','s'),
> +(22,8,5,'2009-05-03','2009-05-03','23:37:52','23:37:52','2001-08-28
> 00:00:00','2001-08-28 00:00:00','y','y'),
> +(23,8,1,'2003-05-28','2003-05-28','16:59:30','16:59:30','2004-04-16
> 00:27:28','2004-04-16 00:27:28','z','z'),
> +(24,18,204,'0000-00-00','0000-00-00','22:21:15','22:21:15','2005-05-03
> 07:06:22','2005-05-03 07:06:22','h','h'),
> +(25,84,224,'2001-11-03','2001-11-03','12:24:37','12:24:37','2009-03-11
> 17:09:50','2009-03-11 17:09:50','p','p'),
> +(26,6,9,'1900-01-01','1900-01-01','15:02:08','15:02:08','2007-12-08
> 01:54:28','2007-12-08 01:54:28','e','e'),
> +(27,3,5,'2000-02-17','2000-02-17','00:00:00','00:00:00','2009-07-28
> 18:19:54','2009-07-28 18:19:54','i','i'),
> +(28,6,0,'2001-01-23','2001-01-23','08:23:30','08:23:30','2008-06-08
> 00:00:00','2008-06-08 00:00:00','y','y'),
> +(29,6,3,'0000-00-00','0000-00-00','08:32:22','08:32:22','2005-02-09
> 09:20:26','2005-02-09 09:20:26','w','w');
> +
> +CREATE TABLE t2 (
> + pk int NOT NULL,
> + col_date_nokey date NOT NULL,
> + PRIMARY KEY (pk)
> +);
> +
> +INSERT INTO t2 VALUES
> +(1, '0000-00-00'),
> +(2, '2004-09-18'),
> +(3, '2009-12-01'),
> +(4, '2004-12-17'),
> +(5, '2000-03-14'),
> +(6, '2000-10-08'),
> +(7, '2006-05-25'),
> +(8, '2008-01-23'),
> +(9, '2007-06-18'),
> +(10, '2002-10-13'),
> +(11, '1900-01-01'),
> +(12, '0000-00-00'),
> +(13, '2006-03-09'),
> +(14, '2001-06-05'),
> +(15, '2006-05-28'),
> +(16, '2001-04-19'),
> +(17, '1900-01-01'),
> +(18, '2004-08-20'),
> +(19, '2004-10-10'),
> +(20, '2000-04-02');
> +
> +CREATE TABLE t3 (
> + pk int NOT NULL,
> + col_int_key int NOT NULL,
> + col_varchar_key varchar(1) NOT NULL,
> + PRIMARY KEY (pk),
> + KEY col_int_key(col_int_key),
> + KEY col_varchar_key(col_varchar_key, col_int_key)
> +);
> +
> +INSERT INTO t3 VALUES
> +(10, 7, 'b');
> +
> +SELECT grandparent1.col_time_key AS g1
> +FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_date_nokey)
> +WHERE (grandparent1.col_varchar_key, grandparent1.col_varchar_key) IN
> + (SELECT parent1.col_varchar_nokey AS p1, parent1.col_varchar_nokey AS p2
> + FROM t1 AS parent1
> + WHERE parent1.col_varchar_key IN
> + (SELECT DISTINCT child1.col_varchar_nokey AS c1
> + FROM t1 AS child1 LEFT JOIN t3 AS child2
> + ON (child1.col_varchar_key> child2.col_varchar_key))
> + AND
> + (parent1.col_int_nokey<= parent1.pk OR
> + grandparent1.pk> 8))
> + AND
> + grandparent1.col_varchar_nokey<> 'w' AND
> + grandparent1.col_date_key<= '2004-09-11'
> +ORDER BY g1;
> +
> +--echo -- Notice that Materialize-scan algorithm reports wrong result for this
> query.
> +--echo -- This problem will be filed as a separate bug and dealt with in WL#5561.
> +
> +DROP TABLE t1, t2, t3;
> +
> +--echo # End of the test for bug#12603183.
>
> === modified file 'mysql-test/r/subquery_sj_all.result'
> --- a/mysql-test/r/subquery_sj_all.result 2011-03-17 11:23:06 +0000
> +++ b/mysql-test/r/subquery_sj_all.result 2011-06-01 10:27:29 +0000
> @@ -5429,4 +5429,120 @@ a
> 0
> DROP TABLE t1;
> DROP VIEW v1;
> +#
> +# Bug#12603183: Segfault in hp_movelink
> +#
> +CREATE TABLE t1 (
> +pk int NOT NULL,
> +col_int_nokey int NOT NULL,
> +col_int_key int NOT NULL,
> +col_date_key date NOT NULL,
> +col_date_nokey date NOT NULL,
> +col_time_key time NOT NULL,
> +col_time_nokey time NOT NULL,
> +col_datetime_key datetime NOT NULL,
> +col_datetime_nokey datetime NOT NULL,
> +col_varchar_key varchar(1) NOT NULL,
> +col_varchar_nokey varchar(1) NOT NULL,
> +PRIMARY KEY (pk),
> +KEY col_int_key (col_int_key),
> +KEY col_date_key(col_date_key),
> +KEY col_time_key(col_time_key),
> +KEY col_datetime_key(col_datetime_key),
> +KEY col_varchar_key(col_varchar_key, col_int_key)
> +);
> +INSERT INTO t1 VALUES
> +(10,1,7,'2007-04-28','2007-04-28','00:00:00','00:00:00','2004-06-06
> 04:22:12','2004-06-06 04:22:12','v','v'),
> +(11,7,0,'2006-04-05','2006-04-05','00:00:00','00:00:00','2005-11-13
> 01:12:31','2005-11-13 01:12:31','s','s'),
> +(12,4,9,'2002-02-02','2002-02-02','06:35:17','06:35:17','2002-05-04
> 01:50:00','2002-05-04 01:50:00','l','l'),
> +(13,7,3,'2002-06-07','2002-06-07','18:07:14','18:07:14','2004-10-27
> 10:28:45','2004-10-27 10:28:45','y','y'),
> +(14,0,4,'0000-00-00','0000-00-00','20:36:52','20:36:52','2006-07-22
> 05:24:23','2006-07-22 05:24:23','c','c'),
> +(15,2,2,'2003-01-13','2003-01-13','21:29:07','21:29:07','2002-05-16
> 21:34:03','2002-05-16 21:34:03','i','i'),
> +(16,9,5,'2006-07-07','2006-07-07','23:45:57','23:45:57','2008-04-17
> 10:45:30','2008-04-17 10:45:30','h','h'),
> +(17,4,3,'0000-00-00','0000-00-00','22:54:57','22:54:57','2009-04-21
> 02:58:02','2009-04-21 02:58:02','q','q'),
> +(18,0,1,'2002-06-15','2002-06-15','18:45:09','18:45:09','2008-01-11
> 11:01:51','2008-01-11 11:01:51','a','a'),
> +(19,9,3,'2006-07-09','2006-07-09','14:30:46','14:30:46','1900-01-01
> 00:00:00','1900-01-01 00:00:00','v','v'),
> +(20,1,6,'2001-04-17','2001-04-17','19:23:43','19:23:43','2007-05-17
> 18:24:57','2007-05-17 18:24:57','u','u'),
> +(21,3,7,'2005-12-22','2005-12-22','03:39:30','03:39:30','2007-08-07
> 00:00:00','2007-08-07 00:00:00','s','s'),
> +(22,8,5,'2009-05-03','2009-05-03','23:37:52','23:37:52','2001-08-28
> 00:00:00','2001-08-28 00:00:00','y','y'),
> +(23,8,1,'2003-05-28','2003-05-28','16:59:30','16:59:30','2004-04-16
> 00:27:28','2004-04-16 00:27:28','z','z'),
> +(24,18,204,'0000-00-00','0000-00-00','22:21:15','22:21:15','2005-05-03
> 07:06:22','2005-05-03 07:06:22','h','h'),
> +(25,84,224,'2001-11-03','2001-11-03','12:24:37','12:24:37','2009-03-11
> 17:09:50','2009-03-11 17:09:50','p','p'),
> +(26,6,9,'1900-01-01','1900-01-01','15:02:08','15:02:08','2007-12-08
> 01:54:28','2007-12-08 01:54:28','e','e'),
> +(27,3,5,'2000-02-17','2000-02-17','00:00:00','00:00:00','2009-07-28
> 18:19:54','2009-07-28 18:19:54','i','i'),
> +(28,6,0,'2001-01-23','2001-01-23','08:23:30','08:23:30','2008-06-08
> 00:00:00','2008-06-08 00:00:00','y','y'),
> +(29,6,3,'0000-00-00','0000-00-00','08:32:22','08:32:22','2005-02-09
> 09:20:26','2005-02-09 09:20:26','w','w');
> +CREATE TABLE t2 (
> +pk int NOT NULL,
> +col_date_nokey date NOT NULL,
> +PRIMARY KEY (pk)
> +);
> +INSERT INTO t2 VALUES
> +(1, '0000-00-00'),
> +(2, '2004-09-18'),
> +(3, '2009-12-01'),
> +(4, '2004-12-17'),
> +(5, '2000-03-14'),
> +(6, '2000-10-08'),
> +(7, '2006-05-25'),
> +(8, '2008-01-23'),
> +(9, '2007-06-18'),
> +(10, '2002-10-13'),
> +(11, '1900-01-01'),
> +(12, '0000-00-00'),
> +(13, '2006-03-09'),
> +(14, '2001-06-05'),
> +(15, '2006-05-28'),
> +(16, '2001-04-19'),
> +(17, '1900-01-01'),
> +(18, '2004-08-20'),
> +(19, '2004-10-10'),
> +(20, '2000-04-02');
> +CREATE TABLE t3 (
> +pk int NOT NULL,
> +col_int_key int NOT NULL,
> +col_varchar_key varchar(1) NOT NULL,
> +PRIMARY KEY (pk),
> +KEY col_int_key(col_int_key),
> +KEY col_varchar_key(col_varchar_key, col_int_key)
> +);
> +INSERT INTO t3 VALUES
> +(10, 7, 'b');
> +SELECT grandparent1.col_time_key AS g1
> +FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_date_nokey)
> +WHERE (grandparent1.col_varchar_key, grandparent1.col_varchar_key) IN
> +(SELECT parent1.col_varchar_nokey AS p1, parent1.col_varchar_nokey AS p2
> +FROM t1 AS parent1
> +WHERE parent1.col_varchar_key IN
> +(SELECT DISTINCT child1.col_varchar_nokey AS c1
> +FROM t1 AS child1 LEFT JOIN t3 AS child2
> +ON (child1.col_varchar_key> child2.col_varchar_key))
> +AND
> +(parent1.col_int_nokey<= parent1.pk OR
> +grandparent1.pk> 8))
> +AND
> +grandparent1.col_varchar_nokey<> 'w' AND
> +grandparent1.col_date_key<= '2004-09-11'
> +ORDER BY g1;
> +g1
> +00:00:00
> +06:35:17
> +08:23:30
> +12:24:37
> +15:02:08
> +15:02:08
> +16:59:30
> +18:07:14
> +19:23:43
> +20:36:52
> +20:36:52
> +21:29:07
> +22:21:15
> +22:21:15
> +22:54:57
> +22:54:57
> +-- 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, t3;
> +# End of the test for bug#12603183.
> set optimizer_switch=default;
>
> === modified file 'mysql-test/r/subquery_sj_all_jcl6.result'
> --- a/mysql-test/r/subquery_sj_all_jcl6.result 2011-03-17 11:23:06 +0000
> +++ b/mysql-test/r/subquery_sj_all_jcl6.result 2011-06-01 10:27:29 +0000
> @@ -5433,5 +5433,121 @@ a
> 0
> DROP TABLE t1;
> DROP VIEW v1;
> +#
> +# Bug#12603183: Segfault in hp_movelink
> +#
> +CREATE TABLE t1 (
> +pk int NOT NULL,
> +col_int_nokey int NOT NULL,
> +col_int_key int NOT NULL,
> +col_date_key date NOT NULL,
> +col_date_nokey date NOT NULL,
> +col_time_key time NOT NULL,
> +col_time_nokey time NOT NULL,
> +col_datetime_key datetime NOT NULL,
> +col_datetime_nokey datetime NOT NULL,
> +col_varchar_key varchar(1) NOT NULL,
> +col_varchar_nokey varchar(1) NOT NULL,
> +PRIMARY KEY (pk),
> +KEY col_int_key (col_int_key),
> +KEY col_date_key(col_date_key),
> +KEY col_time_key(col_time_key),
> +KEY col_datetime_key(col_datetime_key),
> +KEY col_varchar_key(col_varchar_key, col_int_key)
> +);
> +INSERT INTO t1 VALUES
> +(10,1,7,'2007-04-28','2007-04-28','00:00:00','00:00:00','2004-06-06
> 04:22:12','2004-06-06 04:22:12','v','v'),
> +(11,7,0,'2006-04-05','2006-04-05','00:00:00','00:00:00','2005-11-13
> 01:12:31','2005-11-13 01:12:31','s','s'),
> +(12,4,9,'2002-02-02','2002-02-02','06:35:17','06:35:17','2002-05-04
> 01:50:00','2002-05-04 01:50:00','l','l'),
> +(13,7,3,'2002-06-07','2002-06-07','18:07:14','18:07:14','2004-10-27
> 10:28:45','2004-10-27 10:28:45','y','y'),
> +(14,0,4,'0000-00-00','0000-00-00','20:36:52','20:36:52','2006-07-22
> 05:24:23','2006-07-22 05:24:23','c','c'),
> +(15,2,2,'2003-01-13','2003-01-13','21:29:07','21:29:07','2002-05-16
> 21:34:03','2002-05-16 21:34:03','i','i'),
> +(16,9,5,'2006-07-07','2006-07-07','23:45:57','23:45:57','2008-04-17
> 10:45:30','2008-04-17 10:45:30','h','h'),
> +(17,4,3,'0000-00-00','0000-00-00','22:54:57','22:54:57','2009-04-21
> 02:58:02','2009-04-21 02:58:02','q','q'),
> +(18,0,1,'2002-06-15','2002-06-15','18:45:09','18:45:09','2008-01-11
> 11:01:51','2008-01-11 11:01:51','a','a'),
> +(19,9,3,'2006-07-09','2006-07-09','14:30:46','14:30:46','1900-01-01
> 00:00:00','1900-01-01 00:00:00','v','v'),
> +(20,1,6,'2001-04-17','2001-04-17','19:23:43','19:23:43','2007-05-17
> 18:24:57','2007-05-17 18:24:57','u','u'),
> +(21,3,7,'2005-12-22','2005-12-22','03:39:30','03:39:30','2007-08-07
> 00:00:00','2007-08-07 00:00:00','s','s'),
> +(22,8,5,'2009-05-03','2009-05-03','23:37:52','23:37:52','2001-08-28
> 00:00:00','2001-08-28 00:00:00','y','y'),
> +(23,8,1,'2003-05-28','2003-05-28','16:59:30','16:59:30','2004-04-16
> 00:27:28','2004-04-16 00:27:28','z','z'),
> +(24,18,204,'0000-00-00','0000-00-00','22:21:15','22:21:15','2005-05-03
> 07:06:22','2005-05-03 07:06:22','h','h'),
> +(25,84,224,'2001-11-03','2001-11-03','12:24:37','12:24:37','2009-03-11
> 17:09:50','2009-03-11 17:09:50','p','p'),
> +(26,6,9,'1900-01-01','1900-01-01','15:02:08','15:02:08','2007-12-08
> 01:54:28','2007-12-08 01:54:28','e','e'),
> +(27,3,5,'2000-02-17','2000-02-17','00:00:00','00:00:00','2009-07-28
> 18:19:54','2009-07-28 18:19:54','i','i'),
> +(28,6,0,'2001-01-23','2001-01-23','08:23:30','08:23:30','2008-06-08
> 00:00:00','2008-06-08 00:00:00','y','y'),
> +(29,6,3,'0000-00-00','0000-00-00','08:32:22','08:32:22','2005-02-09
> 09:20:26','2005-02-09 09:20:26','w','w');
> +CREATE TABLE t2 (
> +pk int NOT NULL,
> +col_date_nokey date NOT NULL,
> +PRIMARY KEY (pk)
> +);
> +INSERT INTO t2 VALUES
> +(1, '0000-00-00'),
> +(2, '2004-09-18'),
> +(3, '2009-12-01'),
> +(4, '2004-12-17'),
> +(5, '2000-03-14'),
> +(6, '2000-10-08'),
> +(7, '2006-05-25'),
> +(8, '2008-01-23'),
> +(9, '2007-06-18'),
> +(10, '2002-10-13'),
> +(11, '1900-01-01'),
> +(12, '0000-00-00'),
> +(13, '2006-03-09'),
> +(14, '2001-06-05'),
> +(15, '2006-05-28'),
> +(16, '2001-04-19'),
> +(17, '1900-01-01'),
> +(18, '2004-08-20'),
> +(19, '2004-10-10'),
> +(20, '2000-04-02');
> +CREATE TABLE t3 (
> +pk int NOT NULL,
> +col_int_key int NOT NULL,
> +col_varchar_key varchar(1) NOT NULL,
> +PRIMARY KEY (pk),
> +KEY col_int_key(col_int_key),
> +KEY col_varchar_key(col_varchar_key, col_int_key)
> +);
> +INSERT INTO t3 VALUES
> +(10, 7, 'b');
> +SELECT grandparent1.col_time_key AS g1
> +FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_date_nokey)
> +WHERE (grandparent1.col_varchar_key, grandparent1.col_varchar_key) IN
> +(SELECT parent1.col_varchar_nokey AS p1, parent1.col_varchar_nokey AS p2
> +FROM t1 AS parent1
> +WHERE parent1.col_varchar_key IN
> +(SELECT DISTINCT child1.col_varchar_nokey AS c1
> +FROM t1 AS child1 LEFT JOIN t3 AS child2
> +ON (child1.col_varchar_key> child2.col_varchar_key))
> +AND
> +(parent1.col_int_nokey<= parent1.pk OR
> +grandparent1.pk> 8))
> +AND
> +grandparent1.col_varchar_nokey<> 'w' AND
> +grandparent1.col_date_key<= '2004-09-11'
> +ORDER BY g1;
> +g1
> +00:00:00
> +06:35:17
> +08:23:30
> +12:24:37
> +15:02:08
> +15:02:08
> +16:59:30
> +18:07:14
> +19:23:43
> +20:36:52
> +20:36:52
> +21:29:07
> +22:21:15
> +22:21:15
> +22:54:57
> +22:54:57
> +-- 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, t3;
> +# End of the test for bug#12603183.
> set optimizer_switch=default;
> set optimizer_join_cache_level=default;
>
> === modified file 'mysql-test/r/subquery_sj_all_jcl7.result'
> --- a/mysql-test/r/subquery_sj_all_jcl7.result 2011-03-17 11:23:06 +0000
> +++ b/mysql-test/r/subquery_sj_all_jcl7.result 2011-06-01 10:27:29 +0000
> @@ -5433,5 +5433,121 @@ a
> 0
> DROP TABLE t1;
> DROP VIEW v1;
> +#
> +# Bug#12603183: Segfault in hp_movelink
> +#
> +CREATE TABLE t1 (
> +pk int NOT NULL,
> +col_int_nokey int NOT NULL,
> +col_int_key int NOT NULL,
> +col_date_key date NOT NULL,
> +col_date_nokey date NOT NULL,
> +col_time_key time NOT NULL,
> +col_time_nokey time NOT NULL,
> +col_datetime_key datetime NOT NULL,
> +col_datetime_nokey datetime NOT NULL,
> +col_varchar_key varchar(1) NOT NULL,
> +col_varchar_nokey varchar(1) NOT NULL,
> +PRIMARY KEY (pk),
> +KEY col_int_key (col_int_key),
> +KEY col_date_key(col_date_key),
> +KEY col_time_key(col_time_key),
> +KEY col_datetime_key(col_datetime_key),
> +KEY col_varchar_key(col_varchar_key, col_int_key)
> +);
> +INSERT INTO t1 VALUES
> +(10,1,7,'2007-04-28','2007-04-28','00:00:00','00:00:00','2004-06-06
> 04:22:12','2004-06-06 04:22:12','v','v'),
> +(11,7,0,'2006-04-05','2006-04-05','00:00:00','00:00:00','2005-11-13
> 01:12:31','2005-11-13 01:12:31','s','s'),
> +(12,4,9,'2002-02-02','2002-02-02','06:35:17','06:35:17','2002-05-04
> 01:50:00','2002-05-04 01:50:00','l','l'),
> +(13,7,3,'2002-06-07','2002-06-07','18:07:14','18:07:14','2004-10-27
> 10:28:45','2004-10-27 10:28:45','y','y'),
> +(14,0,4,'0000-00-00','0000-00-00','20:36:52','20:36:52','2006-07-22
> 05:24:23','2006-07-22 05:24:23','c','c'),
> +(15,2,2,'2003-01-13','2003-01-13','21:29:07','21:29:07','2002-05-16
> 21:34:03','2002-05-16 21:34:03','i','i'),
> +(16,9,5,'2006-07-07','2006-07-07','23:45:57','23:45:57','2008-04-17
> 10:45:30','2008-04-17 10:45:30','h','h'),
> +(17,4,3,'0000-00-00','0000-00-00','22:54:57','22:54:57','2009-04-21
> 02:58:02','2009-04-21 02:58:02','q','q'),
> +(18,0,1,'2002-06-15','2002-06-15','18:45:09','18:45:09','2008-01-11
> 11:01:51','2008-01-11 11:01:51','a','a'),
> +(19,9,3,'2006-07-09','2006-07-09','14:30:46','14:30:46','1900-01-01
> 00:00:00','1900-01-01 00:00:00','v','v'),
> +(20,1,6,'2001-04-17','2001-04-17','19:23:43','19:23:43','2007-05-17
> 18:24:57','2007-05-17 18:24:57','u','u'),
> +(21,3,7,'2005-12-22','2005-12-22','03:39:30','03:39:30','2007-08-07
> 00:00:00','2007-08-07 00:00:00','s','s'),
> +(22,8,5,'2009-05-03','2009-05-03','23:37:52','23:37:52','2001-08-28
> 00:00:00','2001-08-28 00:00:00','y','y'),
> +(23,8,1,'2003-05-28','2003-05-28','16:59:30','16:59:30','2004-04-16
> 00:27:28','2004-04-16 00:27:28','z','z'),
> +(24,18,204,'0000-00-00','0000-00-00','22:21:15','22:21:15','2005-05-03
> 07:06:22','2005-05-03 07:06:22','h','h'),
> +(25,84,224,'2001-11-03','2001-11-03','12:24:37','12:24:37','2009-03-11
> 17:09:50','2009-03-11 17:09:50','p','p'),
> +(26,6,9,'1900-01-01','1900-01-01','15:02:08','15:02:08','2007-12-08
> 01:54:28','2007-12-08 01:54:28','e','e'),
> +(27,3,5,'2000-02-17','2000-02-17','00:00:00','00:00:00','2009-07-28
> 18:19:54','2009-07-28 18:19:54','i','i'),
> +(28,6,0,'2001-01-23','2001-01-23','08:23:30','08:23:30','2008-06-08
> 00:00:00','2008-06-08 00:00:00','y','y'),
> +(29,6,3,'0000-00-00','0000-00-00','08:32:22','08:32:22','2005-02-09
> 09:20:26','2005-02-09 09:20:26','w','w');
> +CREATE TABLE t2 (
> +pk int NOT NULL,
> +col_date_nokey date NOT NULL,
> +PRIMARY KEY (pk)
> +);
> +INSERT INTO t2 VALUES
> +(1, '0000-00-00'),
> +(2, '2004-09-18'),
> +(3, '2009-12-01'),
> +(4, '2004-12-17'),
> +(5, '2000-03-14'),
> +(6, '2000-10-08'),
> +(7, '2006-05-25'),
> +(8, '2008-01-23'),
> +(9, '2007-06-18'),
> +(10, '2002-10-13'),
> +(11, '1900-01-01'),
> +(12, '0000-00-00'),
> +(13, '2006-03-09'),
> +(14, '2001-06-05'),
> +(15, '2006-05-28'),
> +(16, '2001-04-19'),
> +(17, '1900-01-01'),
> +(18, '2004-08-20'),
> +(19, '2004-10-10'),
> +(20, '2000-04-02');
> +CREATE TABLE t3 (
> +pk int NOT NULL,
> +col_int_key int NOT NULL,
> +col_varchar_key varchar(1) NOT NULL,
> +PRIMARY KEY (pk),
> +KEY col_int_key(col_int_key),
> +KEY col_varchar_key(col_varchar_key, col_int_key)
> +);
> +INSERT INTO t3 VALUES
> +(10, 7, 'b');
> +SELECT grandparent1.col_time_key AS g1
> +FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_date_nokey)
> +WHERE (grandparent1.col_varchar_key, grandparent1.col_varchar_key) IN
> +(SELECT parent1.col_varchar_nokey AS p1, parent1.col_varchar_nokey AS p2
> +FROM t1 AS parent1
> +WHERE parent1.col_varchar_key IN
> +(SELECT DISTINCT child1.col_varchar_nokey AS c1
> +FROM t1 AS child1 LEFT JOIN t3 AS child2
> +ON (child1.col_varchar_key> child2.col_varchar_key))
> +AND
> +(parent1.col_int_nokey<= parent1.pk OR
> +grandparent1.pk> 8))
> +AND
> +grandparent1.col_varchar_nokey<> 'w' AND
> +grandparent1.col_date_key<= '2004-09-11'
> +ORDER BY g1;
> +g1
> +00:00:00
> +06:35:17
> +08:23:30
> +12:24:37
> +15:02:08
> +15:02:08
> +16:59:30
> +18:07:14
> +19:23:43
> +20:36:52
> +20:36:52
> +21:29:07
> +22:21:15
> +22:21:15
> +22:54:57
> +22:54:57
> +-- 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, t3;
> +# End of the test for bug#12603183.
> set optimizer_switch=default;
> set optimizer_join_cache_level=default;
>
> === modified file 'mysql-test/r/subquery_sj_dupsweed.result'
> --- a/mysql-test/r/subquery_sj_dupsweed.result 2011-03-17 11:23:06 +0000
> +++ b/mysql-test/r/subquery_sj_dupsweed.result 2011-06-01 10:27:29 +0000
> @@ -5428,4 +5428,121 @@ a
> 0
> DROP TABLE t1;
> DROP VIEW v1;
> +#
> +# Bug#12603183: Segfault in hp_movelink
> +#
> +CREATE TABLE t1 (
> +pk int NOT NULL,
> +col_int_nokey int NOT NULL,
> +col_int_key int NOT NULL,
> +col_date_key date NOT NULL,
> +col_date_nokey date NOT NULL,
> +col_time_key time NOT NULL,
> +col_time_nokey time NOT NULL,
> +col_datetime_key datetime NOT NULL,
> +col_datetime_nokey datetime NOT NULL,
> +col_varchar_key varchar(1) NOT NULL,
> +col_varchar_nokey varchar(1) NOT NULL,
> +PRIMARY KEY (pk),
> +KEY col_int_key (col_int_key),
> +KEY col_date_key(col_date_key),
> +KEY col_time_key(col_time_key),
> +KEY col_datetime_key(col_datetime_key),
> +KEY col_varchar_key(col_varchar_key, col_int_key)
> +);
> +INSERT INTO t1 VALUES
> +(10,1,7,'2007-04-28','2007-04-28','00:00:00','00:00:00','2004-06-06
> 04:22:12','2004-06-06 04:22:12','v','v'),
> +(11,7,0,'2006-04-05','2006-04-05','00:00:00','00:00:00','2005-11-13
> 01:12:31','2005-11-13 01:12:31','s','s'),
> +(12,4,9,'2002-02-02','2002-02-02','06:35:17','06:35:17','2002-05-04
> 01:50:00','2002-05-04 01:50:00','l','l'),
> +(13,7,3,'2002-06-07','2002-06-07','18:07:14','18:07:14','2004-10-27
> 10:28:45','2004-10-27 10:28:45','y','y'),
> +(14,0,4,'0000-00-00','0000-00-00','20:36:52','20:36:52','2006-07-22
> 05:24:23','2006-07-22 05:24:23','c','c'),
> +(15,2,2,'2003-01-13','2003-01-13','21:29:07','21:29:07','2002-05-16
> 21:34:03','2002-05-16 21:34:03','i','i'),
> +(16,9,5,'2006-07-07','2006-07-07','23:45:57','23:45:57','2008-04-17
> 10:45:30','2008-04-17 10:45:30','h','h'),
> +(17,4,3,'0000-00-00','0000-00-00','22:54:57','22:54:57','2009-04-21
> 02:58:02','2009-04-21 02:58:02','q','q'),
> +(18,0,1,'2002-06-15','2002-06-15','18:45:09','18:45:09','2008-01-11
> 11:01:51','2008-01-11 11:01:51','a','a'),
> +(19,9,3,'2006-07-09','2006-07-09','14:30:46','14:30:46','1900-01-01
> 00:00:00','1900-01-01 00:00:00','v','v'),
> +(20,1,6,'2001-04-17','2001-04-17','19:23:43','19:23:43','2007-05-17
> 18:24:57','2007-05-17 18:24:57','u','u'),
> +(21,3,7,'2005-12-22','2005-12-22','03:39:30','03:39:30','2007-08-07
> 00:00:00','2007-08-07 00:00:00','s','s'),
> +(22,8,5,'2009-05-03','2009-05-03','23:37:52','23:37:52','2001-08-28
> 00:00:00','2001-08-28 00:00:00','y','y'),
> +(23,8,1,'2003-05-28','2003-05-28','16:59:30','16:59:30','2004-04-16
> 00:27:28','2004-04-16 00:27:28','z','z'),
> +(24,18,204,'0000-00-00','0000-00-00','22:21:15','22:21:15','2005-05-03
> 07:06:22','2005-05-03 07:06:22','h','h'),
> +(25,84,224,'2001-11-03','2001-11-03','12:24:37','12:24:37','2009-03-11
> 17:09:50','2009-03-11 17:09:50','p','p'),
> +(26,6,9,'1900-01-01','1900-01-01','15:02:08','15:02:08','2007-12-08
> 01:54:28','2007-12-08 01:54:28','e','e'),
> +(27,3,5,'2000-02-17','2000-02-17','00:00:00','00:00:00','2009-07-28
> 18:19:54','2009-07-28 18:19:54','i','i'),
> +(28,6,0,'2001-01-23','2001-01-23','08:23:30','08:23:30','2008-06-08
> 00:00:00','2008-06-08 00:00:00','y','y'),
> +(29,6,3,'0000-00-00','0000-00-00','08:32:22','08:32:22','2005-02-09
> 09:20:26','2005-02-09 09:20:26','w','w');
> +CREATE TABLE t2 (
> +pk int NOT NULL,
> +col_date_nokey date NOT NULL,
> +PRIMARY KEY (pk)
> +);
> +INSERT INTO t2 VALUES
> +(1, '0000-00-00'),
> +(2, '2004-09-18'),
> +(3, '2009-12-01'),
> +(4, '2004-12-17'),
> +(5, '2000-03-14'),
> +(6, '2000-10-08'),
> +(7, '2006-05-25'),
> +(8, '2008-01-23'),
> +(9, '2007-06-18'),
> +(10, '2002-10-13'),
> +(11, '1900-01-01'),
> +(12, '0000-00-00'),
> +(13, '2006-03-09'),
> +(14, '2001-06-05'),
> +(15, '2006-05-28'),
> +(16, '2001-04-19'),
> +(17, '1900-01-01'),
> +(18, '2004-08-20'),
> +(19, '2004-10-10'),
> +(20, '2000-04-02');
> +CREATE TABLE t3 (
> +pk int NOT NULL,
> +col_int_key int NOT NULL,
> +col_varchar_key varchar(1) NOT NULL,
> +PRIMARY KEY (pk),
> +KEY col_int_key(col_int_key),
> +KEY col_varchar_key(col_varchar_key, col_int_key)
> +);
> +INSERT INTO t3 VALUES
> +(10, 7, 'b');
> +SELECT grandparent1.col_time_key AS g1
> +FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_date_nokey)
> +WHERE (grandparent1.col_varchar_key, grandparent1.col_varchar_key) IN
> +(SELECT parent1.col_varchar_nokey AS p1, parent1.col_varchar_nokey AS p2
> +FROM t1 AS parent1
> +WHERE parent1.col_varchar_key IN
> +(SELECT DISTINCT child1.col_varchar_nokey AS c1
> +FROM t1 AS child1 LEFT JOIN t3 AS child2
> +ON (child1.col_varchar_key> child2.col_varchar_key))
> +AND
> +(parent1.col_int_nokey<= parent1.pk OR
> +grandparent1.pk> 8))
> +AND
> +grandparent1.col_varchar_nokey<> 'w' AND
> +grandparent1.col_date_key<= '2004-09-11'
> +ORDER BY g1;
> +g1
> +00:00:00
> +06:35:17
> +08:23:30
> +12:24:37
> +15:02:08
> +15:02:08
> +16:59:30
> +18:07:14
> +18:45:09
> +19:23:43
> +20:36:52
> +20:36:52
> +21:29:07
> +22:21:15
> +22:21:15
> +22:54:57
> +22:54:57
> +-- 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, t3;
> +# End of the test for bug#12603183.
> set optimizer_switch=default;
>
> === modified file 'mysql-test/r/subquery_sj_dupsweed_jcl6.result'
> --- a/mysql-test/r/subquery_sj_dupsweed_jcl6.result 2011-03-17 11:23:06 +0000
> +++ b/mysql-test/r/subquery_sj_dupsweed_jcl6.result 2011-06-01 10:27:29 +0000
> @@ -5432,5 +5432,122 @@ a
> 0
> DROP TABLE t1;
> DROP VIEW v1;
> +#
> +# Bug#12603183: Segfault in hp_movelink
> +#
> +CREATE TABLE t1 (
> +pk int NOT NULL,
> +col_int_nokey int NOT NULL,
> +col_int_key int NOT NULL,
> +col_date_key date NOT NULL,
> +col_date_nokey date NOT NULL,
> +col_time_key time NOT NULL,
> +col_time_nokey time NOT NULL,
> +col_datetime_key datetime NOT NULL,
> +col_datetime_nokey datetime NOT NULL,
> +col_varchar_key varchar(1) NOT NULL,
> +col_varchar_nokey varchar(1) NOT NULL,
> +PRIMARY KEY (pk),
> +KEY col_int_key (col_int_key),
> +KEY col_date_key(col_date_key),
> +KEY col_time_key(col_time_key),
> +KEY col_datetime_key(col_datetime_key),
> +KEY col_varchar_key(col_varchar_key, col_int_key)
> +);
> +INSERT INTO t1 VALUES
> +(10,1,7,'2007-04-28','2007-04-28','00:00:00','00:00:00','2004-06-06
> 04:22:12','2004-06-06 04:22:12','v','v'),
> +(11,7,0,'2006-04-05','2006-04-05','00:00:00','00:00:00','2005-11-13
> 01:12:31','2005-11-13 01:12:31','s','s'),
> +(12,4,9,'2002-02-02','2002-02-02','06:35:17','06:35:17','2002-05-04
> 01:50:00','2002-05-04 01:50:00','l','l'),
> +(13,7,3,'2002-06-07','2002-06-07','18:07:14','18:07:14','2004-10-27
> 10:28:45','2004-10-27 10:28:45','y','y'),
> +(14,0,4,'0000-00-00','0000-00-00','20:36:52','20:36:52','2006-07-22
> 05:24:23','2006-07-22 05:24:23','c','c'),
> +(15,2,2,'2003-01-13','2003-01-13','21:29:07','21:29:07','2002-05-16
> 21:34:03','2002-05-16 21:34:03','i','i'),
> +(16,9,5,'2006-07-07','2006-07-07','23:45:57','23:45:57','2008-04-17
> 10:45:30','2008-04-17 10:45:30','h','h'),
> +(17,4,3,'0000-00-00','0000-00-00','22:54:57','22:54:57','2009-04-21
> 02:58:02','2009-04-21 02:58:02','q','q'),
> +(18,0,1,'2002-06-15','2002-06-15','18:45:09','18:45:09','2008-01-11
> 11:01:51','2008-01-11 11:01:51','a','a'),
> +(19,9,3,'2006-07-09','2006-07-09','14:30:46','14:30:46','1900-01-01
> 00:00:00','1900-01-01 00:00:00','v','v'),
> +(20,1,6,'2001-04-17','2001-04-17','19:23:43','19:23:43','2007-05-17
> 18:24:57','2007-05-17 18:24:57','u','u'),
> +(21,3,7,'2005-12-22','2005-12-22','03:39:30','03:39:30','2007-08-07
> 00:00:00','2007-08-07 00:00:00','s','s'),
> +(22,8,5,'2009-05-03','2009-05-03','23:37:52','23:37:52','2001-08-28
> 00:00:00','2001-08-28 00:00:00','y','y'),
> +(23,8,1,'2003-05-28','2003-05-28','16:59:30','16:59:30','2004-04-16
> 00:27:28','2004-04-16 00:27:28','z','z'),
> +(24,18,204,'0000-00-00','0000-00-00','22:21:15','22:21:15','2005-05-03
> 07:06:22','2005-05-03 07:06:22','h','h'),
> +(25,84,224,'2001-11-03','2001-11-03','12:24:37','12:24:37','2009-03-11
> 17:09:50','2009-03-11 17:09:50','p','p'),
> +(26,6,9,'1900-01-01','1900-01-01','15:02:08','15:02:08','2007-12-08
> 01:54:28','2007-12-08 01:54:28','e','e'),
> +(27,3,5,'2000-02-17','2000-02-17','00:00:00','00:00:00','2009-07-28
> 18:19:54','2009-07-28 18:19:54','i','i'),
> +(28,6,0,'2001-01-23','2001-01-23','08:23:30','08:23:30','2008-06-08
> 00:00:00','2008-06-08 00:00:00','y','y'),
> +(29,6,3,'0000-00-00','0000-00-00','08:32:22','08:32:22','2005-02-09
> 09:20:26','2005-02-09 09:20:26','w','w');
> +CREATE TABLE t2 (
> +pk int NOT NULL,
> +col_date_nokey date NOT NULL,
> +PRIMARY KEY (pk)
> +);
> +INSERT INTO t2 VALUES
> +(1, '0000-00-00'),
> +(2, '2004-09-18'),
> +(3, '2009-12-01'),
> +(4, '2004-12-17'),
> +(5, '2000-03-14'),
> +(6, '2000-10-08'),
> +(7, '2006-05-25'),
> +(8, '2008-01-23'),
> +(9, '2007-06-18'),
> +(10, '2002-10-13'),
> +(11, '1900-01-01'),
> +(12, '0000-00-00'),
> +(13, '2006-03-09'),
> +(14, '2001-06-05'),
> +(15, '2006-05-28'),
> +(16, '2001-04-19'),
> +(17, '1900-01-01'),
> +(18, '2004-08-20'),
> +(19, '2004-10-10'),
> +(20, '2000-04-02');
> +CREATE TABLE t3 (
> +pk int NOT NULL,
> +col_int_key int NOT NULL,
> +col_varchar_key varchar(1) NOT NULL,
> +PRIMARY KEY (pk),
> +KEY col_int_key(col_int_key),
> +KEY col_varchar_key(col_varchar_key, col_int_key)
> +);
> +INSERT INTO t3 VALUES
> +(10, 7, 'b');
> +SELECT grandparent1.col_time_key AS g1
> +FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_date_nokey)
> +WHERE (grandparent1.col_varchar_key, grandparent1.col_varchar_key) IN
> +(SELECT parent1.col_varchar_nokey AS p1, parent1.col_varchar_nokey AS p2
> +FROM t1 AS parent1
> +WHERE parent1.col_varchar_key IN
> +(SELECT DISTINCT child1.col_varchar_nokey AS c1
> +FROM t1 AS child1 LEFT JOIN t3 AS child2
> +ON (child1.col_varchar_key> child2.col_varchar_key))
> +AND
> +(parent1.col_int_nokey<= parent1.pk OR
> +grandparent1.pk> 8))
> +AND
> +grandparent1.col_varchar_nokey<> 'w' AND
> +grandparent1.col_date_key<= '2004-09-11'
> +ORDER BY g1;
> +g1
> +00:00:00
> +06:35:17
> +08:23:30
> +12:24:37
> +15:02:08
> +15:02:08
> +16:59:30
> +18:07:14
> +18:45:09
> +19:23:43
> +20:36:52
> +20:36:52
> +21:29:07
> +22:21:15
> +22:21:15
> +22:54:57
> +22:54:57
> +-- 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, t3;
> +# End of the test for bug#12603183.
> set optimizer_switch=default;
> set optimizer_join_cache_level=default;
>
> === modified file 'mysql-test/r/subquery_sj_dupsweed_jcl7.result'
> --- a/mysql-test/r/subquery_sj_dupsweed_jcl7.result 2011-03-17 11:23:06 +0000
> +++ b/mysql-test/r/subquery_sj_dupsweed_jcl7.result 2011-06-01 10:27:29 +0000
> @@ -5432,5 +5432,122 @@ a
> 0
> DROP TABLE t1;
> DROP VIEW v1;
> +#
> +# Bug#12603183: Segfault in hp_movelink
> +#
> +CREATE TABLE t1 (
> +pk int NOT NULL,
> +col_int_nokey int NOT NULL,
> +col_int_key int NOT NULL,
> +col_date_key date NOT NULL,
> +col_date_nokey date NOT NULL,
> +col_time_key time NOT NULL,
> +col_time_nokey time NOT NULL,
> +col_datetime_key datetime NOT NULL,
> +col_datetime_nokey datetime NOT NULL,
> +col_varchar_key varchar(1) NOT NULL,
> +col_varchar_nokey varchar(1) NOT NULL,
> +PRIMARY KEY (pk),
> +KEY col_int_key (col_int_key),
> +KEY col_date_key(col_date_key),
> +KEY col_time_key(col_time_key),
> +KEY col_datetime_key(col_datetime_key),
> +KEY col_varchar_key(col_varchar_key, col_int_key)
> +);
> +INSERT INTO t1 VALUES
> +(10,1,7,'2007-04-28','2007-04-28','00:00:00','00:00:00','2004-06-06
> 04:22:12','2004-06-06 04:22:12','v','v'),
> +(11,7,0,'2006-04-05','2006-04-05','00:00:00','00:00:00','2005-11-13
> 01:12:31','2005-11-13 01:12:31','s','s'),
> +(12,4,9,'2002-02-02','2002-02-02','06:35:17','06:35:17','2002-05-04
> 01:50:00','2002-05-04 01:50:00','l','l'),
> +(13,7,3,'2002-06-07','2002-06-07','18:07:14','18:07:14','2004-10-27
> 10:28:45','2004-10-27 10:28:45','y','y'),
> +(14,0,4,'0000-00-00','0000-00-00','20:36:52','20:36:52','2006-07-22
> 05:24:23','2006-07-22 05:24:23','c','c'),
> +(15,2,2,'2003-01-13','2003-01-13','21:29:07','21:29:07','2002-05-16
> 21:34:03','2002-05-16 21:34:03','i','i'),
> +(16,9,5,'2006-07-07','2006-07-07','23:45:57','23:45:57','2008-04-17
> 10:45:30','2008-04-17 10:45:30','h','h'),
> +(17,4,3,'0000-00-00','0000-00-00','22:54:57','22:54:57','2009-04-21
> 02:58:02','2009-04-21 02:58:02','q','q'),
> +(18,0,1,'2002-06-15','2002-06-15','18:45:09','18:45:09','2008-01-11
> 11:01:51','2008-01-11 11:01:51','a','a'),
> +(19,9,3,'2006-07-09','2006-07-09','14:30:46','14:30:46','1900-01-01
> 00:00:00','1900-01-01 00:00:00','v','v'),
> +(20,1,6,'2001-04-17','2001-04-17','19:23:43','19:23:43','2007-05-17
> 18:24:57','2007-05-17 18:24:57','u','u'),
> +(21,3,7,'2005-12-22','2005-12-22','03:39:30','03:39:30','2007-08-07
> 00:00:00','2007-08-07 00:00:00','s','s'),
> +(22,8,5,'2009-05-03','2009-05-03','23:37:52','23:37:52','2001-08-28
> 00:00:00','2001-08-28 00:00:00','y','y'),
> +(23,8,1,'2003-05-28','2003-05-28','16:59:30','16:59:30','2004-04-16
> 00:27:28','2004-04-16 00:27:28','z','z'),
> +(24,18,204,'0000-00-00','0000-00-00','22:21:15','22:21:15','2005-05-03
> 07:06:22','2005-05-03 07:06:22','h','h'),
> +(25,84,224,'2001-11-03','2001-11-03','12:24:37','12:24:37','2009-03-11
> 17:09:50','2009-03-11 17:09:50','p','p'),
> +(26,6,9,'1900-01-01','1900-01-01','15:02:08','15:02:08','2007-12-08
> 01:54:28','2007-12-08 01:54:28','e','e'),
> +(27,3,5,'2000-02-17','2000-02-17','00:00:00','00:00:00','2009-07-28
> 18:19:54','2009-07-28 18:19:54','i','i'),
> +(28,6,0,'2001-01-23','2001-01-23','08:23:30','08:23:30','2008-06-08
> 00:00:00','2008-06-08 00:00:00','y','y'),
> +(29,6,3,'0000-00-00','0000-00-00','08:32:22','08:32:22','2005-02-09
> 09:20:26','2005-02-09 09:20:26','w','w');
> +CREATE TABLE t2 (
> +pk int NOT NULL,
> +col_date_nokey date NOT NULL,
> +PRIMARY KEY (pk)
> +);
> +INSERT INTO t2 VALUES
> +(1, '0000-00-00'),
> +(2, '2004-09-18'),
> +(3, '2009-12-01'),
> +(4, '2004-12-17'),
> +(5, '2000-03-14'),
> +(6, '2000-10-08'),
> +(7, '2006-05-25'),
> +(8, '2008-01-23'),
> +(9, '2007-06-18'),
> +(10, '2002-10-13'),
> +(11, '1900-01-01'),
> +(12, '0000-00-00'),
> +(13, '2006-03-09'),
> +(14, '2001-06-05'),
> +(15, '2006-05-28'),
> +(16, '2001-04-19'),
> +(17, '1900-01-01'),
> +(18, '2004-08-20'),
> +(19, '2004-10-10'),
> +(20, '2000-04-02');
> +CREATE TABLE t3 (
> +pk int NOT NULL,
> +col_int_key int NOT NULL,
> +col_varchar_key varchar(1) NOT NULL,
> +PRIMARY KEY (pk),
> +KEY col_int_key(col_int_key),
> +KEY col_varchar_key(col_varchar_key, col_int_key)
> +);
> +INSERT INTO t3 VALUES
> +(10, 7, 'b');
> +SELECT grandparent1.col_time_key AS g1
> +FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_date_nokey)
> +WHERE (grandparent1.col_varchar_key, grandparent1.col_varchar_key) IN
> +(SELECT parent1.col_varchar_nokey AS p1, parent1.col_varchar_nokey AS p2
> +FROM t1 AS parent1
> +WHERE parent1.col_varchar_key IN
> +(SELECT DISTINCT child1.col_varchar_nokey AS c1
> +FROM t1 AS child1 LEFT JOIN t3 AS child2
> +ON (child1.col_varchar_key> child2.col_varchar_key))
> +AND
> +(parent1.col_int_nokey<= parent1.pk OR
> +grandparent1.pk> 8))
> +AND
> +grandparent1.col_varchar_nokey<> 'w' AND
> +grandparent1.col_date_key<= '2004-09-11'
> +ORDER BY g1;
> +g1
> +00:00:00
> +06:35:17
> +08:23:30
> +12:24:37
> +15:02:08
> +15:02:08
> +16:59:30
> +18:07:14
> +18:45:09
> +19:23:43
> +20:36:52
> +20:36:52
> +21:29:07
> +22:21:15
> +22:21:15
> +22:54:57
> +22:54:57
> +-- 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, t3;
> +# End of the test for bug#12603183.
> set optimizer_switch=default;
> set optimizer_join_cache_level=default;
>
> === modified file 'mysql-test/r/subquery_sj_firstmatch.result'
> --- a/mysql-test/r/subquery_sj_firstmatch.result 2011-03-17 11:23:06 +0000
> +++ b/mysql-test/r/subquery_sj_firstmatch.result 2011-06-01 10:27:29 +0000
> @@ -5430,6 +5430,123 @@ a
> DROP TABLE t1;
> DROP VIEW v1;
> #
> +# Bug#12603183: Segfault in hp_movelink
> +#
> +CREATE TABLE t1 (
> +pk int NOT NULL,
> +col_int_nokey int NOT NULL,
> +col_int_key int NOT NULL,
> +col_date_key date NOT NULL,
> +col_date_nokey date NOT NULL,
> +col_time_key time NOT NULL,
> +col_time_nokey time NOT NULL,
> +col_datetime_key datetime NOT NULL,
> +col_datetime_nokey datetime NOT NULL,
> +col_varchar_key varchar(1) NOT NULL,
> +col_varchar_nokey varchar(1) NOT NULL,
> +PRIMARY KEY (pk),
> +KEY col_int_key (col_int_key),
> +KEY col_date_key(col_date_key),
> +KEY col_time_key(col_time_key),
> +KEY col_datetime_key(col_datetime_key),
> +KEY col_varchar_key(col_varchar_key, col_int_key)
> +);
> +INSERT INTO t1 VALUES
> +(10,1,7,'2007-04-28','2007-04-28','00:00:00','00:00:00','2004-06-06
> 04:22:12','2004-06-06 04:22:12','v','v'),
> +(11,7,0,'2006-04-05','2006-04-05','00:00:00','00:00:00','2005-11-13
> 01:12:31','2005-11-13 01:12:31','s','s'),
> +(12,4,9,'2002-02-02','2002-02-02','06:35:17','06:35:17','2002-05-04
> 01:50:00','2002-05-04 01:50:00','l','l'),
> +(13,7,3,'2002-06-07','2002-06-07','18:07:14','18:07:14','2004-10-27
> 10:28:45','2004-10-27 10:28:45','y','y'),
> +(14,0,4,'0000-00-00','0000-00-00','20:36:52','20:36:52','2006-07-22
> 05:24:23','2006-07-22 05:24:23','c','c'),
> +(15,2,2,'2003-01-13','2003-01-13','21:29:07','21:29:07','2002-05-16
> 21:34:03','2002-05-16 21:34:03','i','i'),
> +(16,9,5,'2006-07-07','2006-07-07','23:45:57','23:45:57','2008-04-17
> 10:45:30','2008-04-17 10:45:30','h','h'),
> +(17,4,3,'0000-00-00','0000-00-00','22:54:57','22:54:57','2009-04-21
> 02:58:02','2009-04-21 02:58:02','q','q'),
> +(18,0,1,'2002-06-15','2002-06-15','18:45:09','18:45:09','2008-01-11
> 11:01:51','2008-01-11 11:01:51','a','a'),
> +(19,9,3,'2006-07-09','2006-07-09','14:30:46','14:30:46','1900-01-01
> 00:00:00','1900-01-01 00:00:00','v','v'),
> +(20,1,6,'2001-04-17','2001-04-17','19:23:43','19:23:43','2007-05-17
> 18:24:57','2007-05-17 18:24:57','u','u'),
> +(21,3,7,'2005-12-22','2005-12-22','03:39:30','03:39:30','2007-08-07
> 00:00:00','2007-08-07 00:00:00','s','s'),
> +(22,8,5,'2009-05-03','2009-05-03','23:37:52','23:37:52','2001-08-28
> 00:00:00','2001-08-28 00:00:00','y','y'),
> +(23,8,1,'2003-05-28','2003-05-28','16:59:30','16:59:30','2004-04-16
> 00:27:28','2004-04-16 00:27:28','z','z'),
> +(24,18,204,'0000-00-00','0000-00-00','22:21:15','22:21:15','2005-05-03
> 07:06:22','2005-05-03 07:06:22','h','h'),
> +(25,84,224,'2001-11-03','2001-11-03','12:24:37','12:24:37','2009-03-11
> 17:09:50','2009-03-11 17:09:50','p','p'),
> +(26,6,9,'1900-01-01','1900-01-01','15:02:08','15:02:08','2007-12-08
> 01:54:28','2007-12-08 01:54:28','e','e'),
> +(27,3,5,'2000-02-17','2000-02-17','00:00:00','00:00:00','2009-07-28
> 18:19:54','2009-07-28 18:19:54','i','i'),
> +(28,6,0,'2001-01-23','2001-01-23','08:23:30','08:23:30','2008-06-08
> 00:00:00','2008-06-08 00:00:00','y','y'),
> +(29,6,3,'0000-00-00','0000-00-00','08:32:22','08:32:22','2005-02-09
> 09:20:26','2005-02-09 09:20:26','w','w');
> +CREATE TABLE t2 (
> +pk int NOT NULL,
> +col_date_nokey date NOT NULL,
> +PRIMARY KEY (pk)
> +);
> +INSERT INTO t2 VALUES
> +(1, '0000-00-00'),
> +(2, '2004-09-18'),
> +(3, '2009-12-01'),
> +(4, '2004-12-17'),
> +(5, '2000-03-14'),
> +(6, '2000-10-08'),
> +(7, '2006-05-25'),
> +(8, '2008-01-23'),
> +(9, '2007-06-18'),
> +(10, '2002-10-13'),
> +(11, '1900-01-01'),
> +(12, '0000-00-00'),
> +(13, '2006-03-09'),
> +(14, '2001-06-05'),
> +(15, '2006-05-28'),
> +(16, '2001-04-19'),
> +(17, '1900-01-01'),
> +(18, '2004-08-20'),
> +(19, '2004-10-10'),
> +(20, '2000-04-02');
> +CREATE TABLE t3 (
> +pk int NOT NULL,
> +col_int_key int NOT NULL,
> +col_varchar_key varchar(1) NOT NULL,
> +PRIMARY KEY (pk),
> +KEY col_int_key(col_int_key),
> +KEY col_varchar_key(col_varchar_key, col_int_key)
> +);
> +INSERT INTO t3 VALUES
> +(10, 7, 'b');
> +SELECT grandparent1.col_time_key AS g1
> +FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_date_nokey)
> +WHERE (grandparent1.col_varchar_key, grandparent1.col_varchar_key) IN
> +(SELECT parent1.col_varchar_nokey AS p1, parent1.col_varchar_nokey AS p2
> +FROM t1 AS parent1
> +WHERE parent1.col_varchar_key IN
> +(SELECT DISTINCT child1.col_varchar_nokey AS c1
> +FROM t1 AS child1 LEFT JOIN t3 AS child2
> +ON (child1.col_varchar_key> child2.col_varchar_key))
> +AND
> +(parent1.col_int_nokey<= parent1.pk OR
> +grandparent1.pk> 8))
> +AND
> +grandparent1.col_varchar_nokey<> 'w' AND
> +grandparent1.col_date_key<= '2004-09-11'
> +ORDER BY g1;
> +g1
> +00:00:00
> +06:35:17
> +08:23:30
> +12:24:37
> +15:02:08
> +15:02:08
> +16:59:30
> +18:07:14
> +18:45:09
> +19:23:43
> +20:36:52
> +20:36:52
> +21:29:07
> +22:21:15
> +22:21:15
> +22:54:57
> +22:54:57
> +-- 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, t3;
> +# End of the test for bug#12603183.
> +#
> # Bug#51457 Firstmatch semijoin strategy gives wrong results for
> # certain query plans
> #
>
> === modified file 'mysql-test/r/subquery_sj_firstmatch_jcl6.result'
> --- a/mysql-test/r/subquery_sj_firstmatch_jcl6.result 2011-03-17 11:23:06 +0000
> +++ b/mysql-test/r/subquery_sj_firstmatch_jcl6.result 2011-06-01 10:27:29 +0000
> @@ -5434,6 +5434,123 @@ a
> DROP TABLE t1;
> DROP VIEW v1;
> #
> +# Bug#12603183: Segfault in hp_movelink
> +#
> +CREATE TABLE t1 (
> +pk int NOT NULL,
> +col_int_nokey int NOT NULL,
> +col_int_key int NOT NULL,
> +col_date_key date NOT NULL,
> +col_date_nokey date NOT NULL,
> +col_time_key time NOT NULL,
> +col_time_nokey time NOT NULL,
> +col_datetime_key datetime NOT NULL,
> +col_datetime_nokey datetime NOT NULL,
> +col_varchar_key varchar(1) NOT NULL,
> +col_varchar_nokey varchar(1) NOT NULL,
> +PRIMARY KEY (pk),
> +KEY col_int_key (col_int_key),
> +KEY col_date_key(col_date_key),
> +KEY col_time_key(col_time_key),
> +KEY col_datetime_key(col_datetime_key),
> +KEY col_varchar_key(col_varchar_key, col_int_key)
> +);
> +INSERT INTO t1 VALUES
> +(10,1,7,'2007-04-28','2007-04-28','00:00:00','00:00:00','2004-06-06
> 04:22:12','2004-06-06 04:22:12','v','v'),
> +(11,7,0,'2006-04-05','2006-04-05','00:00:00','00:00:00','2005-11-13
> 01:12:31','2005-11-13 01:12:31','s','s'),
> +(12,4,9,'2002-02-02','2002-02-02','06:35:17','06:35:17','2002-05-04
> 01:50:00','2002-05-04 01:50:00','l','l'),
> +(13,7,3,'2002-06-07','2002-06-07','18:07:14','18:07:14','2004-10-27
> 10:28:45','2004-10-27 10:28:45','y','y'),
> +(14,0,4,'0000-00-00','0000-00-00','20:36:52','20:36:52','2006-07-22
> 05:24:23','2006-07-22 05:24:23','c','c'),
> +(15,2,2,'2003-01-13','2003-01-13','21:29:07','21:29:07','2002-05-16
> 21:34:03','2002-05-16 21:34:03','i','i'),
> +(16,9,5,'2006-07-07','2006-07-07','23:45:57','23:45:57','2008-04-17
> 10:45:30','2008-04-17 10:45:30','h','h'),
> +(17,4,3,'0000-00-00','0000-00-00','22:54:57','22:54:57','2009-04-21
> 02:58:02','2009-04-21 02:58:02','q','q'),
> +(18,0,1,'2002-06-15','2002-06-15','18:45:09','18:45:09','2008-01-11
> 11:01:51','2008-01-11 11:01:51','a','a'),
> +(19,9,3,'2006-07-09','2006-07-09','14:30:46','14:30:46','1900-01-01
> 00:00:00','1900-01-01 00:00:00','v','v'),
> +(20,1,6,'2001-04-17','2001-04-17','19:23:43','19:23:43','2007-05-17
> 18:24:57','2007-05-17 18:24:57','u','u'),
> +(21,3,7,'2005-12-22','2005-12-22','03:39:30','03:39:30','2007-08-07
> 00:00:00','2007-08-07 00:00:00','s','s'),
> +(22,8,5,'2009-05-03','2009-05-03','23:37:52','23:37:52','2001-08-28
> 00:00:00','2001-08-28 00:00:00','y','y'),
> +(23,8,1,'2003-05-28','2003-05-28','16:59:30','16:59:30','2004-04-16
> 00:27:28','2004-04-16 00:27:28','z','z'),
> +(24,18,204,'0000-00-00','0000-00-00','22:21:15','22:21:15','2005-05-03
> 07:06:22','2005-05-03 07:06:22','h','h'),
> +(25,84,224,'2001-11-03','2001-11-03','12:24:37','12:24:37','2009-03-11
> 17:09:50','2009-03-11 17:09:50','p','p'),
> +(26,6,9,'1900-01-01','1900-01-01','15:02:08','15:02:08','2007-12-08
> 01:54:28','2007-12-08 01:54:28','e','e'),
> +(27,3,5,'2000-02-17','2000-02-17','00:00:00','00:00:00','2009-07-28
> 18:19:54','2009-07-28 18:19:54','i','i'),
> +(28,6,0,'2001-01-23','2001-01-23','08:23:30','08:23:30','2008-06-08
> 00:00:00','2008-06-08 00:00:00','y','y'),
> +(29,6,3,'0000-00-00','0000-00-00','08:32:22','08:32:22','2005-02-09
> 09:20:26','2005-02-09 09:20:26','w','w');
> +CREATE TABLE t2 (
> +pk int NOT NULL,
> +col_date_nokey date NOT NULL,
> +PRIMARY KEY (pk)
> +);
> +INSERT INTO t2 VALUES
> +(1, '0000-00-00'),
> +(2, '2004-09-18'),
> +(3, '2009-12-01'),
> +(4, '2004-12-17'),
> +(5, '2000-03-14'),
> +(6, '2000-10-08'),
> +(7, '2006-05-25'),
> +(8, '2008-01-23'),
> +(9, '2007-06-18'),
> +(10, '2002-10-13'),
> +(11, '1900-01-01'),
> +(12, '0000-00-00'),
> +(13, '2006-03-09'),
> +(14, '2001-06-05'),
> +(15, '2006-05-28'),
> +(16, '2001-04-19'),
> +(17, '1900-01-01'),
> +(18, '2004-08-20'),
> +(19, '2004-10-10'),
> +(20, '2000-04-02');
> +CREATE TABLE t3 (
> +pk int NOT NULL,
> +col_int_key int NOT NULL,
> +col_varchar_key varchar(1) NOT NULL,
> +PRIMARY KEY (pk),
> +KEY col_int_key(col_int_key),
> +KEY col_varchar_key(col_varchar_key, col_int_key)
> +);
> +INSERT INTO t3 VALUES
> +(10, 7, 'b');
> +SELECT grandparent1.col_time_key AS g1
> +FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_date_nokey)
> +WHERE (grandparent1.col_varchar_key, grandparent1.col_varchar_key) IN
> +(SELECT parent1.col_varchar_nokey AS p1, parent1.col_varchar_nokey AS p2
> +FROM t1 AS parent1
> +WHERE parent1.col_varchar_key IN
> +(SELECT DISTINCT child1.col_varchar_nokey AS c1
> +FROM t1 AS child1 LEFT JOIN t3 AS child2
> +ON (child1.col_varchar_key> child2.col_varchar_key))
> +AND
> +(parent1.col_int_nokey<= parent1.pk OR
> +grandparent1.pk> 8))
> +AND
> +grandparent1.col_varchar_nokey<> 'w' AND
> +grandparent1.col_date_key<= '2004-09-11'
> +ORDER BY g1;
> +g1
> +00:00:00
> +06:35:17
> +08:23:30
> +12:24:37
> +15:02:08
> +15:02:08
> +16:59:30
> +18:07:14
> +18:45:09
> +19:23:43
> +20:36:52
> +20:36:52
> +21:29:07
> +22:21:15
> +22:21:15
> +22:54:57
> +22:54:57
> +-- 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, t3;
> +# End of the test for bug#12603183.
> +#
> # Bug#51457 Firstmatch semijoin strategy gives wrong results for
> # certain query plans
> #
>
> === modified file 'mysql-test/r/subquery_sj_firstmatch_jcl7.result'
> --- a/mysql-test/r/subquery_sj_firstmatch_jcl7.result 2011-03-17 11:23:06 +0000
> +++ b/mysql-test/r/subquery_sj_firstmatch_jcl7.result 2011-06-01 10:27:29 +0000
> @@ -5434,6 +5434,123 @@ a
> DROP TABLE t1;
> DROP VIEW v1;
> #
> +# Bug#12603183: Segfault in hp_movelink
> +#
> +CREATE TABLE t1 (
> +pk int NOT NULL,
> +col_int_nokey int NOT NULL,
> +col_int_key int NOT NULL,
> +col_date_key date NOT NULL,
> +col_date_nokey date NOT NULL,
> +col_time_key time NOT NULL,
> +col_time_nokey time NOT NULL,
> +col_datetime_key datetime NOT NULL,
> +col_datetime_nokey datetime NOT NULL,
> +col_varchar_key varchar(1) NOT NULL,
> +col_varchar_nokey varchar(1) NOT NULL,
> +PRIMARY KEY (pk),
> +KEY col_int_key (col_int_key),
> +KEY col_date_key(col_date_key),
> +KEY col_time_key(col_time_key),
> +KEY col_datetime_key(col_datetime_key),
> +KEY col_varchar_key(col_varchar_key, col_int_key)
> +);
> +INSERT INTO t1 VALUES
> +(10,1,7,'2007-04-28','2007-04-28','00:00:00','00:00:00','2004-06-06
> 04:22:12','2004-06-06 04:22:12','v','v'),
> +(11,7,0,'2006-04-05','2006-04-05','00:00:00','00:00:00','2005-11-13
> 01:12:31','2005-11-13 01:12:31','s','s'),
> +(12,4,9,'2002-02-02','2002-02-02','06:35:17','06:35:17','2002-05-04
> 01:50:00','2002-05-04 01:50:00','l','l'),
> +(13,7,3,'2002-06-07','2002-06-07','18:07:14','18:07:14','2004-10-27
> 10:28:45','2004-10-27 10:28:45','y','y'),
> +(14,0,4,'0000-00-00','0000-00-00','20:36:52','20:36:52','2006-07-22
> 05:24:23','2006-07-22 05:24:23','c','c'),
> +(15,2,2,'2003-01-13','2003-01-13','21:29:07','21:29:07','2002-05-16
> 21:34:03','2002-05-16 21:34:03','i','i'),
> +(16,9,5,'2006-07-07','2006-07-07','23:45:57','23:45:57','2008-04-17
> 10:45:30','2008-04-17 10:45:30','h','h'),
> +(17,4,3,'0000-00-00','0000-00-00','22:54:57','22:54:57','2009-04-21
> 02:58:02','2009-04-21 02:58:02','q','q'),
> +(18,0,1,'2002-06-15','2002-06-15','18:45:09','18:45:09','2008-01-11
> 11:01:51','2008-01-11 11:01:51','a','a'),
> +(19,9,3,'2006-07-09','2006-07-09','14:30:46','14:30:46','1900-01-01
> 00:00:00','1900-01-01 00:00:00','v','v'),
> +(20,1,6,'2001-04-17','2001-04-17','19:23:43','19:23:43','2007-05-17
> 18:24:57','2007-05-17 18:24:57','u','u'),
> +(21,3,7,'2005-12-22','2005-12-22','03:39:30','03:39:30','2007-08-07
> 00:00:00','2007-08-07 00:00:00','s','s'),
> +(22,8,5,'2009-05-03','2009-05-03','23:37:52','23:37:52','2001-08-28
> 00:00:00','2001-08-28 00:00:00','y','y'),
> +(23,8,1,'2003-05-28','2003-05-28','16:59:30','16:59:30','2004-04-16
> 00:27:28','2004-04-16 00:27:28','z','z'),
> +(24,18,204,'0000-00-00','0000-00-00','22:21:15','22:21:15','2005-05-03
> 07:06:22','2005-05-03 07:06:22','h','h'),
> +(25,84,224,'2001-11-03','2001-11-03','12:24:37','12:24:37','2009-03-11
> 17:09:50','2009-03-11 17:09:50','p','p'),
> +(26,6,9,'1900-01-01','1900-01-01','15:02:08','15:02:08','2007-12-08
> 01:54:28','2007-12-08 01:54:28','e','e'),
> +(27,3,5,'2000-02-17','2000-02-17','00:00:00','00:00:00','2009-07-28
> 18:19:54','2009-07-28 18:19:54','i','i'),
> +(28,6,0,'2001-01-23','2001-01-23','08:23:30','08:23:30','2008-06-08
> 00:00:00','2008-06-08 00:00:00','y','y'),
> +(29,6,3,'0000-00-00','0000-00-00','08:32:22','08:32:22','2005-02-09
> 09:20:26','2005-02-09 09:20:26','w','w');
> +CREATE TABLE t2 (
> +pk int NOT NULL,
> +col_date_nokey date NOT NULL,
> +PRIMARY KEY (pk)
> +);
> +INSERT INTO t2 VALUES
> +(1, '0000-00-00'),
> +(2, '2004-09-18'),
> +(3, '2009-12-01'),
> +(4, '2004-12-17'),
> +(5, '2000-03-14'),
> +(6, '2000-10-08'),
> +(7, '2006-05-25'),
> +(8, '2008-01-23'),
> +(9, '2007-06-18'),
> +(10, '2002-10-13'),
> +(11, '1900-01-01'),
> +(12, '0000-00-00'),
> +(13, '2006-03-09'),
> +(14, '2001-06-05'),
> +(15, '2006-05-28'),
> +(16, '2001-04-19'),
> +(17, '1900-01-01'),
> +(18, '2004-08-20'),
> +(19, '2004-10-10'),
> +(20, '2000-04-02');
> +CREATE TABLE t3 (
> +pk int NOT NULL,
> +col_int_key int NOT NULL,
> +col_varchar_key varchar(1) NOT NULL,
> +PRIMARY KEY (pk),
> +KEY col_int_key(col_int_key),
> +KEY col_varchar_key(col_varchar_key, col_int_key)
> +);
> +INSERT INTO t3 VALUES
> +(10, 7, 'b');
> +SELECT grandparent1.col_time_key AS g1
> +FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_date_nokey)
> +WHERE (grandparent1.col_varchar_key, grandparent1.col_varchar_key) IN
> +(SELECT parent1.col_varchar_nokey AS p1, parent1.col_varchar_nokey AS p2
> +FROM t1 AS parent1
> +WHERE parent1.col_varchar_key IN
> +(SELECT DISTINCT child1.col_varchar_nokey AS c1
> +FROM t1 AS child1 LEFT JOIN t3 AS child2
> +ON (child1.col_varchar_key> child2.col_varchar_key))
> +AND
> +(parent1.col_int_nokey<= parent1.pk OR
> +grandparent1.pk> 8))
> +AND
> +grandparent1.col_varchar_nokey<> 'w' AND
> +grandparent1.col_date_key<= '2004-09-11'
> +ORDER BY g1;
> +g1
> +00:00:00
> +06:35:17
> +08:23:30
> +12:24:37
> +15:02:08
> +15:02:08
> +16:59:30
> +18:07:14
> +18:45:09
> +19:23:43
> +20:36:52
> +20:36:52
> +21:29:07
> +22:21:15
> +22:21:15
> +22:54:57
> +22:54:57
> +-- 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, t3;
> +# End of the test for bug#12603183.
> +#
> # 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-03-17 11:23:06 +0000
> +++ b/mysql-test/r/subquery_sj_loosescan.result 2011-06-01 10:27:29 +0000
> @@ -5429,4 +5429,121 @@ a
> 0
> DROP TABLE t1;
> DROP VIEW v1;
> +#
> +# Bug#12603183: Segfault in hp_movelink
> +#
> +CREATE TABLE t1 (
> +pk int NOT NULL,
> +col_int_nokey int NOT NULL,
> +col_int_key int NOT NULL,
> +col_date_key date NOT NULL,
> +col_date_nokey date NOT NULL,
> +col_time_key time NOT NULL,
> +col_time_nokey time NOT NULL,
> +col_datetime_key datetime NOT NULL,
> +col_datetime_nokey datetime NOT NULL,
> +col_varchar_key varchar(1) NOT NULL,
> +col_varchar_nokey varchar(1) NOT NULL,
> +PRIMARY KEY (pk),
> +KEY col_int_key (col_int_key),
> +KEY col_date_key(col_date_key),
> +KEY col_time_key(col_time_key),
> +KEY col_datetime_key(col_datetime_key),
> +KEY col_varchar_key(col_varchar_key, col_int_key)
> +);
> +INSERT INTO t1 VALUES
> +(10,1,7,'2007-04-28','2007-04-28','00:00:00','00:00:00','2004-06-06
> 04:22:12','2004-06-06 04:22:12','v','v'),
> +(11,7,0,'2006-04-05','2006-04-05','00:00:00','00:00:00','2005-11-13
> 01:12:31','2005-11-13 01:12:31','s','s'),
> +(12,4,9,'2002-02-02','2002-02-02','06:35:17','06:35:17','2002-05-04
> 01:50:00','2002-05-04 01:50:00','l','l'),
> +(13,7,3,'2002-06-07','2002-06-07','18:07:14','18:07:14','2004-10-27
> 10:28:45','2004-10-27 10:28:45','y','y'),
> +(14,0,4,'0000-00-00','0000-00-00','20:36:52','20:36:52','2006-07-22
> 05:24:23','2006-07-22 05:24:23','c','c'),
> +(15,2,2,'2003-01-13','2003-01-13','21:29:07','21:29:07','2002-05-16
> 21:34:03','2002-05-16 21:34:03','i','i'),
> +(16,9,5,'2006-07-07','2006-07-07','23:45:57','23:45:57','2008-04-17
> 10:45:30','2008-04-17 10:45:30','h','h'),
> +(17,4,3,'0000-00-00','0000-00-00','22:54:57','22:54:57','2009-04-21
> 02:58:02','2009-04-21 02:58:02','q','q'),
> +(18,0,1,'2002-06-15','2002-06-15','18:45:09','18:45:09','2008-01-11
> 11:01:51','2008-01-11 11:01:51','a','a'),
> +(19,9,3,'2006-07-09','2006-07-09','14:30:46','14:30:46','1900-01-01
> 00:00:00','1900-01-01 00:00:00','v','v'),
> +(20,1,6,'2001-04-17','2001-04-17','19:23:43','19:23:43','2007-05-17
> 18:24:57','2007-05-17 18:24:57','u','u'),
> +(21,3,7,'2005-12-22','2005-12-22','03:39:30','03:39:30','2007-08-07
> 00:00:00','2007-08-07 00:00:00','s','s'),
> +(22,8,5,'2009-05-03','2009-05-03','23:37:52','23:37:52','2001-08-28
> 00:00:00','2001-08-28 00:00:00','y','y'),
> +(23,8,1,'2003-05-28','2003-05-28','16:59:30','16:59:30','2004-04-16
> 00:27:28','2004-04-16 00:27:28','z','z'),
> +(24,18,204,'0000-00-00','0000-00-00','22:21:15','22:21:15','2005-05-03
> 07:06:22','2005-05-03 07:06:22','h','h'),
> +(25,84,224,'2001-11-03','2001-11-03','12:24:37','12:24:37','2009-03-11
> 17:09:50','2009-03-11 17:09:50','p','p'),
> +(26,6,9,'1900-01-01','1900-01-01','15:02:08','15:02:08','2007-12-08
> 01:54:28','2007-12-08 01:54:28','e','e'),
> +(27,3,5,'2000-02-17','2000-02-17','00:00:00','00:00:00','2009-07-28
> 18:19:54','2009-07-28 18:19:54','i','i'),
> +(28,6,0,'2001-01-23','2001-01-23','08:23:30','08:23:30','2008-06-08
> 00:00:00','2008-06-08 00:00:00','y','y'),
> +(29,6,3,'0000-00-00','0000-00-00','08:32:22','08:32:22','2005-02-09
> 09:20:26','2005-02-09 09:20:26','w','w');
> +CREATE TABLE t2 (
> +pk int NOT NULL,
> +col_date_nokey date NOT NULL,
> +PRIMARY KEY (pk)
> +);
> +INSERT INTO t2 VALUES
> +(1, '0000-00-00'),
> +(2, '2004-09-18'),
> +(3, '2009-12-01'),
> +(4, '2004-12-17'),
> +(5, '2000-03-14'),
> +(6, '2000-10-08'),
> +(7, '2006-05-25'),
> +(8, '2008-01-23'),
> +(9, '2007-06-18'),
> +(10, '2002-10-13'),
> +(11, '1900-01-01'),
> +(12, '0000-00-00'),
> +(13, '2006-03-09'),
> +(14, '2001-06-05'),
> +(15, '2006-05-28'),
> +(16, '2001-04-19'),
> +(17, '1900-01-01'),
> +(18, '2004-08-20'),
> +(19, '2004-10-10'),
> +(20, '2000-04-02');
> +CREATE TABLE t3 (
> +pk int NOT NULL,
> +col_int_key int NOT NULL,
> +col_varchar_key varchar(1) NOT NULL,
> +PRIMARY KEY (pk),
> +KEY col_int_key(col_int_key),
> +KEY col_varchar_key(col_varchar_key, col_int_key)
> +);
> +INSERT INTO t3 VALUES
> +(10, 7, 'b');
> +SELECT grandparent1.col_time_key AS g1
> +FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_date_nokey)
> +WHERE (grandparent1.col_varchar_key, grandparent1.col_varchar_key) IN
> +(SELECT parent1.col_varchar_nokey AS p1, parent1.col_varchar_nokey AS p2
> +FROM t1 AS parent1
> +WHERE parent1.col_varchar_key IN
> +(SELECT DISTINCT child1.col_varchar_nokey AS c1
> +FROM t1 AS child1 LEFT JOIN t3 AS child2
> +ON (child1.col_varchar_key> child2.col_varchar_key))
> +AND
> +(parent1.col_int_nokey<= parent1.pk OR
> +grandparent1.pk> 8))
> +AND
> +grandparent1.col_varchar_nokey<> 'w' AND
> +grandparent1.col_date_key<= '2004-09-11'
> +ORDER BY g1;
> +g1
> +00:00:00
> +06:35:17
> +08:23:30
> +12:24:37
> +15:02:08
> +15:02:08
> +16:59:30
> +18:07:14
> +18:45:09
> +19:23:43
> +20:36:52
> +20:36:52
> +21:29:07
> +22:21:15
> +22:21:15
> +22:54:57
> +22:54:57
> +-- 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, t3;
> +# End of the test for bug#12603183.
> set optimizer_switch=default;
>
> === modified file 'mysql-test/r/subquery_sj_loosescan_jcl6.result'
> --- a/mysql-test/r/subquery_sj_loosescan_jcl6.result 2011-03-17 11:23:06 +0000
> +++ b/mysql-test/r/subquery_sj_loosescan_jcl6.result 2011-06-01 10:27:29 +0000
> @@ -5433,5 +5433,122 @@ a
> 0
> DROP TABLE t1;
> DROP VIEW v1;
> +#
> +# Bug#12603183: Segfault in hp_movelink
> +#
> +CREATE TABLE t1 (
> +pk int NOT NULL,
> +col_int_nokey int NOT NULL,
> +col_int_key int NOT NULL,
> +col_date_key date NOT NULL,
> +col_date_nokey date NOT NULL,
> +col_time_key time NOT NULL,
> +col_time_nokey time NOT NULL,
> +col_datetime_key datetime NOT NULL,
> +col_datetime_nokey datetime NOT NULL,
> +col_varchar_key varchar(1) NOT NULL,
> +col_varchar_nokey varchar(1) NOT NULL,
> +PRIMARY KEY (pk),
> +KEY col_int_key (col_int_key),
> +KEY col_date_key(col_date_key),
> +KEY col_time_key(col_time_key),
> +KEY col_datetime_key(col_datetime_key),
> +KEY col_varchar_key(col_varchar_key, col_int_key)
> +);
> +INSERT INTO t1 VALUES
> +(10,1,7,'2007-04-28','2007-04-28','00:00:00','00:00:00','2004-06-06
> 04:22:12','2004-06-06 04:22:12','v','v'),
> +(11,7,0,'2006-04-05','2006-04-05','00:00:00','00:00:00','2005-11-13
> 01:12:31','2005-11-13 01:12:31','s','s'),
> +(12,4,9,'2002-02-02','2002-02-02','06:35:17','06:35:17','2002-05-04
> 01:50:00','2002-05-04 01:50:00','l','l'),
> +(13,7,3,'2002-06-07','2002-06-07','18:07:14','18:07:14','2004-10-27
> 10:28:45','2004-10-27 10:28:45','y','y'),
> +(14,0,4,'0000-00-00','0000-00-00','20:36:52','20:36:52','2006-07-22
> 05:24:23','2006-07-22 05:24:23','c','c'),
> +(15,2,2,'2003-01-13','2003-01-13','21:29:07','21:29:07','2002-05-16
> 21:34:03','2002-05-16 21:34:03','i','i'),
> +(16,9,5,'2006-07-07','2006-07-07','23:45:57','23:45:57','2008-04-17
> 10:45:30','2008-04-17 10:45:30','h','h'),
> +(17,4,3,'0000-00-00','0000-00-00','22:54:57','22:54:57','2009-04-21
> 02:58:02','2009-04-21 02:58:02','q','q'),
> +(18,0,1,'2002-06-15','2002-06-15','18:45:09','18:45:09','2008-01-11
> 11:01:51','2008-01-11 11:01:51','a','a'),
> +(19,9,3,'2006-07-09','2006-07-09','14:30:46','14:30:46','1900-01-01
> 00:00:00','1900-01-01 00:00:00','v','v'),
> +(20,1,6,'2001-04-17','2001-04-17','19:23:43','19:23:43','2007-05-17
> 18:24:57','2007-05-17 18:24:57','u','u'),
> +(21,3,7,'2005-12-22','2005-12-22','03:39:30','03:39:30','2007-08-07
> 00:00:00','2007-08-07 00:00:00','s','s'),
> +(22,8,5,'2009-05-03','2009-05-03','23:37:52','23:37:52','2001-08-28
> 00:00:00','2001-08-28 00:00:00','y','y'),
> +(23,8,1,'2003-05-28','2003-05-28','16:59:30','16:59:30','2004-04-16
> 00:27:28','2004-04-16 00:27:28','z','z'),
> +(24,18,204,'0000-00-00','0000-00-00','22:21:15','22:21:15','2005-05-03
> 07:06:22','2005-05-03 07:06:22','h','h'),
> +(25,84,224,'2001-11-03','2001-11-03','12:24:37','12:24:37','2009-03-11
> 17:09:50','2009-03-11 17:09:50','p','p'),
> +(26,6,9,'1900-01-01','1900-01-01','15:02:08','15:02:08','2007-12-08
> 01:54:28','2007-12-08 01:54:28','e','e'),
> +(27,3,5,'2000-02-17','2000-02-17','00:00:00','00:00:00','2009-07-28
> 18:19:54','2009-07-28 18:19:54','i','i'),
> +(28,6,0,'2001-01-23','2001-01-23','08:23:30','08:23:30','2008-06-08
> 00:00:00','2008-06-08 00:00:00','y','y'),
> +(29,6,3,'0000-00-00','0000-00-00','08:32:22','08:32:22','2005-02-09
> 09:20:26','2005-02-09 09:20:26','w','w');
> +CREATE TABLE t2 (
> +pk int NOT NULL,
> +col_date_nokey date NOT NULL,
> +PRIMARY KEY (pk)
> +);
> +INSERT INTO t2 VALUES
> +(1, '0000-00-00'),
> +(2, '2004-09-18'),
> +(3, '2009-12-01'),
> +(4, '2004-12-17'),
> +(5, '2000-03-14'),
> +(6, '2000-10-08'),
> +(7, '2006-05-25'),
> +(8, '2008-01-23'),
> +(9, '2007-06-18'),
> +(10, '2002-10-13'),
> +(11, '1900-01-01'),
> +(12, '0000-00-00'),
> +(13, '2006-03-09'),
> +(14, '2001-06-05'),
> +(15, '2006-05-28'),
> +(16, '2001-04-19'),
> +(17, '1900-01-01'),
> +(18, '2004-08-20'),
> +(19, '2004-10-10'),
> +(20, '2000-04-02');
> +CREATE TABLE t3 (
> +pk int NOT NULL,
> +col_int_key int NOT NULL,
> +col_varchar_key varchar(1) NOT NULL,
> +PRIMARY KEY (pk),
> +KEY col_int_key(col_int_key),
> +KEY col_varchar_key(col_varchar_key, col_int_key)
> +);
> +INSERT INTO t3 VALUES
> +(10, 7, 'b');
> +SELECT grandparent1.col_time_key AS g1
> +FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_date_nokey)
> +WHERE (grandparent1.col_varchar_key, grandparent1.col_varchar_key) IN
> +(SELECT parent1.col_varchar_nokey AS p1, parent1.col_varchar_nokey AS p2
> +FROM t1 AS parent1
> +WHERE parent1.col_varchar_key IN
> +(SELECT DISTINCT child1.col_varchar_nokey AS c1
> +FROM t1 AS child1 LEFT JOIN t3 AS child2
> +ON (child1.col_varchar_key> child2.col_varchar_key))
> +AND
> +(parent1.col_int_nokey<= parent1.pk OR
> +grandparent1.pk> 8))
> +AND
> +grandparent1.col_varchar_nokey<> 'w' AND
> +grandparent1.col_date_key<= '2004-09-11'
> +ORDER BY g1;
> +g1
> +00:00:00
> +06:35:17
> +08:23:30
> +12:24:37
> +15:02:08
> +15:02:08
> +16:59:30
> +18:07:14
> +18:45:09
> +19:23:43
> +20:36:52
> +20:36:52
> +21:29:07
> +22:21:15
> +22:21:15
> +22:54:57
> +22:54:57
> +-- 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, t3;
> +# End of the test for bug#12603183.
> set optimizer_switch=default;
> set optimizer_join_cache_level=default;
>
> === modified file 'mysql-test/r/subquery_sj_loosescan_jcl7.result'
> --- a/mysql-test/r/subquery_sj_loosescan_jcl7.result 2011-03-17 11:23:06 +0000
> +++ b/mysql-test/r/subquery_sj_loosescan_jcl7.result 2011-06-01 10:27:29 +0000
> @@ -5433,5 +5433,122 @@ a
> 0
> DROP TABLE t1;
> DROP VIEW v1;
> +#
> +# Bug#12603183: Segfault in hp_movelink
> +#
> +CREATE TABLE t1 (
> +pk int NOT NULL,
> +col_int_nokey int NOT NULL,
> +col_int_key int NOT NULL,
> +col_date_key date NOT NULL,
> +col_date_nokey date NOT NULL,
> +col_time_key time NOT NULL,
> +col_time_nokey time NOT NULL,
> +col_datetime_key datetime NOT NULL,
> +col_datetime_nokey datetime NOT NULL,
> +col_varchar_key varchar(1) NOT NULL,
> +col_varchar_nokey varchar(1) NOT NULL,
> +PRIMARY KEY (pk),
> +KEY col_int_key (col_int_key),
> +KEY col_date_key(col_date_key),
> +KEY col_time_key(col_time_key),
> +KEY col_datetime_key(col_datetime_key),
> +KEY col_varchar_key(col_varchar_key, col_int_key)
> +);
> +INSERT INTO t1 VALUES
> +(10,1,7,'2007-04-28','2007-04-28','00:00:00','00:00:00','2004-06-06
> 04:22:12','2004-06-06 04:22:12','v','v'),
> +(11,7,0,'2006-04-05','2006-04-05','00:00:00','00:00:00','2005-11-13
> 01:12:31','2005-11-13 01:12:31','s','s'),
> +(12,4,9,'2002-02-02','2002-02-02','06:35:17','06:35:17','2002-05-04
> 01:50:00','2002-05-04 01:50:00','l','l'),
> +(13,7,3,'2002-06-07','2002-06-07','18:07:14','18:07:14','2004-10-27
> 10:28:45','2004-10-27 10:28:45','y','y'),
> +(14,0,4,'0000-00-00','0000-00-00','20:36:52','20:36:52','2006-07-22
> 05:24:23','2006-07-22 05:24:23','c','c'),
> +(15,2,2,'2003-01-13','2003-01-13','21:29:07','21:29:07','2002-05-16
> 21:34:03','2002-05-16 21:34:03','i','i'),
> +(16,9,5,'2006-07-07','2006-07-07','23:45:57','23:45:57','2008-04-17
> 10:45:30','2008-04-17 10:45:30','h','h'),
> +(17,4,3,'0000-00-00','0000-00-00','22:54:57','22:54:57','2009-04-21
> 02:58:02','2009-04-21 02:58:02','q','q'),
> +(18,0,1,'2002-06-15','2002-06-15','18:45:09','18:45:09','2008-01-11
> 11:01:51','2008-01-11 11:01:51','a','a'),
> +(19,9,3,'2006-07-09','2006-07-09','14:30:46','14:30:46','1900-01-01
> 00:00:00','1900-01-01 00:00:00','v','v'),
> +(20,1,6,'2001-04-17','2001-04-17','19:23:43','19:23:43','2007-05-17
> 18:24:57','2007-05-17 18:24:57','u','u'),
> +(21,3,7,'2005-12-22','2005-12-22','03:39:30','03:39:30','2007-08-07
> 00:00:00','2007-08-07 00:00:00','s','s'),
> +(22,8,5,'2009-05-03','2009-05-03','23:37:52','23:37:52','2001-08-28
> 00:00:00','2001-08-28 00:00:00','y','y'),
> +(23,8,1,'2003-05-28','2003-05-28','16:59:30','16:59:30','2004-04-16
> 00:27:28','2004-04-16 00:27:28','z','z'),
> +(24,18,204,'0000-00-00','0000-00-00','22:21:15','22:21:15','2005-05-03
> 07:06:22','2005-05-03 07:06:22','h','h'),
> +(25,84,224,'2001-11-03','2001-11-03','12:24:37','12:24:37','2009-03-11
> 17:09:50','2009-03-11 17:09:50','p','p'),
> +(26,6,9,'1900-01-01','1900-01-01','15:02:08','15:02:08','2007-12-08
> 01:54:28','2007-12-08 01:54:28','e','e'),
> +(27,3,5,'2000-02-17','2000-02-17','00:00:00','00:00:00','2009-07-28
> 18:19:54','2009-07-28 18:19:54','i','i'),
> +(28,6,0,'2001-01-23','2001-01-23','08:23:30','08:23:30','2008-06-08
> 00:00:00','2008-06-08 00:00:00','y','y'),
> +(29,6,3,'0000-00-00','0000-00-00','08:32:22','08:32:22','2005-02-09
> 09:20:26','2005-02-09 09:20:26','w','w');
> +CREATE TABLE t2 (
> +pk int NOT NULL,
> +col_date_nokey date NOT NULL,
> +PRIMARY KEY (pk)
> +);
> +INSERT INTO t2 VALUES
> +(1, '0000-00-00'),
> +(2, '2004-09-18'),
> +(3, '2009-12-01'),
> +(4, '2004-12-17'),
> +(5, '2000-03-14'),
> +(6, '2000-10-08'),
> +(7, '2006-05-25'),
> +(8, '2008-01-23'),
> +(9, '2007-06-18'),
> +(10, '2002-10-13'),
> +(11, '1900-01-01'),
> +(12, '0000-00-00'),
> +(13, '2006-03-09'),
> +(14, '2001-06-05'),
> +(15, '2006-05-28'),
> +(16, '2001-04-19'),
> +(17, '1900-01-01'),
> +(18, '2004-08-20'),
> +(19, '2004-10-10'),
> +(20, '2000-04-02');
> +CREATE TABLE t3 (
> +pk int NOT NULL,
> +col_int_key int NOT NULL,
> +col_varchar_key varchar(1) NOT NULL,
> +PRIMARY KEY (pk),
> +KEY col_int_key(col_int_key),
> +KEY col_varchar_key(col_varchar_key, col_int_key)
> +);
> +INSERT INTO t3 VALUES
> +(10, 7, 'b');
> +SELECT grandparent1.col_time_key AS g1
> +FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_date_nokey)
> +WHERE (grandparent1.col_varchar_key, grandparent1.col_varchar_key) IN
> +(SELECT parent1.col_varchar_nokey AS p1, parent1.col_varchar_nokey AS p2
> +FROM t1 AS parent1
> +WHERE parent1.col_varchar_key IN
> +(SELECT DISTINCT child1.col_varchar_nokey AS c1
> +FROM t1 AS child1 LEFT JOIN t3 AS child2
> +ON (child1.col_varchar_key> child2.col_varchar_key))
> +AND
> +(parent1.col_int_nokey<= parent1.pk OR
> +grandparent1.pk> 8))
> +AND
> +grandparent1.col_varchar_nokey<> 'w' AND
> +grandparent1.col_date_key<= '2004-09-11'
> +ORDER BY g1;
> +g1
> +00:00:00
> +06:35:17
> +08:23:30
> +12:24:37
> +15:02:08
> +15:02:08
> +16:59:30
> +18:07:14
> +18:45:09
> +19:23:43
> +20:36:52
> +20:36:52
> +21:29:07
> +22:21:15
> +22:21:15
> +22:54:57
> +22:54:57
> +-- 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, t3;
> +# End of the test for bug#12603183.
> set optimizer_switch=default;
> set optimizer_join_cache_level=default;
>
> === modified file 'mysql-test/r/subquery_sj_mat.result'
> --- a/mysql-test/r/subquery_sj_mat.result 2011-03-17 11:23:06 +0000
> +++ b/mysql-test/r/subquery_sj_mat.result 2011-06-01 10:27:29 +0000
> @@ -5429,4 +5429,120 @@ a
> 0
> DROP TABLE t1;
> DROP VIEW v1;
> +#
> +# Bug#12603183: Segfault in hp_movelink
> +#
> +CREATE TABLE t1 (
> +pk int NOT NULL,
> +col_int_nokey int NOT NULL,
> +col_int_key int NOT NULL,
> +col_date_key date NOT NULL,
> +col_date_nokey date NOT NULL,
> +col_time_key time NOT NULL,
> +col_time_nokey time NOT NULL,
> +col_datetime_key datetime NOT NULL,
> +col_datetime_nokey datetime NOT NULL,
> +col_varchar_key varchar(1) NOT NULL,
> +col_varchar_nokey varchar(1) NOT NULL,
> +PRIMARY KEY (pk),
> +KEY col_int_key (col_int_key),
> +KEY col_date_key(col_date_key),
> +KEY col_time_key(col_time_key),
> +KEY col_datetime_key(col_datetime_key),
> +KEY col_varchar_key(col_varchar_key, col_int_key)
> +);
> +INSERT INTO t1 VALUES
> +(10,1,7,'2007-04-28','2007-04-28','00:00:00','00:00:00','2004-06-06
> 04:22:12','2004-06-06 04:22:12','v','v'),
> +(11,7,0,'2006-04-05','2006-04-05','00:00:00','00:00:00','2005-11-13
> 01:12:31','2005-11-13 01:12:31','s','s'),
> +(12,4,9,'2002-02-02','2002-02-02','06:35:17','06:35:17','2002-05-04
> 01:50:00','2002-05-04 01:50:00','l','l'),
> +(13,7,3,'2002-06-07','2002-06-07','18:07:14','18:07:14','2004-10-27
> 10:28:45','2004-10-27 10:28:45','y','y'),
> +(14,0,4,'0000-00-00','0000-00-00','20:36:52','20:36:52','2006-07-22
> 05:24:23','2006-07-22 05:24:23','c','c'),
> +(15,2,2,'2003-01-13','2003-01-13','21:29:07','21:29:07','2002-05-16
> 21:34:03','2002-05-16 21:34:03','i','i'),
> +(16,9,5,'2006-07-07','2006-07-07','23:45:57','23:45:57','2008-04-17
> 10:45:30','2008-04-17 10:45:30','h','h'),
> +(17,4,3,'0000-00-00','0000-00-00','22:54:57','22:54:57','2009-04-21
> 02:58:02','2009-04-21 02:58:02','q','q'),
> +(18,0,1,'2002-06-15','2002-06-15','18:45:09','18:45:09','2008-01-11
> 11:01:51','2008-01-11 11:01:51','a','a'),
> +(19,9,3,'2006-07-09','2006-07-09','14:30:46','14:30:46','1900-01-01
> 00:00:00','1900-01-01 00:00:00','v','v'),
> +(20,1,6,'2001-04-17','2001-04-17','19:23:43','19:23:43','2007-05-17
> 18:24:57','2007-05-17 18:24:57','u','u'),
> +(21,3,7,'2005-12-22','2005-12-22','03:39:30','03:39:30','2007-08-07
> 00:00:00','2007-08-07 00:00:00','s','s'),
> +(22,8,5,'2009-05-03','2009-05-03','23:37:52','23:37:52','2001-08-28
> 00:00:00','2001-08-28 00:00:00','y','y'),
> +(23,8,1,'2003-05-28','2003-05-28','16:59:30','16:59:30','2004-04-16
> 00:27:28','2004-04-16 00:27:28','z','z'),
> +(24,18,204,'0000-00-00','0000-00-00','22:21:15','22:21:15','2005-05-03
> 07:06:22','2005-05-03 07:06:22','h','h'),
> +(25,84,224,'2001-11-03','2001-11-03','12:24:37','12:24:37','2009-03-11
> 17:09:50','2009-03-11 17:09:50','p','p'),
> +(26,6,9,'1900-01-01','1900-01-01','15:02:08','15:02:08','2007-12-08
> 01:54:28','2007-12-08 01:54:28','e','e'),
> +(27,3,5,'2000-02-17','2000-02-17','00:00:00','00:00:00','2009-07-28
> 18:19:54','2009-07-28 18:19:54','i','i'),
> +(28,6,0,'2001-01-23','2001-01-23','08:23:30','08:23:30','2008-06-08
> 00:00:00','2008-06-08 00:00:00','y','y'),
> +(29,6,3,'0000-00-00','0000-00-00','08:32:22','08:32:22','2005-02-09
> 09:20:26','2005-02-09 09:20:26','w','w');
> +CREATE TABLE t2 (
> +pk int NOT NULL,
> +col_date_nokey date NOT NULL,
> +PRIMARY KEY (pk)
> +);
> +INSERT INTO t2 VALUES
> +(1, '0000-00-00'),
> +(2, '2004-09-18'),
> +(3, '2009-12-01'),
> +(4, '2004-12-17'),
> +(5, '2000-03-14'),
> +(6, '2000-10-08'),
> +(7, '2006-05-25'),
> +(8, '2008-01-23'),
> +(9, '2007-06-18'),
> +(10, '2002-10-13'),
> +(11, '1900-01-01'),
> +(12, '0000-00-00'),
> +(13, '2006-03-09'),
> +(14, '2001-06-05'),
> +(15, '2006-05-28'),
> +(16, '2001-04-19'),
> +(17, '1900-01-01'),
> +(18, '2004-08-20'),
> +(19, '2004-10-10'),
> +(20, '2000-04-02');
> +CREATE TABLE t3 (
> +pk int NOT NULL,
> +col_int_key int NOT NULL,
> +col_varchar_key varchar(1) NOT NULL,
> +PRIMARY KEY (pk),
> +KEY col_int_key(col_int_key),
> +KEY col_varchar_key(col_varchar_key, col_int_key)
> +);
> +INSERT INTO t3 VALUES
> +(10, 7, 'b');
> +SELECT grandparent1.col_time_key AS g1
> +FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_date_nokey)
> +WHERE (grandparent1.col_varchar_key, grandparent1.col_varchar_key) IN
> +(SELECT parent1.col_varchar_nokey AS p1, parent1.col_varchar_nokey AS p2
> +FROM t1 AS parent1
> +WHERE parent1.col_varchar_key IN
> +(SELECT DISTINCT child1.col_varchar_nokey AS c1
> +FROM t1 AS child1 LEFT JOIN t3 AS child2
> +ON (child1.col_varchar_key> child2.col_varchar_key))
> +AND
> +(parent1.col_int_nokey<= parent1.pk OR
> +grandparent1.pk> 8))
> +AND
> +grandparent1.col_varchar_nokey<> 'w' AND
> +grandparent1.col_date_key<= '2004-09-11'
> +ORDER BY g1;
> +g1
> +00:00:00
> +06:35:17
> +08:23:30
> +12:24:37
> +15:02:08
> +15:02:08
> +16:59:30
> +18:07:14
> +19:23:43
> +20:36:52
> +20:36:52
> +21:29:07
> +22:21:15
> +22:21:15
> +22:54:57
> +22:54:57
> +-- 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, t3;
> +# End of the test for bug#12603183.
> set optimizer_switch=default;
>
> === modified file 'mysql-test/r/subquery_sj_mat_jcl6.result'
> --- a/mysql-test/r/subquery_sj_mat_jcl6.result 2011-03-17 11:23:06 +0000
> +++ b/mysql-test/r/subquery_sj_mat_jcl6.result 2011-06-01 10:27:29 +0000
> @@ -5433,5 +5433,121 @@ a
> 0
> DROP TABLE t1;
> DROP VIEW v1;
> +#
> +# Bug#12603183: Segfault in hp_movelink
> +#
> +CREATE TABLE t1 (
> +pk int NOT NULL,
> +col_int_nokey int NOT NULL,
> +col_int_key int NOT NULL,
> +col_date_key date NOT NULL,
> +col_date_nokey date NOT NULL,
> +col_time_key time NOT NULL,
> +col_time_nokey time NOT NULL,
> +col_datetime_key datetime NOT NULL,
> +col_datetime_nokey datetime NOT NULL,
> +col_varchar_key varchar(1) NOT NULL,
> +col_varchar_nokey varchar(1) NOT NULL,
> +PRIMARY KEY (pk),
> +KEY col_int_key (col_int_key),
> +KEY col_date_key(col_date_key),
> +KEY col_time_key(col_time_key),
> +KEY col_datetime_key(col_datetime_key),
> +KEY col_varchar_key(col_varchar_key, col_int_key)
> +);
> +INSERT INTO t1 VALUES
> +(10,1,7,'2007-04-28','2007-04-28','00:00:00','00:00:00','2004-06-06
> 04:22:12','2004-06-06 04:22:12','v','v'),
> +(11,7,0,'2006-04-05','2006-04-05','00:00:00','00:00:00','2005-11-13
> 01:12:31','2005-11-13 01:12:31','s','s'),
> +(12,4,9,'2002-02-02','2002-02-02','06:35:17','06:35:17','2002-05-04
> 01:50:00','2002-05-04 01:50:00','l','l'),
> +(13,7,3,'2002-06-07','2002-06-07','18:07:14','18:07:14','2004-10-27
> 10:28:45','2004-10-27 10:28:45','y','y'),
> +(14,0,4,'0000-00-00','0000-00-00','20:36:52','20:36:52','2006-07-22
> 05:24:23','2006-07-22 05:24:23','c','c'),
> +(15,2,2,'2003-01-13','2003-01-13','21:29:07','21:29:07','2002-05-16
> 21:34:03','2002-05-16 21:34:03','i','i'),
> +(16,9,5,'2006-07-07','2006-07-07','23:45:57','23:45:57','2008-04-17
> 10:45:30','2008-04-17 10:45:30','h','h'),
> +(17,4,3,'0000-00-00','0000-00-00','22:54:57','22:54:57','2009-04-21
> 02:58:02','2009-04-21 02:58:02','q','q'),
> +(18,0,1,'2002-06-15','2002-06-15','18:45:09','18:45:09','2008-01-11
> 11:01:51','2008-01-11 11:01:51','a','a'),
> +(19,9,3,'2006-07-09','2006-07-09','14:30:46','14:30:46','1900-01-01
> 00:00:00','1900-01-01 00:00:00','v','v'),
> +(20,1,6,'2001-04-17','2001-04-17','19:23:43','19:23:43','2007-05-17
> 18:24:57','2007-05-17 18:24:57','u','u'),
> +(21,3,7,'2005-12-22','2005-12-22','03:39:30','03:39:30','2007-08-07
> 00:00:00','2007-08-07 00:00:00','s','s'),
> +(22,8,5,'2009-05-03','2009-05-03','23:37:52','23:37:52','2001-08-28
> 00:00:00','2001-08-28 00:00:00','y','y'),
> +(23,8,1,'2003-05-28','2003-05-28','16:59:30','16:59:30','2004-04-16
> 00:27:28','2004-04-16 00:27:28','z','z'),
> +(24,18,204,'0000-00-00','0000-00-00','22:21:15','22:21:15','2005-05-03
> 07:06:22','2005-05-03 07:06:22','h','h'),
> +(25,84,224,'2001-11-03','2001-11-03','12:24:37','12:24:37','2009-03-11
> 17:09:50','2009-03-11 17:09:50','p','p'),
> +(26,6,9,'1900-01-01','1900-01-01','15:02:08','15:02:08','2007-12-08
> 01:54:28','2007-12-08 01:54:28','e','e'),
> +(27,3,5,'2000-02-17','2000-02-17','00:00:00','00:00:00','2009-07-28
> 18:19:54','2009-07-28 18:19:54','i','i'),
> +(28,6,0,'2001-01-23','2001-01-23','08:23:30','08:23:30','2008-06-08
> 00:00:00','2008-06-08 00:00:00','y','y'),
> +(29,6,3,'0000-00-00','0000-00-00','08:32:22','08:32:22','2005-02-09
> 09:20:26','2005-02-09 09:20:26','w','w');
> +CREATE TABLE t2 (
> +pk int NOT NULL,
> +col_date_nokey date NOT NULL,
> +PRIMARY KEY (pk)
> +);
> +INSERT INTO t2 VALUES
> +(1, '0000-00-00'),
> +(2, '2004-09-18'),
> +(3, '2009-12-01'),
> +(4, '2004-12-17'),
> +(5, '2000-03-14'),
> +(6, '2000-10-08'),
> +(7, '2006-05-25'),
> +(8, '2008-01-23'),
> +(9, '2007-06-18'),
> +(10, '2002-10-13'),
> +(11, '1900-01-01'),
> +(12, '0000-00-00'),
> +(13, '2006-03-09'),
> +(14, '2001-06-05'),
> +(15, '2006-05-28'),
> +(16, '2001-04-19'),
> +(17, '1900-01-01'),
> +(18, '2004-08-20'),
> +(19, '2004-10-10'),
> +(20, '2000-04-02');
> +CREATE TABLE t3 (
> +pk int NOT NULL,
> +col_int_key int NOT NULL,
> +col_varchar_key varchar(1) NOT NULL,
> +PRIMARY KEY (pk),
> +KEY col_int_key(col_int_key),
> +KEY col_varchar_key(col_varchar_key, col_int_key)
> +);
> +INSERT INTO t3 VALUES
> +(10, 7, 'b');
> +SELECT grandparent1.col_time_key AS g1
> +FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_date_nokey)
> +WHERE (grandparent1.col_varchar_key, grandparent1.col_varchar_key) IN
> +(SELECT parent1.col_varchar_nokey AS p1, parent1.col_varchar_nokey AS p2
> +FROM t1 AS parent1
> +WHERE parent1.col_varchar_key IN
> +(SELECT DISTINCT child1.col_varchar_nokey AS c1
> +FROM t1 AS child1 LEFT JOIN t3 AS child2
> +ON (child1.col_varchar_key> child2.col_varchar_key))
> +AND
> +(parent1.col_int_nokey<= parent1.pk OR
> +grandparent1.pk> 8))
> +AND
> +grandparent1.col_varchar_nokey<> 'w' AND
> +grandparent1.col_date_key<= '2004-09-11'
> +ORDER BY g1;
> +g1
> +00:00:00
> +06:35:17
> +08:23:30
> +12:24:37
> +15:02:08
> +15:02:08
> +16:59:30
> +18:07:14
> +19:23:43
> +20:36:52
> +20:36:52
> +21:29:07
> +22:21:15
> +22:21:15
> +22:54:57
> +22:54:57
> +-- 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, t3;
> +# End of the test for bug#12603183.
> set optimizer_switch=default;
> set optimizer_join_cache_level=default;
>
> === modified file 'mysql-test/r/subquery_sj_mat_jcl7.result'
> --- a/mysql-test/r/subquery_sj_mat_jcl7.result 2011-03-17 11:23:06 +0000
> +++ b/mysql-test/r/subquery_sj_mat_jcl7.result 2011-06-01 10:27:29 +0000
> @@ -5433,5 +5433,121 @@ a
> 0
> DROP TABLE t1;
> DROP VIEW v1;
> +#
> +# Bug#12603183: Segfault in hp_movelink
> +#
> +CREATE TABLE t1 (
> +pk int NOT NULL,
> +col_int_nokey int NOT NULL,
> +col_int_key int NOT NULL,
> +col_date_key date NOT NULL,
> +col_date_nokey date NOT NULL,
> +col_time_key time NOT NULL,
> +col_time_nokey time NOT NULL,
> +col_datetime_key datetime NOT NULL,
> +col_datetime_nokey datetime NOT NULL,
> +col_varchar_key varchar(1) NOT NULL,
> +col_varchar_nokey varchar(1) NOT NULL,
> +PRIMARY KEY (pk),
> +KEY col_int_key (col_int_key),
> +KEY col_date_key(col_date_key),
> +KEY col_time_key(col_time_key),
> +KEY col_datetime_key(col_datetime_key),
> +KEY col_varchar_key(col_varchar_key, col_int_key)
> +);
> +INSERT INTO t1 VALUES
> +(10,1,7,'2007-04-28','2007-04-28','00:00:00','00:00:00','2004-06-06
> 04:22:12','2004-06-06 04:22:12','v','v'),
> +(11,7,0,'2006-04-05','2006-04-05','00:00:00','00:00:00','2005-11-13
> 01:12:31','2005-11-13 01:12:31','s','s'),
> +(12,4,9,'2002-02-02','2002-02-02','06:35:17','06:35:17','2002-05-04
> 01:50:00','2002-05-04 01:50:00','l','l'),
> +(13,7,3,'2002-06-07','2002-06-07','18:07:14','18:07:14','2004-10-27
> 10:28:45','2004-10-27 10:28:45','y','y'),
> +(14,0,4,'0000-00-00','0000-00-00','20:36:52','20:36:52','2006-07-22
> 05:24:23','2006-07-22 05:24:23','c','c'),
> +(15,2,2,'2003-01-13','2003-01-13','21:29:07','21:29:07','2002-05-16
> 21:34:03','2002-05-16 21:34:03','i','i'),
> +(16,9,5,'2006-07-07','2006-07-07','23:45:57','23:45:57','2008-04-17
> 10:45:30','2008-04-17 10:45:30','h','h'),
> +(17,4,3,'0000-00-00','0000-00-00','22:54:57','22:54:57','2009-04-21
> 02:58:02','2009-04-21 02:58:02','q','q'),
> +(18,0,1,'2002-06-15','2002-06-15','18:45:09','18:45:09','2008-01-11
> 11:01:51','2008-01-11 11:01:51','a','a'),
> +(19,9,3,'2006-07-09','2006-07-09','14:30:46','14:30:46','1900-01-01
> 00:00:00','1900-01-01 00:00:00','v','v'),
> +(20,1,6,'2001-04-17','2001-04-17','19:23:43','19:23:43','2007-05-17
> 18:24:57','2007-05-17 18:24:57','u','u'),
> +(21,3,7,'2005-12-22','2005-12-22','03:39:30','03:39:30','2007-08-07
> 00:00:00','2007-08-07 00:00:00','s','s'),
> +(22,8,5,'2009-05-03','2009-05-03','23:37:52','23:37:52','2001-08-28
> 00:00:00','2001-08-28 00:00:00','y','y'),
> +(23,8,1,'2003-05-28','2003-05-28','16:59:30','16:59:30','2004-04-16
> 00:27:28','2004-04-16 00:27:28','z','z'),
> +(24,18,204,'0000-00-00','0000-00-00','22:21:15','22:21:15','2005-05-03
> 07:06:22','2005-05-03 07:06:22','h','h'),
> +(25,84,224,'2001-11-03','2001-11-03','12:24:37','12:24:37','2009-03-11
> 17:09:50','2009-03-11 17:09:50','p','p'),
> +(26,6,9,'1900-01-01','1900-01-01','15:02:08','15:02:08','2007-12-08
> 01:54:28','2007-12-08 01:54:28','e','e'),
> +(27,3,5,'2000-02-17','2000-02-17','00:00:00','00:00:00','2009-07-28
> 18:19:54','2009-07-28 18:19:54','i','i'),
> +(28,6,0,'2001-01-23','2001-01-23','08:23:30','08:23:30','2008-06-08
> 00:00:00','2008-06-08 00:00:00','y','y'),
> +(29,6,3,'0000-00-00','0000-00-00','08:32:22','08:32:22','2005-02-09
> 09:20:26','2005-02-09 09:20:26','w','w');
> +CREATE TABLE t2 (
> +pk int NOT NULL,
> +col_date_nokey date NOT NULL,
> +PRIMARY KEY (pk)
> +);
> +INSERT INTO t2 VALUES
> +(1, '0000-00-00'),
> +(2, '2004-09-18'),
> +(3, '2009-12-01'),
> +(4, '2004-12-17'),
> +(5, '2000-03-14'),
> +(6, '2000-10-08'),
> +(7, '2006-05-25'),
> +(8, '2008-01-23'),
> +(9, '2007-06-18'),
> +(10, '2002-10-13'),
> +(11, '1900-01-01'),
> +(12, '0000-00-00'),
> +(13, '2006-03-09'),
> +(14, '2001-06-05'),
> +(15, '2006-05-28'),
> +(16, '2001-04-19'),
> +(17, '1900-01-01'),
> +(18, '2004-08-20'),
> +(19, '2004-10-10'),
> +(20, '2000-04-02');
> +CREATE TABLE t3 (
> +pk int NOT NULL,
> +col_int_key int NOT NULL,
> +col_varchar_key varchar(1) NOT NULL,
> +PRIMARY KEY (pk),
> +KEY col_int_key(col_int_key),
> +KEY col_varchar_key(col_varchar_key, col_int_key)
> +);
> +INSERT INTO t3 VALUES
> +(10, 7, 'b');
> +SELECT grandparent1.col_time_key AS g1
> +FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_date_nokey)
> +WHERE (grandparent1.col_varchar_key, grandparent1.col_varchar_key) IN
> +(SELECT parent1.col_varchar_nokey AS p1, parent1.col_varchar_nokey AS p2
> +FROM t1 AS parent1
> +WHERE parent1.col_varchar_key IN
> +(SELECT DISTINCT child1.col_varchar_nokey AS c1
> +FROM t1 AS child1 LEFT JOIN t3 AS child2
> +ON (child1.col_varchar_key> child2.col_varchar_key))
> +AND
> +(parent1.col_int_nokey<= parent1.pk OR
> +grandparent1.pk> 8))
> +AND
> +grandparent1.col_varchar_nokey<> 'w' AND
> +grandparent1.col_date_key<= '2004-09-11'
> +ORDER BY g1;
> +g1
> +00:00:00
> +06:35:17
> +08:23:30
> +12:24:37
> +15:02:08
> +15:02:08
> +16:59:30
> +18:07:14
> +19:23:43
> +20:36:52
> +20:36:52
> +21:29:07
> +22:21:15
> +22:21:15
> +22:54:57
> +22:54:57
> +-- 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, t3;
> +# End of the test for bug#12603183.
> set optimizer_switch=default;
> set optimizer_join_cache_level=default;
>
> === modified file 'mysql-test/r/subquery_sj_mat_nosj.result'
> --- a/mysql-test/r/subquery_sj_mat_nosj.result 2011-03-17 11:23:06 +0000
> +++ b/mysql-test/r/subquery_sj_mat_nosj.result 2011-06-01 10:27:29 +0000
> @@ -5651,4 +5651,121 @@ a
> 0
> DROP TABLE t1;
> DROP VIEW v1;
> +#
> +# Bug#12603183: Segfault in hp_movelink
> +#
> +CREATE TABLE t1 (
> +pk int NOT NULL,
> +col_int_nokey int NOT NULL,
> +col_int_key int NOT NULL,
> +col_date_key date NOT NULL,
> +col_date_nokey date NOT NULL,
> +col_time_key time NOT NULL,
> +col_time_nokey time NOT NULL,
> +col_datetime_key datetime NOT NULL,
> +col_datetime_nokey datetime NOT NULL,
> +col_varchar_key varchar(1) NOT NULL,
> +col_varchar_nokey varchar(1) NOT NULL,
> +PRIMARY KEY (pk),
> +KEY col_int_key (col_int_key),
> +KEY col_date_key(col_date_key),
> +KEY col_time_key(col_time_key),
> +KEY col_datetime_key(col_datetime_key),
> +KEY col_varchar_key(col_varchar_key, col_int_key)
> +);
> +INSERT INTO t1 VALUES
> +(10,1,7,'2007-04-28','2007-04-28','00:00:00','00:00:00','2004-06-06
> 04:22:12','2004-06-06 04:22:12','v','v'),
> +(11,7,0,'2006-04-05','2006-04-05','00:00:00','00:00:00','2005-11-13
> 01:12:31','2005-11-13 01:12:31','s','s'),
> +(12,4,9,'2002-02-02','2002-02-02','06:35:17','06:35:17','2002-05-04
> 01:50:00','2002-05-04 01:50:00','l','l'),
> +(13,7,3,'2002-06-07','2002-06-07','18:07:14','18:07:14','2004-10-27
> 10:28:45','2004-10-27 10:28:45','y','y'),
> +(14,0,4,'0000-00-00','0000-00-00','20:36:52','20:36:52','2006-07-22
> 05:24:23','2006-07-22 05:24:23','c','c'),
> +(15,2,2,'2003-01-13','2003-01-13','21:29:07','21:29:07','2002-05-16
> 21:34:03','2002-05-16 21:34:03','i','i'),
> +(16,9,5,'2006-07-07','2006-07-07','23:45:57','23:45:57','2008-04-17
> 10:45:30','2008-04-17 10:45:30','h','h'),
> +(17,4,3,'0000-00-00','0000-00-00','22:54:57','22:54:57','2009-04-21
> 02:58:02','2009-04-21 02:58:02','q','q'),
> +(18,0,1,'2002-06-15','2002-06-15','18:45:09','18:45:09','2008-01-11
> 11:01:51','2008-01-11 11:01:51','a','a'),
> +(19,9,3,'2006-07-09','2006-07-09','14:30:46','14:30:46','1900-01-01
> 00:00:00','1900-01-01 00:00:00','v','v'),
> +(20,1,6,'2001-04-17','2001-04-17','19:23:43','19:23:43','2007-05-17
> 18:24:57','2007-05-17 18:24:57','u','u'),
> +(21,3,7,'2005-12-22','2005-12-22','03:39:30','03:39:30','2007-08-07
> 00:00:00','2007-08-07 00:00:00','s','s'),
> +(22,8,5,'2009-05-03','2009-05-03','23:37:52','23:37:52','2001-08-28
> 00:00:00','2001-08-28 00:00:00','y','y'),
> +(23,8,1,'2003-05-28','2003-05-28','16:59:30','16:59:30','2004-04-16
> 00:27:28','2004-04-16 00:27:28','z','z'),
> +(24,18,204,'0000-00-00','0000-00-00','22:21:15','22:21:15','2005-05-03
> 07:06:22','2005-05-03 07:06:22','h','h'),
> +(25,84,224,'2001-11-03','2001-11-03','12:24:37','12:24:37','2009-03-11
> 17:09:50','2009-03-11 17:09:50','p','p'),
> +(26,6,9,'1900-01-01','1900-01-01','15:02:08','15:02:08','2007-12-08
> 01:54:28','2007-12-08 01:54:28','e','e'),
> +(27,3,5,'2000-02-17','2000-02-17','00:00:00','00:00:00','2009-07-28
> 18:19:54','2009-07-28 18:19:54','i','i'),
> +(28,6,0,'2001-01-23','2001-01-23','08:23:30','08:23:30','2008-06-08
> 00:00:00','2008-06-08 00:00:00','y','y'),
> +(29,6,3,'0000-00-00','0000-00-00','08:32:22','08:32:22','2005-02-09
> 09:20:26','2005-02-09 09:20:26','w','w');
> +CREATE TABLE t2 (
> +pk int NOT NULL,
> +col_date_nokey date NOT NULL,
> +PRIMARY KEY (pk)
> +);
> +INSERT INTO t2 VALUES
> +(1, '0000-00-00'),
> +(2, '2004-09-18'),
> +(3, '2009-12-01'),
> +(4, '2004-12-17'),
> +(5, '2000-03-14'),
> +(6, '2000-10-08'),
> +(7, '2006-05-25'),
> +(8, '2008-01-23'),
> +(9, '2007-06-18'),
> +(10, '2002-10-13'),
> +(11, '1900-01-01'),
> +(12, '0000-00-00'),
> +(13, '2006-03-09'),
> +(14, '2001-06-05'),
> +(15, '2006-05-28'),
> +(16, '2001-04-19'),
> +(17, '1900-01-01'),
> +(18, '2004-08-20'),
> +(19, '2004-10-10'),
> +(20, '2000-04-02');
> +CREATE TABLE t3 (
> +pk int NOT NULL,
> +col_int_key int NOT NULL,
> +col_varchar_key varchar(1) NOT NULL,
> +PRIMARY KEY (pk),
> +KEY col_int_key(col_int_key),
> +KEY col_varchar_key(col_varchar_key, col_int_key)
> +);
> +INSERT INTO t3 VALUES
> +(10, 7, 'b');
> +SELECT grandparent1.col_time_key AS g1
> +FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_date_nokey)
> +WHERE (grandparent1.col_varchar_key, grandparent1.col_varchar_key) IN
> +(SELECT parent1.col_varchar_nokey AS p1, parent1.col_varchar_nokey AS p2
> +FROM t1 AS parent1
> +WHERE parent1.col_varchar_key IN
> +(SELECT DISTINCT child1.col_varchar_nokey AS c1
> +FROM t1 AS child1 LEFT JOIN t3 AS child2
> +ON (child1.col_varchar_key> child2.col_varchar_key))
> +AND
> +(parent1.col_int_nokey<= parent1.pk OR
> +grandparent1.pk> 8))
> +AND
> +grandparent1.col_varchar_nokey<> 'w' AND
> +grandparent1.col_date_key<= '2004-09-11'
> +ORDER BY g1;
> +g1
> +00:00:00
> +06:35:17
> +08:23:30
> +12:24:37
> +15:02:08
> +15:02:08
> +16:59:30
> +18:07:14
> +18:45:09
> +19:23:43
> +20:36:52
> +20:36:52
> +21:29:07
> +22:21:15
> +22:21:15
> +22:54:57
> +22:54:57
> +-- 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, t3;
> +# End of the test for bug#12603183.
> set optimizer_switch=default;
>
> === modified file 'mysql-test/r/subquery_sj_none.result'
> --- a/mysql-test/r/subquery_sj_none.result 2011-03-17 08:00:10 +0000
> +++ b/mysql-test/r/subquery_sj_none.result 2011-06-01 10:27:29 +0000
> @@ -5574,4 +5574,121 @@ a
> 0
> DROP TABLE t1;
> DROP VIEW v1;
> +#
> +# Bug#12603183: Segfault in hp_movelink
> +#
> +CREATE TABLE t1 (
> +pk int NOT NULL,
> +col_int_nokey int NOT NULL,
> +col_int_key int NOT NULL,
> +col_date_key date NOT NULL,
> +col_date_nokey date NOT NULL,
> +col_time_key time NOT NULL,
> +col_time_nokey time NOT NULL,
> +col_datetime_key datetime NOT NULL,
> +col_datetime_nokey datetime NOT NULL,
> +col_varchar_key varchar(1) NOT NULL,
> +col_varchar_nokey varchar(1) NOT NULL,
> +PRIMARY KEY (pk),
> +KEY col_int_key (col_int_key),
> +KEY col_date_key(col_date_key),
> +KEY col_time_key(col_time_key),
> +KEY col_datetime_key(col_datetime_key),
> +KEY col_varchar_key(col_varchar_key, col_int_key)
> +);
> +INSERT INTO t1 VALUES
> +(10,1,7,'2007-04-28','2007-04-28','00:00:00','00:00:00','2004-06-06
> 04:22:12','2004-06-06 04:22:12','v','v'),
> +(11,7,0,'2006-04-05','2006-04-05','00:00:00','00:00:00','2005-11-13
> 01:12:31','2005-11-13 01:12:31','s','s'),
> +(12,4,9,'2002-02-02','2002-02-02','06:35:17','06:35:17','2002-05-04
> 01:50:00','2002-05-04 01:50:00','l','l'),
> +(13,7,3,'2002-06-07','2002-06-07','18:07:14','18:07:14','2004-10-27
> 10:28:45','2004-10-27 10:28:45','y','y'),
> +(14,0,4,'0000-00-00','0000-00-00','20:36:52','20:36:52','2006-07-22
> 05:24:23','2006-07-22 05:24:23','c','c'),
> +(15,2,2,'2003-01-13','2003-01-13','21:29:07','21:29:07','2002-05-16
> 21:34:03','2002-05-16 21:34:03','i','i'),
> +(16,9,5,'2006-07-07','2006-07-07','23:45:57','23:45:57','2008-04-17
> 10:45:30','2008-04-17 10:45:30','h','h'),
> +(17,4,3,'0000-00-00','0000-00-00','22:54:57','22:54:57','2009-04-21
> 02:58:02','2009-04-21 02:58:02','q','q'),
> +(18,0,1,'2002-06-15','2002-06-15','18:45:09','18:45:09','2008-01-11
> 11:01:51','2008-01-11 11:01:51','a','a'),
> +(19,9,3,'2006-07-09','2006-07-09','14:30:46','14:30:46','1900-01-01
> 00:00:00','1900-01-01 00:00:00','v','v'),
> +(20,1,6,'2001-04-17','2001-04-17','19:23:43','19:23:43','2007-05-17
> 18:24:57','2007-05-17 18:24:57','u','u'),
> +(21,3,7,'2005-12-22','2005-12-22','03:39:30','03:39:30','2007-08-07
> 00:00:00','2007-08-07 00:00:00','s','s'),
> +(22,8,5,'2009-05-03','2009-05-03','23:37:52','23:37:52','2001-08-28
> 00:00:00','2001-08-28 00:00:00','y','y'),
> +(23,8,1,'2003-05-28','2003-05-28','16:59:30','16:59:30','2004-04-16
> 00:27:28','2004-04-16 00:27:28','z','z'),
> +(24,18,204,'0000-00-00','0000-00-00','22:21:15','22:21:15','2005-05-03
> 07:06:22','2005-05-03 07:06:22','h','h'),
> +(25,84,224,'2001-11-03','2001-11-03','12:24:37','12:24:37','2009-03-11
> 17:09:50','2009-03-11 17:09:50','p','p'),
> +(26,6,9,'1900-01-01','1900-01-01','15:02:08','15:02:08','2007-12-08
> 01:54:28','2007-12-08 01:54:28','e','e'),
> +(27,3,5,'2000-02-17','2000-02-17','00:00:00','00:00:00','2009-07-28
> 18:19:54','2009-07-28 18:19:54','i','i'),
> +(28,6,0,'2001-01-23','2001-01-23','08:23:30','08:23:30','2008-06-08
> 00:00:00','2008-06-08 00:00:00','y','y'),
> +(29,6,3,'0000-00-00','0000-00-00','08:32:22','08:32:22','2005-02-09
> 09:20:26','2005-02-09 09:20:26','w','w');
> +CREATE TABLE t2 (
> +pk int NOT NULL,
> +col_date_nokey date NOT NULL,
> +PRIMARY KEY (pk)
> +);
> +INSERT INTO t2 VALUES
> +(1, '0000-00-00'),
> +(2, '2004-09-18'),
> +(3, '2009-12-01'),
> +(4, '2004-12-17'),
> +(5, '2000-03-14'),
> +(6, '2000-10-08'),
> +(7, '2006-05-25'),
> +(8, '2008-01-23'),
> +(9, '2007-06-18'),
> +(10, '2002-10-13'),
> +(11, '1900-01-01'),
> +(12, '0000-00-00'),
> +(13, '2006-03-09'),
> +(14, '2001-06-05'),
> +(15, '2006-05-28'),
> +(16, '2001-04-19'),
> +(17, '1900-01-01'),
> +(18, '2004-08-20'),
> +(19, '2004-10-10'),
> +(20, '2000-04-02');
> +CREATE TABLE t3 (
> +pk int NOT NULL,
> +col_int_key int NOT NULL,
> +col_varchar_key varchar(1) NOT NULL,
> +PRIMARY KEY (pk),
> +KEY col_int_key(col_int_key),
> +KEY col_varchar_key(col_varchar_key, col_int_key)
> +);
> +INSERT INTO t3 VALUES
> +(10, 7, 'b');
> +SELECT grandparent1.col_time_key AS g1
> +FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_date_nokey)
> +WHERE (grandparent1.col_varchar_key, grandparent1.col_varchar_key) IN
> +(SELECT parent1.col_varchar_nokey AS p1, parent1.col_varchar_nokey AS p2
> +FROM t1 AS parent1
> +WHERE parent1.col_varchar_key IN
> +(SELECT DISTINCT child1.col_varchar_nokey AS c1
> +FROM t1 AS child1 LEFT JOIN t3 AS child2
> +ON (child1.col_varchar_key> child2.col_varchar_key))
> +AND
> +(parent1.col_int_nokey<= parent1.pk OR
> +grandparent1.pk> 8))
> +AND
> +grandparent1.col_varchar_nokey<> 'w' AND
> +grandparent1.col_date_key<= '2004-09-11'
> +ORDER BY g1;
> +g1
> +00:00:00
> +06:35:17
> +08:23:30
> +12:24:37
> +15:02:08
> +15:02:08
> +16:59:30
> +18:07:14
> +18:45:09
> +19:23:43
> +20:36:52
> +20:36:52
> +21:29:07
> +22:21:15
> +22:21:15
> +22:54:57
> +22:54:57
> +-- 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, t3;
> +# End of the test for bug#12603183.
> set optimizer_switch=default;
>
> === modified file 'mysql-test/r/subquery_sj_none_jcl6.result'
> --- a/mysql-test/r/subquery_sj_none_jcl6.result 2011-03-17 11:23:06 +0000
> +++ b/mysql-test/r/subquery_sj_none_jcl6.result 2011-06-01 10:27:29 +0000
> @@ -5578,5 +5578,122 @@ a
> 0
> DROP TABLE t1;
> DROP VIEW v1;
> +#
> +# Bug#12603183: Segfault in hp_movelink
> +#
> +CREATE TABLE t1 (
> +pk int NOT NULL,
> +col_int_nokey int NOT NULL,
> +col_int_key int NOT NULL,
> +col_date_key date NOT NULL,
> +col_date_nokey date NOT NULL,
> +col_time_key time NOT NULL,
> +col_time_nokey time NOT NULL,
> +col_datetime_key datetime NOT NULL,
> +col_datetime_nokey datetime NOT NULL,
> +col_varchar_key varchar(1) NOT NULL,
> +col_varchar_nokey varchar(1) NOT NULL,
> +PRIMARY KEY (pk),
> +KEY col_int_key (col_int_key),
> +KEY col_date_key(col_date_key),
> +KEY col_time_key(col_time_key),
> +KEY col_datetime_key(col_datetime_key),
> +KEY col_varchar_key(col_varchar_key, col_int_key)
> +);
> +INSERT INTO t1 VALUES
> +(10,1,7,'2007-04-28','2007-04-28','00:00:00','00:00:00','2004-06-06
> 04:22:12','2004-06-06 04:22:12','v','v'),
> +(11,7,0,'2006-04-05','2006-04-05','00:00:00','00:00:00','2005-11-13
> 01:12:31','2005-11-13 01:12:31','s','s'),
> +(12,4,9,'2002-02-02','2002-02-02','06:35:17','06:35:17','2002-05-04
> 01:50:00','2002-05-04 01:50:00','l','l'),
> +(13,7,3,'2002-06-07','2002-06-07','18:07:14','18:07:14','2004-10-27
> 10:28:45','2004-10-27 10:28:45','y','y'),
> +(14,0,4,'0000-00-00','0000-00-00','20:36:52','20:36:52','2006-07-22
> 05:24:23','2006-07-22 05:24:23','c','c'),
> +(15,2,2,'2003-01-13','2003-01-13','21:29:07','21:29:07','2002-05-16
> 21:34:03','2002-05-16 21:34:03','i','i'),
> +(16,9,5,'2006-07-07','2006-07-07','23:45:57','23:45:57','2008-04-17
> 10:45:30','2008-04-17 10:45:30','h','h'),
> +(17,4,3,'0000-00-00','0000-00-00','22:54:57','22:54:57','2009-04-21
> 02:58:02','2009-04-21 02:58:02','q','q'),
> +(18,0,1,'2002-06-15','2002-06-15','18:45:09','18:45:09','2008-01-11
> 11:01:51','2008-01-11 11:01:51','a','a'),
> +(19,9,3,'2006-07-09','2006-07-09','14:30:46','14:30:46','1900-01-01
> 00:00:00','1900-01-01 00:00:00','v','v'),
> +(20,1,6,'2001-04-17','2001-04-17','19:23:43','19:23:43','2007-05-17
> 18:24:57','2007-05-17 18:24:57','u','u'),
> +(21,3,7,'2005-12-22','2005-12-22','03:39:30','03:39:30','2007-08-07
> 00:00:00','2007-08-07 00:00:00','s','s'),
> +(22,8,5,'2009-05-03','2009-05-03','23:37:52','23:37:52','2001-08-28
> 00:00:00','2001-08-28 00:00:00','y','y'),
> +(23,8,1,'2003-05-28','2003-05-28','16:59:30','16:59:30','2004-04-16
> 00:27:28','2004-04-16 00:27:28','z','z'),
> +(24,18,204,'0000-00-00','0000-00-00','22:21:15','22:21:15','2005-05-03
> 07:06:22','2005-05-03 07:06:22','h','h'),
> +(25,84,224,'2001-11-03','2001-11-03','12:24:37','12:24:37','2009-03-11
> 17:09:50','2009-03-11 17:09:50','p','p'),
> +(26,6,9,'1900-01-01','1900-01-01','15:02:08','15:02:08','2007-12-08
> 01:54:28','2007-12-08 01:54:28','e','e'),
> +(27,3,5,'2000-02-17','2000-02-17','00:00:00','00:00:00','2009-07-28
> 18:19:54','2009-07-28 18:19:54','i','i'),
> +(28,6,0,'2001-01-23','2001-01-23','08:23:30','08:23:30','2008-06-08
> 00:00:00','2008-06-08 00:00:00','y','y'),
> +(29,6,3,'0000-00-00','0000-00-00','08:32:22','08:32:22','2005-02-09
> 09:20:26','2005-02-09 09:20:26','w','w');
> +CREATE TABLE t2 (
> +pk int NOT NULL,
> +col_date_nokey date NOT NULL,
> +PRIMARY KEY (pk)
> +);
> +INSERT INTO t2 VALUES
> +(1, '0000-00-00'),
> +(2, '2004-09-18'),
> +(3, '2009-12-01'),
> +(4, '2004-12-17'),
> +(5, '2000-03-14'),
> +(6, '2000-10-08'),
> +(7, '2006-05-25'),
> +(8, '2008-01-23'),
> +(9, '2007-06-18'),
> +(10, '2002-10-13'),
> +(11, '1900-01-01'),
> +(12, '0000-00-00'),
> +(13, '2006-03-09'),
> +(14, '2001-06-05'),
> +(15, '2006-05-28'),
> +(16, '2001-04-19'),
> +(17, '1900-01-01'),
> +(18, '2004-08-20'),
> +(19, '2004-10-10'),
> +(20, '2000-04-02');
> +CREATE TABLE t3 (
> +pk int NOT NULL,
> +col_int_key int NOT NULL,
> +col_varchar_key varchar(1) NOT NULL,
> +PRIMARY KEY (pk),
> +KEY col_int_key(col_int_key),
> +KEY col_varchar_key(col_varchar_key, col_int_key)
> +);
> +INSERT INTO t3 VALUES
> +(10, 7, 'b');
> +SELECT grandparent1.col_time_key AS g1
> +FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_date_nokey)
> +WHERE (grandparent1.col_varchar_key, grandparent1.col_varchar_key) IN
> +(SELECT parent1.col_varchar_nokey AS p1, parent1.col_varchar_nokey AS p2
> +FROM t1 AS parent1
> +WHERE parent1.col_varchar_key IN
> +(SELECT DISTINCT child1.col_varchar_nokey AS c1
> +FROM t1 AS child1 LEFT JOIN t3 AS child2
> +ON (child1.col_varchar_key> child2.col_varchar_key))
> +AND
> +(parent1.col_int_nokey<= parent1.pk OR
> +grandparent1.pk> 8))
> +AND
> +grandparent1.col_varchar_nokey<> 'w' AND
> +grandparent1.col_date_key<= '2004-09-11'
> +ORDER BY g1;
> +g1
> +00:00:00
> +06:35:17
> +08:23:30
> +12:24:37
> +15:02:08
> +15:02:08
> +16:59:30
> +18:07:14
> +18:45:09
> +19:23:43
> +20:36:52
> +20:36:52
> +21:29:07
> +22:21:15
> +22:21:15
> +22:54:57
> +22:54:57
> +-- 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, t3;
> +# End of the test for bug#12603183.
> set optimizer_switch=default;
> set optimizer_join_cache_level=default;
>
> === modified file 'mysql-test/r/subquery_sj_none_jcl7.result'
> --- a/mysql-test/r/subquery_sj_none_jcl7.result 2011-03-17 11:23:06 +0000
> +++ b/mysql-test/r/subquery_sj_none_jcl7.result 2011-06-01 10:27:29 +0000
> @@ -5578,5 +5578,122 @@ a
> 0
> DROP TABLE t1;
> DROP VIEW v1;
> +#
> +# Bug#12603183: Segfault in hp_movelink
> +#
> +CREATE TABLE t1 (
> +pk int NOT NULL,
> +col_int_nokey int NOT NULL,
> +col_int_key int NOT NULL,
> +col_date_key date NOT NULL,
> +col_date_nokey date NOT NULL,
> +col_time_key time NOT NULL,
> +col_time_nokey time NOT NULL,
> +col_datetime_key datetime NOT NULL,
> +col_datetime_nokey datetime NOT NULL,
> +col_varchar_key varchar(1) NOT NULL,
> +col_varchar_nokey varchar(1) NOT NULL,
> +PRIMARY KEY (pk),
> +KEY col_int_key (col_int_key),
> +KEY col_date_key(col_date_key),
> +KEY col_time_key(col_time_key),
> +KEY col_datetime_key(col_datetime_key),
> +KEY col_varchar_key(col_varchar_key, col_int_key)
> +);
> +INSERT INTO t1 VALUES
> +(10,1,7,'2007-04-28','2007-04-28','00:00:00','00:00:00','2004-06-06
> 04:22:12','2004-06-06 04:22:12','v','v'),
> +(11,7,0,'2006-04-05','2006-04-05','00:00:00','00:00:00','2005-11-13
> 01:12:31','2005-11-13 01:12:31','s','s'),
> +(12,4,9,'2002-02-02','2002-02-02','06:35:17','06:35:17','2002-05-04
> 01:50:00','2002-05-04 01:50:00','l','l'),
> +(13,7,3,'2002-06-07','2002-06-07','18:07:14','18:07:14','2004-10-27
> 10:28:45','2004-10-27 10:28:45','y','y'),
> +(14,0,4,'0000-00-00','0000-00-00','20:36:52','20:36:52','2006-07-22
> 05:24:23','2006-07-22 05:24:23','c','c'),
> +(15,2,2,'2003-01-13','2003-01-13','21:29:07','21:29:07','2002-05-16
> 21:34:03','2002-05-16 21:34:03','i','i'),
> +(16,9,5,'2006-07-07','2006-07-07','23:45:57','23:45:57','2008-04-17
> 10:45:30','2008-04-17 10:45:30','h','h'),
> +(17,4,3,'0000-00-00','0000-00-00','22:54:57','22:54:57','2009-04-21
> 02:58:02','2009-04-21 02:58:02','q','q'),
> +(18,0,1,'2002-06-15','2002-06-15','18:45:09','18:45:09','2008-01-11
> 11:01:51','2008-01-11 11:01:51','a','a'),
> +(19,9,3,'2006-07-09','2006-07-09','14:30:46','14:30:46','1900-01-01
> 00:00:00','1900-01-01 00:00:00','v','v'),
> +(20,1,6,'2001-04-17','2001-04-17','19:23:43','19:23:43','2007-05-17
> 18:24:57','2007-05-17 18:24:57','u','u'),
> +(21,3,7,'2005-12-22','2005-12-22','03:39:30','03:39:30','2007-08-07
> 00:00:00','2007-08-07 00:00:00','s','s'),
> +(22,8,5,'2009-05-03','2009-05-03','23:37:52','23:37:52','2001-08-28
> 00:00:00','2001-08-28 00:00:00','y','y'),
> +(23,8,1,'2003-05-28','2003-05-28','16:59:30','16:59:30','2004-04-16
> 00:27:28','2004-04-16 00:27:28','z','z'),
> +(24,18,204,'0000-00-00','0000-00-00','22:21:15','22:21:15','2005-05-03
> 07:06:22','2005-05-03 07:06:22','h','h'),
> +(25,84,224,'2001-11-03','2001-11-03','12:24:37','12:24:37','2009-03-11
> 17:09:50','2009-03-11 17:09:50','p','p'),
> +(26,6,9,'1900-01-01','1900-01-01','15:02:08','15:02:08','2007-12-08
> 01:54:28','2007-12-08 01:54:28','e','e'),
> +(27,3,5,'2000-02-17','2000-02-17','00:00:00','00:00:00','2009-07-28
> 18:19:54','2009-07-28 18:19:54','i','i'),
> +(28,6,0,'2001-01-23','2001-01-23','08:23:30','08:23:30','2008-06-08
> 00:00:00','2008-06-08 00:00:00','y','y'),
> +(29,6,3,'0000-00-00','0000-00-00','08:32:22','08:32:22','2005-02-09
> 09:20:26','2005-02-09 09:20:26','w','w');
> +CREATE TABLE t2 (
> +pk int NOT NULL,
> +col_date_nokey date NOT NULL,
> +PRIMARY KEY (pk)
> +);
> +INSERT INTO t2 VALUES
> +(1, '0000-00-00'),
> +(2, '2004-09-18'),
> +(3, '2009-12-01'),
> +(4, '2004-12-17'),
> +(5, '2000-03-14'),
> +(6, '2000-10-08'),
> +(7, '2006-05-25'),
> +(8, '2008-01-23'),
> +(9, '2007-06-18'),
> +(10, '2002-10-13'),
> +(11, '1900-01-01'),
> +(12, '0000-00-00'),
> +(13, '2006-03-09'),
> +(14, '2001-06-05'),
> +(15, '2006-05-28'),
> +(16, '2001-04-19'),
> +(17, '1900-01-01'),
> +(18, '2004-08-20'),
> +(19, '2004-10-10'),
> +(20, '2000-04-02');
> +CREATE TABLE t3 (
> +pk int NOT NULL,
> +col_int_key int NOT NULL,
> +col_varchar_key varchar(1) NOT NULL,
> +PRIMARY KEY (pk),
> +KEY col_int_key(col_int_key),
> +KEY col_varchar_key(col_varchar_key, col_int_key)
> +);
> +INSERT INTO t3 VALUES
> +(10, 7, 'b');
> +SELECT grandparent1.col_time_key AS g1
> +FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_date_nokey)
> +WHERE (grandparent1.col_varchar_key, grandparent1.col_varchar_key) IN
> +(SELECT parent1.col_varchar_nokey AS p1, parent1.col_varchar_nokey AS p2
> +FROM t1 AS parent1
> +WHERE parent1.col_varchar_key IN
> +(SELECT DISTINCT child1.col_varchar_nokey AS c1
> +FROM t1 AS child1 LEFT JOIN t3 AS child2
> +ON (child1.col_varchar_key> child2.col_varchar_key))
> +AND
> +(parent1.col_int_nokey<= parent1.pk OR
> +grandparent1.pk> 8))
> +AND
> +grandparent1.col_varchar_nokey<> 'w' AND
> +grandparent1.col_date_key<= '2004-09-11'
> +ORDER BY g1;
> +g1
> +00:00:00
> +06:35:17
> +08:23:30
> +12:24:37
> +15:02:08
> +15:02:08
> +16:59:30
> +18:07:14
> +18:45:09
> +19:23:43
> +20:36:52
> +20:36:52
> +21:29:07
> +22:21:15
> +22:21:15
> +22:54:57
> +22:54:57
> +-- 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, t3;
> +# End of the test for bug#12603183.
> set optimizer_switch=default;
> set optimizer_join_cache_level=default;
>
> === modified file 'sql/sql_select.cc'
> --- a/sql/sql_select.cc 2011-05-26 06:03:02 +0000
> +++ b/sql/sql_select.cc 2011-06-01 10:27:29 +0000
> @@ -17315,6 +17315,20 @@ sub_select_sjm(JOIN *join, JOIN_TAB *joi
> Next_select_func next_func= join_tab[sjm->table_count - 1].next_select;
> join_tab[sjm->table_count - 1].next_select= end_sj_materialize;
>
> + if (sjm->is_scan)
> + {
> + JOIN_TAB *last_tab= join_tab + (sjm->table_count - 1);
> + if (last_tab->save_read_first_record == NULL)
> + {
> + /* Save a copy of the read first function before substituting it */
> + last_tab->save_read_first_record= last_tab->read_first_record;
> + }
> + else
> + {
> + /* Restore read function saved in previous materialization round */
> + last_tab->read_first_record= last_tab->save_read_first_record;
> + }
> + }
> /*
> Now run the join for the inner tables. The first call is to run the
> join, the second one is to signal EOF (this is essential for some
> @@ -17331,7 +17345,6 @@ sub_select_sjm(JOIN *join, JOIN_TAB *joi
> /*
> Ok, materialization finished. Initialize the access to the temptable
> */
> - sjm->materialized= TRUE;
> join_tab->read_record.read_record= join_no_more_records;
> if (sjm->is_scan)
> {
> @@ -17340,7 +17353,6 @@ sub_select_sjm(JOIN *join, JOIN_TAB *joi
> init_read_record(&last_tab->read_record, join->thd,
> sjm->table, NULL, TRUE, TRUE, FALSE);
>
> - DBUG_ASSERT(last_tab->read_record.read_record == rr_sequential);
> last_tab->read_first_record= join_read_record_no_init;
> last_tab->read_record.copy_field= sjm->copy_field;
> last_tab->read_record.copy_field_end= sjm->copy_field +
> @@ -17350,6 +17362,8 @@ sub_select_sjm(JOIN *join, JOIN_TAB *joi
> // Clear possible outer join information from earlier use of this join tab
> last_tab->last_inner= NULL;
> }
> +
> + sjm->materialized= true;
> }
> else
> {
>
> === modified file 'sql/sql_select.h'
> --- a/sql/sql_select.h 2011-05-26 06:03:02 +0000
> +++ b/sql/sql_select.h 2011-06-01 10:27:29 +0000
> @@ -308,9 +308,10 @@ public:
> Next_select_func next_select;
> READ_RECORD read_record;
> /*
> - Currently the following two fields are used only for a [NOT] IN subquery
> - if it is executed by an alternative full table scan when the left operand of
> + The following two fields are used for a [NOT] IN subquery if it is
> + executed by an alternative full table scan when the left operand of
> the subquery predicate is evaluated to NULL.
> + save_read_first_record is also used by semi-join materialization strategy.
> */
> READ_RECORD::Setup_func save_read_first_record;/* to save read_first_record */
> READ_RECORD::Read_func save_read_record;/* to save read_record.read_record */
>
>
>
>
>
--
Øystein Grøvlen, Principal Software Engineer
MySQL Group, Oracle
Trondheim, Norway
--echo #
--echo # Bug#12603183: Segfault in hp_movelink
--echo #
CREATE TABLE t1 (
col_varchar_key varchar(1) ,
col_varchar_nokey varchar(1) ,
KEY col_varchar_key(col_varchar_key)
);
INSERT INTO t1 VALUES
('i','i'),
('h','h'),
('q','q'),
('a','a'),
('v','v'),
('u','u'),
('s','s'),
('y','y'),
('z','z'),
('h','h'),
('p','p'),
('e','e'),
('i','i'),
('y','y'),
('w','w');
CREATE TABLE t2 (
col_varchar_nokey varchar(1)
);
INSERT INTO t2 VALUES
('b');
SELECT grandparent1.col_varchar_nokey
FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
WHERE (grandparent1.col_varchar_key) IN
(SELECT parent1.col_varchar_nokey
FROM t1 AS parent1
WHERE parent1.col_varchar_key IN
(SELECT child1.col_varchar_nokey AS c1
FROM t1 AS child1 LEFT JOIN t2 AS child2
ON (child1.col_varchar_key > child2.col_varchar_nokey)));
--echo -- Notice that Materialize-scan algorithm reports wrong result for this query.
--echo -- This problem will be filed as a separate bug and dealt with in WL#5561.
DROP TABLE t1, t2;
--echo # End of the test for bug#12603183.
