MySQL Bugs: #87711: CREATE TABLE AS ... UNION fails with DATE column

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

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;

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.

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.

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.

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;

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.