MySQL Bugs: #110614: Left join query with wrong result

Bug #110614	Left join query with wrong result
Submitted:	5 Apr 2023 8:32	Modified:	5 Apr 2023 13:42
Reporter:	Pedro Ferreira	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	left join

Description:
Using the same principle from Bug #110579

SELECT 1 FROM (SELECT 1) x(x) LEFT JOIN (SELECT 1) y(y) ON FALSE; --1 row
SELECT 1 FROM (SELECT 1) x(x) LEFT JOIN (SELECT 1) y(y) ON FALSE WHERE interval(y.y, x.x) IS NOT NULL; --0 rows
SELECT 1 FROM (SELECT 1) x(x) LEFT JOIN (SELECT 1) y(y) ON FALSE WHERE NOT (interval(y.y, x.x) IS NOT NULL); --0 rows
SELECT 1 FROM (SELECT 1) x(x) LEFT JOIN (SELECT 1) y(y) ON FALSE WHERE (interval(y.y, x.x) IS NOT NULL) IS NULL; --0 rows

By looking more carefully, the query result from:

SELECT 1 FROM (SELECT 1) x(x) LEFT JOIN (SELECT 1) y(y) ON FALSE WHERE interval(y.y, x.x) IS NOT NULL;

is wrong, interval(y.y, x.x) outputs -1 which is not null and the predicate evaluates to true, so 1 row in the output is expected.

I am not sure if all of these right/left join query bugs result from the same 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.

Hi Mr. Ferreira,

Thank you for your bug report.

We repeated your results.

Your query should have returned syntax errors for several reasons, but it did not ..... For example, if you omit the last clause, ON FALSE, you get a syntax error. 

There is a problem with aliases and with usage of fields in the INTERVAL() function.

Hence, it is our opinion that the absence or syntax error is the more important bug then the wrong results.

Verified as reported.

We  checked it for other OS and hardware and repeated it, which is why we changed certain flags .......