diff options
| -rw-r--r-- | mysql-test/r/subselect_cache.result | 28 | ||||
| -rw-r--r-- | mysql-test/t/subselect_cache.test | 34 | ||||
| -rw-r--r-- | sql/item.cc | 4 | ||||
| -rw-r--r-- | sql/item.h | 5 | ||||
| -rw-r--r-- | sql/item_subselect.cc | 4 | ||||
| -rw-r--r-- | sql/item_sum.cc | 4 | ||||
| -rw-r--r-- | sql/sql_lex.cc | 1 | ||||
| -rw-r--r-- | sql/sql_lex.h | 7 | ||||
| -rw-r--r-- | sql/sql_parse.cc | 1 |
9 files changed, 84 insertions, 4 deletions
diff --git a/mysql-test/r/subselect_cache.result b/mysql-test/r/subselect_cache.result index c4826eeb50f..8758497f7d7 100644 --- a/mysql-test/r/subselect_cache.result +++ b/mysql-test/r/subselect_cache.result @@ -1,3 +1,5 @@ +drop table if exists t1,t2,t3,t4,t5; +drop view if exists v1; set optimizer_switch='subquery_cache=on'; create table t1 (a int, b int); insert into t1 values (1,2),(3,4),(1,2),(3,4),(3,4),(4,5),(4,5),(5,6),(5,6),(4,5); @@ -3354,5 +3356,31 @@ f1 f2 f3 f3 7 0 0 0 7 0 0 0 drop tables t1, t2, t3; +# +# Test of LP BUG#872775 view with "outer references" bug +# +set @@optimizer_switch= default; +set optimizer_switch='subquery_cache=on'; +CREATE TABLE t1 (a int) ; +CREATE TABLE t2 (b int, c varchar(1) NOT NULL ) ; +INSERT INTO t2 VALUES (1,'x'),(2,'y'); +CREATE TABLE t3 (a int) ; +CREATE TABLE t4 ( pk int(11) NOT NULL , b int(11) NOT NULL ) ; +INSERT INTO t4 VALUES (26,9),(27,5),(28,0),(29,3); +CREATE OR REPLACE VIEW v1 AS +SELECT t2.b +FROM t1 +JOIN t2 +WHERE t2 .c > ( +SELECT t2.c FROM t3 +); +SELECT * FROM t4 WHERE b NOT IN ( SELECT * FROM v1 ); +pk b +26 9 +27 5 +28 0 +29 3 +drop view v1; +drop table t1,t2,t3,t4; # restore default set @@optimizer_switch= default; diff --git a/mysql-test/t/subselect_cache.test b/mysql-test/t/subselect_cache.test index 3d8de4e4808..6bf5028b6cf 100644 --- a/mysql-test/t/subselect_cache.test +++ b/mysql-test/t/subselect_cache.test @@ -1,4 +1,10 @@ +--disable_warnings +drop table if exists t1,t2,t3,t4,t5; +drop view if exists v1; +--enable_warnings + + set optimizer_switch='subquery_cache=on'; create table t1 (a int, b int); @@ -1664,5 +1670,33 @@ FROM t1 WHERE t2.f1 OR t2.f3 ); drop tables t1, t2, t3; +--echo # +--echo # Test of LP BUG#872775 view with "outer references" bug +--echo # +set @@optimizer_switch= default; +set optimizer_switch='subquery_cache=on'; +CREATE TABLE t1 (a int) ; + +CREATE TABLE t2 (b int, c varchar(1) NOT NULL ) ; +INSERT INTO t2 VALUES (1,'x'),(2,'y'); + +CREATE TABLE t3 (a int) ; + +CREATE TABLE t4 ( pk int(11) NOT NULL , b int(11) NOT NULL ) ; +INSERT INTO t4 VALUES (26,9),(27,5),(28,0),(29,3); + +CREATE OR REPLACE VIEW v1 AS +SELECT t2.b +FROM t1 +JOIN t2 +WHERE t2 .c > ( + SELECT t2.c FROM t3 + ); + +SELECT * FROM t4 WHERE b NOT IN ( SELECT * FROM v1 ); + +drop view v1; +drop table t1,t2,t3,t4; + --echo # restore default set @@optimizer_switch= default; diff --git a/sql/item.cc b/sql/item.cc index f5ce0fbc7a7..7056e1dd5d2 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -755,7 +755,9 @@ bool Item_ident::remove_dependence_processor(uchar * arg) bool Item_ident::collect_outer_ref_processor(uchar *param) { Collect_deps_prm *prm= (Collect_deps_prm *)param; - if (depended_from && depended_from->nest_level < prm->nest_level) + if (depended_from && + depended_from->nest_level_base == prm->nest_level_base && + depended_from->nest_level < prm->nest_level) prm->parameters->add_unique(this, &cmp_items); return FALSE; } diff --git a/sql/item.h b/sql/item.h index bfe0d78ed29..fa6918d8484 100644 --- a/sql/item.h +++ b/sql/item.h @@ -505,6 +505,7 @@ typedef void (*Cond_traverser) (const Item *item, void *arg); class Item_equal; class COND_EQUAL; +class st_select_lex_unit; class Item { Item(const Item &); /* Prevent use of these */ @@ -1159,8 +1160,10 @@ public: } struct Collect_deps_prm { - int nest_level; List<Item> *parameters; + /* unit from which we count nest_level */ + st_select_lex_unit *nest_level_base; + int nest_level; }; /** Collect outer references diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 22c62f0a6d7..4ab2e2e2c69 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -599,7 +599,9 @@ bool Item_subselect::exec() void Item_subselect::get_cache_parameters(List<Item> ¶meters) { - Collect_deps_prm prm= { unit->first_select()->nest_level, ¶meters }; + Collect_deps_prm prm= {¶meters, + unit->first_select()->nest_level_base, + unit->first_select()->nest_level}; walk(&Item::collect_outer_ref_processor, TRUE, (uchar*)&prm); } diff --git a/sql/item_sum.cc b/sql/item_sum.cc index 65314a0ff91..063406990cb 100644 --- a/sql/item_sum.cc +++ b/sql/item_sum.cc @@ -359,7 +359,9 @@ bool Item_sum::collect_outer_ref_processor(uchar *param) { Collect_deps_prm *prm= (Collect_deps_prm *)param; SELECT_LEX *ds; - if ((ds= depended_from()) && ds->nest_level < prm->nest_level) + if ((ds= depended_from()) && + ds->nest_level_base == prm->nest_level_base && + ds->nest_level < prm->nest_level) prm->parameters->add_unique(this, &cmp_items); return FALSE; } diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index cc10cd5dee3..8513f73b90c 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -339,6 +339,7 @@ void lex_start(THD *thd) lex->event_parse_data= NULL; lex->profile_options= PROFILE_NONE; lex->nest_level=0 ; + lex->select_lex.nest_level_base= &lex->unit; lex->allow_sum_func= 0; lex->in_sum_func= NULL; lex->protect_against_global_read_lock= FALSE; diff --git a/sql/sql_lex.h b/sql/sql_lex.h index 32ad4f414c9..4caf2a1dbb3 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -684,6 +684,13 @@ public: ulong table_join_options; uint in_sum_expr; uint select_number; /* number of select (used for EXPLAIN) */ + + /* + nest_levels are local to the query or VIEW, + and that view merge procedure does not re-calculate them. + So we also have to remember unit against which we count levels. + */ + SELECT_LEX_UNIT *nest_level_base; int nest_level; /* nesting level of select */ Item_sum *inner_sum_func_list; /* list of sum func in nested selects */ uint with_wild; /* item list contain '*' */ diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index 6202b9d888a..a1c373ce426 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -5917,6 +5917,7 @@ mysql_new_select(LEX *lex, bool move_down) DBUG_RETURN(1); } select_lex->nest_level= lex->nest_level; + select_lex->nest_level_base= &thd->lex->unit; if (move_down) { SELECT_LEX_UNIT *unit; |