Bug #109736 ANY query with wrong result
Submitted: 23 Jan 2023 10:41 Modified: 25 Jan 2023 7:40
Reporter: Pedro Ferreira Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:8.0.32 OS:Ubuntu (22.04)
Assigned to: CPU Architecture:x86 (x86_64)
Tags: ANY, subquery

[23 Jan 2023 10:41] Pedro Ferreira 
Description:
Run these two queries:

SELECT 1 FROM DUAL WHERE 2 < ANY(VALUES ROW(CAST(X'7C' AS BINARY)),ROW(CAST(X'31' AS BINARY)));

SELECT CAST(sum(c0) AS SIGNED) FROM (SELECT CAST(2 < ANY(VALUES ROW(CAST(X'7C' AS BINARY)),ROW(CAST(X'31' AS BINARY))) AS SIGNED) FROM DUAL) t0(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. 2 should get converted to X'32', so the ANY predicate should evaluate to true and the first query return 1 row.

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

Thank you for the report and feedback.

regards,
Umesh