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.

      Attachments

        Issue Links

          relates to

          Task - A task that needs to be done. MDEV-20729 Fix REFERENCES constraint in column definition

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            bar Alexander Barkov created issue -
            bar Alexander Barkov made changes -
            Field Original Value New Value
            Description USING and TYPE are supposed to be synonyms in the key algorithm type clause,
            and both work fine in the following two scripts:
            {code}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            ALTER TABLE t1 ADD INDEX type USING BTREE (a);
            {code}

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

            However, if I remove the index name (which is optional, according to the grammar), USING still works fine:
            {code}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            ALTER TABLE t1 ADD INDEX USING BTREE (a);
            {code}
            but TYPE fails:
            {code}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            ALTER TABLE t1 ADD INDEX TYPE BTREE (a);
            {code}

            It returns the following error:
            {noformat}
            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
            {noformat}
            		 USING and TYPE are supposed to be synonyms in the key algorithm type clause,
            and both work fine in the following two scripts:
            {code}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            ALTER TABLE t1 ADD INDEX type USING BTREE (a);
            {code}

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

            However, if I remove the index name (which is optional, according to the grammar), USING still works fine:
            {code}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            ALTER TABLE t1 ADD INDEX USING BTREE (a);
            {code}
            but TYPE fails:
            {code}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            ALTER TABLE t1 ADD INDEX TYPE BTREE (a);
            {code}

            It returns the following error:
            {noformat}
            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
            {noformat}

            The problem is obviously in this piece of grammar in sql_yacc.yy:
            {code}
            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;
                      }
            {code}
            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".
            bar Alexander Barkov made changes -
            Description USING and TYPE are supposed to be synonyms in the key algorithm type clause,
            and both work fine in the following two scripts:
            {code}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            ALTER TABLE t1 ADD INDEX type USING BTREE (a);
            {code}

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

            However, if I remove the index name (which is optional, according to the grammar), USING still works fine:
            {code}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            ALTER TABLE t1 ADD INDEX USING BTREE (a);
            {code}
            but TYPE fails:
            {code}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            ALTER TABLE t1 ADD INDEX TYPE BTREE (a);
            {code}

            It returns the following error:
            {noformat}
            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
            {noformat}

            The problem is obviously in this piece of grammar in sql_yacc.yy:
            {code}
            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;
                      }
            {code}
            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".             		USING and TYPE are supposed to be synonyms in the key algorithm type clause,
            and both work fine in the following two scripts:
            {code}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            ALTER TABLE t1 ADD INDEX type USING BTREE (a);
            {code}

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

            However, if I remove the index name (which is optional, according to the grammar), USING still works fine:
            {code}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            ALTER TABLE t1 ADD INDEX USING BTREE (a);
            {code}
            but TYPE fails:
            {code}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            ALTER TABLE t1 ADD INDEX TYPE BTREE (a);
            {code}

            It returns the following error:
            {noformat}
            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
            {noformat}

            The problem is obviously in this piece of grammar in sql_yacc.yy:
            {code}
            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;
                      }
            {code}
            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:
            {code}
            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;
            {code}
            ignores the first USING and returns:
            {code}
            +-------+---------------------------------------------------------------------------------------------------------------------+
            | Table | Create Table |
            +-------+---------------------------------------------------------------------------------------------------------------------+
            | t1 | CREATE TABLE `t1` (
              `a` int(11) DEFAULT NULL,
              KEY `i1` (`a`) USING BTREE
            ) ENGINE=MEMORY DEFAULT CHARSET=latin1 |
            +-------+---------------------------------------------------------------------------------------------------------------------+
            {code}
            The expected behaviour would be to return an error telling about conflicting declarations.
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            Description USING and TYPE are supposed to be synonyms in the key algorithm type clause,
            and both work fine in the following two scripts:
            {code}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            ALTER TABLE t1 ADD INDEX type USING BTREE (a);
            {code}

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

            However, if I remove the index name (which is optional, according to the grammar), USING still works fine:
            {code}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            ALTER TABLE t1 ADD INDEX USING BTREE (a);
            {code}
            but TYPE fails:
            {code}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            ALTER TABLE t1 ADD INDEX TYPE BTREE (a);
            {code}

            It returns the following error:
            {noformat}
            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
            {noformat}

            The problem is obviously in this piece of grammar in sql_yacc.yy:
            {code}
            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;
                      }
            {code}
            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:
            {code}
            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;
            {code}
            ignores the first USING and returns:
            {code}
            +-------+---------------------------------------------------------------------------------------------------------------------+
            | Table | Create Table |
            +-------+---------------------------------------------------------------------------------------------------------------------+
            | t1 | CREATE TABLE `t1` (
              `a` int(11) DEFAULT NULL,
              KEY `i1` (`a`) USING BTREE
            ) ENGINE=MEMORY DEFAULT CHARSET=latin1 |
            +-------+---------------------------------------------------------------------------------------------------------------------+
            {code}
            The expected behaviour would be to return an error telling about conflicting declarations.
            		 USING and TYPE are supposed to be synonyms in the key algorithm type clause,
            and both work fine in the following two scripts:
            {code}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            ALTER TABLE t1 ADD INDEX type USING BTREE (a);
            {code}

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

            However, if I remove the index name (which is optional, according to the grammar), USING still works fine:
            {code}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            ALTER TABLE t1 ADD INDEX USING BTREE (a);
            {code}
            but TYPE fails:
            {code}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            ALTER TABLE t1 ADD INDEX TYPE BTREE (a);
            {code}

            It returns the following error:
            {noformat}
            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
            {noformat}

            The problem is obviously in this piece of grammar in sql_yacc.yy:
            {code}
            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;
                      }
            {code}
            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:
            {code}
            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;
            {code}
            The above script ignores the first USING and returns:
            {code}
            +-------+---------------------------------------------------------------------------------------------------------------------+
            | Table | Create Table |
            +-------+---------------------------------------------------------------------------------------------------------------------+
            | t1 | CREATE TABLE `t1` (
              `a` int(11) DEFAULT NULL,
              KEY `i1` (`a`) USING BTREE
            ) ENGINE=MEMORY DEFAULT CHARSET=latin1 |
            +-------+---------------------------------------------------------------------------------------------------------------------+
            {code}
            The expected behaviour would be to return an error telling about conflicting declarations.
            bar Alexander Barkov made changes -
            elenst Elena Stepanova made changes -
            Component/s Data Definition - Alter Table [ 10114 ]
            Affects Version/s 5.3.12 [ 12000 ]
            Affects Version/s 5.2.14 [ 12101 ]
            Affects Version/s 5.1.67 [ 12100 ]
            Affects Version/s 10.1 [ 16100 ]
            Affects Version/s 10.0 [ 16000 ]
            Affects Version/s 5.5 [ 15800 ]
            Labels upstream verified
            ratzpo Rasmus Johansson (Inactive) made changes -
            Workflow MariaDB v2 [ 60156 ] MariaDB v3 [ 66731 ]
            serg Sergei Golubchik made changes -
            Priority Major [ 3 ] Minor [ 4 ]
            danblack Daniel Black made changes -
            Affects Version/s 10.3.4 [ 22904 ]
            midenok Aleksey Midenkov made changes -
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 66731 ] MariaDB v4 [ 139789 ]

            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.