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)
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)