Description:
SQL_MODE has quite a variety of settings, some of which are value restrictions. Surely these are type information that belong in declarations:
ALLOW_INVALID_DATES
NO_ZERO_DATE
NO_ZERO_IN_DATE
NO_AUTO_VALUE_ON_ZERO
NO_UNSIGNED_SUBTRACTION
Others have suggested attaching SQL_MODE at creating to the created table, even as it is attached to routines--certainly good for assuring that partitioning functions stay the same when SQL_MODE differs from that at programming time (I forget the bug-report number), but otherwise it seems too severe. Considering what is in it, to make the command "SET SESSION SQL_MODE= ..." privileged is really too severe.
How to repeat:
mysql> set sql_mode ='';
Query OK, 0 rows affected (0.00 sec)
mysql> create temporary table mmu (v date not null);
Query OK, 0 rows affected (0.37 sec)
mysql> insert into mmu value (0);
Query OK, 1 row affected (0.02 sec)
mysql> select * from mmu;
+------------+
| v |
+------------+
| 0000-00-00 |
+------------+
1 row in set (0.00 sec)
mysql> set sql_mode='no_zero_date,traditional';
Query OK, 0 rows affected (0.00 sec)
mysql> insert into mmu value (0);
ERROR 1292 (22007): Incorrect date value: '0' for column 'v' at row 1
The type of field "v" has, in effect, changed.
Suggested fix:
These become part of the type when a relevant field or local variable is declared:
ALLOW_INVALID_DATES
NO_ZERO_DATE
NO_ZERO_IN_DATE
NO_AUTO_VALUE_ON_ZERO
NO_UNSIGNED_SUBTRACTION
If the desired declaration conflicts with the SQL_MODE, then the phrase is entered after "NOT", even as in "NOT NULL": "NOT NO_ZERO_DATE", "NOT NO_AUTO_VALUE_ON_ZERO". Thus the table-designer s intent is preserved.
© 2024, Oracle Corporation and/or its affiliates