Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
5.5, 10.0, 10.1, 10.2, 10.3, 10.4
-
None
Description
SET sql_mode=ALLOW_INVALID_DATES; |
DROP TABLE IF EXISTS t1; |
CREATE TABLE t1 (a DATE); |
INSERT INTO t1 VALUES ('0000-00-00'),('2001-00-01'),('2001-02-30'); |
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE'; |
SELECT *, COALESCE(a) FROM t1; |
Versions 10.0, 10.1, 10.2, 10.3, 10.4 return these results:
+------------+-------------+
|
| a | COALESCE(a) |
|
+------------+-------------+
|
| 0000-00-00 | NULL |
|
| 2001-00-01 | NULL |
|
| 2001-02-30 | 2001-02-30 |
|
+------------+-------------+
|
COALESCE() respects NO_ZERO_DATE and NO_ZERO_IN_DATE, but ignores unset ALLOW_INVALID_DATE.
(Note, table fields ignore sql_mode. But this is by design, it's hard to fix, and it's out of scope of this MDEV)
5.5 returns these results
+------------+-------------+
|
| a | COALESCE(a) |
|
+------------+-------------+
|
| 0000-00-00 | 0000-00-00 |
|
| 2001-00-01 | 2001-00-01 |
|
| 2001-02-30 | 2001-02-30 |
|
+------------+-------------+
|
So in 5.5 COALESCE() ignores sql_mode completely.
Attachments
Issue Links
- relates to
-
-
- Open
-