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

Unexpected syntax error in ALTER TABLE t1 ADD INDEX TYPE BTREE

    XMLWordPrintable

Details

    Description

      USING and TYPE are supposed to be synonyms in the key algorithm type clause,
      and both work fine in the following two scripts:

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a INT);
      ALTER TABLE t1 ADD INDEX type USING BTREE (a);

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a INT);
      ALTER TABLE t1 ADD INDEX type TYPE BTREE (a);

      However, if I remove the index name (which is optional, according to the grammar), USING still works fine:

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a INT);
      ALTER TABLE t1 ADD INDEX USING BTREE (a);

      but TYPE fails:

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a INT);
      ALTER TABLE t1 ADD INDEX TYPE BTREE (a);

      It returns the following error:

      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 'BTREE (a)' at line 1

      The problem is obviously in this piece of grammar in sql_yacc.yy:

      key_def:
                normal_key_type opt_if_not_exists_opt_table_element_name
                key_alg '(' key_list ')'
                { Lex->option_list= NULL; }
                normal_key_options
                {
                  if (add_create_index (Lex, $1, $2, Lex->check_exists))
                    MYSQL_YYABORT;
                }

      When it gets the word TYPE, it does no know if it is the index name, or the start of the key algorithm clause, such as "TYPE BTREE".

      There are more parser related problems:

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a INT) ENGINE=HEAP;
      CREATE INDEX i1 USING  HASH ON t1 (a) USING BTREE;
      SHOW CREATE TABLE t1;

      The above script ignores the first USING and returns:

      +-------+---------------------------------------------------------------------------------------------------------------------+
      | Table | Create Table                                                                                                        |
      +-------+---------------------------------------------------------------------------------------------------------------------+
      | t1    | CREATE TABLE `t1` (
        `a` int(11) DEFAULT NULL,
        KEY `i1` (`a`) USING BTREE
      ) ENGINE=MEMORY DEFAULT CHARSET=latin1 |
      +-------+---------------------------------------------------------------------------------------------------------------------+

      The expected behaviour would be to return an error telling about conflicting declarations.

      Attachments

        Issue Links

          Activity

            People

              bar Alexander Barkov
              bar Alexander Barkov
              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.