MySQL Bugs: #91369: Enable STRICT mode also for SELECT and SET statements

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

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.

Hello Roy,

Thank you for the feature request!

Regards,
Umesh

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.