Hi Timour, Ok to push.
On Mon, Mar 28, 2011 at 12:55:43PM +0300, tim...@askmonty.org wrote: > At file:///home/tsk/mprog/src/5.3/ > > ------------------------------------------------------------ > revno: 2947 > revision-id: tim...@askmonty.org-20110328095536-wbmu1hiwsnhw6bs8 > parent: tim...@askmonty.org-20110324143406-04q5peh1r7nthcyb > committer: tim...@askmonty.org > 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 > comm...@mariadb.org > 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 _______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp