Permalink
Fetching contributors…
Cannot retrieve contributors at this time
988 lines (944 sloc) 22.8 KB
# Test cases in this file follow the code in ordered_aggregate.go.
# Aggregate on unsharded
"select count(*), col from unsharded"
{
"Original": "select count(*), col from unsharded",
"Instructions": {
"Opcode": "SelectUnsharded",
"Keyspace": {
"Name": "main",
"Sharded": false
},
"Query": "select count(*), col from unsharded",
"FieldQuery": "select count(*), col from unsharded where 1 != 1"
}
}
# Aggregate on unique sharded
"select count(*), col from user where id = 1"
{
"Original": "select count(*), col from user where id = 1",
"Instructions": {
"Opcode": "SelectEqualUnique",
"Keyspace": {
"Name": "user",
"Sharded": true
},
"Query": "select count(*), col from user where id = 1",
"FieldQuery": "select count(*), col from user where 1 != 1",
"Vindex": "user_index",
"Values": [1]
}
}
# Aggregate detection (non-aggregate function)
"select fun(1), col from user"
{
"Original": "select fun(1), col from user",
"Instructions": {
"Opcode": "SelectScatter",
"Keyspace": {
"Name": "user",
"Sharded": true
},
"Query": "select fun(1), col from user",
"FieldQuery": "select fun(1), col from user where 1 != 1"
}
}
# select distinct with unique vindex for scatter route.
"select distinct col1, id from user"
{
"Original": "select distinct col1, id from user",
"Instructions": {
"Opcode": "SelectScatter",
"Keyspace": {
"Name": "user",
"Sharded": true
},
"Query": "select distinct col1, id from user",
"FieldQuery": "select col1, id from user where 1 != 1"
}
}
# distinct and group by together for single route.
"select distinct col1, id from user group by col1"
{
"Original": "select distinct col1, id from user group by col1",
"Instructions": {
"Opcode": "SelectScatter",
"Keyspace": {
"Name": "user",
"Sharded": true
},
"Query": "select distinct col1, id from user group by col1",
"FieldQuery": "select col1, id from user where 1 != 1 group by col1"
}
}
# scatter group by a text column
"select count(*), a, textcol1, b from user group by a, textcol1, b"
{
"Original": "select count(*), a, textcol1, b from user group by a, textcol1, b",
"Instructions": {
"Aggregates": [
{
"Opcode": "count",
"Col": 0
}
],
"Keys": [
1,
4,
3
],
"TruncateColumnCount": 4,
"Input": {
"Opcode": "SelectScatter",
"Keyspace": {
"Name": "user",
"Sharded": true
},
"Query": "select count(*), a, textcol1, b, weight_string(textcol1) from user group by a, textcol1, b order by a asc, textcol1 asc, b asc",
"FieldQuery": "select count(*), a, textcol1, b, weight_string(textcol1) from user where 1 != 1 group by a, textcol1, b",
"OrderBy": [
{
"Col": 1,
"Desc": false
},
{
"Col": 4,
"Desc": false
},
{
"Col": 3,
"Desc": false
}
]
}
}
}
# count aggregate
"select count(*) from user"
{
"Original": "select count(*) from user",
"Instructions": {
"Aggregates": [
{
"Opcode": "count",
"Col": 0
}
],
"Keys": null,
"Input": {
"Opcode": "SelectScatter",
"Keyspace": {
"Name": "user",
"Sharded": true
},
"Query": "select count(*) from user",
"FieldQuery": "select count(*) from user where 1 != 1"
}
}
}
# sum aggregate
"select sum(col) from user"
{
"Original": "select sum(col) from user",
"Instructions": {
"Aggregates": [
{
"Opcode": "sum",
"Col": 0
}
],
"Keys": null,
"Input": {
"Opcode": "SelectScatter",
"Keyspace": {
"Name": "user",
"Sharded": true
},
"Query": "select sum(col) from user",
"FieldQuery": "select sum(col) from user where 1 != 1"
}
}
}
# min aggregate
"select min(col) from user"
{
"Original": "select min(col) from user",
"Instructions": {
"Aggregates": [
{
"Opcode": "min",
"Col": 0
}
],
"Keys": null,
"Input": {
"Opcode": "SelectScatter",
"Keyspace": {
"Name": "user",
"Sharded": true
},
"Query": "select min(col) from user",
"FieldQuery": "select min(col) from user where 1 != 1"
}
}
}
# max aggregate
"select max(col) from user"
{
"Original": "select max(col) from user",
"Instructions": {
"Aggregates": [
{
"Opcode": "max",
"Col": 0
}
],
"Keys": null,
"Input": {
"Opcode": "SelectScatter",
"Keyspace": {
"Name": "user",
"Sharded": true
},
"Query": "select max(col) from user",
"FieldQuery": "select max(col) from user where 1 != 1"
}
}
}
# distinct and group by together for scatter route
"select distinct col1, col2 from user group by col1"
{
"Original": "select distinct col1, col2 from user group by col1",
"Instructions": {
"Aggregates": null,
"Keys": [
0,
1,
0
],
"Input": {
"Opcode": "SelectScatter",
"Keyspace": {
"Name": "user",
"Sharded": true
},
"Query": "select distinct col1, col2 from user group by col1 order by col1 asc, col2 asc, col1 asc",
"FieldQuery": "select col1, col2 from user where 1 != 1 group by col1",
"OrderBy": [
{
"Col": 0,
"Desc": false
},
{
"Col": 1,
"Desc": false
},
{
"Col": 0,
"Desc": false
}
]
}
}
}
# aggregate on RHS subquery (tests symbol table merge)
"select user.a, t.b from user join (select count(*) b from unsharded) as t"
{
"Original": "select user.a, t.b from user join (select count(*) b from unsharded) as t",
"Instructions": {
"Opcode": "Join",
"Left": {
"Opcode": "SelectScatter",
"Keyspace": {
"Name": "user",
"Sharded": true
},
"Query": "select user.a from user",
"FieldQuery": "select user.a from user where 1 != 1"
},
"Right": {
"Opcode": "SelectUnsharded",
"Keyspace": {
"Name": "main",
"Sharded": false
},
"Query": "select t.b from (select count(*) as b from unsharded) as t",
"FieldQuery": "select t.b from (select count(*) as b from unsharded where 1 != 1) as t where 1 != 1"
},
"Cols": [
-1,
1
]
}
}
# group by a unique vindex should use a simple route
"select id, count(*) from user group by id"
{
"Original": "select id, count(*) from user group by id",
"Instructions": {
"Opcode": "SelectScatter",
"Keyspace": {
"Name": "user",
"Sharded": true
},
"Query": "select id, count(*) from user group by id",
"FieldQuery": "select id, count(*) from user where 1 != 1 group by id"
}
}
# group by a unique vindex and other column should use a simple route
"select id, col, count(*) from user group by id, col"
{
"Original": "select id, col, count(*) from user group by id, col",
"Instructions": {
"Opcode": "SelectScatter",
"Keyspace": {
"Name": "user",
"Sharded": true
},
"Query": "select id, col, count(*) from user group by id, col",
"FieldQuery": "select id, col, count(*) from user where 1 != 1 group by id, col"
}
}
# group by a non-vindex column should use an OrderdAggregate primitive
"select col, count(*) from user group by col"
{
"Original": "select col, count(*) from user group by col",
"Instructions": {
"Aggregates": [
{
"Opcode": "count",
"Col": 1
}
],
"Keys": [
0
],
"Input": {
"Opcode": "SelectScatter",
"Keyspace": {
"Name": "user",
"Sharded": true
},
"Query": "select col, count(*) from user group by col order by col asc",
"FieldQuery": "select col, count(*) from user where 1 != 1 group by col",
"OrderBy": [
{
"Col": 0,
"Desc": false
}
]
}
}
}
# group by a non-unique vindex column should use an OrderdAggregate primitive
"select name, count(*) from user group by name"
{
"Original": "select name, count(*) from user group by name",
"Instructions": {
"Aggregates": [
{
"Opcode": "count",
"Col": 1
}
],
"Keys": [
0
],
"Input": {
"Opcode": "SelectScatter",
"Keyspace": {
"Name": "user",
"Sharded": true
},
"Query": "select name, count(*) from user group by name order by name asc",
"FieldQuery": "select name, count(*) from user where 1 != 1 group by name",
"OrderBy": [
{
"Col": 0,
"Desc": false
}
]
}
}
}
# group by a unique vindex should use a simple route, even if aggr is complex
"select id, 1+count(*) from user group by id"
{
"Original": "select id, 1+count(*) from user group by id",
"Instructions": {
"Opcode": "SelectScatter",
"Keyspace": {
"Name": "user",
"Sharded": true
},
"Query": "select id, 1 + count(*) from user group by id",
"FieldQuery": "select id, 1 + count(*) from user where 1 != 1 group by id"
}
}
# group by a unique vindex where alias from select list is used
"select id as val, 1+count(*) from user group by val"
{
"Original": "select id as val, 1+count(*) from user group by val",
"Instructions": {
"Opcode": "SelectScatter",
"Keyspace": {
"Name": "user",
"Sharded": true
},
"Query": "select id as val, 1 + count(*) from user group by val",
"FieldQuery": "select id as val, 1 + count(*) from user where 1 != 1 group by val"
}
}
# group by a unique vindex where expression is qualified (alias should be ignored)
"select val as id, 1+count(*) from user group by user.id"
{
"Original": "select val as id, 1+count(*) from user group by user.id",
"Instructions": {
"Opcode": "SelectScatter",
"Keyspace": {
"Name": "user",
"Sharded": true
},
"Query": "select val as id, 1 + count(*) from user group by user.id",
"FieldQuery": "select val as id, 1 + count(*) from user where 1 != 1 group by user.id"
}
}
# group by a unique vindex where it should skip non-aliased expressions.
"select *, id, 1+count(*) from user group by id"
{
"Original": "select *, id, 1+count(*) from user group by id",
"Instructions": {
"Opcode": "SelectScatter",
"Keyspace": {
"Name": "user",
"Sharded": true
},
"Query": "select *, id, 1 + count(*) from user group by id",
"FieldQuery": "select *, id, 1 + count(*) from user where 1 != 1 group by id"
}
}
# group by a unique vindex should revert to simple route, and having clause should find the correct symbols.
"select id, count(*) c from user group by id having id=1 and c=10"
{
"Original": "select id, count(*) c from user group by id having id=1 and c=10",
"Instructions": {
"Opcode": "SelectEqualUnique",
"Keyspace": {
"Name": "user",
"Sharded": true
},
"Query": "select id, count(*) as c from user group by id having id = 1 and c = 10",
"FieldQuery": "select id, count(*) as c from user where 1 != 1 group by id",
"Vindex": "user_index",
"Values": [1]
}
}
# scatter aggregate in a subquery
"select a from (select count(*) as a from user) t"
{
"Original": "select a from (select count(*) as a from user) t",
"Instructions": {
"Cols": [
0
],
"Subquery": {
"Aggregates": [
{
"Opcode": "count",
"Col": 0
}
],
"Keys": null,
"Input": {
"Opcode": "SelectScatter",
"Keyspace": {
"Name": "user",
"Sharded": true
},
"Query": "select count(*) as a from user",
"FieldQuery": "select count(*) as a from user where 1 != 1"
}
}
}
}
# scatter aggregate with non-aggregate expressions.
"select id, count(*) from user"
{
"Original": "select id, count(*) from user",
"Instructions": {
"Aggregates": [
{
"Opcode": "count",
"Col": 1
}
],
"Keys": null,
"Input": {
"Opcode": "SelectScatter",
"Keyspace": {
"Name": "user",
"Sharded": true
},
"Query": "select id, count(*) from user",
"FieldQuery": "select id, count(*) from user where 1 != 1"
}
}
}
# scatter aggregate using distinct
"select distinct col from user"
{
"Original": "select distinct col from user",
"Instructions": {
"Aggregates": null,
"Keys": [
0
],
"Input": {
"Opcode": "SelectScatter",
"Keyspace": {
"Name": "user",
"Sharded": true
},
"Query": "select distinct col from user order by col asc",
"FieldQuery": "select col from user where 1 != 1",
"OrderBy": [
{
"Col": 0,
"Desc": false
}
]
}
}
}
# scatter aggregate group by select col
"select col from user group by col"
{
"Original": "select col from user group by col",
"Instructions": {
"Aggregates": null,
"Keys": [
0
],
"Input": {
"Opcode": "SelectScatter",
"Keyspace": {
"Name": "user",
"Sharded": true
},
"Query": "select col from user group by col order by col asc",
"FieldQuery": "select col from user where 1 != 1 group by col",
"OrderBy": [
{
"Col": 0,
"Desc": false
}
]
}
}
}
# scatter aggregate group by aggregate function
" select count(*) b from user group by b"
"group by expression cannot reference an aggregate function: b"
# scatter aggregate multiple group by (columns)
"select a, b, count(*) from user group by b, a"
{
"Original": "select a, b, count(*) from user group by b, a",
"Instructions": {
"Aggregates": [
{
"Opcode": "count",
"Col": 2
}
],
"Keys": [
1,
0
],
"Input": {
"Opcode": "SelectScatter",
"Keyspace": {
"Name": "user",
"Sharded": true
},
"Query": "select a, b, count(*) from user group by b, a order by b asc, a asc",
"FieldQuery": "select a, b, count(*) from user where 1 != 1 group by b, a",
"OrderBy": [
{
"Col": 1,
"Desc": false
},
{
"Col": 0,
"Desc": false
}
]
}
}
}
# scatter aggregate multiple group by (numbers)
"select a, b, count(*) from user group by 2, 1"
{
"Original": "select a, b, count(*) from user group by 2, 1",
"Instructions": {
"Aggregates": [
{
"Opcode": "count",
"Col": 2
}
],
"Keys": [
1,
0
],
"Input": {
"Opcode": "SelectScatter",
"Keyspace": {
"Name": "user",
"Sharded": true
},
"Query": "select a, b, count(*) from user group by 2, 1 order by b asc, a asc",
"FieldQuery": "select a, b, count(*) from user where 1 != 1 group by 2, 1",
"OrderBy": [
{
"Col": 1,
"Desc": false
},
{
"Col": 0,
"Desc": false
}
]
}
}
}
# scatter aggregate group by column number
"select col from user group by 1"
{
"Original": "select col from user group by 1",
"Instructions": {
"Aggregates": null,
"Keys": [
0
],
"Input": {
"Opcode": "SelectScatter",
"Keyspace": {
"Name": "user",
"Sharded": true
},
"Query": "select col from user group by 1 order by col asc",
"FieldQuery": "select col from user where 1 != 1 group by 1",
"OrderBy": [
{
"Col": 0,
"Desc": false
}
]
}
}
}
# scatter aggregate group by invalid column number
"select col from user group by 2"
"column number out of range: 2"
# scatter aggregate order by null
"select count(*) from user order by null"
{
"Original": "select count(*) from user order by null",
"Instructions": {
"Aggregates": [
{
"Opcode": "count",
"Col": 0
}
],
"Keys": null,
"Input": {
"Opcode": "SelectScatter",
"Keyspace": {
"Name": "user",
"Sharded": true
},
"Query": "select count(*) from user",
"FieldQuery": "select count(*) from user where 1 != 1"
}
}
}
# scatter aggregate with complex select list (can't build order by)
"select distinct a+1 from user"
"generating order by clause: cannot reference a complex expression"
# scatter aggregate with numbered order by columns
"select a, b, c, d, count(*) from user group by 1, 2, 3 order by 1, 2, 3"
{
"Original": "select a, b, c, d, count(*) from user group by 1, 2, 3 order by 1, 2, 3",
"Instructions": {
"Aggregates": [
{
"Opcode": "count",
"Col": 4
}
],
"Keys": [
0,
1,
2
],
"Input": {
"Opcode": "SelectScatter",
"Keyspace": {
"Name": "user",
"Sharded": true
},
"Query": "select a, b, c, d, count(*) from user group by 1, 2, 3 order by 1 asc, 2 asc, 3 asc",
"FieldQuery": "select a, b, c, d, count(*) from user where 1 != 1 group by 1, 2, 3",
"OrderBy": [
{
"Col": 0,
"Desc": false
},
{
"Col": 1,
"Desc": false
},
{
"Col": 2,
"Desc": false
}
]
}
}
}
# scatter aggregate with named order by columns
"select a, b, c, d, count(*) from user group by 1, 2, 3 order by a, b, c"
{
"Original": "select a, b, c, d, count(*) from user group by 1, 2, 3 order by a, b, c",
"Instructions": {
"Aggregates": [
{
"Opcode": "count",
"Col": 4
}
],
"Keys": [
0,
1,
2
],
"Input": {
"Opcode": "SelectScatter",
"Keyspace": {
"Name": "user",
"Sharded": true
},
"Query": "select a, b, c, d, count(*) from user group by 1, 2, 3 order by a asc, b asc, c asc",
"FieldQuery": "select a, b, c, d, count(*) from user where 1 != 1 group by 1, 2, 3",
"OrderBy": [
{
"Col": 0,
"Desc": false
},
{
"Col": 1,
"Desc": false
},
{
"Col": 2,
"Desc": false
}
]
}
}
}
# scatter aggregate with jumbled order by columns
"select a, b, c, d, count(*) from user group by 1, 2, 3, 4 order by d, b, a, c"
{
"Original": "select a, b, c, d, count(*) from user group by 1, 2, 3, 4 order by d, b, a, c",
"Instructions": {
"Aggregates": [
{
"Opcode": "count",
"Col": 4
}
],
"Keys": [
0,
1,
2,
3
],
"Input": {
"Opcode": "SelectScatter",
"Keyspace": {
"Name": "user",
"Sharded": true
},
"Query": "select a, b, c, d, count(*) from user group by 1, 2, 3, 4 order by d asc, b asc, a asc, c asc",
"FieldQuery": "select a, b, c, d, count(*) from user where 1 != 1 group by 1, 2, 3, 4",
"OrderBy": [
{
"Col": 3,
"Desc": false
},
{
"Col": 1,
"Desc": false
},
{
"Col": 0,
"Desc": false
},
{
"Col": 2,
"Desc": false
}
]
}
}
}
# scatter aggregate with jumbled group by and order by columns
"select a, b, c, d, count(*) from user group by 3, 2, 1, 4 order by d, b, a, c"
{
"Original": "select a, b, c, d, count(*) from user group by 3, 2, 1, 4 order by d, b, a, c",
"Instructions": {
"Aggregates": [
{
"Opcode": "count",
"Col": 4
}
],
"Keys": [
2,
1,
0,
3
],
"Input": {
"Opcode": "SelectScatter",
"Keyspace": {
"Name": "user",
"Sharded": true
},
"Query": "select a, b, c, d, count(*) from user group by 3, 2, 1, 4 order by d asc, b asc, a asc, c asc",
"FieldQuery": "select a, b, c, d, count(*) from user where 1 != 1 group by 3, 2, 1, 4",
"OrderBy": [
{
"Col": 3,
"Desc": false
},
{
"Col": 1,
"Desc": false
},
{
"Col": 0,
"Desc": false
},
{
"Col": 2,
"Desc": false
}
]
}
}
}
# scatter aggregate with some descending order by cols
"select a, b, c, count(*) from user group by 3, 2, 1 order by 1 desc, 3 desc, b"
{
"Original": "select a, b, c, count(*) from user group by 3, 2, 1 order by 1 desc, 3 desc, b",
"Instructions": {
"Aggregates": [
{
"Opcode": "count",
"Col": 3
}
],
"Keys": [
2,
1,
0
],
"Input": {
"Opcode": "SelectScatter",
"Keyspace": {
"Name": "user",
"Sharded": true
},
"Query": "select a, b, c, count(*) from user group by 3, 2, 1 order by 1 desc, 3 desc, b asc",
"FieldQuery": "select a, b, c, count(*) from user where 1 != 1 group by 3, 2, 1",
"OrderBy": [
{
"Col": 0,
"Desc": true
},
{
"Col": 2,
"Desc": true
},
{
"Col": 1,
"Desc": false
}
]
}
}
}
# invalid order by column numner for scatter
"select col, count(*) from user group by col order by 5 limit 10"
"invalid order by: column number out of range: 5"
# aggregate with limit
"select col, count(*) from user group by col limit 10"
{
"Original": "select col, count(*) from user group by col limit 10",
"Instructions": {
"Opcode": "Limit",
"Count": 10,
"Offset": null,
"Input": {
"Aggregates": [
{
"Opcode": "count",
"Col": 1
}
],
"Keys": [
0
],
"Input": {
"Opcode": "SelectScatter",
"Keyspace": {
"Name": "user",
"Sharded": true
},
"Query": "select col, count(*) from user group by col order by col asc limit :__upper_limit",
"FieldQuery": "select col, count(*) from user where 1 != 1 group by col",
"OrderBy": [
{
"Col": 0,
"Desc": false
}
]
}
}
}
}
# Group by with collate operator
"select user.col1 as a from user where user.id = 5 group by a collate utf8_general_ci"
{
"Original":"select user.col1 as a from user where user.id = 5 group by a collate utf8_general_ci",
"Instructions":{
"Opcode":"SelectEqualUnique",
"Keyspace":{
"Name":"user",
"Sharded":true
},
"Query":"select user.col1 as a from user where user.id = 5 group by a collate utf8_general_ci",
"FieldQuery":"select user.col1 as a from user where 1 != 1 group by a collate utf8_general_ci",
"Vindex":"user_index",
"Values":[5]
}
}
# Group by invalid column number (code is duplicated from symab).
"select id from user group by 1.1"
"column number is not an int"
# Group by out of range column number (code is duplicated from symab).
"select id from user group by 2"
"column number out of range: 2"