diff options
author | unknown <timour@askmonty.org> | 2011-07-14 12:53:00 +0300 |
---|---|---|
committer | unknown <timour@askmonty.org> | 2011-07-14 12:53:00 +0300 |
commit | 53681ee5db74e0495efb527e11fc556744967f5a (patch) | |
tree | ea592be46a85ac8d6d282d06c7dd18a3435f1a64 /mysql-test/t/subselect4.test | |
parent | c4097382efab39baaa142b6c475c9f9afc24d787 (diff) | |
download | mariadb-git-53681ee5db74e0495efb527e11fc556744967f5a.tar.gz |
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().
Diffstat (limited to 'mysql-test/t/subselect4.test')
-rw-r--r-- | mysql-test/t/subselect4.test | 34 |
1 files changed, 34 insertions, 0 deletions
diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test index f2dca9d3a4f..cfaddd58685 100644 --- a/mysql-test/t/subselect4.test +++ b/mysql-test/t/subselect4.test @@ -968,6 +968,40 @@ set @@optimizer_switch=@save_optimizer_switch; 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 # |