'select eid from (select eid from vtocc_a where id = 2) as a limit 10001'
]),
MultiCase('select in transaction', [
'begin',
Case(sql='select * from vtocc_a where eid = 2 and id = 1',
result=[],
rewritten=[
"select * from vtocc_a where 1 != 1",
"select * from vtocc_a where eid = 2 and id = 1 limit 10001"
]),
Case(sql='select * from vtocc_a where eid = 2 and id = 1',
result=[],
rewritten=[
"select * from vtocc_a where eid = 2 and id = 1 limit 10001"
]),
Case(sql="select :bv from vtocc_a where eid = 2 and id = 1",
bindings={'bv': 1},
result=[],
rewritten=[
"select 1 from vtocc_a where eid = 2 and id = 1 limit 10001"
]),
Case(
sql="select :bv from vtocc_a where eid = 2 and id = 1",
bindings={'bv': 'abcd'},
result=[],
rewritten=[
"select 'abcd' from vtocc_a where eid = 2 and id = 1 limit 10001"
]), 'commit'
]),
MultiCase('simple insert', [
'begin',
result=[(1L, 'bar', 'abcd1', 'fghi')],
rowcount=1,
rewritten=['select eid, bid, name, foo from vtocc_cached2 '
"where (eid = 1 and bid = 'bar')"],
cache_misses=1)]),
# (1.foo, 1.bar, 2.foo, 2.bar)
MultiCase(
'delete',
['begin',
"delete from vtocc_cached2 where eid = 1 and bid = 'bar'",
Case2(
sql='commit',
cache_invalidations=1),
Case2(
sql="select * from vtocc_cached2 where eid = 1 and bid = 'bar'",
result=[],
rowcount=0,
rewritten='select eid, bid, name, foo from vtocc_cached2 '
"where (eid = 1 and bid = 'bar')",
cache_absent=1),
'begin',
'insert into vtocc_cached2(eid, bid, name, foo) '
"values (1, 'bar', 'abcd1', 'efgh')",
Case2(sql='commit', cache_invalidations=0)]),
# (1.foo, 2.foo, 2.bar)
Case2(doc='Verify 1.foo is in cache',
sql="select * from vtocc_cached2 where eid = 1 and bid = 'foo'",
result=[(1, 'foo', 'abcd1', 'efgh')],
rowcount=1,
rewritten=['select * from vtocc_cached2 where 1 != 1'],
# (1.foo, 1.bar, 2.foo, 2.bar)
MultiCase("this will not invalidate the cache", [
'begin', "update vtocc_cached2 set foo='fghi' where bid = 'bar'",
'rollback',
Case2(sql="select * from vtocc_cached2 where eid = 1 and bid = 'bar'",
result=[(1L, 'bar', 'abcd1', 'fghi')],
rewritten=[],
cache_hits=1)
]),
# (1.foo, 1.bar, 2.foo, 2.bar)
MultiCase("delete", [
'begin', "delete from vtocc_cached2 where eid = 1 and bid = 'bar'",
Case2(sql="commit", cache_invalidations=1),
Case2(
sql="select * from vtocc_cached2 where eid = 1 and bid = 'bar'",
result=[],
rewritten=
"select eid, bid, name, foo from vtocc_cached2 where (eid, bid) = (1, 'bar')",
cache_absent=1), "begin",
"insert into vtocc_cached2(eid, bid, name, foo) values (1, 'bar', 'abcd1', 'efgh')",
Case2(sql="commit", cache_invalidations=0)
]),
# (1.foo, 2.foo, 2.bar)
Case2(doc="Verify 1.foo is in cache",
sql="select * from vtocc_cached2 where eid = 1 and bid = 'foo'",
result=[(1, 'foo', 'abcd1', 'efgh')],
rewritten=["select * from vtocc_cached2 where 1 != 1"],
cache_hits=1),
# (1.foo, 2.foo, 2.bar)
# DDL
"alter table vtocc_cached2 comment 'test'",
Case2(
doc="out of order columns list, use cache",
sql="select bid, eid from vtocc_cached2 where eid = 1 and bid = 'foo'",
result=[('foo', 1)],
rewritten=[],
cache_hits=1),
# (1.foo, 2.foo)
MultiCase(
"PASS_SELECT", # it currently doesn't cache
[
"select * from vtocc_cached2 where eid = 1 and bid in('foo', 'bar')",
Case2(
query_plan="PASS_SELECT",
sql=
"select eid, bid, name, foo from vtocc_cached2 where eid = 1 and bid in('foo', 'bar')",
rewritten=[
"select eid, bid, name, foo from vtocc_cached2 where 1 != 1",
"select eid, bid, name, foo from vtocc_cached2 where eid = 1 and bid in ('foo', 'bar') limit 10001"
],
cache_hits=0,
cache_misses=0,
cache_absent=0,
cache_invalidations=0)
]),
# (1.foo, 2.foo)
Case2(
doc="verify 1.bar is not in cache",
sql="select bid, eid from vtocc_cached2 where eid = 1 and bid = 'bar'",
result=[('bar', 1)],
rewritten=[
"select bid, eid from vtocc_cached2 where 1 != 1",
cache_misses=1),
# (1.foo, 2.bar, 2.foo)
Case(doc="out of order columns list, use cache",
sql="select bid, eid from vtocc_cached where eid = 1 and bid = 'foo'",
result=[('foo', 1)],
rewritten=[],
cache_hits=1),
# (1.foo, 2.bar, 2.foo)
MultiCase(
"PASS_SELECT", # it currently doesn't cache
[
'select * from vtocc_cached',
Case(
query_plan="PASS_SELECT",
sql="select eid, bid, name, foo from vtocc_cached",
rewritten=[
"select eid, bid, name, foo from vtocc_cached where 1 != 1",
"select eid, bid, name, foo from vtocc_cached limit 10001"
],
cache_hits=0,
cache_misses=0,
cache_absent=0,
cache_invalidations=0)
]),
# (1.foo, 2.bar, 2.foo)
Case(
doc="verify 1.bar is not cached",
sql="select bid, eid from vtocc_cached where eid = 1 and bid = 'bar'",
result=[('bar', 1)],
rewritten=[
"select bid, eid from vtocc_cached where 1 != 1",
"select eid, bid, name, foo from vtocc_cached where eid = 1 and bid = 'bar'"