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

Unexpected syntax error in ALTER TABLE t1 ADD INDEX TYPE BTREE

        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.

            Gliffy Diagrams

              Attachments

                Activity

                There are no comments yet on this issue.

                  People

                  • Assignee:
                    bar Alexander Barkov
                    Reporter:
                    bar Alexander Barkov
                  • Votes:
                    0 Vote for this issue
                    Watchers:
                    2 Start watching this issue

                    Dates

                    • Created:
                      Updated: