Bug #110301 Change column wrong result
Submitted: 8 Mar 2023 11:13 Modified: 9 Mar 2023 5:55
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)
Tags: ALTER TABLE

[8 Mar 2023 11:13] Pedro Ferreira 
Description:
Run the following queries:

CREATE TABLE t1 (c0 INT);
ALTER TABLE t1 CHANGE COLUMN c0 c0 DATE NOT NULL;
INSERT INTO t1 (c0) VALUES ROW(t1.c0);

Then run these two:

SELECT 1 FROM t1 WHERE t1.c0 IS NULL;
SELECT CAST(sum(c0) AS SIGNED) FROM (SELECT CAST(t1.c0 IS NULL AS SIGNED) FROM t1) t0 (c0);

Although they are equivalent, the first query returns 1 row, while the global aggregate on the second query returns 0. The number of rows of the first should be the same as the sum result. Doing SELECT t1.c0 FROM t1 returns 0000-00-00 so the value is not null and the first query result is 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 queries above.
[9 Mar 2023 5:55] MySQL Verification Team 
Hello Pedro Ferreira,

Thank you for the report and feedback.

regards,
Umesh