List:Commits« Previous MessageNext Message »
From:Øystein Grøvlen Date:June 7 2011 11:32am
Subject:Re: bzr commit into mysql-trunk branch (roy.lyseng:3379) Bug#12603183
View as plain text  
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.

Thread
bzr commit into mysql-trunk branch (roy.lyseng:3379) Bug#12603183Roy Lyseng1 Jun
  • Re: bzr commit into mysql-trunk branch (roy.lyseng:3379) Bug#12603183Roy Lyseng1 Jun
  • Re: bzr commit into mysql-trunk branch (roy.lyseng:3379) Bug#12603183Øystein Grøvlen7 Jun
    • Re: bzr commit into mysql-trunk branch (roy.lyseng:3379) Bug#12603183Roy Lyseng25 Jun