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

[13 Feb 2005 0:24] Trudy Pelzer
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.
[13 Feb 2005 2:16] MySQL Verification Team
Thank you for the bug report.
[14 Mar 2005 12:38] Bugs System
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
[14 Mar 2005 15:15] Sergei Golubchik
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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.039 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.