Bug #110170 ZEROFILL should not change the query result

• Submitted: 22 Feb 2023 12:43
• Modified: 22 Feb 2023 16:25
• Reporter: John Jove
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 8.0.32
• OS: Any
• CPU Architecture: Any

[22 Feb 2023 12:43] John Jove

Description:
I try the following two test cases, where they have the same statements except for the CREATE TABLE statement. I expect these two SELECT statements should return the same result, because the only difference between the two CREATE TABLE statements is with/without column constraint ZEROFILL. I think ZEROFILL should not change the query result. That's the SELECT statement from the second test case is expected to return the same result as the SELECT statement from the first test case.

How to repeat:
The first test case is the following:
CREATE TABLE `t0`(`c1` BIGINT(20));
INSERT INTO t0 VALUES (0);
SELECT t0.c1 FROM t0 WHERE ((IF(t0.c1, 1, t0.c1)) LIKE ((t0.c1) NOT IN (t0.c1))) || (t0.c1); -- {0}

The second test case is the following:
CREATE TABLE `t0`(`c1` BIGINT(20) ZEROFILL);
INSERT INTO t0 VALUES (0);
SELECT t0.c1 FROM t0 WHERE ((IF(t0.c1, 1, t0.c1)) LIKE ((t0.c1) NOT IN (t0.c1))) || (t0.c1); -- {}

[22 Feb 2023 14:39] John Jove

For the second test case, if I revert the where clause or append IS NULL to it, the queries still return an empty result.

CREATE TABLE `t0`(`c1` BIGINT(20) ZEROFILL);
INSERT INTO t0 VALUES (0);
SELECT t0.c1 FROM t0 WHERE ((IF(t0.c1, 1, t0.c1)) LIKE ((t0.c1) NOT IN (t0.c1))) || (t0.c1); -- {}
SELECT t0.c1 FROM t0 WHERE (! (((IF(t0.c1, -1448582331, t0.c1)) LIKE ((t0.c1) NOT IN (t0.c1))) || (t0.c1))); -- {}
SELECT t0.c1 FROM t0 WHERE (((IF(t0.c1, -1448582331, t0.c1)) LIKE ((t0.c1) NOT IN (t0.c1))) || (t0.c1)) IS UNKNOWN; -- {}

[22 Feb 2023 16:25] MySQL Verification Team

Hi Mr. Jove,

Thank you for your bug report.

However, it is not a bug. 

If you have  read our Reference Manual, you could have read the following sentence:
"
As of MySQL 8.0.17, the ZEROFILL attribute is deprecated for numeric data types;
"

Hence, since not supported, results should not be trusted. In the next version. exempli gratia 9.0, this should be totally removed and will return a syntax error.

For now, it just should not be used.

Not a bug.