MySQL Bugs: #115559: Row constructor comparison with nested join fails to match rows

Bug #115559	Row constructor comparison with nested join fails to match rows
Submitted:	10 Jul 2024 13:59	Modified:	11 Jul 2024 10:45
Reporter:	Timothy Liang	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, nested join, row constructor

Description:
My program runs the query SELECT t1.id, u1.id, t2.id, u2.id FROM test t1 LEFT JOIN u u1 ON u1.id = t1.id JOIN (test t2 LEFT JOIN u u2 ON u2.id = t2.id) ON (t2.id, u2.id) > (t1.id, u1.id). I expected MySQL to return the row (1, 1, 2, NULL). It gave me no rows.

How to repeat:
CREATE TABLE test (
  id INT NOT NULL
);
INSERT INTO test (id) VALUES (1);
INSERT INTO test (id) VALUES (2);

CREATE TABLE u (
  id INT NOT NULL
);
INSERT INTO u (id) VALUES (1);

SELECT t1.id, u1.id, t2.id, u2.id FROM test t1 LEFT JOIN u u1 ON u1.id = t1.id JOIN (test t2 LEFT JOIN u u2 ON u2.id = t2.id) ON (t2.id, u2.id) > (t1.id, u1.id);

Hi Mr. Liang,

Thank you for your bug report.

However , this is not a bug .....

You have a row condition:

ON (t2.id, u2.id) > (t1.id, u1.id

but, since t1 is identical with t2 and u1 is identical with u2, how can one be greater then the other.

t1, t2, u1, u2 are just aliases of the real tables ....... so you can not have any inequality expression returning TRUE.

Not a bug.

Hi MR. Liang,

After further examination, due to the inequality expression, we decided that this is a bug.

It affects version 8.0 and all higher versions.

Verified as reported.