Bug #110322 Binary column primary key query with wrong result

• Submitted: 9 Mar 2023 11:41
• Modified: 9 Mar 2023 12:22
• 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: between, BINARY, primary key

[9 Mar 2023 11:41] Pedro Ferreira

Description:
This issue may be related issue #110310. Run these queries:

CREATE TABLE t1 (c0 BINARY(200) PRIMARY KEY);
INSERT INTO t1(c0) VALUES (CAST(X'62F59F' AS BINARY)),(CAST(X'33BE7B85646822DB7FF3A67FBA69830C11A8F9' AS BINARY)),(CAST(X'AC3B6B430DC8D1A61EAF3CD1D3255C89B01C6B9A9D860465ED4D' AS BINARY)),(CAST(X'' AS BINARY));

Then run these two:

SELECT (1) FROM t1 WHERE c0 BETWEEN '1' AND DATE '2000-9-11';
SELECT CAST(sum(c0) AS SIGNED) FROM (SELECT CAST(c0 BETWEEN '1' AND DATE '2000-9-11' AS SIGNED) FROM t1) t0 (c0);

Although they are equivalent, the first query returns 3 rows, while the global aggregate on the second query returns 4. The number of rows of the first should be the same as the sum result. On the first query, the case for the row CAST(X'' AS BINARY) is missing, so I suspect that one being wrong.

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 statements above.

[9 Mar 2023 12:22] MySQL Verification Team

Hello Pedro Ferreira,

Thank you for the report and feedback.

regards,
Umesh