main_test.go

package main

import (
	"database/sql"
	"fmt"
	"log"
	"os"
	"os/exec"
	"strings"
	"testing"

	"github.com/ory/dockertest"
)

var toolExecutable = "./random-data-load"

var testsdb map[string]struct {
	resource *dockertest.Resource
	db       *sql.DB
	port     string
}

func TestMain(m *testing.M) {

	// uses a sensible default on windows (tcp/http) and linux/osx (socket)
	// DOCKER_HOST=unix:///run/user/1000/docker.sock go test .
	pool, err := dockertest.NewPool("")
	if err != nil {
		log.Panicf("Could not construct pool: %s", err)
	}

	err = pool.Client.Ping()
	if err != nil {
		log.Panicf("Could not connect to Docker: %s", err)
	}

	pgresource, err := pool.Run("postgres", "17", []string{"POSTGRES_PASSWORD=dockertest", "POSTGRES_USER=dockertest", "POSTGRES_DB=test"})
	if err != nil {
		log.Panicf("Could not start pg resource: %s", err)
	}
	// MYSQL_TAG=5.7 runs the same cases against an older server
	mysqlTag := os.Getenv("MYSQL_TAG")
	if mysqlTag == "" {
		mysqlTag = "8.0"
	}
	mysqlresource, err := pool.Run("mysql", mysqlTag, []string{"MYSQL_ROOT_PASSWORD=dockertest", "MYSQL_PASSWORD=dockertest", "MYSQL_DATABASE=test", "MYSQL_USER=dockertest"})
	if err != nil {
		log.Panicf("Could not start mysql resource: %s", err)
	}
	/*	defer func() {
			for _, resource := range []*dockertest.Resource{pgresource, mysqlresource} {
				if err := pool.Purge(resource); err != nil {
					log.Panicf("Could not purge resource: %s", err)
				}
			}
		}()
	*/

	var pgdb *sql.DB
	if err = pool.Retry(func() error {
		pgdb, err = sql.Open("postgres", fmt.Sprintf("postgres://dockertest:dockertest@%s/test?sslmode=disable", pgresource.GetHostPort("5432/tcp")))
		if err != nil {
			return err
		}
		return pgdb.Ping()
	}); err != nil {
		log.Panicf("Could not connect to pg docker: %s", err)
	}

	// The image only grants the test user the `test` database, and the catalog
	// only ever shows a user the objects it has rights on. A fixture giving a
	// constraint name a namesake in a second database needs both to create it
	// and to have the tool see it. Global privileges only reach a client on
	// its next connection, so this comes before the test user connects.
	var mysqlrootdb *sql.DB
	if err = pool.Retry(func() error {
		mysqlrootdb, err = sql.Open("mysql", fmt.Sprintf("root:dockertest@(localhost:%s)/test", mysqlresource.GetPort("3306/tcp")))
		if err != nil {
			return err
		}
		return mysqlrootdb.Ping()
	}); err != nil {
		log.Panicf("Could not connect to mysql docker as root: %s", err)
	}
	if _, err = mysqlrootdb.Exec("GRANT ALL PRIVILEGES ON *.* TO 'dockertest'@'%'"); err != nil {
		log.Panicf("Could not grant privileges to the mysql test user: %s", err)
	}
	mysqlrootdb.Close()

	mysqldb, err := sql.Open("mysql", fmt.Sprintf("dockertest:dockertest@(localhost:%s)/test?multiStatements=true", mysqlresource.GetPort("3306/tcp")))
	if err != nil {
		log.Panicf("Could not connect to mysql docker: %s", err)
	}
	if err = mysqldb.Ping(); err != nil {
		log.Panicf("Could not connect to mysql docker: %s", err)
	}

	testsdb = map[string]struct {
		resource *dockertest.Resource
		db       *sql.DB
		port     string
	}{
		"pg": struct {
			resource *dockertest.Resource
			db       *sql.DB
			port     string
		}{
			resource: pgresource,
			db:       pgdb,
			port:     pgresource.GetPort("5432/tcp"),
		},
		"mysql": struct {
			resource *dockertest.Resource
			db       *sql.DB
			port     string
		}{
			resource: mysqlresource,
			db:       mysqldb,
			port:     mysqlresource.GetPort("3306/tcp"),
		},
	}

	// run tests
	code := m.Run()

	if code != 0 && keepDB() {
		log.Printf("Keeping database running because tests failed and KEEP_DB=1")
		return
	}
	for _, resource := range []*dockertest.Resource{pgresource, mysqlresource} {
		if err := pool.Purge(resource); err != nil {
			log.Panicf("Could not purge resource: %s", err)
		}
	}
}

func TestRun(t *testing.T) {

	tests := []struct {
		name       string
		checkQuery string // used to check if the generated result seems appropriate
		inputQuery string // applicative query we want to optimize
		engines    []string
		tables     []string
		cmds       [][]string
		verify     []string // a "verify --strict" pass over what the run left
		expectErr  string   // the run has to fail, and to say this much
	}{
		{
			name:       "basic",
			checkQuery: "select count(*) = 10 from t1;",
			engines:    []string{"pg", "mysql"},
			cmds:       [][]string{[]string{"--rows=10", "--table=t1"}},
		},

		{
			name:       "pk_bigserial",
			checkQuery: "select count(*) = 100 from t1;",
			engines:    []string{"pg"},
			cmds:       [][]string{[]string{"--rows=100", "--table=t1"}},
		},
		{
			name:       "pk_identity",
			checkQuery: "select count(*) = 100 from t1 where id < 101;",
			engines:    []string{"pg"},
			cmds:       [][]string{[]string{"--rows=100", "--table=t1"}},
		},
		{
			name:       "pk",
			checkQuery: "select count(*) = 100 from t1;",
			engines:    []string{"pg", "mysql"},
			cmds:       [][]string{[]string{"--rows=100", "--table=t1"}},
		},
		{
			name:       "pk_auto_increment",
			checkQuery: "select count(*) = 100 from t1 where id < 101;",
			engines:    []string{"mysql"},
			cmds:       [][]string{[]string{"--rows=100", "--table=t1"}},
		},

		{
			name:       "pk_varchar",
			checkQuery: "select count(*) = 100 from t1;",
			engines:    []string{"pg", "mysql"},
			cmds:       [][]string{[]string{"--rows=100", "--table=t1"}},
		},

		{
			name:       "bool",
			checkQuery: "select (count(*) = 100) and (sum(CASE WHEN c1 THEN 1 ELSE 0 END) between 1 and 99) from t1;",
			engines:    []string{"pg", "mysql"},
			cmds:       [][]string{[]string{"--rows=100", "--table=t1"}},
		},

		{
			// postgres reports char(n) as "character" and a bare time as "time
			// without time zone". Neither used to be mapped, so both columns
			// were left out of the INSERT and the NOT NULL ones failed the run.
			name:       "char_types",
			checkQuery: "select (count(*) = 100) and (count(c1) = 100) and (count(c3) = 100) from t1;",
			engines:    []string{"pg"},
			cmds:       [][]string{[]string{"--rows=100", "--table=t1"}},
		},

		{
			// numeric(p,s) holds p digits with s after the point, so a
			// numeric(5,2) tops out at 999.99. The precision used to be read as
			// a magnitude, which put the draw in range of 99999 and had the
			// insert rejected.
			name:       "numeric_scale",
			checkQuery: "select (count(*) = 100) and (max(c1) < 1000) and (max(c3) < 10) and (count(c1) = 100) from t1;",
			engines:    []string{"pg", "mysql"},
			cmds:       [][]string{[]string{"--rows=100", "--table=t1", "--null-freq=0"}},
		},

		{
			name:       "timestamp",
			checkQuery: "select (count(*) = 100) and (sum(CASE WHEN c1 between '2015-07-02' and '2020-09-09' THEN 1 ELSE 0 END) = 100) from t1;",
			engines:    []string{"pg", "mysql"},
			cmds:       [][]string{[]string{"--rows=100", "--table=t1", "--min-generated-time=2015-07-02T00:00:00Z", "--max-generated-time=2020-09-08T00:00:00Z", "--null-freq=0"}},
		},

		{
			name:       "fk_uniform",
			checkQuery: "select count(*) = 100 from t1 join t2 on t1.id = t2.t1_id;",
			engines:    []string{"pg", "mysql"},
			cmds:       [][]string{[]string{"--rows=100", "--table=t1"}, []string{"--rows=100", "--table=t2", "--default-relationship=sequential"}},
		},

		// not a great test for now, but we want some matches, but not every lines matched
		{
			name:       "fk_binomial",
			checkQuery: "select count(distinct t1.id) between 1 and 99 from t1 join t2 on t1.id = t2.t1_id;",
			engines:    []string{"pg", "mysql"},
			cmds:       [][]string{[]string{"--rows=100", "--table=t1"}, []string{"--rows=100", "--table=t2", "--default-relationship=binomial", "--coin-flip-percent=60"}},
		},
		{
			// The tuning of every sampler can be given per parent table, which
			// is the only way a run touching several relationships can satisfy
			// them all at once. Routing it wrongly is visible here: over a
			// 1000-row parent the default mean is row 500, so a mean of 50
			// landing where it was asked for keeps every sampled id low.
			name:       "fk_normal_per_parent",
			checkQuery: "select (count(*) = 200) and (max(t1_id) < 200) and (count(distinct t1_id) > 1) from t2;",
			engines:    []string{"pg", "mysql"},
			cmds: [][]string{
				[]string{"--rows=1000", "--table=t1"},
				[]string{"--rows=200", "--table=t2", "--default-relationship=normal", "--normal-mean=t1=50", "--normal-stddev=t1=5", "--null-freq=0"},
			},
		},

		{
			name:       "fk_pareto",
			checkQuery: "select count(distinct t1.id) between 1 and 99 from t1 join t2 on t1.id = t2.t1_id;",
			engines:    []string{"pg", "mysql"},
			cmds:       [][]string{[]string{"--rows=100", "--table=t1"}, []string{"--rows=100", "--table=t2", "--default-relationship=pareto"}},
		},
		// A zipf law over 1000 parents puts 17.9% of the children on the
		// hottest one and 48.1% on the ten hottest, whatever the bulk size.
		// Each bulk used to give every parent it drew the same share, however
		// often it was drawn, so the head came out flattened by an amount the
		// bulk size decided: t2 held about 1000 on its hottest parent and t3
		// about 80, for the 8970 asked for.
		{
			name: "fk_pareto_bulk_size",
			checkQuery: `select (select max(c) from (select count(*) c from t2 group by t1_id) a) between 7500 and 10500
				and (select sum(c) from (select count(*) c from t2 group by t1_id order by c desc limit 10) b) between 22000 and 26000
				and (select max(c) from (select count(*) c from t3 group by t1_id) a) between 7500 and 10500
				and (select sum(c) from (select count(*) c from t3 group by t1_id order by c desc limit 10) b) between 22000 and 26000;`,
			engines: []string{"pg", "mysql"},
			cmds: [][]string{
				[]string{"--rows=1000", "--table=t1"},
				[]string{"--rows=50000", "--table=t2", "--default-relationship=pareto", "--bulk-size=100"},
				[]string{"--rows=50000", "--table=t3", "--default-relationship=pareto", "--bulk-size=5000"},
			},
		},
		// A bell curve of 50000 children over the parents around row 500, 20
		// rows wide, puts about 4990 of them on the 5 parents at its mean and
		// 680 on the 5 at two standard deviations. Filled the way the zipf law
		// was, every parent drawn in a bulk got the same share of it, and the
		// curve came out as a plateau: about 2500 in both places.
		{
			name: "fk_normal_bulk_size",
			checkQuery: `select (select count(*) from t2 where t1_id between 498 and 502) between 4300 and 5700
				and (select count(*) from t2 where t1_id between 538 and 542) between 450 and 900
				and (select count(*) from t3 where t1_id between 498 and 502) between 4300 and 5700
				and (select count(*) from t3 where t1_id between 538 and 542) between 450 and 900;`,
			engines: []string{"pg", "mysql"},
			cmds: [][]string{
				[]string{"--rows=1000", "--table=t1"},
				[]string{"--rows=50000", "--table=t2", "--default-relationship=normal", "--normal-mean=t1=500", "--normal-stddev=t1=20", "--bulk-size=100"},
				[]string{"--rows=50000", "--table=t3", "--default-relationship=normal", "--normal-mean=t1=500", "--normal-stddev=t1=20", "--bulk-size=5000"},
			},
		},
		{
			name:       "fk_normal",
			checkQuery: "select count(distinct t1.id) between 1 and 99 from t1 join t2 on t1.id = t2.t1_id;",
			engines:    []string{"pg", "mysql"},
			cmds:       [][]string{[]string{"--rows=100", "--table=t1"}, []string{"--rows=100", "--table=t2", "--default-relationship=normal"}},
		},

		// The normal law reaches past the end of the table, so half of these
		// row numbers have to be drawn again. Testing the same draw again
		// could only give the same row number back, and looped forever.
		{
			name:       "fk_normal_outside_table",
			checkQuery: "select count(distinct t1.id) between 1 and 99 from t1 join t2 on t1.id = t2.t1_id;",
			engines:    []string{"pg", "mysql"},
			cmds:       [][]string{[]string{"--rows=100", "--table=t1"}, []string{"--rows=100", "--table=t2", "--default-relationship=normal", "--normal-mean=100", "--normal-stddev=50"}},
		},

		// 5% of 1000 will end up being 50, but we need 100 samples per chunks and t1_id has NOT NULL so it has to loop to get more samples
		{
			name:       "fk_binomial_looping_chunks",
			checkQuery: "select count(distinct t1.id) between 1 and 999 from t1 join t2 on t1.id = t2.t1_id;",
			engines:    []string{"pg", "mysql"},
			cmds:       [][]string{[]string{"--rows=1000", "--table=t1"}, []string{"--rows=1000", "--table=t2", "--default-relationship=binomial", "--coin-flip-percent=5", "--bulk-size=100"}},
		},

		{
			name:       "fk_multicol",
			checkQuery: "select count(*) = 100 from t1 join t2 on t1.id = t2.t1_id and t1.id2 = t2.t1_id2;",
			engines:    []string{"pg", "mysql"},
			cmds:       [][]string{[]string{"--rows=100", "--table=t1"}, []string{"--rows=100", "--table=t2", "--default-relationship=sequential"}},
		},

		// A constraint is only identified by its name together with the table
		// holding it. Looked up by name alone, the namesake the fixture adds
		// gets its column folded into t2's constraint, which then lists t1_id
		// twice in the INSERT, and points at the namesake's parent table.
		{
			name:       "fk_shared_constraint_name",
			checkQuery: "select count(*) = 100 from t1 join t2 on t1.id = t2.t1_id;",
			engines:    []string{"pg", "mysql"},
			cmds:       [][]string{[]string{"--rows=100", "--table=t1"}, []string{"--rows=100", "--table=t2", "--default-relationship=sequential"}},
		},

		{
			name:       "fk_pivot_varchar_integer",
			checkQuery: "select count(*) = 100 from t1 join t3 on t1.order_id=t3.order_id join t2 on t2.id = t3.product_no;",
			inputQuery: "select sum(t2.price), count(t1.*) from t1 join t3 on t1.order_id=t3.order_id join t2 on t2.id = t3.product_no where t1.currency='EUR';",
			engines:    []string{"pg", "mysql"},
			cmds:       [][]string{[]string{"--rows=100", "--default-relationship=sequential"}},
		},

		{
			// Without --truncate a second run adds to what the first left, so
			// the count here would be 200.
			name:       "truncate",
			checkQuery: "select count(*) = 100 from t1;",
			engines:    []string{"pg", "mysql"},
			cmds: [][]string{
				[]string{"--rows=100", "--table=t1"},
				[]string{"--rows=100", "--table=t1", "--truncate"},
			},
		},

		{
			// Emptying a set of tables that point at each other: postgres needs
			// them in one statement, mysql needs its key checks held off.
			name:       "truncate_fk",
			checkQuery: "select (select count(*) from t1) = 100 and (select count(*) from t2) = 100;",
			inputQuery: "select t1.id, t2.id from t1 join t2 on t1.id = t2.t1_id;",
			engines:    []string{"pg", "mysql"},
			cmds: [][]string{
				[]string{"--rows=100"},
				[]string{"--rows=100", "--truncate"},
			},
		},

		{
			name:       "basic_query",
			checkQuery: "select (count(*) = 100) and (sum(CASE WHEN c2 IS NULL THEN 1 ELSE 0 END) = 100)  from t1 where c1 is not null;",
			inputQuery: "select c1 from t1;",
			engines:    []string{"pg", "mysql"},
			cmds:       [][]string{[]string{"--rows=100", "--table=t1", "--null-freq=0"}},
		},

		{
			name:       "identifiers_skip_not_null_nodefaults",
			checkQuery: "select (count(*) = 100) and (sum(CASE WHEN c2 <> '' THEN 1 ELSE 0 END) = 100)  from t1 where c1 is not null;",
			inputQuery: "select c1 from t1;",
			engines:    []string{"pg", "mysql"},
			cmds:       [][]string{[]string{"--rows=100", "--table=t1"}},
		},

		{
			name:       "identifiers_skip_not_null_defaults",
			checkQuery: "select (count(*) = 100) and (sum(CASE WHEN c2 <> 'test' THEN 1 ELSE 0 END) = 0)  from t1 where c1 is not null;",
			inputQuery: "select c1 from t1;",
			engines:    []string{"pg", "mysql"},
			cmds:       [][]string{[]string{"--rows=100", "--table=t1"}},
		},

		{
			name:       "identifiers_skip_fk_multicol",
			checkQuery: "select count(*) = 100 from t1 join t2 on t1.id = t2.t1_id and t1.id2 = t2.t1_id2;",
			inputQuery: "select a1.id, a1.id2 from t1 a1 join t2 a2 on a1.id = a2.t1_id;",
			engines:    []string{"pg", "mysql"},
			cmds:       [][]string{[]string{"--rows=100", "--table=t1"}, []string{"--rows=100", "--table=t2", "--default-relationship=sequential"}},
		},
		{
			name: "fk_cascade_recursive",
			// t1 alone, t2 dep on t1, t3 dep on t2 and t4 dep on t2+t3
			checkQuery: "select count(*) = 100 from t1 join t2 on t1.id = t2.t1_id join t3 on t2.id = t3.t2_id join t4 on t3.id = t4.t3_id and t2.id = t4.t2_id;",
			inputQuery: "select count(*) = 100 from t1 join t2 on t1.id = t2.t1_id join t3 on t2.id = t3.t2_id join t4 on t3.id = t4.t3_id and t2.id = t4.t2_id;",
			engines:    []string{"pg", "mysql"},
			cmds:       [][]string{[]string{"--rows=100", "--default-relationship=sequential"}},
		},
		{
			// same as above, but with the query join order reversed
			name:       "fk_cascade_recursive_reversed",
			checkQuery: "select count(*) = 100 from t4 join t2 on t2.id = t4.t2_id join t3 on t3.id = t4.t3_id and t3.t2_id = t2.id join t1 on t1.id = t2.t1_id;",
			inputQuery: "select count(*) = 100 from t4 join t2 on t2.id = t4.t2_id join t3 on t3.id = t4.t3_id and t3.t2_id = t2.id join t1 on t1.id = t2.t1_id;",
			engines:    []string{"pg", "mysql"},
			cmds:       [][]string{[]string{"--rows=100", "--default-relationship=sequential"}},
		},

		{
			name:       "fk_virtual",
			checkQuery: "select count(*) = 100 from t1 join t2 on t1.id = t2.t1_id;",
			inputQuery: "select * from t1 join t2 on t1.id = t2.t1_id;",
			engines:    []string{"pg", "mysql"},
			cmds:       [][]string{[]string{"--rows=100", "--table=t1"}, []string{"--rows=100", "--table=t2", "--default-relationship=sequential"}},
		},

		{
			// The join is written through a CTE, so it can only be found by
			// descending into the CTE body and projecting the reference down
			// onto the table it reads.
			name:       "fk_virtual_cte",
			checkQuery: "select count(*) = 100 from t1 join t2 on t1.id = t2.t1_id;",
			inputQuery: "with a as (select * from t1) select * from a join t2 on a.id = t2.t1_id;",
			engines:    []string{"pg", "mysql"},
			cmds:       [][]string{[]string{"--rows=100", "--table=t1"}, []string{"--rows=100", "--table=t2", "--default-relationship=sequential"}},
		},

		{
			name:       "fk_virtual_derived_table",
			checkQuery: "select count(*) = 100 from t1 join t2 on t1.id = t2.t1_id;",
			inputQuery: "select * from (select * from t1) a join t2 on a.id = t2.t1_id;",
			engines:    []string{"pg", "mysql"},
			cmds:       [][]string{[]string{"--rows=100", "--table=t1"}, []string{"--rows=100", "--table=t2", "--default-relationship=sequential"}},
		},

		{
			// A semi-join asks for the same value overlap as a join: t2's
			// values have to exist in t1.
			name:       "fk_virtual_semijoin",
			checkQuery: "select count(*) = 100 from t1 join t2 on t1.id = t2.t1_id;",
			inputQuery: "select * from t2 where t2.t1_id in (select t1.id from t1);",
			engines:    []string{"pg", "mysql"},
			cmds:       [][]string{[]string{"--rows=100", "--table=t1"}, []string{"--rows=100", "--table=t2", "--default-relationship=sequential"}},
		},

		{
			// A comma-separated FROM puts the join condition in WHERE.
			name:       "fk_virtual_where_join",
			checkQuery: "select count(*) = 100 from t1 join t2 on t1.id = t2.t1_id;",
			inputQuery: "select * from t1, t2 where t1.id = t2.t1_id;",
			engines:    []string{"pg", "mysql"},
			cmds:       [][]string{[]string{"--rows=100", "--table=t1"}, []string{"--rows=100", "--table=t2", "--default-relationship=sequential"}},
		},

		{
			// A composite key guessed from the query, with no foreign key in
			// the schema to fall back on. Both columns have to come from the
			// same parent row, which is only guaranteed if the two equalities
			// are generated as one key rather than two.
			name:       "fk_virtual_multicol",
			checkQuery: "select count(*) = 100 from t1 join t2 on t1.id = t2.t1_id and t1.id2 = t2.t1_id2;",
			inputQuery: "select * from t1 join t2 on t1.id = t2.t1_id and t1.id2 = t2.t1_id2;",
			engines:    []string{"pg", "mysql"},
			cmds:       [][]string{[]string{"--rows=100", "--table=t1"}, []string{"--rows=100", "--table=t2", "--default-relationship=sequential"}},
		},

		{
			name:       "fk_virtual_cascade_table_per_table",
			checkQuery: "select count(*) = 100 from t1 join t2 on t1.id = t2.t1_id join t3 on t2.id = t3.t2_id join t4 on t3.id = t4.t3_id;",
			inputQuery: "select * from t1 join t2 on t1.id = t2.t1_id join t3 on t2.id = t3.t2_id join t4 on t3.id = t4.t3_id;",
			engines:    []string{"pg", "mysql"},
			cmds:       [][]string{[]string{"--rows=100", "--table=t1"}, []string{"--rows=100", "--table=t2", "--default-relationship=sequential"}, []string{"--rows=100", "--table=t3", "--default-relationship=sequential"}, []string{"--rows=100", "--table=t4", "--default-relationship=sequential"}},
		},
		{
			name:       "fk_virtual_cascade_recursive",
			checkQuery: "select count(*) = 100 from t1 join t2 on t1.id = t2.t1_id join t3 on t2.id = t3.t2_id join t4 on t3.id = t4.t3_id;",
			inputQuery: "select count(*) = 100 from t1 join t2 on t1.id = t2.t1_id join t3 on t2.id = t3.t2_id join t4 on t3.id = t4.t3_id;",
			engines:    []string{"pg", "mysql"},
			cmds:       [][]string{[]string{"--rows=100", "--default-relationship=sequential"}},
		},

		{
			name:       "star_query",
			checkQuery: "select (count(*) = 100) and (sum(CASE WHEN c2 IS NOT NULL THEN 1 ELSE 0 END) = 100)  from t1 where c1 is not null;",
			inputQuery: "select * from t1;",
			engines:    []string{"pg", "mysql"},
			cmds:       [][]string{[]string{"--rows=100", "--table=t1"}},
		},

		{
			name:       "text_max_size",
			checkQuery: "select (count(*) = 100) from t1 where length(data) < 10;",
			engines:    []string{"pg", "mysql"},
			cmds:       [][]string{[]string{"--rows=100", "--table=t1", "--max-text-size=9"}},
		},

		{
			name:       "uuid",
			checkQuery: "select (count(*) = 100) from t1;",
			engines:    []string{"pg"},
			cmds:       [][]string{[]string{"--rows=100", "--table=t1"}},
		},

		{
			name:       "enum",
			checkQuery: "select (sum(CASE WHEN possible_values = 'V1' THEN 1 ELSE 0 END) > 0) and (sum(CASE WHEN possible_values = 'V2' THEN 1 ELSE 0 END) > 0) and (sum(CASE WHEN possible_values = 'V3' THEN 1 ELSE 0 END) > 0) from t1;",
			engines:    []string{"mysql"},
			cmds:       [][]string{[]string{"--rows=300", "--table=t1"}},
		},

		{
			name:       "mixed_cases",
			checkQuery: "select count(*) = 100 from `SomeTABLEWithCase` where `COLUMN_1` is not null and `aNOTHER_COLUMN` is not null;",
			engines:    []string{"mysql"},
			cmds:       [][]string{[]string{"--rows=100", "--table=SomeTABLEWithCase", "--null-freq=0"}},
		},

		{
			name:       "fk_virtual_mixed_cases",
			checkQuery: "select count(*) = 100 from `PARENT_TABLE` pT join `CHILD_TABLE` cT on pT.`ParentTableId` = cT.`ParentTableId` where pT.`pARENTTableData` is not null;",
			inputQuery: "select * from `PARENT_TABLE` pT join `CHILD_TABLE` cT on pT.`ParentTableId` = cT.`ParentTableId` where pT.`pARENTTableData` is not null;",
			engines:    []string{"mysql"},
			cmds:       [][]string{[]string{"--rows=100", "--default-relationship=sequential", "--null-freq=0"}},
		},

		{
			name:       "fk_sbtest_pointing_to_shared_tables",
			checkQuery: "SELECT count(*) = 100 FROM t1 LEFT JOIN t2 ON t1.id = t2.id LEFT JOIN t3 ON t1.id = t3.id LEFT JOIN t4 ON t1.id = t4.id",
			inputQuery: "SELECT t1.c, t2.c, t3.c, t4.c FROM t1 LEFT JOIN t2 ON t1.id = t2.id LEFT JOIN t3 ON t1.id = t3.id LEFT JOIN t4 ON t1.id = t4.id WHERE t1.id = 49877",
			engines:    []string{"mysql"},
			cmds:       [][]string{[]string{"--rows=100", "--default-relationship=sequential", "--null-freq=0"}},
		},

		{
			name:       "fk_sbtest_pointing_to_shared_tables_no_fk_guess_manual_fks",
			checkQuery: "SELECT count(*) = 100 FROM t1 LEFT JOIN t2 ON t1.id = t2.id LEFT JOIN t3 ON t1.id = t3.id LEFT JOIN t4 ON t1.id = t4.id",
			inputQuery: "SELECT t1.c, t2.c, t3.c, t4.c FROM t1 LEFT JOIN t2 ON t1.id = t2.id LEFT JOIN t3 ON t1.id = t3.id LEFT JOIN t4 ON t1.id = t4.id WHERE t1.id = 49877",
			engines:    []string{"mysql"},
			cmds:       [][]string{[]string{"--rows=100", "--default-relationship=sequential", "--null-freq=0", "--no-fk-guess", "--add-fk=\"t1.id=t2.id;t1.id=t3.id;t1.id=t4.id\""}},
		},

		{
			name:       "null_map",
			checkQuery: "select (count(*) = 100000) AND (sum(CASE WHEN c1 IS NULL THEN 1 ELSE 0 END) between 19500 and 20500) AND (sum(CASE WHEN c2 IS NULL THEN 1 ELSE 0 END) between 39500 and 40500) AND (sum(CASE WHEN c3 IS NULL THEN 1 ELSE 0 END) between 89500 and 90500) from t1;",
			engines:    []string{"pg", "mysql"},
			cmds:       [][]string{[]string{"--rows=100000", "--table=t1", "--null-freq=0.2;t1.c2=0.4;t1.c3=0.9"}},
		},

		{
			name:       "values_freq_map",
			checkQuery: "select (count(*) = 100000) AND (sum(CASE WHEN c1 = 42 THEN 1 ELSE 0 END) between 79500 and 80500) AND (sum(CASE WHEN c1 = 7 THEN 1 ELSE 0 END) between 4500 and 5500) AND (sum(CASE WHEN c2 = 'pg' THEN 1 ELSE 0 END) between 36500 and 37500) AND (sum(CASE WHEN c2 = 'mysql' THEN 1 ELSE 0 END) between 33500 and 34500) AND (sum(CASE WHEN c2 = 'other' THEN 1 ELSE 0 END) between 400 and 600) from t1;",
			engines:    []string{"pg", "mysql"},
			cmds:       [][]string{[]string{"--rows=100000", "--table=t1", "--null-freq=0", "--values-freq-map=t1.c1=42:0.8,7:0.05;t1.c2=pg:0.37,mysql:0.34,other:0.005"}},
		},

		{
			name:       "query_params",
			checkQuery: "select (count(*) = 100000) AND (sum(CASE WHEN c2 = 'it' THEN 1 ELSE 0 END) between 9500 and 10500) AND (sum(CASE WHEN c2 = 'should' THEN 1 ELSE 0 END) between 9500 and 10500) AND (sum(CASE WHEN c2 = 'work' THEN 1 ELSE 0 END) between 9500 and 10500) from t1;",
			inputQuery: "select * from t1 where t1.c2 in ('it', 'should', 'work')",
			engines:    []string{"pg", "mysql"},
			cmds:       [][]string{[]string{"--rows=100000", "--table=t1", "--null-freq=0", "--query-param-freq=0.1"}},
		},
		{
			name:       "query_params_no_infix",
			checkQuery: "select (count(*) = 100000) AND (sum(CASE WHEN c2 = 'it' THEN 1 ELSE 0 END) between 9500 and 10500) AND (sum(CASE WHEN c2 = 'should' THEN 1 ELSE 0 END) between 9500 and 10500) AND (sum(CASE WHEN c2 = 'work' THEN 1 ELSE 0 END) between 9500 and 10500) from t1;",
			inputQuery: "select * from t1 where c2 in ('it', 'should', 'work')",
			engines:    []string{"pg", "mysql"},
			cmds:       [][]string{[]string{"--rows=100000", "--table=t1", "--null-freq=0", "--query-param-freq=0.1"}},
		},

		{
			name:       "fk_self_referencing",
			checkQuery: "select count(*) = 500 from t1 join t1 t1_2 on t1.id = t1_2.t1_id;",
			engines:    []string{"pg", "mysql"},
			cmds:       [][]string{[]string{"--rows=1000", "--table=t1", "--default-relationship=sequential"}},
		},

		// A tree of four levels over a tenth of the rows as roots: 100, 166,
		// 276 and 458 rows. Each level points at the one before only, so
		// every row sits exactly as deep as the level it was inserted at.
		{
			name: "fk_self_referencing_depth",
			// each row's depth from its ancestors, joined rather than walked
			// with a recursive CTE, which MySQL only has from 8.0
			checkQuery: `select count(*) = 1000
					and max(depth) = 3
					and sum(case when depth = 0 then 1 else 0 end) = 100
					and sum(case when depth = 1 then 1 else 0 end) = 166
					and sum(case when depth = 2 then 1 else 0 end) = 276
					and sum(case when depth = 3 then 1 else 0 end) = 458
				from (
					select case when t.t1_id is null then 0 when p1.t1_id is null then 1
						when p2.t1_id is null then 2 when p3.t1_id is null then 3 else 4 end as depth
					from t1 t
					left join t1 p1 on p1.id = t.t1_id
					left join t1 p2 on p2.id = p1.t1_id
					left join t1 p3 on p3.id = p2.t1_id
				) d;`,
			engines: []string{"pg", "mysql"},
			cmds:    [][]string{[]string{"--rows=1000", "--table=t1", "--self-fk-depth=4", "--self-fk-roots=0.1"}},
		},

		// The roots are the rows with a NULL parent, so the null_frac a dump
		// holds for the key column is their share on the source.
		{
			name:       "fk_self_referencing_stat",
			checkQuery: "select (count(*) = 1000) and (sum(case when t1_id is null then 1 else 0 end) = 200) from t1;",
			engines:    []string{"pg"},
			cmds:       [][]string{[]string{"--rows=1000", "--table=t1", "--stat-file=tests/pg/fk_self_referencing_stat.json"}},
		},

		// A table pointing at a self-referencing one has to see all of it. It
		// used to see the roots only, twice over: the copy inserting them
		// bears the table's name, so the child could be sorted right after
		// it, and the parent's size was counted once per run, the first time
		// a key asked for it, which was the self-referencing key itself half
		// way through.
		{
			name:       "fk_self_referencing_child",
			checkQuery: "select count(distinct t1_id) = 1000 from t2;",
			inputQuery: "select t2.id from t2 join t1 on t2.t1_id = t1.id",
			engines:    []string{"pg", "mysql"},
			cmds:       [][]string{[]string{"--rows=1000", "--sequential=t1=t2", "--null-freq=0"}},
		},

		// A table the query joins to itself has no foreign key saying so, and
		// the guessed one is a loop of its own: it used to leave the run with
		// no possible insert order, sorting the tables forever.
		{
			name:       "fk_virtual_self_referencing",
			checkQuery: "select count(*) = 500 from t1 join t1 t1_2 on t1.id = t1_2.t1_id;",
			inputQuery: "select a.id, b.t1_id from t1 a join t1 b on a.id = b.t1_id;",
			engines:    []string{"pg", "mysql"},
			cmds:       [][]string{[]string{"--rows=1000", "--default-relationship=sequential"}},
		},

		// A key can only point at a key, so the parent is t1 even though the
		// query names t2 first. Read as written, t1's own primary key was
		// sampled from t2's rows instead, and took their values.
		{
			name:       "fk_virtual_join_written_backwards",
			checkQuery: "select (count(*) = 100) and (max(id) <= 100) from t1;",
			inputQuery: "select t2.id from t2 join t1 on t2.t1_id = t1.id;",
			engines:    []string{"pg", "mysql"},
			cmds:       [][]string{[]string{"--rows=100", "--default-relationship=sequential"}},
		},

		// The same join, filled one table at a time. With --table=t2, t1 is
		// not part of the run, so whether its id is a key used to be unknown:
		// the guess stayed as written, landed on t1, and was dropped, leaving
		// t2.t1_id random. With --table=t1, the key side, nothing is added.
		{
			name:       "fk_virtual_join_written_backwards_table_per_table",
			checkQuery: "select ((select count(*) from t1) = 100) and (max(t1.id) <= 100) and (count(*) = 100) from t2 join t1 on t2.t1_id = t1.id;",
			inputQuery: "select t2.id from t2 join t1 on t2.t1_id = t1.id;",
			engines:    []string{"pg", "mysql"},
			cmds:       [][]string{[]string{"--rows=100", "--table=t1"}, []string{"--rows=100", "--table=t2", "--default-relationship=sequential", "--null-freq=0"}},
		},

		// The side a join names first means nothing, so the guess has to be
		// read the other way round when it would close a loop with a key the
		// schema already has. Read as written, t1 and t2 waited on each other
		// and the tables were sorted forever.
		{
			name:       "fk_virtual_reversed_join",
			checkQuery: "select count(*) = 100 from t2 join t1 on t2.t1_id = t1.id;",
			inputQuery: "select t2.id from t2 join t1 on t2.t1_id = t1.id;",
			engines:    []string{"pg", "mysql"},
			cmds:       [][]string{[]string{"--rows=100", "--default-relationship=sequential"}},
		},

		{
			name:       "virtual_col",
			checkQuery: "select count(*) = 100 from t1 where id = v",
			engines:    []string{"pg", "mysql"},
			cmds:       [][]string{[]string{"--rows=100", "--table=t1"}},
		},

		{
			// tests/pg/pg_stats.json is a dump as a real postgres would have
			// returned it: c1 seen with 60.5% of 42, 14.9% of 7 and 9.8% of
			// nulls, c2 with 30.2% of 'pg', 24.8% of a value holding a quote
			// and 24.9% of nulls, and c3 with no common value and no null at
			// all. Regenerating has to land back on those shares, c3 included:
			// a column postgres saw no null in must not fall back to
			// --null-freq.
			name: "pg_stats",
			checkQuery: `select (count(*) = 100000)
				AND (sum(CASE WHEN c1 = 42 THEN 1 ELSE 0 END) between 58500 and 62500)
				AND (sum(CASE WHEN c1 = 7 THEN 1 ELSE 0 END) between 13000 and 17000)
				AND (sum(CASE WHEN c1 IS NULL THEN 1 ELSE 0 END) between 8000 and 12000)
				AND (sum(CASE WHEN c2 = 'pg' THEN 1 ELSE 0 END) between 28200 and 32200)
				AND (sum(CASE WHEN c2 = 'it''s quoted' THEN 1 ELSE 0 END) between 22800 and 26800)
				AND (sum(CASE WHEN c2 IS NULL THEN 1 ELSE 0 END) between 22900 and 26900)
				AND (sum(CASE WHEN c3 IS NULL THEN 1 ELSE 0 END) = 0)
				from t1;`,
			engines: []string{"pg"},
			cmds:    [][]string{[]string{"--rows=100000", "--table=t1", "--stat-file=tests/pg/pg_stats.json"}},
		},
		// Every column of this key is a type the generator can write but the
		// sampler had no reader for, or had the wrong one: uuid and numeric
		// arrive as bytes, boolean as a bool, and a timestamp written back
		// with a Go-shaped layout is not the instant it was read. A key only
		// joins if all four round-trip exactly.
		{
			name:       "fk_key_types",
			checkQuery: "select count(*) = 100 from t1 join t2 on t1.id = t2.t1_id and t1.amount = t2.t1_amount and t1.flag = t2.t1_flag and t1.created_at = t2.t1_created_at;",
			engines:    []string{"pg", "mysql"},
			cmds:       [][]string{[]string{"--rows=100", "--table=t1"}, []string{"--rows=100", "--table=t2", "--default-relationship=sequential"}},
		},

		// A coin flip of the default 1% over a 50-row parent is expected to
		// bring back half a row, and brings back none often enough to be the
		// normal outcome. The guard rail used to measure --rows, the table
		// being filled, so a small parent never tripped it.
		{
			name:       "fk_binomial_small_parent",
			checkQuery: "select count(*) = 5000 from t2 join t1 on t1.id = t2.t1_id;",
			engines:    []string{"pg", "mysql"},
			cmds:       [][]string{[]string{"--rows=50", "--table=t1"}, []string{"--rows=5000", "--table=t2", "--default-relationship=binomial", "--bulk-size=1000"}},
		},

		// More children than the parent has rows. A sequential relationship
		// is 1-1 while the parent lasts and a round robin past that, so every
		// parent row is used and none of the children is left unfilled.
		{
			name:       "fk_sequential_fanout",
			checkQuery: "select (count(*) = 250) and (count(distinct t2.t1_id) = 100) from t2 join t1 on t1.id = t2.t1_id;",
			engines:    []string{"pg", "mysql"},
			cmds:       [][]string{[]string{"--rows=100", "--table=t1"}, []string{"--rows=250", "--table=t2", "--default-relationship=sequential"}},
		},

		// A column of a type nothing can generate is left out of the INSERT.
		// NOT NULL and with no default, that can only be rejected by the
		// engine, naming a column the user never mentioned, so it is refused
		// up front instead.
		{
			name:      "unsupported_type_not_null",
			engines:   []string{"pg"},
			cmds:      [][]string{[]string{"--rows=100", "--table=t1"}},
			expectErr: "no value can be generated for public.t1.source",
		},

		// Nullable, the same column is only worth a warning: the run works
		// and the column stays empty.
		{
			name:       "unsupported_type_nullable",
			checkQuery: "select (count(*) = 100) and (sum(CASE WHEN source IS NULL THEN 1 ELSE 0 END) = 100) and (sum(CASE WHEN note IS NOT NULL THEN 1 ELSE 0 END) = 100) from t1;",
			engines:    []string{"pg"},
			cmds:       [][]string{[]string{"--rows=100", "--table=t1"}},
		},

		// json documents are generated rather than left out: a document is
		// usually the widest thing a row holds, and a row missing it is not
		// the row being reproduced.
		{
			name:       "json",
			checkQuery: "select (count(*) = 100) and (sum(CASE WHEN doc->>'generated_by' = 'random-data-load' THEN 1 ELSE 0 END) = 100) from t1;",
			engines:    []string{"pg"},
			cmds:       [][]string{[]string{"--rows=100", "--table=t1"}},
		},
		{
			name:       "json",
			checkQuery: "select (count(*) = 100) and (sum(CASE WHEN json_unquote(json_extract(doc, '$.generated_by')) = 'random-data-load' THEN 1 ELSE 0 END) = 100) from t1;",
			engines:    []string{"mysql"},
			cmds:       [][]string{[]string{"--rows=100", "--table=t1"}},
		},

		// The query filters on status='cancelled' and the export measured that
		// value at 0.0398. Nothing is inserted because a query mentions it, so
		// the export is alone and the measurement is what comes out. Inserting
		// it at 10% instead, as the default used to, is a sequential scan where
		// the reported side had a bitmap scan.
		{
			name: "query_param_stat_file",
			checkQuery: `select (count(*) = 50000)
				and (sum(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) between 1700 and 2300)
				and (sum(CASE WHEN status = 'shipped' THEN 1 ELSE 0 END) between 30000 and 32000)
				from t1;`,
			inputQuery: "select id, status from t1 where status = 'cancelled'",
			engines:    []string{"pg"},
			cmds:       [][]string{[]string{"--rows=50000", "--table=t1", "--null-freq=0", "--stat-file=tests/pg/query_param_stat_file.json"}},
		},

		// The same run with --query-param-freq set. That is an instruction
		// rather than a measurement, so it overrides what the export says about
		// the value it names -- and only about that value: 'shipped' is left
		// where the export put it.
		{
			name: "query_param_stat_file",
			checkQuery: `select (count(*) = 50000)
				and (sum(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) between 12000 and 13000)
				and (sum(CASE WHEN status = 'shipped' THEN 1 ELSE 0 END) between 30000 and 32000)
				from t1;`,
			inputQuery: "select id, status from t1 where status = 'cancelled'",
			engines:    []string{"pg"},
			cmds:       [][]string{[]string{"--rows=50000", "--table=t1", "--null-freq=0", "--query-param-freq=0.25", "--stat-file=tests/pg/query_param_stat_file.json"}},
		},

		// Nothing pins the query's literals at the default, so the column holds
		// none of them and the query returns no row. The run says so rather
		// than leaving an empty result to be worked back from.
		{
			name:       "query_params",
			checkQuery: "select (count(*) = 20000) and (sum(CASE WHEN c2 in ('it', 'should', 'work') THEN 1 ELSE 0 END) = 0) from t1;",
			inputQuery: "select * from t1 where c2 in ('it', 'should', 'work')",
			engines:    []string{"pg", "mysql"},
			cmds:       [][]string{[]string{"--rows=20000", "--table=t1", "--null-freq=0"}},
		},

		// tests/pg/fk_skew.json is what a dump holds for a foreign key column:
		// most_common_vals full of the source database's own parent ids, which
		// mean nothing here, and most_common_freqs saying how skewed the join
		// key is, which is what a planner reads to size a hash join. The ids
		// must not be inserted -- 881271 is not an id of this t1 -- while the
		// shares must come out as measured: one parent row on 40% of the child
		// rows, another on 10%, the other 98 sharing what is left.
		{
			name: "fk_skew",
			checkQuery: `select (select count(*) from t2) = 50000
				and (select count(*) from t2 where t1_id = 881271) = 0
				and (select count(*) from (select count(*) c from t2 group by t1_id) s where c between 19000 and 21000) = 1
				and (select count(*) from (select count(*) c from t2 group by t1_id) s where c between 4500 and 5500) = 1;`,
			engines: []string{"pg"},
			cmds: [][]string{
				[]string{"--rows=100", "--table=t1"},
				[]string{"--rows=50000", "--table=t2", "--default-relationship=sequential", "--stat-file=tests/pg/fk_skew.json"},
			},
		},

		// Filling t3 alone needs t2, and filling t2 needs t1. Neither holds a
		// row, so the run cannot be done as asked and is refused before
		// anything is written. The whole closure has to be named in that one
		// message -- naming t2 alone would only send the caller round the same
		// loop again for t1 -- which is what checking for the deeper table
		// here is for.
		{
			name:      "fk_missing_parent",
			engines:   []string{"pg", "mysql"},
			cmds:      [][]string{[]string{"--rows=50", "--table=t3"}},
			expectErr: "t1 (pointed at by",
		},
		{
			name:       "fk_missing_parent",
			checkQuery: "select (select count(*) from t1) = 50 and (select count(*) from t2) = 50 and (select count(*) from t3) = 50;",
			engines:    []string{"pg", "mysql"},
			cmds:       [][]string{[]string{"--rows=50", "--table=t3", "--fill-fk-parents", "--default-relationship=sequential"}},
		},

		// A parent outside the run that already holds rows needs nothing:
		// filling a child against a dimension table an earlier run left behind
		// is ordinary, and reloading it would be the surprise.
		{
			name:       "fk_missing_parent",
			checkQuery: "select (select count(*) from t2) = 50 and (select count(*) from t3) = 50;",
			engines:    []string{"pg", "mysql"},
			cmds: [][]string{
				[]string{"--rows=50", "--table=t1"},
				[]string{"--rows=50", "--table=t2", "--default-relationship=sequential"},
				[]string{"--rows=50", "--table=t3", "--default-relationship=sequential"},
			},
		},

		// A two-column primary key whose columns come from two different
		// parents. Filled one key at a time, the pair repeats as soon as the
		// shorter walk comes round again -- over 50 and 100 parent rows, every
		// 100 rows -- and the primary key refuses it. Filled together, the
		// combinations stay unique, which the primary key itself is the check
		// for: 2000 rows landing at all means 2000 distinct pairs.
		{
			name:       "fk_composite_unique",
			checkQuery: "select count(*) = 2000 from t3;",
			engines:    []string{"pg", "mysql"},
			cmds: [][]string{
				[]string{"--rows=50", "--table=t1"},
				[]string{"--rows=100", "--table=t2"},
				[]string{"--rows=2000", "--table=t3", "--default-relationship=sequential"},
			},
		},

		// The same key asked for more rows than its parents can make
		// combinations. That cannot be done at all, so it is refused before
		// the first row of the child is written rather than part way through.
		{
			name:    "fk_composite_unique",
			engines: []string{"pg", "mysql"},
			cmds: [][]string{
				[]string{"--rows=50", "--table=t1"},
				[]string{"--rows=100", "--table=t2"},
				[]string{"--rows=6000", "--table=t3"},
			},
			expectErr: "can only be filled with 5000 different values",
		},

		// Row width decides how many rows fit in a page, page count decides
		// scan costs, and scan costs decide the plan, so a reproduction
		// usually has to hit a width. pg_column_size of a whole row is the
		// column data plus the 24-byte tuple header, so a run aimed at 180
		// bytes has to come back at 204.
		// The two flags are the same target seen from either end, so asking
		// for both is a contradiction rather than a refinement.
		{
			name:      "target_width",
			engines:   []string{"pg"},
			cmds:      [][]string{[]string{"--rows=20000", "--table=t1", "--null-freq=0", "--target-relpages=400", "--target-bytes-per-row=180"}},
			expectErr: "they are two ways of asking for the same thing",
		},
		{
			name:       "target_width",
			checkQuery: "select (count(*) = 20000) and (avg(pg_column_size(t1.*)) between 200 and 208) from t1;",
			engines:    []string{"pg"},
			cmds:       [][]string{[]string{"--rows=20000", "--table=t1", "--null-freq=0", "--target-bytes-per-row=180"}},
		},

		// The same target written as the page count it exists to reach. 20,000
		// rows cannot be spread over exactly 400 pages -- a page holds a whole
		// number of rows -- so the run lands on the nearest reachable count,
		// 409, at 136 bytes per row.
		{
			name:       "target_width",
			checkQuery: "select (count(*) = 20000) and (avg(pg_column_size(t1.*)) between 156 and 164) from t1;",
			engines:    []string{"pg"},
			cmds:       [][]string{[]string{"--rows=20000", "--table=t1", "--null-freq=0", "--target-relpages=400"}},
		},

		// verify reads the generated tables back and holds them against what
		// the run was given. The statistics export is the sharpest input it
		// takes: every null fraction and every common value's frequency in it
		// is a figure the generated table has to land on.
		//
		// The export covers t1 whole, so it also sizes the rows, with nothing
		// asked for on the command line: 4, 11 and 60 bytes per value, on the
		// 0.90, 0.75 and 1.00 of the rows that hold one, is 72 bytes of
		// columns, which is 96 with the tuple header on top.
		{
			name:       "pg_stats",
			checkQuery: "select (count(*) = 100000) and (avg(pg_column_size(t1.*)) between 92 and 100) from t1;",
			engines:    []string{"pg"},
			cmds:       [][]string{[]string{"--rows=100000", "--table=t1", "--stat-file=tests/pg/pg_stats.json"}},
			verify:     []string{"--table=t1", "--rows=100000", "--stat-file=tests/pg/pg_stats.json", "--tolerance=0.1"},
		},

		// The same export with c3 left out of it, which is what a
		// --query-scoped one looks like. Its columns add up to 12 bytes, which
		// is not a row of t1, and aiming at it would shrink c3 to nothing and
		// leave the table at 36 bytes a row, header included. The width is
		// left alone instead, so c3 is generated at its natural length and the
		// row stays well clear of that.
		{
			name:       "pg_stats",
			checkQuery: "select (count(*) = 100000) and (avg(pg_column_size(t1.*)) > 45) from t1;",
			engines:    []string{"pg"},
			cmds:       [][]string{[]string{"--rows=100000", "--table=t1", "--stat-file=tests/pg/pg_stats_partial.json"}},
			// and verify does not hold the table against that partial sum
			// either: it says which column is missing from the export and
			// leaves the width without a target, rather than calling a
			// correct table too wide
			verify: []string{"--table=t1", "--rows=100000", "--stat-file=tests/pg/pg_stats_partial.json", "--tolerance=0.1"},
		},

		// The same pass over a foreign key relationship, on both engines: row
		// counts, page counts and the catalog's own row estimate all have to
		// be readable, which is what needs an engine behind them.
		{
			name:    "fk_uniform",
			engines: []string{"pg", "mysql"},
			cmds:    [][]string{[]string{"--rows=100", "--table=t1"}, []string{"--rows=100", "--table=t2", "--default-relationship=sequential"}},
			verify:  []string{"--rows=100;t1=100;t2=100"},
			// no --table and no --query, so the tables come from --rows
		},

		// A value pinned by hand that the query also filters on. The two used
		// to be registered separately and drawn independently, so the
		// selectivity asked for, 0.28, came out at 0.28 + 0.1*(1-0.28).
		{
			name:       "values_freq_map_query_overlap",
			checkQuery: "select (count(*) = 20000) AND (sum(CASE WHEN c2 = 'DHL' THEN 1 ELSE 0 END) between 5300 and 5900) from t1;",
			inputQuery: "select * from t1 where c2 = 'DHL'",
			engines:    []string{"pg", "mysql"},
			cmds:       [][]string{[]string{"--rows=20000", "--table=t1", "--null-freq=0", "--query-param-freq=0.1", "--values-freq-map=t1.c2=DHL:0.28"}},
		},
	}

	for _, test := range tests {
		for _, engine := range test.engines {
			// One subtest per case and engine, so a failure stops that case
			// only, and one case can be picked: go test -run 'TestRun/fk_pareto'
			t.Run(test.name+"/"+engine, func(t *testing.T) {
				errlog := fmt.Sprintf("engine: %s, container: %s, testname: %s", engine, testsdb[engine].resource.Container.Name, test.name)
				if !keepDB() {
					errlog = fmt.Sprintf("to repeat the test and keep the container running, use KEEP_DB=1 go test .\n%s", errlog)
				}

				switch engine {
				case "mysql":
					errlog += fmt.Sprintf("\ndocker exec -it %s mysql -u dockertest -pdockertest test", testsdb[engine].resource.Container.Name)
				case "pg":
					errlog += fmt.Sprintf("\ndocker exec -it %s bash -c 'PGPASSWORD=dockertest psql -U dockertest test'", testsdb[engine].resource.Container.Name)
				}
				errlog += "\n"

				if err := ddl(engine, "reset"); err != nil {
					t.Fatalf("%sfailed to reset table schema: %v", errlog, err)
				}
				if err := ddl(engine, test.name); err != nil {
					t.Fatalf("%sfailed to apply test ddl: %v", errlog, err)
				}

				// calling tool with args directly
				for i, cmd := range test.cmds {
					args := []string{"run", "--engine=" + engine, "--host=127.0.0.1", "--user=dockertest", "--password=dockertest", "--database=test", "--port=" + testsdb[engine].port}
					args = append(args, cmd...)

					if test.inputQuery != "" {
						args = append(args, "--query="+test.inputQuery)
					}
					errlog += toolExecutable + " " + strings.Join(args, " ") + "\n"

					// Only the last run is the one expected to fail: a refusal
					// often needs the tables it refuses over to be filled first,
					// and those runs have to succeed like any other.
					expectErr := ""
					if i == len(test.cmds)-1 {
						expectErr = test.expectErr
					}

					out, err := exec.Command(toolExecutable, args...).CombinedOutput()
					if expectErr != "" {
						// The run has to refuse the job rather than insert
						// nothing and report success: a script checking $? has
						// no other way to know.
						if err == nil {
							t.Fatalf("%sexpected %s to fail with %q, it succeeded. out: %s", errlog, toolExecutable, expectErr, out)
						}
						if !strings.Contains(string(out), expectErr) {
							t.Fatalf("%sexpected the failure to mention %q, out: %s", errlog, expectErr, out)
						}
						continue
					}
					if err != nil {
						t.Fatalf("%sfailed to exec %s: %v, out: %s", errlog, toolExecutable, err, out)
					}
				}

				// Whatever the check query asserts, the tool's own reading of the
				// generated tables has to agree with what the run was asked for.
				if len(test.verify) > 0 {
					args := []string{"verify", "--engine=" + engine, "--host=127.0.0.1", "--user=dockertest", "--password=dockertest", "--database=test", "--port=" + testsdb[engine].port, "--strict"}
					args = append(args, test.verify...)
					errlog += toolExecutable + " " + strings.Join(args, " ") + "\n"

					out, err := exec.Command(toolExecutable, args...).CombinedOutput()
					if err != nil {
						t.Fatalf("%sverify refused what the run generated: %v, out: %s", errlog, err, out)
					}
				}

				if test.checkQuery == "" {
					return
				}

				row := testsdb[engine].db.QueryRow(test.checkQuery)
				var ok bool
				err := row.Scan(&ok)
				if err != nil {
					t.Fatalf("%sfailed to query check sql: %v", errlog, err)
				}
				if !ok {
					t.Fatalf("%ssql check returned false, query:\n%s", errlog, test.checkQuery)
				}
			})
		}
	}
}

func ddl(engine, name string) error {
	ddl, err := os.ReadFile(fmt.Sprintf("tests/%s/%s", engine, name))
	if err != nil {
		return fmt.Errorf("failed to read %s testcase %s: %v", engine, name, err)
	}

	// loading table schema
	_, err = testsdb[engine].db.Exec(string(ddl))
	if err != nil {
		return fmt.Errorf("failed to exec %s ddl for testname %s: %v", engine, name, err)
	}
	return nil
}

func keepDB() bool {
	return os.Getenv("KEEP_DB") == "1"
}