From 1a48919036a7746a15ff6bd19cb52c25fc6a57c1 Mon Sep 17 00:00:00 2001
From: unknown <sanja@montyprogram.com>
Date: Thu, 5 Apr 2012 23:32:57 +0300
Subject: Fix of LP bug#968720.

When a view/derived table is converted from merged to materialized the
items from the used_item lists are substituted for items referring to
the fields of the result of the materialization. The problem appeared
with queries employing natural joins. Since the resolution of a natural
join was performed only once the used_item list formed at the second
execution of the query lacked the references to the fields that were
used only in the equality predicates generated for the natural join.
---
 mysql-test/r/derived_view.result | 39 +++++++++++++++++++++++++++++++++++++++
 mysql-test/t/derived_view.test   | 37 +++++++++++++++++++++++++++++++++++++
 2 files changed, 76 insertions(+)

(limited to 'mysql-test')

diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result
index 28c4c362a9d..3151bb14657 100644
--- a/mysql-test/r/derived_view.result
+++ b/mysql-test/r/derived_view.result
@@ -1997,5 +1997,44 @@ a	b	gc
 SET SESSION optimizer_switch= @save_optimizer_switch;
 DROP VIEW v;
 DROP TABLE t1,t2;
+#
+# LP BUG#968720 crash due to converting to materialized and
+# natural join made only once
+#
+SET @save968720_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch = 'derived_merge=on';
+CREATE TABLE t1 (a int, INDEX(a));
+INSERT INTO t1 VALUES (1);
+CREATE TABLE t2 (a int, INDEX(a));
+INSERT INTO t2 VALUES (1), (2);
+INSERT INTO t1 SELECT a FROM (SELECT a FROM test.t1) AS s1 NATURAL JOIN
+t2 AS s2;
+SELECT * FROM t1;
+a
+1
+1
+DELETE FROM t1;
+INSERT INTO t1 VALUES (1);
+PREPARE stmt FROM "
+INSERT INTO t1 SELECT a FROM (SELECT a FROM test.t1) AS s1 NATURAL JOIN
+t2 AS s2;
+";
+EXECUTE stmt;
+SELECT * FROM t1;
+a
+1
+1
+EXECUTE stmt;
+SELECT * FROM t1;
+a
+1
+1
+1
+1
+drop table t1,t2;
+set optimizer_switch=@save968720_optimizer_switch;
+#
+# end of 5.3 tests
+#
 set optimizer_switch=@exit_optimizer_switch;
 set join_cache_level=@exit_join_cache_level;
diff --git a/mysql-test/t/derived_view.test b/mysql-test/t/derived_view.test
index d1ed2ff5ba6..03d308b6c45 100644
--- a/mysql-test/t/derived_view.test
+++ b/mysql-test/t/derived_view.test
@@ -1380,6 +1380,43 @@ SET SESSION optimizer_switch= @save_optimizer_switch;
 DROP VIEW v;
 DROP TABLE t1,t2;
 
+--echo #
+--echo # LP BUG#968720 crash due to converting to materialized and
+--echo # natural join made only once
+--echo #
+
+SET @save968720_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch = 'derived_merge=on';
+
+CREATE TABLE t1 (a int, INDEX(a));
+INSERT INTO t1 VALUES (1);
+
+CREATE TABLE t2 (a int, INDEX(a));
+INSERT INTO t2 VALUES (1), (2);
+
+INSERT INTO t1 SELECT a FROM (SELECT a FROM test.t1) AS s1 NATURAL JOIN
+t2 AS s2;
+SELECT * FROM t1;
+
+DELETE FROM t1;
+INSERT INTO t1 VALUES (1);
+
+PREPARE stmt FROM "
+INSERT INTO t1 SELECT a FROM (SELECT a FROM test.t1) AS s1 NATURAL JOIN
+t2 AS s2;
+";
+EXECUTE stmt;
+SELECT * FROM t1;
+EXECUTE stmt;
+SELECT * FROM t1;
+
+drop table t1,t2;
+set optimizer_switch=@save968720_optimizer_switch;
+
+--echo #
+--echo # end of 5.3 tests
+--echo #
+
 # The following command must be the last one the file 
 set optimizer_switch=@exit_optimizer_switch;
 set join_cache_level=@exit_join_cache_level;
-- 
cgit v1.2.1