| Bug #103781 | PREPARE DATE_ADD/DATE_SUB incorrectly returns DATETIME | ||
|---|---|---|---|
| Submitted: | 22 May 2021 20:49 | Modified: | 29 Oct 2021 16:32 |
| Reporter: | Max Nyman | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 8.0.23/8.0.25 | OS: | Ubuntu |
| Assigned to: | CPU Architecture: | Any | |
[23 May 2021 1:54]
MySQL Verification Team
Thank you for the bug report.
[3 Jun 2021 21:05]
Roy Lyseng
The behavior has been changed because we now do type resolving based on the prepared statement only. For DATE_ADD, if the first argument is a dynamic parameter, its type is assumed to be DATETIME, since it is the most comprehensive temporal type, and since a DATE value can easily be converted to a DATETIME value. However, you can tell MySQL that the first argument is a DATE value, and hence the returned type is a DATE value, with the syntax: PREPARE stmt1 FROM "SELECT DATE_ADD(CAST(? AS DATE), INTERVAL 1 DAY)";
[29 Oct 2021 16:32]
Jon Stephens
Documented fix as follows in the MySQL 8.0.28 changelog:
When using prepared statements, the DATE_ADD() and DATE_SUB()
functions returned DATETIME values, even when calculations
involved combinations of YEAR, MONTH, or DAY parts only (that
is, no time parts).
Previous to implementing single preparation of prepared
statements in MySQL 8.0.22, TIME values were returned in such
cases; before this was done, values used as arguments and their
types were used to determine the result type of certain temporal
functions at resolution time, such as DATE_ADD(), DATE_SUB(),
and ADDTIME(). Afterwards, user variable references and dynamic
parameters are considered constant for the lifetime of one
execution only, requiring that the return type be determined in
another fashion, in this case from the function type. For
example, the default resolved type for DATE_ADD() was deemed to
be DATETIME if the first argument was a dynamic parameter, since
a DATETIME accomodates all temporal values and implicit
reprepare can be avoided.
The change just described represents a regression; the problem
is better solved by deriving a more precise resolved data type,
and performing a reprepare only if that does not match the
actual value of the parameter. (Such functionality was already
in use in the MySQL server for numeric parameters.) The better
solution is implemented by this fix.
We now parse string and numeric values when temporal values are
expected. When a valid temporal value is found, the value is
converted. This fix also improves determination of resolved data
types for temporal functions.
The DATE_ADD() and DATE_SUB() functions (and their synonyms
functions ADDDATE() and SUBDATE()) are changed by this fix as
follows:
If the first argument is a dynamic parameter, its resolved
type is DATE if the second argument is an interval that
contains some combination of YEAR, MONTH, or DAY values
only; otherwise, its type is DATETIME.
If the first argument is resolved as DATETIME, the resolved
type of the function is also DATETIME.
If the first argument is DATE, the resolved type of the
function is also DATE, unless the interval argument uses
HOUR, MINUTE, or SECOND, in which case it is DATETIME.
If the first argument is TIME, the resolved type is also
TIME, unless the interval argument uses YEAR, MONTH, or DAY,
in which case the resolved type of the function is DATETIME.
If none of the preceding conditions are met, the function is
resolved as VARCHAR (as in MySQL 8.0.21 and earlier).
The ADDTIME() and SUBTIME() functions are changed as follows:
If the first argument is a dynamic parameter, the resolved
type is TIME, rather than DATETIME.
Otherwise, the resolved type of the function is derived from
the resolved type of the first argument.
In addition, for DATE_ADD() and DATE_SUB(), if the resolved type
of the first argument is DATE, and a DATETIME value is provided,
the statement is reprepared so that the function has the
resolved type DATETIME. Behavior is unchanged when a TIME value
is provided.
For ADDTIME() and SUBTIME(), there are no forced reprepares.
Also noted the behaviour change(s) in the descriptions of the affected functions in the 8.0 Manual.
Closed.
Closed.
Description: When using prepared statements, the DATE_ADD and DATE_SUB functions return value is in DateTime format, even if calculations involve only YEAR, MONTH, and DAY parts (that is, no time parts). This behavior is changed from at least MySQL version 8.0.21, in which the statement only returned the date part. PREPARE stmt1 FROM "SELECT DATE_ADD(?, INTERVAL 1 DAY) AS a"; SET @pc = '2021-05-22'; EXECUTE stmt1 USING @pc; Returns # a -> '2021-05-23 00:00:00.000000' As a note, the below statement yields another result: PREPARE stmt2 FROM "SELECT DATE_ADD('2021-05-22', INTERVAL 1 DAY) AS a"; EXECUTE stmt2; Returns # a -> '2021-05-23' How to repeat: PREPARE stmt1 FROM "SELECT DATE_ADD(?, INTERVAL 1 DAY)"; SET @pc = '2021-05-22'; EXECUTE stmt1 USING @pc; Suggested fix: Returning only the date part when calculations do not involve any time parts.