summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/subselect_sj2.result17
-rw-r--r--mysql-test/r/subselect_sj2_jcl6.result17
-rw-r--r--mysql-test/t/subselect_sj2.test18
-rw-r--r--sql/item.cc1
-rw-r--r--sql/sql_base.cc16
-rw-r--r--sql/sql_lex.cc41
-rw-r--r--sql/sql_lex.h2
7 files changed, 110 insertions, 2 deletions
diff --git a/mysql-test/r/subselect_sj2.result b/mysql-test/r/subselect_sj2.result
index 036fd6ebb48..e4e66ce01f2 100644
--- a/mysql-test/r/subselect_sj2.result
+++ b/mysql-test/r/subselect_sj2.result
@@ -747,4 +747,21 @@ LEFT JOIN t2 AS SQ4_alias3 ON SQ4_alias3.f10
GROUP BY field2;
field2
drop table t1, t2, t3;
+#
+# BUG#849763: Wrong result with second execution of prepared statement with semijoin + view
+#
+CREATE TABLE t1 ( c varchar(1)) engine=innodb;
+INSERT INTO t1 VALUES ('r');
+CREATE TABLE t2 ( a integer, b varchar(1), c varchar(1)) engine=innodb;
+INSERT INTO t2 VALUES (1,'r','r');
+CREATE OR REPLACE VIEW v1 AS SELECT * FROM t1;
+PREPARE st1 FROM 'SELECT * FROM t2 WHERE a = SOME (SELECT a FROM v1 WHERE v1.c = t2.c)';
+EXECUTE st1;
+a b c
+1 r r
+EXECUTE st1;
+a b c
+1 r r
+DROP VIEW v1;
+DROP TABLE t1, t2;
set optimizer_switch=@subselect_sj2_tmp;
diff --git a/mysql-test/r/subselect_sj2_jcl6.result b/mysql-test/r/subselect_sj2_jcl6.result
index a24c2d8f18a..234fd625886 100644
--- a/mysql-test/r/subselect_sj2_jcl6.result
+++ b/mysql-test/r/subselect_sj2_jcl6.result
@@ -758,6 +758,23 @@ LEFT JOIN t2 AS SQ4_alias3 ON SQ4_alias3.f10
GROUP BY field2;
field2
drop table t1, t2, t3;
+#
+# BUG#849763: Wrong result with second execution of prepared statement with semijoin + view
+#
+CREATE TABLE t1 ( c varchar(1)) engine=innodb;
+INSERT INTO t1 VALUES ('r');
+CREATE TABLE t2 ( a integer, b varchar(1), c varchar(1)) engine=innodb;
+INSERT INTO t2 VALUES (1,'r','r');
+CREATE OR REPLACE VIEW v1 AS SELECT * FROM t1;
+PREPARE st1 FROM 'SELECT * FROM t2 WHERE a = SOME (SELECT a FROM v1 WHERE v1.c = t2.c)';
+EXECUTE st1;
+a b c
+1 r r
+EXECUTE st1;
+a b c
+1 r r
+DROP VIEW v1;
+DROP TABLE t1, t2;
set optimizer_switch=@subselect_sj2_tmp;
set join_cache_level=default;
show variables like 'join_cache_level';
diff --git a/mysql-test/t/subselect_sj2.test b/mysql-test/t/subselect_sj2.test
index 285146481f4..ed96168a6c8 100644
--- a/mysql-test/t/subselect_sj2.test
+++ b/mysql-test/t/subselect_sj2.test
@@ -944,4 +944,22 @@ WHERE alias2.f11 IN (
GROUP BY field2;
drop table t1, t2, t3;
+--echo #
+--echo # BUG#849763: Wrong result with second execution of prepared statement with semijoin + view
+--echo #
+CREATE TABLE t1 ( c varchar(1)) engine=innodb;
+INSERT INTO t1 VALUES ('r');
+
+CREATE TABLE t2 ( a integer, b varchar(1), c varchar(1)) engine=innodb;
+INSERT INTO t2 VALUES (1,'r','r');
+
+CREATE OR REPLACE VIEW v1 AS SELECT * FROM t1;
+
+PREPARE st1 FROM 'SELECT * FROM t2 WHERE a = SOME (SELECT a FROM v1 WHERE v1.c = t2.c)';
+EXECUTE st1;
+EXECUTE st1;
+
+DROP VIEW v1;
+DROP TABLE t1, t2;
+
set optimizer_switch=@subselect_sj2_tmp;
diff --git a/sql/item.cc b/sql/item.cc
index 02263106fe8..a4cd292a5b1 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -4648,6 +4648,7 @@ bool Item_field::fix_fields(THD *thd, Item **reference)
if (!outer_fixed && cached_table && cached_table->select_lex &&
context->select_lex &&
cached_table->select_lex != context->select_lex &&
+ !context->select_lex->is_merged_child_of(cached_table->select_lex) &&
is_outer_table(cached_table, context->select_lex))
{
int ret;
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index bca8167f2b9..e2142bc2734 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -6447,11 +6447,25 @@ find_field_in_tables(THD *thd, Item_ident *item,
{
SELECT_LEX *current_sel= thd->lex->current_select;
SELECT_LEX *last_select= table_ref->select_lex;
+ bool all_merged= TRUE;
+ for (SELECT_LEX *sl= current_sel; sl && sl!=last_select;
+ sl=sl->outer_select())
+ {
+ Item *subs= sl->master_unit()->item;
+ if (subs->type() == Item::SUBSELECT_ITEM &&
+ ((Item_subselect*)subs)->substype() == Item_subselect::IN_SUBS &&
+ ((Item_in_subselect*)subs)->in_strategy & SUBS_SEMI_JOIN)
+ {
+ continue;
+ }
+ all_merged= FALSE;
+ break;
+ }
/*
If the field was an outer referencee, mark all selects using this
sub query as dependent on the outer query
*/
- if (current_sel != last_select)
+ if (!all_merged && current_sel != last_select)
{
mark_select_range_as_dependent(thd, last_select, current_sel,
found, *ref, item);
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 23662339cff..72cf0179449 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -3622,6 +3622,47 @@ bool st_select_lex::save_prep_leaf_tables(THD *thd)
}
+/*
+ Return true if this select_lex has been converted into a semi-join nest
+ within 'ancestor'.
+
+ We need a loop to check this because there could be several nested
+ subselects, like
+
+ SELECT ... FROM grand_parent
+ WHERE expr1 IN (SELECT ... FROM parent
+ WHERE expr2 IN ( SELECT ... FROM child)
+
+ which were converted into:
+
+ SELECT ...
+ FROM grand_parent SEMI_JOIN (parent JOIN child)
+ WHERE
+ expr1 AND expr2
+
+ In this case, both parent and child selects were merged into the parent.
+*/
+
+bool st_select_lex::is_merged_child_of(st_select_lex *ancestor)
+{
+ bool all_merged= TRUE;
+ for (SELECT_LEX *sl= this; sl && sl!=ancestor;
+ sl=sl->outer_select())
+ {
+ Item *subs= sl->master_unit()->item;
+ if (subs && subs->type() == Item::SUBSELECT_ITEM &&
+ ((Item_subselect*)subs)->substype() == Item_subselect::IN_SUBS &&
+ ((Item_in_subselect*)subs)->in_strategy & SUBS_SEMI_JOIN)
+ {
+ continue;
+ }
+ all_merged= FALSE;
+ break;
+ }
+ return all_merged;
+}
+
+
/**
A routine used by the parser to decide whether we are specifying a full
partitioning or if only partitions to add or to split.
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index 0da628b7ca0..4ef24876474 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -907,7 +907,7 @@ public:
bool save_leaf_tables(THD *thd);
bool save_prep_leaf_tables(THD *thd);
-
+ bool is_merged_child_of(st_select_lex *ancestor);
private:
/* current index hint kind. used in filling up index_hints */
enum index_hint_type current_index_hint_type;
View Lorry Controller Status