envs::{ simple_1:[{col1: 1, col2: 10}, {col1: 1, col2: 5}, {col1: 1, col2: 7}, {col1: 5, col2: 7}, {col1: 3, col2: 12}], suppliers:[ { supplierId: 10, supplierName: "Umbrella" }, { supplierId: 11, supplierName: "Initech" } ], products:[ { productId: 1, supplierId: 10, categoryId: 20, price: 5.0, numInStock: 1 }, { productId: 2, supplierId: 10, categoryId: 20, price: 10.0, numInStock: 10 }, { productId: 3, supplierId: 10, categoryId: 21, price: 15.0, numInStock: 100 }, { productId: 4, supplierId: 11, categoryId: 21, price: 5.0, numInStock: 1000 }, { productId: 5, supplierId: 11, categoryId: 21, price: 15.0, numInStock: 10000 } ], products_sparse:[ { productId: 1, categoryId: 20, regionId: 100, supplierId_nulls: 10, supplierId_missings: 10, supplierId_mixed: 10, price_nulls: 1.0, price_missings: 1.0, price_mixed: 1.0 }, { productId: 2, categoryId: 20, regionId: 100, supplierId_nulls: 10, supplierId_missings: 10, supplierId_mixed: 10, price_nulls: 2.0, price_missings: 2.0, price_mixed: 2.0 }, { productId: 3, categoryId: 20, regionId: 200, supplierId_nulls: 10, supplierId_missings: 10, supplierId_mixed: 10, price_nulls: 3.0, price_missings: 3.0, price_mixed: 3.0 }, { productId: 5, categoryId: 21, regionId: 100, supplierId_nulls: null, price_nulls: null }, { productId: 4, categoryId: 20, regionId: 100, supplierId_nulls: null, supplierId_mixed: null, price_nulls: null, price_mixed: null }, { productId: 6, categoryId: 21, regionId: 100, supplierId_nulls: 11, supplierId_missings: 11, supplierId_mixed: 11, price_nulls: 4.0, price_missings: 4.0, price_mixed: 4.0 }, { productId: 7, categoryId: 21, regionId: 200, supplierId_nulls: 11, supplierId_missings: 11, supplierId_mixed: 11, price_nulls: 5.0, price_missings: 5.0, price_mixed: 5.0 }, { productId: 8, categoryId: 21, regionId: 200, supplierId_nulls: null, supplierId_mixed: null, price_nulls: null, price_mixed: null }, { productId: 9, categoryId: 21, regionId: 200, supplierId_nulls: null, price_nulls: null, }, { productId: 10, categoryId: 21, regionId: 200, supplierId_nulls: null, supplierId_mixed: null, price_nulls: null, } ], orders:[ { customerId: 123, sellerId: 1, productId: 11111, cost: 1 }, { customerId: 123, sellerId: 2, productId: 22222, cost: 2 }, { customerId: 123, sellerId: 1, productId: 33333, cost: 3 }, { customerId: 456, sellerId: 2, productId: 44444, cost: 4 }, { customerId: 456, sellerId: 1, productId: 55555, cost: 5 }, { customerId: 456, sellerId: 2, productId: 66666, cost: 6 }, { customerId: 789, sellerId: 1, productId: 77777, cost: 7 }, { customerId: 789, sellerId: 2, productId: 88888, cost: 8 }, { customerId: 789, sellerId: 1, productId: 99999, cost: 9 }, { customerId: 100, sellerId: 2, productId: 10000, cost: 10 } ], differentDataTypes:[ { 'data_value': {} }, { 'data_value': 5 }, { 'data_value': 2017-01-01T00:00-00:00 }, { 'data_value': [] }, { 'data_value': $time::{'hour':12, 'minute':12, 'second':12.1,'timezone_hour':null.int,'timezone_minute':null.int} }, { 'data_value': 'a' }, { 'data_value': null }, { 'data_value': false }, { 'data_value': {{YWFhYWFhYWFhYWFhYf8=}} }, { 'data_value': $date::2021-08-22 }, { 'data_value': $bag::[] }, { 'data_value': {{"aaaaaaaaaaaaa\xFF"}} } ] } simple::[ // should order by col1 asc { name:"col1 asc", statement:"SELECT col1 FROM simple_1 ORDER BY col1", assert:{ evalMode:[EvalModeCoerce, EvalModeError], result:EvaluationSuccess, output:[{'col1': 1}, {'col1': 1}, {'col1': 1}, {'col1': 3}, {'col1': 5}] } }, // should order by col1 desc { name:"col1 desc", statement:"SELECT col1 FROM simple_1 ORDER BY col1 DESC", assert:{ evalMode:[EvalModeCoerce, EvalModeError], result:EvaluationSuccess, output:[{'col1': 5}, {'col1': 3}, {'col1': 1}, {'col1': 1}, {'col1': 1}] } }, // should order by col1 and then col2 asc { name:"col1 asc, col2 asc", statement:"SELECT * FROM simple_1 ORDER BY col1, col2", assert:{ evalMode:[EvalModeCoerce, EvalModeError], result:EvaluationSuccess, output:[{'col1': 1, 'col2': 5}, {'col1': 1, 'col2': 7}, {'col1': 1, 'col2': 10}, {'col1': 3, 'col2': 12}, {'col1': 5, 'col2': 7}] } }, // should order by price desc and productId asc { name:"price desc, productId asc", statement:"SELECT productId, price FROM products ORDER BY price DESC, productId ASC", assert:{ evalMode:[EvalModeCoerce, EvalModeError], result:EvaluationSuccess, output:[{'productId': 3, 'price': 15.0}, {'productId': 5, 'price': 15.0}, {'productId': 2, 'price': 10.0}, {'productId': 1, 'price': 5.0}, {'productId': 4, 'price': 5.0}] } }, // should order by supplierId_nulls nulls last { name:"supplierId_nulls nulls last", statement:"SELECT productId, supplierId_nulls FROM products_sparse ORDER BY supplierId_nulls NULLS LAST, productId", assert:{ evalMode:[EvalModeCoerce, EvalModeError], result:EvaluationSuccess, output:[{'productId': 1, 'supplierId_nulls': 10}, {'productId': 2, 'supplierId_nulls': 10}, {'productId': 3, 'supplierId_nulls': 10}, {'productId': 6, 'supplierId_nulls': 11}, {'productId': 7, 'supplierId_nulls': 11}, {'productId': 4, 'supplierId_nulls': null}, {'productId': 5, 'supplierId_nulls': null}, {'productId': 8, 'supplierId_nulls': null}, {'productId': 9, 'supplierId_nulls': null}, {'productId': 10, 'supplierId_nulls': null}] } }, // should order by supplierId_nulls nulls first { name:"supplierId_nulls nulls first", statement:"SELECT productId, supplierId_nulls FROM products_sparse ORDER BY supplierId_nulls NULLS FIRST, productId", assert:{ evalMode:[EvalModeCoerce, EvalModeError], result:EvaluationSuccess, output:[{'productId': 4, 'supplierId_nulls': null}, {'productId': 5, 'supplierId_nulls': null}, {'productId': 8, 'supplierId_nulls': null}, {'productId': 9, 'supplierId_nulls': null}, {'productId': 10, 'supplierId_nulls': null}, {'productId': 1, 'supplierId_nulls': 10}, {'productId': 2, 'supplierId_nulls': 10}, {'productId': 3, 'supplierId_nulls': 10}, {'productId': 6, 'supplierId_nulls': 11}, {'productId': 7, 'supplierId_nulls': 11}] } }, // should order by nulls last as default for supplierId_nulls asc { name:"supplierId_nulls asc nulls last, productId asc", statement:"SELECT productId, supplierId_nulls FROM products_sparse ORDER BY supplierId_nulls ASC, productId", assert:{ evalMode:[EvalModeCoerce, EvalModeError], result:EvaluationSuccess, output:[{'productId': 1, 'supplierId_nulls': 10}, {'productId': 2, 'supplierId_nulls': 10}, {'productId': 3, 'supplierId_nulls': 10}, {'productId': 6, 'supplierId_nulls': 11}, {'productId': 7, 'supplierId_nulls': 11}, {'productId': 4, 'supplierId_nulls': null}, {'productId': 5, 'supplierId_nulls': null}, {'productId': 8, 'supplierId_nulls': null}, {'productId': 9, 'supplierId_nulls': null}, {'productId': 10, 'supplierId_nulls': null}] } }, // should order by nulls first as default for supplierId_nulls desc { name:"nulls first as default for supplierId_nulls desc", statement:"SELECT productId, supplierId_nulls FROM products_sparse ORDER BY supplierId_nulls DESC, productId", assert:{ evalMode:[EvalModeCoerce, EvalModeError], result:EvaluationSuccess, output:[{'productId': 4, 'supplierId_nulls': null}, {'productId': 5, 'supplierId_nulls': null}, {'productId': 8, 'supplierId_nulls': null}, {'productId': 9, 'supplierId_nulls': null}, {'productId': 10, 'supplierId_nulls': null}, {'productId': 6, 'supplierId_nulls': 11}, {'productId': 7, 'supplierId_nulls': 11}, {'productId': 1, 'supplierId_nulls': 10}, {'productId': 2, 'supplierId_nulls': 10}, {'productId': 3, 'supplierId_nulls': 10}] } }, // order by nulls first for supplierId_nulls desc { name:"nulls first for supplierId_nulls desc", statement:"SELECT productId, supplierId_nulls FROM products_sparse ORDER BY supplierId_nulls DESC NULLS FIRST, productId", assert:{ evalMode:[EvalModeCoerce, EvalModeError], result:EvaluationSuccess, output:[{'productId': 4, 'supplierId_nulls': null}, {'productId': 5, 'supplierId_nulls': null}, {'productId': 8, 'supplierId_nulls': null}, {'productId': 9, 'supplierId_nulls': null}, {'productId': 10, 'supplierId_nulls': null}, {'productId': 6, 'supplierId_nulls': 11}, {'productId': 7, 'supplierId_nulls': 11}, {'productId': 1, 'supplierId_nulls': 10}, {'productId': 2, 'supplierId_nulls': 10}, {'productId': 3, 'supplierId_nulls': 10}] } }, // order by nulls last for supplierId_nulls desc { name:"nulls last for supplierId_nulls desc", statement:"SELECT productId, supplierId_nulls FROM products_sparse ORDER BY supplierId_nulls DESC NULLS LAST, productId", assert:{ evalMode:[EvalModeCoerce, EvalModeError], result:EvaluationSuccess, output:[{'productId': 6, 'supplierId_nulls': 11}, {'productId': 7, 'supplierId_nulls': 11}, {'productId': 1, 'supplierId_nulls': 10}, {'productId': 2, 'supplierId_nulls': 10}, {'productId': 3, 'supplierId_nulls': 10}, {'productId': 4, 'supplierId_nulls': null}, {'productId': 5, 'supplierId_nulls': null}, {'productId': 8, 'supplierId_nulls': null}, {'productId': 9, 'supplierId_nulls': null}, {'productId': 10, 'supplierId_nulls': null}] } }, // should group and order by asc sellerId { name:"group and order by asc sellerId", statement:"SELECT sellerId FROM orders GROUP BY sellerId ORDER BY sellerId ASC", assert:{ evalMode:[EvalModeCoerce, EvalModeError], result:EvaluationSuccess, output:[{'sellerId': 1}, {'sellerId': 2}] } }, // should group and order by desc sellerId { name:"group and order by desc sellerId", statement:"SELECT sellerId FROM orders GROUP BY sellerId ORDER BY sellerId DESC", assert:{ evalMode:[EvalModeCoerce, EvalModeError], result:EvaluationSuccess, output:[{'sellerId': 2}, {'sellerId': 1}] } }, // should group and order by DESC (NULLS FIRST as default) { name:"group and order by DESC (NULLS FIRST as default)", statement:"SELECT supplierId_nulls FROM products_sparse GROUP BY supplierId_nulls ORDER BY supplierId_nulls DESC", assert:{ evalMode:[EvalModeCoerce, EvalModeError], result:EvaluationSuccess, output: [{'supplierId_nulls': null}, {'supplierId_nulls': 11}, {'supplierId_nulls': 10}] } }, // should group and order by ASC (NULLS LAST as default) { name:"group and order by ASC (NULLS LAST as default)", statement:"SELECT supplierId_nulls FROM products_sparse GROUP BY supplierId_nulls ORDER BY supplierId_nulls ASC", assert:{ evalMode:[EvalModeCoerce, EvalModeError], result:EvaluationSuccess, output:[{'supplierId_nulls': 10}, {'supplierId_nulls': 11}, {'supplierId_nulls': null}] } }, // should group and place nulls first (asc as default) { name:"group and place nulls first (asc as default)", statement:"SELECT supplierId_nulls FROM products_sparse GROUP BY supplierId_nulls ORDER BY supplierId_nulls NULLS FIRST", assert:{ evalMode:[EvalModeCoerce, EvalModeError], result:EvaluationSuccess, output:[{'supplierId_nulls': null}, {'supplierId_nulls': 10}, {'supplierId_nulls': 11}] } }, // should group and place nulls last (asc as default) { name:"group and place nulls last (asc as default)", statement:"SELECT supplierId_nulls FROM products_sparse GROUP BY supplierId_nulls ORDER BY supplierId_nulls NULLS LAST", assert:{ evalMode:[EvalModeCoerce, EvalModeError], result:EvaluationSuccess, output:[{'supplierId_nulls': 10}, {'supplierId_nulls': 11}, {'supplierId_nulls': null}] } }, // should group and order by asc and place nulls first { name:"group and order by asc and place nulls first", statement:"SELECT supplierId_nulls FROM products_sparse GROUP BY supplierId_nulls ORDER BY supplierId_nulls ASC NULLS FIRST", assert:{ evalMode:[EvalModeCoerce, EvalModeError], result:EvaluationSuccess, output:[{'supplierId_nulls': null}, {'supplierId_nulls': 10}, {'supplierId_nulls': 11}] } }, ] data_types::[ // DIFFERENT DATA TYPES // should order different data types by following order bool, numbers, date, time, timestamp, text, LOB Types, lists, struct, bag // handling nulls/missing can be change by ordering spec(if nulls spec is not specified, NULLS FIRST is default for asc, NULLS LAST default for desc) or nulls spec { name:"should order data types by the specifications (NULLS LAST default for asc)", statement:"SELECT * FROM differentDataTypes ORDER BY data_value", assert:{ evalMode:[EvalModeCoerce, EvalModeError], result:EvaluationSuccess, output:[{'data_value': false}, {'data_value': 5}, {'data_value': $date::2021-08-22}, {'data_value': $time::{'hour':12, 'minute':12, 'second':12.1,'timezone_hour':null.int,'timezone_minute':null.int} }, {'data_value': 2017-01-01T00:00-00:00}, {'data_value': 'a'}, {'data_value': {{YWFhYWFhYWFhYWFhYf8=}}}, {'data_value': {{"aaaaaaaaaaaaa\xff"}}}, {'data_value': []}, {'data_value': {}}, {'data_value': $bag::[]}, {'data_value': null}] } }, { name:"should order data types by the specifications (NULLS FIRST default for desc)", statement:"SELECT * FROM differentDataTypes ORDER BY data_value DESC", assert:{ evalMode:[EvalModeCoerce, EvalModeError], result:EvaluationSuccess, output:[{'data_value': null}, {'data_value': $bag::[]}, {'data_value': {}}, {'data_value': []}, {'data_value': {{YWFhYWFhYWFhYWFhYf8=}}}, {'data_value': {{"aaaaaaaaaaaaa\xff"}}}, {'data_value': 'a'}, {'data_value': 2017-01-01T00:00-00:00}, {'data_value': $time::{'hour':12, 'minute':12, 'second':12.1,'timezone_hour':null.int,'timezone_minute':null.int}}, {'data_value': $date::2021-08-22}, {'data_value': 5}, {'data_value': false}] } }, { name:"should order data types by the specifications (nulls should be first due to nulls spec)", statement:"SELECT * FROM differentDataTypes ORDER BY data_value NULLS FIRST", assert:{ evalMode:[EvalModeCoerce, EvalModeError], result:EvaluationSuccess, output:[{'data_value': null}, {'data_value': false}, {'data_value': 5}, {'data_value': $date::2021-08-22}, {'data_value': $time::{'hour':12, 'minute':12, 'second':12.1,'timezone_hour':null.int,'timezone_minute':null.int}}, {'data_value': 2017-01-01T00:00-00:00}, {'data_value': 'a'}, {'data_value': {{YWFhYWFhYWFhYWFhYf8=}}}, {'data_value': {{"aaaaaaaaaaaaa\xff"}}}, {'data_value': []}, {'data_value': {}}, {'data_value': $bag::[]}] } }, { name:"should order data types by the specifications (nulls should be last due to nulls spec)", statement:"SELECT * FROM differentDataTypes ORDER BY data_value NULLS LAST", assert:{ evalMode:[EvalModeCoerce, EvalModeError], result:EvaluationSuccess, output:[{'data_value': false}, {'data_value': 5}, {'data_value': $date::2021-08-22}, {'data_value': $time::{'hour':12, 'minute':12, 'second':12.1,'timezone_hour':null.int,'timezone_minute':null.int}}, {'data_value': 2017-01-01T00:00-00:00}, {'data_value': 'a'}, {'data_value': {{YWFhYWFhYWFhYWFhYf8=}}}, {'data_value': {{"aaaaaaaaaaaaa\xff"}}}, {'data_value': []}, {'data_value': {}}, {'data_value': $bag::[]}, {'data_value': null}] } }, ] edge_cases::[ // false before true (ASC) { name:"false before true (ASC)", statement:"SELECT * FROM [{ 'a': false }, { 'a': true }, { 'a': true }, { 'a': false }] ORDER BY a", assert:{ evalMode:[EvalModeCoerce, EvalModeError], result:EvaluationSuccess, output:[{'a': false}, {'a': false}, {'a': true}, {'a': true}] } }, // true before false (DESC) { name:"true before false (DESC)", statement:"SELECT * FROM [{ 'a': false }, { 'a': true }, { 'a': true }, { 'a': false }] ORDER BY a DESC", assert:{ evalMode:[EvalModeCoerce, EvalModeError], result:EvaluationSuccess, output:[{'a': true}, {'a': true}, {'a': false}, {'a': false}] } }, // nan before -inf, then numeric values then +inf (ASC) { name:"nan before -inf, then numeric values then +inf (ASC)", statement:"SELECT * FROM [{ 'a': 5 }, { 'a': -5e-1 }, { 'a': `-inf` }, { 'a': `nan` }, { 'a': 7 }, { 'a': `+inf` }, { 'a': 9 }] ORDER BY a", assert:{ evalMode:[EvalModeCoerce, EvalModeError], result:EvaluationSuccess, output:[{'a': nan}, {'a': -inf}, {'a': -0.5}, {'a': 5}, {'a': 7}, {'a': 9}, {'a': +inf}] } }, // +inf before numeric values then -inf then nan (DESC) { name:"+inf before numeric values then -inf then nan (DESC)", statement:"SELECT * FROM [{ 'a': 5 }, { 'a': -5e-1 }, { 'a': `-inf` }, { 'a': `nan` }, { 'a': 7 }, { 'a': `+inf` }, { 'a': 9 }] ORDER BY a DESC", assert:{ evalMode:[EvalModeCoerce, EvalModeError], result:EvaluationSuccess, output:[{'a': +inf}, {'a': 9}, {'a': 7}, {'a': 5}, {'a': -0.5}, {'a': -inf}, {'a': nan}] } }, // text types compared by lexicographical ordering of Unicode scalar (ASC) { name:"text types compared by lexicographical ordering of Unicode scalar (ASC)", statement:'''SELECT * FROM [{ 'a': `'\uD83D\uDCA9'`}, { 'a': 'Z'}, { 'a': '9' }, { 'a': 'A'}, { 'a': `"\U0001F4A9"`}, { 'a': 'a'}, { 'a': 'z'}, { 'a': '0' }] ORDER BY a''', assert:{ evalMode:[EvalModeCoerce, EvalModeError], result:EvaluationSuccess, output:[{'a': '0'}, {'a': '9'}, {'a': 'A'}, {'a': 'Z'}, {'a': 'a'}, {'a': 'z'}, {'a': '\uD83D\uDCA9'}, {'a': "\U0001F4A9"}] } }, // text types compared by lexicographical ordering of Unicode scalar (DESC) { name:"text types compared by lexicographical ordering of Unicode scalar (DESC)", statement:'''SELECT * FROM [{ 'a': `'\uD83D\uDCA9'`}, { 'a': 'Z'}, { 'a': '9' }, { 'a': 'A'}, { 'a': `"\U0001F4A9"`}, { 'a': 'a'}, { 'a': 'z'}, { 'a': '0' }] ORDER BY a DESC''', assert:{ evalMode:[EvalModeCoerce, EvalModeError], result:EvaluationSuccess, output:[{'a': '\uD83D\uDCA9'}, {'a': "\U0001F4A9"}, {'a': 'z'}, {'a': 'a'}, {'a': 'Z'}, {'a': 'A'}, {'a': '9'}, {'a': '0'}] } }, // LOB types follow their lexicographical ordering by octet (ASC) { name:"LOB types follow their lexicographical ordering by octet (ASC)", statement:'''SELECT * FROM [{'a': `{{"Z"}}`}, {'a': `{{"a"}}`}, {'a': `{{"A"}}`}, {'a': `{{"z"}}`}] ORDER BY a''', assert:{ evalMode:[EvalModeCoerce, EvalModeError], result:EvaluationSuccess, output:[{'a': {{"A"}}}, {'a': {{"Z"}}}, {'a': {{"a"}}}, {'a': {{"z"}}}] } }, // LOB types should ordered (DESC) { name:"LOB types should ordered (DESC)", statement:'''SELECT * FROM [{'a': `{{"Z"}}`}, {'a': `{{"a"}}`}, {'a': `{{"A"}}`}, {'a': `{{"z"}}`}] ORDER BY a DESC''', assert:{ evalMode:[EvalModeCoerce, EvalModeError], result:EvaluationSuccess, output:[{'a': {{"z"}}}, {'a': {{"a"}}}, {'a': {{"Z"}}}, {'a': {{"A"}}}] } }, // shorter array comes first (ASC) { name:"shorter array comes first (ASC)", statement:"SELECT * FROM [ {'a': [1, 2, 3, 4]}, {'a': [1, 2]}, {'a': [1, 2, 3]}, {'a': []}] ORDER BY a", assert:{ evalMode:[EvalModeCoerce, EvalModeError], result:EvaluationSuccess, output:[{'a': []}, {'a': [1, 2]}, {'a': [1, 2, 3]}, {'a': [1, 2, 3, 4]}] } }, // longer array comes first (DESC) { name:"longer array comes first (DESC)", statement:"SELECT * FROM [ {'a': [1, 2, 3, 4]}, {'a': [1, 2]}, {'a': [1, 2, 3]}, {'a': []}] ORDER BY a DESC", assert:{ evalMode:[EvalModeCoerce, EvalModeError], result:EvaluationSuccess, output:[{'a': [1, 2, 3, 4]}, {'a': [1, 2, 3]}, {'a': [1, 2]}, {'a': []}] } }, // lists compared lexicographically based on comparison of elements (ASC) { name:"lists compared lexicographically based on comparison of elements (ASC)", statement:"SELECT * FROM [ {'a': ['b', 'a']}, {'a': ['a', 'b']}, {'a': ['b', 'c']}, {'a': ['a', 'c']}] ORDER BY a", assert:{ evalMode:[EvalModeCoerce, EvalModeError], result:EvaluationSuccess, output:[{'a': ["a", "b"]}, {'a': ["a", "c"]}, {'a': ["b", "a"]}, {'a': ["b", "c"]}] } }, // lists compared lexicographically based on comparison of elements (DESC) { name:"lists compared lexicographically based on comparison of elements (DESC)", statement:"SELECT * FROM [ {'a': ['b', 'a']}, {'a': ['a', 'b']}, {'a': ['b', 'c']}, {'a': ['a', 'c']}] ORDER BY a DESC", assert:{ evalMode:[EvalModeCoerce, EvalModeError], result:EvaluationSuccess, output:[{'a': ["b", "c"]}, {'a': ["b", "a"]}, {'a': ["a", "c"]}, {'a': ["a", "b"]}] } }, // lists items should be ordered by data types (ASC) (nulls last as default for asc) { name:"lists items should be ordered by data types (ASC) (nulls last as default for asc)", statement:"SELECT * FROM [{'a': ['a']}, {'a': [1]}, {'a': [true]}, {'a': [null]}, {'a': [{}]}, {'a': [<<>>]}, {'a': [`{{}}`]}, {'a': [[]]} ] ORDER BY a", assert:{ evalMode:[EvalModeCoerce, EvalModeError], result:EvaluationSuccess, output:[{'a': [true]}, {'a': [1]}, {'a': ["a"]}, {'a': [{{}}]}, {'a': [[]]}, {'a': [{}]}, {'a': [$bag::[]]}, {'a': [null]}] } }, // lists items should be ordered by data types (DESC) (nulls first as default for desc) { name:"lists items should be ordered by data types (DESC) (nulls first as default for desc)", statement:"SELECT * FROM [{'a': ['a']}, {'a': [1]}, {'a': [true]}, {'a': [null]}, {'a': [{}]}, {'a': [<<>>]}, {'a': [`{{}}`]}, {'a': [[]]} ] ORDER BY a DESC", assert:{ evalMode:[EvalModeCoerce, EvalModeError], result:EvaluationSuccess, output:[{'a': [null]}, {'a': [$bag::[]]}, {'a': [{}]}, {'a': [[]]}, {'a': [{{}}]}, {'a': ["a"]}, {'a': [1]}, {'a': [true]}] } }, // structs compared lexicographically first by key then by value (ASC) { name:"structs compared lexicographically first by key then by value (ASC)", statement:"SELECT * FROM [{'a': {'b': 'a'}}, {'a': {'a': 'b'}}, {'a': {'b': 'c'}}, {'a': {'a': 'c'}}] ORDER BY a", assert:{ evalMode:[EvalModeCoerce, EvalModeError], result:EvaluationSuccess, output:[{'a': {'a': "b"}}, {'a': {'a': "c"}}, {'a': {'b': "a"}}, {'a': {'b': "c"}}] } }, // structs compared lexicographically first by key then by value (DESC) { name:"structs compared lexicographically first by key then by value (DESC)", statement:"SELECT * FROM [{'a': {'b': 'a'}}, {'a': {'a': 'b'}}, {'a': {'b': 'c'}}, {'a': {'a': 'c'}}] ORDER BY a DESC", assert:{ evalMode:[EvalModeCoerce, EvalModeError], result:EvaluationSuccess, output:[{'a': {'b': "c"}}, {'a': {'b': "a"}}, {'a': {'a': "c"}}, {'a': {'a': "b"}}] } }, // structs should be ordered by data types (ASC) (nulls last as default for asc) { name:"structs should be ordered by data types (ASC) (nulls last as default for asc)", statement:"SELECT * FROM [{'a': {'a': 5}}, {'a': {'a': 'b'}}, {'a': {'a': true}}, {'a': {'a': []}}, {'a': {'a': {}}}, {'a': {'a': <<>>}}, {'a': {'a': `{{}}`}}, {'a': {'a': null}}] ORDER BY a", assert:{ evalMode:[EvalModeCoerce, EvalModeError], result:EvaluationSuccess, output:[{'a': {'a': true}}, {'a': {'a': 5}}, {'a': {'a': "b"}}, {'a': {'a': {{}}}}, {'a': {'a': []}}, {'a': {'a': {}}}, {'a': {'a': $bag::[]}}, {'a': {'a': null}}] } }, // structs should be ordered by data types (DESC) (nulls first as default for desc) { name:"structs should be ordered by data types (DESC) (nulls first as default for desc)", statement:"SELECT * FROM [{'a': {'a': 5}}, {'a': {'a': 'b'}}, {'a': {'a': true}}, {'a': {'a': []}}, {'a': {'a': {}}}, {'a': {'a': <<>>}}, {'a': {'a': `{{}}`}}, {'a': {'a': null}}] ORDER BY a DESC", assert:{ evalMode:[EvalModeCoerce, EvalModeError], result:EvaluationSuccess, output:[{'a': {'a': null}}, {'a': {'a': $bag::[]}}, {'a': {'a': {}}}, {'a': {'a': []}}, {'a': {'a': {{}}}}, {'a': {'a': "b"}}, {'a': {'a': 5}}, {'a': {'a': true}}] } }, // bags compared as sorted lists (ASC) { name:"bags compared as sorted lists (ASC)", statement:"SELECT * FROM [{'a': <<5>>}, {'a': <<1>>}, {'a': <<10>>}] ORDER BY a", assert:{ evalMode:[EvalModeCoerce, EvalModeError], result:EvaluationSuccess, output:[{'a': $bag::[1]}, {'a': $bag::[5]}, {'a': $bag::[10]}] } }, // bags compared as sorted lists (DESC) { name:"bags compared as sorted lists (DESC)", statement:"SELECT * FROM [{'a': <<5>>}, {'a': <<1>>}, {'a': <<10>>}] ORDER BY a DESC", assert:{ evalMode:[EvalModeCoerce, EvalModeError], result:EvaluationSuccess, output:[{'a': $bag::[10]}, {'a': $bag::[5]}, {'a': $bag::[1]}] } }, // testing alias support { name:"testing alias support", statement:"SELECT a AS b FROM [{'a': <<5>>}, {'a': <<1>>}, {'a': <<10>>}] ORDER BY b DESC", assert:{ evalMode:[EvalModeCoerce, EvalModeError], result:EvaluationSuccess, output:[{'b': $bag::[10]}, {'b': $bag::[5]}, {'b': $bag::[1]}] } }, // testing nested alias support { name:"testing nested alias support", statement:"SELECT b AS \"C\" FROM (SELECT a AS b FROM [{'a': <<5>>}, {'a': <<1>>}, {'a': <<10>>}] ORDER BY b DESC) ORDER BY \"C\"", assert:{ evalMode:[EvalModeCoerce, EvalModeError], result:EvaluationSuccess, output:[{'C': $bag::[1]}, {'C': $bag::[5]}, {'C': $bag::[10]}] } }, // Empty Output (ordered) { name:"Empty Output (ordered)", statement:"SELECT * FROM <<>> ORDER BY true", assert:{ evalMode:[EvalModeCoerce, EvalModeError], result:EvaluationSuccess, output:[] } }, // Empty Projection item (ordered) -- Output (unordered) { name:"Empty Projection item (ordered) -- Output (unordered)", statement:"SELECT (SELECT * FROM <<>> ORDER BY true) AS ordered FROM <<0>>", assert:{ evalMode:[EvalModeCoerce, EvalModeError], result:EvaluationSuccess, output:$bag::[ { 'ordered': [] } ] } }, // Empty Projection item (unordered) -- Output (ordered) { name:"Empty Projection item (unordered) -- Output (ordered)", statement:"SELECT (SELECT * FROM <<>>) AS ordered FROM <<0>> ORDER BY true", assert:{ evalMode:[EvalModeCoerce, EvalModeError], result:EvaluationSuccess, output:[ { 'ordered': $bag::[] } ] } }, // Empty Projection item (ordered) -- Output (ordered) { name:"Empty Projection item (ordered) -- Output (ordered)", statement:"SELECT (SELECT * FROM <<>> ORDER BY true) AS ordered FROM <<0>> ORDER BY true", assert:{ evalMode:[EvalModeCoerce, EvalModeError], result:EvaluationSuccess, output:[ { 'ordered': [] } ] } }, // Empty Projection item (unordered) -- Output (unordered) { name:"Empty Projection item (unordered) -- Output (unordered)", statement:"SELECT (SELECT * FROM <<>>) AS ordered FROM <<0>>", assert:{ evalMode:[EvalModeCoerce, EvalModeError], result:EvaluationSuccess, output:$bag::[ { 'ordered': $bag::[] } ] } }, ]