Description:
The SQL Mode TRADITIONAL will set SQL modes so that warnings would be raised as error. However, if you do not use any tables, and you use a function in a statement which produces a result which lager than max_allowed_packet (for example), then NULL is returned, and a warning.
Maybe I missed a spot in in the docs explaining this restriction, but maybe it's good, when this is the case, to set in each warning when it will be raised as error, or something.
How to repeat:
# With default SQL Modes, or TRADITIONAL ON
mysql> SET @@session.sql_mode = 'TRADITIONAL';
mysql> CREATE TABLE t1 (c1 TEXT);
mysql> INSERT INTO foo (bar) VALUES (repeat('a', @@global.max_allowed_packet+1));
ERROR 1301 (HY000): Result of repeat() was larger than max_allowed_packet (4194304) - truncated
But:
mysql> SET @@session.sql_mode = 'TRADITIONAL';
mysql> SELECT REPEAT('a', @@global.max_allowed_packet+1);
+--------------------------------------------+
| REPEAT('a', @@global.max_allowed_packet+1) |
+--------------------------------------------+
| NULL |
+--------------------------------------------+
1 row in set, 1 warning
mysql> SET @@session.sql_mode = '';
mysql> INSERT INTO foo (bar) VALUES (REPEAT('a', @@global.max_allowed_packet+1));
Query OK, 1 row affected, 1 warning (0.00 sec)
Suggested fix:
Be consistent: make sure the warnings are thrown also for statements where no table is used at all. Use case? Can not think of one, except testing in connectors (for which we have workarounds, no issue).
Just be consistent; and maybe a documentation update? Or maybe point me where it is said that this doesn't work :)
Description: The SQL Mode TRADITIONAL will set SQL modes so that warnings would be raised as error. However, if you do not use any tables, and you use a function in a statement which produces a result which lager than max_allowed_packet (for example), then NULL is returned, and a warning. Maybe I missed a spot in in the docs explaining this restriction, but maybe it's good, when this is the case, to set in each warning when it will be raised as error, or something. How to repeat: # With default SQL Modes, or TRADITIONAL ON mysql> SET @@session.sql_mode = 'TRADITIONAL'; mysql> CREATE TABLE t1 (c1 TEXT); mysql> INSERT INTO foo (bar) VALUES (repeat('a', @@global.max_allowed_packet+1)); ERROR 1301 (HY000): Result of repeat() was larger than max_allowed_packet (4194304) - truncated But: mysql> SET @@session.sql_mode = 'TRADITIONAL'; mysql> SELECT REPEAT('a', @@global.max_allowed_packet+1); +--------------------------------------------+ | REPEAT('a', @@global.max_allowed_packet+1) | +--------------------------------------------+ | NULL | +--------------------------------------------+ 1 row in set, 1 warning mysql> SET @@session.sql_mode = ''; mysql> INSERT INTO foo (bar) VALUES (REPEAT('a', @@global.max_allowed_packet+1)); Query OK, 1 row affected, 1 warning (0.00 sec) Suggested fix: Be consistent: make sure the warnings are thrown also for statements where no table is used at all. Use case? Can not think of one, except testing in connectors (for which we have workarounds, no issue). Just be consistent; and maybe a documentation update? Or maybe point me where it is said that this doesn't work :)