maria-developers team mailing list archive

[Sergei Petrunia <psergey@xxxxxxxxxxxx>]

Hi Sanja,

Please add a comment around the defitions of nest_level / nest_level_base that
nest_levels are local to the query or VIEW, and that view merge procedure does
not re-calculate them.

Ok to push after the above is addressed.

On Tue, Oct 25, 2011 at 11:59:37AM +0300, sanja@xxxxxxxxxxxx wrote:
> At file:///home/bell/maria/bzr/work-maria-5.3-lpb872775/
> 
> ------------------------------------------------------------
> revno: 3249
> revision-id: sanja@xxxxxxxxxxxx-20111025085936-h0jyyxtbv3qhp4f4
> parent: igor@xxxxxxxxxxxx-20111023124603-sjv665ajk25b4dgi
> committer: sanja@xxxxxxxxxxxx
> 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.

> === 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> &parameters)
>  {
> -  Collect_deps_prm prm= { unit->first_select()->nest_level, &parameters };
> +  Collect_deps_prm prm= {&parameters,
> +    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;
> 

> _______________________________________________
> commits mailing list
> commits@xxxxxxxxxxx
> https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits

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