Bug #91369 Enable STRICT mode also for SELECT and SET statements
Submitted: 22 Jun 2018 10:32 Modified: 24 Jun 2018 8:18
Reporter: Roy Lyseng Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S4 (Feature request)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any

[22 Jun 2018 10:32] Roy Lyseng
Description:
Currently, the STRICT mode that disallows invalid conversions between data types is only effective for data change statements. SELECT statements and SET statements are not affected.

This means that e.g the following query only reports a warning:

  SELECT * FROM fruit WHERE weight = '123banana';

The string '0banana' is interpreted as an integer (123), and the remaining part of the string is ignored.
If this WHERE clause was used in an UPDATE statement, an exception would correctly be raised.

It would be a good idea if STRICT mode applied to all statements.

To ease transitioning for existing applications, one could add a session variable strict_for_select that controls whether strict mode was actually applicable for SELECT and SET statements. When first implemented, this variable would be set to FALSE per default. In the next release, it could be set to TRUE per default, and also deprecated. In the next release after that, remove the variable so that STRICT mode applies to all statements, always.

How to repeat:
N/A

Suggested fix:
See Description.
[24 Jun 2018 8:18] MySQL Verification Team
Hello Roy,

Thank you for the feature request!

Regards,
Umesh
[1 Aug 2023 13:08] MySQL Verification Team
Hi,

This verified feature request is the original one for the following feature request:

https://bugs.mysql.com/bug.php?id=111927

Hence, when this FR is implemented, the above report should be updated as well.

That report also contains some references on the issue of the strict expression evaluation.