MySQL Bugs: #107230: [HY000][1525] Incorrect DATETIME value: '' on latest MySQL 8.0.29

Bug #107230	[HY000][1525] Incorrect DATETIME value: '' on latest MySQL 8.0.29
Submitted:	6 May 2022 14:10	Modified:	10 May 2022 12:08
Reporter:	Adam Mospan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	8.0.29	OS:	Ubuntu (20.04.2)
Assigned to:		CPU Architecture:	x86
Tags:	datetime

Description:
After updating from 8.0.28 to 8.0.29, starts receiveng errors on queries with datetimes.

How to repeat:
CREATE TABLE test (user_id int unsigned);

prepare stmt1 FROM 'select *
    from (
             (select NOW() as `c` from `test` where user_id = ?)
             union
             (select NOW() as `c` from `test` where user_id = 0)
         ) as `a` where `c` <= ?';
SET @a = 1;
SET @b = '2022-05-06 16:49:45';

EXECUTE stmt1 USING @a, @b; // ERROR 1525 (HY000): Incorrect DATETIME value: ''

Hi Mr. Mospan,

Thank you for your bug report.

Can you just let us know whether it happens only with prepared statements or does it occur with normal statements as well ......

Thanks in advance.

Hi ,

This actually seems not to be a bug.

Here is the excerpt from the release notes:

--------------

Important Change: Previously, MySQL allowed arbitrary delimiters and an arbitrary number of them in TIME, DATE, DATETIME, and TIMESTAMP literals, as well as an arbitrary number of whitespaces before, after, and between the date and time values in DATETIME and TIMESTAMP literals. This behavior is now deprecated, and you should expect it to be removed in a future version of MySQL. With this release, the use of any nonstandard or excess delimiter or whitespace characters now triggers a warning of the form Delimiter 'char' in position pos in datetime value 'value' at row rownum is superfluous and is deprecated, followed by Please remove, or in cases in which a suitable replacement can be suggested, Prefer the standard 'replacementchar'.

----------------

Hence, you probably have few spaces extra.

It's working fine for query without preparing:
select * from ((select NOW() as `c` from `qrs` where user_id = 0) union (select NOW() as `c` from `qrs`)) as `a` where `c` <= '2022-05-06 21:04:26'

Yes, I saw release notes about delimeters, but I am using prefered '-' for date and ':' for time, and there is no extra spaces as you can see.

I have tried this on 3 different machines and results the same everywhere, it works on 8.0.28, and brokes on 8.0.29.

I have spend a day for investigating this bug, also this query was generated by Laravel framework so extra spaces can't just appear from no where.

Hi Mr. Mospan,

Thank you for reporting the bug. It is indeed a bug in 8.0.29. We have introduced condition pushdown to derived tables with unions newly in 8.0.29. 
While we work on the solution to this problem, a possible workaround would be to turn off the optimizer_switch, derived_condition_pushdown by doing the following:

set optimizer_switch="derived_condition_pushdown=off";

Another possible workaround:

The problem happens because there is a repreparation of the statement because the actual type of the argument is a signed integer, whereas the type of the parameter is unsigned integer, since it takes its type from the column user_id.

If the actual argument is also supplied as an unsigned integer, the repreparation will not occur and execution should carry out successfully.

With the supplied test case, replace the variable assignment as follows:

  SET @a = CAST(1 AS UNSIGNED);

Since this change has been introduced in 8.0.29, this should be treated as a regression bug.

Documented fix as follows in the MySQL 8.0.30 changelog:

    When pushing a condition down to derived table for prepared
    statements, we clone a condition which also includes parameters
    when a derived table contains unions. When a statement needed to
    be reprepared during execution--for example, when the signedness
    of the value specified does not match that of the actual
    datatype--the parameter was not cloned correctly resulting in
    errors. This occurred because the value specified for the
    parameter was used to print the string for re-parsing, instead
    of a literal '?' placeholder character.

    Now in such cases we set a flag QT_NO_DATA_EXPANSION for
    printing parameters for reparsing which print '?' rather than
    the actual value.

Closed.

Thanks you, very much, Jon ......

Bug #107495 marked as duplicate of this one

Thank you very much.

That means good news for a number of users, since the fix is coming out in the next release.