summaryrefslogtreecommitdiff
path: root/mysql-test/t/subselect4.test
diff options
context:
space:
mode:
authorunknown <timour@askmonty.org>2011-07-14 12:53:00 +0300
committerunknown <timour@askmonty.org>2011-07-14 12:53:00 +0300
commit53681ee5db74e0495efb527e11fc556744967f5a (patch)
treeea592be46a85ac8d6d282d06c7dd18a3435f1a64 /mysql-test/t/subselect4.test
parentc4097382efab39baaa142b6c475c9f9afc24d787 (diff)
downloadmariadb-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.test34
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 #