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:
None 
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
Description:
I have a simple table:

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` date NOT NULL,
  PRIMARY KEY (`id`)
);

I want to make a query with a union and store the result in a temporary table:

CREATE TEMPORARY TABLE IF NOT EXISTS result AS    
SELECT * FROM test
UNION
SELECT * FROM test

I add data like so:

INSERT INTO test (`date`) VALUES ('2017-09-01');

The response from MySQL is

Error Code: 1067. Invalid default value for 'date'

* Adding a default doesn't help (date date NOT NULL DEFAULT '2017-09-06').
* Without the UNION and the second SELECT it works.
* Using UNION ALL instead also works (but that's not what I need).
* Replacing the DATE column with e.g. VARCHAR also works.
* It is independent of the engine (tried MyISAM and InnoDb).

It is working with version 5.7.12, nut not in 5.7.17 or 5.7.19.

How to repeat:
CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` date NOT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO test (`date`) VALUES (date '2017-03-02');
INSERT INTO test (`id`, `date`) VALUES (5, date '2017-04-03');

CREATE TEMPORARY TABLE IF NOT EXISTS result AS    
SELECT * FROM test
UNION
SELECT * FROM test;

SELECT * FROM result;

DROP TABLE test;
[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.