Bug #36534 sql_mode traditional restrictions affect data manipulation queries only
Submitted: 6 May 2008 13:54
Reporter: Victoria Reznichenko Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S4 (Feature request)
Version:all OS:Any
Assigned to: Geir H√łydalsvik CPU Architecture:Any
Tags: SQL_MODE

[6 May 2008 13:54] Victoria Reznichenko
Description:
If you set SQL_MODE='TRADITIONAL', this will prevent MySQL to insert invalid data but for SELECT queries it generates only warnings, not errors.

For example:

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

mysql> create table t1(dt date not null);
Query OK, 0 rows affected (0.05 sec)

mysql> insert into t1 values(2008-05-06);
ERROR 1292 (22007): Incorrect date value: '1997' for column 'dt' at row 1
mysql> insert into t1 values('2008-05-06');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1 where dt > 2008-05-06;
+------------+
| dt         |
+------------+
| 2008-05-06 |
+------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------------+
| Level   | Code | Message                                               |
+---------+------+-------------------------------------------------------+
| Warning | 1292 | Incorrect date value: '1997' for column 'dt' at row 1 |
+---------+------+-------------------------------------------------------+
1 row in set (0.00 sec)

It would be good if MySQL will also generate error message for SELECT queries as well.

Also I should note that other RDBMS (Oracle, Sybase) generate error in this case.
These are examples made by our user on Oracle and Sybase:

example #1

1> create table simon_delete_me ( some_date date not null )
2> go
1> insert into simon_delete_me values ( '2008-05-06' )
2> go
(1 row affected)
1> SELECT * from simon_delete_me where some_date > 2008-05-06
2> go
Msg 206, Level 16, State 2:
Server 'AMS03', Line 1:
Operand type clash: INT is incompatible with DATE
1> SELECT * from simon_delete_me where some_date > 20080506
2> go
Msg 206, Level 16, State 2:
Server 'AMS03', Line 1:
Operand type clash: INT is incompatible with DATE

example #2

1> create table simon_delete_me ( some_date date not null )
2> go
1> insert into simon_delete_me values ( '2008-05-06' )
2> go
(1 row affected)
1> SELECT * from simon_delete_me where some_date > 2008-05-06
2> go
Msg 206, Level 16, State 2:
Server 'AMS03', Line 1:
Operand type clash: INT is incompatible with DATE
1> SELECT * from simon_delete_me where some_date > 20080506
2> go
Msg 206, Level 16, State 2:
Server 'AMS03', Line 1:
Operand type clash: INT is incompatible with DATE

How to repeat:
set @@sql_mode=traditional;
create table t1(dt date not null);
insert into t1 values(2008-05-06);
insert into t1 values('2008-05-06');
select * from t1 where dt > 2008-05-06;
show warnings;
[6 May 2008 15:44] MySQL Verification Team
Sorry for double copy-paste. The second example is:

SQL> create table SIMON_DELETE_ME ( SOME_DATE DATE NOT NULL )
2 /

Table created.

SQL> INSERT INTO SIMON_DELETE_ME ( SOME_DATE ) VALUES ( TO_DATE('2008-05-06','yyyy-mm-dd' ) )
2 /

1 row created.

SQL> SELECT * FROM SIMON_DELETE_ME WHERE SOME_DATE > 2008-05-06
2 /
SELECT * FROM SIMON_DELETE_ME WHERE SOME_DATE > 2008-05-06
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected DATE got NUMBER
[26 Aug 2017 20:47] MySQL Verification Team
https://bugs.mysql.com/bug.php?id=87553 marked as duplicate of this one.