Bug #59943 materialization=on/off leads to different result set when using IN & VAR_POP

• Submitted: 4 Feb 2011 11:12
• Modified: 29 Jul 2012 22:37
• Reporter: Roel Van de Paar
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S1 (Critical)
• Version: 5.6.2-m5
• OS: Any
• CPU Architecture: Any
• Tags: materialization, optimizer_switch, subquery

[4 Feb 2011 11:12] Roel Van de Paar

Description:
SELECT * FROM t2 WHERE (X) IN (SELECT VAR_POP(`id`) AS X FROM C);
Returns different results (using a InnoDB, MyISAM or Memory table) when materialization is turned on or off.

How to repeat:
DROP DATABASE IF EXISTS test2; CREATE DATABASE test2; USE test2; /* Test setup */

CREATE TABLE C (id int);
INSERT INTO C VALUES (5),(9),(9);

CREATE TABLE t2 SELECT VAR_POP(`id`) AS X FROM C;

SET @@SESSION.optimizer_switch="materialization=off";

SELECT * FROM t2 WHERE (X) IN (SELECT VAR_POP(`id`) AS X FROM C);  /* 3.5556 */

SET @@SESSION.optimizer_switch="materialization=on";

SELECT * FROM t2 WHERE (X) IN (SELECT VAR_POP(`id`) AS X FROM C);  /* No results */

Suggested fix:
Return same results.

[6 Feb 2011 22:13] Roel Van de Paar

See also bug #59833

[29 Jul 2012 22:37] Paul DuBois

Noted in 5.6.6 changelog. 

IN subqueries that used a variance or standard deviation aggregate 
function could return a different result depending on whether the 
optimizer_switch materialization flag was enabled.