On Tue, Oct 25, 2011 at 11:59:37AM +0300, sanja@askmonty.org wrote:

At file:///home/bell/maria/bzr/work-maria-5.3-lpb872775/

------------------------------------------------------------ revno: 3249 revision-id: sanja@askmonty.org-20111025085936-h0jyyxtbv3qhp4f4 parent: igor@askmonty.org-20111023124603-sjv665ajk25b4dgi committer: sanja@askmonty.org branch nick: work-maria-5.3-lpb872775 timestamp: Tue 2011-10-25 11:59:36 +0300 message: Fix of LP BUG#872775.

The problem was that merged views has its own nest_level numbering => when we compare nest levels we should take into considiration basis (i.e. 0 level), if it is different then nest levels are not comparable.

:grep nest_level_base *.h *.c* item.h|1165| st_select_lex_unit *nest_level_base; sql_lex.h|687| SELECT_LEX_UNIT *nest_level_base; /* unit from which we count nest_level */ item.cc|759| depended_from->nest_level_base == prm->nest_level_base && item_subselect.cc|603| unit->first_select()->nest_level_base, item_sum.cc|363| ds->nest_level_base == prm->nest_level_base && sql_lex.cc|342| lex->select_lex.nest_level_base= &lex->unit; sql_parse.cc|5920| select_lex->nest_level_base= &thd->lex->unit; The only places where nest_level_base is assigned are: sql_lex.cc|342| lex->select_lex.nest_level_base= &lex->unit; sql_parse.cc|5920| select_lex->nest_level_base= &thd->lex->unit; both of these initialize it to the top-level unit. So what is the point of having nest_level_base in this case? Please clarify :)

=== modified file 'mysql-test/r/subselect_cache.result' --- a/mysql-test/r/subselect_cache.result 2011-07-19 20:19:10 +0000 +++ b/mysql-test/r/subselect_cache.result 2011-10-25 08:59:36 +0000 @@ -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;

=== modified file 'mysql-test/t/subselect_cache.test' --- a/mysql-test/t/subselect_cache.test 2011-07-19 20:19:10 +0000 +++ b/mysql-test/t/subselect_cache.test 2011-10-25 08:59:36 +0000 @@ -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;

=== modified file 'sql/item.cc' --- a/sql/item.cc 2011-10-22 07:14:27 +0000 +++ b/sql/item.cc 2011-10-25 08:59:36 +0000 @@ -755,7 +755,9 @@ bool Item_ident::remove_dependence_proce 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; }

=== modified file 'sql/item.h' --- a/sql/item.h 2011-10-22 07:14:27 +0000 +++ b/sql/item.h 2011-10-25 08:59:36 +0000 @@ -505,6 +505,7 @@ typedef void (*Cond_traverser) (const It 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

=== modified file 'sql/item_subselect.cc' --- a/sql/item_subselect.cc 2011-10-22 07:14:27 +0000 +++ b/sql/item_subselect.cc 2011-10-25 08:59:36 +0000 @@ -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); }

=== modified file 'sql/item_sum.cc' --- a/sql/item_sum.cc 2011-07-19 20:19:10 +0000 +++ b/sql/item_sum.cc 2011-10-25 08:59:36 +0000 @@ -359,7 +359,9 @@ bool Item_sum::collect_outer_ref_process { 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; }

=== modified file 'sql/sql_lex.cc' --- a/sql/sql_lex.cc 2011-10-22 07:14:27 +0000 +++ b/sql/sql_lex.cc 2011-10-25 08:59:36 +0000 @@ -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;

=== modified file 'sql/sql_lex.h' --- a/sql/sql_lex.h 2011-09-22 22:13:38 +0000 +++ b/sql/sql_lex.h 2011-10-25 08:59:36 +0000 @@ -684,6 +684,7 @@ public: ulong table_join_options; uint in_sum_expr; uint select_number; /* number of select (used for EXPLAIN) */ + SELECT_LEX_UNIT *nest_level_base; /* unit from which we count nest_level */ 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 '*' */

=== modified file 'sql/sql_parse.cc' --- a/sql/sql_parse.cc 2011-10-11 10:55:42 +0000 +++ b/sql/sql_parse.cc 2011-10-25 08:59:36 +0000 @@ -5917,6 +5917,7 @@ mysql_new_select(LEX *lex, bool move_dow 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;

BR Sergey -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog