Skip to content

Commit

Permalink
Fix some more SQL set op + PartiQL bag op tests
Browse files Browse the repository at this point in the history
  • Loading branch information
@alancai98
alancai98 committed Aug 9, 2024
1 parent 9aa7694 commit aa2e51f
Show file tree
Hide file tree
Showing 3 changed files with 242 additions and 50 deletions.
204 changes: 176 additions & 28 deletions partiql-tests-data/eval/primitives/operators/bag-operators.ion
View file
Original file line number Diff line number Diff line change
Expand Up @@ -143,56 +143,204 @@ bagOperators::[
]
}
},
// outer union coercion
{
name:"outerUnionCoerceScalar",
statement:"1 OUTER UNION 2",
assert:{
evalMode:[EvalModeCoerce, EvalModeError],
result:EvaluationSuccess,
output:$bag::[
1,
2
]
}
assert:[
{
evalMode:EvalModeCoerce,
result:EvaluationSuccess,
output:$bag::[
1,
2
]
},
{
evalMode:EvalModeError,
result:EvaluationFail
},
]
},
{
name:"outerUnionCoerceStruct",
statement:"{'a': 1} OUTER UNION {'b': 2}",
assert:[
{
evalMode:EvalModeCoerce,
result:EvaluationSuccess,
output:$bag::[
{
a:1
},
{
b:2
}
]
},
{
evalMode:EvalModeError,
result:EvaluationFail
},
]
},
{
name:"outerUnionCoerceNullMissing",
statement:"NULL OUTER UNION MISSING",
assert:[
{
evalMode:EvalModeCoerce,
result:EvaluationSuccess,
output:$bag::[
]
},
{
evalMode: EvalModeError,
result:EvaluationFail
},
]
},
{
name:"outerUnionCoerceList",
statement:"[ 1, 1, 1 ] OUTER UNION ALL [ 1, 2 ]",
assert:{
evalMode:[EvalModeCoerce, EvalModeError],
result:EvaluationSuccess,
output:$bag::[
{
a:1
},
{
b:2
}
1,
1,
1,
1,
2
]
}
},
// outer intersect coercion
{
name:"outerUnionCoerceNullMissing",
statement:"NULL OUTER UNION MISSING",
name:"outerIntersectCoerceScalar",
statement:"1 OUTER INTERSECT 1",
assert:[
{
evalMode:EvalModeCoerce,
result:EvaluationSuccess,
output:$bag::[
1
]
},
{
evalMode:EvalModeError,
result:EvaluationFail
},
]
},
{
name:"outerIntersectCoerceStruct",
statement:"{'a': 1} OUTER INTERSECT {'a': 1}",
assert:[
{
evalMode:EvalModeCoerce,
result:EvaluationSuccess,
output:$bag::[
{
a:1
}
]
},
{
evalMode:EvalModeError,
result:EvaluationFail
},
]
},
{
name:"outerIntersectCoerceNullMissing",
statement:"NULL OUTER INTERSECT MISSING",
assert:[
{
evalMode:EvalModeCoerce,
result:EvaluationSuccess,
output:$bag::[
]
},
{
evalMode: EvalModeError,
result:EvaluationFail
},
]
},
{
name:"outerIntersectCoerceList",
statement:"[ 1, 1, 1 ] OUTER INTERSECT ALL [ 1, 2 ]",
assert:{
evalMode:[EvalModeCoerce, EvalModeError],
result:EvaluationSuccess,
output:$bag::[
1
]
}
},
// outer except coercion
{
name:"outerUnionCoerceList",
statement:"[ 1, 1, 1 ] OUTER UNION ALL [ 1, 2 ]",
name:"outerExceptCoerceScalar",
statement:"1 OUTER EXCEPT 2",
assert:[
{
evalMode:EvalModeCoerce,
result:EvaluationSuccess,
output:$bag::[
1
]
},
{
evalMode:EvalModeError,
result:EvaluationFail
},
]
},
{
name:"outerExceptCoerceStruct",
statement:"{'a': 1} OUTER EXCEPT {'b': 2}",
assert:[
{
evalMode:EvalModeCoerce,
result:EvaluationSuccess,
output:$bag::[
{
a:1
}
]
},
{
evalMode:EvalModeError,
result:EvaluationFail
},
]
},
{
name:"outerExceptCoerceNullMissing",
statement:"NULL OUTER EXCEPT MISSING",
assert:[
{
evalMode:EvalModeCoerce,
result:EvaluationSuccess,
output:$bag::[
]
},
{
evalMode: EvalModeError,
result:EvaluationFail
},
]
},
{
name:"outerExceptCoerceList",
statement:"[ 1, 1, 1 ] OUTER EXCEPT ALL [ 1, 2 ]",
assert:{
evalMode:[EvalModeCoerce, EvalModeError],
result:EvaluationSuccess,
output:$bag::[
1,
1,
1,
1,
2
1
]
}
},
Expand Down Expand Up @@ -244,8 +392,8 @@ bagOperators::[
}
},
{
name:"OUTER UNION with ORDER BY LIMIT on children and bag op",
statement:"(SELECT a, tbl FROM t1 ORDER BY a LIMIT 2) OUTER UNION ALL (SELECT a, tbl FROM t2 ORDER BY a LIMIT 2) ORDER BY a LIMIT 2",
name:"SQL UNION with ORDER BY LIMIT on children and bag op",
statement:"(SELECT a, tbl FROM t1 ORDER BY a LIMIT 2) UNION ALL (SELECT a, tbl FROM t2 ORDER BY a LIMIT 2) ORDER BY a LIMIT 2",
assert:{
evalMode:[EvalModeCoerce, EvalModeError],
result:EvaluationSuccess,
Expand All @@ -262,8 +410,8 @@ bagOperators::[
}
},
{
name:"OUTER INTERSECT with ORDER BY LIMIT on children and bag op",
statement:"(SELECT a FROM t1 ORDER BY a LIMIT 4) OUTER INTERSECT ALL (SELECT a FROM t2 ORDER BY a LIMIT 4) ORDER BY a LIMIT 2",
name:"SQL INTERSECT with ORDER BY LIMIT on children and bag op",
statement:"(SELECT a FROM t1 ORDER BY a LIMIT 4) INTERSECT ALL (SELECT a FROM t2 ORDER BY a LIMIT 4) ORDER BY a LIMIT 2",
assert:{
evalMode:[EvalModeCoerce, EvalModeError],
result:EvaluationSuccess,
Expand All @@ -278,8 +426,8 @@ bagOperators::[
}
},
{
name:"OUTER EXCEPT with ORDER BY LIMIT on children and bag op",
statement:"(SELECT a FROM t1 ORDER BY a LIMIT 2) OUTER EXCEPT ALL (SELECT a FROM t2 ORDER BY a LIMIT 2) ORDER BY a LIMIT 2",
name:"SQL EXCEPT with ORDER BY LIMIT on children and bag op",
statement:"(SELECT a FROM t1 ORDER BY a LIMIT 2) EXCEPT ALL (SELECT a FROM t2 ORDER BY a LIMIT 2) ORDER BY a LIMIT 2",
assert:{
evalMode:[EvalModeCoerce, EvalModeError],
result:EvaluationSuccess,
Expand Down
24 changes: 16 additions & 8 deletions partiql-tests-data/eval/rfc/0007.ion
View file
Original file line number Diff line number Diff line change
Expand Up @@ -174,14 +174,22 @@ bag::[
{
name: "Example 6 — Value Coercion; Coercion of single value",
statement: '''SELECT * FROM << 1 >> OUTER UNION 'A' ''',
assert: {
evalMode: [EvalModeCoerce, EvalModeError],
result: EvaluationSuccess,
output: $bag::[
{ "_1": 1 },
"A",
]
}
assert: [
{
evalMode: EvalModeCoerce,
result: EvaluationSuccess,
output: $bag::[
{
"_1": 1
},
"A",
]
},
{
evalMode: EvalModeError,
result: EvaluationFail,
},
]
},
{
name: "Example 6 — Value Coercion",
Expand Down
64 changes: 50 additions & 14 deletions partiql-tests-data/success/syntax/primitives/union-except-intersect.ion
View file
Original file line number Diff line number Diff line change
Expand Up @@ -158,7 +158,7 @@

'order-by-limit-offset-bag-ops'::[
{
name: "UNION SFW children with ORDER BY LIMIT OFFSET",
name: "SQL UNION SFW children with ORDER BY LIMIT OFFSET",
statement: '''
(SELECT a1 FROM b1 ORDER BY c1 LIMIT d1 OFFSET e1)
UNION
Expand All @@ -169,7 +169,7 @@
}
},
{
name: "INTERSECT SFW children with ORDER BY LIMIT OFFSET",
name: "SQL INTERSECT SFW children with ORDER BY LIMIT OFFSET",
statement: '''
(SELECT a1 FROM b1 ORDER BY c1 LIMIT d1 OFFSET e1)
INTERSECT
Expand All @@ -180,7 +180,7 @@
}
},
{
name: "EXCEPT SFW children with ORDER BY LIMIT OFFSET",
name: "SQL EXCEPT SFW children with ORDER BY LIMIT OFFSET",
statement: '''
(SELECT a1 FROM b1 ORDER BY c1 LIMIT d1 OFFSET e1)
EXCEPT
Expand All @@ -191,33 +191,69 @@
}
},
{
name: "nested bag op with ORDER BY LIMIT OFFSET",
name: "nested SQL set op with ORDER BY LIMIT OFFSET",
statement: '''
(
(SELECT a1 FROM b1 ORDER BY c1 LIMIT d1 OFFSET e1)
UNION DISTINCT
(SELECT a2 FROM b2 ORDER BY c2 LIMIT d2 OFFSET e2)
)
OUTER UNION ALL
UNION ALL
(SELECT a3 FROM b3 ORDER BY c3 LIMIT d3 OFFSET e3)
ORDER BY c4 LIMIT d4 OFFSET e4''',
assert: {
result: SyntaxSuccess
}
},
{
name: "deep nested bag ops with ORDER BY LIMIT OFFSET",
name: "bag ops with ORDER BY LIMIT OFFSET in children",
statement: '''
(a UNION b)
INTERSECT
(
c EXCEPT
d UNION ALL (e INTERSECT f ORDER BY g LIMIT h OFFSET i)
)
EXCEPT j
ORDER BY k LIMIT l OFFSET m''',
(SELECT a1 FROM b1 ORDER BY c1 LIMIT d1 OFFSET e1)
OUTER UNION
(SELECT a2 FROM b2 ORDER BY c2 LIMIT d2 OFFSET e2)
OUTER INTERSECT ALL
(SELECT a3 FROM b3 ORDER BY c3 LIMIT d3 OFFSET e3)
OUTER EXCEPT DISTINCT
(SELECT a4 FROM b4 ORDER BY c4 LIMIT d4 OFFSET e4)
''',
assert: {
result: SyntaxSuccess
}
},
{
name: "disallow bag ops with ORDER BY",
statement: '''
(SELECT a1 FROM b1 ORDER BY c1 LIMIT d1 OFFSET e1)
OUTER UNION
(SELECT a2 FROM b2 ORDER BY c2 LIMIT d2 OFFSET e2)
ORDER BY c3
''',
assert: {
result: SyntaxFail
}
},
{
name: "disallow bag ops with LIMIT",
statement: '''
(SELECT a1 FROM b1 ORDER BY c1 LIMIT d1 OFFSET e1)
OUTER UNION
(SELECT a2 FROM b2 ORDER BY c2 LIMIT d2 OFFSET e2)
LIMIT d3
''',
assert: {
result: SyntaxFail
}
},
{
name: "disallow bag ops with OFFSET",
statement: '''
(SELECT a1 FROM b1 ORDER BY c1 LIMIT d1 OFFSET e1)
OUTER UNION
(SELECT a2 FROM b2 ORDER BY c2 LIMIT d2 OFFSET e2)
OFFSET e3
''',
assert: {
result: SyntaxFail
}
},
]

0 comments on commit aa2e51f

Please sign in to comment.