Hi Timour, Ok to push. On Mon, Mar 28, 2011 at 12:55:43PM +0300, timour@askmonty.org wrote:

At file:///home/tsk/mprog/src/5.3/

------------------------------------------------------------ revno: 2947 revision-id: timour@askmonty.org-20110328095536-wbmu1hiwsnhw6bs8 parent: timour@askmonty.org-20110324143406-04q5peh1r7nthcyb committer: timour@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 commits@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