Bug #110563 NULL safe equality operator query with wrong result

• Submitted: 30 Mar 2023 8:21
• Modified: 30 Mar 2023 8:47
• Reporter: Pedro Ferreira
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.32
• OS: Ubuntu (22.04)
• CPU Architecture: x86 (x86_64)
• Tags: equality, null

[30 Mar 2023 8:21] Pedro Ferreira

Description:
Create the table:

CREATE TABLE t0 (c0 INT, c1 INT);
INSERT INTO t0(c0,c1) VALUES (43,47),(92,71);

Then run these queries:

SELECT 1 FROM t0 tx RIGHT JOIN t0 ty ON ty.c0 <=> 1 WHERE ROW(ty.c0, tx.c1) >= ROW(ty.c1, ty.c1);
SELECT CAST(sum(c0) AS SIGNED) FROM (SELECT CAST(ROW(ty.c0, tx.c1) >= ROW(ty.c1, ty.c1) AS SIGNED) FROM t0 tx RIGHT JOIN t0 ty ON ty.c0 <=> 1) t1 (c0);

Although they are equivalent, the first query returns no rows, while the global aggregate on the second query returns 1. The number of rows of the first should be the same as the sum result. The rows compared are:

(43, NULL) >= (47,47)
(92, NULL) >= (71,71)

The second comparison evaluates to true because 92 >= 71, so the first query should return that row that doesn't.

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.

[30 Mar 2023 8:47] MySQL Verification Team

Hello Pedro Ferreira,

Thank you for the report and feedback.

regards,
Umesh