Details
-
Bug
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
5.5, 10.1, 10.2, 10.3, 10.4, 10.5, 10.6
-
None
Description
Among compatibility SQL modes there are NO_FIELD_OPTIONS and NO_KEY_OPTIONS. They are very scarcely documented, all that is said about them implies that the names are self-explanatory; but they are not. In fact, NO_FIELD_OPTIONS also hides key options.
create table t (a int, key(a) using btree); |
show create table t; |
set sql_mode= 'NO_FIELD_OPTIONS'; |
show create table t; |
set sql_mode= 'NO_KEY_OPTIONS'; |
show create table t; |
drop table t; |
Query OK, 0 rows affected (0.042 sec) |
|
MariaDB [test]> show create table t; |
+-------+-------------------------------------------------------------------------------------------------------------------+ |
| Table | Create Table | |
+-------+-------------------------------------------------------------------------------------------------------------------+ |
| t | CREATE TABLE `t` ( |
`a` int(11) DEFAULT NULL, |
KEY `a` (`a`) USING BTREE |
) ENGINE=InnoDB DEFAULT CHARSET=latin1 | |
+-------+-------------------------------------------------------------------------------------------------------------------+ |
1 row in set (0.001 sec) |
MariaDB [test]> set sql_mode= 'NO_FIELD_OPTIONS'; |
Query OK, 0 rows affected (0.000 sec) |
|
MariaDB [test]> show create table t; |
+-------+-------------------------------------------------------------------------------------------------------+ |
| Table | Create Table | |
+-------+-------------------------------------------------------------------------------------------------------+ |
| t | CREATE TABLE `t` ( |
`a` int(11) DEFAULT NULL, |
KEY `a` (`a`) |
) ENGINE=InnoDB DEFAULT CHARSET=latin1 | |
+-------+-------------------------------------------------------------------------------------------------------+ |
1 row in set (0.000 sec) |
|
MariaDB [test]> select @@sql_mode; |
+------------------+ |
| @@sql_mode |
|
+------------------+ |
| NO_FIELD_OPTIONS |
|
+------------------+ |
1 row in set (0.000 sec) |
It is so in all versions of MariaDB and in MySQL up to 5.7. In MySQL 8.0 both modes have been removed.
I am not sure we can change it in existing versions, given that they are compatibility modes to begin with, but maybe adjustments could be made in upcoming versions – that is, if the plan is to keep the modes at all.