dolthub / dolthub/dolt

dolt_patch() attempts to re-create an index

Open
#9,677 6 comments 0 reactions 1 assignee Claimed by @elianddb View on GitHub
customer issue enhancement sql version control
Dominant language
Go
Stars
24.4k
Forks
873
Avg merge
1d 5h
Merged PRs (30d)
108

Description

I have two versions of a table schema, old and new, in branches of those names. In old, the description for a table is:

```OLD

+------------------------+--------------------------------------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+--------------------------------------------------------------+------+-----+---------+----------------+
| field_name | int unsigned | NO | UNI | NULL | |
+------------------------+--------------------------------------------------------------+------+-----+---------+----------------+
INDEXES
+------------+------------+--------------------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+------------+------------+--------------------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| table_name | 0 | field_name_UNIQUE | 1 | field_name | NULL | 0 | NULL | NULL | | BTREE | | | YES | NULL |
+------------+------------+--------------------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
```

And in the new version,

```NEW
+------------------------+--------------------------------------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+--------------------------------------------------------------+------+-----+---------+----------------+
| field_name | int unsigned | NO | UNI | NULL | |
+------------------------+--------------------------------------------------------------+------+-----+---------+----------------+
INDEXES
+------------+------------+--------------------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+------------+------------+--------------------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| table_name | 0 | field_name_UNIQUE | 1 | field_name | NULL | 0 | NULL | NULL | | BTREE | | | YES | NULL |
+------------+------------+--------------------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
```
So why, when I call:
``` dolt sql -q "SELECT statement FROM dolt_patch('old', 'new', 'table_name') WHERE diff_type = 'schema';" --result-format=csv```

Do I get this:

```ALTER TABLE `table_name` DROP INDEX `field_name_UNIQUE`;```
```ALTER TABLE `table_name` ADD INDEX `field_name_UNIQUE`(`field_name`);```

Why is there any need to destroy and recreate the index?

What's more, if I do NOT specify the table name, and just call:
``` dolt sql -q "SELECT statement FROM dolt_patch('old', 'new') WHERE diff_type = 'schema';" --result-format=csv```
The `DROP` disappears, and we are left only with the `ADD INDEX`, which tries to create an index which is already there.

Contributor guide

No contributing guide indexed for this repository

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.