Bug #118887 Zero '0000-00-00 00:00:00' datetime returned when SQL_BUFFER_RESULT is used
Description:
When a row contains the illegal DATETIME value '9999-00-00 00:00:00.0', a plain SELECT * returns it literally, but adding SQL_BUFFER_RESULT causes the same value to be silently converted to '0000-00-00 00:00:00.0'.
This inconsistency violates the expectation that both queries should return identical rows.
How to repeat:
mysql> CREATE TABLE t1 (
-> col_datetime_1_not_null datetime NOT NULL,
-> col_datetime_2_not_null datetime NOT NULL
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
mysql> SET sql_mode=NO_ENGINE_SUBSTITUTION;
Query OK, 0 rows affected (0.000 sec)
mysql> INSERT INTO t1 VALUES ('0000-00-00 00:00:00.0','2005-12-22 19:53:31.01');
:00.0','2005-12-22 19:53:31.01');
Query OK, 1 row affected (0.003 sec)
mysql> INSERT INTO t1 VALUES ('9999-00-00 00:00:00.0','2005-12-22 19:53:31.01');
Query OK, 1 row affected (0.003 sec)
mysql> SET sql_mode=default;
Query OK, 0 rows affected (0.000 sec)
mysql> SELECT * FROM t1;
+-------------------------+-------------------------+
| col_datetime_1_not_null | col_datetime_2_not_null |
+-------------------------+-------------------------+
| 0000-00-00 00:00:00 | 2005-12-22 19:53:31 |
| 9999-00-00 00:00:00 | 2005-12-22 19:53:31 |
+-------------------------+-------------------------+
2 rows in set (0.000 sec)
mysql> select sql_buffer_result * from t1;
+-------------------------+-------------------------+
| col_datetime_1_not_null | col_datetime_2_not_null |
+-------------------------+-------------------------+
| 0000-00-00 00:00:00 | 2005-12-22 19:53:31 |
| 0000-00-00 00:00:00 | 2005-12-22 19:53:31 |
+-------------------------+-------------------------+
2 rows in set, 2 warnings (0.000 sec)
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.037 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
Description: When a row contains the illegal DATETIME value '9999-00-00 00:00:00.0', a plain SELECT * returns it literally, but adding SQL_BUFFER_RESULT causes the same value to be silently converted to '0000-00-00 00:00:00.0'. This inconsistency violates the expectation that both queries should return identical rows. How to repeat: mysql> CREATE TABLE t1 ( -> col_datetime_1_not_null datetime NOT NULL, -> col_datetime_2_not_null datetime NOT NULL -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; mysql> SET sql_mode=NO_ENGINE_SUBSTITUTION; Query OK, 0 rows affected (0.000 sec) mysql> INSERT INTO t1 VALUES ('0000-00-00 00:00:00.0','2005-12-22 19:53:31.01'); :00.0','2005-12-22 19:53:31.01'); Query OK, 1 row affected (0.003 sec) mysql> INSERT INTO t1 VALUES ('9999-00-00 00:00:00.0','2005-12-22 19:53:31.01'); Query OK, 1 row affected (0.003 sec) mysql> SET sql_mode=default; Query OK, 0 rows affected (0.000 sec) mysql> SELECT * FROM t1; +-------------------------+-------------------------+ | col_datetime_1_not_null | col_datetime_2_not_null | +-------------------------+-------------------------+ | 0000-00-00 00:00:00 | 2005-12-22 19:53:31 | | 9999-00-00 00:00:00 | 2005-12-22 19:53:31 | +-------------------------+-------------------------+ 2 rows in set (0.000 sec) mysql> select sql_buffer_result * from t1; +-------------------------+-------------------------+ | col_datetime_1_not_null | col_datetime_2_not_null | +-------------------------+-------------------------+ | 0000-00-00 00:00:00 | 2005-12-22 19:53:31 | | 0000-00-00 00:00:00 | 2005-12-22 19:53:31 | +-------------------------+-------------------------+ 2 rows in set, 2 warnings (0.000 sec)