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

Ignorable index makes the resulting CREATE TABLE invalid

Details

    Description

      The syntax for creating an ignorable index, according to MDEV_7317, is KEY .. IGNORE. However, the resulting SHOW CREATE TABLE shows "IGNORED" instead, and an attempt to use it causes a syntax error:

      10.6 03ff588d1

      MariaDB [test]> CREATE TABLE t (a INT, KEY (a) IGNORE);
      Query OK, 0 rows affected (0.046 sec)
       
      MariaDB [test]> SHOW CREATE TABLE t;
      +-------+---------------------------------------------------------------------------------------------------------------+
      | Table | Create Table                                                                                                  |
      +-------+---------------------------------------------------------------------------------------------------------------+
      | t     | CREATE TABLE `t` (
        `a` int(11) DEFAULT NULL,
        KEY `a` (`a`) IGNORED
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
      +-------+---------------------------------------------------------------------------------------------------------------+
      1 row in set (0.001 sec)
      

      MariaDB [test]> DROP TABLE t;
      Query OK, 0 rows affected (0.031 sec)
       
      MariaDB [test]> CREATE TABLE `t` (
          ->   `a` int(11) DEFAULT NULL,
          ->   KEY `a` (`a`) IGNORED
          -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ;
      ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') ENGINE=InnoDB DEFAULT CHARSET=latin1' at line 4
      

      Attachments

        Issue Links

          Activity

            Also, since the new ALTER INDEX sub-command took a form ALTER INDEX <name> <attribute>, it's better to make it consistent with CREATE TABLE and SHOW CREATE TABLE and use IGNORED in all of them, it's the more natural variant:

            • CREATE TABLE t (a INT, KEY(a) IGNORED)
            • ALTER TABLE t ALTER INDEX a NOT IGNORED
            • ALTER TABLE t ALTER INDEX a IGNORED
            elenst Elena Stepanova added a comment - Also, since the new ALTER INDEX sub-command took a form ALTER INDEX <name> <attribute> , it's better to make it consistent with CREATE TABLE and SHOW CREATE TABLE and use IGNORED in all of them, it's the more natural variant: CREATE TABLE t (a INT, KEY(a) IGNORED) ALTER TABLE t ALTER INDEX a NOT IGNORED ALTER TABLE t ALTER INDEX a IGNORED

            Yes blunder on my part, I just made the change in the output of SHOW CREATE TABLE but forgot the other cases.
            Have made a minor fix to the parser. Pushed the fix to a staging branch
            http://buildbot.askmonty.org/buildbot/grid?category=main&branch=bb-10.6-mdev7317

            varun Varun Gupta (Inactive) added a comment - Yes blunder on my part, I just made the change in the output of SHOW CREATE TABLE but forgot the other cases. Have made a minor fix to the parser. Pushed the fix to a staging branch http://buildbot.askmonty.org/buildbot/grid?category=main&branch=bb-10.6-mdev7317
            psergei Sergei Petrunia added a comment - ... Fails in the BB. Adjusted patch: https://github.com/mariadb/server/tree/bb-10.6-mdev25075 http://buildbot.askmonty.org/buildbot/grid?category=main&branch=bb-10.6-mdev25075

            Also, taking care about MDEV-25078 with this

            psergei Sergei Petrunia added a comment - Also, taking care about MDEV-25078 with this

            People

              psergei Sergei Petrunia
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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