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
Sergey Petrunya