MemoryDB: Deleting rows silently fails if there are multiple CASCADING constraints pointing to the same parent

[seanlaff]

Have 3 tables, where cascade delete relationships look like this:

  graph TD;
      table2-->table1;
      table3-->table2;
      table3-->table1;

Delete a row from table1. It won't actually be deleted (a subsequent SELECT still returns it).

Here's a go reproduction. If you remove either constraint in table3, the bug doesn't happen- both must be present to see the bad behavior.

package main

import (
	"context"
	"database/sql"
	"fmt"
	"testing"

	sqle "github.com/dolthub/go-mysql-server"
	gmsSql "github.com/dolthub/go-mysql-server/sql"
	msql "github.com/dolthub/go-mysql-server/sql"
	vsql "github.com/dolthub/vitess/go/mysql"

	"github.com/dolthub/go-mysql-server/memory"
	"github.com/dolthub/go-mysql-server/server"

	_ "github.com/go-sql-driver/mysql"
)

var (
	dbName  = "mydb"
	address = "localhost"
	port    = 3306
)

func TestBadCascade(t *testing.T) {
	mdb := memory.NewDatabase(dbName)
	mdb.EnablePrimaryKeyIndexes()
	pro := memory.NewDBProvider(mdb)
	engine := sqle.NewDefault(pro)

	config := server.Config{
		Protocol: "tcp",
		Address:  fmt.Sprintf("%s:%d", address, port),
	}
	sessionBuilder := func(ctx context.Context, c *vsql.Conn, addr string) (gmsSql.Session, error) {
		host := ""
		user := ""
		mysqlConnectionUser, ok := c.UserData.(msql.MysqlConnectionUser)
		if ok {
			host = mysqlConnectionUser.Host
			user = mysqlConnectionUser.User
		}
		client := gmsSql.Client{Address: host, User: user, Capabilities: c.Capabilities}
		return memory.NewSession(msql.NewBaseSessionWithClientServer(addr, client, c.ConnectionID), pro), nil
	}
	s, err := server.NewServer(config, engine, sessionBuilder, nil)
	if err != nil {
		panic(err)
	}
	go func() {
		if err = s.Start(); err != nil {
			panic(err)
		}
	}()

	db, err := sql.Open("mysql", "/mydb")
	if err != nil {
		panic(err)
	}
	_, err = db.Exec(`
		CREATE TABLE table1 (
			id int NOT NULL AUTO_INCREMENT,
			PRIMARY KEY (id)
		)
	`)
	_, err = db.Exec(`
		CREATE TABLE table2	(
			id int NOT NULL AUTO_INCREMENT,
			table1Id int NOT NULL,
			PRIMARY KEY (id),
			CONSTRAINT t2tot1 FOREIGN KEY (table1Id) REFERENCES table1 (id) ON DELETE CASCADE
		)
	`)
	if err != nil {
		panic(err)
	}
	_, err = db.Exec(`
		CREATE TABLE table3	(
			id int NOT NULL AUTO_INCREMENT,
			table1Id int NOT NULL,
			table2Id int NOT NULL,
			PRIMARY KEY (id),
			CONSTRAINT t3tot1 FOREIGN KEY (table1Id) REFERENCES table1 (id) ON DELETE CASCADE,
			CONSTRAINT t3tot2 FOREIGN KEY (table2Id) REFERENCES table2 (id) ON DELETE CASCADE
		)
	`)
	if err != nil {
		panic(err)
	}
	_, err = db.Exec("INSERT INTO table1 VALUES ()")
	if err != nil {
		panic(err)
	}
	_, err = db.Exec("DELETE FROM table1 WHERE id = 1")
	if err != nil {
		panic(err)
	}
	row := db.QueryRow("SELECT count(*) AS Count FROM table1")
	var dst uint64
	err = row.Scan(&dst)
	if err != nil {
		panic(err)
	}
	if dst != 0 {
		panic("found non-zero rows")
	}
}

[timsehn]

We don't heavily use the in-memory implementation. We would welcome a contribution here.