Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.1(EOL), 10.2(EOL), 10.3(EOL)
-
None
Description
I set an empty sql_mode and run a query with CAST(AS DATE):
SET sql_mode=''; |
SELECT CAST(20061108.01 AS DATE); |
+---------------------------+
|
| CAST(20061108.01 AS DATE) |
|
+---------------------------+
|
| 2006-11-08 |
|
+---------------------------+
|
1 row in set (0.00 sec)
|
Notice, the value was returned without any warnings.
Now I set sql_mode to NO_ZERO_IN_DATE and re-run the query:
SET sql_mode=NO_ZERO_IN_DATE; |
SELECT CAST(20061108.01 AS DATE); |
+---------------------------+
|
| CAST(20061108.01 AS DATE) |
|
+---------------------------+
|
| 2006-11-08 |
|
+---------------------------+
|
1 row in set, 1 warning (0.00 sec)
|
Hmm, a warning was generated. Let's check it:
SHOW WARNINGS;
|
+---------+------+---------------------------------------------------+
|
| Level | Code | Message |
|
+---------+------+---------------------------------------------------+
|
| Warning | 1292 | Truncated incorrect datetime value: '20061108.01' |
|
+---------+------+---------------------------------------------------+
|
Now let's remove fractional digits from the CAST argument:
SET sql_mode=NO_ZERO_IN_DATE; |
SELECT CAST(20061108 AS DATE); |
+------------------------+
|
| CAST(20061108 AS DATE) |
|
+------------------------+
|
| 2006-11-08 |
|
+------------------------+
|
1 row in set (0.00 sec)
|
The warning has gone.
So the warning happens:
- only if there are some fractional digits, and
- only if NO_ZERO_IN_DATE is set
This looks wrong. Fractional digit truncation has no any relevance to NO_ZERO_IN_DATE.
CAST(20061108.01 AS DATE) should return the same warnings no matter if NO_ZERO_IN_DATE is set.
Possible ways:
- Return the result silently, without any messages
- Return the result with a NOTE rather than a WARNING
Note, in case of an implicit conversion on INSERT, fractional digit truncation always generates a note, independently from NO_ZERO_IN_DATE flag:
SET sql_mode=''; |
CREATE OR REPLACE TABLE t1 (a DATE); |
INSERT INTO t1 VALUES (20061108.01); |
SHOW WARNINGS;
|
+-------+------+----------------------------------------+
|
| Level | Code | Message |
|
+-------+------+----------------------------------------+
|
| Note | 1265 | Data truncated for column 'a' at row 1 |
|
+-------+------+----------------------------------------+
|
SET sql_mode=NO_ZERO_IN_DATE; |
CREATE OR REPLACE TABLE t1 (a DATE); |
INSERT INTO t1 VALUES (20061108.01); |
SHOW WARNINGS;
|
+-------+------+----------------------------------------+
|
| Level | Code | Message |
|
+-------+------+----------------------------------------+
|
| Note | 1265 | Data truncated for column 'a' at row 1 |
|
+-------+------+----------------------------------------+
|
Attachments
Issue Links
- blocks
-
MDEV-4912 Data type plugin API version 1
-
- Closed
-
-
-
- Closed
-
-
MDEV-16991 Rounding vs truncation for TIME, DATETIME, TIMESTAMP
-
- Closed
-