Unique index not preventing a duplicate insert

[bojanz]

This might be a problem with the way I define the index.

I have the following database:

func testDatabase() *memory.Database {
	ctx := sql.NewEmptyContext()
	db := memory.NewDatabase("users")

	tableName := "users"
	table := memory.NewTable(tableName, sql.Schema{
		{Name: "id", Type: newVarChar(26), Nullable: false, Source: tableName, PrimaryKey: true},
		{Name: "namespace", Type: newVarChar(50), Nullable: false, Source: tableName},
		{Name: "name", Type: newVarChar(50), Nullable: false, Source: tableName},
		{Name: "label", Type: newVarChar(255), Nullable: false, Source: tableName},
		{Name: "created_at", Type: sql.Datetime, Nullable: false, Source: tableName},
		{Name: "updated_at", Type: sql.Datetime, Nullable: false, Source: tableName},
	})
	table.CreateIndex(ctx, "namespace__name", sql.IndexUsing_Default, sql.IndexConstraint_Unique, []sql.IndexColumn{
		{Name: "namespace"}, {Name: "name"},
	}, "")
	db.AddTable(tableName, table)

	return db
}

func newVarChar(length int64) sql.StringType {
	return sql.MustCreateStringWithDefaults(sqltypes.VarChar, length)
}

Once I start the db and do a "SHOW CREATE TABLE", I see my index:

CREATE TABLE `users` (
  `id` varchar(26) NOT NULL,
  `namespace` varchar(50) NOT NULL,
  `name` varchar(50) NOT NULL,
  `label` varchar(255) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `namespace__name` (`namespace`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |

And yet, I can insert as many identical namespace/name pairs as I want, no error is returned.

[VinaiRachakonda]

Hi @bojanz It's possible we have bugs in our memory implementation of a table. Most of our work with GMS is meant to support Dolt.

We'll triage this and get to it soon

[zachmu]

This is a bug: unique indexes other than primary keys are not enforced in the in-memory implementation.

[zachmu]

@VinaiRachakonda did you fix this with your recent work in this space?

[zachmu]

This needs an engine test -- we don't have tests of inserting duplicate indexes for the most part

[VinaiRachakonda]

No this is still an open issue. We do not properly support unique indexes with the memory table.

[jiale1029]

Any plan to support this in the near future?

[timsehn]

This is supported in Dolt (our implementation), just not in the in-memory tables. We would welcome a contribution but we don't use the in-memory tables so we're not super motivated to fix it.

[timsehn]

Updating my previous comment, this is also a problem in Dolt: https://github.com/dolthub/dolt/issues/4475

Resolving in favor of that bug.

[bojanz]

@timsehn What is the benefit in closing an unresolved bug report? Doesn't it just open the door for duplicate reports, requiring further duplicate responses? It is fine to say that there are no plans to work on a fix, and that it's up to the community, but the original report is still a valuable place for coordinating that.

[timsehn]

What is the benefit in closing an unresolved bug report? Doesn't it just open the door for duplicate reports, requiring further duplicate responses?

This is a duplicate and we're tracking it in another issue. Our main project is Dolt and that gets all the drive/attention. I personally track all the bugs there. We close many bugs per week. We have 7 devs working full time on Dolt. I don't pay as much attention to go-mysql-server (GMS). I asked Zach to go through all the bugs in GMS and move any that effected Dolt there so they would get more attention. Zach moved this bug over there. Once there, I noticed it was a dupe of one we were already tracking that had a clean Dolt repro. In fact, we're going to take another pass on unique indexes in the coming week since we have a handful pretty bad bugs in this space.

https://github.com/dolthub/dolt/issues?q=is%3Aissue+is%3Aopen+unique+index+label%3Abug

So, we are actually going to fix this because it was resolved in GMS and "upvoted" a bug in Dolt.

Moreover, I find there is value in keeping the bug space as lean and clean as possible. If we can keep it to a manageable sized queue a human can notice duplicates and prioritize fixes accordingly.

[timsehn]

So, I was wrong. This is actually a bug in GMS. Nothing with unique indexes works on the in-memory tables. The Dolt bug was a different root cause. Reopening and renaming to track.

[jycor]

Hello @bojanz, the code here has changed a bit since this issue was last touched on, and it appears that at some point this issue was resolved.

Here is an example of what the updated code should look like:

func something() {
    ctx := sql.NewEmptyContext()
    db := memory.NewDatabase("testDatabase")

    tableName := "users"
    schema := sql.Schema{
        {Name: "id", Type: newVarChar(26), Nullable: false, Source: tableName, PrimaryKey: true},
        {Name: "namespace", Type: newVarChar(50), Nullable: false, Source: tableName},
        {Name: "name", Type: newVarChar(50), Nullable: false, Source: tableName},
        {Name: "label", Type: newVarChar(255), Nullable: false, Source: tableName},
        {Name: "created_at", Type: sql.Datetime, Nullable: false, Source: tableName},
        {Name: "updated_at", Type: sql.Datetime, Nullable: false, Source: tableName},
    }
    pkSchema := sql.NewPrimaryKeySchema(schema)
    table := memory.NewTable(tableName, pkSchema, &memory.ForeignKeyCollection{})
    indexDef := sql.IndexDef{
        Name: "namespace__name",
        Columns: []sql.IndexColumn{
            {
                Name:   "namespace",
                Length: 0,
            },
            {
                Name:   "name",
                Length: 0,
            },
        },
        Constraint: sql.IndexConstraint_Unique,
    }
    table.CreateIndex(ctx, indexDef)
    db.AddTable(tableName, table)

    r1 := sql.Row{
        "id1",
        "namespace",
        "name",
        "label",
        time.Now(),
        time.Now(),
    }
    err := table.Insert(ctx, r1)
    if err != nil {
        panic(err)
    }
    r2 := sql.Row{
        "id2",
        "namespace",
        "name",
        "label",
        time.Now(),
        time.Now(),
    }
    err = table.Insert(ctx, r2)
    if err != nil {
        panic(err) // this should panic with duplicate unique key err
    }
}

Try it out, and let us know if there are any problems.