Bug #109824 Bit query with wrong result
Submitted: 27 Jan 2023 15:43 Modified: 28 Jan 2023 8:01
Reporter: Pedro Ferreira Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0.32 OS:Ubuntu (22.04)
Assigned to: CPU Architecture:x86 (x86_64)

[27 Jan 2023 15:43] Pedro Ferreira
Description:
Create the view:

CREATE VIEW t0(c2) AS (SELECT b'1');

Then run these two queries:

SELECT 1 FROM t0 WHERE NOT t0.c2;

SELECT CAST(sum(c0) AS SIGNED) FROM (SELECT CAST(NOT t0.c2 AS SIGNED) FROM t0) 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. b'1' evaluates to true, so not b'1' evaluates to false and the second query should return 0.

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

Thank you for the report and feedback.

regards,
Umesh