Bug #110250 Right join query with wrong result
Submitted: 1 Mar 2023 11:59 Modified: 7 Mar 2023 7:04
Reporter: Pedro Ferreira Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0.32 OS:Ubuntu (22.04)
Assigned to: CPU Architecture:x86 (x86_64)
Tags: RIGHT JOIN

[1 Mar 2023 11:59] Pedro Ferreira 
Description:
Create the tables:

CREATE TABLE t1 (c0 BIGINT);
CREATE TABLE t0 (c0 BOOLEAN);
INSERT INTO t0 VALUES (1),(NULL);

Then run these queries:

SELECT 1 FROM t1 RIGHT JOIN t0 USING (c0) WHERE -1 < char(t1.c0);
SELECT CAST(sum(c0) AS SIGNED) FROM (SELECT CAST(-1 < char(t1.c0) AS SIGNED) FROM t1 RIGHT JOIN t0 USING (c0)) t0 (c0);

Although they are equivalent, the first query returns no rows, while the global aggregate on the second query returns 2. The number of rows of the first should be the same as the sum result. t1.c0 evaluates to NULL because no rows in t0 match t1, so char(t1.c0) returns 0 and `-1 < char(t1.c0)` evaluates to true. Therefore the first query should return 2 rows.

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.
[7 Mar 2023 7:04] MySQL Verification Team 
Hello Pedro Ferreira,

Thank you for the report and feedback.

regards,
Umesh