| Bug #111318 | block_nested_loop optimizer wrong result? | ||
|---|---|---|---|
| Submitted: | 7 Jun 2023 14:36 | Modified: | 7 Jun 2023 14:42 |
| Reporter: | Pedro Ferreira | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 8.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | block_nested_loop | ||
[7 Jun 2023 14:42]
MySQL Verification Team
HI Mr. Ferreira, Thank you for your bug report. We managed to repeat your test case: +------+------+ | c0 | c1 | +------+------+ | A | 1 | | B | 1 | | C | NULL | +------+------+ +------+------+ | c0 | c1 | +------+------+ | A | NULL | | B | 1 | | C | 1 | +------+------+ In our opinion, this is a bug. Verified as reported.
Description: Run the queries: CREATE TABLE t0 (c0 TEXT); INSERT INTO t0(c0) VALUES ('A'),('B'),('C'),(NULL); SET @@SESSION.OPTIMIZER_SWITCH = 'block_nested_loop=off'; SELECT c0, c1 FROM (SELECT t0.c0, CASE WHEN lead(5) OVER () THEN 1 WHEN t0.c0 THEN 2 END FROM t0 STRAIGHT_JOIN (SELECT COUNT(1) WHERE FALSE) x(c0) USING (c0)) t0(c0,c1) ORDER BY c0, c1; +------+------+ | c0 | c1 | +------+------+ | A | 1 | | B | 1 | | C | NULL | +------+------+ SET @@SESSION.OPTIMIZER_SWITCH = 'block_nested_loop=on'; SELECT c0, c1 FROM (SELECT t0.c0, CASE WHEN lead(5) OVER () THEN 1 WHEN t0.c0 THEN 2 END FROM t0 STRAIGHT_JOIN (SELECT COUNT(1) WHERE FALSE) x(c0) USING (c0)) t0(c0,c1) ORDER BY c0, c1; +------+------+ | c0 | c1 | +------+------+ | A | NULL | | B | 1 | | C | 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.