List:Commits« Previous MessageNext Message »
From:Roy Lyseng Date:July 9 2010 1:36pm
Subject:bzr commit into mysql-next-mr-opt-backporting branch (roy.lyseng:3211)
Bug#43768
View as plain text  
#At file:///home/rl136806/mysql/repo/mysql-work4/ based on revid:guilhem@stripped

 3211 Roy Lyseng	2010-07-09
      Bug#43768: Prepared query with nested subqueries core dump on second execution
      
      The problem here is that pullout of semijoin tables is attempted for
      each execution, but because those tables are not "pushed back" again
      after each execution, the pullout fails on second attempt.
      
      The solution chosen here is to pullout only those semijoin tables that are
      functionally dependent upon the outer tables. This pullout operation
      need to be performed only once, and, unlike the current procedure, is not
      dependent upon the row count of the involved tables.
      We still pullout tables that are classified as const tables based on row count
      if this is a direct execution, though.
      
      The practical implication of this is as follows:
      
       - Only outer tables containing zero or one rows will now be extracted
         as "const tables". Thus, such tables from a semijoin nest are no
         longer accessed during optimization, and some (rare) optimizations
         are no longer possible.
      
       - In the majority of cases, there is no performance impact. Often,
         the new strategy chosen is Materialization, meaning that the row
         of these table is accessed only once and saved in local memory.
      
       - Const table analysis now has to be done in two phases:
         1) Pullout tables based on dependencies. Both outer and inner tables
         may apply, and
         2) Pullout tables based on row count. Outer tables are always pulled out,
         inner tables only if inside a non-prepared statement.
      
      In order to implement the latter point above, and assure that pullout
      of semijoin tables occurs only once, make_join_statistics() has been
      restructured slightly. The conditional logic within the function has also
      been enhanced for better readability.
      
      The logic of make_join_statistics() is now as follows:
      
      1. Initialize JOIN data structures
         (major part of first loop in existing implementation).
      
      2. Update dependencies based on join information
         (the Warshall algorithm).
      
      3. Make key descriptions (update_ref_and_keys()).
      
      4. Pull out semijoin tables, called only once.
      
      5. Extract tables with zero or one rows as const tables
         (in prepared mode, consider outer tables only, no semijoin tables).
      
      6. Extract dependent tables as const tables.
         (in prepared mode, consider outer tables only, no semijoin tables).
      
      7. The remaining parts of the function.
      
      mysql-test/r/select_found.result
        Possible keys changed from "PRIMARY,kid" to "kid".
        Happens because analysis order is slightly changed, but harmless as
        the table is identified as "const".
      
      mysql-test/r/subselect_sj.result
        Added test case for bug#43768
        A number of plan changes because of extensive testing of semi-join
        tables with 0, 1 and 2 rows.
      
      mysql-test/r/subselect_sj_jcl6.result
        Ditto.
      
      mysql-test/t/subselect_sj.test
        Added test case for bug#43768
        bug#46744 now becomes a duplicate of bug#50489, and test case is moved.
      
      mysql-test/suite/optimizer_unfixed_bugs/t/bug46744.test
        Test for bug#46744 moved here.
      
      sql/sql_select.cc
        pull_out_semijoin_tables()
          join_tab->emb_sj_nest is no longer updated. Comments updated.
        make_join_statistics()
          Removed const table pullout from first loop.
          Simplified testing based on inner/outer/semi-join properties.
          Calls pull_out_semijoin_tables() just after dependency analysis.
          Then, added loop that performs pullout based on row count
          but excludes semijoined tables in prepared statements.
          Second call of pull_out_semijoin_tables() is needed after this.
          Simplified testing based on inner/outer/semi-join properties.
          Semijoin tables are no longer pulled out based on row count in
          prepared statements.
          Because pull_out_semijoin_tables() is no longer called for each
          execution, emb_sj_nest is now set in this function.

    added:
      mysql-test/suite/optimizer_unfixed_bugs/t/bug46744.test
    modified:
      mysql-test/include/subquery_sj.inc
      mysql-test/r/optimizer_switch.result
      mysql-test/r/select_found.result
      mysql-test/r/subquery_sj_all.result
      mysql-test/r/subquery_sj_all_jcl6.result
      mysql-test/r/subquery_sj_all_jcl7.result
      mysql-test/r/subquery_sj_dupsweed.result
      mysql-test/r/subquery_sj_dupsweed_jcl6.result
      mysql-test/r/subquery_sj_dupsweed_jcl7.result
      mysql-test/r/subquery_sj_firstmatch.result
      mysql-test/r/subquery_sj_firstmatch_jcl6.result
      mysql-test/r/subquery_sj_firstmatch_jcl7.result
      mysql-test/r/subquery_sj_loosescan.result
      mysql-test/r/subquery_sj_loosescan_jcl6.result
      mysql-test/r/subquery_sj_loosescan_jcl7.result
      mysql-test/r/subquery_sj_mat.result
      mysql-test/r/subquery_sj_mat_jcl6.result
      mysql-test/r/subquery_sj_mat_jcl7.result
      mysql-test/r/subquery_sj_mat_nosj.result
      mysql-test/r/subquery_sj_none.result
      mysql-test/r/subquery_sj_none_jcl6.result
      mysql-test/r/subquery_sj_none_jcl7.result
      mysql-test/t/optimizer_switch.test
      sql/sql_select.cc
=== modified file 'mysql-test/include/subquery_sj.inc'
--- a/mysql-test/include/subquery_sj.inc	2010-06-20 07:49:07 +0000
+++ b/mysql-test/include/subquery_sj.inc	2010-07-09 13:35:44 +0000
@@ -2731,6 +2731,95 @@ select name from t2, t1 
 drop table t1,t2,t3,t4;
 
 --echo #
+--echo # Bug#43768 Prepared query with nested subqueries core dump on second execution
+--echo #
+
+CREATE TABLE t1 (
+  id INT PRIMARY KEY,
+  partner_id VARCHAR(35)
+);
+
+INSERT INTO t1 VALUES
+ (1, 'partner1'), (2, 'partner2'),
+ (3, 'partner3'), (4, 'partner4');
+
+CREATE TABLE t2 (
+  id INT NOT NULL,
+  t1_line_id INT,
+  article_id VARCHAR(20),
+  PRIMARY KEY(id, t1_line_id)
+);
+
+INSERT INTO t2 VALUES
+ (1, 1, 'sup'), (2, 1, 'sup'),
+ (2, 2, 'sup'), (2, 3, 'sup'),
+ (2, 4, 'imp'), (3, 1, 'sup'),
+ (4, 1, 'sup');
+
+CREATE TABLE t3 (
+  user_id VARCHAR(50),
+  article_id VARCHAR(20) NOT NULL,
+  PRIMARY KEY(user_id)
+);
+
+INSERT INTO t3 VALUES('nicke', 'imp');
+
+EXPLAIN
+SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+    SELECT t2.id
+    FROM t2
+    WHERE article_id IN (
+      SELECT article_id FROM t3
+      WHERE user_id = 'nicke'
+    )
+  );
+
+SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+    SELECT t2.id
+    FROM t2
+    WHERE article_id IN (
+      SELECT article_id FROM t3
+      WHERE user_id = 'nicke'
+    )
+  );
+
+PREPARE stmt FROM
+'EXPLAIN SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+    SELECT t2.id
+    FROM t2
+    WHERE article_id IN (
+      SELECT article_id FROM t3
+      WHERE user_id = \'nicke\'
+    )
+  )';
+EXECUTE stmt;
+EXECUTE stmt;
+
+PREPARE stmt FROM
+'SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+    SELECT t2.id
+    FROM t2
+    WHERE article_id IN (
+      SELECT article_id FROM t3
+      WHERE user_id = \'nicke\'
+    )
+  )';
+EXECUTE stmt;
+EXECUTE stmt;
+
+DROP TABLE t1,t2,t3;
+
+--echo # End of Bug#43768
+
+--echo #
 --echo # Bug#53058 - semijoin execution of subquery with outerjoin yields wrong result
 --echo #
 CREATE TABLE t1 (i INTEGER);

=== modified file 'mysql-test/r/optimizer_switch.result'
--- a/mysql-test/r/optimizer_switch.result	2010-06-18 08:45:53 +0000
+++ b/mysql-test/r/optimizer_switch.result	2010-07-09 13:35:44 +0000
@@ -110,31 +110,3 @@ ERROR 42S22: Unknown column 'f1' in 'whe
 DROP PROCEDURE p1;
 DROP TABLE t1, t2;
 SET SESSION optimizer_switch = 'default';
-#
-# Bug #46744 Crash in optimize_semijoin_nests on empty view
-# with limit and procedure.
-#
-DROP TABLE IF EXISTS t1, t2;
-DROP VIEW IF EXISTS v1;
-DROP PROCEDURE IF EXISTS p1;
-CREATE TABLE t1 ( f1 int );
-CREATE TABLE t2 ( f1 int );
-insert into t2 values (5), (7);
-CREATE OR REPLACE VIEW v1 AS SELECT * FROM t1 LIMIT 2;
-create procedure p1() 
-select COUNT(*) 
-FROM v1 WHERE f1 IN 
-(SELECT f1 FROM t2 WHERE f1 = ANY (SELECT f1 FROM v1));
-SET SESSION optimizer_switch = 'semijoin=on';
-CALL p1();
-COUNT(*)
-0
-SET SESSION optimizer_switch = 'semijoin=off';
-CALL p1();
-COUNT(*)
-0
-drop table t1, t2;
-drop view v1;
-drop procedure p1;
-set SESSION optimizer_switch='default';
-# End of bug#46744

=== modified file 'mysql-test/r/select_found.result'
--- a/mysql-test/r/select_found.result	2007-08-02 19:45:56 +0000
+++ b/mysql-test/r/select_found.result	2010-07-09 13:35:44 +0000
@@ -83,7 +83,7 @@ UNIQUE KEY e_n (email,name)
 );
 EXPLAIN SELECT SQL_CALC_FOUND_ROWS DISTINCT email FROM t2 LEFT JOIN t1  ON kid = t2.id WHERE t1.id IS NULL LIMIT 10;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	system	PRIMARY,kid	NULL	NULL	NULL	0	const row not found
+1	SIMPLE	t1	system	kid	NULL	NULL	NULL	0	const row not found
 1	SIMPLE	t2	index	NULL	e_n	104	NULL	10	
 SELECT SQL_CALC_FOUND_ROWS DISTINCT email FROM t2 LEFT JOIN t1  ON kid = t2.id WHERE t1.id IS NULL LIMIT 10;
 email

=== modified file 'mysql-test/r/subquery_sj_all.result'
--- a/mysql-test/r/subquery_sj_all.result	2010-06-23 09:00:02 +0000
+++ b/mysql-test/r/subquery_sj_all.result	2010-07-09 13:35:44 +0000
@@ -4432,6 +4432,100 @@ H
 I
 drop table t1,t2,t3,t4;
 #
+# Bug#43768 Prepared query with nested subqueries core dump on second execution
+#
+CREATE TABLE t1 (
+id INT PRIMARY KEY,
+partner_id VARCHAR(35)
+);
+INSERT INTO t1 VALUES
+(1, 'partner1'), (2, 'partner2'),
+(3, 'partner3'), (4, 'partner4');
+CREATE TABLE t2 (
+id INT NOT NULL,
+t1_line_id INT,
+article_id VARCHAR(20),
+PRIMARY KEY(id, t1_line_id)
+);
+INSERT INTO t2 VALUES
+(1, 1, 'sup'), (2, 1, 'sup'),
+(2, 2, 'sup'), (2, 3, 'sup'),
+(2, 4, 'imp'), (3, 1, 'sup'),
+(4, 1, 'sup');
+CREATE TABLE t3 (
+user_id VARCHAR(50),
+article_id VARCHAR(20) NOT NULL,
+PRIMARY KEY(user_id)
+);
+INSERT INTO t3 VALUES('nicke', 'imp');
+EXPLAIN
+SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+SELECT t2.id
+FROM t2
+WHERE article_id IN (
+SELECT article_id FROM t3
+WHERE user_id = 'nicke'
+    )
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	system	PRIMARY	NULL	NULL	NULL	1	
+1	PRIMARY	t1	ALL	PRIMARY	NULL	NULL	NULL	4	
+1	PRIMARY	t2	ref	PRIMARY	PRIMARY	4	test.t1.id	1	Using where; FirstMatch(t1)
+SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+SELECT t2.id
+FROM t2
+WHERE article_id IN (
+SELECT article_id FROM t3
+WHERE user_id = 'nicke'
+    )
+);
+partner_id
+partner2
+PREPARE stmt FROM
+'EXPLAIN SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+    SELECT t2.id
+    FROM t2
+    WHERE article_id IN (
+      SELECT article_id FROM t3
+      WHERE user_id = \'nicke\'
+    )
+  )';
+EXECUTE stmt;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	system	PRIMARY	NULL	NULL	NULL	1	
+1	PRIMARY	t1	ALL	PRIMARY	NULL	NULL	NULL	4	
+1	PRIMARY	t2	ref	PRIMARY	PRIMARY	4	test.t1.id	1	Using where; FirstMatch(t1)
+EXECUTE stmt;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t3	system	PRIMARY	NULL	NULL	NULL	1	
+1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	4	
+1	SIMPLE	t2	ref	PRIMARY	PRIMARY	4	test.t1.id	1	Using where; FirstMatch(t1)
+PREPARE stmt FROM
+'SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+    SELECT t2.id
+    FROM t2
+    WHERE article_id IN (
+      SELECT article_id FROM t3
+      WHERE user_id = \'nicke\'
+    )
+  )';
+EXECUTE stmt;
+partner_id
+partner2
+EXECUTE stmt;
+partner_id
+partner2
+DROP TABLE t1,t2,t3;
+# End of Bug#43768
+#
 # Bug#53058 - semijoin execution of subquery with outerjoin yields wrong result
 #
 CREATE TABLE t1 (i INTEGER);

=== modified file 'mysql-test/r/subquery_sj_all_jcl6.result'
--- a/mysql-test/r/subquery_sj_all_jcl6.result	2010-06-23 09:00:02 +0000
+++ b/mysql-test/r/subquery_sj_all_jcl6.result	2010-07-09 13:35:44 +0000
@@ -4435,6 +4435,100 @@ H
 I
 drop table t1,t2,t3,t4;
 #
+# Bug#43768 Prepared query with nested subqueries core dump on second execution
+#
+CREATE TABLE t1 (
+id INT PRIMARY KEY,
+partner_id VARCHAR(35)
+);
+INSERT INTO t1 VALUES
+(1, 'partner1'), (2, 'partner2'),
+(3, 'partner3'), (4, 'partner4');
+CREATE TABLE t2 (
+id INT NOT NULL,
+t1_line_id INT,
+article_id VARCHAR(20),
+PRIMARY KEY(id, t1_line_id)
+);
+INSERT INTO t2 VALUES
+(1, 1, 'sup'), (2, 1, 'sup'),
+(2, 2, 'sup'), (2, 3, 'sup'),
+(2, 4, 'imp'), (3, 1, 'sup'),
+(4, 1, 'sup');
+CREATE TABLE t3 (
+user_id VARCHAR(50),
+article_id VARCHAR(20) NOT NULL,
+PRIMARY KEY(user_id)
+);
+INSERT INTO t3 VALUES('nicke', 'imp');
+EXPLAIN
+SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+SELECT t2.id
+FROM t2
+WHERE article_id IN (
+SELECT article_id FROM t3
+WHERE user_id = 'nicke'
+    )
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	system	PRIMARY	NULL	NULL	NULL	1	
+1	PRIMARY	t1	ALL	PRIMARY	NULL	NULL	NULL	4	
+1	PRIMARY	t2	ref	PRIMARY	PRIMARY	4	test.t1.id	1	Using where; FirstMatch(t1); Using join buffer (BKA, incremental buffers)
+SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+SELECT t2.id
+FROM t2
+WHERE article_id IN (
+SELECT article_id FROM t3
+WHERE user_id = 'nicke'
+    )
+);
+partner_id
+partner2
+PREPARE stmt FROM
+'EXPLAIN SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+    SELECT t2.id
+    FROM t2
+    WHERE article_id IN (
+      SELECT article_id FROM t3
+      WHERE user_id = \'nicke\'
+    )
+  )';
+EXECUTE stmt;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	system	PRIMARY	NULL	NULL	NULL	1	
+1	PRIMARY	t1	ALL	PRIMARY	NULL	NULL	NULL	4	
+1	PRIMARY	t2	ref	PRIMARY	PRIMARY	4	test.t1.id	1	Using where; FirstMatch(t1); Using join buffer (BKA, incremental buffers)
+EXECUTE stmt;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t3	system	PRIMARY	NULL	NULL	NULL	1	
+1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	4	
+1	SIMPLE	t2	ref	PRIMARY	PRIMARY	4	test.t1.id	1	Using where; FirstMatch(t1); Using join buffer (BKA, incremental buffers)
+PREPARE stmt FROM
+'SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+    SELECT t2.id
+    FROM t2
+    WHERE article_id IN (
+      SELECT article_id FROM t3
+      WHERE user_id = \'nicke\'
+    )
+  )';
+EXECUTE stmt;
+partner_id
+partner2
+EXECUTE stmt;
+partner_id
+partner2
+DROP TABLE t1,t2,t3;
+# End of Bug#43768
+#
 # Bug#53058 - semijoin execution of subquery with outerjoin yields wrong result
 #
 CREATE TABLE t1 (i INTEGER);

=== modified file 'mysql-test/r/subquery_sj_all_jcl7.result'
--- a/mysql-test/r/subquery_sj_all_jcl7.result	2010-06-23 09:00:02 +0000
+++ b/mysql-test/r/subquery_sj_all_jcl7.result	2010-07-09 13:35:44 +0000
@@ -4435,6 +4435,100 @@ H
 I
 drop table t1,t2,t3,t4;
 #
+# Bug#43768 Prepared query with nested subqueries core dump on second execution
+#
+CREATE TABLE t1 (
+id INT PRIMARY KEY,
+partner_id VARCHAR(35)
+);
+INSERT INTO t1 VALUES
+(1, 'partner1'), (2, 'partner2'),
+(3, 'partner3'), (4, 'partner4');
+CREATE TABLE t2 (
+id INT NOT NULL,
+t1_line_id INT,
+article_id VARCHAR(20),
+PRIMARY KEY(id, t1_line_id)
+);
+INSERT INTO t2 VALUES
+(1, 1, 'sup'), (2, 1, 'sup'),
+(2, 2, 'sup'), (2, 3, 'sup'),
+(2, 4, 'imp'), (3, 1, 'sup'),
+(4, 1, 'sup');
+CREATE TABLE t3 (
+user_id VARCHAR(50),
+article_id VARCHAR(20) NOT NULL,
+PRIMARY KEY(user_id)
+);
+INSERT INTO t3 VALUES('nicke', 'imp');
+EXPLAIN
+SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+SELECT t2.id
+FROM t2
+WHERE article_id IN (
+SELECT article_id FROM t3
+WHERE user_id = 'nicke'
+    )
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	system	PRIMARY	NULL	NULL	NULL	1	
+1	PRIMARY	t1	ALL	PRIMARY	NULL	NULL	NULL	4	
+1	PRIMARY	t2	ref	PRIMARY	PRIMARY	4	test.t1.id	1	Using where; FirstMatch(t1); Using join buffer (BKA_UNIQUE, regular buffers)
+SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+SELECT t2.id
+FROM t2
+WHERE article_id IN (
+SELECT article_id FROM t3
+WHERE user_id = 'nicke'
+    )
+);
+partner_id
+partner2
+PREPARE stmt FROM
+'EXPLAIN SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+    SELECT t2.id
+    FROM t2
+    WHERE article_id IN (
+      SELECT article_id FROM t3
+      WHERE user_id = \'nicke\'
+    )
+  )';
+EXECUTE stmt;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	system	PRIMARY	NULL	NULL	NULL	1	
+1	PRIMARY	t1	ALL	PRIMARY	NULL	NULL	NULL	4	
+1	PRIMARY	t2	ref	PRIMARY	PRIMARY	4	test.t1.id	1	Using where; FirstMatch(t1); Using join buffer (BKA_UNIQUE, regular buffers)
+EXECUTE stmt;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t3	system	PRIMARY	NULL	NULL	NULL	1	
+1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	4	
+1	SIMPLE	t2	ref	PRIMARY	PRIMARY	4	test.t1.id	1	Using where; FirstMatch(t1); Using join buffer (BKA_UNIQUE, regular buffers)
+PREPARE stmt FROM
+'SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+    SELECT t2.id
+    FROM t2
+    WHERE article_id IN (
+      SELECT article_id FROM t3
+      WHERE user_id = \'nicke\'
+    )
+  )';
+EXECUTE stmt;
+partner_id
+partner2
+EXECUTE stmt;
+partner_id
+partner2
+DROP TABLE t1,t2,t3;
+# End of Bug#43768
+#
 # Bug#53058 - semijoin execution of subquery with outerjoin yields wrong result
 #
 CREATE TABLE t1 (i INTEGER);

=== modified file 'mysql-test/r/subquery_sj_dupsweed.result'
--- a/mysql-test/r/subquery_sj_dupsweed.result	2010-06-23 09:00:02 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed.result	2010-07-09 13:35:44 +0000
@@ -4431,6 +4431,100 @@ H
 I
 drop table t1,t2,t3,t4;
 #
+# Bug#43768 Prepared query with nested subqueries core dump on second execution
+#
+CREATE TABLE t1 (
+id INT PRIMARY KEY,
+partner_id VARCHAR(35)
+);
+INSERT INTO t1 VALUES
+(1, 'partner1'), (2, 'partner2'),
+(3, 'partner3'), (4, 'partner4');
+CREATE TABLE t2 (
+id INT NOT NULL,
+t1_line_id INT,
+article_id VARCHAR(20),
+PRIMARY KEY(id, t1_line_id)
+);
+INSERT INTO t2 VALUES
+(1, 1, 'sup'), (2, 1, 'sup'),
+(2, 2, 'sup'), (2, 3, 'sup'),
+(2, 4, 'imp'), (3, 1, 'sup'),
+(4, 1, 'sup');
+CREATE TABLE t3 (
+user_id VARCHAR(50),
+article_id VARCHAR(20) NOT NULL,
+PRIMARY KEY(user_id)
+);
+INSERT INTO t3 VALUES('nicke', 'imp');
+EXPLAIN
+SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+SELECT t2.id
+FROM t2
+WHERE article_id IN (
+SELECT article_id FROM t3
+WHERE user_id = 'nicke'
+    )
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	system	PRIMARY	NULL	NULL	NULL	1	
+1	PRIMARY	t1	ALL	PRIMARY	NULL	NULL	NULL	4	
+1	PRIMARY	t2	ref	PRIMARY	PRIMARY	4	test.t1.id	1	Using where; Start temporary; End temporary
+SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+SELECT t2.id
+FROM t2
+WHERE article_id IN (
+SELECT article_id FROM t3
+WHERE user_id = 'nicke'
+    )
+);
+partner_id
+partner2
+PREPARE stmt FROM
+'EXPLAIN SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+    SELECT t2.id
+    FROM t2
+    WHERE article_id IN (
+      SELECT article_id FROM t3
+      WHERE user_id = \'nicke\'
+    )
+  )';
+EXECUTE stmt;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	system	PRIMARY	NULL	NULL	NULL	1	
+1	PRIMARY	t1	ALL	PRIMARY	NULL	NULL	NULL	4	
+1	PRIMARY	t2	ref	PRIMARY	PRIMARY	4	test.t1.id	1	Using where; Start temporary; End temporary
+EXECUTE stmt;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t3	system	PRIMARY	NULL	NULL	NULL	1	
+1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	4	
+1	SIMPLE	t2	ref	PRIMARY	PRIMARY	4	test.t1.id	1	Using where; Start temporary; End temporary
+PREPARE stmt FROM
+'SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+    SELECT t2.id
+    FROM t2
+    WHERE article_id IN (
+      SELECT article_id FROM t3
+      WHERE user_id = \'nicke\'
+    )
+  )';
+EXECUTE stmt;
+partner_id
+partner2
+EXECUTE stmt;
+partner_id
+partner2
+DROP TABLE t1,t2,t3;
+# End of Bug#43768
+#
 # Bug#53058 - semijoin execution of subquery with outerjoin yields wrong result
 #
 CREATE TABLE t1 (i INTEGER);

=== modified file 'mysql-test/r/subquery_sj_dupsweed_jcl6.result'
--- a/mysql-test/r/subquery_sj_dupsweed_jcl6.result	2010-06-23 09:00:02 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_jcl6.result	2010-07-09 13:35:44 +0000
@@ -4435,6 +4435,100 @@ H
 I
 drop table t1,t2,t3,t4;
 #
+# Bug#43768 Prepared query with nested subqueries core dump on second execution
+#
+CREATE TABLE t1 (
+id INT PRIMARY KEY,
+partner_id VARCHAR(35)
+);
+INSERT INTO t1 VALUES
+(1, 'partner1'), (2, 'partner2'),
+(3, 'partner3'), (4, 'partner4');
+CREATE TABLE t2 (
+id INT NOT NULL,
+t1_line_id INT,
+article_id VARCHAR(20),
+PRIMARY KEY(id, t1_line_id)
+);
+INSERT INTO t2 VALUES
+(1, 1, 'sup'), (2, 1, 'sup'),
+(2, 2, 'sup'), (2, 3, 'sup'),
+(2, 4, 'imp'), (3, 1, 'sup'),
+(4, 1, 'sup');
+CREATE TABLE t3 (
+user_id VARCHAR(50),
+article_id VARCHAR(20) NOT NULL,
+PRIMARY KEY(user_id)
+);
+INSERT INTO t3 VALUES('nicke', 'imp');
+EXPLAIN
+SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+SELECT t2.id
+FROM t2
+WHERE article_id IN (
+SELECT article_id FROM t3
+WHERE user_id = 'nicke'
+    )
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	system	PRIMARY	NULL	NULL	NULL	1	
+1	PRIMARY	t1	ALL	PRIMARY	NULL	NULL	NULL	4	
+1	PRIMARY	t2	ref	PRIMARY	PRIMARY	4	test.t1.id	1	Using where; Start temporary; End temporary
+SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+SELECT t2.id
+FROM t2
+WHERE article_id IN (
+SELECT article_id FROM t3
+WHERE user_id = 'nicke'
+    )
+);
+partner_id
+partner2
+PREPARE stmt FROM
+'EXPLAIN SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+    SELECT t2.id
+    FROM t2
+    WHERE article_id IN (
+      SELECT article_id FROM t3
+      WHERE user_id = \'nicke\'
+    )
+  )';
+EXECUTE stmt;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	system	PRIMARY	NULL	NULL	NULL	1	
+1	PRIMARY	t1	ALL	PRIMARY	NULL	NULL	NULL	4	
+1	PRIMARY	t2	ref	PRIMARY	PRIMARY	4	test.t1.id	1	Using where; Start temporary; End temporary
+EXECUTE stmt;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t3	system	PRIMARY	NULL	NULL	NULL	1	
+1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	4	
+1	SIMPLE	t2	ref	PRIMARY	PRIMARY	4	test.t1.id	1	Using where; Start temporary; End temporary
+PREPARE stmt FROM
+'SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+    SELECT t2.id
+    FROM t2
+    WHERE article_id IN (
+      SELECT article_id FROM t3
+      WHERE user_id = \'nicke\'
+    )
+  )';
+EXECUTE stmt;
+partner_id
+partner2
+EXECUTE stmt;
+partner_id
+partner2
+DROP TABLE t1,t2,t3;
+# End of Bug#43768
+#
 # Bug#53058 - semijoin execution of subquery with outerjoin yields wrong result
 #
 CREATE TABLE t1 (i INTEGER);

=== modified file 'mysql-test/r/subquery_sj_dupsweed_jcl7.result'
--- a/mysql-test/r/subquery_sj_dupsweed_jcl7.result	2010-06-23 09:00:02 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_jcl7.result	2010-07-09 13:35:44 +0000
@@ -4435,6 +4435,100 @@ H
 I
 drop table t1,t2,t3,t4;
 #
+# Bug#43768 Prepared query with nested subqueries core dump on second execution
+#
+CREATE TABLE t1 (
+id INT PRIMARY KEY,
+partner_id VARCHAR(35)
+);
+INSERT INTO t1 VALUES
+(1, 'partner1'), (2, 'partner2'),
+(3, 'partner3'), (4, 'partner4');
+CREATE TABLE t2 (
+id INT NOT NULL,
+t1_line_id INT,
+article_id VARCHAR(20),
+PRIMARY KEY(id, t1_line_id)
+);
+INSERT INTO t2 VALUES
+(1, 1, 'sup'), (2, 1, 'sup'),
+(2, 2, 'sup'), (2, 3, 'sup'),
+(2, 4, 'imp'), (3, 1, 'sup'),
+(4, 1, 'sup');
+CREATE TABLE t3 (
+user_id VARCHAR(50),
+article_id VARCHAR(20) NOT NULL,
+PRIMARY KEY(user_id)
+);
+INSERT INTO t3 VALUES('nicke', 'imp');
+EXPLAIN
+SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+SELECT t2.id
+FROM t2
+WHERE article_id IN (
+SELECT article_id FROM t3
+WHERE user_id = 'nicke'
+    )
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	system	PRIMARY	NULL	NULL	NULL	1	
+1	PRIMARY	t1	ALL	PRIMARY	NULL	NULL	NULL	4	
+1	PRIMARY	t2	ref	PRIMARY	PRIMARY	4	test.t1.id	1	Using where; Start temporary; End temporary
+SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+SELECT t2.id
+FROM t2
+WHERE article_id IN (
+SELECT article_id FROM t3
+WHERE user_id = 'nicke'
+    )
+);
+partner_id
+partner2
+PREPARE stmt FROM
+'EXPLAIN SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+    SELECT t2.id
+    FROM t2
+    WHERE article_id IN (
+      SELECT article_id FROM t3
+      WHERE user_id = \'nicke\'
+    )
+  )';
+EXECUTE stmt;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	system	PRIMARY	NULL	NULL	NULL	1	
+1	PRIMARY	t1	ALL	PRIMARY	NULL	NULL	NULL	4	
+1	PRIMARY	t2	ref	PRIMARY	PRIMARY	4	test.t1.id	1	Using where; Start temporary; End temporary
+EXECUTE stmt;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t3	system	PRIMARY	NULL	NULL	NULL	1	
+1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	4	
+1	SIMPLE	t2	ref	PRIMARY	PRIMARY	4	test.t1.id	1	Using where; Start temporary; End temporary
+PREPARE stmt FROM
+'SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+    SELECT t2.id
+    FROM t2
+    WHERE article_id IN (
+      SELECT article_id FROM t3
+      WHERE user_id = \'nicke\'
+    )
+  )';
+EXECUTE stmt;
+partner_id
+partner2
+EXECUTE stmt;
+partner_id
+partner2
+DROP TABLE t1,t2,t3;
+# End of Bug#43768
+#
 # Bug#53058 - semijoin execution of subquery with outerjoin yields wrong result
 #
 CREATE TABLE t1 (i INTEGER);

=== modified file 'mysql-test/r/subquery_sj_firstmatch.result'
--- a/mysql-test/r/subquery_sj_firstmatch.result	2010-06-23 09:00:02 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch.result	2010-07-09 13:35:44 +0000
@@ -4432,6 +4432,100 @@ H
 I
 drop table t1,t2,t3,t4;
 #
+# Bug#43768 Prepared query with nested subqueries core dump on second execution
+#
+CREATE TABLE t1 (
+id INT PRIMARY KEY,
+partner_id VARCHAR(35)
+);
+INSERT INTO t1 VALUES
+(1, 'partner1'), (2, 'partner2'),
+(3, 'partner3'), (4, 'partner4');
+CREATE TABLE t2 (
+id INT NOT NULL,
+t1_line_id INT,
+article_id VARCHAR(20),
+PRIMARY KEY(id, t1_line_id)
+);
+INSERT INTO t2 VALUES
+(1, 1, 'sup'), (2, 1, 'sup'),
+(2, 2, 'sup'), (2, 3, 'sup'),
+(2, 4, 'imp'), (3, 1, 'sup'),
+(4, 1, 'sup');
+CREATE TABLE t3 (
+user_id VARCHAR(50),
+article_id VARCHAR(20) NOT NULL,
+PRIMARY KEY(user_id)
+);
+INSERT INTO t3 VALUES('nicke', 'imp');
+EXPLAIN
+SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+SELECT t2.id
+FROM t2
+WHERE article_id IN (
+SELECT article_id FROM t3
+WHERE user_id = 'nicke'
+    )
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	system	PRIMARY	NULL	NULL	NULL	1	
+1	PRIMARY	t1	ALL	PRIMARY	NULL	NULL	NULL	4	
+1	PRIMARY	t2	ref	PRIMARY	PRIMARY	4	test.t1.id	1	Using where; FirstMatch(t1)
+SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+SELECT t2.id
+FROM t2
+WHERE article_id IN (
+SELECT article_id FROM t3
+WHERE user_id = 'nicke'
+    )
+);
+partner_id
+partner2
+PREPARE stmt FROM
+'EXPLAIN SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+    SELECT t2.id
+    FROM t2
+    WHERE article_id IN (
+      SELECT article_id FROM t3
+      WHERE user_id = \'nicke\'
+    )
+  )';
+EXECUTE stmt;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	system	PRIMARY	NULL	NULL	NULL	1	
+1	PRIMARY	t1	ALL	PRIMARY	NULL	NULL	NULL	4	
+1	PRIMARY	t2	ref	PRIMARY	PRIMARY	4	test.t1.id	1	Using where; FirstMatch(t1)
+EXECUTE stmt;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t3	system	PRIMARY	NULL	NULL	NULL	1	
+1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	4	
+1	SIMPLE	t2	ref	PRIMARY	PRIMARY	4	test.t1.id	1	Using where; FirstMatch(t1)
+PREPARE stmt FROM
+'SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+    SELECT t2.id
+    FROM t2
+    WHERE article_id IN (
+      SELECT article_id FROM t3
+      WHERE user_id = \'nicke\'
+    )
+  )';
+EXECUTE stmt;
+partner_id
+partner2
+EXECUTE stmt;
+partner_id
+partner2
+DROP TABLE t1,t2,t3;
+# End of Bug#43768
+#
 # Bug#53058 - semijoin execution of subquery with outerjoin yields wrong result
 #
 CREATE TABLE t1 (i INTEGER);

=== modified file 'mysql-test/r/subquery_sj_firstmatch_jcl6.result'
--- a/mysql-test/r/subquery_sj_firstmatch_jcl6.result	2010-06-23 09:00:02 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_jcl6.result	2010-07-09 13:35:44 +0000
@@ -4436,6 +4436,100 @@ H
 I
 drop table t1,t2,t3,t4;
 #
+# Bug#43768 Prepared query with nested subqueries core dump on second execution
+#
+CREATE TABLE t1 (
+id INT PRIMARY KEY,
+partner_id VARCHAR(35)
+);
+INSERT INTO t1 VALUES
+(1, 'partner1'), (2, 'partner2'),
+(3, 'partner3'), (4, 'partner4');
+CREATE TABLE t2 (
+id INT NOT NULL,
+t1_line_id INT,
+article_id VARCHAR(20),
+PRIMARY KEY(id, t1_line_id)
+);
+INSERT INTO t2 VALUES
+(1, 1, 'sup'), (2, 1, 'sup'),
+(2, 2, 'sup'), (2, 3, 'sup'),
+(2, 4, 'imp'), (3, 1, 'sup'),
+(4, 1, 'sup');
+CREATE TABLE t3 (
+user_id VARCHAR(50),
+article_id VARCHAR(20) NOT NULL,
+PRIMARY KEY(user_id)
+);
+INSERT INTO t3 VALUES('nicke', 'imp');
+EXPLAIN
+SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+SELECT t2.id
+FROM t2
+WHERE article_id IN (
+SELECT article_id FROM t3
+WHERE user_id = 'nicke'
+    )
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	system	PRIMARY	NULL	NULL	NULL	1	
+1	PRIMARY	t1	ALL	PRIMARY	NULL	NULL	NULL	4	
+1	PRIMARY	t2	ref	PRIMARY	PRIMARY	4	test.t1.id	1	Using where; FirstMatch(t1)
+SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+SELECT t2.id
+FROM t2
+WHERE article_id IN (
+SELECT article_id FROM t3
+WHERE user_id = 'nicke'
+    )
+);
+partner_id
+partner2
+PREPARE stmt FROM
+'EXPLAIN SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+    SELECT t2.id
+    FROM t2
+    WHERE article_id IN (
+      SELECT article_id FROM t3
+      WHERE user_id = \'nicke\'
+    )
+  )';
+EXECUTE stmt;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	system	PRIMARY	NULL	NULL	NULL	1	
+1	PRIMARY	t1	ALL	PRIMARY	NULL	NULL	NULL	4	
+1	PRIMARY	t2	ref	PRIMARY	PRIMARY	4	test.t1.id	1	Using where; FirstMatch(t1)
+EXECUTE stmt;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t3	system	PRIMARY	NULL	NULL	NULL	1	
+1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	4	
+1	SIMPLE	t2	ref	PRIMARY	PRIMARY	4	test.t1.id	1	Using where; FirstMatch(t1)
+PREPARE stmt FROM
+'SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+    SELECT t2.id
+    FROM t2
+    WHERE article_id IN (
+      SELECT article_id FROM t3
+      WHERE user_id = \'nicke\'
+    )
+  )';
+EXECUTE stmt;
+partner_id
+partner2
+EXECUTE stmt;
+partner_id
+partner2
+DROP TABLE t1,t2,t3;
+# End of Bug#43768
+#
 # Bug#53058 - semijoin execution of subquery with outerjoin yields wrong result
 #
 CREATE TABLE t1 (i INTEGER);

=== modified file 'mysql-test/r/subquery_sj_firstmatch_jcl7.result'
--- a/mysql-test/r/subquery_sj_firstmatch_jcl7.result	2010-06-23 09:00:02 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_jcl7.result	2010-07-09 13:35:44 +0000
@@ -4436,6 +4436,100 @@ H
 I
 drop table t1,t2,t3,t4;
 #
+# Bug#43768 Prepared query with nested subqueries core dump on second execution
+#
+CREATE TABLE t1 (
+id INT PRIMARY KEY,
+partner_id VARCHAR(35)
+);
+INSERT INTO t1 VALUES
+(1, 'partner1'), (2, 'partner2'),
+(3, 'partner3'), (4, 'partner4');
+CREATE TABLE t2 (
+id INT NOT NULL,
+t1_line_id INT,
+article_id VARCHAR(20),
+PRIMARY KEY(id, t1_line_id)
+);
+INSERT INTO t2 VALUES
+(1, 1, 'sup'), (2, 1, 'sup'),
+(2, 2, 'sup'), (2, 3, 'sup'),
+(2, 4, 'imp'), (3, 1, 'sup'),
+(4, 1, 'sup');
+CREATE TABLE t3 (
+user_id VARCHAR(50),
+article_id VARCHAR(20) NOT NULL,
+PRIMARY KEY(user_id)
+);
+INSERT INTO t3 VALUES('nicke', 'imp');
+EXPLAIN
+SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+SELECT t2.id
+FROM t2
+WHERE article_id IN (
+SELECT article_id FROM t3
+WHERE user_id = 'nicke'
+    )
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	system	PRIMARY	NULL	NULL	NULL	1	
+1	PRIMARY	t1	ALL	PRIMARY	NULL	NULL	NULL	4	
+1	PRIMARY	t2	ref	PRIMARY	PRIMARY	4	test.t1.id	1	Using where; FirstMatch(t1)
+SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+SELECT t2.id
+FROM t2
+WHERE article_id IN (
+SELECT article_id FROM t3
+WHERE user_id = 'nicke'
+    )
+);
+partner_id
+partner2
+PREPARE stmt FROM
+'EXPLAIN SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+    SELECT t2.id
+    FROM t2
+    WHERE article_id IN (
+      SELECT article_id FROM t3
+      WHERE user_id = \'nicke\'
+    )
+  )';
+EXECUTE stmt;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	system	PRIMARY	NULL	NULL	NULL	1	
+1	PRIMARY	t1	ALL	PRIMARY	NULL	NULL	NULL	4	
+1	PRIMARY	t2	ref	PRIMARY	PRIMARY	4	test.t1.id	1	Using where; FirstMatch(t1)
+EXECUTE stmt;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t3	system	PRIMARY	NULL	NULL	NULL	1	
+1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	4	
+1	SIMPLE	t2	ref	PRIMARY	PRIMARY	4	test.t1.id	1	Using where; FirstMatch(t1)
+PREPARE stmt FROM
+'SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+    SELECT t2.id
+    FROM t2
+    WHERE article_id IN (
+      SELECT article_id FROM t3
+      WHERE user_id = \'nicke\'
+    )
+  )';
+EXECUTE stmt;
+partner_id
+partner2
+EXECUTE stmt;
+partner_id
+partner2
+DROP TABLE t1,t2,t3;
+# End of Bug#43768
+#
 # Bug#53058 - semijoin execution of subquery with outerjoin yields wrong result
 #
 CREATE TABLE t1 (i INTEGER);

=== modified file 'mysql-test/r/subquery_sj_loosescan.result'
--- a/mysql-test/r/subquery_sj_loosescan.result	2010-06-23 09:00:02 +0000
+++ b/mysql-test/r/subquery_sj_loosescan.result	2010-07-09 13:35:44 +0000
@@ -4436,6 +4436,100 @@ H
 I
 drop table t1,t2,t3,t4;
 #
+# Bug#43768 Prepared query with nested subqueries core dump on second execution
+#
+CREATE TABLE t1 (
+id INT PRIMARY KEY,
+partner_id VARCHAR(35)
+);
+INSERT INTO t1 VALUES
+(1, 'partner1'), (2, 'partner2'),
+(3, 'partner3'), (4, 'partner4');
+CREATE TABLE t2 (
+id INT NOT NULL,
+t1_line_id INT,
+article_id VARCHAR(20),
+PRIMARY KEY(id, t1_line_id)
+);
+INSERT INTO t2 VALUES
+(1, 1, 'sup'), (2, 1, 'sup'),
+(2, 2, 'sup'), (2, 3, 'sup'),
+(2, 4, 'imp'), (3, 1, 'sup'),
+(4, 1, 'sup');
+CREATE TABLE t3 (
+user_id VARCHAR(50),
+article_id VARCHAR(20) NOT NULL,
+PRIMARY KEY(user_id)
+);
+INSERT INTO t3 VALUES('nicke', 'imp');
+EXPLAIN
+SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+SELECT t2.id
+FROM t2
+WHERE article_id IN (
+SELECT article_id FROM t3
+WHERE user_id = 'nicke'
+    )
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	system	PRIMARY	NULL	NULL	NULL	1	
+1	PRIMARY	t1	ALL	PRIMARY	NULL	NULL	NULL	4	
+1	PRIMARY	t2	ref	PRIMARY	PRIMARY	4	test.t1.id	1	Using where; Start temporary; End temporary
+SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+SELECT t2.id
+FROM t2
+WHERE article_id IN (
+SELECT article_id FROM t3
+WHERE user_id = 'nicke'
+    )
+);
+partner_id
+partner2
+PREPARE stmt FROM
+'EXPLAIN SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+    SELECT t2.id
+    FROM t2
+    WHERE article_id IN (
+      SELECT article_id FROM t3
+      WHERE user_id = \'nicke\'
+    )
+  )';
+EXECUTE stmt;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	system	PRIMARY	NULL	NULL	NULL	1	
+1	PRIMARY	t1	ALL	PRIMARY	NULL	NULL	NULL	4	
+1	PRIMARY	t2	ref	PRIMARY	PRIMARY	4	test.t1.id	1	Using where; Start temporary; End temporary
+EXECUTE stmt;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t3	system	PRIMARY	NULL	NULL	NULL	1	
+1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	4	
+1	SIMPLE	t2	ref	PRIMARY	PRIMARY	4	test.t1.id	1	Using where; Start temporary; End temporary
+PREPARE stmt FROM
+'SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+    SELECT t2.id
+    FROM t2
+    WHERE article_id IN (
+      SELECT article_id FROM t3
+      WHERE user_id = \'nicke\'
+    )
+  )';
+EXECUTE stmt;
+partner_id
+partner2
+EXECUTE stmt;
+partner_id
+partner2
+DROP TABLE t1,t2,t3;
+# End of Bug#43768
+#
 # Bug#53058 - semijoin execution of subquery with outerjoin yields wrong result
 #
 CREATE TABLE t1 (i INTEGER);

=== modified file 'mysql-test/r/subquery_sj_loosescan_jcl6.result'
--- a/mysql-test/r/subquery_sj_loosescan_jcl6.result	2010-06-23 09:00:02 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_jcl6.result	2010-07-09 13:35:44 +0000
@@ -4440,6 +4440,100 @@ H
 I
 drop table t1,t2,t3,t4;
 #
+# Bug#43768 Prepared query with nested subqueries core dump on second execution
+#
+CREATE TABLE t1 (
+id INT PRIMARY KEY,
+partner_id VARCHAR(35)
+);
+INSERT INTO t1 VALUES
+(1, 'partner1'), (2, 'partner2'),
+(3, 'partner3'), (4, 'partner4');
+CREATE TABLE t2 (
+id INT NOT NULL,
+t1_line_id INT,
+article_id VARCHAR(20),
+PRIMARY KEY(id, t1_line_id)
+);
+INSERT INTO t2 VALUES
+(1, 1, 'sup'), (2, 1, 'sup'),
+(2, 2, 'sup'), (2, 3, 'sup'),
+(2, 4, 'imp'), (3, 1, 'sup'),
+(4, 1, 'sup');
+CREATE TABLE t3 (
+user_id VARCHAR(50),
+article_id VARCHAR(20) NOT NULL,
+PRIMARY KEY(user_id)
+);
+INSERT INTO t3 VALUES('nicke', 'imp');
+EXPLAIN
+SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+SELECT t2.id
+FROM t2
+WHERE article_id IN (
+SELECT article_id FROM t3
+WHERE user_id = 'nicke'
+    )
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	system	PRIMARY	NULL	NULL	NULL	1	
+1	PRIMARY	t1	ALL	PRIMARY	NULL	NULL	NULL	4	
+1	PRIMARY	t2	ref	PRIMARY	PRIMARY	4	test.t1.id	1	Using where; Start temporary; End temporary
+SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+SELECT t2.id
+FROM t2
+WHERE article_id IN (
+SELECT article_id FROM t3
+WHERE user_id = 'nicke'
+    )
+);
+partner_id
+partner2
+PREPARE stmt FROM
+'EXPLAIN SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+    SELECT t2.id
+    FROM t2
+    WHERE article_id IN (
+      SELECT article_id FROM t3
+      WHERE user_id = \'nicke\'
+    )
+  )';
+EXECUTE stmt;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	system	PRIMARY	NULL	NULL	NULL	1	
+1	PRIMARY	t1	ALL	PRIMARY	NULL	NULL	NULL	4	
+1	PRIMARY	t2	ref	PRIMARY	PRIMARY	4	test.t1.id	1	Using where; Start temporary; End temporary
+EXECUTE stmt;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t3	system	PRIMARY	NULL	NULL	NULL	1	
+1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	4	
+1	SIMPLE	t2	ref	PRIMARY	PRIMARY	4	test.t1.id	1	Using where; Start temporary; End temporary
+PREPARE stmt FROM
+'SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+    SELECT t2.id
+    FROM t2
+    WHERE article_id IN (
+      SELECT article_id FROM t3
+      WHERE user_id = \'nicke\'
+    )
+  )';
+EXECUTE stmt;
+partner_id
+partner2
+EXECUTE stmt;
+partner_id
+partner2
+DROP TABLE t1,t2,t3;
+# End of Bug#43768
+#
 # Bug#53058 - semijoin execution of subquery with outerjoin yields wrong result
 #
 CREATE TABLE t1 (i INTEGER);

=== modified file 'mysql-test/r/subquery_sj_loosescan_jcl7.result'
--- a/mysql-test/r/subquery_sj_loosescan_jcl7.result	2010-06-23 09:00:02 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_jcl7.result	2010-07-09 13:35:44 +0000
@@ -4440,6 +4440,100 @@ H
 I
 drop table t1,t2,t3,t4;
 #
+# Bug#43768 Prepared query with nested subqueries core dump on second execution
+#
+CREATE TABLE t1 (
+id INT PRIMARY KEY,
+partner_id VARCHAR(35)
+);
+INSERT INTO t1 VALUES
+(1, 'partner1'), (2, 'partner2'),
+(3, 'partner3'), (4, 'partner4');
+CREATE TABLE t2 (
+id INT NOT NULL,
+t1_line_id INT,
+article_id VARCHAR(20),
+PRIMARY KEY(id, t1_line_id)
+);
+INSERT INTO t2 VALUES
+(1, 1, 'sup'), (2, 1, 'sup'),
+(2, 2, 'sup'), (2, 3, 'sup'),
+(2, 4, 'imp'), (3, 1, 'sup'),
+(4, 1, 'sup');
+CREATE TABLE t3 (
+user_id VARCHAR(50),
+article_id VARCHAR(20) NOT NULL,
+PRIMARY KEY(user_id)
+);
+INSERT INTO t3 VALUES('nicke', 'imp');
+EXPLAIN
+SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+SELECT t2.id
+FROM t2
+WHERE article_id IN (
+SELECT article_id FROM t3
+WHERE user_id = 'nicke'
+    )
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	system	PRIMARY	NULL	NULL	NULL	1	
+1	PRIMARY	t1	ALL	PRIMARY	NULL	NULL	NULL	4	
+1	PRIMARY	t2	ref	PRIMARY	PRIMARY	4	test.t1.id	1	Using where; Start temporary; End temporary
+SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+SELECT t2.id
+FROM t2
+WHERE article_id IN (
+SELECT article_id FROM t3
+WHERE user_id = 'nicke'
+    )
+);
+partner_id
+partner2
+PREPARE stmt FROM
+'EXPLAIN SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+    SELECT t2.id
+    FROM t2
+    WHERE article_id IN (
+      SELECT article_id FROM t3
+      WHERE user_id = \'nicke\'
+    )
+  )';
+EXECUTE stmt;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	system	PRIMARY	NULL	NULL	NULL	1	
+1	PRIMARY	t1	ALL	PRIMARY	NULL	NULL	NULL	4	
+1	PRIMARY	t2	ref	PRIMARY	PRIMARY	4	test.t1.id	1	Using where; Start temporary; End temporary
+EXECUTE stmt;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t3	system	PRIMARY	NULL	NULL	NULL	1	
+1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	4	
+1	SIMPLE	t2	ref	PRIMARY	PRIMARY	4	test.t1.id	1	Using where; Start temporary; End temporary
+PREPARE stmt FROM
+'SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+    SELECT t2.id
+    FROM t2
+    WHERE article_id IN (
+      SELECT article_id FROM t3
+      WHERE user_id = \'nicke\'
+    )
+  )';
+EXECUTE stmt;
+partner_id
+partner2
+EXECUTE stmt;
+partner_id
+partner2
+DROP TABLE t1,t2,t3;
+# End of Bug#43768
+#
 # Bug#53058 - semijoin execution of subquery with outerjoin yields wrong result
 #
 CREATE TABLE t1 (i INTEGER);

=== modified file 'mysql-test/r/subquery_sj_mat.result'
--- a/mysql-test/r/subquery_sj_mat.result	2010-06-23 09:00:02 +0000
+++ b/mysql-test/r/subquery_sj_mat.result	2010-07-09 13:35:44 +0000
@@ -4432,6 +4432,100 @@ H
 I
 drop table t1,t2,t3,t4;
 #
+# Bug#43768 Prepared query with nested subqueries core dump on second execution
+#
+CREATE TABLE t1 (
+id INT PRIMARY KEY,
+partner_id VARCHAR(35)
+);
+INSERT INTO t1 VALUES
+(1, 'partner1'), (2, 'partner2'),
+(3, 'partner3'), (4, 'partner4');
+CREATE TABLE t2 (
+id INT NOT NULL,
+t1_line_id INT,
+article_id VARCHAR(20),
+PRIMARY KEY(id, t1_line_id)
+);
+INSERT INTO t2 VALUES
+(1, 1, 'sup'), (2, 1, 'sup'),
+(2, 2, 'sup'), (2, 3, 'sup'),
+(2, 4, 'imp'), (3, 1, 'sup'),
+(4, 1, 'sup');
+CREATE TABLE t3 (
+user_id VARCHAR(50),
+article_id VARCHAR(20) NOT NULL,
+PRIMARY KEY(user_id)
+);
+INSERT INTO t3 VALUES('nicke', 'imp');
+EXPLAIN
+SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+SELECT t2.id
+FROM t2
+WHERE article_id IN (
+SELECT article_id FROM t3
+WHERE user_id = 'nicke'
+    )
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	system	PRIMARY	NULL	NULL	NULL	1	
+1	PRIMARY	t1	ALL	PRIMARY	NULL	NULL	NULL	4	
+1	PRIMARY	t2	ref	PRIMARY	PRIMARY	4	test.t1.id	1	Using where; Start temporary; End temporary
+SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+SELECT t2.id
+FROM t2
+WHERE article_id IN (
+SELECT article_id FROM t3
+WHERE user_id = 'nicke'
+    )
+);
+partner_id
+partner2
+PREPARE stmt FROM
+'EXPLAIN SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+    SELECT t2.id
+    FROM t2
+    WHERE article_id IN (
+      SELECT article_id FROM t3
+      WHERE user_id = \'nicke\'
+    )
+  )';
+EXECUTE stmt;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	system	PRIMARY	NULL	NULL	NULL	1	
+1	PRIMARY	t1	ALL	PRIMARY	NULL	NULL	NULL	4	
+1	PRIMARY	t2	ref	PRIMARY	PRIMARY	4	test.t1.id	1	Using where; Start temporary; End temporary
+EXECUTE stmt;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t3	system	PRIMARY	NULL	NULL	NULL	1	
+1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	4	
+1	SIMPLE	t2	ref	PRIMARY	PRIMARY	4	test.t1.id	1	Using where; Start temporary; End temporary
+PREPARE stmt FROM
+'SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+    SELECT t2.id
+    FROM t2
+    WHERE article_id IN (
+      SELECT article_id FROM t3
+      WHERE user_id = \'nicke\'
+    )
+  )';
+EXECUTE stmt;
+partner_id
+partner2
+EXECUTE stmt;
+partner_id
+partner2
+DROP TABLE t1,t2,t3;
+# End of Bug#43768
+#
 # Bug#53058 - semijoin execution of subquery with outerjoin yields wrong result
 #
 CREATE TABLE t1 (i INTEGER);

=== modified file 'mysql-test/r/subquery_sj_mat_jcl6.result'
--- a/mysql-test/r/subquery_sj_mat_jcl6.result	2010-06-23 09:00:02 +0000
+++ b/mysql-test/r/subquery_sj_mat_jcl6.result	2010-07-09 13:35:44 +0000
@@ -4436,6 +4436,100 @@ H
 I
 drop table t1,t2,t3,t4;
 #
+# Bug#43768 Prepared query with nested subqueries core dump on second execution
+#
+CREATE TABLE t1 (
+id INT PRIMARY KEY,
+partner_id VARCHAR(35)
+);
+INSERT INTO t1 VALUES
+(1, 'partner1'), (2, 'partner2'),
+(3, 'partner3'), (4, 'partner4');
+CREATE TABLE t2 (
+id INT NOT NULL,
+t1_line_id INT,
+article_id VARCHAR(20),
+PRIMARY KEY(id, t1_line_id)
+);
+INSERT INTO t2 VALUES
+(1, 1, 'sup'), (2, 1, 'sup'),
+(2, 2, 'sup'), (2, 3, 'sup'),
+(2, 4, 'imp'), (3, 1, 'sup'),
+(4, 1, 'sup');
+CREATE TABLE t3 (
+user_id VARCHAR(50),
+article_id VARCHAR(20) NOT NULL,
+PRIMARY KEY(user_id)
+);
+INSERT INTO t3 VALUES('nicke', 'imp');
+EXPLAIN
+SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+SELECT t2.id
+FROM t2
+WHERE article_id IN (
+SELECT article_id FROM t3
+WHERE user_id = 'nicke'
+    )
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	system	PRIMARY	NULL	NULL	NULL	1	
+1	PRIMARY	t1	ALL	PRIMARY	NULL	NULL	NULL	4	
+1	PRIMARY	t2	ref	PRIMARY	PRIMARY	4	test.t1.id	1	Using where; Start temporary; End temporary
+SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+SELECT t2.id
+FROM t2
+WHERE article_id IN (
+SELECT article_id FROM t3
+WHERE user_id = 'nicke'
+    )
+);
+partner_id
+partner2
+PREPARE stmt FROM
+'EXPLAIN SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+    SELECT t2.id
+    FROM t2
+    WHERE article_id IN (
+      SELECT article_id FROM t3
+      WHERE user_id = \'nicke\'
+    )
+  )';
+EXECUTE stmt;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	system	PRIMARY	NULL	NULL	NULL	1	
+1	PRIMARY	t1	ALL	PRIMARY	NULL	NULL	NULL	4	
+1	PRIMARY	t2	ref	PRIMARY	PRIMARY	4	test.t1.id	1	Using where; Start temporary; End temporary
+EXECUTE stmt;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t3	system	PRIMARY	NULL	NULL	NULL	1	
+1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	4	
+1	SIMPLE	t2	ref	PRIMARY	PRIMARY	4	test.t1.id	1	Using where; Start temporary; End temporary
+PREPARE stmt FROM
+'SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+    SELECT t2.id
+    FROM t2
+    WHERE article_id IN (
+      SELECT article_id FROM t3
+      WHERE user_id = \'nicke\'
+    )
+  )';
+EXECUTE stmt;
+partner_id
+partner2
+EXECUTE stmt;
+partner_id
+partner2
+DROP TABLE t1,t2,t3;
+# End of Bug#43768
+#
 # Bug#53058 - semijoin execution of subquery with outerjoin yields wrong result
 #
 CREATE TABLE t1 (i INTEGER);

=== modified file 'mysql-test/r/subquery_sj_mat_jcl7.result'
--- a/mysql-test/r/subquery_sj_mat_jcl7.result	2010-06-23 09:00:02 +0000
+++ b/mysql-test/r/subquery_sj_mat_jcl7.result	2010-07-09 13:35:44 +0000
@@ -4436,6 +4436,100 @@ H
 I
 drop table t1,t2,t3,t4;
 #
+# Bug#43768 Prepared query with nested subqueries core dump on second execution
+#
+CREATE TABLE t1 (
+id INT PRIMARY KEY,
+partner_id VARCHAR(35)
+);
+INSERT INTO t1 VALUES
+(1, 'partner1'), (2, 'partner2'),
+(3, 'partner3'), (4, 'partner4');
+CREATE TABLE t2 (
+id INT NOT NULL,
+t1_line_id INT,
+article_id VARCHAR(20),
+PRIMARY KEY(id, t1_line_id)
+);
+INSERT INTO t2 VALUES
+(1, 1, 'sup'), (2, 1, 'sup'),
+(2, 2, 'sup'), (2, 3, 'sup'),
+(2, 4, 'imp'), (3, 1, 'sup'),
+(4, 1, 'sup');
+CREATE TABLE t3 (
+user_id VARCHAR(50),
+article_id VARCHAR(20) NOT NULL,
+PRIMARY KEY(user_id)
+);
+INSERT INTO t3 VALUES('nicke', 'imp');
+EXPLAIN
+SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+SELECT t2.id
+FROM t2
+WHERE article_id IN (
+SELECT article_id FROM t3
+WHERE user_id = 'nicke'
+    )
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	system	PRIMARY	NULL	NULL	NULL	1	
+1	PRIMARY	t1	ALL	PRIMARY	NULL	NULL	NULL	4	
+1	PRIMARY	t2	ref	PRIMARY	PRIMARY	4	test.t1.id	1	Using where; Start temporary; End temporary
+SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+SELECT t2.id
+FROM t2
+WHERE article_id IN (
+SELECT article_id FROM t3
+WHERE user_id = 'nicke'
+    )
+);
+partner_id
+partner2
+PREPARE stmt FROM
+'EXPLAIN SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+    SELECT t2.id
+    FROM t2
+    WHERE article_id IN (
+      SELECT article_id FROM t3
+      WHERE user_id = \'nicke\'
+    )
+  )';
+EXECUTE stmt;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	system	PRIMARY	NULL	NULL	NULL	1	
+1	PRIMARY	t1	ALL	PRIMARY	NULL	NULL	NULL	4	
+1	PRIMARY	t2	ref	PRIMARY	PRIMARY	4	test.t1.id	1	Using where; Start temporary; End temporary
+EXECUTE stmt;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t3	system	PRIMARY	NULL	NULL	NULL	1	
+1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	4	
+1	SIMPLE	t2	ref	PRIMARY	PRIMARY	4	test.t1.id	1	Using where; Start temporary; End temporary
+PREPARE stmt FROM
+'SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+    SELECT t2.id
+    FROM t2
+    WHERE article_id IN (
+      SELECT article_id FROM t3
+      WHERE user_id = \'nicke\'
+    )
+  )';
+EXECUTE stmt;
+partner_id
+partner2
+EXECUTE stmt;
+partner_id
+partner2
+DROP TABLE t1,t2,t3;
+# End of Bug#43768
+#
 # Bug#53058 - semijoin execution of subquery with outerjoin yields wrong result
 #
 CREATE TABLE t1 (i INTEGER);

=== modified file 'mysql-test/r/subquery_sj_mat_nosj.result'
--- a/mysql-test/r/subquery_sj_mat_nosj.result	2010-06-23 09:00:02 +0000
+++ b/mysql-test/r/subquery_sj_mat_nosj.result	2010-07-09 13:35:44 +0000
@@ -316,8 +316,7 @@ EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
-2	SUBQUERY	t2	system	NULL	NULL	NULL	NULL	0	const row not found
-2	SUBQUERY	t3	system	NULL	NULL	NULL	NULL	0	const row not found
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 SELECT * FROM t1 WHERE (t1.i) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -325,8 +324,7 @@ EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN
 (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
-2	SUBQUERY	t2	system	NULL	NULL	NULL	NULL	0	const row not found
-2	SUBQUERY	t3	system	NULL	NULL	NULL	NULL	0	const row not found
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 SELECT * FROM t1 WHERE (t1.i) IN 
 (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
 i
@@ -343,8 +341,7 @@ EXPLAIN SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
-2	SUBQUERY	t2	system	NULL	NULL	NULL	NULL	0	const row not found
-2	SUBQUERY	t3	system	NULL	NULL	NULL	NULL	0	const row not found
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -360,8 +357,7 @@ EXPLAIN SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
-2	SUBQUERY	t2	system	NULL	NULL	NULL	NULL	0	const row not found
-2	SUBQUERY	t3	system	NULL	NULL	NULL	NULL	0	const row not found
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
 i
@@ -928,8 +924,7 @@ EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
-2	SUBQUERY	t2	system	NULL	NULL	NULL	NULL	0	const row not found
-2	SUBQUERY	t3	system	NULL	NULL	NULL	NULL	0	const row not found
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 SELECT * FROM t1 WHERE (t1.i) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -937,8 +932,7 @@ EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN
 (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
-2	SUBQUERY	t2	system	NULL	NULL	NULL	NULL	0	const row not found
-2	SUBQUERY	t3	system	NULL	NULL	NULL	NULL	0	const row not found
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 SELECT * FROM t1 WHERE (t1.i) IN 
 (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
 i
@@ -955,8 +949,7 @@ EXPLAIN SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
-2	SUBQUERY	t2	system	NULL	NULL	NULL	NULL	0	const row not found
-2	SUBQUERY	t3	system	NULL	NULL	NULL	NULL	0	const row not found
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -972,8 +965,7 @@ EXPLAIN SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
-2	SUBQUERY	t2	system	NULL	NULL	NULL	NULL	0	const row not found
-2	SUBQUERY	t3	system	NULL	NULL	NULL	NULL	0	const row not found
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
 i
@@ -1555,8 +1547,7 @@ EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 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	SUBQUERY	t2	system	NULL	NULL	NULL	NULL	0	const row not found
-2	SUBQUERY	t3	system	NULL	NULL	NULL	NULL	0	const row not found
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 SELECT * FROM t1 WHERE (t1.i) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -1564,8 +1555,7 @@ EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN
 (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
 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	SUBQUERY	t2	system	NULL	NULL	NULL	NULL	0	const row not found
-2	SUBQUERY	t3	system	NULL	NULL	NULL	NULL	0	const row not found
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 SELECT * FROM t1 WHERE (t1.i) IN 
 (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
 i
@@ -1582,8 +1572,7 @@ EXPLAIN SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 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	SUBQUERY	t2	system	NULL	NULL	NULL	NULL	0	const row not found
-2	SUBQUERY	t3	system	NULL	NULL	NULL	NULL	0	const row not found
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -1599,8 +1588,7 @@ EXPLAIN SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
 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	SUBQUERY	t2	system	NULL	NULL	NULL	NULL	0	const row not found
-2	SUBQUERY	t3	system	NULL	NULL	NULL	NULL	0	const row not found
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
 i
@@ -4654,6 +4642,100 @@ H
 I
 drop table t1,t2,t3,t4;
 #
+# Bug#43768 Prepared query with nested subqueries core dump on second execution
+#
+CREATE TABLE t1 (
+id INT PRIMARY KEY,
+partner_id VARCHAR(35)
+);
+INSERT INTO t1 VALUES
+(1, 'partner1'), (2, 'partner2'),
+(3, 'partner3'), (4, 'partner4');
+CREATE TABLE t2 (
+id INT NOT NULL,
+t1_line_id INT,
+article_id VARCHAR(20),
+PRIMARY KEY(id, t1_line_id)
+);
+INSERT INTO t2 VALUES
+(1, 1, 'sup'), (2, 1, 'sup'),
+(2, 2, 'sup'), (2, 3, 'sup'),
+(2, 4, 'imp'), (3, 1, 'sup'),
+(4, 1, 'sup');
+CREATE TABLE t3 (
+user_id VARCHAR(50),
+article_id VARCHAR(20) NOT NULL,
+PRIMARY KEY(user_id)
+);
+INSERT INTO t3 VALUES('nicke', 'imp');
+EXPLAIN
+SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+SELECT t2.id
+FROM t2
+WHERE article_id IN (
+SELECT article_id FROM t3
+WHERE user_id = 'nicke'
+    )
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Using where
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	7	Using where
+3	SUBQUERY	t3	system	PRIMARY	NULL	NULL	NULL	1	
+SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+SELECT t2.id
+FROM t2
+WHERE article_id IN (
+SELECT article_id FROM t3
+WHERE user_id = 'nicke'
+    )
+);
+partner_id
+partner2
+PREPARE stmt FROM
+'EXPLAIN SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+    SELECT t2.id
+    FROM t2
+    WHERE article_id IN (
+      SELECT article_id FROM t3
+      WHERE user_id = \'nicke\'
+    )
+  )';
+EXECUTE stmt;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Using where
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	7	Using where
+3	SUBQUERY	t3	system	PRIMARY	NULL	NULL	NULL	1	
+EXECUTE stmt;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Using where
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	7	Using where
+3	SUBQUERY	t3	system	PRIMARY	NULL	NULL	NULL	1	
+PREPARE stmt FROM
+'SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+    SELECT t2.id
+    FROM t2
+    WHERE article_id IN (
+      SELECT article_id FROM t3
+      WHERE user_id = \'nicke\'
+    )
+  )';
+EXECUTE stmt;
+partner_id
+partner2
+EXECUTE stmt;
+partner_id
+partner2
+DROP TABLE t1,t2,t3;
+# End of Bug#43768
+#
 # Bug#53058 - semijoin execution of subquery with outerjoin yields wrong result
 #
 CREATE TABLE t1 (i INTEGER);

=== modified file 'mysql-test/r/subquery_sj_none.result'
--- a/mysql-test/r/subquery_sj_none.result	2010-06-23 09:00:02 +0000
+++ b/mysql-test/r/subquery_sj_none.result	2010-07-09 13:35:44 +0000
@@ -4580,6 +4580,100 @@ H
 I
 drop table t1,t2,t3,t4;
 #
+# Bug#43768 Prepared query with nested subqueries core dump on second execution
+#
+CREATE TABLE t1 (
+id INT PRIMARY KEY,
+partner_id VARCHAR(35)
+);
+INSERT INTO t1 VALUES
+(1, 'partner1'), (2, 'partner2'),
+(3, 'partner3'), (4, 'partner4');
+CREATE TABLE t2 (
+id INT NOT NULL,
+t1_line_id INT,
+article_id VARCHAR(20),
+PRIMARY KEY(id, t1_line_id)
+);
+INSERT INTO t2 VALUES
+(1, 1, 'sup'), (2, 1, 'sup'),
+(2, 2, 'sup'), (2, 3, 'sup'),
+(2, 4, 'imp'), (3, 1, 'sup'),
+(4, 1, 'sup');
+CREATE TABLE t3 (
+user_id VARCHAR(50),
+article_id VARCHAR(20) NOT NULL,
+PRIMARY KEY(user_id)
+);
+INSERT INTO t3 VALUES('nicke', 'imp');
+EXPLAIN
+SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+SELECT t2.id
+FROM t2
+WHERE article_id IN (
+SELECT article_id FROM t3
+WHERE user_id = 'nicke'
+    )
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Using where
+2	DEPENDENT SUBQUERY	t2	index_subquery	PRIMARY	PRIMARY	4	func	1	Using where
+3	DEPENDENT SUBQUERY	t3	system	PRIMARY	NULL	NULL	NULL	1	
+SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+SELECT t2.id
+FROM t2
+WHERE article_id IN (
+SELECT article_id FROM t3
+WHERE user_id = 'nicke'
+    )
+);
+partner_id
+partner2
+PREPARE stmt FROM
+'EXPLAIN SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+    SELECT t2.id
+    FROM t2
+    WHERE article_id IN (
+      SELECT article_id FROM t3
+      WHERE user_id = \'nicke\'
+    )
+  )';
+EXECUTE stmt;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Using where
+2	DEPENDENT SUBQUERY	t2	index_subquery	PRIMARY	PRIMARY	4	func	1	Using where
+3	DEPENDENT SUBQUERY	t3	system	PRIMARY	NULL	NULL	NULL	1	
+EXECUTE stmt;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Using where
+2	DEPENDENT SUBQUERY	t2	index_subquery	PRIMARY	PRIMARY	4	func	1	Using where
+3	DEPENDENT SUBQUERY	t3	system	PRIMARY	NULL	NULL	NULL	1	
+PREPARE stmt FROM
+'SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+    SELECT t2.id
+    FROM t2
+    WHERE article_id IN (
+      SELECT article_id FROM t3
+      WHERE user_id = \'nicke\'
+    )
+  )';
+EXECUTE stmt;
+partner_id
+partner2
+EXECUTE stmt;
+partner_id
+partner2
+DROP TABLE t1,t2,t3;
+# End of Bug#43768
+#
 # Bug#53058 - semijoin execution of subquery with outerjoin yields wrong result
 #
 CREATE TABLE t1 (i INTEGER);

=== modified file 'mysql-test/r/subquery_sj_none_jcl6.result'
--- a/mysql-test/r/subquery_sj_none_jcl6.result	2010-06-23 09:00:02 +0000
+++ b/mysql-test/r/subquery_sj_none_jcl6.result	2010-07-09 13:35:44 +0000
@@ -4584,6 +4584,100 @@ H
 I
 drop table t1,t2,t3,t4;
 #
+# Bug#43768 Prepared query with nested subqueries core dump on second execution
+#
+CREATE TABLE t1 (
+id INT PRIMARY KEY,
+partner_id VARCHAR(35)
+);
+INSERT INTO t1 VALUES
+(1, 'partner1'), (2, 'partner2'),
+(3, 'partner3'), (4, 'partner4');
+CREATE TABLE t2 (
+id INT NOT NULL,
+t1_line_id INT,
+article_id VARCHAR(20),
+PRIMARY KEY(id, t1_line_id)
+);
+INSERT INTO t2 VALUES
+(1, 1, 'sup'), (2, 1, 'sup'),
+(2, 2, 'sup'), (2, 3, 'sup'),
+(2, 4, 'imp'), (3, 1, 'sup'),
+(4, 1, 'sup');
+CREATE TABLE t3 (
+user_id VARCHAR(50),
+article_id VARCHAR(20) NOT NULL,
+PRIMARY KEY(user_id)
+);
+INSERT INTO t3 VALUES('nicke', 'imp');
+EXPLAIN
+SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+SELECT t2.id
+FROM t2
+WHERE article_id IN (
+SELECT article_id FROM t3
+WHERE user_id = 'nicke'
+    )
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Using where
+2	DEPENDENT SUBQUERY	t2	index_subquery	PRIMARY	PRIMARY	4	func	1	Using where
+3	DEPENDENT SUBQUERY	t3	system	PRIMARY	NULL	NULL	NULL	1	
+SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+SELECT t2.id
+FROM t2
+WHERE article_id IN (
+SELECT article_id FROM t3
+WHERE user_id = 'nicke'
+    )
+);
+partner_id
+partner2
+PREPARE stmt FROM
+'EXPLAIN SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+    SELECT t2.id
+    FROM t2
+    WHERE article_id IN (
+      SELECT article_id FROM t3
+      WHERE user_id = \'nicke\'
+    )
+  )';
+EXECUTE stmt;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Using where
+2	DEPENDENT SUBQUERY	t2	index_subquery	PRIMARY	PRIMARY	4	func	1	Using where
+3	DEPENDENT SUBQUERY	t3	system	PRIMARY	NULL	NULL	NULL	1	
+EXECUTE stmt;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Using where
+2	DEPENDENT SUBQUERY	t2	index_subquery	PRIMARY	PRIMARY	4	func	1	Using where
+3	DEPENDENT SUBQUERY	t3	system	PRIMARY	NULL	NULL	NULL	1	
+PREPARE stmt FROM
+'SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+    SELECT t2.id
+    FROM t2
+    WHERE article_id IN (
+      SELECT article_id FROM t3
+      WHERE user_id = \'nicke\'
+    )
+  )';
+EXECUTE stmt;
+partner_id
+partner2
+EXECUTE stmt;
+partner_id
+partner2
+DROP TABLE t1,t2,t3;
+# End of Bug#43768
+#
 # Bug#53058 - semijoin execution of subquery with outerjoin yields wrong result
 #
 CREATE TABLE t1 (i INTEGER);

=== modified file 'mysql-test/r/subquery_sj_none_jcl7.result'
--- a/mysql-test/r/subquery_sj_none_jcl7.result	2010-06-23 09:00:02 +0000
+++ b/mysql-test/r/subquery_sj_none_jcl7.result	2010-07-09 13:35:44 +0000
@@ -4584,6 +4584,100 @@ H
 I
 drop table t1,t2,t3,t4;
 #
+# Bug#43768 Prepared query with nested subqueries core dump on second execution
+#
+CREATE TABLE t1 (
+id INT PRIMARY KEY,
+partner_id VARCHAR(35)
+);
+INSERT INTO t1 VALUES
+(1, 'partner1'), (2, 'partner2'),
+(3, 'partner3'), (4, 'partner4');
+CREATE TABLE t2 (
+id INT NOT NULL,
+t1_line_id INT,
+article_id VARCHAR(20),
+PRIMARY KEY(id, t1_line_id)
+);
+INSERT INTO t2 VALUES
+(1, 1, 'sup'), (2, 1, 'sup'),
+(2, 2, 'sup'), (2, 3, 'sup'),
+(2, 4, 'imp'), (3, 1, 'sup'),
+(4, 1, 'sup');
+CREATE TABLE t3 (
+user_id VARCHAR(50),
+article_id VARCHAR(20) NOT NULL,
+PRIMARY KEY(user_id)
+);
+INSERT INTO t3 VALUES('nicke', 'imp');
+EXPLAIN
+SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+SELECT t2.id
+FROM t2
+WHERE article_id IN (
+SELECT article_id FROM t3
+WHERE user_id = 'nicke'
+    )
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Using where
+2	DEPENDENT SUBQUERY	t2	index_subquery	PRIMARY	PRIMARY	4	func	1	Using where
+3	DEPENDENT SUBQUERY	t3	system	PRIMARY	NULL	NULL	NULL	1	
+SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+SELECT t2.id
+FROM t2
+WHERE article_id IN (
+SELECT article_id FROM t3
+WHERE user_id = 'nicke'
+    )
+);
+partner_id
+partner2
+PREPARE stmt FROM
+'EXPLAIN SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+    SELECT t2.id
+    FROM t2
+    WHERE article_id IN (
+      SELECT article_id FROM t3
+      WHERE user_id = \'nicke\'
+    )
+  )';
+EXECUTE stmt;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Using where
+2	DEPENDENT SUBQUERY	t2	index_subquery	PRIMARY	PRIMARY	4	func	1	Using where
+3	DEPENDENT SUBQUERY	t3	system	PRIMARY	NULL	NULL	NULL	1	
+EXECUTE stmt;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Using where
+2	DEPENDENT SUBQUERY	t2	index_subquery	PRIMARY	PRIMARY	4	func	1	Using where
+3	DEPENDENT SUBQUERY	t3	system	PRIMARY	NULL	NULL	NULL	1	
+PREPARE stmt FROM
+'SELECT t1.partner_id
+FROM t1
+WHERE t1.id IN (
+    SELECT t2.id
+    FROM t2
+    WHERE article_id IN (
+      SELECT article_id FROM t3
+      WHERE user_id = \'nicke\'
+    )
+  )';
+EXECUTE stmt;
+partner_id
+partner2
+EXECUTE stmt;
+partner_id
+partner2
+DROP TABLE t1,t2,t3;
+# End of Bug#43768
+#
 # Bug#53058 - semijoin execution of subquery with outerjoin yields wrong result
 #
 CREATE TABLE t1 (i INTEGER);

=== added file 'mysql-test/suite/optimizer_unfixed_bugs/t/bug46744.test'
--- a/mysql-test/suite/optimizer_unfixed_bugs/t/bug46744.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/optimizer_unfixed_bugs/t/bug46744.test	2010-07-09 13:35:44 +0000
@@ -0,0 +1,35 @@
+--echo #
+--echo # Bug #46744 Crash in optimize_semijoin_nests on empty view
+--echo # with limit and procedure.
+--echo #
+
+--disable_warnings
+DROP TABLE IF EXISTS t1, t2;
+DROP VIEW IF EXISTS v1;
+DROP PROCEDURE IF EXISTS p1;
+--enable_warnings
+
+CREATE TABLE t1 ( f1 int );
+CREATE TABLE t2 ( f1 int );
+
+insert into t2 values (5), (7);
+
+CREATE OR REPLACE VIEW v1 AS SELECT * FROM t1 LIMIT 2;
+
+create procedure p1() 
+select COUNT(*) 
+FROM v1 WHERE f1 IN 
+(SELECT f1 FROM t2 WHERE f1 = ANY (SELECT f1 FROM v1));
+
+SET SESSION optimizer_switch = 'semijoin=on';
+CALL p1();
+SET SESSION optimizer_switch = 'semijoin=off';
+CALL p1();
+
+drop table t1, t2;
+drop view v1;
+drop procedure p1;
+
+set SESSION optimizer_switch='default';
+
+--echo # End of bug#46744

=== modified file 'mysql-test/t/optimizer_switch.test'
--- a/mysql-test/t/optimizer_switch.test	2010-06-18 08:45:53 +0000
+++ b/mysql-test/t/optimizer_switch.test	2010-07-09 13:35:44 +0000
@@ -111,40 +111,3 @@ CALL p1;
 DROP PROCEDURE p1;
 DROP TABLE t1, t2;
 SET SESSION optimizer_switch = 'default';
-
-
---echo #
---echo # Bug #46744 Crash in optimize_semijoin_nests on empty view
---echo # with limit and procedure.
---echo #
-
---disable_warnings
-DROP TABLE IF EXISTS t1, t2;
-DROP VIEW IF EXISTS v1;
-DROP PROCEDURE IF EXISTS p1;
---enable_warnings
-
-CREATE TABLE t1 ( f1 int );
-CREATE TABLE t2 ( f1 int );
-
-insert into t2 values (5), (7);
-
-CREATE OR REPLACE VIEW v1 AS SELECT * FROM t1 LIMIT 2;
-
-create procedure p1() 
-select COUNT(*) 
-FROM v1 WHERE f1 IN 
-(SELECT f1 FROM t2 WHERE f1 = ANY (SELECT f1 FROM v1));
-
-SET SESSION optimizer_switch = 'semijoin=on';
-CALL p1();
-SET SESSION optimizer_switch = 'semijoin=off';
-CALL p1();
-
-drop table t1, t2;
-drop view v1;
-drop procedure p1;
-
-set SESSION optimizer_switch='default';
-
---echo # End of bug#46744

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2010-07-04 15:07:55 +0000
+++ b/sql/sql_select.cc	2010-07-09 13:35:44 +0000
@@ -65,7 +65,8 @@ struct st_sargable_param;
 
 static void optimize_keyuse(JOIN *join, DYNAMIC_ARRAY *keyuse_array);
 static bool make_join_statistics(JOIN *join, TABLE_LIST *leaves, Item *conds,
-				 DYNAMIC_ARRAY *keyuse);
+				 DYNAMIC_ARRAY *keyuse,
+                                 bool first_optimization);
 static bool optimize_semijoin_nests(JOIN *join, table_map all_table_map);
 static bool update_ref_and_keys(THD *thd, DYNAMIC_ARRAY *keyuse,
                                 JOIN_TAB *join_tab,
@@ -1515,6 +1516,8 @@ JOIN::optimize()
   if (optimized)
     DBUG_RETURN(0);
   optimized= 1;
+  const bool first_optimization= select_lex->first_cond_optimization;
+  select_lex->first_cond_optimization= FALSE;
 
   thd_proc_info(thd, "optimizing");
 
@@ -1557,7 +1560,7 @@ JOIN::optimize()
   }
 #endif
   SELECT_LEX *sel= thd->lex->current_select;
-  if (sel->first_cond_optimization)
+  if (first_optimization)
   {
     /*
       The following code will allocate the new items in a permanent
@@ -1570,8 +1573,6 @@ JOIN::optimize()
     else
       thd->set_n_backup_active_arena(arena, &backup);
 
-    sel->first_cond_optimization= 0;
-
     /* Convert all outer joins to inner joins if possible */
     conds= simplify_joins(this, join_list, conds, TRUE, FALSE);
     build_bitmap_for_nested_joins(join_list, 0);
@@ -1714,7 +1715,8 @@ JOIN::optimize()
 
   /* Calculate how to do the join */
   thd_proc_info(thd, "statistics");
-  if (make_join_statistics(this, select_lex->leaf_tables, conds, &keyuse) ||
+  if (make_join_statistics(this, select_lex->leaf_tables, conds,
+                           &keyuse, first_optimization) ||
       thd->is_fatal_error)
   {
     DBUG_PRINT("error",("Error: make_join_statistics() failed"));
@@ -3943,20 +3945,30 @@ bool find_eq_ref_candidate(TABLE *table,
     PRECONDITIONS
     When this function is called, the join may have several semi-join nests
     but it is guaranteed that one semi-join nest does not contain another.
+    For functionally dependent tables to be pulled out, key information must
+    have been calculated (see update_ref_and_keys()).
+    For tables to be pulled out based on row counts, join->const_table_map
+    must have corresponding bits set.
    
     ACTION
-    A table can be pulled out of the semi-join nest if
-     - It is a constant table, or
-     - It is accessed via eq_ref(outer_tables)
+    A table can be pulled out of the semi-join nest if it is a const table,
+    ie
+     - it is accessed via eq_ref(outer_tables), or
+     - it contains zero or one rows.
 
     POSTCONDITIONS
-     * Tables that were pulled out have JOIN_TAB::emb_sj_nest == NULL
-     * Tables that were not pulled out have JOIN_TAB::emb_sj_nest pointing 
-       to semi-join nest they are in.
-     * Semi-join nests' TABLE_LIST::sj_inner_tables is updated accordingly
-
-    This operation is (and should be) performed at each PS execution since
-    tables may become/cease to be constant across PS reexecutions.
+     * Tables that were pulled out are removed from the semijoin nest they
+       belonged to and added to the parent join nest.
+     * For these tables, the used_tables and not_null_tables fields of
+       the semijoin nest they belonged to will be adjusted.
+       The semijoin nest is also marked as correlated, and
+       sj_corr_tables and sj_depends_on are adjusted if necessary.
+     * Semi-join nests' sj_inner_tables is set equal to used_tables
+
+    For queries that can be executed multiple times, only functionally
+    dependent tables can be pulled out.
+    For queries that can be executed only once, tables that have one
+    or zero rows can also be pulled out.
     
   NOTE
     Table pullout may make uncorrelated subquery correlated. Consider this
@@ -3984,17 +3996,19 @@ int pull_out_semijoin_tables(JOIN *join)
    
   /* Try pulling out of the each of the semi-joins */
   while ((sj_nest= sj_list_it++))
-  {
-    /* Action #1: Mark the constant tables to be pulled out */
+  {    
     table_map pulled_tables= 0;
-     
     List_iterator<TABLE_LIST> child_li(sj_nest->nested_join->join_list);
     TABLE_LIST *tbl;
+
+    /*
+      Add tables that have been marked as const to the set of tables
+      to be pulled out.
+    */
     while ((tbl= child_li++))
     {
       if (tbl->table)
       {
-        tbl->table->reginfo.join_tab->emb_sj_nest= sj_nest;
         if (tbl->table->map & join->const_table_map)
         {
           pulled_tables |= tbl->table->map;
@@ -4003,11 +4017,11 @@ int pull_out_semijoin_tables(JOIN *join)
         }
       }
     }
-    
+
     /*
-      Action #2: Find which tables we can pull out based on
-      update_ref_and_keys() data. Note that pulling one table out can allow
-      us to pull out some other tables too.
+      Find which tables we can pull out based on key dependency data.
+      Note that pulling one table out can allow us to pull out some
+      other tables too.
     */
     bool pulled_a_table;
     do 
@@ -4040,32 +4054,27 @@ int pull_out_semijoin_tables(JOIN *join)
  
     child_li.rewind();
     /*
-      Action #3: Move the pulled out TABLE_LIST elements to the parents.
+      Move the pulled out TABLE_LIST elements to the parents.
     */
-    table_map inner_tables= sj_nest->nested_join->used_tables & 
-                            ~pulled_tables;
-    /* Record the bitmap of inner tables */
-    sj_nest->sj_inner_tables= inner_tables;
+    sj_nest->nested_join->used_tables&= ~pulled_tables;
+    sj_nest->nested_join->not_null_tables&= ~pulled_tables;
+
+    /* sj_inner_tables is a copy of nested_join->used_tables */
+    sj_nest->sj_inner_tables= sj_nest->nested_join->used_tables;
+
     if (pulled_tables)
     {
-      List<TABLE_LIST> *upper_join_list= (sj_nest->embedding != NULL)?
-                                           (&sj_nest->embedding->nested_join->join_list): 
-                                           (&join->select_lex->top_join_list);
+      List<TABLE_LIST> *upper_join_list= (sj_nest->embedding != NULL) ?
+          &sj_nest->embedding->nested_join->join_list : 
+          &join->select_lex->top_join_list;
       Query_arena *arena, backup;
       arena= join->thd->activate_stmt_arena_if_needed(&backup);
       while ((tbl= child_li++))
       {
         if (tbl->table)
         {
-          if (inner_tables & tbl->table->map)
+          if (!(sj_nest->nested_join->used_tables & tbl->table->map))
           {
-            /* This table is not pulled out */
-            tbl->table->reginfo.join_tab->emb_sj_nest= sj_nest;
-          }
-          else
-          {
-            /* This table has been pulled out of the semi-join nest */
-            tbl->table->reginfo.join_tab->emb_sj_nest= NULL;
             /*
               Pull the table up in the same way as simplify_joins() does:
               update join_list and embedding pointers but keep next[_local]
@@ -4080,7 +4089,7 @@ int pull_out_semijoin_tables(JOIN *join)
       }
 
       /* Remove the sj-nest itself if we've removed everything from it */
-      if (!inner_tables)
+      if (!sj_nest->nested_join->used_tables)
       {
         List_iterator<TABLE_LIST> li(*upper_join_list);
         /* Find the sj_nest in the list. */
@@ -4201,9 +4210,37 @@ static uint get_tmp_table_rec_length(Lis
   return len;
 }
 
-
 /**
-  Calculate the best possible join and initialize the join structure.
+  Calculate best possible join order and initialize the join structure.
+
+  @details
+  Here is an overview of the logic of this function:
+
+  - Initialize JOIN data structures and setup basic dependencies between tables.
+
+  - Update dependencies based on join information.
+
+  - Make key descriptions (update_ref_and_keys()).
+
+  - Pull out semijoin tables based on table dependencies.
+
+  - Extract tables with zero or one rows as const tables.
+
+  - Read contents of const tables, substitute columns from these tables with
+    actual data. Also keep track of empty tables vs. one-row tables. 
+
+  - Extract tables that have become dependent after row-count based
+    table extraction as const tables.
+
+  - Add new sargable predicates based on retrieved const values.
+
+  - Calculate number of rows to be retrieved from each table.
+
+  - Calculate cost of potential semijoin materializations.
+
+  - Calculate best possible join order based on available statistics.
+
+  - Fill in remaining information for the generated join order.
 
   @retval
     0	ok
@@ -4213,7 +4250,7 @@ static uint get_tmp_table_rec_length(Lis
 
 static bool
 make_join_statistics(JOIN *join, TABLE_LIST *tables_arg, Item *conds,
-		     DYNAMIC_ARRAY *keyuse_array)
+		     DYNAMIC_ARRAY *keyuse_array, bool first_optimization)
 {
   int error;
   TABLE *table;
@@ -4236,11 +4273,16 @@ make_join_statistics(JOIN *join, TABLE_L
     DBUG_RETURN(1);				// Eom /* purecov: inspected */
 
   join->best_ref=stat_vector;
+  join->const_table_map= 0;
 
   stat_end=stat+table_count;
   found_const_table_map= all_table_map=0;
   const_count=0;
 
+  /*
+    Initialize data structures for tables to be joined.
+    Initialize dependencies between tables.
+  */
   for (s= stat, i= 0;
        tables;
        s++, tables= tables->next_leaf, i++)
@@ -4267,31 +4309,10 @@ make_join_statistics(JOIN *join, TABLE_L
     table->quick_condition_rows= table->file->stats.records;
 
     s->on_expr_ref= &tables->on_expr;
-    if (*s->on_expr_ref)
-    {
-      /* s is the only inner table of an outer join */
-#ifdef WITH_PARTITION_STORAGE_ENGINE
-      if ((!table->file->stats.records || table->no_partitions_used) && 
-          !tables->in_outer_join_nest())
-#else
-      if (!table->file->stats.records && !tables->in_outer_join_nest())
-#endif
-      {						// Empty table
-        s->dependent= 0;                        // Ignore LEFT JOIN depend.
-	set_position(join,const_count++,s,(KEYUSE*) 0);
-	continue;
-      }
-      outer_join|= table->map;
-      s->embedding_map= 0;
-      for (TABLE_LIST *embedding= tables->embedding;
-           embedding;
-           embedding= embedding->embedding)
-        s->embedding_map|= embedding->nested_join->nj_map;
-      continue;
-    }
+
     if (tables->in_outer_join_nest())
     {
-      /* s belongs to a nested join, maybe to several embedded joins */
+      /* s belongs to a nested join, maybe to several embedding joins */
       s->embedding_map= 0;
       for (TABLE_LIST *embedding= tables->embedding;
            embedding;
@@ -4302,20 +4323,16 @@ make_join_statistics(JOIN *join, TABLE_L
         s->dependent|= embedding->dep_tables;
         outer_join|= nested_join->used_tables;
       }
-      continue;
     }
-#ifdef WITH_PARTITION_STORAGE_ENGINE
-    const bool no_partitions_used= table->no_partitions_used;
-#else
-    const bool no_partitions_used= FALSE;
-#endif
-    if ((table->s->system || table->file->stats.records <= 1 ||
-         no_partitions_used) &&
-	!s->dependent &&
-	(table->file->ha_table_flags() & HA_STATS_RECORDS_IS_EXACT) &&
-        !table->fulltext_searched && !join->no_const_tables)
+    else if (*s->on_expr_ref)
     {
-      set_position(join,const_count++,s,(KEYUSE*) 0);
+      /* s is the only inner table of an outer join */
+      outer_join|= table->map;
+      s->embedding_map= 0;
+      for (TABLE_LIST *embedding= tables->embedding;
+           embedding;
+           embedding= embedding->embedding)
+        s->embedding_map|= embedding->nested_join->nj_map;
     }
   }
   stat_vector[i]=0;
@@ -4324,6 +4341,7 @@ make_join_statistics(JOIN *join, TABLE_L
   if (join->outer_join)
   {
     /* 
+       Complete the dependency analysis.
        Build transitive closure for relation 'to be dependent on'.
        This will speed up the plan search for many cases with outer joins,
        as well as allow us to catch illegal cross references.
@@ -4383,8 +4401,88 @@ make_join_statistics(JOIN *join, TABLE_L
                             ~outer_join, join->select_lex, &sargables))
       goto error;
 
-  /* Read tables with 0 or 1 rows (system tables) */
-  join->const_table_map= 0;
+  /*
+    Pull out semijoin tables based on dependencies. Dependencies are valid
+    throughout the lifetime of a query, so this operation can be performed
+    on the first optimization only.
+  */
+  if (first_optimization)
+  {
+    if (pull_out_semijoin_tables(join))
+      DBUG_RETURN(TRUE);
+  }
+
+  /*
+    Extract const tables based on row counts, must be done for each execution.
+    Tables containing exactly zero or one rows are marked as const, but
+    notice the additional constraints checked below.
+    Tables that are extracted have their rows read before actual execution
+    starts and are placed in the beginning of the join_tab array.
+    Thus, they do not take part in join order optimization process,
+    which can significantly reduce the optimization time.
+    The data read from these tables can also be regarded as "constant"
+    throughout query execution, hence the column values can be used for
+    additional constant propagation and extraction of const tables based
+    on eq-ref properties.
+  */
+  for (i= 0, s= stat; i < table_count; i++, s++)
+  {
+    TABLE      *table= s->table;
+    TABLE_LIST *tables= table->pos_in_table_list;
+
+#ifdef WITH_PARTITION_STORAGE_ENGINE
+    const bool no_partitions_used= table->no_partitions_used;
+#else
+    const bool no_partitions_used= FALSE;
+#endif
+
+    if (tables->in_outer_join_nest())
+    {
+      /*
+        Table belongs to a nested join, no candidate for const table extraction.
+      */
+    }
+    else if (tables->embedding && tables->embedding->sj_on_expr &&
+             !join->thd->stmt_arena->is_conventional())
+    {
+      /*
+        Table belongs to a semi-join and this is a PS/SP.
+        Since extracting this table as const would imply a table pullout,
+        which is an irreversible operation, the table cannot be a candidate
+        for const table extraction.
+      */
+    }
+    else if (*s->on_expr_ref)
+    {
+      /* s is the only inner table of an outer join */
+      if (!table->file->stats.records || no_partitions_used)
+      {						// Empty table
+        set_position(join,const_count++,s,(KEYUSE*) 0);
+      }
+    }
+    else
+    {
+      /*
+        Extract const tables (tables with only zero or one rows).
+        Exclude tables that
+         1. are dependent upon other tables, or
+         2. have no exact statistics (usually transactional tables), or
+         3. are full-text searched, or
+         4. the join object does not allow const table elimination.
+      */ 
+      if ((table->s->system ||
+           table->file->stats.records <= 1 ||
+           no_partitions_used) &&
+          !s->dependent &&                                               // 1
+          (table->file->ha_table_flags() & HA_STATS_RECORDS_IS_EXACT) && // 2
+          !table->fulltext_searched &&                                   // 3
+          !join->no_const_tables)                                        // 4
+      {
+        set_position(join,const_count++,s,(KEYUSE*) 0);
+      }
+    }
+  }
+  /* Read const tables (tables matching no more than 1 rows) */
 
   for (POSITION *p_pos=join->positions, *p_end=p_pos+const_count;
        p_pos < p_end ;
@@ -4505,14 +4603,19 @@ make_join_statistics(JOIN *join, TABLE_L
 	  } while (keyuse->table == table && keyuse->key == key);
 
           /*
+            Extract const tables with proper key dependencies.
+            Exclude tables that
+             1. are full-text searched, or
+             2. are part of nested outer join.
+
             TODO (low priority): currently we ignore the const tables that
             are within a semi-join nest which is within an outer join nest.
             The effect of this is that we don't do const substitution for
             such tables.
           */
 	  if (eq_part.is_prefix(table->key_info[key].key_parts) &&
-              !table->fulltext_searched && 
-              !table->pos_in_table_list->in_outer_join_nest())
+              !table->fulltext_searched &&                           // 1
+              !table->pos_in_table_list->in_outer_join_nest())       // 2
 	  {
             if (table->key_info[key].flags & HA_NOSAME)
             {
@@ -4571,6 +4674,8 @@ make_join_statistics(JOIN *join, TABLE_L
 
   for (s=stat ; s < stat_end ; s++)
   {
+    TABLE_LIST *tl= s->table->pos_in_table_list;
+
     if (s->type == JT_SYSTEM || s->type == JT_CONST)
     {
       /* Only one matching row */
@@ -4603,9 +4708,8 @@ make_join_statistics(JOIN *join, TABLE_L
       Do range analysis if we're on the inner side of a semi-join (3).
     */
     if (!s->const_keys.is_clear_all() &&                        // (1)
-        (!s->table->pos_in_table_list->embedding ||             // (2)
-         (s->table->pos_in_table_list->embedding &&             // (3)
-          s->table->pos_in_table_list->embedding->sj_on_expr))) // (3)
+        (!tl->embedding ||                                      // (2)
+         (tl->embedding && tl->embedding->sj_on_expr)))         // (3)
     {
       ha_rows records;
       SQL_SELECT *select;
@@ -4620,7 +4724,15 @@ make_join_statistics(JOIN *join, TABLE_L
       s->quick=select->quick;
       s->needed_reg=select->needed_reg;
       select->quick=0;
-      if (records == 0 && s->table->reginfo.impossible_range)
+      /*
+        Check for "impossible range", make sure that semi-joined tables (1)
+        are not accounted for in prepared statements (2).
+      */
+      if (records == 0 &&
+          s->table->reginfo.impossible_range &&
+          (s->table->file->ha_table_flags() & HA_STATS_RECORDS_IS_EXACT) &&
+          (!(tl->embedding && tl->embedding->sj_on_expr) ||               // 1
+           join->thd->stmt_arena->is_conventional()))                     // 2
       {
 	/*
 	  Impossible WHERE or ON expression
@@ -4649,8 +4761,28 @@ make_join_statistics(JOIN *join, TABLE_L
     }
   }
 
-  if (pull_out_semijoin_tables(join))
-    DBUG_RETURN(TRUE);
+  /*
+    Pull out tables based on row counts. Because this is an irreversible
+    process and row counts may vary from one execution to the next,
+    this is only done for non-prepared execution.
+  */
+  if (join->thd->stmt_arena->is_conventional())
+  {
+    if (pull_out_semijoin_tables(join))
+      DBUG_RETURN(TRUE);
+  }
+
+  /*
+    Set embedding semijoin pointer. This must be done after pullout of
+    semijoin tables, but it must also be done for each execution, hence it
+    cannot be done in pull_out_semijoin_tables().
+  */
+  for (s= stat; s < stat_end; i++, s++)
+  {
+    TABLE_LIST *tables= s->table->pos_in_table_list;
+    if (tables->embedding && tables->embedding->sj_on_expr)
+      s->emb_sj_nest= tables->embedding;
+  }
 
   join->join_tab=stat;
   join->map2table=stat_ref;


Attachment: [text/bzr-bundle] bzr/roy.lyseng@sun.com-20100709133544-3ozkmiaqt44wbllz.bundle
Thread
bzr commit into mysql-next-mr-opt-backporting branch (roy.lyseng:3211)Bug#43768Roy Lyseng9 Jul
  • Re: bzr commit into mysql-next-mr-opt-backporting branch(roy.lyseng:3211) Bug#43768Øystein Grøvlen2 Aug
    • Re: bzr commit into mysql-next-mr-opt-backporting branch (roy.lyseng:3211)Bug#43768Roy Lyseng6 Aug