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;
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;