List:Commits« Previous MessageNext Message »
From:Roy Lyseng Date:September 30 2011 12:26pm
Subject:bzr push into mysql-trunk branch (roy.lyseng:3447 to 3448) Bug#12797534
View as plain text  
 3448 Roy Lyseng	2011-09-30
      Bug#12797534: Segfault in hp_movelink still exists
      
      There was a silly mistake in the fix for bug#12603183: only the
      read_first_record() function was saved, but not the read_record()
      function. The result was more Valgrind warnings and/or segmentation
      faults for slightly different test cases.
      
      The proposed solution is simpler: A new scan is set up (by calling
      init_read_record) every time a scan over the materialized table
      is needed. Thus, saving the access parameters for the materialization
      can be done locally, without involving "saved" fields in join_tab.
      The tradeoff is slightly higher setup cost, because it would have
      been sufficient to call init_read_record() once for every
      materialization and only call ha_rnd_init() for each scan.
      This tiny problem may be attacked by refactoring.
      
      mysql-test/include/subquery_sj.inc
        Added two test cases for bug#12797534.
      
      mysql-test/r/subquery_sj_all.result
      mysql-test/r/subquery_sj_all_bka.result
      mysql-test/r/subquery_sj_all_bka_nobnl.result
      mysql-test/r/subquery_sj_all_bkaunique.result
      mysql-test/r/subquery_sj_dupsweed.result
      mysql-test/r/subquery_sj_dupsweed_bka.result
      mysql-test/r/subquery_sj_dupsweed_bka_nobnl.result
      mysql-test/r/subquery_sj_dupsweed_bkaunique.result
      mysql-test/r/subquery_sj_firstmatch.result
      mysql-test/r/subquery_sj_firstmatch_bka.result
      mysql-test/r/subquery_sj_firstmatch_bka_nobnl.result
      mysql-test/r/subquery_sj_firstmatch_bkaunique.result
      mysql-test/r/subquery_sj_loosescan.result
      mysql-test/r/subquery_sj_loosescan_bka.result
      mysql-test/r/subquery_sj_loosescan_bka_nobnl.result
      mysql-test/r/subquery_sj_loosescan_bkaunique.result
      mysql-test/r/subquery_sj_mat.result
      mysql-test/r/subquery_sj_mat_bka.result
      mysql-test/r/subquery_sj_mat_bka_nobnl.result
      mysql-test/r/subquery_sj_mat_bkaunique.result
      mysql-test/r/subquery_sj_mat_nosj.result
      mysql-test/r/subquery_sj_none.result
      mysql-test/r/subquery_sj_none_bka.result
      mysql-test/r/subquery_sj_none_bka_nobnl.result
      mysql-test/r/subquery_sj_none_bkaunique.result
        Updated with test results for bug#12797534.
      
      sql/sql_select.cc
        sub_select_sjm():
        Save access parameters for materialization in a local struct
        every time a scan over the materialized table is needed.
        Restore the parameters immediately after scan is finished.
        Call end_read_record() after the materialized table is scanned.
        Some code cleanup suggested by reviewers.
      
      sql/sql_select.h
        Removed a comment about usage of 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_bka.result
      mysql-test/r/subquery_sj_all_bka_nobnl.result
      mysql-test/r/subquery_sj_all_bkaunique.result
      mysql-test/r/subquery_sj_dupsweed.result
      mysql-test/r/subquery_sj_dupsweed_bka.result
      mysql-test/r/subquery_sj_dupsweed_bka_nobnl.result
      mysql-test/r/subquery_sj_dupsweed_bkaunique.result
      mysql-test/r/subquery_sj_firstmatch.result
      mysql-test/r/subquery_sj_firstmatch_bka.result
      mysql-test/r/subquery_sj_firstmatch_bka_nobnl.result
      mysql-test/r/subquery_sj_firstmatch_bkaunique.result
      mysql-test/r/subquery_sj_loosescan.result
      mysql-test/r/subquery_sj_loosescan_bka.result
      mysql-test/r/subquery_sj_loosescan_bka_nobnl.result
      mysql-test/r/subquery_sj_loosescan_bkaunique.result
      mysql-test/r/subquery_sj_mat.result
      mysql-test/r/subquery_sj_mat_bka.result
      mysql-test/r/subquery_sj_mat_bka_nobnl.result
      mysql-test/r/subquery_sj_mat_bkaunique.result
      mysql-test/r/subquery_sj_mat_nosj.result
      mysql-test/r/subquery_sj_none.result
      mysql-test/r/subquery_sj_none_bka.result
      mysql-test/r/subquery_sj_none_bka_nobnl.result
      mysql-test/r/subquery_sj_none_bkaunique.result
      sql/sql_select.cc
      sql/sql_select.h
 3447 Tor Didriksen	2011-09-28 [merge]
      empty merge trunk => opt-backporting

=== modified file 'mysql-test/include/subquery_sj.inc'
--- a/mysql-test/include/subquery_sj.inc	2011-09-08 12:48:08 +0000
+++ b/mysql-test/include/subquery_sj.inc	2011-09-30 10:22:38 +0000
@@ -3983,3 +3983,105 @@ HAVING a IN (SELECT a
 DROP TABLE t1, t2;
 
 --echo # End of test for bug#12803439.
+
+--echo #
+--echo # Bug#12797534: Segfault in hp_movelink still exists
+--echo #
+
+CREATE TABLE t1 (
+ g1 VARCHAR(1) NOT NULL
+) ENGINE=InnoDB;
+
+INSERT INTO t1 VALUES ('d'), ('s');
+
+CREATE TABLE t2 (
+ pk INT NOT NULL,
+ col_int_key INT NOT NULL,
+ col_varchar_key VARCHAR(1) NOT NULL,
+ col_varchar_nokey VARCHAR(1) NOT NULL,
+ PRIMARY KEY (pk),
+ KEY col_varchar_key(col_varchar_key, col_int_key)
+) ENGINE=InnoDB;
+
+INSERT INTO t2 VALUES
+ (1,4,'j','j'), (2,6,'v','v'), (3,3,'c','c'), (4,5,'m','m'),
+ (5,3,'d','d'), (6,246,'d','d'), (7,2,'y','y'), (8,9,'t','t'),
+ (9,3,'d','d'), (10,8,'s','s'), (11,1,'r','r'), (12,8,'m','m'),
+ (13,8,'b','b'), (14,5,'x','x'), (15,7,'g','g'), (16,5,'p','p'),
+ (17,1,'q','q'), (18,6,'w','w'), (19,2,'d','d'), (20,9,'e','e');
+
+CREATE TABLE t3 (
+ pk INTEGER NOT NULL,
+ PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+
+INSERT INTO t3 VALUES (10);
+
+let $query=
+SELECT *
+FROM t1
+WHERE g1 NOT IN
+   (SELECT  grandparent1.col_varchar_nokey AS g1
+    FROM t2 AS grandparent1
+    WHERE grandparent1.col_varchar_key IN
+       (SELECT parent1.col_varchar_nokey AS p1
+        FROM t2 AS parent1 LEFT JOIN t3 AS parent2 USING (pk)
+        )
+      AND grandparent1.col_varchar_key IS NOT NULL
+    );
+
+eval EXPLAIN $query;
+eval $query;
+
+DROP TABLE t1, t2, t3;
+
+CREATE TABLE t1 (
+ pk INTEGER AUTO_INCREMENT,
+ col_int_key INTEGER ,
+ col_varchar_key VARCHAR(1) ,
+ col_varchar_nokey VARCHAR(1) ,
+ PRIMARY KEY (pk),
+ KEY (col_varchar_key,col_int_key)
+ ) ENGINE=INNODB;
+INSERT INTO t1 (col_int_key,col_varchar_key,col_varchar_nokey) VALUES
+ (0,'x','x'), (1,'j','j'), (1,'r','r'), (9,'v','v'), (5,'r','r');
+
+CREATE TABLE t2 (
+ pk INTEGER AUTO_INCREMENT,
+ col_int_key INTEGER ,
+ col_varchar_key VARCHAR(1) ,
+ col_varchar_nokey VARCHAR(1) ,
+ PRIMARY KEY (pk),
+ KEY (col_int_key),
+ KEY (col_varchar_key,col_int_key)
+ ) AUTO_INCREMENT=10 ENGINE=INNODB;
+INSERT INTO t2 (col_int_key, col_varchar_key, col_varchar_nokey) VALUES
+ (NULL,'x','x'), (NULL,'j','j'), (8,'c','c');
+
+CREATE TABLE t3
+SELECT outr.col_varchar_nokey AS x
+FROM t1 AS outr
+WHERE outr.col_varchar_nokey IN
+   (SELECT innr.col_varchar_nokey AS y
+    FROM t2 AS innr
+    WHERE innr.col_int_key IS NULL)
+  AND outr.col_varchar_nokey IS NOT NULL
+  AND NOT col_varchar_key IS NULL;
+
+SELECT *
+FROM t3
+WHERE x NOT IN
+   (SELECT outr.col_varchar_nokey AS x
+    FROM t1 AS outr
+    WHERE outr.col_varchar_nokey IN
+       (SELECT innr.col_varchar_nokey AS y
+        FROM t2 AS innr
+        WHERE innr.col_int_key IS NULL)
+      AND outr.col_varchar_nokey IS NOT NULL
+      AND NOT col_varchar_key IS NULL);
+
+DROP TABLE t1, t2, t3;
+
+--echo # End of test for bug#12797534.
+
+--echo # End of 5.6 tests

=== modified file 'mysql-test/r/subquery_sj_all.result'
--- a/mysql-test/r/subquery_sj_all.result	2011-09-08 12:48:08 +0000
+++ b/mysql-test/r/subquery_sj_all.result	2011-09-30 10:22:38 +0000
@@ -6668,4 +6668,104 @@ a
 1
 DROP TABLE t1, t2;
 # End of test for bug#12803439.
+#
+# Bug#12797534: Segfault in hp_movelink still exists
+#
+CREATE TABLE t1 (
+g1 VARCHAR(1) NOT NULL
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES ('d'), ('s');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,4,'j','j'), (2,6,'v','v'), (3,3,'c','c'), (4,5,'m','m'),
+(5,3,'d','d'), (6,246,'d','d'), (7,2,'y','y'), (8,9,'t','t'),
+(9,3,'d','d'), (10,8,'s','s'), (11,1,'r','r'), (12,8,'m','m'),
+(13,8,'b','b'), (14,5,'x','x'), (15,7,'g','g'), (16,5,'p','p'),
+(17,1,'q','q'), (18,6,'w','w'), (19,2,'d','d'), (20,9,'e','e');
+CREATE TABLE t3 (
+pk INTEGER NOT NULL,
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES (10);
+EXPLAIN SELECT *
+FROM t1
+WHERE g1 NOT IN
+(SELECT  grandparent1.col_varchar_nokey AS g1
+FROM t2 AS grandparent1
+WHERE grandparent1.col_varchar_key IN
+(SELECT parent1.col_varchar_nokey AS p1
+FROM t2 AS parent1 LEFT JOIN t3 AS parent2 USING (pk)
+)
+AND grandparent1.col_varchar_key IS NOT NULL
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	parent1	ALL	NULL	NULL	NULL	NULL	20	Start materialize; Scan
+2	DEPENDENT SUBQUERY	parent2	eq_ref	PRIMARY	PRIMARY	4	test.parent1.pk	1	Using index; End materialize
+2	DEPENDENT SUBQUERY	grandparent1	ref	col_varchar_key	col_varchar_key	3	test.parent1.col_varchar_nokey	1	Using index condition; Using where
+SELECT *
+FROM t1
+WHERE g1 NOT IN
+(SELECT  grandparent1.col_varchar_nokey AS g1
+FROM t2 AS grandparent1
+WHERE grandparent1.col_varchar_key IN
+(SELECT parent1.col_varchar_nokey AS p1
+FROM t2 AS parent1 LEFT JOIN t3 AS parent2 USING (pk)
+)
+AND grandparent1.col_varchar_key IS NOT NULL
+);
+g1
+DROP TABLE t1, t2, t3;
+CREATE TABLE t1 (
+pk INTEGER AUTO_INCREMENT,
+col_int_key INTEGER ,
+col_varchar_key VARCHAR(1) ,
+col_varchar_nokey VARCHAR(1) ,
+PRIMARY KEY (pk),
+KEY (col_varchar_key,col_int_key)
+) ENGINE=INNODB;
+INSERT INTO t1 (col_int_key,col_varchar_key,col_varchar_nokey) VALUES
+(0,'x','x'), (1,'j','j'), (1,'r','r'), (9,'v','v'), (5,'r','r');
+CREATE TABLE t2 (
+pk INTEGER AUTO_INCREMENT,
+col_int_key INTEGER ,
+col_varchar_key VARCHAR(1) ,
+col_varchar_nokey VARCHAR(1) ,
+PRIMARY KEY (pk),
+KEY (col_int_key),
+KEY (col_varchar_key,col_int_key)
+) AUTO_INCREMENT=10 ENGINE=INNODB;
+INSERT INTO t2 (col_int_key, col_varchar_key, col_varchar_nokey) VALUES
+(NULL,'x','x'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t3
+SELECT outr.col_varchar_nokey AS x
+FROM t1 AS outr
+WHERE outr.col_varchar_nokey IN
+(SELECT innr.col_varchar_nokey AS y
+FROM t2 AS innr
+WHERE innr.col_int_key IS NULL)
+AND outr.col_varchar_nokey IS NOT NULL
+AND NOT col_varchar_key IS NULL;
+SELECT *
+FROM t3
+WHERE x NOT IN
+(SELECT outr.col_varchar_nokey AS x
+FROM t1 AS outr
+WHERE outr.col_varchar_nokey IN
+(SELECT innr.col_varchar_nokey AS y
+FROM t2 AS innr
+WHERE innr.col_int_key IS NULL)
+AND outr.col_varchar_nokey IS NOT NULL
+AND NOT col_varchar_key IS NULL);
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#12797534.
+# End of 5.6 tests
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_all_bka.result'
--- a/mysql-test/r/subquery_sj_all_bka.result	2011-09-08 12:48:08 +0000
+++ b/mysql-test/r/subquery_sj_all_bka.result	2011-09-30 10:22:38 +0000
@@ -6669,5 +6669,105 @@ a
 1
 DROP TABLE t1, t2;
 # End of test for bug#12803439.
+#
+# Bug#12797534: Segfault in hp_movelink still exists
+#
+CREATE TABLE t1 (
+g1 VARCHAR(1) NOT NULL
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES ('d'), ('s');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,4,'j','j'), (2,6,'v','v'), (3,3,'c','c'), (4,5,'m','m'),
+(5,3,'d','d'), (6,246,'d','d'), (7,2,'y','y'), (8,9,'t','t'),
+(9,3,'d','d'), (10,8,'s','s'), (11,1,'r','r'), (12,8,'m','m'),
+(13,8,'b','b'), (14,5,'x','x'), (15,7,'g','g'), (16,5,'p','p'),
+(17,1,'q','q'), (18,6,'w','w'), (19,2,'d','d'), (20,9,'e','e');
+CREATE TABLE t3 (
+pk INTEGER NOT NULL,
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES (10);
+EXPLAIN SELECT *
+FROM t1
+WHERE g1 NOT IN
+(SELECT  grandparent1.col_varchar_nokey AS g1
+FROM t2 AS grandparent1
+WHERE grandparent1.col_varchar_key IN
+(SELECT parent1.col_varchar_nokey AS p1
+FROM t2 AS parent1 LEFT JOIN t3 AS parent2 USING (pk)
+)
+AND grandparent1.col_varchar_key IS NOT NULL
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	parent1	ALL	NULL	NULL	NULL	NULL	20	Start materialize; Scan
+2	DEPENDENT SUBQUERY	parent2	eq_ref	PRIMARY	PRIMARY	4	test.parent1.pk	1	Using index; End materialize
+2	DEPENDENT SUBQUERY	grandparent1	ref	col_varchar_key	col_varchar_key	3	test.parent1.col_varchar_nokey	1	Using index condition; Using where; Using join buffer (Batch Key Access)
+SELECT *
+FROM t1
+WHERE g1 NOT IN
+(SELECT  grandparent1.col_varchar_nokey AS g1
+FROM t2 AS grandparent1
+WHERE grandparent1.col_varchar_key IN
+(SELECT parent1.col_varchar_nokey AS p1
+FROM t2 AS parent1 LEFT JOIN t3 AS parent2 USING (pk)
+)
+AND grandparent1.col_varchar_key IS NOT NULL
+);
+g1
+DROP TABLE t1, t2, t3;
+CREATE TABLE t1 (
+pk INTEGER AUTO_INCREMENT,
+col_int_key INTEGER ,
+col_varchar_key VARCHAR(1) ,
+col_varchar_nokey VARCHAR(1) ,
+PRIMARY KEY (pk),
+KEY (col_varchar_key,col_int_key)
+) ENGINE=INNODB;
+INSERT INTO t1 (col_int_key,col_varchar_key,col_varchar_nokey) VALUES
+(0,'x','x'), (1,'j','j'), (1,'r','r'), (9,'v','v'), (5,'r','r');
+CREATE TABLE t2 (
+pk INTEGER AUTO_INCREMENT,
+col_int_key INTEGER ,
+col_varchar_key VARCHAR(1) ,
+col_varchar_nokey VARCHAR(1) ,
+PRIMARY KEY (pk),
+KEY (col_int_key),
+KEY (col_varchar_key,col_int_key)
+) AUTO_INCREMENT=10 ENGINE=INNODB;
+INSERT INTO t2 (col_int_key, col_varchar_key, col_varchar_nokey) VALUES
+(NULL,'x','x'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t3
+SELECT outr.col_varchar_nokey AS x
+FROM t1 AS outr
+WHERE outr.col_varchar_nokey IN
+(SELECT innr.col_varchar_nokey AS y
+FROM t2 AS innr
+WHERE innr.col_int_key IS NULL)
+AND outr.col_varchar_nokey IS NOT NULL
+AND NOT col_varchar_key IS NULL;
+SELECT *
+FROM t3
+WHERE x NOT IN
+(SELECT outr.col_varchar_nokey AS x
+FROM t1 AS outr
+WHERE outr.col_varchar_nokey IN
+(SELECT innr.col_varchar_nokey AS y
+FROM t2 AS innr
+WHERE innr.col_int_key IS NULL)
+AND outr.col_varchar_nokey IS NOT NULL
+AND NOT col_varchar_key IS NULL);
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#12797534.
+# End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_all_bka_nobnl.result'
--- a/mysql-test/r/subquery_sj_all_bka_nobnl.result	2011-09-08 12:48:08 +0000
+++ b/mysql-test/r/subquery_sj_all_bka_nobnl.result	2011-09-30 10:22:38 +0000
@@ -6669,5 +6669,105 @@ a
 1
 DROP TABLE t1, t2;
 # End of test for bug#12803439.
+#
+# Bug#12797534: Segfault in hp_movelink still exists
+#
+CREATE TABLE t1 (
+g1 VARCHAR(1) NOT NULL
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES ('d'), ('s');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,4,'j','j'), (2,6,'v','v'), (3,3,'c','c'), (4,5,'m','m'),
+(5,3,'d','d'), (6,246,'d','d'), (7,2,'y','y'), (8,9,'t','t'),
+(9,3,'d','d'), (10,8,'s','s'), (11,1,'r','r'), (12,8,'m','m'),
+(13,8,'b','b'), (14,5,'x','x'), (15,7,'g','g'), (16,5,'p','p'),
+(17,1,'q','q'), (18,6,'w','w'), (19,2,'d','d'), (20,9,'e','e');
+CREATE TABLE t3 (
+pk INTEGER NOT NULL,
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES (10);
+EXPLAIN SELECT *
+FROM t1
+WHERE g1 NOT IN
+(SELECT  grandparent1.col_varchar_nokey AS g1
+FROM t2 AS grandparent1
+WHERE grandparent1.col_varchar_key IN
+(SELECT parent1.col_varchar_nokey AS p1
+FROM t2 AS parent1 LEFT JOIN t3 AS parent2 USING (pk)
+)
+AND grandparent1.col_varchar_key IS NOT NULL
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	parent1	ALL	NULL	NULL	NULL	NULL	20	Start materialize; Scan
+2	DEPENDENT SUBQUERY	parent2	eq_ref	PRIMARY	PRIMARY	4	test.parent1.pk	1	Using index; End materialize
+2	DEPENDENT SUBQUERY	grandparent1	ref	col_varchar_key	col_varchar_key	3	test.parent1.col_varchar_nokey	1	Using index condition; Using where; Using join buffer (Batch Key Access)
+SELECT *
+FROM t1
+WHERE g1 NOT IN
+(SELECT  grandparent1.col_varchar_nokey AS g1
+FROM t2 AS grandparent1
+WHERE grandparent1.col_varchar_key IN
+(SELECT parent1.col_varchar_nokey AS p1
+FROM t2 AS parent1 LEFT JOIN t3 AS parent2 USING (pk)
+)
+AND grandparent1.col_varchar_key IS NOT NULL
+);
+g1
+DROP TABLE t1, t2, t3;
+CREATE TABLE t1 (
+pk INTEGER AUTO_INCREMENT,
+col_int_key INTEGER ,
+col_varchar_key VARCHAR(1) ,
+col_varchar_nokey VARCHAR(1) ,
+PRIMARY KEY (pk),
+KEY (col_varchar_key,col_int_key)
+) ENGINE=INNODB;
+INSERT INTO t1 (col_int_key,col_varchar_key,col_varchar_nokey) VALUES
+(0,'x','x'), (1,'j','j'), (1,'r','r'), (9,'v','v'), (5,'r','r');
+CREATE TABLE t2 (
+pk INTEGER AUTO_INCREMENT,
+col_int_key INTEGER ,
+col_varchar_key VARCHAR(1) ,
+col_varchar_nokey VARCHAR(1) ,
+PRIMARY KEY (pk),
+KEY (col_int_key),
+KEY (col_varchar_key,col_int_key)
+) AUTO_INCREMENT=10 ENGINE=INNODB;
+INSERT INTO t2 (col_int_key, col_varchar_key, col_varchar_nokey) VALUES
+(NULL,'x','x'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t3
+SELECT outr.col_varchar_nokey AS x
+FROM t1 AS outr
+WHERE outr.col_varchar_nokey IN
+(SELECT innr.col_varchar_nokey AS y
+FROM t2 AS innr
+WHERE innr.col_int_key IS NULL)
+AND outr.col_varchar_nokey IS NOT NULL
+AND NOT col_varchar_key IS NULL;
+SELECT *
+FROM t3
+WHERE x NOT IN
+(SELECT outr.col_varchar_nokey AS x
+FROM t1 AS outr
+WHERE outr.col_varchar_nokey IN
+(SELECT innr.col_varchar_nokey AS y
+FROM t2 AS innr
+WHERE innr.col_int_key IS NULL)
+AND outr.col_varchar_nokey IS NOT NULL
+AND NOT col_varchar_key IS NULL);
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#12797534.
+# End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_all_bkaunique.result'
--- a/mysql-test/r/subquery_sj_all_bkaunique.result	2011-09-08 12:48:08 +0000
+++ b/mysql-test/r/subquery_sj_all_bkaunique.result	2011-09-30 10:22:38 +0000
@@ -6670,5 +6670,105 @@ a
 1
 DROP TABLE t1, t2;
 # End of test for bug#12803439.
+#
+# Bug#12797534: Segfault in hp_movelink still exists
+#
+CREATE TABLE t1 (
+g1 VARCHAR(1) NOT NULL
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES ('d'), ('s');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,4,'j','j'), (2,6,'v','v'), (3,3,'c','c'), (4,5,'m','m'),
+(5,3,'d','d'), (6,246,'d','d'), (7,2,'y','y'), (8,9,'t','t'),
+(9,3,'d','d'), (10,8,'s','s'), (11,1,'r','r'), (12,8,'m','m'),
+(13,8,'b','b'), (14,5,'x','x'), (15,7,'g','g'), (16,5,'p','p'),
+(17,1,'q','q'), (18,6,'w','w'), (19,2,'d','d'), (20,9,'e','e');
+CREATE TABLE t3 (
+pk INTEGER NOT NULL,
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES (10);
+EXPLAIN SELECT *
+FROM t1
+WHERE g1 NOT IN
+(SELECT  grandparent1.col_varchar_nokey AS g1
+FROM t2 AS grandparent1
+WHERE grandparent1.col_varchar_key IN
+(SELECT parent1.col_varchar_nokey AS p1
+FROM t2 AS parent1 LEFT JOIN t3 AS parent2 USING (pk)
+)
+AND grandparent1.col_varchar_key IS NOT NULL
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	parent1	ALL	NULL	NULL	NULL	NULL	20	Start materialize; Scan
+2	DEPENDENT SUBQUERY	parent2	eq_ref	PRIMARY	PRIMARY	4	test.parent1.pk	1	Using index; End materialize
+2	DEPENDENT SUBQUERY	grandparent1	ref	col_varchar_key	col_varchar_key	3	test.parent1.col_varchar_nokey	1	Using index condition; Using where; Using join buffer (Batch Key Access (unique))
+SELECT *
+FROM t1
+WHERE g1 NOT IN
+(SELECT  grandparent1.col_varchar_nokey AS g1
+FROM t2 AS grandparent1
+WHERE grandparent1.col_varchar_key IN
+(SELECT parent1.col_varchar_nokey AS p1
+FROM t2 AS parent1 LEFT JOIN t3 AS parent2 USING (pk)
+)
+AND grandparent1.col_varchar_key IS NOT NULL
+);
+g1
+DROP TABLE t1, t2, t3;
+CREATE TABLE t1 (
+pk INTEGER AUTO_INCREMENT,
+col_int_key INTEGER ,
+col_varchar_key VARCHAR(1) ,
+col_varchar_nokey VARCHAR(1) ,
+PRIMARY KEY (pk),
+KEY (col_varchar_key,col_int_key)
+) ENGINE=INNODB;
+INSERT INTO t1 (col_int_key,col_varchar_key,col_varchar_nokey) VALUES
+(0,'x','x'), (1,'j','j'), (1,'r','r'), (9,'v','v'), (5,'r','r');
+CREATE TABLE t2 (
+pk INTEGER AUTO_INCREMENT,
+col_int_key INTEGER ,
+col_varchar_key VARCHAR(1) ,
+col_varchar_nokey VARCHAR(1) ,
+PRIMARY KEY (pk),
+KEY (col_int_key),
+KEY (col_varchar_key,col_int_key)
+) AUTO_INCREMENT=10 ENGINE=INNODB;
+INSERT INTO t2 (col_int_key, col_varchar_key, col_varchar_nokey) VALUES
+(NULL,'x','x'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t3
+SELECT outr.col_varchar_nokey AS x
+FROM t1 AS outr
+WHERE outr.col_varchar_nokey IN
+(SELECT innr.col_varchar_nokey AS y
+FROM t2 AS innr
+WHERE innr.col_int_key IS NULL)
+AND outr.col_varchar_nokey IS NOT NULL
+AND NOT col_varchar_key IS NULL;
+SELECT *
+FROM t3
+WHERE x NOT IN
+(SELECT outr.col_varchar_nokey AS x
+FROM t1 AS outr
+WHERE outr.col_varchar_nokey IN
+(SELECT innr.col_varchar_nokey AS y
+FROM t2 AS innr
+WHERE innr.col_int_key IS NULL)
+AND outr.col_varchar_nokey IS NOT NULL
+AND NOT col_varchar_key IS NULL);
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#12797534.
+# End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_dupsweed.result'
--- a/mysql-test/r/subquery_sj_dupsweed.result	2011-09-08 12:48:08 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed.result	2011-09-30 10:22:38 +0000
@@ -6668,4 +6668,104 @@ a
 1
 DROP TABLE t1, t2;
 # End of test for bug#12803439.
+#
+# Bug#12797534: Segfault in hp_movelink still exists
+#
+CREATE TABLE t1 (
+g1 VARCHAR(1) NOT NULL
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES ('d'), ('s');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,4,'j','j'), (2,6,'v','v'), (3,3,'c','c'), (4,5,'m','m'),
+(5,3,'d','d'), (6,246,'d','d'), (7,2,'y','y'), (8,9,'t','t'),
+(9,3,'d','d'), (10,8,'s','s'), (11,1,'r','r'), (12,8,'m','m'),
+(13,8,'b','b'), (14,5,'x','x'), (15,7,'g','g'), (16,5,'p','p'),
+(17,1,'q','q'), (18,6,'w','w'), (19,2,'d','d'), (20,9,'e','e');
+CREATE TABLE t3 (
+pk INTEGER NOT NULL,
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES (10);
+EXPLAIN SELECT *
+FROM t1
+WHERE g1 NOT IN
+(SELECT  grandparent1.col_varchar_nokey AS g1
+FROM t2 AS grandparent1
+WHERE grandparent1.col_varchar_key IN
+(SELECT parent1.col_varchar_nokey AS p1
+FROM t2 AS parent1 LEFT JOIN t3 AS parent2 USING (pk)
+)
+AND grandparent1.col_varchar_key IS NOT NULL
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	parent1	ALL	NULL	NULL	NULL	NULL	20	Start temporary
+2	DEPENDENT SUBQUERY	grandparent1	ref	col_varchar_key	col_varchar_key	3	test.parent1.col_varchar_nokey	1	Using where
+2	DEPENDENT SUBQUERY	parent2	eq_ref	PRIMARY	PRIMARY	4	test.parent1.pk	1	Using index; End temporary
+SELECT *
+FROM t1
+WHERE g1 NOT IN
+(SELECT  grandparent1.col_varchar_nokey AS g1
+FROM t2 AS grandparent1
+WHERE grandparent1.col_varchar_key IN
+(SELECT parent1.col_varchar_nokey AS p1
+FROM t2 AS parent1 LEFT JOIN t3 AS parent2 USING (pk)
+)
+AND grandparent1.col_varchar_key IS NOT NULL
+);
+g1
+DROP TABLE t1, t2, t3;
+CREATE TABLE t1 (
+pk INTEGER AUTO_INCREMENT,
+col_int_key INTEGER ,
+col_varchar_key VARCHAR(1) ,
+col_varchar_nokey VARCHAR(1) ,
+PRIMARY KEY (pk),
+KEY (col_varchar_key,col_int_key)
+) ENGINE=INNODB;
+INSERT INTO t1 (col_int_key,col_varchar_key,col_varchar_nokey) VALUES
+(0,'x','x'), (1,'j','j'), (1,'r','r'), (9,'v','v'), (5,'r','r');
+CREATE TABLE t2 (
+pk INTEGER AUTO_INCREMENT,
+col_int_key INTEGER ,
+col_varchar_key VARCHAR(1) ,
+col_varchar_nokey VARCHAR(1) ,
+PRIMARY KEY (pk),
+KEY (col_int_key),
+KEY (col_varchar_key,col_int_key)
+) AUTO_INCREMENT=10 ENGINE=INNODB;
+INSERT INTO t2 (col_int_key, col_varchar_key, col_varchar_nokey) VALUES
+(NULL,'x','x'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t3
+SELECT outr.col_varchar_nokey AS x
+FROM t1 AS outr
+WHERE outr.col_varchar_nokey IN
+(SELECT innr.col_varchar_nokey AS y
+FROM t2 AS innr
+WHERE innr.col_int_key IS NULL)
+AND outr.col_varchar_nokey IS NOT NULL
+AND NOT col_varchar_key IS NULL;
+SELECT *
+FROM t3
+WHERE x NOT IN
+(SELECT outr.col_varchar_nokey AS x
+FROM t1 AS outr
+WHERE outr.col_varchar_nokey IN
+(SELECT innr.col_varchar_nokey AS y
+FROM t2 AS innr
+WHERE innr.col_int_key IS NULL)
+AND outr.col_varchar_nokey IS NOT NULL
+AND NOT col_varchar_key IS NULL);
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#12797534.
+# End of 5.6 tests
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_dupsweed_bka.result'
--- a/mysql-test/r/subquery_sj_dupsweed_bka.result	2011-09-08 12:48:08 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bka.result	2011-09-30 10:22:38 +0000
@@ -6669,5 +6669,105 @@ a
 1
 DROP TABLE t1, t2;
 # End of test for bug#12803439.
+#
+# Bug#12797534: Segfault in hp_movelink still exists
+#
+CREATE TABLE t1 (
+g1 VARCHAR(1) NOT NULL
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES ('d'), ('s');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,4,'j','j'), (2,6,'v','v'), (3,3,'c','c'), (4,5,'m','m'),
+(5,3,'d','d'), (6,246,'d','d'), (7,2,'y','y'), (8,9,'t','t'),
+(9,3,'d','d'), (10,8,'s','s'), (11,1,'r','r'), (12,8,'m','m'),
+(13,8,'b','b'), (14,5,'x','x'), (15,7,'g','g'), (16,5,'p','p'),
+(17,1,'q','q'), (18,6,'w','w'), (19,2,'d','d'), (20,9,'e','e');
+CREATE TABLE t3 (
+pk INTEGER NOT NULL,
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES (10);
+EXPLAIN SELECT *
+FROM t1
+WHERE g1 NOT IN
+(SELECT  grandparent1.col_varchar_nokey AS g1
+FROM t2 AS grandparent1
+WHERE grandparent1.col_varchar_key IN
+(SELECT parent1.col_varchar_nokey AS p1
+FROM t2 AS parent1 LEFT JOIN t3 AS parent2 USING (pk)
+)
+AND grandparent1.col_varchar_key IS NOT NULL
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	parent1	ALL	NULL	NULL	NULL	NULL	20	Start temporary
+2	DEPENDENT SUBQUERY	grandparent1	ref	col_varchar_key	col_varchar_key	3	test.parent1.col_varchar_nokey	1	Using where
+2	DEPENDENT SUBQUERY	parent2	eq_ref	PRIMARY	PRIMARY	4	test.parent1.pk	1	Using index; End temporary
+SELECT *
+FROM t1
+WHERE g1 NOT IN
+(SELECT  grandparent1.col_varchar_nokey AS g1
+FROM t2 AS grandparent1
+WHERE grandparent1.col_varchar_key IN
+(SELECT parent1.col_varchar_nokey AS p1
+FROM t2 AS parent1 LEFT JOIN t3 AS parent2 USING (pk)
+)
+AND grandparent1.col_varchar_key IS NOT NULL
+);
+g1
+DROP TABLE t1, t2, t3;
+CREATE TABLE t1 (
+pk INTEGER AUTO_INCREMENT,
+col_int_key INTEGER ,
+col_varchar_key VARCHAR(1) ,
+col_varchar_nokey VARCHAR(1) ,
+PRIMARY KEY (pk),
+KEY (col_varchar_key,col_int_key)
+) ENGINE=INNODB;
+INSERT INTO t1 (col_int_key,col_varchar_key,col_varchar_nokey) VALUES
+(0,'x','x'), (1,'j','j'), (1,'r','r'), (9,'v','v'), (5,'r','r');
+CREATE TABLE t2 (
+pk INTEGER AUTO_INCREMENT,
+col_int_key INTEGER ,
+col_varchar_key VARCHAR(1) ,
+col_varchar_nokey VARCHAR(1) ,
+PRIMARY KEY (pk),
+KEY (col_int_key),
+KEY (col_varchar_key,col_int_key)
+) AUTO_INCREMENT=10 ENGINE=INNODB;
+INSERT INTO t2 (col_int_key, col_varchar_key, col_varchar_nokey) VALUES
+(NULL,'x','x'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t3
+SELECT outr.col_varchar_nokey AS x
+FROM t1 AS outr
+WHERE outr.col_varchar_nokey IN
+(SELECT innr.col_varchar_nokey AS y
+FROM t2 AS innr
+WHERE innr.col_int_key IS NULL)
+AND outr.col_varchar_nokey IS NOT NULL
+AND NOT col_varchar_key IS NULL;
+SELECT *
+FROM t3
+WHERE x NOT IN
+(SELECT outr.col_varchar_nokey AS x
+FROM t1 AS outr
+WHERE outr.col_varchar_nokey IN
+(SELECT innr.col_varchar_nokey AS y
+FROM t2 AS innr
+WHERE innr.col_int_key IS NULL)
+AND outr.col_varchar_nokey IS NOT NULL
+AND NOT col_varchar_key IS NULL);
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#12797534.
+# End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_dupsweed_bka_nobnl.result'
--- a/mysql-test/r/subquery_sj_dupsweed_bka_nobnl.result	2011-09-08 12:48:08 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bka_nobnl.result	2011-09-30 10:22:38 +0000
@@ -6669,5 +6669,105 @@ a
 1
 DROP TABLE t1, t2;
 # End of test for bug#12803439.
+#
+# Bug#12797534: Segfault in hp_movelink still exists
+#
+CREATE TABLE t1 (
+g1 VARCHAR(1) NOT NULL
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES ('d'), ('s');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,4,'j','j'), (2,6,'v','v'), (3,3,'c','c'), (4,5,'m','m'),
+(5,3,'d','d'), (6,246,'d','d'), (7,2,'y','y'), (8,9,'t','t'),
+(9,3,'d','d'), (10,8,'s','s'), (11,1,'r','r'), (12,8,'m','m'),
+(13,8,'b','b'), (14,5,'x','x'), (15,7,'g','g'), (16,5,'p','p'),
+(17,1,'q','q'), (18,6,'w','w'), (19,2,'d','d'), (20,9,'e','e');
+CREATE TABLE t3 (
+pk INTEGER NOT NULL,
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES (10);
+EXPLAIN SELECT *
+FROM t1
+WHERE g1 NOT IN
+(SELECT  grandparent1.col_varchar_nokey AS g1
+FROM t2 AS grandparent1
+WHERE grandparent1.col_varchar_key IN
+(SELECT parent1.col_varchar_nokey AS p1
+FROM t2 AS parent1 LEFT JOIN t3 AS parent2 USING (pk)
+)
+AND grandparent1.col_varchar_key IS NOT NULL
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	parent1	ALL	NULL	NULL	NULL	NULL	20	Start temporary
+2	DEPENDENT SUBQUERY	grandparent1	ref	col_varchar_key	col_varchar_key	3	test.parent1.col_varchar_nokey	1	Using where
+2	DEPENDENT SUBQUERY	parent2	eq_ref	PRIMARY	PRIMARY	4	test.parent1.pk	1	Using index; End temporary
+SELECT *
+FROM t1
+WHERE g1 NOT IN
+(SELECT  grandparent1.col_varchar_nokey AS g1
+FROM t2 AS grandparent1
+WHERE grandparent1.col_varchar_key IN
+(SELECT parent1.col_varchar_nokey AS p1
+FROM t2 AS parent1 LEFT JOIN t3 AS parent2 USING (pk)
+)
+AND grandparent1.col_varchar_key IS NOT NULL
+);
+g1
+DROP TABLE t1, t2, t3;
+CREATE TABLE t1 (
+pk INTEGER AUTO_INCREMENT,
+col_int_key INTEGER ,
+col_varchar_key VARCHAR(1) ,
+col_varchar_nokey VARCHAR(1) ,
+PRIMARY KEY (pk),
+KEY (col_varchar_key,col_int_key)
+) ENGINE=INNODB;
+INSERT INTO t1 (col_int_key,col_varchar_key,col_varchar_nokey) VALUES
+(0,'x','x'), (1,'j','j'), (1,'r','r'), (9,'v','v'), (5,'r','r');
+CREATE TABLE t2 (
+pk INTEGER AUTO_INCREMENT,
+col_int_key INTEGER ,
+col_varchar_key VARCHAR(1) ,
+col_varchar_nokey VARCHAR(1) ,
+PRIMARY KEY (pk),
+KEY (col_int_key),
+KEY (col_varchar_key,col_int_key)
+) AUTO_INCREMENT=10 ENGINE=INNODB;
+INSERT INTO t2 (col_int_key, col_varchar_key, col_varchar_nokey) VALUES
+(NULL,'x','x'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t3
+SELECT outr.col_varchar_nokey AS x
+FROM t1 AS outr
+WHERE outr.col_varchar_nokey IN
+(SELECT innr.col_varchar_nokey AS y
+FROM t2 AS innr
+WHERE innr.col_int_key IS NULL)
+AND outr.col_varchar_nokey IS NOT NULL
+AND NOT col_varchar_key IS NULL;
+SELECT *
+FROM t3
+WHERE x NOT IN
+(SELECT outr.col_varchar_nokey AS x
+FROM t1 AS outr
+WHERE outr.col_varchar_nokey IN
+(SELECT innr.col_varchar_nokey AS y
+FROM t2 AS innr
+WHERE innr.col_int_key IS NULL)
+AND outr.col_varchar_nokey IS NOT NULL
+AND NOT col_varchar_key IS NULL);
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#12797534.
+# End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_dupsweed_bkaunique.result'
--- a/mysql-test/r/subquery_sj_dupsweed_bkaunique.result	2011-09-08 12:48:08 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bkaunique.result	2011-09-30 10:22:38 +0000
@@ -6670,5 +6670,105 @@ a
 1
 DROP TABLE t1, t2;
 # End of test for bug#12803439.
+#
+# Bug#12797534: Segfault in hp_movelink still exists
+#
+CREATE TABLE t1 (
+g1 VARCHAR(1) NOT NULL
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES ('d'), ('s');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,4,'j','j'), (2,6,'v','v'), (3,3,'c','c'), (4,5,'m','m'),
+(5,3,'d','d'), (6,246,'d','d'), (7,2,'y','y'), (8,9,'t','t'),
+(9,3,'d','d'), (10,8,'s','s'), (11,1,'r','r'), (12,8,'m','m'),
+(13,8,'b','b'), (14,5,'x','x'), (15,7,'g','g'), (16,5,'p','p'),
+(17,1,'q','q'), (18,6,'w','w'), (19,2,'d','d'), (20,9,'e','e');
+CREATE TABLE t3 (
+pk INTEGER NOT NULL,
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES (10);
+EXPLAIN SELECT *
+FROM t1
+WHERE g1 NOT IN
+(SELECT  grandparent1.col_varchar_nokey AS g1
+FROM t2 AS grandparent1
+WHERE grandparent1.col_varchar_key IN
+(SELECT parent1.col_varchar_nokey AS p1
+FROM t2 AS parent1 LEFT JOIN t3 AS parent2 USING (pk)
+)
+AND grandparent1.col_varchar_key IS NOT NULL
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	parent1	ALL	NULL	NULL	NULL	NULL	20	Start temporary
+2	DEPENDENT SUBQUERY	grandparent1	ref	col_varchar_key	col_varchar_key	3	test.parent1.col_varchar_nokey	1	Using where
+2	DEPENDENT SUBQUERY	parent2	eq_ref	PRIMARY	PRIMARY	4	test.parent1.pk	1	Using index; End temporary
+SELECT *
+FROM t1
+WHERE g1 NOT IN
+(SELECT  grandparent1.col_varchar_nokey AS g1
+FROM t2 AS grandparent1
+WHERE grandparent1.col_varchar_key IN
+(SELECT parent1.col_varchar_nokey AS p1
+FROM t2 AS parent1 LEFT JOIN t3 AS parent2 USING (pk)
+)
+AND grandparent1.col_varchar_key IS NOT NULL
+);
+g1
+DROP TABLE t1, t2, t3;
+CREATE TABLE t1 (
+pk INTEGER AUTO_INCREMENT,
+col_int_key INTEGER ,
+col_varchar_key VARCHAR(1) ,
+col_varchar_nokey VARCHAR(1) ,
+PRIMARY KEY (pk),
+KEY (col_varchar_key,col_int_key)
+) ENGINE=INNODB;
+INSERT INTO t1 (col_int_key,col_varchar_key,col_varchar_nokey) VALUES
+(0,'x','x'), (1,'j','j'), (1,'r','r'), (9,'v','v'), (5,'r','r');
+CREATE TABLE t2 (
+pk INTEGER AUTO_INCREMENT,
+col_int_key INTEGER ,
+col_varchar_key VARCHAR(1) ,
+col_varchar_nokey VARCHAR(1) ,
+PRIMARY KEY (pk),
+KEY (col_int_key),
+KEY (col_varchar_key,col_int_key)
+) AUTO_INCREMENT=10 ENGINE=INNODB;
+INSERT INTO t2 (col_int_key, col_varchar_key, col_varchar_nokey) VALUES
+(NULL,'x','x'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t3
+SELECT outr.col_varchar_nokey AS x
+FROM t1 AS outr
+WHERE outr.col_varchar_nokey IN
+(SELECT innr.col_varchar_nokey AS y
+FROM t2 AS innr
+WHERE innr.col_int_key IS NULL)
+AND outr.col_varchar_nokey IS NOT NULL
+AND NOT col_varchar_key IS NULL;
+SELECT *
+FROM t3
+WHERE x NOT IN
+(SELECT outr.col_varchar_nokey AS x
+FROM t1 AS outr
+WHERE outr.col_varchar_nokey IN
+(SELECT innr.col_varchar_nokey AS y
+FROM t2 AS innr
+WHERE innr.col_int_key IS NULL)
+AND outr.col_varchar_nokey IS NOT NULL
+AND NOT col_varchar_key IS NULL);
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#12797534.
+# End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_firstmatch.result'
--- a/mysql-test/r/subquery_sj_firstmatch.result	2011-09-08 12:48:08 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch.result	2011-09-30 10:22:38 +0000
@@ -6670,6 +6670,106 @@ a
 DROP TABLE t1, t2;
 # End of test for bug#12803439.
 #
+# Bug#12797534: Segfault in hp_movelink still exists
+#
+CREATE TABLE t1 (
+g1 VARCHAR(1) NOT NULL
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES ('d'), ('s');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,4,'j','j'), (2,6,'v','v'), (3,3,'c','c'), (4,5,'m','m'),
+(5,3,'d','d'), (6,246,'d','d'), (7,2,'y','y'), (8,9,'t','t'),
+(9,3,'d','d'), (10,8,'s','s'), (11,1,'r','r'), (12,8,'m','m'),
+(13,8,'b','b'), (14,5,'x','x'), (15,7,'g','g'), (16,5,'p','p'),
+(17,1,'q','q'), (18,6,'w','w'), (19,2,'d','d'), (20,9,'e','e');
+CREATE TABLE t3 (
+pk INTEGER NOT NULL,
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES (10);
+EXPLAIN SELECT *
+FROM t1
+WHERE g1 NOT IN
+(SELECT  grandparent1.col_varchar_nokey AS g1
+FROM t2 AS grandparent1
+WHERE grandparent1.col_varchar_key IN
+(SELECT parent1.col_varchar_nokey AS p1
+FROM t2 AS parent1 LEFT JOIN t3 AS parent2 USING (pk)
+)
+AND grandparent1.col_varchar_key IS NOT NULL
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	grandparent1	ALL	col_varchar_key	NULL	NULL	NULL	20	Using where
+2	DEPENDENT SUBQUERY	parent1	ALL	NULL	NULL	NULL	NULL	20	Using where
+2	DEPENDENT SUBQUERY	parent2	eq_ref	PRIMARY	PRIMARY	4	test.parent1.pk	1	Using index; FirstMatch(grandparent1)
+SELECT *
+FROM t1
+WHERE g1 NOT IN
+(SELECT  grandparent1.col_varchar_nokey AS g1
+FROM t2 AS grandparent1
+WHERE grandparent1.col_varchar_key IN
+(SELECT parent1.col_varchar_nokey AS p1
+FROM t2 AS parent1 LEFT JOIN t3 AS parent2 USING (pk)
+)
+AND grandparent1.col_varchar_key IS NOT NULL
+);
+g1
+DROP TABLE t1, t2, t3;
+CREATE TABLE t1 (
+pk INTEGER AUTO_INCREMENT,
+col_int_key INTEGER ,
+col_varchar_key VARCHAR(1) ,
+col_varchar_nokey VARCHAR(1) ,
+PRIMARY KEY (pk),
+KEY (col_varchar_key,col_int_key)
+) ENGINE=INNODB;
+INSERT INTO t1 (col_int_key,col_varchar_key,col_varchar_nokey) VALUES
+(0,'x','x'), (1,'j','j'), (1,'r','r'), (9,'v','v'), (5,'r','r');
+CREATE TABLE t2 (
+pk INTEGER AUTO_INCREMENT,
+col_int_key INTEGER ,
+col_varchar_key VARCHAR(1) ,
+col_varchar_nokey VARCHAR(1) ,
+PRIMARY KEY (pk),
+KEY (col_int_key),
+KEY (col_varchar_key,col_int_key)
+) AUTO_INCREMENT=10 ENGINE=INNODB;
+INSERT INTO t2 (col_int_key, col_varchar_key, col_varchar_nokey) VALUES
+(NULL,'x','x'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t3
+SELECT outr.col_varchar_nokey AS x
+FROM t1 AS outr
+WHERE outr.col_varchar_nokey IN
+(SELECT innr.col_varchar_nokey AS y
+FROM t2 AS innr
+WHERE innr.col_int_key IS NULL)
+AND outr.col_varchar_nokey IS NOT NULL
+AND NOT col_varchar_key IS NULL;
+SELECT *
+FROM t3
+WHERE x NOT IN
+(SELECT outr.col_varchar_nokey AS x
+FROM t1 AS outr
+WHERE outr.col_varchar_nokey IN
+(SELECT innr.col_varchar_nokey AS y
+FROM t2 AS innr
+WHERE innr.col_int_key IS NULL)
+AND outr.col_varchar_nokey IS NOT NULL
+AND NOT col_varchar_key IS NULL);
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#12797534.
+# End of 5.6 tests
+#
 # Bug#51457 Firstmatch semijoin strategy gives wrong results for
 #           certain query plans
 #

=== modified file 'mysql-test/r/subquery_sj_firstmatch_bka.result'
--- a/mysql-test/r/subquery_sj_firstmatch_bka.result	2011-09-08 12:48:08 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bka.result	2011-09-30 10:22:38 +0000
@@ -6671,6 +6671,106 @@ a
 DROP TABLE t1, t2;
 # End of test for bug#12803439.
 #
+# Bug#12797534: Segfault in hp_movelink still exists
+#
+CREATE TABLE t1 (
+g1 VARCHAR(1) NOT NULL
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES ('d'), ('s');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,4,'j','j'), (2,6,'v','v'), (3,3,'c','c'), (4,5,'m','m'),
+(5,3,'d','d'), (6,246,'d','d'), (7,2,'y','y'), (8,9,'t','t'),
+(9,3,'d','d'), (10,8,'s','s'), (11,1,'r','r'), (12,8,'m','m'),
+(13,8,'b','b'), (14,5,'x','x'), (15,7,'g','g'), (16,5,'p','p'),
+(17,1,'q','q'), (18,6,'w','w'), (19,2,'d','d'), (20,9,'e','e');
+CREATE TABLE t3 (
+pk INTEGER NOT NULL,
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES (10);
+EXPLAIN SELECT *
+FROM t1
+WHERE g1 NOT IN
+(SELECT  grandparent1.col_varchar_nokey AS g1
+FROM t2 AS grandparent1
+WHERE grandparent1.col_varchar_key IN
+(SELECT parent1.col_varchar_nokey AS p1
+FROM t2 AS parent1 LEFT JOIN t3 AS parent2 USING (pk)
+)
+AND grandparent1.col_varchar_key IS NOT NULL
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	grandparent1	ALL	col_varchar_key	NULL	NULL	NULL	20	Using where
+2	DEPENDENT SUBQUERY	parent1	ALL	NULL	NULL	NULL	NULL	20	Using where
+2	DEPENDENT SUBQUERY	parent2	eq_ref	PRIMARY	PRIMARY	4	test.parent1.pk	1	Using index; FirstMatch(grandparent1)
+SELECT *
+FROM t1
+WHERE g1 NOT IN
+(SELECT  grandparent1.col_varchar_nokey AS g1
+FROM t2 AS grandparent1
+WHERE grandparent1.col_varchar_key IN
+(SELECT parent1.col_varchar_nokey AS p1
+FROM t2 AS parent1 LEFT JOIN t3 AS parent2 USING (pk)
+)
+AND grandparent1.col_varchar_key IS NOT NULL
+);
+g1
+DROP TABLE t1, t2, t3;
+CREATE TABLE t1 (
+pk INTEGER AUTO_INCREMENT,
+col_int_key INTEGER ,
+col_varchar_key VARCHAR(1) ,
+col_varchar_nokey VARCHAR(1) ,
+PRIMARY KEY (pk),
+KEY (col_varchar_key,col_int_key)
+) ENGINE=INNODB;
+INSERT INTO t1 (col_int_key,col_varchar_key,col_varchar_nokey) VALUES
+(0,'x','x'), (1,'j','j'), (1,'r','r'), (9,'v','v'), (5,'r','r');
+CREATE TABLE t2 (
+pk INTEGER AUTO_INCREMENT,
+col_int_key INTEGER ,
+col_varchar_key VARCHAR(1) ,
+col_varchar_nokey VARCHAR(1) ,
+PRIMARY KEY (pk),
+KEY (col_int_key),
+KEY (col_varchar_key,col_int_key)
+) AUTO_INCREMENT=10 ENGINE=INNODB;
+INSERT INTO t2 (col_int_key, col_varchar_key, col_varchar_nokey) VALUES
+(NULL,'x','x'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t3
+SELECT outr.col_varchar_nokey AS x
+FROM t1 AS outr
+WHERE outr.col_varchar_nokey IN
+(SELECT innr.col_varchar_nokey AS y
+FROM t2 AS innr
+WHERE innr.col_int_key IS NULL)
+AND outr.col_varchar_nokey IS NOT NULL
+AND NOT col_varchar_key IS NULL;
+SELECT *
+FROM t3
+WHERE x NOT IN
+(SELECT outr.col_varchar_nokey AS x
+FROM t1 AS outr
+WHERE outr.col_varchar_nokey IN
+(SELECT innr.col_varchar_nokey AS y
+FROM t2 AS innr
+WHERE innr.col_int_key IS NULL)
+AND outr.col_varchar_nokey IS NOT NULL
+AND NOT col_varchar_key IS NULL);
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#12797534.
+# End of 5.6 tests
+#
 # Bug#51457 Firstmatch semijoin strategy gives wrong results for
 #           certain query plans
 #

=== modified file 'mysql-test/r/subquery_sj_firstmatch_bka_nobnl.result'
--- a/mysql-test/r/subquery_sj_firstmatch_bka_nobnl.result	2011-09-08 12:48:08 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bka_nobnl.result	2011-09-30 10:22:38 +0000
@@ -6671,6 +6671,106 @@ a
 DROP TABLE t1, t2;
 # End of test for bug#12803439.
 #
+# Bug#12797534: Segfault in hp_movelink still exists
+#
+CREATE TABLE t1 (
+g1 VARCHAR(1) NOT NULL
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES ('d'), ('s');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,4,'j','j'), (2,6,'v','v'), (3,3,'c','c'), (4,5,'m','m'),
+(5,3,'d','d'), (6,246,'d','d'), (7,2,'y','y'), (8,9,'t','t'),
+(9,3,'d','d'), (10,8,'s','s'), (11,1,'r','r'), (12,8,'m','m'),
+(13,8,'b','b'), (14,5,'x','x'), (15,7,'g','g'), (16,5,'p','p'),
+(17,1,'q','q'), (18,6,'w','w'), (19,2,'d','d'), (20,9,'e','e');
+CREATE TABLE t3 (
+pk INTEGER NOT NULL,
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES (10);
+EXPLAIN SELECT *
+FROM t1
+WHERE g1 NOT IN
+(SELECT  grandparent1.col_varchar_nokey AS g1
+FROM t2 AS grandparent1
+WHERE grandparent1.col_varchar_key IN
+(SELECT parent1.col_varchar_nokey AS p1
+FROM t2 AS parent1 LEFT JOIN t3 AS parent2 USING (pk)
+)
+AND grandparent1.col_varchar_key IS NOT NULL
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	grandparent1	ALL	col_varchar_key	NULL	NULL	NULL	20	Using where
+2	DEPENDENT SUBQUERY	parent1	ALL	NULL	NULL	NULL	NULL	20	Using where
+2	DEPENDENT SUBQUERY	parent2	eq_ref	PRIMARY	PRIMARY	4	test.parent1.pk	1	Using index; FirstMatch(grandparent1)
+SELECT *
+FROM t1
+WHERE g1 NOT IN
+(SELECT  grandparent1.col_varchar_nokey AS g1
+FROM t2 AS grandparent1
+WHERE grandparent1.col_varchar_key IN
+(SELECT parent1.col_varchar_nokey AS p1
+FROM t2 AS parent1 LEFT JOIN t3 AS parent2 USING (pk)
+)
+AND grandparent1.col_varchar_key IS NOT NULL
+);
+g1
+DROP TABLE t1, t2, t3;
+CREATE TABLE t1 (
+pk INTEGER AUTO_INCREMENT,
+col_int_key INTEGER ,
+col_varchar_key VARCHAR(1) ,
+col_varchar_nokey VARCHAR(1) ,
+PRIMARY KEY (pk),
+KEY (col_varchar_key,col_int_key)
+) ENGINE=INNODB;
+INSERT INTO t1 (col_int_key,col_varchar_key,col_varchar_nokey) VALUES
+(0,'x','x'), (1,'j','j'), (1,'r','r'), (9,'v','v'), (5,'r','r');
+CREATE TABLE t2 (
+pk INTEGER AUTO_INCREMENT,
+col_int_key INTEGER ,
+col_varchar_key VARCHAR(1) ,
+col_varchar_nokey VARCHAR(1) ,
+PRIMARY KEY (pk),
+KEY (col_int_key),
+KEY (col_varchar_key,col_int_key)
+) AUTO_INCREMENT=10 ENGINE=INNODB;
+INSERT INTO t2 (col_int_key, col_varchar_key, col_varchar_nokey) VALUES
+(NULL,'x','x'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t3
+SELECT outr.col_varchar_nokey AS x
+FROM t1 AS outr
+WHERE outr.col_varchar_nokey IN
+(SELECT innr.col_varchar_nokey AS y
+FROM t2 AS innr
+WHERE innr.col_int_key IS NULL)
+AND outr.col_varchar_nokey IS NOT NULL
+AND NOT col_varchar_key IS NULL;
+SELECT *
+FROM t3
+WHERE x NOT IN
+(SELECT outr.col_varchar_nokey AS x
+FROM t1 AS outr
+WHERE outr.col_varchar_nokey IN
+(SELECT innr.col_varchar_nokey AS y
+FROM t2 AS innr
+WHERE innr.col_int_key IS NULL)
+AND outr.col_varchar_nokey IS NOT NULL
+AND NOT col_varchar_key IS NULL);
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#12797534.
+# End of 5.6 tests
+#
 # Bug#51457 Firstmatch semijoin strategy gives wrong results for
 #           certain query plans
 #

=== modified file 'mysql-test/r/subquery_sj_firstmatch_bkaunique.result'
--- a/mysql-test/r/subquery_sj_firstmatch_bkaunique.result	2011-09-08 12:48:08 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bkaunique.result	2011-09-30 10:22:38 +0000
@@ -6672,6 +6672,106 @@ a
 DROP TABLE t1, t2;
 # End of test for bug#12803439.
 #
+# Bug#12797534: Segfault in hp_movelink still exists
+#
+CREATE TABLE t1 (
+g1 VARCHAR(1) NOT NULL
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES ('d'), ('s');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,4,'j','j'), (2,6,'v','v'), (3,3,'c','c'), (4,5,'m','m'),
+(5,3,'d','d'), (6,246,'d','d'), (7,2,'y','y'), (8,9,'t','t'),
+(9,3,'d','d'), (10,8,'s','s'), (11,1,'r','r'), (12,8,'m','m'),
+(13,8,'b','b'), (14,5,'x','x'), (15,7,'g','g'), (16,5,'p','p'),
+(17,1,'q','q'), (18,6,'w','w'), (19,2,'d','d'), (20,9,'e','e');
+CREATE TABLE t3 (
+pk INTEGER NOT NULL,
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES (10);
+EXPLAIN SELECT *
+FROM t1
+WHERE g1 NOT IN
+(SELECT  grandparent1.col_varchar_nokey AS g1
+FROM t2 AS grandparent1
+WHERE grandparent1.col_varchar_key IN
+(SELECT parent1.col_varchar_nokey AS p1
+FROM t2 AS parent1 LEFT JOIN t3 AS parent2 USING (pk)
+)
+AND grandparent1.col_varchar_key IS NOT NULL
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	grandparent1	ALL	col_varchar_key	NULL	NULL	NULL	20	Using where
+2	DEPENDENT SUBQUERY	parent1	ALL	NULL	NULL	NULL	NULL	20	Using where
+2	DEPENDENT SUBQUERY	parent2	eq_ref	PRIMARY	PRIMARY	4	test.parent1.pk	1	Using index; FirstMatch(grandparent1)
+SELECT *
+FROM t1
+WHERE g1 NOT IN
+(SELECT  grandparent1.col_varchar_nokey AS g1
+FROM t2 AS grandparent1
+WHERE grandparent1.col_varchar_key IN
+(SELECT parent1.col_varchar_nokey AS p1
+FROM t2 AS parent1 LEFT JOIN t3 AS parent2 USING (pk)
+)
+AND grandparent1.col_varchar_key IS NOT NULL
+);
+g1
+DROP TABLE t1, t2, t3;
+CREATE TABLE t1 (
+pk INTEGER AUTO_INCREMENT,
+col_int_key INTEGER ,
+col_varchar_key VARCHAR(1) ,
+col_varchar_nokey VARCHAR(1) ,
+PRIMARY KEY (pk),
+KEY (col_varchar_key,col_int_key)
+) ENGINE=INNODB;
+INSERT INTO t1 (col_int_key,col_varchar_key,col_varchar_nokey) VALUES
+(0,'x','x'), (1,'j','j'), (1,'r','r'), (9,'v','v'), (5,'r','r');
+CREATE TABLE t2 (
+pk INTEGER AUTO_INCREMENT,
+col_int_key INTEGER ,
+col_varchar_key VARCHAR(1) ,
+col_varchar_nokey VARCHAR(1) ,
+PRIMARY KEY (pk),
+KEY (col_int_key),
+KEY (col_varchar_key,col_int_key)
+) AUTO_INCREMENT=10 ENGINE=INNODB;
+INSERT INTO t2 (col_int_key, col_varchar_key, col_varchar_nokey) VALUES
+(NULL,'x','x'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t3
+SELECT outr.col_varchar_nokey AS x
+FROM t1 AS outr
+WHERE outr.col_varchar_nokey IN
+(SELECT innr.col_varchar_nokey AS y
+FROM t2 AS innr
+WHERE innr.col_int_key IS NULL)
+AND outr.col_varchar_nokey IS NOT NULL
+AND NOT col_varchar_key IS NULL;
+SELECT *
+FROM t3
+WHERE x NOT IN
+(SELECT outr.col_varchar_nokey AS x
+FROM t1 AS outr
+WHERE outr.col_varchar_nokey IN
+(SELECT innr.col_varchar_nokey AS y
+FROM t2 AS innr
+WHERE innr.col_int_key IS NULL)
+AND outr.col_varchar_nokey IS NOT NULL
+AND NOT col_varchar_key IS NULL);
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#12797534.
+# End of 5.6 tests
+#
 # 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-09-08 12:48:08 +0000
+++ b/mysql-test/r/subquery_sj_loosescan.result	2011-09-30 10:22:38 +0000
@@ -6669,4 +6669,104 @@ a
 1
 DROP TABLE t1, t2;
 # End of test for bug#12803439.
+#
+# Bug#12797534: Segfault in hp_movelink still exists
+#
+CREATE TABLE t1 (
+g1 VARCHAR(1) NOT NULL
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES ('d'), ('s');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,4,'j','j'), (2,6,'v','v'), (3,3,'c','c'), (4,5,'m','m'),
+(5,3,'d','d'), (6,246,'d','d'), (7,2,'y','y'), (8,9,'t','t'),
+(9,3,'d','d'), (10,8,'s','s'), (11,1,'r','r'), (12,8,'m','m'),
+(13,8,'b','b'), (14,5,'x','x'), (15,7,'g','g'), (16,5,'p','p'),
+(17,1,'q','q'), (18,6,'w','w'), (19,2,'d','d'), (20,9,'e','e');
+CREATE TABLE t3 (
+pk INTEGER NOT NULL,
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES (10);
+EXPLAIN SELECT *
+FROM t1
+WHERE g1 NOT IN
+(SELECT  grandparent1.col_varchar_nokey AS g1
+FROM t2 AS grandparent1
+WHERE grandparent1.col_varchar_key IN
+(SELECT parent1.col_varchar_nokey AS p1
+FROM t2 AS parent1 LEFT JOIN t3 AS parent2 USING (pk)
+)
+AND grandparent1.col_varchar_key IS NOT NULL
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	parent1	ALL	NULL	NULL	NULL	NULL	20	Start temporary
+2	DEPENDENT SUBQUERY	grandparent1	ref	col_varchar_key	col_varchar_key	3	test.parent1.col_varchar_nokey	1	Using where
+2	DEPENDENT SUBQUERY	parent2	eq_ref	PRIMARY	PRIMARY	4	test.parent1.pk	1	Using index; End temporary
+SELECT *
+FROM t1
+WHERE g1 NOT IN
+(SELECT  grandparent1.col_varchar_nokey AS g1
+FROM t2 AS grandparent1
+WHERE grandparent1.col_varchar_key IN
+(SELECT parent1.col_varchar_nokey AS p1
+FROM t2 AS parent1 LEFT JOIN t3 AS parent2 USING (pk)
+)
+AND grandparent1.col_varchar_key IS NOT NULL
+);
+g1
+DROP TABLE t1, t2, t3;
+CREATE TABLE t1 (
+pk INTEGER AUTO_INCREMENT,
+col_int_key INTEGER ,
+col_varchar_key VARCHAR(1) ,
+col_varchar_nokey VARCHAR(1) ,
+PRIMARY KEY (pk),
+KEY (col_varchar_key,col_int_key)
+) ENGINE=INNODB;
+INSERT INTO t1 (col_int_key,col_varchar_key,col_varchar_nokey) VALUES
+(0,'x','x'), (1,'j','j'), (1,'r','r'), (9,'v','v'), (5,'r','r');
+CREATE TABLE t2 (
+pk INTEGER AUTO_INCREMENT,
+col_int_key INTEGER ,
+col_varchar_key VARCHAR(1) ,
+col_varchar_nokey VARCHAR(1) ,
+PRIMARY KEY (pk),
+KEY (col_int_key),
+KEY (col_varchar_key,col_int_key)
+) AUTO_INCREMENT=10 ENGINE=INNODB;
+INSERT INTO t2 (col_int_key, col_varchar_key, col_varchar_nokey) VALUES
+(NULL,'x','x'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t3
+SELECT outr.col_varchar_nokey AS x
+FROM t1 AS outr
+WHERE outr.col_varchar_nokey IN
+(SELECT innr.col_varchar_nokey AS y
+FROM t2 AS innr
+WHERE innr.col_int_key IS NULL)
+AND outr.col_varchar_nokey IS NOT NULL
+AND NOT col_varchar_key IS NULL;
+SELECT *
+FROM t3
+WHERE x NOT IN
+(SELECT outr.col_varchar_nokey AS x
+FROM t1 AS outr
+WHERE outr.col_varchar_nokey IN
+(SELECT innr.col_varchar_nokey AS y
+FROM t2 AS innr
+WHERE innr.col_int_key IS NULL)
+AND outr.col_varchar_nokey IS NOT NULL
+AND NOT col_varchar_key IS NULL);
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#12797534.
+# End of 5.6 tests
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_loosescan_bka.result'
--- a/mysql-test/r/subquery_sj_loosescan_bka.result	2011-09-08 12:48:08 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bka.result	2011-09-30 10:22:38 +0000
@@ -6670,5 +6670,105 @@ a
 1
 DROP TABLE t1, t2;
 # End of test for bug#12803439.
+#
+# Bug#12797534: Segfault in hp_movelink still exists
+#
+CREATE TABLE t1 (
+g1 VARCHAR(1) NOT NULL
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES ('d'), ('s');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,4,'j','j'), (2,6,'v','v'), (3,3,'c','c'), (4,5,'m','m'),
+(5,3,'d','d'), (6,246,'d','d'), (7,2,'y','y'), (8,9,'t','t'),
+(9,3,'d','d'), (10,8,'s','s'), (11,1,'r','r'), (12,8,'m','m'),
+(13,8,'b','b'), (14,5,'x','x'), (15,7,'g','g'), (16,5,'p','p'),
+(17,1,'q','q'), (18,6,'w','w'), (19,2,'d','d'), (20,9,'e','e');
+CREATE TABLE t3 (
+pk INTEGER NOT NULL,
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES (10);
+EXPLAIN SELECT *
+FROM t1
+WHERE g1 NOT IN
+(SELECT  grandparent1.col_varchar_nokey AS g1
+FROM t2 AS grandparent1
+WHERE grandparent1.col_varchar_key IN
+(SELECT parent1.col_varchar_nokey AS p1
+FROM t2 AS parent1 LEFT JOIN t3 AS parent2 USING (pk)
+)
+AND grandparent1.col_varchar_key IS NOT NULL
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	parent1	ALL	NULL	NULL	NULL	NULL	20	Start temporary
+2	DEPENDENT SUBQUERY	grandparent1	ref	col_varchar_key	col_varchar_key	3	test.parent1.col_varchar_nokey	1	Using where
+2	DEPENDENT SUBQUERY	parent2	eq_ref	PRIMARY	PRIMARY	4	test.parent1.pk	1	Using index; End temporary
+SELECT *
+FROM t1
+WHERE g1 NOT IN
+(SELECT  grandparent1.col_varchar_nokey AS g1
+FROM t2 AS grandparent1
+WHERE grandparent1.col_varchar_key IN
+(SELECT parent1.col_varchar_nokey AS p1
+FROM t2 AS parent1 LEFT JOIN t3 AS parent2 USING (pk)
+)
+AND grandparent1.col_varchar_key IS NOT NULL
+);
+g1
+DROP TABLE t1, t2, t3;
+CREATE TABLE t1 (
+pk INTEGER AUTO_INCREMENT,
+col_int_key INTEGER ,
+col_varchar_key VARCHAR(1) ,
+col_varchar_nokey VARCHAR(1) ,
+PRIMARY KEY (pk),
+KEY (col_varchar_key,col_int_key)
+) ENGINE=INNODB;
+INSERT INTO t1 (col_int_key,col_varchar_key,col_varchar_nokey) VALUES
+(0,'x','x'), (1,'j','j'), (1,'r','r'), (9,'v','v'), (5,'r','r');
+CREATE TABLE t2 (
+pk INTEGER AUTO_INCREMENT,
+col_int_key INTEGER ,
+col_varchar_key VARCHAR(1) ,
+col_varchar_nokey VARCHAR(1) ,
+PRIMARY KEY (pk),
+KEY (col_int_key),
+KEY (col_varchar_key,col_int_key)
+) AUTO_INCREMENT=10 ENGINE=INNODB;
+INSERT INTO t2 (col_int_key, col_varchar_key, col_varchar_nokey) VALUES
+(NULL,'x','x'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t3
+SELECT outr.col_varchar_nokey AS x
+FROM t1 AS outr
+WHERE outr.col_varchar_nokey IN
+(SELECT innr.col_varchar_nokey AS y
+FROM t2 AS innr
+WHERE innr.col_int_key IS NULL)
+AND outr.col_varchar_nokey IS NOT NULL
+AND NOT col_varchar_key IS NULL;
+SELECT *
+FROM t3
+WHERE x NOT IN
+(SELECT outr.col_varchar_nokey AS x
+FROM t1 AS outr
+WHERE outr.col_varchar_nokey IN
+(SELECT innr.col_varchar_nokey AS y
+FROM t2 AS innr
+WHERE innr.col_int_key IS NULL)
+AND outr.col_varchar_nokey IS NOT NULL
+AND NOT col_varchar_key IS NULL);
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#12797534.
+# End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_loosescan_bka_nobnl.result'
--- a/mysql-test/r/subquery_sj_loosescan_bka_nobnl.result	2011-09-08 12:48:08 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bka_nobnl.result	2011-09-30 10:22:38 +0000
@@ -6670,5 +6670,105 @@ a
 1
 DROP TABLE t1, t2;
 # End of test for bug#12803439.
+#
+# Bug#12797534: Segfault in hp_movelink still exists
+#
+CREATE TABLE t1 (
+g1 VARCHAR(1) NOT NULL
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES ('d'), ('s');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,4,'j','j'), (2,6,'v','v'), (3,3,'c','c'), (4,5,'m','m'),
+(5,3,'d','d'), (6,246,'d','d'), (7,2,'y','y'), (8,9,'t','t'),
+(9,3,'d','d'), (10,8,'s','s'), (11,1,'r','r'), (12,8,'m','m'),
+(13,8,'b','b'), (14,5,'x','x'), (15,7,'g','g'), (16,5,'p','p'),
+(17,1,'q','q'), (18,6,'w','w'), (19,2,'d','d'), (20,9,'e','e');
+CREATE TABLE t3 (
+pk INTEGER NOT NULL,
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES (10);
+EXPLAIN SELECT *
+FROM t1
+WHERE g1 NOT IN
+(SELECT  grandparent1.col_varchar_nokey AS g1
+FROM t2 AS grandparent1
+WHERE grandparent1.col_varchar_key IN
+(SELECT parent1.col_varchar_nokey AS p1
+FROM t2 AS parent1 LEFT JOIN t3 AS parent2 USING (pk)
+)
+AND grandparent1.col_varchar_key IS NOT NULL
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	parent1	ALL	NULL	NULL	NULL	NULL	20	Start temporary
+2	DEPENDENT SUBQUERY	grandparent1	ref	col_varchar_key	col_varchar_key	3	test.parent1.col_varchar_nokey	1	Using where
+2	DEPENDENT SUBQUERY	parent2	eq_ref	PRIMARY	PRIMARY	4	test.parent1.pk	1	Using index; End temporary
+SELECT *
+FROM t1
+WHERE g1 NOT IN
+(SELECT  grandparent1.col_varchar_nokey AS g1
+FROM t2 AS grandparent1
+WHERE grandparent1.col_varchar_key IN
+(SELECT parent1.col_varchar_nokey AS p1
+FROM t2 AS parent1 LEFT JOIN t3 AS parent2 USING (pk)
+)
+AND grandparent1.col_varchar_key IS NOT NULL
+);
+g1
+DROP TABLE t1, t2, t3;
+CREATE TABLE t1 (
+pk INTEGER AUTO_INCREMENT,
+col_int_key INTEGER ,
+col_varchar_key VARCHAR(1) ,
+col_varchar_nokey VARCHAR(1) ,
+PRIMARY KEY (pk),
+KEY (col_varchar_key,col_int_key)
+) ENGINE=INNODB;
+INSERT INTO t1 (col_int_key,col_varchar_key,col_varchar_nokey) VALUES
+(0,'x','x'), (1,'j','j'), (1,'r','r'), (9,'v','v'), (5,'r','r');
+CREATE TABLE t2 (
+pk INTEGER AUTO_INCREMENT,
+col_int_key INTEGER ,
+col_varchar_key VARCHAR(1) ,
+col_varchar_nokey VARCHAR(1) ,
+PRIMARY KEY (pk),
+KEY (col_int_key),
+KEY (col_varchar_key,col_int_key)
+) AUTO_INCREMENT=10 ENGINE=INNODB;
+INSERT INTO t2 (col_int_key, col_varchar_key, col_varchar_nokey) VALUES
+(NULL,'x','x'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t3
+SELECT outr.col_varchar_nokey AS x
+FROM t1 AS outr
+WHERE outr.col_varchar_nokey IN
+(SELECT innr.col_varchar_nokey AS y
+FROM t2 AS innr
+WHERE innr.col_int_key IS NULL)
+AND outr.col_varchar_nokey IS NOT NULL
+AND NOT col_varchar_key IS NULL;
+SELECT *
+FROM t3
+WHERE x NOT IN
+(SELECT outr.col_varchar_nokey AS x
+FROM t1 AS outr
+WHERE outr.col_varchar_nokey IN
+(SELECT innr.col_varchar_nokey AS y
+FROM t2 AS innr
+WHERE innr.col_int_key IS NULL)
+AND outr.col_varchar_nokey IS NOT NULL
+AND NOT col_varchar_key IS NULL);
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#12797534.
+# End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_loosescan_bkaunique.result'
--- a/mysql-test/r/subquery_sj_loosescan_bkaunique.result	2011-09-08 12:48:08 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bkaunique.result	2011-09-30 10:22:38 +0000
@@ -6671,5 +6671,105 @@ a
 1
 DROP TABLE t1, t2;
 # End of test for bug#12803439.
+#
+# Bug#12797534: Segfault in hp_movelink still exists
+#
+CREATE TABLE t1 (
+g1 VARCHAR(1) NOT NULL
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES ('d'), ('s');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,4,'j','j'), (2,6,'v','v'), (3,3,'c','c'), (4,5,'m','m'),
+(5,3,'d','d'), (6,246,'d','d'), (7,2,'y','y'), (8,9,'t','t'),
+(9,3,'d','d'), (10,8,'s','s'), (11,1,'r','r'), (12,8,'m','m'),
+(13,8,'b','b'), (14,5,'x','x'), (15,7,'g','g'), (16,5,'p','p'),
+(17,1,'q','q'), (18,6,'w','w'), (19,2,'d','d'), (20,9,'e','e');
+CREATE TABLE t3 (
+pk INTEGER NOT NULL,
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES (10);
+EXPLAIN SELECT *
+FROM t1
+WHERE g1 NOT IN
+(SELECT  grandparent1.col_varchar_nokey AS g1
+FROM t2 AS grandparent1
+WHERE grandparent1.col_varchar_key IN
+(SELECT parent1.col_varchar_nokey AS p1
+FROM t2 AS parent1 LEFT JOIN t3 AS parent2 USING (pk)
+)
+AND grandparent1.col_varchar_key IS NOT NULL
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	parent1	ALL	NULL	NULL	NULL	NULL	20	Start temporary
+2	DEPENDENT SUBQUERY	grandparent1	ref	col_varchar_key	col_varchar_key	3	test.parent1.col_varchar_nokey	1	Using where
+2	DEPENDENT SUBQUERY	parent2	eq_ref	PRIMARY	PRIMARY	4	test.parent1.pk	1	Using index; End temporary
+SELECT *
+FROM t1
+WHERE g1 NOT IN
+(SELECT  grandparent1.col_varchar_nokey AS g1
+FROM t2 AS grandparent1
+WHERE grandparent1.col_varchar_key IN
+(SELECT parent1.col_varchar_nokey AS p1
+FROM t2 AS parent1 LEFT JOIN t3 AS parent2 USING (pk)
+)
+AND grandparent1.col_varchar_key IS NOT NULL
+);
+g1
+DROP TABLE t1, t2, t3;
+CREATE TABLE t1 (
+pk INTEGER AUTO_INCREMENT,
+col_int_key INTEGER ,
+col_varchar_key VARCHAR(1) ,
+col_varchar_nokey VARCHAR(1) ,
+PRIMARY KEY (pk),
+KEY (col_varchar_key,col_int_key)
+) ENGINE=INNODB;
+INSERT INTO t1 (col_int_key,col_varchar_key,col_varchar_nokey) VALUES
+(0,'x','x'), (1,'j','j'), (1,'r','r'), (9,'v','v'), (5,'r','r');
+CREATE TABLE t2 (
+pk INTEGER AUTO_INCREMENT,
+col_int_key INTEGER ,
+col_varchar_key VARCHAR(1) ,
+col_varchar_nokey VARCHAR(1) ,
+PRIMARY KEY (pk),
+KEY (col_int_key),
+KEY (col_varchar_key,col_int_key)
+) AUTO_INCREMENT=10 ENGINE=INNODB;
+INSERT INTO t2 (col_int_key, col_varchar_key, col_varchar_nokey) VALUES
+(NULL,'x','x'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t3
+SELECT outr.col_varchar_nokey AS x
+FROM t1 AS outr
+WHERE outr.col_varchar_nokey IN
+(SELECT innr.col_varchar_nokey AS y
+FROM t2 AS innr
+WHERE innr.col_int_key IS NULL)
+AND outr.col_varchar_nokey IS NOT NULL
+AND NOT col_varchar_key IS NULL;
+SELECT *
+FROM t3
+WHERE x NOT IN
+(SELECT outr.col_varchar_nokey AS x
+FROM t1 AS outr
+WHERE outr.col_varchar_nokey IN
+(SELECT innr.col_varchar_nokey AS y
+FROM t2 AS innr
+WHERE innr.col_int_key IS NULL)
+AND outr.col_varchar_nokey IS NOT NULL
+AND NOT col_varchar_key IS NULL);
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#12797534.
+# End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_mat.result'
--- a/mysql-test/r/subquery_sj_mat.result	2011-09-08 12:48:08 +0000
+++ b/mysql-test/r/subquery_sj_mat.result	2011-09-30 10:22:38 +0000
@@ -6668,4 +6668,104 @@ a
 1
 DROP TABLE t1, t2;
 # End of test for bug#12803439.
+#
+# Bug#12797534: Segfault in hp_movelink still exists
+#
+CREATE TABLE t1 (
+g1 VARCHAR(1) NOT NULL
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES ('d'), ('s');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,4,'j','j'), (2,6,'v','v'), (3,3,'c','c'), (4,5,'m','m'),
+(5,3,'d','d'), (6,246,'d','d'), (7,2,'y','y'), (8,9,'t','t'),
+(9,3,'d','d'), (10,8,'s','s'), (11,1,'r','r'), (12,8,'m','m'),
+(13,8,'b','b'), (14,5,'x','x'), (15,7,'g','g'), (16,5,'p','p'),
+(17,1,'q','q'), (18,6,'w','w'), (19,2,'d','d'), (20,9,'e','e');
+CREATE TABLE t3 (
+pk INTEGER NOT NULL,
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES (10);
+EXPLAIN SELECT *
+FROM t1
+WHERE g1 NOT IN
+(SELECT  grandparent1.col_varchar_nokey AS g1
+FROM t2 AS grandparent1
+WHERE grandparent1.col_varchar_key IN
+(SELECT parent1.col_varchar_nokey AS p1
+FROM t2 AS parent1 LEFT JOIN t3 AS parent2 USING (pk)
+)
+AND grandparent1.col_varchar_key IS NOT NULL
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	parent1	ALL	NULL	NULL	NULL	NULL	20	Start materialize; Scan
+2	DEPENDENT SUBQUERY	parent2	eq_ref	PRIMARY	PRIMARY	4	test.parent1.pk	1	Using index; End materialize
+2	DEPENDENT SUBQUERY	grandparent1	ref	col_varchar_key	col_varchar_key	3	test.parent1.col_varchar_nokey	1	Using where
+SELECT *
+FROM t1
+WHERE g1 NOT IN
+(SELECT  grandparent1.col_varchar_nokey AS g1
+FROM t2 AS grandparent1
+WHERE grandparent1.col_varchar_key IN
+(SELECT parent1.col_varchar_nokey AS p1
+FROM t2 AS parent1 LEFT JOIN t3 AS parent2 USING (pk)
+)
+AND grandparent1.col_varchar_key IS NOT NULL
+);
+g1
+DROP TABLE t1, t2, t3;
+CREATE TABLE t1 (
+pk INTEGER AUTO_INCREMENT,
+col_int_key INTEGER ,
+col_varchar_key VARCHAR(1) ,
+col_varchar_nokey VARCHAR(1) ,
+PRIMARY KEY (pk),
+KEY (col_varchar_key,col_int_key)
+) ENGINE=INNODB;
+INSERT INTO t1 (col_int_key,col_varchar_key,col_varchar_nokey) VALUES
+(0,'x','x'), (1,'j','j'), (1,'r','r'), (9,'v','v'), (5,'r','r');
+CREATE TABLE t2 (
+pk INTEGER AUTO_INCREMENT,
+col_int_key INTEGER ,
+col_varchar_key VARCHAR(1) ,
+col_varchar_nokey VARCHAR(1) ,
+PRIMARY KEY (pk),
+KEY (col_int_key),
+KEY (col_varchar_key,col_int_key)
+) AUTO_INCREMENT=10 ENGINE=INNODB;
+INSERT INTO t2 (col_int_key, col_varchar_key, col_varchar_nokey) VALUES
+(NULL,'x','x'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t3
+SELECT outr.col_varchar_nokey AS x
+FROM t1 AS outr
+WHERE outr.col_varchar_nokey IN
+(SELECT innr.col_varchar_nokey AS y
+FROM t2 AS innr
+WHERE innr.col_int_key IS NULL)
+AND outr.col_varchar_nokey IS NOT NULL
+AND NOT col_varchar_key IS NULL;
+SELECT *
+FROM t3
+WHERE x NOT IN
+(SELECT outr.col_varchar_nokey AS x
+FROM t1 AS outr
+WHERE outr.col_varchar_nokey IN
+(SELECT innr.col_varchar_nokey AS y
+FROM t2 AS innr
+WHERE innr.col_int_key IS NULL)
+AND outr.col_varchar_nokey IS NOT NULL
+AND NOT col_varchar_key IS NULL);
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#12797534.
+# End of 5.6 tests
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_mat_bka.result'
--- a/mysql-test/r/subquery_sj_mat_bka.result	2011-09-08 12:48:08 +0000
+++ b/mysql-test/r/subquery_sj_mat_bka.result	2011-09-30 10:22:38 +0000
@@ -6669,5 +6669,105 @@ a
 1
 DROP TABLE t1, t2;
 # End of test for bug#12803439.
+#
+# Bug#12797534: Segfault in hp_movelink still exists
+#
+CREATE TABLE t1 (
+g1 VARCHAR(1) NOT NULL
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES ('d'), ('s');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,4,'j','j'), (2,6,'v','v'), (3,3,'c','c'), (4,5,'m','m'),
+(5,3,'d','d'), (6,246,'d','d'), (7,2,'y','y'), (8,9,'t','t'),
+(9,3,'d','d'), (10,8,'s','s'), (11,1,'r','r'), (12,8,'m','m'),
+(13,8,'b','b'), (14,5,'x','x'), (15,7,'g','g'), (16,5,'p','p'),
+(17,1,'q','q'), (18,6,'w','w'), (19,2,'d','d'), (20,9,'e','e');
+CREATE TABLE t3 (
+pk INTEGER NOT NULL,
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES (10);
+EXPLAIN SELECT *
+FROM t1
+WHERE g1 NOT IN
+(SELECT  grandparent1.col_varchar_nokey AS g1
+FROM t2 AS grandparent1
+WHERE grandparent1.col_varchar_key IN
+(SELECT parent1.col_varchar_nokey AS p1
+FROM t2 AS parent1 LEFT JOIN t3 AS parent2 USING (pk)
+)
+AND grandparent1.col_varchar_key IS NOT NULL
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	parent1	ALL	NULL	NULL	NULL	NULL	20	Start materialize; Scan
+2	DEPENDENT SUBQUERY	parent2	eq_ref	PRIMARY	PRIMARY	4	test.parent1.pk	1	Using index; End materialize
+2	DEPENDENT SUBQUERY	grandparent1	ref	col_varchar_key	col_varchar_key	3	test.parent1.col_varchar_nokey	1	Using where
+SELECT *
+FROM t1
+WHERE g1 NOT IN
+(SELECT  grandparent1.col_varchar_nokey AS g1
+FROM t2 AS grandparent1
+WHERE grandparent1.col_varchar_key IN
+(SELECT parent1.col_varchar_nokey AS p1
+FROM t2 AS parent1 LEFT JOIN t3 AS parent2 USING (pk)
+)
+AND grandparent1.col_varchar_key IS NOT NULL
+);
+g1
+DROP TABLE t1, t2, t3;
+CREATE TABLE t1 (
+pk INTEGER AUTO_INCREMENT,
+col_int_key INTEGER ,
+col_varchar_key VARCHAR(1) ,
+col_varchar_nokey VARCHAR(1) ,
+PRIMARY KEY (pk),
+KEY (col_varchar_key,col_int_key)
+) ENGINE=INNODB;
+INSERT INTO t1 (col_int_key,col_varchar_key,col_varchar_nokey) VALUES
+(0,'x','x'), (1,'j','j'), (1,'r','r'), (9,'v','v'), (5,'r','r');
+CREATE TABLE t2 (
+pk INTEGER AUTO_INCREMENT,
+col_int_key INTEGER ,
+col_varchar_key VARCHAR(1) ,
+col_varchar_nokey VARCHAR(1) ,
+PRIMARY KEY (pk),
+KEY (col_int_key),
+KEY (col_varchar_key,col_int_key)
+) AUTO_INCREMENT=10 ENGINE=INNODB;
+INSERT INTO t2 (col_int_key, col_varchar_key, col_varchar_nokey) VALUES
+(NULL,'x','x'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t3
+SELECT outr.col_varchar_nokey AS x
+FROM t1 AS outr
+WHERE outr.col_varchar_nokey IN
+(SELECT innr.col_varchar_nokey AS y
+FROM t2 AS innr
+WHERE innr.col_int_key IS NULL)
+AND outr.col_varchar_nokey IS NOT NULL
+AND NOT col_varchar_key IS NULL;
+SELECT *
+FROM t3
+WHERE x NOT IN
+(SELECT outr.col_varchar_nokey AS x
+FROM t1 AS outr
+WHERE outr.col_varchar_nokey IN
+(SELECT innr.col_varchar_nokey AS y
+FROM t2 AS innr
+WHERE innr.col_int_key IS NULL)
+AND outr.col_varchar_nokey IS NOT NULL
+AND NOT col_varchar_key IS NULL);
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#12797534.
+# End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_mat_bka_nobnl.result'
--- a/mysql-test/r/subquery_sj_mat_bka_nobnl.result	2011-09-08 12:48:08 +0000
+++ b/mysql-test/r/subquery_sj_mat_bka_nobnl.result	2011-09-30 10:22:38 +0000
@@ -6669,5 +6669,105 @@ a
 1
 DROP TABLE t1, t2;
 # End of test for bug#12803439.
+#
+# Bug#12797534: Segfault in hp_movelink still exists
+#
+CREATE TABLE t1 (
+g1 VARCHAR(1) NOT NULL
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES ('d'), ('s');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,4,'j','j'), (2,6,'v','v'), (3,3,'c','c'), (4,5,'m','m'),
+(5,3,'d','d'), (6,246,'d','d'), (7,2,'y','y'), (8,9,'t','t'),
+(9,3,'d','d'), (10,8,'s','s'), (11,1,'r','r'), (12,8,'m','m'),
+(13,8,'b','b'), (14,5,'x','x'), (15,7,'g','g'), (16,5,'p','p'),
+(17,1,'q','q'), (18,6,'w','w'), (19,2,'d','d'), (20,9,'e','e');
+CREATE TABLE t3 (
+pk INTEGER NOT NULL,
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES (10);
+EXPLAIN SELECT *
+FROM t1
+WHERE g1 NOT IN
+(SELECT  grandparent1.col_varchar_nokey AS g1
+FROM t2 AS grandparent1
+WHERE grandparent1.col_varchar_key IN
+(SELECT parent1.col_varchar_nokey AS p1
+FROM t2 AS parent1 LEFT JOIN t3 AS parent2 USING (pk)
+)
+AND grandparent1.col_varchar_key IS NOT NULL
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	parent1	ALL	NULL	NULL	NULL	NULL	20	Start materialize; Scan
+2	DEPENDENT SUBQUERY	parent2	eq_ref	PRIMARY	PRIMARY	4	test.parent1.pk	1	Using index; End materialize
+2	DEPENDENT SUBQUERY	grandparent1	ref	col_varchar_key	col_varchar_key	3	test.parent1.col_varchar_nokey	1	Using where
+SELECT *
+FROM t1
+WHERE g1 NOT IN
+(SELECT  grandparent1.col_varchar_nokey AS g1
+FROM t2 AS grandparent1
+WHERE grandparent1.col_varchar_key IN
+(SELECT parent1.col_varchar_nokey AS p1
+FROM t2 AS parent1 LEFT JOIN t3 AS parent2 USING (pk)
+)
+AND grandparent1.col_varchar_key IS NOT NULL
+);
+g1
+DROP TABLE t1, t2, t3;
+CREATE TABLE t1 (
+pk INTEGER AUTO_INCREMENT,
+col_int_key INTEGER ,
+col_varchar_key VARCHAR(1) ,
+col_varchar_nokey VARCHAR(1) ,
+PRIMARY KEY (pk),
+KEY (col_varchar_key,col_int_key)
+) ENGINE=INNODB;
+INSERT INTO t1 (col_int_key,col_varchar_key,col_varchar_nokey) VALUES
+(0,'x','x'), (1,'j','j'), (1,'r','r'), (9,'v','v'), (5,'r','r');
+CREATE TABLE t2 (
+pk INTEGER AUTO_INCREMENT,
+col_int_key INTEGER ,
+col_varchar_key VARCHAR(1) ,
+col_varchar_nokey VARCHAR(1) ,
+PRIMARY KEY (pk),
+KEY (col_int_key),
+KEY (col_varchar_key,col_int_key)
+) AUTO_INCREMENT=10 ENGINE=INNODB;
+INSERT INTO t2 (col_int_key, col_varchar_key, col_varchar_nokey) VALUES
+(NULL,'x','x'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t3
+SELECT outr.col_varchar_nokey AS x
+FROM t1 AS outr
+WHERE outr.col_varchar_nokey IN
+(SELECT innr.col_varchar_nokey AS y
+FROM t2 AS innr
+WHERE innr.col_int_key IS NULL)
+AND outr.col_varchar_nokey IS NOT NULL
+AND NOT col_varchar_key IS NULL;
+SELECT *
+FROM t3
+WHERE x NOT IN
+(SELECT outr.col_varchar_nokey AS x
+FROM t1 AS outr
+WHERE outr.col_varchar_nokey IN
+(SELECT innr.col_varchar_nokey AS y
+FROM t2 AS innr
+WHERE innr.col_int_key IS NULL)
+AND outr.col_varchar_nokey IS NOT NULL
+AND NOT col_varchar_key IS NULL);
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#12797534.
+# End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_mat_bkaunique.result'
--- a/mysql-test/r/subquery_sj_mat_bkaunique.result	2011-09-08 12:48:08 +0000
+++ b/mysql-test/r/subquery_sj_mat_bkaunique.result	2011-09-30 10:22:38 +0000
@@ -6670,5 +6670,105 @@ a
 1
 DROP TABLE t1, t2;
 # End of test for bug#12803439.
+#
+# Bug#12797534: Segfault in hp_movelink still exists
+#
+CREATE TABLE t1 (
+g1 VARCHAR(1) NOT NULL
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES ('d'), ('s');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,4,'j','j'), (2,6,'v','v'), (3,3,'c','c'), (4,5,'m','m'),
+(5,3,'d','d'), (6,246,'d','d'), (7,2,'y','y'), (8,9,'t','t'),
+(9,3,'d','d'), (10,8,'s','s'), (11,1,'r','r'), (12,8,'m','m'),
+(13,8,'b','b'), (14,5,'x','x'), (15,7,'g','g'), (16,5,'p','p'),
+(17,1,'q','q'), (18,6,'w','w'), (19,2,'d','d'), (20,9,'e','e');
+CREATE TABLE t3 (
+pk INTEGER NOT NULL,
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES (10);
+EXPLAIN SELECT *
+FROM t1
+WHERE g1 NOT IN
+(SELECT  grandparent1.col_varchar_nokey AS g1
+FROM t2 AS grandparent1
+WHERE grandparent1.col_varchar_key IN
+(SELECT parent1.col_varchar_nokey AS p1
+FROM t2 AS parent1 LEFT JOIN t3 AS parent2 USING (pk)
+)
+AND grandparent1.col_varchar_key IS NOT NULL
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	parent1	ALL	NULL	NULL	NULL	NULL	20	Start materialize; Scan
+2	DEPENDENT SUBQUERY	parent2	eq_ref	PRIMARY	PRIMARY	4	test.parent1.pk	1	Using index; End materialize
+2	DEPENDENT SUBQUERY	grandparent1	ref	col_varchar_key	col_varchar_key	3	test.parent1.col_varchar_nokey	1	Using where
+SELECT *
+FROM t1
+WHERE g1 NOT IN
+(SELECT  grandparent1.col_varchar_nokey AS g1
+FROM t2 AS grandparent1
+WHERE grandparent1.col_varchar_key IN
+(SELECT parent1.col_varchar_nokey AS p1
+FROM t2 AS parent1 LEFT JOIN t3 AS parent2 USING (pk)
+)
+AND grandparent1.col_varchar_key IS NOT NULL
+);
+g1
+DROP TABLE t1, t2, t3;
+CREATE TABLE t1 (
+pk INTEGER AUTO_INCREMENT,
+col_int_key INTEGER ,
+col_varchar_key VARCHAR(1) ,
+col_varchar_nokey VARCHAR(1) ,
+PRIMARY KEY (pk),
+KEY (col_varchar_key,col_int_key)
+) ENGINE=INNODB;
+INSERT INTO t1 (col_int_key,col_varchar_key,col_varchar_nokey) VALUES
+(0,'x','x'), (1,'j','j'), (1,'r','r'), (9,'v','v'), (5,'r','r');
+CREATE TABLE t2 (
+pk INTEGER AUTO_INCREMENT,
+col_int_key INTEGER ,
+col_varchar_key VARCHAR(1) ,
+col_varchar_nokey VARCHAR(1) ,
+PRIMARY KEY (pk),
+KEY (col_int_key),
+KEY (col_varchar_key,col_int_key)
+) AUTO_INCREMENT=10 ENGINE=INNODB;
+INSERT INTO t2 (col_int_key, col_varchar_key, col_varchar_nokey) VALUES
+(NULL,'x','x'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t3
+SELECT outr.col_varchar_nokey AS x
+FROM t1 AS outr
+WHERE outr.col_varchar_nokey IN
+(SELECT innr.col_varchar_nokey AS y
+FROM t2 AS innr
+WHERE innr.col_int_key IS NULL)
+AND outr.col_varchar_nokey IS NOT NULL
+AND NOT col_varchar_key IS NULL;
+SELECT *
+FROM t3
+WHERE x NOT IN
+(SELECT outr.col_varchar_nokey AS x
+FROM t1 AS outr
+WHERE outr.col_varchar_nokey IN
+(SELECT innr.col_varchar_nokey AS y
+FROM t2 AS innr
+WHERE innr.col_int_key IS NULL)
+AND outr.col_varchar_nokey IS NOT NULL
+AND NOT col_varchar_key IS NULL);
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#12797534.
+# End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_mat_nosj.result'
--- a/mysql-test/r/subquery_sj_mat_nosj.result	2011-09-08 12:48:08 +0000
+++ b/mysql-test/r/subquery_sj_mat_nosj.result	2011-09-30 10:22:38 +0000
@@ -6745,4 +6745,104 @@ a
 1
 DROP TABLE t1, t2;
 # End of test for bug#12803439.
+#
+# Bug#12797534: Segfault in hp_movelink still exists
+#
+CREATE TABLE t1 (
+g1 VARCHAR(1) NOT NULL
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES ('d'), ('s');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,4,'j','j'), (2,6,'v','v'), (3,3,'c','c'), (4,5,'m','m'),
+(5,3,'d','d'), (6,246,'d','d'), (7,2,'y','y'), (8,9,'t','t'),
+(9,3,'d','d'), (10,8,'s','s'), (11,1,'r','r'), (12,8,'m','m'),
+(13,8,'b','b'), (14,5,'x','x'), (15,7,'g','g'), (16,5,'p','p'),
+(17,1,'q','q'), (18,6,'w','w'), (19,2,'d','d'), (20,9,'e','e');
+CREATE TABLE t3 (
+pk INTEGER NOT NULL,
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES (10);
+EXPLAIN SELECT *
+FROM t1
+WHERE g1 NOT IN
+(SELECT  grandparent1.col_varchar_nokey AS g1
+FROM t2 AS grandparent1
+WHERE grandparent1.col_varchar_key IN
+(SELECT parent1.col_varchar_nokey AS p1
+FROM t2 AS parent1 LEFT JOIN t3 AS parent2 USING (pk)
+)
+AND grandparent1.col_varchar_key IS NOT NULL
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	grandparent1	ALL	col_varchar_key	NULL	NULL	NULL	20	Using where
+3	SUBQUERY	parent1	ALL	NULL	NULL	NULL	NULL	20	
+3	SUBQUERY	parent2	eq_ref	PRIMARY	PRIMARY	4	test.parent1.pk	1	Using index
+SELECT *
+FROM t1
+WHERE g1 NOT IN
+(SELECT  grandparent1.col_varchar_nokey AS g1
+FROM t2 AS grandparent1
+WHERE grandparent1.col_varchar_key IN
+(SELECT parent1.col_varchar_nokey AS p1
+FROM t2 AS parent1 LEFT JOIN t3 AS parent2 USING (pk)
+)
+AND grandparent1.col_varchar_key IS NOT NULL
+);
+g1
+DROP TABLE t1, t2, t3;
+CREATE TABLE t1 (
+pk INTEGER AUTO_INCREMENT,
+col_int_key INTEGER ,
+col_varchar_key VARCHAR(1) ,
+col_varchar_nokey VARCHAR(1) ,
+PRIMARY KEY (pk),
+KEY (col_varchar_key,col_int_key)
+) ENGINE=INNODB;
+INSERT INTO t1 (col_int_key,col_varchar_key,col_varchar_nokey) VALUES
+(0,'x','x'), (1,'j','j'), (1,'r','r'), (9,'v','v'), (5,'r','r');
+CREATE TABLE t2 (
+pk INTEGER AUTO_INCREMENT,
+col_int_key INTEGER ,
+col_varchar_key VARCHAR(1) ,
+col_varchar_nokey VARCHAR(1) ,
+PRIMARY KEY (pk),
+KEY (col_int_key),
+KEY (col_varchar_key,col_int_key)
+) AUTO_INCREMENT=10 ENGINE=INNODB;
+INSERT INTO t2 (col_int_key, col_varchar_key, col_varchar_nokey) VALUES
+(NULL,'x','x'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t3
+SELECT outr.col_varchar_nokey AS x
+FROM t1 AS outr
+WHERE outr.col_varchar_nokey IN
+(SELECT innr.col_varchar_nokey AS y
+FROM t2 AS innr
+WHERE innr.col_int_key IS NULL)
+AND outr.col_varchar_nokey IS NOT NULL
+AND NOT col_varchar_key IS NULL;
+SELECT *
+FROM t3
+WHERE x NOT IN
+(SELECT outr.col_varchar_nokey AS x
+FROM t1 AS outr
+WHERE outr.col_varchar_nokey IN
+(SELECT innr.col_varchar_nokey AS y
+FROM t2 AS innr
+WHERE innr.col_int_key IS NULL)
+AND outr.col_varchar_nokey IS NOT NULL
+AND NOT col_varchar_key IS NULL);
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#12797534.
+# End of 5.6 tests
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_none.result'
--- a/mysql-test/r/subquery_sj_none.result	2011-09-27 08:20:14 +0000
+++ b/mysql-test/r/subquery_sj_none.result	2011-09-30 10:22:38 +0000
@@ -6680,4 +6680,104 @@ a
 1
 DROP TABLE t1, t2;
 # End of test for bug#12803439.
+#
+# Bug#12797534: Segfault in hp_movelink still exists
+#
+CREATE TABLE t1 (
+g1 VARCHAR(1) NOT NULL
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES ('d'), ('s');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,4,'j','j'), (2,6,'v','v'), (3,3,'c','c'), (4,5,'m','m'),
+(5,3,'d','d'), (6,246,'d','d'), (7,2,'y','y'), (8,9,'t','t'),
+(9,3,'d','d'), (10,8,'s','s'), (11,1,'r','r'), (12,8,'m','m'),
+(13,8,'b','b'), (14,5,'x','x'), (15,7,'g','g'), (16,5,'p','p'),
+(17,1,'q','q'), (18,6,'w','w'), (19,2,'d','d'), (20,9,'e','e');
+CREATE TABLE t3 (
+pk INTEGER NOT NULL,
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES (10);
+EXPLAIN SELECT *
+FROM t1
+WHERE g1 NOT IN
+(SELECT  grandparent1.col_varchar_nokey AS g1
+FROM t2 AS grandparent1
+WHERE grandparent1.col_varchar_key IN
+(SELECT parent1.col_varchar_nokey AS p1
+FROM t2 AS parent1 LEFT JOIN t3 AS parent2 USING (pk)
+)
+AND grandparent1.col_varchar_key IS NOT NULL
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	grandparent1	ALL	col_varchar_key	NULL	NULL	NULL	20	Using where
+3	DEPENDENT SUBQUERY	parent1	ALL	NULL	NULL	NULL	NULL	20	Using where
+3	DEPENDENT SUBQUERY	parent2	eq_ref	PRIMARY	PRIMARY	4	test.parent1.pk	1	Using index
+SELECT *
+FROM t1
+WHERE g1 NOT IN
+(SELECT  grandparent1.col_varchar_nokey AS g1
+FROM t2 AS grandparent1
+WHERE grandparent1.col_varchar_key IN
+(SELECT parent1.col_varchar_nokey AS p1
+FROM t2 AS parent1 LEFT JOIN t3 AS parent2 USING (pk)
+)
+AND grandparent1.col_varchar_key IS NOT NULL
+);
+g1
+DROP TABLE t1, t2, t3;
+CREATE TABLE t1 (
+pk INTEGER AUTO_INCREMENT,
+col_int_key INTEGER ,
+col_varchar_key VARCHAR(1) ,
+col_varchar_nokey VARCHAR(1) ,
+PRIMARY KEY (pk),
+KEY (col_varchar_key,col_int_key)
+) ENGINE=INNODB;
+INSERT INTO t1 (col_int_key,col_varchar_key,col_varchar_nokey) VALUES
+(0,'x','x'), (1,'j','j'), (1,'r','r'), (9,'v','v'), (5,'r','r');
+CREATE TABLE t2 (
+pk INTEGER AUTO_INCREMENT,
+col_int_key INTEGER ,
+col_varchar_key VARCHAR(1) ,
+col_varchar_nokey VARCHAR(1) ,
+PRIMARY KEY (pk),
+KEY (col_int_key),
+KEY (col_varchar_key,col_int_key)
+) AUTO_INCREMENT=10 ENGINE=INNODB;
+INSERT INTO t2 (col_int_key, col_varchar_key, col_varchar_nokey) VALUES
+(NULL,'x','x'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t3
+SELECT outr.col_varchar_nokey AS x
+FROM t1 AS outr
+WHERE outr.col_varchar_nokey IN
+(SELECT innr.col_varchar_nokey AS y
+FROM t2 AS innr
+WHERE innr.col_int_key IS NULL)
+AND outr.col_varchar_nokey IS NOT NULL
+AND NOT col_varchar_key IS NULL;
+SELECT *
+FROM t3
+WHERE x NOT IN
+(SELECT outr.col_varchar_nokey AS x
+FROM t1 AS outr
+WHERE outr.col_varchar_nokey IN
+(SELECT innr.col_varchar_nokey AS y
+FROM t2 AS innr
+WHERE innr.col_int_key IS NULL)
+AND outr.col_varchar_nokey IS NOT NULL
+AND NOT col_varchar_key IS NULL);
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#12797534.
+# End of 5.6 tests
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_none_bka.result'
--- a/mysql-test/r/subquery_sj_none_bka.result	2011-09-27 08:20:14 +0000
+++ b/mysql-test/r/subquery_sj_none_bka.result	2011-09-30 10:22:38 +0000
@@ -6681,5 +6681,105 @@ a
 1
 DROP TABLE t1, t2;
 # End of test for bug#12803439.
+#
+# Bug#12797534: Segfault in hp_movelink still exists
+#
+CREATE TABLE t1 (
+g1 VARCHAR(1) NOT NULL
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES ('d'), ('s');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,4,'j','j'), (2,6,'v','v'), (3,3,'c','c'), (4,5,'m','m'),
+(5,3,'d','d'), (6,246,'d','d'), (7,2,'y','y'), (8,9,'t','t'),
+(9,3,'d','d'), (10,8,'s','s'), (11,1,'r','r'), (12,8,'m','m'),
+(13,8,'b','b'), (14,5,'x','x'), (15,7,'g','g'), (16,5,'p','p'),
+(17,1,'q','q'), (18,6,'w','w'), (19,2,'d','d'), (20,9,'e','e');
+CREATE TABLE t3 (
+pk INTEGER NOT NULL,
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES (10);
+EXPLAIN SELECT *
+FROM t1
+WHERE g1 NOT IN
+(SELECT  grandparent1.col_varchar_nokey AS g1
+FROM t2 AS grandparent1
+WHERE grandparent1.col_varchar_key IN
+(SELECT parent1.col_varchar_nokey AS p1
+FROM t2 AS parent1 LEFT JOIN t3 AS parent2 USING (pk)
+)
+AND grandparent1.col_varchar_key IS NOT NULL
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	grandparent1	ALL	col_varchar_key	NULL	NULL	NULL	20	Using where
+3	DEPENDENT SUBQUERY	parent1	ALL	NULL	NULL	NULL	NULL	20	Using where
+3	DEPENDENT SUBQUERY	parent2	eq_ref	PRIMARY	PRIMARY	4	test.parent1.pk	1	Using index
+SELECT *
+FROM t1
+WHERE g1 NOT IN
+(SELECT  grandparent1.col_varchar_nokey AS g1
+FROM t2 AS grandparent1
+WHERE grandparent1.col_varchar_key IN
+(SELECT parent1.col_varchar_nokey AS p1
+FROM t2 AS parent1 LEFT JOIN t3 AS parent2 USING (pk)
+)
+AND grandparent1.col_varchar_key IS NOT NULL
+);
+g1
+DROP TABLE t1, t2, t3;
+CREATE TABLE t1 (
+pk INTEGER AUTO_INCREMENT,
+col_int_key INTEGER ,
+col_varchar_key VARCHAR(1) ,
+col_varchar_nokey VARCHAR(1) ,
+PRIMARY KEY (pk),
+KEY (col_varchar_key,col_int_key)
+) ENGINE=INNODB;
+INSERT INTO t1 (col_int_key,col_varchar_key,col_varchar_nokey) VALUES
+(0,'x','x'), (1,'j','j'), (1,'r','r'), (9,'v','v'), (5,'r','r');
+CREATE TABLE t2 (
+pk INTEGER AUTO_INCREMENT,
+col_int_key INTEGER ,
+col_varchar_key VARCHAR(1) ,
+col_varchar_nokey VARCHAR(1) ,
+PRIMARY KEY (pk),
+KEY (col_int_key),
+KEY (col_varchar_key,col_int_key)
+) AUTO_INCREMENT=10 ENGINE=INNODB;
+INSERT INTO t2 (col_int_key, col_varchar_key, col_varchar_nokey) VALUES
+(NULL,'x','x'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t3
+SELECT outr.col_varchar_nokey AS x
+FROM t1 AS outr
+WHERE outr.col_varchar_nokey IN
+(SELECT innr.col_varchar_nokey AS y
+FROM t2 AS innr
+WHERE innr.col_int_key IS NULL)
+AND outr.col_varchar_nokey IS NOT NULL
+AND NOT col_varchar_key IS NULL;
+SELECT *
+FROM t3
+WHERE x NOT IN
+(SELECT outr.col_varchar_nokey AS x
+FROM t1 AS outr
+WHERE outr.col_varchar_nokey IN
+(SELECT innr.col_varchar_nokey AS y
+FROM t2 AS innr
+WHERE innr.col_int_key IS NULL)
+AND outr.col_varchar_nokey IS NOT NULL
+AND NOT col_varchar_key IS NULL);
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#12797534.
+# End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_none_bka_nobnl.result'
--- a/mysql-test/r/subquery_sj_none_bka_nobnl.result	2011-09-14 12:36:47 +0000
+++ b/mysql-test/r/subquery_sj_none_bka_nobnl.result	2011-09-30 10:22:38 +0000
@@ -6681,5 +6681,105 @@ a
 1
 DROP TABLE t1, t2;
 # End of test for bug#12803439.
+#
+# Bug#12797534: Segfault in hp_movelink still exists
+#
+CREATE TABLE t1 (
+g1 VARCHAR(1) NOT NULL
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES ('d'), ('s');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,4,'j','j'), (2,6,'v','v'), (3,3,'c','c'), (4,5,'m','m'),
+(5,3,'d','d'), (6,246,'d','d'), (7,2,'y','y'), (8,9,'t','t'),
+(9,3,'d','d'), (10,8,'s','s'), (11,1,'r','r'), (12,8,'m','m'),
+(13,8,'b','b'), (14,5,'x','x'), (15,7,'g','g'), (16,5,'p','p'),
+(17,1,'q','q'), (18,6,'w','w'), (19,2,'d','d'), (20,9,'e','e');
+CREATE TABLE t3 (
+pk INTEGER NOT NULL,
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES (10);
+EXPLAIN SELECT *
+FROM t1
+WHERE g1 NOT IN
+(SELECT  grandparent1.col_varchar_nokey AS g1
+FROM t2 AS grandparent1
+WHERE grandparent1.col_varchar_key IN
+(SELECT parent1.col_varchar_nokey AS p1
+FROM t2 AS parent1 LEFT JOIN t3 AS parent2 USING (pk)
+)
+AND grandparent1.col_varchar_key IS NOT NULL
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	grandparent1	ALL	col_varchar_key	NULL	NULL	NULL	20	Using where
+3	DEPENDENT SUBQUERY	parent1	ALL	NULL	NULL	NULL	NULL	20	Using where
+3	DEPENDENT SUBQUERY	parent2	eq_ref	PRIMARY	PRIMARY	4	test.parent1.pk	1	Using index
+SELECT *
+FROM t1
+WHERE g1 NOT IN
+(SELECT  grandparent1.col_varchar_nokey AS g1
+FROM t2 AS grandparent1
+WHERE grandparent1.col_varchar_key IN
+(SELECT parent1.col_varchar_nokey AS p1
+FROM t2 AS parent1 LEFT JOIN t3 AS parent2 USING (pk)
+)
+AND grandparent1.col_varchar_key IS NOT NULL
+);
+g1
+DROP TABLE t1, t2, t3;
+CREATE TABLE t1 (
+pk INTEGER AUTO_INCREMENT,
+col_int_key INTEGER ,
+col_varchar_key VARCHAR(1) ,
+col_varchar_nokey VARCHAR(1) ,
+PRIMARY KEY (pk),
+KEY (col_varchar_key,col_int_key)
+) ENGINE=INNODB;
+INSERT INTO t1 (col_int_key,col_varchar_key,col_varchar_nokey) VALUES
+(0,'x','x'), (1,'j','j'), (1,'r','r'), (9,'v','v'), (5,'r','r');
+CREATE TABLE t2 (
+pk INTEGER AUTO_INCREMENT,
+col_int_key INTEGER ,
+col_varchar_key VARCHAR(1) ,
+col_varchar_nokey VARCHAR(1) ,
+PRIMARY KEY (pk),
+KEY (col_int_key),
+KEY (col_varchar_key,col_int_key)
+) AUTO_INCREMENT=10 ENGINE=INNODB;
+INSERT INTO t2 (col_int_key, col_varchar_key, col_varchar_nokey) VALUES
+(NULL,'x','x'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t3
+SELECT outr.col_varchar_nokey AS x
+FROM t1 AS outr
+WHERE outr.col_varchar_nokey IN
+(SELECT innr.col_varchar_nokey AS y
+FROM t2 AS innr
+WHERE innr.col_int_key IS NULL)
+AND outr.col_varchar_nokey IS NOT NULL
+AND NOT col_varchar_key IS NULL;
+SELECT *
+FROM t3
+WHERE x NOT IN
+(SELECT outr.col_varchar_nokey AS x
+FROM t1 AS outr
+WHERE outr.col_varchar_nokey IN
+(SELECT innr.col_varchar_nokey AS y
+FROM t2 AS innr
+WHERE innr.col_int_key IS NULL)
+AND outr.col_varchar_nokey IS NOT NULL
+AND NOT col_varchar_key IS NULL);
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#12797534.
+# End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_none_bkaunique.result'
--- a/mysql-test/r/subquery_sj_none_bkaunique.result	2011-09-27 08:20:14 +0000
+++ b/mysql-test/r/subquery_sj_none_bkaunique.result	2011-09-30 10:22:38 +0000
@@ -6682,5 +6682,105 @@ a
 1
 DROP TABLE t1, t2;
 # End of test for bug#12803439.
+#
+# Bug#12797534: Segfault in hp_movelink still exists
+#
+CREATE TABLE t1 (
+g1 VARCHAR(1) NOT NULL
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES ('d'), ('s');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,4,'j','j'), (2,6,'v','v'), (3,3,'c','c'), (4,5,'m','m'),
+(5,3,'d','d'), (6,246,'d','d'), (7,2,'y','y'), (8,9,'t','t'),
+(9,3,'d','d'), (10,8,'s','s'), (11,1,'r','r'), (12,8,'m','m'),
+(13,8,'b','b'), (14,5,'x','x'), (15,7,'g','g'), (16,5,'p','p'),
+(17,1,'q','q'), (18,6,'w','w'), (19,2,'d','d'), (20,9,'e','e');
+CREATE TABLE t3 (
+pk INTEGER NOT NULL,
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES (10);
+EXPLAIN SELECT *
+FROM t1
+WHERE g1 NOT IN
+(SELECT  grandparent1.col_varchar_nokey AS g1
+FROM t2 AS grandparent1
+WHERE grandparent1.col_varchar_key IN
+(SELECT parent1.col_varchar_nokey AS p1
+FROM t2 AS parent1 LEFT JOIN t3 AS parent2 USING (pk)
+)
+AND grandparent1.col_varchar_key IS NOT NULL
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	grandparent1	ALL	col_varchar_key	NULL	NULL	NULL	20	Using where
+3	DEPENDENT SUBQUERY	parent1	ALL	NULL	NULL	NULL	NULL	20	Using where
+3	DEPENDENT SUBQUERY	parent2	eq_ref	PRIMARY	PRIMARY	4	test.parent1.pk	1	Using index
+SELECT *
+FROM t1
+WHERE g1 NOT IN
+(SELECT  grandparent1.col_varchar_nokey AS g1
+FROM t2 AS grandparent1
+WHERE grandparent1.col_varchar_key IN
+(SELECT parent1.col_varchar_nokey AS p1
+FROM t2 AS parent1 LEFT JOIN t3 AS parent2 USING (pk)
+)
+AND grandparent1.col_varchar_key IS NOT NULL
+);
+g1
+DROP TABLE t1, t2, t3;
+CREATE TABLE t1 (
+pk INTEGER AUTO_INCREMENT,
+col_int_key INTEGER ,
+col_varchar_key VARCHAR(1) ,
+col_varchar_nokey VARCHAR(1) ,
+PRIMARY KEY (pk),
+KEY (col_varchar_key,col_int_key)
+) ENGINE=INNODB;
+INSERT INTO t1 (col_int_key,col_varchar_key,col_varchar_nokey) VALUES
+(0,'x','x'), (1,'j','j'), (1,'r','r'), (9,'v','v'), (5,'r','r');
+CREATE TABLE t2 (
+pk INTEGER AUTO_INCREMENT,
+col_int_key INTEGER ,
+col_varchar_key VARCHAR(1) ,
+col_varchar_nokey VARCHAR(1) ,
+PRIMARY KEY (pk),
+KEY (col_int_key),
+KEY (col_varchar_key,col_int_key)
+) AUTO_INCREMENT=10 ENGINE=INNODB;
+INSERT INTO t2 (col_int_key, col_varchar_key, col_varchar_nokey) VALUES
+(NULL,'x','x'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t3
+SELECT outr.col_varchar_nokey AS x
+FROM t1 AS outr
+WHERE outr.col_varchar_nokey IN
+(SELECT innr.col_varchar_nokey AS y
+FROM t2 AS innr
+WHERE innr.col_int_key IS NULL)
+AND outr.col_varchar_nokey IS NOT NULL
+AND NOT col_varchar_key IS NULL;
+SELECT *
+FROM t3
+WHERE x NOT IN
+(SELECT outr.col_varchar_nokey AS x
+FROM t1 AS outr
+WHERE outr.col_varchar_nokey IN
+(SELECT innr.col_varchar_nokey AS y
+FROM t2 AS innr
+WHERE innr.col_int_key IS NULL)
+AND outr.col_varchar_nokey IS NOT NULL
+AND NOT col_varchar_key IS NULL);
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#12797534.
+# End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2011-09-28 15:00:45 +0000
+++ b/sql/sql_select.cc	2011-09-30 10:22:38 +0000
@@ -18649,10 +18649,14 @@ sub_select_sjm(JOIN *join, JOIN_TAB *joi
     DBUG_RETURN(rc);
   }
 
-  Semijoin_mat_exec *sjm= join_tab->emb_sj_nest->sj_mat_exec;
+  Semijoin_mat_exec *const sjm= join_tab->emb_sj_nest->sj_mat_exec;
+
+  // Cache a pointer to the last of the materialized inner tables:
+  JOIN_TAB *const last_tab= join_tab + (sjm->table_count - 1);
+
   if (end_of_records)
   {
-    rc= (*join_tab[sjm->table_count - 1].next_select)
+    rc= (*last_tab->next_select)
           (join, join_tab + sjm->table_count, end_of_records);
     DBUG_RETURN(rc);
   }
@@ -18662,23 +18666,8 @@ sub_select_sjm(JOIN *join, JOIN_TAB *joi
       Do the materialization. First, put end_sj_materialize after the last
       inner table so we can catch record combinations of sj-inner tables.
     */
-    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;
-      }
-    }
+    const Next_select_func next_func= last_tab->next_select;
+    last_tab->next_select= end_sj_materialize;
     /*
       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
@@ -18687,55 +18676,48 @@ sub_select_sjm(JOIN *join, JOIN_TAB *joi
     if ((rc= sub_select(join, join_tab, FALSE)) < 0 ||
         (rc= sub_select(join, join_tab, TRUE/*EOF*/)) < 0)
     {
-      join_tab[sjm->table_count - 1].next_select= next_func;
+      last_tab->next_select= next_func;
       DBUG_RETURN(rc); /* it's NESTED_LOOP_(ERROR|KILLED)*/
     }
-    join_tab[sjm->table_count - 1].next_select= next_func;
-
-    /*
-      Ok, materialization finished. Initialize the access to the temptable
-    */
-    join_tab->read_record.read_record= join_no_more_records;
-    if (sjm->is_scan)
-    {
-      /* Initialize full scan */
-      JOIN_TAB *last_tab= join_tab + (sjm->table_count - 1);
-      init_read_record(&last_tab->read_record, join->thd,
-                       sjm->table, NULL, TRUE, TRUE, FALSE);
-
-      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 +
-                                            sjm->table_cols.elements;
-      last_tab->read_record.read_record= rr_sequential_and_unpack;
-
-      // Clear possible outer join information from earlier use of this join tab
-      last_tab->last_inner= NULL;
-      last_tab->first_unmatched= NULL;
-    }
+    last_tab->next_select= next_func;
 
     sjm->materialized= true;
   }
-  else
-  {
-    if (sjm->is_scan)
-    {
-      /* Reset the cursor for a new scan over the table */
-      if (sjm->table->file->ha_rnd_init(TRUE))
-        DBUG_RETURN(NESTED_LOOP_ERROR);
-    }
-  }
 
   if (sjm->is_scan)
   {
-    /* Do full scan of the materialized table */
-    JOIN_TAB *last_tab= join_tab + (sjm->table_count - 1);
+    /*
+      Perform a full scan over the materialized table.
+      Reuse the join tab of the last inner table for the materialized table.
+    */
 
-    Item *save_cond= last_tab->condition();
+    // Save contents of join tab for possible repeated materializations:
+    const READ_RECORD saved_access= last_tab->read_record;
+    const READ_RECORD::Setup_func saved_rfr= last_tab->read_first_record;
+
+    // Initialize full scan
+    init_read_record(&last_tab->read_record, join->thd,
+                     sjm->table, NULL, TRUE, TRUE, FALSE);
+
+    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 +
+                                          sjm->table_cols.elements;
+    last_tab->read_record.read_record= rr_sequential_and_unpack;
+
+    // Clear possible outer join information from earlier use of this join tab
+    last_tab->last_inner= NULL;
+    last_tab->first_unmatched= NULL;
+
+    Item *const save_cond= last_tab->condition();
     last_tab->set_condition(sjm->join_cond, __LINE__);
     rc= sub_select(join, last_tab, end_of_records);
+    end_read_record(&last_tab->read_record);
+
+    // Restore access method used for materialization
     last_tab->set_condition(save_cond, __LINE__);
-    DBUG_RETURN(rc);
+    last_tab->read_record= saved_access;
+    last_tab->read_first_record= saved_rfr;
   }
   else
   {
@@ -18744,9 +18726,9 @@ sub_select_sjm(JOIN *join, JOIN_TAB *joi
       DBUG_RETURN(NESTED_LOOP_ERROR); /* purecov: inspected */
     if (res || !sjm->in_equality->val_int())
       DBUG_RETURN(NESTED_LOOP_NO_MORE_ROWS);
+    rc= (*last_tab->next_select)
+      (join, join_tab + sjm->table_count, end_of_records);
   }
-  rc= (*join_tab[sjm->table_count - 1].next_select)
-        (join, join_tab + sjm->table_count, end_of_records);
   DBUG_RETURN(rc);
 }
 

=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h	2011-09-20 13:07:55 +0000
+++ b/sql/sql_select.h	2011-09-30 10:22:38 +0000
@@ -372,7 +372,6 @@ public:
     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 */

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-trunk branch (roy.lyseng:3447 to 3448) Bug#12797534Roy Lyseng2 Oct