Bug #112914 different result related to zerofill and greatest()

• Submitted: 1 Nov 2023 9:43
• Modified: 1 Nov 2023 14:26
• Reporter: Alex Wong
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0
• OS: Any
• CPU Architecture: Any

[1 Nov 2023 9:43] Alex Wong

Description:
Hello,

I found a different result in the MySQL program, and I would like to report it as a potential bug. Here's the code I used:

CREATE TABLE t0(c0 SMALLINT zerofill);
INSERT IGNORE INTO t0(c0) VALUES('');
SELECT t0.c0 FROM t0; -- expect: 00000, actual: 00000
SELECT t0.c0 FROM t0 WHERE NOT((GREATEST(t0.c0, '0.8')) OR (t0.c0)); -- expected : 00000 actual: no rows

I also checked the SQL query SELECT NOT (GREATEST(t0.c0, '0.8')) OR t0.c0 FROM t0, and it returned 1. This indicates that the query SELECT t0.c0 FROM t0 WHERE NOT((GREATEST(t0.c0, '0.8')) OR (t0.c0)) should return 1 row. However, it did not return any rows in practice.

I would appreciate your assistance in investigating this issue and resolving the difference. Thank you for your attention and support.

Best regards,
Ruifeng

How to repeat:
CREATE TABLE t0(c0 SMALLINT zerofill);
INSERT IGNORE INTO t0(c0) VALUES('');
SELECT t0.c0 FROM t0; -- expect: 00000, actual: 00000
SELECT t0.c0 FROM t0 WHERE NOT((GREATEST(t0.c0, '0.8')) OR (t0.c0)); -- expected : 00000 actual: no rows
 SELECT NOT (GREATEST(t0.c0, '0.8')) OR t0.c0 FROM t0 -- 1

[1 Nov 2023 14:26] MySQL Verification Team

Hi Mr. Wong,

Thank you for your bug report.

We reproduced it.

This is an insignificant bug, but still a bug.

Verified.