Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-25076

SQL mode NO_FIELD_OPTIONS implicitly performs functions of NO_KEY_OPTIONS

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: 5.5, 10.1, 10.2, 10.3, 10.4, 10.5, 10.6
    • Fix Version/s: 10.6
    • Component/s: Variables
    • Labels:
      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.

        Attachments

          Activity

            People

            Assignee:
            serg Sergei Golubchik
            Reporter:
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.