Bug #87711 | CREATE TABLE AS ... UNION fails with DATE column | ||
---|---|---|---|
Submitted: | 8 Sep 2017 15:08 | Modified: | 19 Mar 2018 17:40 |
Reporter: | Tim Mundt | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
Version: | 5.7.17 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[8 Sep 2017 15:08]
Tim Mundt
[8 Sep 2017 16:05]
Tim Mundt
The thing with the versions seems to be a little more intricate. I tried "docker run --rm -e MYSQL_ROOT_PASSWORD=pw -p 3306:3306 mysql:5.7.12" and then ran the queries... no success. Here it works with that version 5.7.12: rextester.com/TEM41182 With "docker run --rm -e MYSQL_ROOT_PASSWORD=pw -p 3306:3306 mysql:5.6.37" it also works. So it seems to me that in general 5.6.* work. 5.7.* don't work, but on some systems 5.7.12 works.
[3 Nov 2017 15:18]
MySQL Verification Team
Hi! Thank you for your bug report. I have managed to repeat your test case. Indeed, UNION ALL works without problems, while UNION gives the result: ERROR 1067 (42000) at line 10: Invalid default value for 'sdate' Verified as reported.
[3 Nov 2017 15:18]
MySQL Verification Team
Hi! Thank you for your bug report. I have managed to repeat your test case. Indeed, UNION ALL works without problems, while UNION gives the result: ERROR 1067 (42000) at line 10: Invalid default value for 'sdate' Verified as reported.
[25 Jan 2018 9:46]
Guilhem Bichot
Hi Time. Two workarounds for now: - disable "strict mode", do CREATE TABLE, re-enable strict mode: set sql_mode=''; CREATE etc; set sql_mode=default; - or specify the exact type of the to-be-created column: CREATE TEMPORARY TABLE IF NOT EXISTS result (`date` date not null) AS SELECT * FROM test UNION SELECT * FROM test;
[19 Mar 2018 17:40]
Jon Stephens
Documented fix as follows in the MySQL 5.7.22 and 8.0.5 changelogs: SELECT with UNION failed in strict mode for a DATE column declared as NOT NULL. Closed.