vitess.io/vitess@v0.16.2/go/vt/vttablet/tabletserver/planbuilder/testdata/exec_cases.txt

# union
"select * from a union select * from b"
{
  "PlanID": "Select",
  "TableName": "",
  "Permissions": [
    {
      "TableName": "a",
      "Role": 0
    },
    {
      "TableName": "b",
      "Role": 0
    }
  ],
  "FullQuery": "select * from a union select * from b limit :#maxLimit"
}

# union with limit
"select * from a union select * from b limit 10"
{
  "PlanID": "Select",
  "TableName": "",
  "Permissions": [
    {
      "TableName": "a",
      "Role": 0
    },
    {
      "TableName": "b",
      "Role": 0
    }
  ],
  "FullQuery": "select * from a union select * from b limit 10"
}

# with no where clause
"select * from a"
{
  "PlanID": "Select",
  "TableName": "a",
  "Permissions": [
    {
      "TableName": "a",
      "Role": 0
    }
  ],
  "FullQuery": "select * from a limit :#maxLimit"
}

# select with a regular where clause
"select * from a where id=1"
{
  "PlanID": "Select",
  "TableName": "a",
  "Permissions": [
    {
      "TableName": "a",
      "Role": 0
    }
  ],
  "FullQuery": "select * from a where id = 1 limit :#maxLimit"
}

# select with limit
"select * from a limit 5"
{
  "PlanID": "Select",
  "TableName": "a",
  "Permissions": [
    {
      "TableName": "a",
      "Role": 0
    }
  ],
  "FullQuery": "select * from a limit 5"
}

# limit with offset arg
"select * from a limit 10, 5"
{
  "PlanID": "Select",
  "TableName": "a",
  "Permissions": [
    {
      "TableName": "a",
      "Role": 0
    }
  ],
  "FullQuery": "select * from a limit 10, 5"
}

# select impossible
"select * from a where 1 != 1"
{
  "PlanID": "SelectImpossible",
  "TableName": "a",
  "Permissions": [
    {
      "TableName": "a",
      "Role": 0
    }
  ],
  "FullQuery": "select * from a where 1 != 1 limit :#maxLimit"
}

# bind in select list
"select :bv from a"
{
  "PlanID": "Select",
  "TableName": "a",
  "Permissions": [
    {
      "TableName": "a",
      "Role": 0
    }
  ],
  "FullQuery": "select :bv from a limit :#maxLimit"
}

# bind in select list and impossible
"select :bv from a where 1 != 1"
{
  "PlanID": "SelectImpossible",
  "TableName": "a",
  "Permissions": [
    {
      "TableName": "a",
      "Role": 0
    }
  ],
  "FullQuery": "select :bv from a where 1 != 1 limit :#maxLimit"
}

# single value sequence
"select next value from seq"
{
  "PlanID": "Nextval",
  "TableName": "seq",
  "Permissions": [
    {
      "TableName": "seq",
      "Role": 0
    }
  ],
  "NextCount": "INT64(1)"
}

# sequence with number
"select next 10 values from seq"
{
  "PlanID": "Nextval",
  "TableName": "seq",
  "Permissions": [
    {
      "TableName": "seq",
      "Role": 0
    }
  ],
  "NextCount": "INT64(10)"
}


# sequence with bindvar
"select next :a values from seq"
{
  "PlanID": "Nextval",
  "TableName": "seq",
  "Permissions": [
    {
      "TableName": "seq",
      "Role": 0
    }
  ],
  "NextCount": ":a"
}

# squence with bad value
"select next 12345667852342342342323423423 values from seq"
{
  "PlanID": "Nextval",
  "TableName": "seq",
  "Permissions": [
    {
      "TableName": "seq",
      "Role": 0
    }
  ],
  "NextCount": "DECIMAL(12345667852342342342323423423)"
}

# nextval on non-sequence table
"select next value from a"
"a is not a sequence"

# nextval on non-existent table
"select next value from id"
"id is not a sequence"

# for update
"select eid from a for update"
{
  "PlanID": "Select",
  "TableName": "a",
  "Permissions": [
    {
      "TableName": "a",
      "Role": 0
    }
  ],
  "FullQuery": "select eid from a limit :#maxLimit for update"
}

# lock in share mode
"select eid from a lock in share mode"
{
  "PlanID": "Select",
  "TableName": "a",
  "Permissions": [
    {
      "TableName": "a",
      "Role": 0
    }
  ],
  "FullQuery": "select eid from a limit :#maxLimit lock in share mode"
}

# normal insert
"insert into a(eid, id) values (1, 2)"
{
  "PlanID": "Insert",
  "TableName": "a",
  "Permissions": [
    {
      "TableName": "a",
      "Role": 1
    }
  ],
  "FullQuery": "insert into a(eid, id) values (1, 2)"
}

# insert cross-db
"insert into b.a (eid, id) values (1, 2)"
{
  "PlanID": "Insert",
  "TableName": "",
  "Permissions": [
    {
      "TableName": "a",
      "Role": 1
    }
  ],
  "FullQuery": "insert into b.a(eid, id) values (1, 2)"
}

# insert with bind value
"insert into a (eid, id) values (1, :a)"
{
  "PlanID": "Insert",
  "TableName": "a",
  "Permissions": [
    {
      "TableName": "a",
      "Role": 1
    }
  ],
  "FullQuery": "insert into a(eid, id) values (1, :a)"
}

# insert with subquery
"insert into b (eid, id) select * from a"
{
  "PlanID": "Insert",
  "TableName": "b",
  "Permissions": [
    {
      "TableName": "b",
      "Role": 1
    },
    {
      "TableName": "a",
      "Role": 0
    }
  ],
  "FullQuery": "insert into b(eid, id) select * from a"
}

# upsert
"insert into a (eid, id) values (1, 2) on duplicate key update name = func(a)"
{
  "PlanID": "Insert",
  "TableName": "a",
  "Permissions": [
    {
      "TableName": "a",
      "Role": 1
    }
  ],
  "FullQuery": "insert into a(eid, id) values (1, 2) on duplicate key update `name` = func(a)"
}

# replace
"replace into b (eid, id) values (1, 2), (3, 4)"
{
  "PlanID": "Insert",
  "TableName": "b",
  "Permissions": [
    {
      "TableName": "b",
      "Role": 1
    }
  ],
  "FullQuery": "replace into b(eid, id) values (1, 2), (3, 4)"
}

# update with no where clause
"update d set foo='foo'"
{
  "PlanID": "UpdateLimit",
  "TableName": "d",
  "Permissions": [
    {
      "TableName": "d",
      "Role": 1
    }
  ],
  "FullQuery": "update d set foo = 'foo' limit :#maxLimit"
}

# normal update
"update d set foo='foo' where name in ('a', 'b')"
{
  "PlanID": "UpdateLimit",
  "TableName": "d",
  "Permissions": [
    {
      "TableName": "d",
      "Role": 1
    }
  ],
  "FullQuery": "update d set foo = 'foo' where `name` in ('a', 'b') limit :#maxLimit",
  "WhereClause": "where `name` in ('a', 'b')"
}

# normal update
options:PassthroughDMLs
"update d set foo='foo' where name in ('a', 'b')"
{
  "PlanID": "Update",
  "TableName": "d",
  "Permissions": [
    {
      "TableName": "d",
      "Role": 1
    }
  ],
  "FullQuery": "update d set foo = 'foo' where `name` in ('a', 'b')",
  "WhereClause": "where `name` in ('a', 'b')"
}

# cross-db update
"update a.b set foo='foo' where name in ('a', 'b')"
{
  "PlanID": "Update",
  "TableName": "",
  "Permissions": [
    {
      "TableName": "b",
      "Role": 1
    }
  ],
  "FullQuery": "update a.b set foo = 'foo' where `name` in ('a', 'b')",
  "WhereClause": "where `name` in ('a', 'b')"
}

# update unknown table
"update bogus set name='foo' where id=1"
{
  "PlanID": "Update",
  "TableName": "",
  "Permissions": [
    {
      "TableName": "bogus",
      "Role": 1
    }
  ],
  "FullQuery": "update bogus set `name` = 'foo' where id = 1",
  "WhereClause": "where id = 1"
}

# update unknown table
options:PassthroughDMLs
"update bogus set name='foo' where id=1"
{
  "PlanID": "Update",
  "TableName": "",
  "Permissions": [
    {
      "TableName": "bogus",
      "Role": 1
    }
  ],
  "FullQuery": "update bogus set `name` = 'foo' where id = 1",
  "WhereClause": "where id = 1"
}

# multi-table update
"update a, b set a.name = 'foo' where a.id = b.id and b.var = 'test'"
{
  "PlanID": "Update",
  "TableName": "",
  "Permissions": [
    {
      "TableName": "a",
      "Role": 1
    },
    {
      "TableName": "b",
      "Role": 1
    }
  ],
  "FullQuery": "update a, b set a.`name` = 'foo' where a.id = b.id and b.var = 'test'",
  "WhereClause": "where a.id = b.id and b.var = 'test'"
}

# multi-table update
options:PassthroughDMLs
"update a join b on a.id = b.id set a.name = 'foo' where b.var = 'test'"
{
  "PlanID": "Update",
  "TableName": "",
  "Permissions": [
    {
      "TableName": "a",
      "Role": 1
    },
    {
      "TableName": "b",
      "Role": 1
    }
  ],
  "FullQuery": "update a join b on a.id = b.id set a.`name` = 'foo' where b.var = 'test'",
  "WhereClause": "where b.var = 'test'"
}


# update with limit
"update a set name='foo' limit 1"
{
  "PlanID": "Update",
  "TableName": "a",
  "Permissions": [
    {
      "TableName": "a",
      "Role": 1
    }
  ],
  "FullQuery": "update a set `name` = 'foo' limit 1"
}

# update with limit
options:PassthroughDMLs
"update a set name='foo' limit 1"
{
  "PlanID": "Update",
  "TableName": "a",
  "Permissions": [
    {
      "TableName": "a",
      "Role": 1
    }
  ],
  "FullQuery": "update a set `name` = 'foo' limit 1"
}

# delete with no where clause
"delete from a"
{
  "PlanID": "DeleteLimit",
  "TableName": "a",
  "Permissions": [
    {
      "TableName": "a",
      "Role": 1
    }
  ],
  "FullQuery": "delete from a limit :#maxLimit"
}

# normal delete
"delete from d where name in ('a', 'b')"
{
  "PlanID": "DeleteLimit",
  "TableName": "d",
  "Permissions": [
    {
      "TableName": "d",
      "Role": 1
    }
  ],
  "FullQuery": "delete from d where `name` in ('a', 'b') limit :#maxLimit",
  "WhereClause": "where `name` in ('a', 'b')"
}

# normal delete
options:PassthroughDMLs
"delete from d where name in ('a', 'b')"
{
  "PlanID": "Delete",
  "TableName": "d",
  "Permissions": [
    {
      "TableName": "d",
      "Role": 1
    }
  ],
  "FullQuery": "delete from d where `name` in ('a', 'b')",
  "WhereClause": "where `name` in ('a', 'b')"
}

# delete unknown table
"delete from bogus"
{
  "PlanID": "Delete",
  "TableName": "",
  "Permissions": [
    {
      "TableName": "bogus",
      "Role": 1
    }
  ],
  "FullQuery": "delete from bogus"
}

# delete unknown table
options:PassthroughDMLs
"delete from bogus"
{
  "PlanID": "Delete",
  "TableName": "",
  "Permissions": [
    {
      "TableName": "bogus",
      "Role": 1
    }
  ],
  "FullQuery": "delete from bogus"
}

# multi-table delete
"delete a, b from a, b where id = 1"
{
  "PlanID": "Delete",
  "TableName": "",
  "Permissions": [
    {
      "TableName": "a",
      "Role": 1
    },
    {
      "TableName": "b",
      "Role": 1
    }
  ],
  "FullQuery": "delete a, b from a, b where id = 1",
  "WhereClause": "where id = 1"
}


# delete with limit
"delete from a limit 10"
{
  "PlanID": "Delete",
  "TableName": "a",
  "Permissions": [
    {
      "TableName": "a",
      "Role": 1
    }
  ],
  "FullQuery": "delete from a limit 10"
}

# delete with limit
options:PassthroughDMLs
"delete from a limit 10"
{
  "PlanID": "Delete",
  "TableName": "a",
  "Permissions": [
    {
      "TableName": "a",
      "Role": 1
    }
  ],
  "FullQuery": "delete from a limit 10"
}

# create
"create table a(a int, b varchar(8))"
{
  "PlanID": "DDL",
  "TableName": "",
  "Permissions": [
    {
      "TableName": "a",
      "Role": 2
    }
  ],
  "FullQuery": "create table a (\n\ta int,\n\tb varchar(8)\n)"
}

# create index
"create index a on b(id)"
{
  "PlanID": "DDL",
  "TableName": "",
  "Permissions": [
    {
      "TableName": "b",
      "Role": 2
    }
  ],
  "FullQuery":"alter table b add index a (id)"
}

# create view
"create view a as select * from b"
{
  "PlanID": "DDL",
  "TableName": "",
  "Permissions": [
    {
      "TableName": "a",
      "Role": 2
    }
  ],
  "FullQuery":"create view a as select * from b"
}

# alter
"alter table a add column(a int)"
{
  "PlanID": "DDL",
  "TableName": "",
  "Permissions": [
    {
      "TableName": "a",
      "Role": 2
    }
  ],
  "FullQuery":"alter table a add column a int"
}

# alter rename
"alter table a rename b"
{
  "PlanID": "DDL",
  "TableName": "",
  "Permissions": [
    {
      "TableName": "a",
      "Role": 2
    },
    {
      "TableName": "b",
      "Role": 2
    }
  ],
  "FullQuery":"alter table a rename b"
}

# rename
"rename table a to b"
{
  "PlanID": "DDL",
  "TableName": "",
  "Permissions": [
    {
      "TableName": "a",
      "Role": 2
    },
    {
      "TableName": "b",
      "Role": 2
    }
  ],
  "FullQuery":"rename table a to b"
}

# multi-rename
"rename table a to b, b to a"
{
  "PlanID": "DDL",
  "TableName": "",
  "Permissions": [
    {
      "TableName": "a",
      "Role": 2
    },
    {
      "TableName": "b",
      "Role": 2
    },
    {
      "TableName": "b",
      "Role": 2
    },
    {
      "TableName": "a",
      "Role": 2
    }
  ],
  "FullQuery":"rename table a to b, b to a"
}

# drop
"drop table a"
{
  "PlanID": "DDL",
  "TableName": "",
  "Permissions": [
    {
      "TableName": "a",
      "Role": 2
    }
  ],
  "FullQuery": "drop table a"
}

# drop
"truncate table a"
{
  "PlanID": "DDL",
  "TableName": "",
  "Permissions": [
    {
      "TableName": "a",
      "Role": 2
    }
  ],
  "FullQuery": "truncate table a"
}

# multi-drop
"drop table a, b"
{
  "PlanID": "DDL",
  "TableName": "",
  "Permissions": [
    {
      "TableName": "a",
      "Role": 2
    },
    {
      "TableName": "b",
      "Role": 2
    }
  ],
  "FullQuery": "drop table a, b"
}

# analyze
"analyze table a"
{
  "PlanID": "OtherRead",
  "TableName": ""
}

# show
"show a"
{
  "PlanID": "OtherRead",
  "TableName": ""
}

# describe
"describe a"
{
  "PlanID": "OtherRead",
  "TableName": ""
}

# explain
"explain a"
{
  "PlanID": "OtherRead",
  "TableName": ""
}

# repair
"repair a"
{
  "PlanID": "OtherAdmin",
  "TableName": ""
}

# optimize
"optimize a"
{
  "PlanID": "OtherAdmin",
  "TableName": ""
}

# syntax error
"syntax error"
"syntax error at position 7 near 'syntax'"

# show tables #1
"show tables like 'key%'"
{
  "PlanID": "Show",
  "TableName":"",
  "FullQuery": "show tables like 'key%'"
}

# show tables #2
"show tables where Tables_in_keyspace='apa'"
{
  "PlanID": "Show",
  "TableName":"",
  "FullQuery": "show tables where Tables_in_dbName = 'apa'"
}

# show table status #1
"show table status like 'key%'"
{
  "PlanID": "Show",
  "TableName":"",
  "FullQuery": "show table status like 'key%'"
}

# show table status #2
"show table status where Name='apa'"
{
  "PlanID": "Show",
  "TableName":"",
  "FullQuery": "show table status where `Name` = 'apa'"
}

# show create table
"show create table t1"
{
  "PlanID": "Show",
  "TableName": "",
  "FullQuery": "show create table t1"
}

# show create database system_schema
"show create database mysql"
{
  "PlanID": "Show",
  "TableName": "",
  "FullQuery": "show create database mysql"
}

# show create database
"show create database anything"
{
  "PlanID": "Show",
  "TableName": "",
  "FullQuery": "show create database dbName"
}

# load data
"load data infile 'x.txt' into table a"
{
    "PlanID":"Load",
    "TableName":""
}

# alter view
"alter view a as select * from b"
{
  "PlanID": "DDL",
  "TableName": "",
  "Permissions": [
    {
      "TableName": "a",
      "Role": 2
    }
  ],
  "FullQuery":"alter view a as select * from b"
}

# drop view
"drop view a"
{
  "PlanID": "DDL",
  "TableName": "",
  "Permissions": [
    {
      "TableName": "a",
      "Role": 2
    }
  ],
  "FullQuery":"drop view a"
}

# multi-drop views
"drop view a, b"
{
  "PlanID": "DDL",
  "TableName": "",
  "Permissions": [
    {
      "TableName": "a",
      "Role": 2
    },
    {
      "TableName": "b",
      "Role": 2
    }
  ],
  "FullQuery":"drop view a, b"
}

# flush statement
"flush tables a,b"
{
  "PlanID": "Flush",
  "TableName": "",
  "Permissions": [
    {
      "TableName": "a",
      "Role": 2
    },
    {
      "TableName": "b",
      "Role": 2
    }
  ],
  "FullQuery": "flush tables a, b"
}

# call proc
"call getAllTheThings()"
{
  "PlanID": "CallProcedure",
  "TableName": "",
  "FullQuery": "call getAllTheThings()"
}

# create table with function as a default value
"create table function_default (x varchar(25) DEFAULT (TRIM(' check ')))"
{
  "PlanID": "DDL",
  "TableName": "",
  "Permissions": [
    {
      "TableName": "function_default",
      "Role": 2
    }
  ],
  "FullQuery": "create table function_default (\n\tx varchar(25) default (trim(' check '))\n)"
}

# temporary table
"create temporary table temp(a int)"
{
  "PlanID": "DDL",
  "TableName": "",
  "Permissions": [
    {
      "TableName": "temp",
      "Role": 2
    }
  ],
  "FullQuery": "create temporary table temp (\n\ta int\n)",
  "NeedsReservedConn": true
}