Description:
I run my servers with STRICT_TRANS_TABLES. The idea of this is to prevent invalid data getting into the database. In the past with previous defaults data would be munged or truncated and no error would be generated, just a warning. That might get missed. It wasn't ideal. Anyway now with STRICT_TRANS_TABLES and similar modes we can avoid data being inserted into the database incorrectly.
However, even if STRICT_TRANS_TABLES is enabled if I do an incorrect query (from the point of view that the developer is comparing types which are not comparable then only a warning is thrown and no error.
Here is an example:
select * from some_table where double_col = '2018-04-25 14:22:00';
Empty set, 1 warning (0.00 sec)
Warning (Code 1292): Truncated incorrect DOUBLE value: '2018-04-25 14:22:00'```
clearly the app is doing something stupid but I’d expect this to give an error (or like to have a way to optionally trigger that)
yet it doesn’t happen, it just gives a warning and the end result is likely to be “unpredictable”…
Often MySQL tries to do the right thing and mangle the data into the right format, but if it’s not able to wouldn’t an error be better than the warning above?
It seems that the STRICT_TRANS_TABLES is only for DML statements and SELECTs don't go through the same code path.
Another example:
SQL: Warning 1292 Truncated incorrect DOUBLE value: 'my@email.address' at SELECT * FROM some_table WHERE `double_col` = ?
Clearly knowing what to do is not that simple. There's a lot of logic in the code and there may be many edge cases but I would find it very useful when for example data is truncated, or out or range, or invalid for the SELECT query to fail rather than just generate a warning and give back unreliable results.
How to repeat:
see above.
Suggested fix:
Given the magic with sql_modes if this ever happens it may make more sense to make this a glboal variable. These SELECT statements won't be replicated so it doesn't need to pass down through replication.
Having said that the behaviour of doing an INSERT INTO ... SELECT ... WHERE might have the same problem.
Maybe this is something I need to talk to the Oracle developers about. My main concern is that mysql let's me do stupid queries and in cases where it might be better to generate an error it only generates a warning. That should get picked up but is less visible and can sometimes slip through unnoticed for a while.
