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

alter table - failing to ADD KEY IF NOT EXISTS when existing dropping the same index name earlier in the alter table

        Details

          Description

          > CREATE TABLE b (`transaction_id` int(11) NOT NULL DEFAULT '0', KEY `transaction_id` (`transaction_id`));
Query OK, 0 rows affected (0.01 sec)

> alter table b DROP KEY transaction_id, ADD UNIQUE KEY IF NOT EXISTS (transaction_id);
Query OK, 0 rows affected, 1 warning (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 1

> show create table b;
+-------+-----------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                              |
+-------+-----------------------------------------------------------------------------------------------------------+
| b     | CREATE TABLE `b` (
  `transaction_id` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------------------------------------------+

          expected results: A table with a unique key.

          Alexey Botchkov says in MDEV-8212 that the IF EXISTS applies to what is there BEFORE the query. This is obviously how its implemented. What the expected behaviour reading the SQL statement?

            Gliffy Diagrams

              Attachments

                Issue Links

                relates to

                Bug - A problem which impairs or prevents the functions of the product. MDEV-8212 alter table - failing to ADD PRIMARY KEY IF NOT EXISTS when existing index of same as column name

                • Major - Major loss of function.
                • Closed

                  Activity

                  Hide
                  Permalink
                  serg Sergei Golubchik added a comment -

                  My expectation would be for a key to be created. That is, if this

                  alter table b DROP KEY transaction_id, ADD UNIQUE KEY (transaction_id);

                  succeeds, then this

                  alter table b DROP KEY transaction_id, ADD UNIQUE KEY IF NOT EXISTS (transaction_id);

                  must succeed too and the key should be created.

                  But I think it's not a major issue. And this behavior is pretty difficult to achieve in the current implementation.

                  So, we consider fixing this bug, but not in the next few releases.

                  Show
                  serg Sergei Golubchik added a comment - My expectation would be for a key to be created. That is, if this alter table b DROP KEY transaction_id, ADD UNIQUE KEY (transaction_id); succeeds, then this alter table b DROP KEY transaction_id, ADD UNIQUE KEY IF NOT EXISTS (transaction_id); must succeed too and the key should be created. But I think it's not a major issue. And this behavior is pretty difficult to achieve in the current implementation. So, we consider fixing this bug, but not in the next few releases.

                    People

                    • Assignee:
                      Unassigned
                      Reporter:
                      danblack Daniel Black
                    • Votes:
                      0 Vote for this issue
                      Watchers:
                      3 Start watching this issue

                      Dates

                      • Created:
                        Updated: