MySQL CREATE TABLE FROM UNION gives "invalid default value"

Question

I have a simple table in an InnoDB MySQL database:

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

I want to make a join query and save the result in a temporary table. I simplified it as follows:

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

I add the following data:

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

Answer from MySQL

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

• Adding a default does not help ( date date NOT NULL DEFAULT '2017-09-06').
• Without a UNIONsecond SELECT, it works.
• Using UNION ALLinstead also works (but that is not what I need).
• replacing the DATE column for example. VARCHAR also works.

From what I read, 1067 is related to installation issues, which in this context are strange.

What should I do to make this work?

Answer 1

, MySQL. 5.6.25 5.7.12, 5.7.17 5.7.19. , , . ( : https://bugs.mysql.com/bug.php?id=87711) Thorsten .

EDIT:

5.7. (, ;)). 5.6. MariaDB, !