Description:
When executing a prepared select statement which uses the 'union' operator in a subquery, if the table contains a nullable timestamp and an outer 'where' condition on a timestamp or datetime field, the following error is returned:
Incorrect TIMESTAMP value: ''
On version 8.0.28 the same query with the same bindings worked fine and would successfully return the expected result, but upgrading to 8.0.29 breaks it. This means upgrading to the latest version can suddenly cause unexpected errors in production environments.
How to repeat:
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.29 |
+-----------+
1 row in set (0.00 sec)
mysql> create database example;
Query OK, 1 row affected (0.00 sec)
mysql> use example;
Database changed
mysql> CREATE TABLE test_data( id BIGINT AUTO_INCREMENT, name VARCHAR(256), created_at timestamp NULL DEFAULT NULL, CONSTRAINT PRIMARY KEY (id) );
Query OK, 0 rows affected (0.02 sec)
mysql> PREPARE example FROM '
'> select `a`.*
'> from (
'> (select * from `test_data` where `id` = ?)
'> union
'> (select * from `test_data` where `name` = ?)
'> ) as `a`
'> where (`created_at` >= ?)
'> ';
Query OK, 0 rows affected (0.00 sec)
Statement prepared
mysql> SET @a = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> SET @b = 2;
Query OK, 0 rows affected (0.00 sec)
mysql> SET @c = '2022-05-05 12:00:00';
Query OK, 0 rows affected (0.00 sec)
mysql> EXECUTE example USING @a, @b, @c;
ERROR 1525 (HY000): Incorrect TIMESTAMP value: ''
-------------------------------------------------------
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.28 |
+-----------+
1 row in set (0.00 sec)
mysql> create database example;
Query OK, 1 row affected (0.00 sec)
mysql> use example;
Database changed
mysql> CREATE TABLE test_data( id BIGINT AUTO_INCREMENT, name VARCHAR(256), created_at timestamp NULL DEFAULT NULL, CONSTRAINT PRIMARY KEY (id) );
Query OK, 0 rows affected (0.02 sec)
mysql> PREPARE example FROM '
'> select `a`.*
'> from (
'> (select * from `test_data` where `id` = ?)
'> union
'> (select * from `test_data` where `name` = ?)
'> ) as `a`
'> where (`created_at` >= ?)
'> ';
Query OK, 0 rows affected (0.01 sec)
Statement prepared
mysql> SET @a = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> SET @b = 2;
Query OK, 0 rows affected (0.00 sec)
mysql> SET @c = '2022-05-05 12:00:00';
Query OK, 0 rows affected (0.00 sec)
mysql> EXECUTE example USING @a, @b, @c;
Empty set (0.00 sec)
-------------------------------------------------------
Note: I have, of course, massively simplified the example to the point where it's a completely pointless query! But in much more complicated queries the problem persists.
Suggested fix:
It appears it begins working again if the outer 'where' condition is applied to the unioned subqueries individually. However, this can take quite a lot of work depending on the complexity of the query.
Description: When executing a prepared select statement which uses the 'union' operator in a subquery, if the table contains a nullable timestamp and an outer 'where' condition on a timestamp or datetime field, the following error is returned: Incorrect TIMESTAMP value: '' On version 8.0.28 the same query with the same bindings worked fine and would successfully return the expected result, but upgrading to 8.0.29 breaks it. This means upgrading to the latest version can suddenly cause unexpected errors in production environments. How to repeat: mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.29 | +-----------+ 1 row in set (0.00 sec) mysql> create database example; Query OK, 1 row affected (0.00 sec) mysql> use example; Database changed mysql> CREATE TABLE test_data( id BIGINT AUTO_INCREMENT, name VARCHAR(256), created_at timestamp NULL DEFAULT NULL, CONSTRAINT PRIMARY KEY (id) ); Query OK, 0 rows affected (0.02 sec) mysql> PREPARE example FROM ' '> select `a`.* '> from ( '> (select * from `test_data` where `id` = ?) '> union '> (select * from `test_data` where `name` = ?) '> ) as `a` '> where (`created_at` >= ?) '> '; Query OK, 0 rows affected (0.00 sec) Statement prepared mysql> SET @a = 1; Query OK, 0 rows affected (0.00 sec) mysql> SET @b = 2; Query OK, 0 rows affected (0.00 sec) mysql> SET @c = '2022-05-05 12:00:00'; Query OK, 0 rows affected (0.00 sec) mysql> EXECUTE example USING @a, @b, @c; ERROR 1525 (HY000): Incorrect TIMESTAMP value: '' ------------------------------------------------------- mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.28 | +-----------+ 1 row in set (0.00 sec) mysql> create database example; Query OK, 1 row affected (0.00 sec) mysql> use example; Database changed mysql> CREATE TABLE test_data( id BIGINT AUTO_INCREMENT, name VARCHAR(256), created_at timestamp NULL DEFAULT NULL, CONSTRAINT PRIMARY KEY (id) ); Query OK, 0 rows affected (0.02 sec) mysql> PREPARE example FROM ' '> select `a`.* '> from ( '> (select * from `test_data` where `id` = ?) '> union '> (select * from `test_data` where `name` = ?) '> ) as `a` '> where (`created_at` >= ?) '> '; Query OK, 0 rows affected (0.01 sec) Statement prepared mysql> SET @a = 1; Query OK, 0 rows affected (0.00 sec) mysql> SET @b = 2; Query OK, 0 rows affected (0.00 sec) mysql> SET @c = '2022-05-05 12:00:00'; Query OK, 0 rows affected (0.00 sec) mysql> EXECUTE example USING @a, @b, @c; Empty set (0.00 sec) ------------------------------------------------------- Note: I have, of course, massively simplified the example to the point where it's a completely pointless query! But in much more complicated queries the problem persists. Suggested fix: It appears it begins working again if the outer 'where' condition is applied to the unioned subqueries individually. However, this can take quite a lot of work depending on the complexity of the query.