Bug #110741 NO_ZERO_DATE sql_mode ignored in prepared statements

• Submitted: 19 Apr 2023 23:00
• Modified: 20 Apr 2023 12:11
• Reporter: Colby Clark
• Status: Not a Bug
• Category: MySQL Server: Prepared statements
• Severity: S3 (Non-critical)
• Version: 8.0.26-17
• OS: Any
• CPU Architecture: Any
• Tags: NO_ZERO_DATE, prepared statement

[19 Apr 2023 23:00] Colby Clark

Description:
When sql_mode includes NO_ZERO_DATE, prepared statements execute even when zero-dates are bound to the prepared statement.  Non-prepared-statement versions of the same queries return the error "ERROR 1525 (HY000): Incorrect DATETIME value: '0000-00-00 00:00:00'".

Further, the prepared statement versions silently ignore the portion of the where clause containing a zero date, resulting in unexpected/inaccurate query results.

How to repeat:
set @@sql_mode = 'NO_ENGINE_SUBSTITUTION';-- ok

DROP TABLE IF EXISTS `foo`;-- ok

CREATE TABLE IF NOT EXISTS `foo` (
  `start` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ok

insert into `foo` values ('0000-00-00 00:00:00');-- ok
insert into `foo` values ('2023-04-19 16:50:00');-- ok

select count(*) from foo where start = '0000-00-00 00:00:00' or start = '2023-04-19 16:50:00';-- ok, count(*) => 2

set @@sql_mode = 'NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE';-- ok

prepare stmt1 from 'select count(*) from foo where start = ? or start = ?';-- ok

set @a = '0000-00-00 00:00:00';-- ok
set @b = '2023-04-19 16:50:00';-- ok

execute stmt1 using @a, @b;-- ok, count(*) => 1

select count(*) from foo where start = '0000-00-00 00:00:00' or start = '2023-04-19 16:50:00';-- Error in query (1525): Incorrect DATETIME value: '0000-00-00 00:00:00'

Suggested fix:
When NO_ZERO_DATE is enabled, the behavior of prepared statements and their non-prepared counterparts should be identical - if one returns an error due to an "Incorrect DATETIME value" being provided, the other should as well.

[20 Apr 2023 12:11] MySQL Verification Team

Hi Mr. Clark,

Thank you for your bug report.

However, it is not a bug.

If you read our Reference Manual, you will find that NO_ZERO_DATE is already deprecated. Since it is deprecated, no bug reports that involve this sql_mode will be processed.

There are other explanations in our Reference Manual, on when will this option produce the error. But, even if all conditions are not met, since this is a deprecated mode, it will not be maintained .....

Not a bug.