maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #04254
Fwd: [Commits] Rev 3108: Fix bug lp:777691 in file:///home/tsk/mprog/src/5.3-work/
Sergey,
Please review the below patch. As discussed, all other
solutions that try to keep the call to mark_as_null_row()
lead to a chicken-egg-like problem that is tricky to
solve. Since I am not sure it is worth solving, I suggest
the below simple solution.
Timour
------------------------------------------------------------
revno: 3108
revision-id: timour@xxxxxxxxxxxx-20110714095300-bwi0z7x542cj2avd
parent: timour@xxxxxxxxxxxx-20110714072218-hty7xnwn1dxmn9a3
fixes bug(s): https://launchpad.net/bugs/777691
committer: timour@xxxxxxxxxxxx
branch nick: 5.3-work
timestamp: Thu 2011-07-14 12:53:00 +0300
message:
Fix bug lp:777691
Analysis:
For some of the re-executions of the correlated subquery the
where clause is false. In these cases the execution of the
subquery detects that it must generate a NULL row because of
implicit grouping. In this case the subquery execution reaches
the following code in do_select():
while ((table= li++))
mark_as_null_row(table->table);
This code marks all rows in the table as complete NULL rows.
In the example, when evaluating the field t2.f10 for the second
row, all bits of Field::null_ptr[0] are set by the previous call
to mark_as_null_row(). Then the call to Field::is_null()
returns true, resulting in a NULL for the MAX function.
Thus the lines above are not suitable for subquery re-execution
because mark_as_null_row() changes the NULL bits of each table
field, and there is no logic to restore these fields.
Solution:
The call to mark_as_null_row() was added by the fix for bug
lp:613029. Therefore removing the fix for lp:613029 corrects
this wrong result. At the same time the test for lp:613029
behaves correctly because the changes of MWL#89 result in a
different execution path where:
- the constant subquery is evaluated via JOIN::exec_const_cond
- detecting that it has an empty result triggers the branch
if (zero_result_cause)
return_zero_rows()
- return_zero_rows() calls mark_as_null_row().
=== modified file 'mysql-test/r/subselect4.result'
--- a/mysql-test/r/subselect4.result 2011-07-09 07:20:15 +0000
+++ b/mysql-test/r/subselect4.result 2011-07-14 09:53:00 +0000
@@ -1190,6 +1190,54 @@ set @@optimizer_switch='materialization=
set @@optimizer_switch=@save_optimizer_switch;
drop table t1, t2;
#
+# LP BUG#777691 Wrong result with subqery in select list and subquery cache=off in maria-5.3
+#
+CREATE TABLE t1 ( f1 varchar(32)) ;
+INSERT INTO t1 VALUES ('b'),('x'),('c'),('x');
+CREATE TABLE t2 ( f2 int, f3 varchar(32)) ;
+INSERT INTO t2 VALUES (1,'x');
+set @save_optimizer_switch=@@optimizer_switch;
+set @@optimizer_switch='materialization=off,in_to_exists=on,subquery_cache=off';
+EXPLAIN
+SELECT t1.f1, ( SELECT MAX( f2 ) FROM t2 WHERE t2.f3 = t1.f1 ) as max_f2 FROM t1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 4
+2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 1
+SELECT t1.f1, ( SELECT MAX( f2 ) FROM t2 WHERE t2.f3 = t1.f1 ) as max_f2 FROM t1;
+f1 max_f2
+b NULL
+x 1
+c NULL
+x 1
+set @@optimizer_switch='materialization=on,in_to_exists=off,subquery_cache=off';
+EXPLAIN
+SELECT t1.f1, ( SELECT MAX( f2 ) FROM t2 WHERE t2.f3 = t1.f1 ) as max_f2 FROM t1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 4
+2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 1
+SELECT t1.f1, ( SELECT MAX( f2 ) FROM t2 WHERE t2.f3 = t1.f1 ) as max_f2 FROM t1;
+f1 max_f2
+b NULL
+x 1
+c NULL
+x 1
+set @@optimizer_switch='materialization=off,in_to_exists=on,subquery_cache=off';
+Even when t2 is not constant table, the result must be the same.
+INSERT INTO t2 VALUES (2,'y');
+EXPLAIN
+SELECT t1.f1, ( SELECT MAX( f2 ) FROM t2 WHERE t2.f3 = t1.f1 ) as max_f2 FROM t1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 4
+2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
+SELECT t1.f1, ( SELECT MAX( f2 ) FROM t2 WHERE t2.f3 = t1.f1 ) as max_f2 FROM t1;
+f1 max_f2
+b NULL
+x 1
+c NULL
+x 1
+set @@optimizer_switch=@save_optimizer_switch;
+drop table t1, t2;
+#
# LP BUG#641203 Query returns rows where no result is expected (impossible WHERE)
#
CREATE TABLE t1 (c1 varchar(1) DEFAULT NULL);
=== modified file 'mysql-test/t/subselect4.test'
--- a/mysql-test/t/subselect4.test 2011-07-09 07:20:15 +0000
+++ b/mysql-test/t/subselect4.test 2011-07-14 09:53:00 +0000
@@ -968,6 +968,40 @@ set @@optimizer_switch=@save_optimizer_s
drop table t1, t2;
+
+--echo #
+--echo # LP BUG#777691 Wrong result with subqery in select list and subquery cache=off in maria-5.3
+--echo #
+
+CREATE TABLE t1 ( f1 varchar(32)) ;
+INSERT INTO t1 VALUES ('b'),('x'),('c'),('x');
+
+CREATE TABLE t2 ( f2 int, f3 varchar(32)) ;
+INSERT INTO t2 VALUES (1,'x');
+
+set @save_optimizer_switch=@@optimizer_switch;
+set @@optimizer_switch='materialization=off,in_to_exists=on,subquery_cache=off';
+
+EXPLAIN
+SELECT t1.f1, ( SELECT MAX( f2 ) FROM t2 WHERE t2.f3 = t1.f1 ) as max_f2 FROM t1;
+SELECT t1.f1, ( SELECT MAX( f2 ) FROM t2 WHERE t2.f3 = t1.f1 ) as max_f2 FROM t1;
+
+set @@optimizer_switch='materialization=on,in_to_exists=off,subquery_cache=off';
+EXPLAIN
+SELECT t1.f1, ( SELECT MAX( f2 ) FROM t2 WHERE t2.f3 = t1.f1 ) as max_f2 FROM t1;
+SELECT t1.f1, ( SELECT MAX( f2 ) FROM t2 WHERE t2.f3 = t1.f1 ) as max_f2 FROM t1;
+
+set @@optimizer_switch='materialization=off,in_to_exists=on,subquery_cache=off';
+--echo Even when t2 is not constant table, the result must be the same.
+INSERT INTO t2 VALUES (2,'y');
+EXPLAIN
+SELECT t1.f1, ( SELECT MAX( f2 ) FROM t2 WHERE t2.f3 = t1.f1 ) as max_f2 FROM t1;
+SELECT t1.f1, ( SELECT MAX( f2 ) FROM t2 WHERE t2.f3 = t1.f1 ) as max_f2 FROM t1;
+
+set @@optimizer_switch=@save_optimizer_switch;
+
+drop table t1, t2;
+
--echo #
--echo # LP BUG#641203 Query returns rows where no result is expected (impossible WHERE)
--echo #
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2011-07-14 03:00:28 +0000
+++ b/sql/sql_select.cc 2011-07-14 09:53:00 +0000
@@ -14651,14 +14651,6 @@ 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.
- */
- TABLE_LIST *table;
- List_iterator_fast<TABLE_LIST> li(join->select_lex->leaf_tables);
- while ((table= li++))
- mark_as_null_row(table->table);
rc= join->result->send_data(*columns_list) > 0;
}
}
_______________________________________________
commits mailing list
commits@xxxxxxxxxxx
https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits
