Bug #106980 Query parsed as UPDATE calculates a different value from same query as INSERT
Submitted: 11 Apr 2022 12:43 Modified: 19 Apr 2022 15:05
Reporter: Karl Pielorz Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:5.7 OS:Any
Assigned to: CPU Architecture:Any
Tags: Different dates, Incorrect Datetime, insert, UPDATE

[11 Apr 2022 12:43] Karl Pielorz
Description:
A query when run as a 'SELECT' returns the value '2022-04-03 10:11:12' (which is expected).

However, the same query when issued as an UPDATE errors with:

"[Code: 1292, SQL State: 22001]  Data truncation: Incorrect datetime value: '2022-04-31'"

The query is designed to return a date > @now, returning using the 'day' component of an expiry date.

It's designed so the date returned by the IF() block will be:

 - In the future.
 - Either using the 'expiry_date's day - for this month, or the last day of this month if it's not valid.
 - Or the 'expiry_date's day - next month, or the last day of next month if that's not a valid date.

This works fine as a SELECT, always returning a valid date.

However, if you use the same IF() block to return a field that's being INSERTed or UPDATEd the query fails - it's as if it's not appreciating the final result of the IF() block - and just presuming another date - not the final outcome date was going to be used for the INSERT/UPDATE - and failing because that's not a valid date (whereas SELECT does always return a valid date).

I have hardwired '@expiry_date' and '@now' - to show the problem (and avoid issues with NOW() being used in the example).

How to repeat:
Create a test table, so we have something to INSERT into:

CREATE TABLE `test` (
  `mydate` datetime DEFAULT NULL
) ENGINE=InnoDB

First, the 'SELECT' version of the query - which always returns a 'valid' date (i.e. '2022-04-30 10:11:12') - remember we're using '@' variables to avoid using NOW() or data from a table etc. to make this reproducable.

SET @expiry_date = '2022-12-31 10:11:12';
SET @now = '2022-04-11 13:13:10';

SELECT
        IF(
                        TIMESTAMP(
                                IFNULL(
                                        TIMESTAMP( DATE( CONCAT( DATE_FORMAT( @now, '%Y-%m-' ), DAY( @expiry_date ) ) ), TIME( @expiry_date ) ),
                                        TIMESTAMP( LAST_DAY( @now ), TIME( @expiry_date ) )
                                )
                     ) > @now,
                        TIMESTAMP(
                                IFNULL(
                                        TIMESTAMP( DATE( CONCAT( DATE_FORMAT( @now, '%Y-%m-' ), DAY( @expiry_date ) ) ), TIME( @expiry_date ) ),
                                        TIMESTAMP( LAST_DAY( @now ), TIME( @expiry_date ) )
                                )
                        )
                     ,
                        TIMESTAMP(
                                IFNULL(
                                        TIMESTAMP( DATE( CONCAT( DATE_FORMAT( DATE( DATE_ADD( DATE_FORMAT( @now, '%Y-%m-01' ), INTERVAL 1 MONTH ) ), '%Y-%m-' ), DAY( @expiry_date ) ) ), TIME( @expiry_date ) ),
                                        TIMESTAMP( LAST_DAY( DATE( DATE_ADD( DATE_FORMAT( @now, '%Y-%m-01' ), INTERVAL 1 MONTH ) ) ), TIME( @expiry_date ) )
                                )
                        )
                     )
                 
AS my_date;

This returns, my_date = '2022-04-30 10:11:12' which is what you'd expect.

----

Now the same query as an INSERT - which fails:

INSERT INTO test ( mydate ) VALUES (
        IF(
                        TIMESTAMP(
                                IFNULL(
                                        TIMESTAMP( DATE( CONCAT( DATE_FORMAT( @now, '%Y-%m-' ), DAY( @expiry_date ) ) ), TIME( @expiry_date ) ),
                                        TIMESTAMP( LAST_DAY( @now ), TIME( @expiry_date ) )
                                )
                     ) > @now,
                        TIMESTAMP(
                                IFNULL(
                                        TIMESTAMP( DATE( CONCAT( DATE_FORMAT( @now, '%Y-%m-' ), DAY( @expiry_date ) ) ), TIME( @expiry_date ) ),
                                        TIMESTAMP( LAST_DAY( @now ), TIME( @expiry_date ) )
                                )
                        )
                     ,
                        TIMESTAMP(
                                IFNULL(
                                        TIMESTAMP( DATE( CONCAT( DATE_FORMAT( DATE( DATE_ADD( DATE_FORMAT( @now, '%Y-%m-01' ), INTERVAL 1 MONTH ) ), '%Y-%m-' ), DAY( @expiry_date ) ) ), TIME( @expiry_date ) ),
                                        TIMESTAMP( LAST_DAY( DATE( DATE_ADD( DATE_FORMAT( @now, '%Y-%m-01' ), INTERVAL 1 MONTH ) ) ), TIME( @expiry_date ) )
                                )
                        )
                     )
                
)
[Code: 1292, SQL State: 22001]  Data truncation: Incorrect datetime value: '2022-04-31'

Why has this tried to use the value '2022-04-31' - that's not what the same query running as a SELECT returns?

Suggested fix:
None. I can't seem to persuade the INSERT or UPDATE versions to use the value that the SELECT version of the same query happily calculates.
[11 Apr 2022 12:53] Karl Pielorz
Tested against legacy MySQL 5.1 (works as expected), fails with MySQL 5.6 and MySQL 5.7
[11 Apr 2022 13:36] MySQL Verification Team
Hi Mr. Pielorz,

Thank you for your bug report.

We have managed to repeat the behaviour. The expression is the same for both SELECT and INSERT, but results are different.

This bug affects both 5.7 and 8.0. It affects 5.6, but that version is obsolete and not maintained any more

We must inform you that this is a minor bug, which agrees with a severity that you have set.

Verified as reported.
[11 Apr 2022 21:36] Roy Lyseng
Not a bug.
The expression

  TIMESTAMP( DATE( CONCAT( DATE_FORMAT( @now, '%Y-%m-' ), DAY( @expiry_date ) ) ),
             TIME( @expiry_date ) )

produces the date 2022-04-31 which is an invalid date.
The SELECT statement reports this as a warning, while the INSERT statement
operates in STRICT mode and promotes the invalid date message to an error.

Thus, it is possible to turn off strict mode and then have a warning reported, however operating in non-strict mode, may cause serious consistency errors.
[11 Apr 2022 22:30] Karl Pielorz
Hi,

I get that "TIMESTAMP( DATE( CONCAT( DATE_FORMAT( @now, '%Y-%m-' ), DAY( @expiry_date ) ) ),
             TIME( @expiry_date ) )"

Produces an invalid date - but that result is not used to feed the UPDATE, as that test is meant to be 'protected' as part of an IFNULL() statement - where TIMESTAMP( invalid date ) produces NULL, so the second expression is returned etc.

You can see this when the query runs as SELECT - as ultimately the date returned as 'my_date' is valid (2022-04-30 etc. = valid).

This seems to suggest you cannot rely on 'TIMESTAMP()' to return NULL for invalid date/time (i.e. should not be using it to test for an invalid date?) - as if its present in even conditional code as part of an UPDATE query it will return an error?

If that's the case - what should you use in MySQL to test for a valid date? - Without producing the error?

I tried re-working the query using STR_TO_DATE() - it produces the same behaviour, the spec says it'll return either a DATE, TIME or DATETIME - or NULL if the string is illegal, with warning. It does that for SELECT, but like TIMESTAMP the warning is promoted to 'error' if a date tested is illegal (even if the result is not used as the source data for the UPDATE).

I'm just struggling to see what test we can use for 'if this is not a date, don't use it - and use this other value' - without having the warning promoted to an error if the query involves UPDATE.

I could understand this if the query ran - and it -actually tried to push- '2022-04-31' into the field, but the expectation is that the IF()/IFNULL() conditionals in line prevent an invalid date from being produced as a result (and they do).

I appreciate your point on toggling strict mode - we don't want to do that.

Thanks,

-Karl
[12 Apr 2022 12:15] MySQL Verification Team
Hi Jon,

Thanks a lot for the explanation of the STRICT mode used in some DMLs.
[12 Apr 2022 20:23] Roy Lyseng
Posted by developer:
 
It may be better to operate on character strings and just convert the string to a temporal when you know you have a valid date.
The following will give you the date that you need - I think:

  SELECT LEAST(CONCAT(DATE_FORMAT(@now, '%Y-%m-'), DAY(@expiry_date)), CAST(LAST_DAY(@now) AS CHAR));
[19 Apr 2022 15:05] Karl Pielorz
Roy Lyseng, thanks...

I reworked the entire query to avoid passing any invalid dates to TIMESTAMP() - but this still fails, it appears the IF statements in MySQL are not always short-circuited.

So in my new query - the IF() statement looks at the expiry day specifically to see if it's less than the last day of the month - but it appears both the true and false expressions are evaluated (even though the true is the only one 'used') - but the false part still causes "Invalid date" - even though it should not be executed.

Your solution looks like it might work around this.

This is actually pretty annoying - especially in environments where you cannot enable 'allow invalid dates' - I mean I can see why you don't want invalid dates, but calling/erroring out either functions which claim to be able to handle them (by returning NULL for invalid) or even just having protecting IF() statements not work, because it appears both true and false paths are evaluated - is certainly annoying.
[20 Apr 2022 10:23] Roy Lyseng
I think the arguments to IF may be pre-evaluated if they are constant expressions that can be simplified. In other cases, only the selected path should be evaluated.

The SQL standard is quite rigid about "invalid" entries, and it generally only generate NULL values in two cases: when an argument to a function is NULL, and as the outcome of a CASE (or similar) function.

To be standard compliant, you may have to use a function with a DECLARE HANDLER statement to convert errors into SQL data, but this may be quite cumbersome. It might be nice to have some "is_valid" type functions, where one could check whether a string represents a valid date or other database value.