tests: Invalid DATETIME test fails with mysql >= 8.0.24

[paride]

The test suite is failing when running against MySQL >= 8.0.24 with:

Failures:

  1) Mysql2::Result row data type mapping should raise an error given an invalid DATETIME
     Failure/Error:
       expect { @client.query("SELECT CAST('1972-00-27 00:00:00' AS DATETIME) as bad_datetime").each }.to \
         raise_error(Mysql2::Error, "Invalid date in field 'bad_datetime': 1972-00-27 00:00:00")

       expected Mysql2::Error with "Invalid date in field 'bad_datetime': 1972-00-27 00:00:00" but nothing was raised
     # ./spec/mysql2/result_spec.rb:321:in `block (3 levels) in <top (required)>'

  2) Mysql2::Statement row data type mapping should raise an error given an invalid DATETIME
     Failure/Error:
       expect { @client.query("SELECT CAST('1972-00-27 00:00:00' AS DATETIME) as bad_datetime").each }.to \
         raise_error(Mysql2::Error, "Invalid date in field 'bad_datetime': 1972-00-27 00:00:00")

       expected Mysql2::Error with "Invalid date in field 'bad_datetime': 1972-00-27 00:00:00" but nothing was raised
     # ./spec/mysql2/statement_spec.rb:478:in `block (3 levels) in <top (required)>'

This is because according to the release notes (https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-24.html):

Using CAST() on a DATE or DATETIME value in an INSERT statement raised a warning for "0000-00-00" and "2000-02-31", but not for "2000-01-00" or "2000-00-01". Now a warning is shown in each of these cases also.

The statement_spec.rb and result_spec.rb files need to be updated with a bad date that still generates an error, e.g. 2000-01-00.

[casperisfine]

This is tricky, because it seems that casting to datetime pretty much never error out anymore, but just shows a warning:

mysql> SELECT CAST('WAT' AS DATETIME); SHOW WARNINGS;
+-------------------------+
| CAST('WAT' AS DATETIME) |
+-------------------------+
| NULL                    |
+-------------------------+
1 row in set, 1 warning (0.00 sec)

+---------+------+---------------------------------+
| Level   | Code | Message                         |
+---------+------+---------------------------------+
| Warning | 1292 | Incorrect datetime value: 'WAT' |
+---------+------+---------------------------------+
1 row in set (0.00 sec)

It's really weird because MySQL became stricter lately...

[casperisfine]

Actually it was already a warning on 5.7, so I don't think this change is the root cause.

Looking at the source, it's the mysql2 gem that raise these errors explictly, that part of the code must be broken.

[casperisfine]

Ok, so on 5.7, the client library do return the string that failed to parse, as in row[i] = "1972-00-27 00:00:00".

On 8.0 however row[i] = NULL.

I'll dig a bit more but I don't think we can actually preserve that old behavior.