// TestCacheCases2 covers cases for vitess_cached2.
func TestCacheCases2(t *testing.T) {
client := framework.NewClient()
testCases := []framework.Testable{
framework.TestQuery("alter table vitess_cached2 comment 'new'"),
&framework.TestCase{
Name: "PK_IN (null key)",
Query: "select * from vitess_cached2 where eid = 2 and bid = :bid",
BindVars: map[string]interface{}{
"bid": nil,
},
Rewritten: []string{
"select * from vitess_cached2 where 1 != 1",
"select eid, bid, name, foo from vitess_cached2 where (eid = 2 and bid = null)",
},
Plan: "PK_IN",
Table: "vitess_cached2",
Absent: 1,
},
// (2.foo) is in cache
&framework.TestCase{
Name: "PK_IN (empty cache)",
Query: "select * from vitess_cached2 where eid = 2 and bid = 'foo'",
Result: [][]string{
{"2", "foo", "abcd2", "efgh"},
},
Rewritten: []string{
"select * from vitess_cached2 where 1 != 1",
"select eid, bid, name, foo from vitess_cached2 where (eid = 2 and bid = 'foo')",
},
RowsAffected: 1,
Plan: "PK_IN",
Table: "vitess_cached2",
Misses: 1,
},
// (2.foo)
&framework.TestCase{
Name: "PK_IN, use cache",
Query: "select bid, eid, name, foo from vitess_cached2 where eid = 2 and bid = 'foo'",
Result: [][]string{
{"foo", "2", "abcd2", "efgh"},
},
Rewritten: []string{
"select bid, eid, name, foo from vitess_cached2 where 1 != 1",
},
RowsAffected: 1,
Plan: "PK_IN",
Table: "vitess_cached2",
Hits: 1,
},
// (2.foo)
&framework.TestCase{
Name: "PK_IN, absent",
Query: "select bid, eid, name, foo from vitess_cached2 where eid = 3 and bid = 'foo'",
Rewritten: []string{
"select bid, eid, name, foo from vitess_cached2 where 1 != 1",
"select eid, bid, name, foo from vitess_cached2 where (eid = 3 and bid = 'foo')",
},
Plan: "PK_IN",
Table: "vitess_cached2",
Absent: 1,
},
// (1.foo, 2.foo)
&framework.TestCase{
Name: "out of order columns list",
Query: "select bid, eid from vitess_cached2 where eid = 1 and bid = 'foo'",
Result: [][]string{
{"foo", "1"},
},
Rewritten: []string{
"select bid, eid from vitess_cached2 where 1 != 1",
"select eid, bid, name, foo from vitess_cached2 where (eid = 1 and bid = 'foo')",
},
RowsAffected: 1,
Table: "vitess_cached2",
Misses: 1,
},
// (1.foo, 2.foo)
&framework.TestCase{
Name: "out of order columns list, use cache",
Query: "select bid, eid from vitess_cached2 where eid = 1 and bid = 'foo'",
Result: [][]string{
{"foo", "1"},
},
RowsAffected: 1,
Table: "vitess_cached2",
Hits: 1,
},
// (1.foo, 1.bar, 2.foo)
&framework.TestCase{
Name: "pk_in for composite pk table, two fetches from db (absent)",
Query: "select eid, bid, name, foo from vitess_cached2 where eid = 1 and bid in('absent1', 'absent2')",
Rewritten: []string{
"select eid, bid, name, foo from vitess_cached2 where 1 != 1",
"select eid, bid, name, foo from vitess_cached2 where (eid = 1 and bid = 'absent1') or (eid = 1 and bid = 'absent2')",
},
Plan: "PK_IN",
Table: "vitess_cached2",
Absent: 2,
},
// (1.foo, 1.bar, 2.foo)
&framework.TestCase{
Name: "pk_in for composite pk table, 1 fetch from db",
Query: "select eid, bid, name, foo from vitess_cached2 where eid = 1 and bid in('foo', 'bar')",
Result: [][]string{
{"1", "foo", "abcd1", "efgh"},
{"1", "bar", "abcd1", "efgh"},
},
Rewritten: []string{
"select eid, bid, name, foo from vitess_cached2 where 1 != 1",
"select eid, bid, name, foo from vitess_cached2 where (eid = 1 and bid = 'bar')",
},
RowsAffected: 2,
Plan: "PK_IN",
Table: "vitess_cached2",
Hits: 1,
Misses: 1,
},
// (1.foo, 1.bar, 2.foo)
&framework.TestCase{
Name: "pk_in for composite pk table, 0 fetch from db",
Query: "select eid, bid, name, foo from vitess_cached2 where eid = 1 and bid in('foo', 'bar')",
Result: [][]string{
{"1", "foo", "abcd1", "efgh"},
{"1", "bar", "abcd1", "efgh"},
},
RowsAffected: 2,
Plan: "PK_IN",
Table: "vitess_cached2",
Hits: 2,
},
// (1.foo, 1.bar, 2.foo, 2.bar)
&framework.TestCase{
Name: "select_subquery for composite pk table, 1 fetch from db",
Query: "select eid, bid, name, foo from vitess_cached2 where eid = 2 and name='abcd2'",
Result: [][]string{
{"2", "foo", "abcd2", "efgh"},
{"2", "bar", "abcd2", "efgh"},
},
Rewritten: []string{
"select eid, bid, name, foo from vitess_cached2 where 1 != 1",
"select eid, bid from vitess_cached2 use index (aname2) where eid = 2 and name = 'abcd2' limit 10001",
"select eid, bid, name, foo from vitess_cached2 where (eid = 2 and bid = 'bar')",
},
RowsAffected: 2,
Plan: "SELECT_SUBQUERY",
Table: "vitess_cached2",
Hits: 1,
Misses: 1,
},
// (1.foo, 1.bar, 2.foo, 2.bar)
&framework.TestCase{
Name: "verify 1.bar is in cache",
Query: "select bid, eid from vitess_cached2 where eid = 1 and bid = 'bar'",
Result: [][]string{
{"bar", "1"},
},
Rewritten: []string{
"select bid, eid from vitess_cached2 where 1 != 1",
},
RowsAffected: 1,
Table: "vitess_cached2",
Hits: 1,
},
// (1.foo, 1.bar, 2.foo, 2.bar)
&framework.MultiCase{
Name: "update",
Cases: []framework.Testable{
framework.TestQuery("begin"),
framework.TestQuery("update vitess_cached2 set foo='fghi' where bid = 'bar'"),
&framework.TestCase{
Query: "commit",
Table: "vitess_cached2",
Invalidations: 2,
},
&framework.TestCase{
Query: "select * from vitess_cached2 where eid = 1 and bid = 'bar'",
Result: [][]string{
{"1", "bar", "abcd1", "fghi"},
},
Rewritten: []string{
"select * from vitess_cached2 where 1 != 1",
"select eid, bid, name, foo from vitess_cached2 where (eid = 1 and bid = 'bar')",
},
RowsAffected: 1,
Table: "vitess_cached2",
Misses: 1,
},
},
},
// (1.foo, 1.bar, 2.foo, 2.bar)
&framework.MultiCase{
Name: "this will not invalidate the cache",
Cases: []framework.Testable{
framework.TestQuery("begin"),
framework.TestQuery("update vitess_cached2 set foo='fghi' where bid = 'bar'"),
framework.TestQuery("rollback"),
&framework.TestCase{
Query: "select * from vitess_cached2 where eid = 1 and bid = 'bar'",
Result: [][]string{
{"1", "bar", "abcd1", "fghi"},
},
RowsAffected: 1,
Table: "vitess_cached2",
Hits: 1,
},
},
},
// (1.foo, 1.bar, 2.foo, 2.bar)
&framework.MultiCase{
Name: "upsert should invalidate rowcache",
Cases: []framework.Testable{
&framework.TestCase{
Query: "select * from vitess_cached2 where eid = 1 and bid = 'bar'",
Result: [][]string{
{"1", "bar", "abcd1", "fghi"},
},
RowsAffected: 1,
Table: "vitess_cached2",
Hits: 1,
},
framework.TestQuery("begin"),
&framework.TestCase{
Query: "insert into vitess_cached2 values(1, 'bar', 'abcd1', 'fghi') on duplicate key update foo='fghi'",
Rewritten: []string{
"insert into vitess_cached2 values (1, 'bar', 'abcd1', 'fghi') /* _stream vitess_cached2 (eid bid ) (1 'YmFy' )",
"update vitess_cached2 set foo = 'fghi' where (eid = 1 and bid = 'bar') /* _stream vitess_cached2 (eid bid ) (1 'YmFy' )",
},
Table: "vitess_cached2",
},
framework.TestQuery("commit"),
&framework.TestCase{
Query: "select * from vitess_cached2 where eid = 1 and bid = 'bar'",
Result: [][]string{
{"1", "bar", "abcd1", "fghi"},
},
Rewritten: []string{
"select eid, bid, name, foo from vitess_cached2 where (eid = 1 and bid = 'bar')",
},
RowsAffected: 1,
Table: "vitess_cached2",
Misses: 1,
},
},
},
// (1.foo, 2.foo, 2.bar)
&framework.MultiCase{
Name: "delete",
Cases: []framework.Testable{
framework.TestQuery("begin"),
framework.TestQuery("delete from vitess_cached2 where eid = 1 and bid = 'bar'"),
&framework.TestCase{
Query: "commit",
Table: "vitess_cached2",
Invalidations: 1,
},
&framework.TestCase{
Query: "select * from vitess_cached2 where eid = 1 and bid = 'bar'",
Rewritten: []string{
"select eid, bid, name, foo from vitess_cached2 where (eid = 1 and bid = 'bar')",
},
Table: "vitess_cached2",
Absent: 1,
},
framework.TestQuery("begin"),
framework.TestQuery("insert into vitess_cached2(eid, bid, name, foo) values (1, 'bar', 'abcd1', 'efgh')"),
&framework.TestCase{
Query: "commit",
Table: "vitess_cached2",
},
},
},
// (1.foo, 2.foo, 2.bar)
&framework.TestCase{
Name: "Verify 1.foo is in cache",
Query: "select * from vitess_cached2 where eid = 1 and bid = 'foo'",
Result: [][]string{
{"1", "foo", "abcd1", "efgh"},
},
Rewritten: []string{
"select * from vitess_cached2 where 1 != 1",
},
RowsAffected: 1,
Table: "vitess_cached2",
Hits: 1,
},
// DDL
framework.TestQuery("alter table vitess_cached2 comment 'test'"),
// (1.foo)
&framework.TestCase{
Name: "Verify cache is empty after DDL",
Query: "select * from vitess_cached2 where eid = 1 and bid = 'foo'",
Result: [][]string{
{"1", "foo", "abcd1", "efgh"},
},
Rewritten: []string{
"select * from vitess_cached2 where 1 != 1",
"select eid, bid, name, foo from vitess_cached2 where (eid = 1 and bid = 'foo')",
},
RowsAffected: 1,
Table: "vitess_cached2",
Misses: 1,
},
// (1.foo)
&framework.TestCase{
Name: "Verify row is cached",
Query: "select * from vitess_cached2 where eid = 1 and bid = 'foo'",
Result: [][]string{
{"1", "foo", "abcd1", "efgh"},
},
RowsAffected: 1,
Table: "vitess_cached2",
Hits: 1,
},
}
for _, tcase := range testCases {
if err := tcase.Test("", client); err != nil {
t.Error(err)
}
}
}
// TestCacheCases1 covers cases for vitess_cached1.
func TestCacheCases1(t *testing.T) {
client := framework.NewClient()
testCases := []framework.Testable{
framework.TestQuery("alter table vitess_cached1 comment 'new'"),
// (1) will be in cache after this.
&framework.TestCase{
Name: "PK_IN (empty cache)",
Query: "select * from vitess_cached1 where eid = 1",
Result: [][]string{
{"1", "a", "abcd"},
},
Rewritten: []string{
"select * from vitess_cached1 where 1 != 1",
"select eid, name, foo from vitess_cached1 where eid in (1)",
},
RowsAffected: 1,
Plan: "PK_IN",
Table: "vitess_cached1",
Misses: 1,
},
// (1)
&framework.TestCase{
Name: "PK_IN, use cache",
Query: "select * from vitess_cached1 where eid = 1",
Result: [][]string{
{"1", "a", "abcd"},
},
RowsAffected: 1,
Plan: "PK_IN",
Table: "vitess_cached1",
Hits: 1,
},
// (1)
&framework.TestCase{
Name: "PK_IN int bind var",
Query: "select * from vitess_cached1 where eid = :eid",
BindVars: map[string]interface{}{
"eid": 1,
},
Result: [][]string{
{"1", "a", "abcd"},
},
RowsAffected: 1,
Plan: "PK_IN",
Table: "vitess_cached1",
Hits: 1,
},
// (1)
&framework.TestCase{
Name: "PK_IN string bind var",
Query: "select * from vitess_cached1 where eid = :eid",
BindVars: map[string]interface{}{
"eid": "1",
},
Result: [][]string{
{"1", "a", "abcd"},
},
RowsAffected: 1,
Plan: "PK_IN",
Table: "vitess_cached1",
Hits: 1,
},
// (1, 3)
&framework.TestCase{
Name: "PK_IN (empty cache)",
Query: "select * from vitess_cached1 where eid in (1, 3, 6)",
Result: [][]string{
{"1", "a", "abcd"},
{"3", "c", "abcd"},
},
Rewritten: []string{
"select * from vitess_cached1 where 1 != 1",
"select eid, name, foo from vitess_cached1 where eid in (3, 6)",
},
RowsAffected: 2,
Plan: "PK_IN",
Table: "vitess_cached1",
Hits: 1,
Misses: 1,
Absent: 1,
},
// (1, 3)
&framework.TestCase{
Name: "PK_IN limit 0",
Query: "select * from vitess_cached1 where eid in (1, 3, 6) limit 0",
Rewritten: []string{
"select * from vitess_cached1 where 1 != 1",
},
Plan: "PK_IN",
Table: "vitess_cached1",
},
// (1, 3)
&framework.TestCase{
Name: "PK_IN limit 1",
Query: "select * from vitess_cached1 where eid in (1, 3, 6) limit 1",
Result: [][]string{
{"1", "a", "abcd"},
},
Rewritten: []string{
"select * from vitess_cached1 where 1 != 1",
"select eid, name, foo from vitess_cached1 where eid in (6)",
},
RowsAffected: 1,
Plan: "PK_IN",
Table: "vitess_cached1",
Hits: 2,
Absent: 1,
},
// (1, 3)
&framework.TestCase{
Name: "PK_IN limit :a",
Query: "select * from vitess_cached1 where eid in (1, 3, 6) limit :a",
BindVars: map[string]interface{}{
"a": 1,
},
Result: [][]string{
{"1", "a", "abcd"},
},
Rewritten: []string{
"select * from vitess_cached1 where 1 != 1",
"select eid, name, foo from vitess_cached1 where eid in (6)",
},
RowsAffected: 1,
Plan: "PK_IN",
Table: "vitess_cached1",
Hits: 2,
Absent: 1,
},
// (1, 2, 3)
&framework.TestCase{
Name: "SELECT_SUBQUERY (1, 2)",
Query: "select * from vitess_cached1 where name = 'a'",
Result: [][]string{
{"1", "a", "abcd"},
{"2", "a", "abcd"},
},
Rewritten: []string{
"select * from vitess_cached1 where 1 != 1",
"select eid from vitess_cached1 use index (aname1) where name = 'a' limit 10001",
"select eid, name, foo from vitess_cached1 where eid in (2)",
},
RowsAffected: 2,
Table: "vitess_cached1",
Hits: 1,
Misses: 1,
},
// (1, 2, 3)
&framework.TestCase{
Name: "covering index",
Query: "select eid, name from vitess_cached1 where name = 'a'",
Result: [][]string{
{"1", "a"},
{"2", "a"},
},
Rewritten: []string{
"select eid, name from vitess_cached1 where 1 != 1",
"select eid, name from vitess_cached1 where name = 'a' limit 10001",
},
RowsAffected: 2,
Plan: "PASS_SELECT",
Table: "vitess_cached1",
},
// (1, 2, 3)
&framework.TestCase{
Name: "SELECT_SUBQUERY (1, 2)",
Query: "select * from vitess_cached1 where name = 'a'",
Result: [][]string{
{"1", "a", "abcd"},
{"2", "a", "abcd"},
},
Rewritten: []string{
"select eid from vitess_cached1 use index (aname1) where name = 'a' limit 10001",
},
RowsAffected: 2,
Table: "vitess_cached1",
Hits: 2,
},
// (1, 2, 3, 4, 5)
&framework.TestCase{
Name: "SELECT_SUBQUERY (4, 5)",
Query: "select * from vitess_cached1 where name between 'd' and 'e'",
Result: [][]string{
{"4", "d", "abcd"},
{"5", "e", "efgh"},
},
Rewritten: []string{
"select * from vitess_cached1 where 1 != 1",
"select eid from vitess_cached1 use index (aname1) where name between 'd' and 'e' limit 10001",
"select eid, name, foo from vitess_cached1 where eid in (4, 5)",
},
RowsAffected: 2,
Plan: "SELECT_SUBQUERY",
Table: "vitess_cached1",
Misses: 2,
},
// (1, 2, 3, 4, 5)
&framework.TestCase{
Name: "PASS_SELECT",
Query: "select * from vitess_cached1 where foo='abcd'",
Result: [][]string{
{"1", "a", "abcd"},
{"2", "a", "abcd"},
{"3", "c", "abcd"},
{"4", "d", "abcd"},
},
Rewritten: []string{
"select * from vitess_cached1 where 1 != 1",
"select * from vitess_cached1 where foo = 'abcd' limit 10001",
},
RowsAffected: 4,
Plan: "PASS_SELECT",
Table: "vitess_cached1",
},
}
for _, tcase := range testCases {
if err := tcase.Test("", client); err != nil {
t.Error(err)
}
}
}