maria-developers team mailing list archive

[Sergey Petrunya <psergey@xxxxxxxxxxxx>]

Hi Timour,

Ok to push.

On Mon, Mar 28, 2011 at 12:55:43PM +0300, timour@xxxxxxxxxxxx wrote:
> At file:///home/tsk/mprog/src/5.3/
> 
> ------------------------------------------------------------
> revno: 2947
> revision-id: timour@xxxxxxxxxxxx-20110328095536-wbmu1hiwsnhw6bs8
> parent: timour@xxxxxxxxxxxx-20110324143406-04q5peh1r7nthcyb
> committer: timour@xxxxxxxxxxxx
> branch nick: 5.3
> timestamp: Mon 2011-03-28 12:55:36 +0300
> message:
>   Fix LP BUG#613029
>   
>   Analysis:
>   There are two code paths through which JOIN::exec may produce
>   an all-NULL row for an empty result set. One goes via the
>   function return_zero_rows(), when query processing detectes
>   early that the where clause is false, the other one is via
>   do_select() in the case of join execution.
>   
>   In the case of do_select(), the problem was that the executioner
>   didn't set TABLE::null_row to 1. As result when sending the only
>   result row, the evaluation of each field didn't detect that all
>   non-aggregated fields are NULL, because Field::is_null returned
>   true, after checking that field->table->null_row was false.
>   
>   Given that the each non-aggregated field was not considered NULL,
>   select_result::send_data sent whatever was in the buffer of each
>   field. However, since there was no actual data in the field buffer,
>   send_data() accessed and sent whatever junk was in the field's
>   data buffer.
>   
>   Solution:
>   Similar to the analogous case in return_zero_rows() mark all
>   tables that their current row is NULL before sending the
>   artificailly created NULL row.

> === modified file 'mysql-test/r/subselect4.result'
> --- a/mysql-test/r/subselect4.result	2011-03-24 14:34:06 +0000
> +++ b/mysql-test/r/subselect4.result	2011-03-28 09:55:36 +0000
> @@ -1133,3 +1133,55 @@ SELECT * FROM t1 WHERE (2, 0) NOT IN (SE
>  f1      f2
>  set @@optimizer_switch=@save_optimizer_switch;
>  drop table t1,t2;
> +#
> +# LP BUG#613029 Wrong result with materialization and semijoin, and
> +# valgrind warnings in Protocol::net_store_data with materialization
> +# for implicit grouping
> +#
> +CREATE TABLE t1 (
> +pk int(11) NOT NULL AUTO_INCREMENT,
> +f2 int(11) NOT NULL,
> +f3 varchar(1) NOT NULL,
> +PRIMARY KEY (pk),
> +KEY f2 (f2));
> +INSERT INTO t1 VALUES (1,9,'x');
> +INSERT INTO t1 VALUES (2,5,'g');
> +CREATE TABLE t2 (
> +pk int(11) NOT NULL AUTO_INCREMENT,
> +f2 int(11) NOT NULL,
> +f3 varchar(1) NOT NULL,
> +PRIMARY KEY (pk),
> +KEY f2 (f2));
> +INSERT INTO t2 VALUES (1,7,'p');
> +set @save_optimizer_switch=@@optimizer_switch;
> +set @@optimizer_switch='materialization=off,semijoin=off';
> +EXPLAIN
> +SELECT t1.f3, MAX(t1.f2)
> +FROM t1, t2
> +WHERE (t2.pk = t1.pk) AND t2.pk IN (SELECT f2 FROM t1);
> +id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
> +1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
> +2       DEPENDENT SUBQUERY      t1      index_subquery  f2      f2      4       func    2       Using index
> +SELECT t1.f3, MAX(t1.f2)
> +FROM t1, t2
> +WHERE (t2.pk = t1.pk) AND t2.pk IN (SELECT f2 FROM t1);
> +f3      MAX(t1.f2)
> +NULL    NULL
> +set @@optimizer_switch='materialization=on,semijoin=off';
> +EXPLAIN
> +SELECT t1.f3, MAX(t1.f2)
> +FROM t1, t2
> +WHERE (t2.pk = t1.pk) AND t2.pk IN (SELECT f2 FROM t1);
> +id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
> +1       PRIMARY t2      system  PRIMARY NULL    NULL    NULL    1       
> +1       PRIMARY t1      const   PRIMARY PRIMARY 4       const   1       
> +2       SUBQUERY        t1      index   NULL    f2      4       NULL    2       Using index
> +SELECT t1.f3, MAX(t1.f2)
> +FROM t1, t2
> +WHERE (t2.pk = t1.pk) AND t2.pk IN (SELECT f2 FROM t1);
> +f3      MAX(t1.f2)
> +NULL    NULL
> +TODO: add a test case for semijoin when the wrong result is fixed
> +set @@optimizer_switch='materialization=off,semijoin=on';
> +set @@optimizer_switch=@save_optimizer_switch;
> +drop table t1, t2;
> 
> === modified file 'mysql-test/t/subselect4.test'
> --- a/mysql-test/t/subselect4.test	2011-03-24 14:34:06 +0000
> +++ b/mysql-test/t/subselect4.test	2011-03-28 09:55:36 +0000
> @@ -906,3 +906,60 @@ SELECT * FROM t1 WHERE (2, 0) NOT IN (SE
>  set @@optimizer_switch=@save_optimizer_switch;
>  
>  drop table t1,t2;
> +
> +--echo #
> +--echo # LP BUG#613029 Wrong result with materialization and semijoin, and
> +--echo # valgrind warnings in Protocol::net_store_data with materialization
> +--echo # for implicit grouping
> +--echo #
> +
> +CREATE TABLE t1 (
> +  pk int(11) NOT NULL AUTO_INCREMENT,
> +  f2 int(11) NOT NULL,
> +  f3 varchar(1) NOT NULL,
> +  PRIMARY KEY (pk),
> +  KEY f2 (f2));
> +
> +INSERT INTO t1 VALUES (1,9,'x');
> +INSERT INTO t1 VALUES (2,5,'g');
> +
> +CREATE TABLE t2 (
> +  pk int(11) NOT NULL AUTO_INCREMENT,
> +  f2 int(11) NOT NULL,
> +  f3 varchar(1) NOT NULL,
> +  PRIMARY KEY (pk),
> +  KEY f2 (f2));
> +
> +INSERT INTO t2 VALUES (1,7,'p');
> +
> +set @save_optimizer_switch=@@optimizer_switch;
> +
> +set @@optimizer_switch='materialization=off,semijoin=off';
> +
> +EXPLAIN
> +SELECT t1.f3, MAX(t1.f2)
> +FROM t1, t2
> +WHERE (t2.pk = t1.pk) AND t2.pk IN (SELECT f2 FROM t1);
> +
> +SELECT t1.f3, MAX(t1.f2)
> +FROM t1, t2
> +WHERE (t2.pk = t1.pk) AND t2.pk IN (SELECT f2 FROM t1);
> +
> +set @@optimizer_switch='materialization=on,semijoin=off';
> +
> +EXPLAIN
> +SELECT t1.f3, MAX(t1.f2)
> +FROM t1, t2
> +WHERE (t2.pk = t1.pk) AND t2.pk IN (SELECT f2 FROM t1);
> +
> +SELECT t1.f3, MAX(t1.f2)
> +FROM t1, t2
> +WHERE (t2.pk = t1.pk) AND t2.pk IN (SELECT f2 FROM t1);
> +
> +-- echo TODO: add a test case for semijoin when the wrong result is fixed
> +-- echo set @@optimizer_switch='materialization=off,semijoin=on';
> +
> +
> +set @@optimizer_switch=@save_optimizer_switch;
> +
> +drop table t1, t2;
> 
> === modified file 'sql/sql_select.cc'
> --- a/sql/sql_select.cc	2011-03-24 14:34:06 +0000
> +++ b/sql/sql_select.cc	2011-03-28 09:55:36 +0000
> @@ -13452,6 +13452,13 @@ do_select(JOIN *join,List<Item> *fields,
>        {
>          List<Item> *columns_list= (procedure ? &join->procedure_fields_list :
>                                     fields);
> +        /*
> +          With implicit grouping all fields of special row produced for an
> +          empty result are NULL. See return_zero_rows() for the same behavior.
> +        */
> +        for (TABLE_LIST *table= join->select_lex->leaf_tables;
> +             table; table= table->next_leaf)
> +          mark_as_null_row(table->table);
>          rc= join->result->send_data(*columns_list);
>        }
>      }
> 
> === modified file 'sql/sql_select.h'
> --- a/sql/sql_select.h	2011-03-13 10:50:14 +0000
> +++ b/sql/sql_select.h	2011-03-28 09:55:36 +0000
> @@ -954,8 +954,8 @@ public:
>    bool init_save_join_tab();
>    bool send_row_on_empty_set()
>    {
> -    return (do_send_rows && tmp_table_param.sum_func_count != 0 &&
> -            !group_list && having_value != Item::COND_FALSE);
> +    return (do_send_rows && implicit_grouping &&
> +            having_value != Item::COND_FALSE);
>    }
>    bool change_result(select_result *result);
>    bool is_top_level_join() const
> 

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

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