Bug #37115 Subquery result differs between materialization and in->exists strategies

• Submitted: 31 May 2008 2:16
• Modified: 23 Nov 2009 15:01
• Reporter: Sergey Petrunya
• Status: Can't repeat
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 6.0-bk
• OS: Any
• Assigned to: Roy Lyseng
• CPU Architecture: Any
• Tags: materialization, subquery

[31 May 2008 2:16] Sergey Petrunya

Description:
One can get different result when running subquery using materialization and in->exists strategies.

How to repeat:
Apply this patch to mysql-test/t/subselect_mat.test:

===== t/subselect_mat.test 1.8 vs edited =====
--- 1.8/mysql-test/t/subselect_mat.test 2008-05-31 06:08:45 +04:00
+++ edited/t/subselect_mat.test 2008-05-31 06:08:42 +04:00
@@ -491,6 +491,16 @@
 select left(a1,7), left(a2,7)
 from t1_512
 where a1 in (select group_concat(b1) from t2_512 group by b2);
+--echo #############################
+select left(a1,7), left(a2,7)
+from t1_512
+where a1 in (select group_concat(b1) from t2_512 group by b2);
+set @@optimizer_switch='no_materialization';
+select left(a1,7), left(a2,7)
+from t1_512
+where a1 in (select group_concat(b1) from t2_512 group by b2);
+set @@optimizer_switch='no_semijoin';
+--echo #############################

Then run the testcase and see it to fail like this:
main.subselect_mat             [ fail ]

--- /home/psergey/mysql-6.0-look/mysql-test/r/subselect_mat.result      2008-04-25 02:57:15.000000000 +0300
+++ /home/psergey/mysql-6.0-look/mysql-test/r/subselect_mat.reject      2008-05-31 05:08:52.000000000 +0300
@@ -745,6 +745,20 @@
 left(a1,7)     left(a2,7)
 1 - 01x        2 - 01x
 1 - 02x        2 - 02x
+#############################
+select left(a1,7), left(a2,7)
+from t1_512
+where a1 in (select group_concat(b1) from t2_512 group by b2);
+left(a1,7)     left(a2,7)
+1 - 01x        2 - 01x
+1 - 02x        2 - 02x
+set @@optimizer_switch='no_materialization';
+select left(a1,7), left(a2,7)
+from t1_512
+where a1 in (select group_concat(b1) from t2_512 group by b2);
+left(a1,7)     left(a2,7)
+set @@optimizer_switch='no_semijoin';
+#############################
 set @@group_concat_max_len = 256;
 explain extended select left(a1,7), left(a2,7)
 from t1_512

Here we see that the same query will produce different result depending on the optimizer settings, which is a bug.

Suggested fix:
Find out which result is correct and fix the strategy that produces the incorrect one.

[31 May 2008 16:28] Sveta Smirnova

Thank you for the report.

Verified as described.

[8 Jul 2009 9:46] Roy Lyseng

I tested this on azalea branch and it appears that this issue has been fixed.

Tested with the following optimizer switch settings and got same results in each run:

set @@optimizer_switch='default';
set @@optimizer_switch='default,materialization=off';
set @@optimizer_switch='default,semijoin=off';
set @@optimizer_switch='default,materialization=off,semijoin=off';

[23 Nov 2009 15:01] Roy Lyseng

Tried the commands with the following tagged releases on Linux:

Version     Date
----------------------
6.0.3       2007-11-22
6.0.4.alpha 2008-02-12
6.0.5       2008-06-12
6.0.6       2008-08-11
6.0.14      2009-11-23 (untagged)

None of these versions show the malfunction reported in this bugcase, hence I think that this problem must have existed temporarily in a development branch.

The test case reports a problem with no_materialization. This query is already part of subselect_mat.test run with no materialization=off, hence there is no need to extend the test case.