spec-tests.ion

'section-4'::[
    {
        name: "array navigation",
        statement: "[2, 4, 6][1 + 1]",
        assert: {
            evalMode: [EvalModeCoerce, EvalModeError],
            result: EvaluationSuccess,
            output: 6
        }
    },
    {
        name: "tuple navigation with array notation with explicit CAST to string",
        statement: "{'attr': 1, 'b':2}[CAST('at' || 'tr' AS STRING)]",
        assert: {
            evalMode: [EvalModeCoerce, EvalModeError],
            result: EvaluationSuccess,
            output: 1
        }
    },
    {
        name: "tuple navigation with array notation without explicit CAST to string",
        statement: "{'attr': 1, 'b': 2}[v || w]",
        env: {
            v: "at",
            w: "tr"
        },
        assert: [
            {
                evalMode: EvalModeCoerce,
                result: EvaluationSuccess,
                output: $missing::null
            },
            {
                evalMode: EvalModeError,
                result: EvaluationFail,
            },
        ]
    },
    {
        name: "path on string",
        statement: "'not a tuple'.a",
        assert: [
            {
                evalMode: EvalModeCoerce,
                result: EvaluationSuccess,
                output: $missing::null
            },
            {
                evalMode: EvalModeError,
                result: EvaluationFail,
            },
        ]
    },
    {
        name: "tuple navigation missing attribute dot notation",
        statement: "{'a':1, 'b':2}.noSuchAttribute",
        assert: [
            {
                evalMode: EvalModeCoerce,
                result: EvaluationSuccess,
                output: $missing::null
            },
            {
                evalMode: EvalModeError,
                result: EvaluationFail,
            }
        ]
    },
    {
        name: "tuple navigation missing attribute array notation",
        statement: "{'a': 1, 'b': 2}['noSuchAttribute']",
        assert: [
            {
                evalMode: EvalModeCoerce,
                result: EvaluationSuccess,
                output: $missing::null
            },
            {
                evalMode: EvalModeError,
                result: EvaluationFail,
            }
        ]
    },
    {
        name: "array navigation with wrongly typed array index",
        statement: "[1, 2, 3][1.0]",
        assert: [
            {
                evalMode: EvalModeCoerce,
                result: EvaluationSuccess,
                output: $missing::null
            },
            {
                evalMode: EvalModeError,
                result: EvaluationFail,
            }
        ]
    },
    {
        name: "wildcard steps in SELECT clause item",
        statement: "SELECT t.* FROM <<{'a':1, 'b':1}, {'a':2, 'b':2}>> AS t",
        assert: {
            evalMode: [EvalModeCoerce, EvalModeError],
            result: EvaluationSuccess,
            output: $bag::[{a: 1, b: 1}, {a: 2, b: 2}]
        }
    },
]

'section-5'::[
    envs::{
        someOrderedTable: [
            {a: 0, b: 0},
            {a: 1, b: 1}
        ],
        someUnorderedTable: $bag::[
            {a: 0, b: 0},
            {a: 1, b: 1}
        ]
    },
    {
        name: "single source FROM with list and AT clause",
        statement: "SELECT x, y FROM someOrderedTable AS x AT y",
        assert: {
            evalMode: [EvalModeCoerce, EvalModeError],
            result: EvaluationSuccess,
            output: $bag::[{x: {a: 0, b: 0}, y: 0}, {x: {a: 1, b: 1}, y: 1}]
        }
    },
    {
        name: "single source FROM with bag and AT clause",
        statement: "SELECT x, y FROM someUnorderedTable AS x AT y",
        assert: [
            {
                evalMode: EvalModeCoerce,
                result: EvaluationSuccess,
                output: $bag::[{x: {a: 0, b: 0}}, {x: {a: 1, b: 1}}]
            },
            {
                evalMode: EvalModeError,
                result: EvaluationFail,
            }
        ]
    },
    {
        name: "single source FROM with scalar",
        statement: "SELECT x FROM someOrderedTable[0].a AS x",
        assert: {
            evalMode: [EvalModeCoerce, EvalModeError],
            result: EvaluationSuccess,
            output: $bag::[{x: 0}]
        }
    },
    {
        name: "single source FROM with scalar and AT clause",
        statement: "SELECT x, p FROM someOrderedTable[0].a AS x AT p",
        assert: [
            {
                evalMode: EvalModeCoerce,
                result: EvaluationSuccess,
                output: $bag::[{x: 0}]
            },
            {
                evalMode: EvalModeError,
                result: EvaluationFail,
            },
        ]
    },
    {
        name: "single source FROM with tuple",
        statement: "SELECT x FROM {'someKey': 'someValue' } AS x",
        assert: {
            evalMode: [EvalModeCoerce, EvalModeError],
            result: EvaluationSuccess,
            output: $bag::[{x: {someKey: "someValue"}}]
        }
    },
    {
        name: "single source FROM with tuple and AT clause",
        statement: "SELECT x, p FROM {'someKey': 'someValue' } AS x AT p",
        assert: [
            {
                evalMode: EvalModeCoerce,
                result: EvaluationSuccess,
                output: $bag::[{x: {someKey: "someValue"}}]
            },
            {
                evalMode: EvalModeError,
                result: EvaluationFail,
            },
        ]
    },
    {
        name: "single source FROM with absent value null",
        statement: "SELECT x FROM NULL AS x",
        assert: {
            evalMode: [EvalModeCoerce, EvalModeError],
            result: EvaluationSuccess,
            output: $bag::[{x: null}]
        }
    },
    {
        name: "single source FROM with absent value null and AT clause",
        statement: "SELECT x, p FROM NULL AS x AT p",
        assert: [
            {
                evalMode: EvalModeCoerce,
                result: EvaluationSuccess,
                output: $bag::[{x: null}]
            },
            {
                evalMode: EvalModeError,
                result: EvaluationFail,
            },
        ]
    },
    {
        name: "single source FROM with absent value missing",
        statement: "SELECT x FROM MISSING AS x",
        assert: {
            evalMode: [EvalModeCoerce, EvalModeError],
            result: EvaluationSuccess,
            output: $bag::[{}]
        }
    },
    {
        name: "single source FROM with absent value missing and AT clause",
        statement: "SELECT x, p FROM MISSING AS x AT p",
        assert: [
            {
                evalMode: EvalModeCoerce,
                result: EvaluationSuccess,
                output: $bag::[{}]
            },
            {
                evalMode: EvalModeError,
                result: EvaluationFail,
            },
        ]
    },
    {
        name: "ranging over attribute value pairs with UNPIVOT",
        statement: "SELECT price, \"symbol\" FROM UNPIVOT justATuple AS price AT \"symbol\"",
        env: { justATuple: {amzn: 840.05, tdc: 31.06} },
        assert: {
            evalMode: [EvalModeCoerce, EvalModeError],
            result: EvaluationSuccess,
            output: $bag::[{price: 840.05, symbol: "amzn"}, {price: 31.06, symbol: "tdc"}]
        }
    }
]

'section-6'::[
    {
        name: "select value",
        statement: "SELECT VALUE 2*x.a FROM [{'a':1}, {'a':2}, {'a':3}] as x",
        assert: {
            evalMode: [EvalModeCoerce, EvalModeError],
            result: EvaluationSuccess,
            output: $bag::[2, 4, 6]
        }
    },
    {
        name: "select value with tuple constructor",
        statement: "SELECT VALUE {'a':v.a, 'b':v.b} FROM [{'a':1, 'b':1}, {'a':2, 'b':2}] AS v",
        assert: {
            evalMode: [EvalModeCoerce, EvalModeError],
            result: EvaluationSuccess,
            output: $bag::[{a:1, b:1}, {a:2, b:2}]
        }
    },
    {
        name: "tuple constructor and mistyped attribute name",
        statement: "SELECT VALUE {v.a: v.b} FROM [{'a':'legit', 'b':1}, {'a':400, 'b':2}] AS v",
        assert: [
            {
                evalMode: EvalModeCoerce,
                result: EvaluationSuccess,
                output: $bag::[{legit:1}, {}]
            },
            {
                evalMode: EvalModeError,
                result: EvaluationFail,
            },
        ]
    },
    {
        name: "duplicate attribute names",
        statement: "SELECT VALUE {v.a: v.b, v.c: v.d} FROM [{'a':'same', 'b':1, 'c':'same', 'd':2}] AS v",
        assert: {
            evalMode: [EvalModeCoerce, EvalModeError],
            result: EvaluationSuccess,
            output: $bag::[{same:1, same:2}]
        }
    },
    {
        name: "bag constructor",
        statement: "SELECT VALUE <<v.a, v.b>> FROM [{'a':1, 'b':1}, {'a':2, 'b':2}] AS v",
        assert: {
            evalMode: [EvalModeCoerce, EvalModeError],
            result: EvaluationSuccess,
            output: $bag::[$bag::[1, 1], $bag::[2, 2]]
        }
    },
    {
        name: "attribute value evaluates to MISSING",
        statement: "SELECT VALUE {'a':v.a, 'b':v.b} FROM [{'a':1, 'b':1}, {'a':2}] AS v",
        assert: {
            evalMode: [EvalModeCoerce, EvalModeError],
            result: EvaluationSuccess,
            output: $bag::[{a:1, b:1}, {a:2}]
        }
    },
    {
        name: "array element evaluates to MISSING",
        statement: "SELECT VALUE [v.a, v.b] FROM [{'a':1, 'b':1}, {'a':2}] AS v",
        assert: {
            evalMode: [EvalModeCoerce, EvalModeError],
            result: EvaluationSuccess,
            output: $bag::[[1, 1], [2, $missing::null]]
        }
    },
    {
        name: "bag element evaluates to MISSING",
        statement: "SELECT VALUE v.b FROM [{'a':1, 'b':1}, {'a':2}] AS v",
        assert: {
            evalMode: [EvalModeCoerce, EvalModeError],
            result: EvaluationSuccess,
            output: $bag::[1, $missing::null]
        }
    },
    {
        name: "bag element evaluates to MISSING in bag constructor",
        statement: "SELECT VALUE <<v.a, v.b>> FROM [{'a':1, 'b':1}, {'a':2}] AS v",
        assert: {
            evalMode: [EvalModeCoerce, EvalModeError],
            result: EvaluationSuccess,
            output: $bag::[$bag::[1, 1], $bag::[2, $missing::null]]
        }
    },
    {
        name: "pivot into a tuple",
        statement: "PIVOT t.price AT t.sym FROM [{'sym':'tdc', 'price': 31.52}, {'sym': 'amzn', 'price': 840.05}] AS t",
        assert: {
            evalMode: [EvalModeCoerce, EvalModeError],
            result: EvaluationSuccess,
            output: {tdc:31.52, amzn:840.05}
        }
    },
    {
        name: "pivot into a tuple with invalid attribute name",
        statement: "PIVOT t.price AT t.sym FROM [{'sym':25, 'price':31.52}, {'sym':'amzn', 'price':840.05}] AS t",
        assert: {
            evalMode: [EvalModeCoerce, EvalModeError],
            result: EvaluationSuccess,
            output: {amzn: 840.05}
        }
    },
    {
        name: "select variable star with non tuples",
        statement: "SELECT x.* FROM [{'a':1, 'b':1}, {'a':2}, 'foo'] AS x",
        assert: {
            evalMode: [EvalModeCoerce, EvalModeError],
            result: EvaluationSuccess,
            output: $bag::[{a:1, b:1}, {a:2}, {_1:"foo"}]
        }
    },
    {
        name: "unpivot with pivot to analyze attribute names",
        statement: '''SELECT VALUE (PIVOT v AT g
                                  FROM UNPIVOT r AS v AT g
                                  WHERE g LIKE 'co%')
                    FROM sensors AS r''',
        env: {
            sensors: [{no2:0.6, co:0.7, co2:0.5},
                      {no2:0.5, co:0.4, co2:1.3}
                     ]
        },
        assert: {
            evalMode: [EvalModeCoerce, EvalModeError],
            result: EvaluationSuccess,
            output: $bag::[{co:0.7, co2:0.5}, {co:0.4, co2:1.3}]
        }
    },
]

'section-7'::[
    {
        name: "cast and operations with missing argument",
        statement: "SELECT VALUE {'a':3*v.a, 'b':3*(CAST(v.b AS INTEGER))} FROM [{'a':1, 'b':'1'}, {'a':2}] v",
        assert: {
            evalMode: [EvalModeCoerce, EvalModeError],
            result: EvaluationSuccess,
            output: $bag::[{a:3, b:3}, {a:6}]
        }
    },
    {
        name: "missing value in arithmetic expression",
        statement: "5 + missing",
        assert: {
            evalMode: [EvalModeCoerce, EvalModeError],
            result: EvaluationSuccess,
            output: $missing::null
        }
    },
    {
        name: "data type mismatch in comparison expression",
        statement: "5 > 'a'",
        assert: [
            {
                evalMode: EvalModeCoerce,
                result: EvaluationSuccess,
                output: $missing::null
            },
            {
                evalMode: EvalModeError,
                result: EvaluationFail,
            },
        ]
    },
    {
        name: "data type mismatch in logical expression",
        statement: "NOT {a: 1}",
        assert: [
            {
                evalMode: EvalModeCoerce,
                result: EvaluationSuccess,
                output: $missing::null
            },
            {
                evalMode: EvalModeError,
                result: EvaluationFail,
            },
        ]
    },
    {
        name: "equality always returns boolean",
        statement: "5 = 'a'",
        assert: {
            evalMode: [EvalModeCoerce, EvalModeError],
            result: EvaluationSuccess,
            output: false
        }
    },
    {
        name: "equality of scalar null",
        statement: "NULL = NULL",
        assert: {
            evalMode: [EvalModeCoerce, EvalModeError],
            result: EvaluationSuccess,
            output: null
        }
    },
    {
        name: "equality of null in array",
        statement: "[NULL] = [NULL]",
        assert: {
            evalMode: [EvalModeCoerce, EvalModeError],
            result: EvaluationSuccess,
            output: true
        }
    },
    {
        name: "equality of scalar missing",
        statement: "MISSING = MISSING",
        assert: {
            evalMode: [EvalModeCoerce, EvalModeError],
            result: EvaluationSuccess,
            output: $missing::null
        }
    },
    {
        name: "equality of missing in array",
        statement: "[MISSING] = [MISSING]",
        assert: {
            evalMode: [EvalModeCoerce, EvalModeError],
            result: EvaluationSuccess,
            output: true
        }
    },
    {
        name: "equality of arrays of different lengths",
        statement: "[NULL, MISSING] = [NULL]",
        assert: {
            evalMode: [EvalModeCoerce, EvalModeError],
            result: EvaluationSuccess,
            output: false
        }
    },
    {
        name: "equality of equal tuples",
        statement: "{'a':1, 'b':2} = {'b':2, 'a':1}",
        assert: {
            evalMode: [EvalModeCoerce, EvalModeError],
            result: EvaluationSuccess,
            output: true
        }
    },
    {
        name: "equality of different tuples",
        statement: "{'a':1, 'b':2} = {'a':1}",
        assert: {
            evalMode: [EvalModeCoerce, EvalModeError],
            result: EvaluationSuccess,
            output: false
        }
    },
    {
        name: "equality of different tuples with null attribute",
        statement: "{'a':1, 'b':2} = {'a':1, 'b': null}",
        assert: {
            evalMode: [EvalModeCoerce, EvalModeError],
            result: EvaluationSuccess,
            output: false
        }
    },
    {
        name: "equality of same element bags",
        statement: "<<3, 2, 4, 2>> = <<2, 2, 3, 4>>",
        assert: {
            evalMode: [EvalModeCoerce, EvalModeError],
            result: EvaluationSuccess,
            output: true
        }
    },
    {
        name: "equality of different element bags",
        statement: "<<3, 4, 2>> = <<2, 2, 3, 4>>",
        assert: {
            evalMode: [EvalModeCoerce, EvalModeError],
            result: EvaluationSuccess,
            output: false
        }
    },
    {
        name: "equality of mixed equal container types",
        statement: "{'a':[0,1], 'b':2} = {'b':2, 'a':[0,1]}",
        assert: {
            evalMode: [EvalModeCoerce, EvalModeError],
            result: EvaluationSuccess,
            output: true
        }
    },
    {
        name: "equality of mixed non equal container types",
        statement: "{'a':[0,1], 'b':2} = {'b':2, 'a':[0,1,2]}",
        assert: {
            evalMode: [EvalModeCoerce, EvalModeError],
            result: EvaluationSuccess,
            output: false
        }
    },
    {
        name: "equality of mixed non equal container types with null",
        statement: "{'a':[0,1], 'b':2} = {'b':2, 'a':[null,1]}",
        assert: {
            evalMode: [EvalModeCoerce, EvalModeError],
            result: EvaluationSuccess,
            output: false
        }
    },
]

'section-8'::[
    {
        name: "missing and true",
        statement: "MISSING AND TRUE",
        assert: {
            evalMode: [EvalModeCoerce, EvalModeError],
            result: EvaluationSuccess,
            output: null
        }
    },
    {
        name: "null and true",
        statement: "NULL AND TRUE",
        assert: {
            evalMode: [EvalModeCoerce, EvalModeError],
            result: EvaluationSuccess,
            output: null
        }
    },
    {
        name: "WHERE clause eliminating absent values",
        statement: "SELECT VALUE v.a FROM [{'a':1, 'b':true}, {'a':2, 'b':null}, {'a':3}] v WHERE v.b",
        assert: {
            evalMode: [EvalModeCoerce, EvalModeError],
            result: EvaluationSuccess,
            output: $bag::[1]
        }
    },
    {
        name: "null is missing",
        statement: "NULL IS MISSING",
        assert: {
            evalMode: [EvalModeCoerce, EvalModeError],
            result: EvaluationSuccess,
            output: false
        }
    },
    {
        name: "missing is missing",
        statement: "MISSING IS MISSING",
        assert: {
            evalMode: [EvalModeCoerce, EvalModeError],
            result: EvaluationSuccess,
            output: true
        }
    },
]

'section-9'::[
    {
        name: "inner select evaluating to collection with more than one element",
        statement:
            '''
            SELECT o.name AS orderName,
                   (SELECT c.name FROM customers c WHERE c.id=o.custId) AS customerName
            FROM orders o
            ''',
        env: {
            customers : [{id:1, name: "Mary"},
                         {id:2, name: "Helen"},
                         {id:1, name: "John"}
                        ],
            orders : [{custId:1, name: "foo"},
                      {custId:2, name: "bar"}
                     ]
        },
        assert: {
            evalMode: EvalModeCoerce,
            result: EvaluationSuccess,
            output: $bag::[{orderName:"foo"}, {orderName:"bar", customerName:"Helen"}]
        }
    },
]

'section-11'::[
    envs::{
        sensors: [{sensor:1}, {'sensor':2}],
        logs: [{sensor:1, co:0.4}, {sensor:1, co:0.2}, {sensor:2, co:0.3}]
    },
    {
        name: "group by without aggregates",
        statement:
            '''
            SELECT VALUE {'sensor': sensor,
                          'readings': (SELECT VALUE v.l.co FROM g AS v) }
            FROM logs AS l
            GROUP BY l.sensor AS sensor GROUP AS g
        ''',
        env: {
            sensors: [{sensor:1}, {'sensor':2}],
            logs: [{sensor:1, co:0.4}, {sensor:1, co:0.2}, {sensor:2, co:0.3}]
        },
        assert: {
            evalMode: [EvalModeCoerce, EvalModeError],
            result: EvaluationSuccess,
            output: $bag::[{sensor:1, readings:$bag::[0.4, 0.2]},
                           {sensor:2, readings:$bag::[0.3]}
            ]
        }
    },
    {
        name: "coll_count without group by",
        statement: "COLL_COUNT([5, {a:2, b:3}])",
        assert: {
            evalMode: [EvalModeCoerce, EvalModeError],
            result: EvaluationSuccess,
            output: 2
        }
    },
    {
        name: "coll_count with result of subquery",
        statement: "COLL_COUNT(SELECT VALUE x FROM logs x WHERE x.sensor=1)",
        assert: {
            evalMode: [EvalModeCoerce, EvalModeError],
            result: EvaluationSuccess,
            output: 2
        }
    },
    {
        name: "group by with absent values",
        statement:
            '''
            SELECT VALUE {'sensor': sensor,
                          'readings': (SELECT VALUE v.l.co FROM g AS v) }
            FROM logs AS l
            GROUP BY l.sensor AS sensor GROUP AS g
        ''',
        env: {
            logs:[
                {sensor: 1, co:0.4},
                {sensor: 2, co:0.3},
                {sensor: null, co:0.1},
                {sensor: 1, co:0.2},
                {co:0.5}
            ]
        },
        assert: {
            evalMode: [EvalModeCoerce, EvalModeError],
            result: EvaluationSuccess,
            output: $bag::[
                {sensor:1, readings:$bag::[0.4, 0.2]},
                {sensor:2, readings:$bag::[0.3]},
                {sensor:null, readings:$bag::[0.1, 0.5]}
            ]
        }
    },
    {
        name: "group by with differenciated absent values",
        statement:
            '''
            SELECT VALUE {'sensor': CASE WHEN missingFlag THEN MISSING ELSE sensor END,
                          'readings': (SELECT VALUE v.l.co FROM g AS v) }
            FROM logs AS l
            GROUP BY l.sensor IS MISSING AS missingFlag, l.sensor AS sensor GROUP AS g
        ''',
        env: {
            logs:[
                {sensor: 1, co:0.4},
                {sensor: 2, co:0.3},
                {sensor: null, co:0.1},
                {sensor: 1, co:0.2},
                {co:0.5}
            ]
        },
        assert: {
            evalMode: [EvalModeCoerce, EvalModeError],
            result: EvaluationSuccess,
            output: $bag::[
                {sensor:1, readings:$bag::[0.4, 0.2]},
                {sensor:2, readings:$bag::[0.3]},
                {sensor:null, readings:$bag::[0.1]},
                {readings:$bag::[0.5]}
            ]
        }
    },
    {
        name: "windowing simplified with grouping",
        statement:
            '''
            SELECT sensor AS sensor,
                   (WITH orderedReadings
                         AS (SELECT v FROM oneSensorsReadings v ORDER BY v.timestamp)
                    SELECT r.co, r.timestamp
                    FROM orderedReadings r AT p
                    WHERE r.co > 2*orderedReadings[p-1].co
                    ORDER BY p
                    ) AS jumpReadings
            FROM logs l
            GROUP BY l.sensor AS sensor GROUP AS oneSensorsReadings
            ''',
        env: {
            logs: [{sensor:1, co:0.4, timestamp: $time::"04:05:06"},    // ex in spec used an undocumented timestamp
                   {sensor:1, co:0.2, timestamp: $time::"04:05:07"},    // syntax. changed to use time annotation
                   {sensor:1, co:0.5, timestamp: $time::"04:05:10"},    // in the test suite
                   {sensor:2, co:0.3}
            ]
        },
        assert: {
            evalMode: [EvalModeCoerce, EvalModeError],
            result: EvaluationSuccess,
            output: $bag::[
                {sensor:1, jumpReadings:[{co:0.4, timestamp: $time::"04:05:06"}]},
                {sensor:2, jumpReadings:[]}
            ]
        }
    },
]

'section-13'::[
    // TODO: RFC#0007 bag operators (https://github.com/partiql/partiql-docs/blob/main/RFCs/0007-rfc-bag-operators.md)
    // is added to spec, port tests
]

'section-14'::[
    {
        name: "pivot to tuple from collection of tuples",
        statement: "PIVOT x.v AT x.a FROM << {'a': 'first', 'v': 'john'}, {'a': 'last', 'v': 'doe'} >> AS x",
        assert: {
            evalMode: [EvalModeCoerce, EvalModeError],
            result: EvaluationSuccess,
            output: {first:"john", last:"doe"}
        }
    },
]