| Bug #112306 | Unexpected Result by JOIN_SUFFIX | ||
|---|---|---|---|
| Submitted: | 11 Sep 2023 1:49 | Modified: | 12 Sep 2023 11:26 |
| Reporter: | JINSHENG BA | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 8.1.0, 8.0.34 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[11 Sep 2023 4:59]
MySQL Verification Team
Hello Jinsheng Ba, Thank you for the report and test case. Verified as described. regards, Umesh
[12 Sep 2023 11:25]
Knut Anders Hatlen
Posted by developer: This bug appears to have the same cause as bug#112296, so I'm closing it as a duplicate. The issue is that WHERE (t0.c0 IS NOT NULL) = (BIT_COUNT(t3.c2)) is incorrectly considered NULL-rejecting on table t0, so the optimizer ends up pruning a too big subtree from the join when it detects that t0 will return only NULL-complemented rows. Bug#112296 has a similar IS NOT NULL predicate on a pruned table, and suffers from the same issue.
Description: The same query returns different results with the hint: JOIN_SUFFIX. CREATE TABLE t0(c0 INT); CREATE TABLE t1(c0 INT); CREATE TABLE t2(c0 INT, c1 INT, c2 INT); CREATE TABLE t3(c0 INT, c1 INT, c2 INT); INSERT IGNORE INTO t3 VALUES(1, 2, 0.1); INSERT INTO t1 VALUES(-3); SELECT COUNT(*) FROM t3, t2 INNER JOIN t0 ON t0.c0 RIGHT JOIN t1 ON FALSE WHERE (t0.c0 IS NOT NULL) = (BIT_COUNT(t3.c2)); -- {1} SELECT /*+ JOIN_SUFFIX(t2, t3)*/ COUNT(*) FROM t3, t2 INNER JOIN t0 ON t0.c0 RIGHT JOIN t1 ON FALSE WHERE (t0.c0 IS NOT NULL) = (BIT_COUNT(t3.c2)); -- {0} How to repeat: docker run -it -p 3306:3306 -e MYSQL_ROOT_PASSWORD=root mysql:8.1.0 Then execute the above test case.