Bug #111431 condition_fanout_filter optimizer wrong result?

• Submitted: 15 Jun 2023 8:47
• Modified: 15 Jun 2023 9:16
• Reporter: Pedro Ferreira
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.33
• OS: Ubuntu (22.04)
• CPU Architecture: Other (x86_64)
• Tags: Optimizer

[15 Jun 2023 8:47] Pedro Ferreira

Description:
Run the queries:

CREATE TABLE t0 (c0 INT AUTO_INCREMENT UNIQUE KEY, c1 INT);
CREATE TABLE t1 (c0 INT, c1 INT AUTO_INCREMENT UNIQUE KEY);
INSERT INTO t0 VALUES (1,1),(2,1),(4,1),(5,2),(6,3),(7,4);
INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4);

SET @@SESSION.OPTIMIZER_SWITCH = 'condition_fanout_filter=off';
SELECT c0 FROM (SELECT lag(t1.c1, 3) OVER () FROM t0 JOIN t1 ON t0.c0 = (t0.c0 DIV t0.c0)) t0 (c0) ORDER BY c0;

+------+
| c0   |
+------+
| NULL |
| NULL |
| NULL |
|    4 |
+------+

SET @@SESSION.OPTIMIZER_SWITCH = 'condition_fanout_filter=on';
SELECT c0 FROM (SELECT lag(t1.c1, 3) OVER () FROM t0 JOIN t1 ON t0.c0 = (t0.c0 DIV t0.c0)) t0 (c0) ORDER BY c0;

+------+
| c0   |
+------+
| NULL |
| NULL |
| NULL |
|    1 |
+------+

Despite the explicit order by clause in the outermost query, the query results are different. Is this a bug?

The compilation parameters are the same as issue 108148:

-DWITH_DEBUG=1 -DWITH_ASAN=ON -DWITH_UBSAN=ON and boost library version 1.77

How to repeat:
Run the queries above.

[15 Jun 2023 9:16] MySQL Verification Team

Hello Pedro Ferreira,

Thank you for the report and feedback.
Verified as described.

regards,
Umesh

[26 Jun 2023 14:48] huahua xu

Hi Pedro Ferreira,

The parameter `condition_fanout_filter` affected the choice of driver table which made by optimizer. For the case, You can use  STRAIGHT_JOIN to avoid puting the tables in the wrong order by the join optimizer

mysql> SET @@SESSION.OPTIMIZER_SWITCH = 'condition_fanout_filter=on';
mysql> SELECT c0 FROM (SELECT lag(t1.c1, 3) OVER () FROM t0 STRAIGHT_JOIN t1 ON t0.c0 = (t0.c0 DIV t0.c0)) t0 (c0) ORDER BY c0;
+------+
| c0   |
+------+
| NULL |
| NULL |
| NULL |
|    1 |
+------+

mysql>  SET @@SESSION.OPTIMIZER_SWITCH = 'condition_fanout_filter=off';
mysql> SELECT c0 FROM (SELECT lag(t1.c1, 3) OVER () FROM t0 STRAIGHT_JOIN t1 ON t0.c0 = (t0.c0 DIV t0.c0)) t0 (c0) ORDER BY c0;
+------+
| c0   |
+------+
| NULL |
| NULL |
| NULL |
|    1 |
+------+

In addition, I do not think that it is a bug.