MySQL Bugs: #8484: Traditional: STRICT_TRANS_TABLES and STRICT_ALL

Bug #8484	Traditional: STRICT_TRANS_TABLES and STRICT_ALL_TABLES don't work
Submitted:	13 Feb 2005 0:24	Modified:	14 Mar 2005 15:15
Reporter:	Trudy Pelzer	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.3-alpha-debug	OS:	Linux (SuSE 9.1)
Assigned to:	Ramil Kalimullin	CPU Architecture:	Any

Description:
The MySQL Reference Manual has this to say about the
STRICT_TRANS_TABLES and STRICT_ALL_TABLES settings
for sql_mode:
"STRICT_ALL_TABLES 
Enable strict mode for all storage engines. Invalid data values 
are rejected. ...
STRICT_TRANS_TABLES 
Enable strict mode for transactional storage engines, and when 
possible for non-transactional storage engines. ..."

In other words, when sql_mode is set to either of these settings,
the server should behave much as it does when sql_mode='tradtional'
and reject all attempts to put invalid values into the database,
provided that (a) a transaction storage engine is in use or
(b) the invalid value is found in the first row of a data change to
a table with a non-transactional storage engine. But this is not
happening.

How to repeat:
mysql> set sql_mode='traditional';
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1 (col1 int) engine=innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values(5/0);
ERROR 1365 (22012): Division by 0
-- This is the correct response under 'traditional' mode.

mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values(5/0);
Query OK, 1 row affected (0.00 sec)
-- This is the correct response under "regular" mode.

mysql> set sql_mode='strict_trans_tables';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values(5/0);
Query OK, 1 row affected (0.00 sec)
-- This is the incorrect response. The statement should fail,
with SQLSTATE 22012 Division by 0, just like under 'traditional'
mode.

mysql> set sql_mode='strict_all_tables';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values(5/0);
Query OK, 1 row affected (0.00 sec)
-- This is the incorrect response. The statement should fail,
with SQLSTATE 22012 Division by 0, just like under 'traditional'
mode.

mysql> select * from t1;
+------+
| col1 |
+------+
| NULL |
| NULL |
| NULL |
+------+
3 rows in set (0.00 sec)
-- The table should have only 1 row.

Thank you for the bug report.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/22995

I don't think it's a bug.

The manual says that in the strict mode "Invalid data values are rejected."

But unless ERROR_FOR_DIVISION_BY_ZERO mode is used 
5/0 is not an invalid value - it's NULL. The manual specifies:

`ERROR_FOR_DIVISION_BY_ZERO'
     Produce an error in strict mode (otherwise a warning) when we
     encounter a division by zero (or `MOD(X,0)') during an `INSERT'/
     `UPDATE'. If this mode is not given, MySQL instead returns `NULL'
     for divisions by zero.

To summarize: STRICT mode defines whether invalid data should be rejected or auto-converted. ERROR_FOR_DIVISION_BY_ZERO specifies whether 5/0 is invalid data or not.