#At file:///export/home/didrik/mysqldev-6.0-codebase/6.0-codebase-bf-fix49198/ based on revid:li-bing.song@stripped
3860 Tor Didriksen 2010-02-01
Bug #49198 Wrong result for second call of procedure with view in subselect
The problem was that fix_after_pullout() after semijoin conversion
wasn't propagated from the view to the underlying table.
On subesequent executions of the prepared statement,
we would mark the underlying table as 'dependent' and the predicate
anlysis would lead to a different (and illegal) execution plan.
@ mysql-test/r/subselect_sj.result
Add test cases.
@ mysql-test/r/subselect_sj_jcl6.result
Add test cases.
@ mysql-test/t/subselect_sj.test
Add test cases.
@ sql/item.cc
Always propagate the fix_after_pullout() to the referenced object in Item_ref::fix_after_pullout()
@ sql/sql_base.cc
Do not call DBUG_RETURN(some_function_which_also_has_dbug_trace())
as the corresponding trace/indentation gets messed up.
modified:
mysql-test/r/subselect_sj.result
mysql-test/r/subselect_sj_jcl6.result
mysql-test/t/subselect_sj.test
sql/item.cc
sql/sql_base.cc
=== modified file 'mysql-test/r/subselect_sj.result'
--- a/mysql-test/r/subselect_sj.result 2010-01-20 10:11:29 +0000
+++ b/mysql-test/r/subselect_sj.result 2010-02-01 15:44:20 +0000
@@ -1660,3 +1660,50 @@ id select_type table type possible_keys
1 PRIMARY it2 ALL NULL NULL NULL NULL 20 Using where; End temporary
DROP TABLE ot1, it1, it2;
# End of BUG#38075
+#
+# Bug#49198 Wrong result for second call of procedure
+# with view in subselect.
+#
+CREATE TABLE t1 (t1field integer, primary key (t1field));
+CREATE TABLE t2 (t2field integer, primary key (t2field));
+CREATE TABLE t3 (t3field integer, primary key (t3field));
+CREATE VIEW v2 AS SELECT * FROM t2;
+CREATE VIEW v3 AS SELECT * FROM t3;
+INSERT INTO t1 VALUES(1),(2);
+INSERT INTO t2 VALUES(1),(2);
+INSERT INTO t3 VALUES(1),(2);
+PREPARE stmt FROM
+"
+SELECT t1field
+FROM t1
+WHERE t1field IN (SELECT * FROM v2);
+";
+EXECUTE stmt;
+t1field
+1
+2
+EXECUTE stmt;
+t1field
+1
+2
+PREPARE stmt FROM
+"
+EXPLAIN
+SELECT t1field
+FROM t1
+WHERE t1field IN (SELECT * FROM v2)
+ AND t1field IN (SELECT * FROM v3)
+";
+EXECUTE stmt;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using index
+1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index
+1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index
+EXECUTE stmt;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 2 Using index
+1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index
+1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index
+DROP TABLE t1, t2, t3;
+DROP VIEW v2, v3;
+# End of Bug#49198
=== modified file 'mysql-test/r/subselect_sj_jcl6.result'
--- a/mysql-test/r/subselect_sj_jcl6.result 2010-01-20 10:11:29 +0000
+++ b/mysql-test/r/subselect_sj_jcl6.result 2010-02-01 15:44:20 +0000
@@ -1664,6 +1664,53 @@ id select_type table type possible_keys
1 PRIMARY it2 ALL NULL NULL NULL NULL 20 Using where; End temporary; Using join buffer
DROP TABLE ot1, it1, it2;
# End of BUG#38075
+#
+# Bug#49198 Wrong result for second call of procedure
+# with view in subselect.
+#
+CREATE TABLE t1 (t1field integer, primary key (t1field));
+CREATE TABLE t2 (t2field integer, primary key (t2field));
+CREATE TABLE t3 (t3field integer, primary key (t3field));
+CREATE VIEW v2 AS SELECT * FROM t2;
+CREATE VIEW v3 AS SELECT * FROM t3;
+INSERT INTO t1 VALUES(1),(2);
+INSERT INTO t2 VALUES(1),(2);
+INSERT INTO t3 VALUES(1),(2);
+PREPARE stmt FROM
+"
+SELECT t1field
+FROM t1
+WHERE t1field IN (SELECT * FROM v2);
+";
+EXECUTE stmt;
+t1field
+1
+2
+EXECUTE stmt;
+t1field
+1
+2
+PREPARE stmt FROM
+"
+EXPLAIN
+SELECT t1field
+FROM t1
+WHERE t1field IN (SELECT * FROM v2)
+ AND t1field IN (SELECT * FROM v3)
+";
+EXECUTE stmt;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using index
+1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index
+1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index
+EXECUTE stmt;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 2 Using index
+1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index
+1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index
+DROP TABLE t1, t2, t3;
+DROP VIEW v2, v3;
+# End of Bug#49198
set optimizer_join_cache_level=default;
show variables like 'optimizer_join_cache_level';
Variable_name Value
=== modified file 'mysql-test/t/subselect_sj.test'
--- a/mysql-test/t/subselect_sj.test 2010-01-15 12:12:55 +0000
+++ b/mysql-test/t/subselect_sj.test 2010-02-01 15:44:20 +0000
@@ -889,3 +889,45 @@ DROP TABLE ot1, it1, it2;
--echo # End of BUG#38075
+--echo #
+--echo # Bug#49198 Wrong result for second call of procedure
+--echo # with view in subselect.
+--echo #
+
+CREATE TABLE t1 (t1field integer, primary key (t1field));
+CREATE TABLE t2 (t2field integer, primary key (t2field));
+CREATE TABLE t3 (t3field integer, primary key (t3field));
+
+CREATE VIEW v2 AS SELECT * FROM t2;
+CREATE VIEW v3 AS SELECT * FROM t3;
+
+INSERT INTO t1 VALUES(1),(2);
+INSERT INTO t2 VALUES(1),(2);
+INSERT INTO t3 VALUES(1),(2);
+
+PREPARE stmt FROM
+"
+SELECT t1field
+FROM t1
+WHERE t1field IN (SELECT * FROM v2);
+";
+
+EXECUTE stmt;
+EXECUTE stmt;
+
+PREPARE stmt FROM
+"
+EXPLAIN
+SELECT t1field
+FROM t1
+WHERE t1field IN (SELECT * FROM v2)
+ AND t1field IN (SELECT * FROM v3)
+";
+
+EXECUTE stmt;
+EXECUTE stmt;
+
+DROP TABLE t1, t2, t3;
+DROP VIEW v2, v3;
+
+--echo # End of Bug#49198
=== modified file 'sql/item.cc'
--- a/sql/item.cc 2009-12-28 13:17:31 +0000
+++ b/sql/item.cc 2010-02-01 15:44:20 +0000
@@ -6679,11 +6679,8 @@ void Item_outer_ref::fix_after_pullout(s
void Item_ref::fix_after_pullout(st_select_lex *new_parent, Item **refptr)
{
- if (depended_from == new_parent)
- {
- (*ref)->fix_after_pullout(new_parent, ref);
- depended_from= NULL;
- }
+ DBUG_ASSERT(NULL == depended_from);
+ (*ref)->fix_after_pullout(new_parent, ref);
}
/**
=== modified file 'sql/sql_base.cc'
--- a/sql/sql_base.cc 2010-01-22 08:06:07 +0000
+++ b/sql/sql_base.cc 2010-02-01 15:44:20 +0000
@@ -4910,7 +4910,10 @@ bool lock_tables(THD *thd, TABLE_LIST *t
*need_reopen= FALSE;
if (!tables && !thd->lex->requires_prelocking())
- DBUG_RETURN(thd->decide_logging_format(tables));
+ {
+ const bool retval= thd->decide_logging_format(tables);
+ DBUG_RETURN(retval);
+ }
/*
Check for thd->locked_tables_mode to avoid a redundant
@@ -5055,7 +5058,8 @@ bool lock_tables(THD *thd, TABLE_LIST *t
}
}
- DBUG_RETURN(thd->decide_logging_format(tables));
+ const bool retval= thd->decide_logging_format(tables);
+ DBUG_RETURN(retval);
}
Attachment: [text/bzr-bundle] bzr/tor.didriksen@sun.com-20100201154420-2bkre9sqptkjbbrb.bundle
