diff options
| author | robot-piglet <[email protected]> | 2024-12-27 12:59:19 +0300 |
|---|---|---|
| committer | robot-piglet <[email protected]> | 2024-12-27 13:12:33 +0300 |
| commit | c7a2519eaf60067fe973fc27f3f0c3c7d943bae9 (patch) | |
| tree | 0d59847bdc681d374545fdfd0c7989f2f5a98e29 /yql/essentials/tests/sql/suites | |
| parent | 27df3d52304f343a3e9e87aa36b014a7a442f94e (diff) | |
Intermediate changes
commit_hash:170e724b371baded6fdcbc5f6b63da52252c1598
Diffstat (limited to 'yql/essentials/tests/sql/suites')
139 files changed, 1242 insertions, 0 deletions
diff --git a/yql/essentials/tests/sql/suites/action/large_evaluate_for_fail.cfg b/yql/essentials/tests/sql/suites/action/large_evaluate_for_fail.cfg new file mode 100644 index 00000000000..5dae597903c --- /dev/null +++ b/yql/essentials/tests/sql/suites/action/large_evaluate_for_fail.cfg @@ -0,0 +1 @@ +xfail diff --git a/yql/essentials/tests/sql/suites/action/large_evaluate_for_fail.sql b/yql/essentials/tests/sql/suites/action/large_evaluate_for_fail.sql new file mode 100644 index 00000000000..539bda0b60f --- /dev/null +++ b/yql/essentials/tests/sql/suites/action/large_evaluate_for_fail.sql @@ -0,0 +1,6 @@ +/* syntax version 1 */ +/* postgres can not */ +/* custom error: Too large list for EVALUATE FOR, allowed: 3, got: 10 */ +pragma config.flags("EvaluateForLimit", "3"); + +evaluate for $_i in ListFromRange(0, 10) do empty_action(); diff --git a/yql/essentials/tests/sql/suites/agg_apply/avg_decimal.sql b/yql/essentials/tests/sql/suites/agg_apply/avg_decimal.sql new file mode 100644 index 00000000000..96f49ebf5d0 --- /dev/null +++ b/yql/essentials/tests/sql/suites/agg_apply/avg_decimal.sql @@ -0,0 +1,7 @@ +/* syntax version 1 */ +/* postgres can not */ +pragma EmitAggApply; + +SELECT + avg(key) +FROM (values (Decimal("0.1",10,1)),(Decimal("0.2",10,1)),(Decimal("0.3",10,1))) as a(key)
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/agg_apply/avg_interval.sql b/yql/essentials/tests/sql/suites/agg_apply/avg_interval.sql new file mode 100644 index 00000000000..d87106a9912 --- /dev/null +++ b/yql/essentials/tests/sql/suites/agg_apply/avg_interval.sql @@ -0,0 +1,7 @@ +/* syntax version 1 */ +/* postgres can not */ +pragma EmitAggApply; + +SELECT + avg(key) +FROM (values (Interval("P1D")),(Interval("P2D")),(Interval("P3D"))) as a(key)
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/agg_apply/avg_numeric.sql b/yql/essentials/tests/sql/suites/agg_apply/avg_numeric.sql new file mode 100644 index 00000000000..8e5ae7410c1 --- /dev/null +++ b/yql/essentials/tests/sql/suites/agg_apply/avg_numeric.sql @@ -0,0 +1,7 @@ +/* syntax version 1 */ +/* postgres can not */ +pragma EmitAggApply; + +SELECT + avg(key) +FROM (values (1),(2),(3)) as a(key)
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/agg_apply/avg_numeric_opt.sql b/yql/essentials/tests/sql/suites/agg_apply/avg_numeric_opt.sql new file mode 100644 index 00000000000..7f1249d119a --- /dev/null +++ b/yql/essentials/tests/sql/suites/agg_apply/avg_numeric_opt.sql @@ -0,0 +1,7 @@ +/* syntax version 1 */ +/* postgres can not */ +pragma EmitAggApply; + +SELECT + avg(key) +FROM (values (1),(null),(3)) as a(key)
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/agg_apply/avg_state_type.sql b/yql/essentials/tests/sql/suites/agg_apply/avg_state_type.sql new file mode 100644 index 00000000000..67e79977569 --- /dev/null +++ b/yql/essentials/tests/sql/suites/agg_apply/avg_state_type.sql @@ -0,0 +1,13 @@ +/* syntax version 1 */ +/* postgres can not */ +pragma EmitAggApply; + +select +formattype(typeof(avg(null))), +formattype(typeof(avg(1))), +formattype(typeof(avg(decimal("10",10,1)))), +formattype(typeof(avg(interval("P10D")))), +formattype(typeof(avg(just(1)))), +formattype(typeof(avg(just(decimal("10",10,1))))), +formattype(typeof(avg(just(interval("P10D"))))) +from (select 1) group by () with combine
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/agg_apply/count.sql b/yql/essentials/tests/sql/suites/agg_apply/count.sql new file mode 100644 index 00000000000..4d2917715e6 --- /dev/null +++ b/yql/essentials/tests/sql/suites/agg_apply/count.sql @@ -0,0 +1,7 @@ +/* syntax version 1 */ +/* postgres can not */ +pragma EmitAggApply; + +SELECT + count(*),count(key) +FROM (values (1),(null),(3)) as a(key)
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/agg_apply/max.sql b/yql/essentials/tests/sql/suites/agg_apply/max.sql new file mode 100644 index 00000000000..70895a37807 --- /dev/null +++ b/yql/essentials/tests/sql/suites/agg_apply/max.sql @@ -0,0 +1,7 @@ +/* syntax version 1 */ +/* postgres can not */ +pragma EmitAggApply; + +SELECT + max(key) +FROM (values (1),(2),(3)) as a(key)
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/agg_apply/min.sql b/yql/essentials/tests/sql/suites/agg_apply/min.sql new file mode 100644 index 00000000000..60b25ff5c54 --- /dev/null +++ b/yql/essentials/tests/sql/suites/agg_apply/min.sql @@ -0,0 +1,7 @@ +/* syntax version 1 */ +/* postgres can not */ +pragma EmitAggApply; + +SELECT + min(key) +FROM (values (1),(2),(3)) as a(key)
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/agg_apply/opt_sum_divide_by_zero.sql b/yql/essentials/tests/sql/suites/agg_apply/opt_sum_divide_by_zero.sql new file mode 100644 index 00000000000..15236cd2ff9 --- /dev/null +++ b/yql/essentials/tests/sql/suites/agg_apply/opt_sum_divide_by_zero.sql @@ -0,0 +1,7 @@ +/* syntax version 1 */ +/* postgres can not */ +pragma EmitAggApply; + +SELECT + sum(1/0) +FROM (values (1),(null),(3)) as a(key) diff --git a/yql/essentials/tests/sql/suites/agg_apply/opt_sum_null.sql b/yql/essentials/tests/sql/suites/agg_apply/opt_sum_null.sql new file mode 100644 index 00000000000..c2086f7f5be --- /dev/null +++ b/yql/essentials/tests/sql/suites/agg_apply/opt_sum_null.sql @@ -0,0 +1,7 @@ +/* syntax version 1 */ +/* postgres can not */ +pragma EmitAggApply; + +SELECT + sum(null) +FROM (values (1),(null),(3)) as a(key) diff --git a/yql/essentials/tests/sql/suites/agg_apply/some_notnull.sql b/yql/essentials/tests/sql/suites/agg_apply/some_notnull.sql new file mode 100644 index 00000000000..7e3cd4fb3ce --- /dev/null +++ b/yql/essentials/tests/sql/suites/agg_apply/some_notnull.sql @@ -0,0 +1,7 @@ +/* syntax version 1 */ +/* postgres can not */ +pragma EmitAggApply; + +SELECT + some(key) +FROM (values (1)) as a(key)
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/agg_apply/some_null.sql b/yql/essentials/tests/sql/suites/agg_apply/some_null.sql new file mode 100644 index 00000000000..2bf12e30d91 --- /dev/null +++ b/yql/essentials/tests/sql/suites/agg_apply/some_null.sql @@ -0,0 +1,7 @@ +/* syntax version 1 */ +/* postgres can not */ +pragma EmitAggApply; + +SELECT + some(key) +FROM (values (null)) as a(key)
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/agg_apply/sum_decimal.sql b/yql/essentials/tests/sql/suites/agg_apply/sum_decimal.sql new file mode 100644 index 00000000000..5ba05328dcd --- /dev/null +++ b/yql/essentials/tests/sql/suites/agg_apply/sum_decimal.sql @@ -0,0 +1,7 @@ +/* syntax version 1 */ +/* postgres can not */ +pragma EmitAggApply; + +SELECT + sum(key) +FROM (values (CAST("1.51" AS Decimal(10, 3))), (CAST("2.22" AS Decimal(10, 3))), (CAST("3.49" AS Decimal(10, 3)))) as a(key)
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/agg_apply/sum_decimal_null.sql b/yql/essentials/tests/sql/suites/agg_apply/sum_decimal_null.sql new file mode 100644 index 00000000000..75df4277f20 --- /dev/null +++ b/yql/essentials/tests/sql/suites/agg_apply/sum_decimal_null.sql @@ -0,0 +1,7 @@ +/* syntax version 1 */ +/* postgres can not */ +pragma EmitAggApply; + +SELECT + sum(key) +FROM (values (CAST("1.51" AS Decimal(10, 3))), (null), (CAST("3.49" AS Decimal(10, 3)))) as a(key)
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/agg_apply/sum_double.sql b/yql/essentials/tests/sql/suites/agg_apply/sum_double.sql new file mode 100644 index 00000000000..698e215e5c5 --- /dev/null +++ b/yql/essentials/tests/sql/suites/agg_apply/sum_double.sql @@ -0,0 +1,7 @@ +/* syntax version 1 */ +/* postgres can not */ +pragma EmitAggApply; + +SELECT + sum(key) +FROM (values (1.51),(null),(3.49)) as a(key)
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/agg_apply/sum_interval.sql b/yql/essentials/tests/sql/suites/agg_apply/sum_interval.sql new file mode 100644 index 00000000000..b08de4bb54e --- /dev/null +++ b/yql/essentials/tests/sql/suites/agg_apply/sum_interval.sql @@ -0,0 +1,7 @@ +/* syntax version 1 */ +/* postgres can not */ +pragma EmitAggApply; + +SELECT + sum(key) +FROM (values (cast(1 as Interval)),(null),(cast(3 as Interval))) as a(key)
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/agg_apply/sum_signed.sql b/yql/essentials/tests/sql/suites/agg_apply/sum_signed.sql new file mode 100644 index 00000000000..ab731a9042f --- /dev/null +++ b/yql/essentials/tests/sql/suites/agg_apply/sum_signed.sql @@ -0,0 +1,7 @@ +/* syntax version 1 */ +/* postgres can not */ +pragma EmitAggApply; + +SELECT + sum(key) +FROM (values (1),(null),(3)) as a(key)
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/agg_apply/sum_unsigned.sql b/yql/essentials/tests/sql/suites/agg_apply/sum_unsigned.sql new file mode 100644 index 00000000000..1fb4103665e --- /dev/null +++ b/yql/essentials/tests/sql/suites/agg_apply/sum_unsigned.sql @@ -0,0 +1,7 @@ +/* syntax version 1 */ +/* postgres can not */ +pragma EmitAggApply; + +SELECT + sum(key) +FROM (values (1u),(null),(3u)) as a(key)
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/aggregate/yql-18511.cfg b/yql/essentials/tests/sql/suites/aggregate/yql-18511.cfg new file mode 100644 index 00000000000..bb349dd8abd --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/yql-18511.cfg @@ -0,0 +1 @@ +providers yt diff --git a/yql/essentials/tests/sql/suites/aggregate/yql-18511.sql b/yql/essentials/tests/sql/suites/aggregate/yql-18511.sql new file mode 100644 index 00000000000..e0d2d2b0d4c --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/yql-18511.sql @@ -0,0 +1,70 @@ +/* yt can not */ + +$round_period = ($day, $period) -> { + RETURN + CASE + WHEN $period = 'd' THEN $day + WHEN $period = 'w' THEN DateTime::MakeDate(DateTime::StartOfWeek($day)) + WHEN $period = 'm' THEN DateTime::MakeDate(DateTime::StartOfMonth($day)) + ELSE $day + END +}; + +$data = +SELECT + $round_period(day, 'd') AS day, + $round_period(day, 'w') AS week, + $round_period(day, 'm') AS month, + IF(user_card_cnt <= 10, user_card_cnt, 11) AS user_cards_segm, + is_proven_owner, + user_id, +FROM ( + SELECT + Date("2024-04-29") AS day, + "ALLO" AS mark, + "???" AS model, + 5 AS user_card_cnt, + 'ACTIVE' AS status, + 999 AS user_id, + 1 AS is_proven_owner, + UNION ALL + SELECT + Date("2024-04-29") AS day, + "ALLO" AS mark, + "!!!!!!" AS model, + 50 AS user_card_cnt, + 'ACTIVE' AS status, + 1111 AS user_id, + 0 AS is_proven_owner, +); + +SELECT + day, + GROUPING(day) AS grouping_day, + week, + GROUPING(week) AS grouping_week, + month, + GROUPING(month) as grouping_month, + CASE + WHEN GROUPING(week) == 1 AND GROUPING(month) == 1 THEN 'd' + WHEN GROUPING(day) == 1 AND GROUPING(month) == 1 THEN 'w' + WHEN GROUPING(day) == 1 AND GROUPING(week) == 1 THEN 'm' + ELSE NULL + END AS period_type, + user_cards_segm, + if(GROUPING(user_cards_segm) = 1, -300, user_cards_segm) AS __user_cards_segm__, + GROUPING(user_cards_segm) as grouping_user_cards_segm, + COUNT(DISTINCT user_id) AS all_user_qty, +FROM $data AS t +GROUP BY + GROUPING SETS( + -- day grouping + (day), + (day, user_cards_segm), + -- -- week grouping + (week), + (week, user_cards_segm), + -- -- month grouping + (month), + (month, user_cards_segm) + ) diff --git a/yql/essentials/tests/sql/suites/binding/tie_bad_count_fail.cfg b/yql/essentials/tests/sql/suites/binding/tie_bad_count_fail.cfg new file mode 100644 index 00000000000..5dae597903c --- /dev/null +++ b/yql/essentials/tests/sql/suites/binding/tie_bad_count_fail.cfg @@ -0,0 +1 @@ +xfail diff --git a/yql/essentials/tests/sql/suites/binding/tie_bad_count_fail.sql b/yql/essentials/tests/sql/suites/binding/tie_bad_count_fail.sql new file mode 100644 index 00000000000..18e5b364db9 --- /dev/null +++ b/yql/essentials/tests/sql/suites/binding/tie_bad_count_fail.sql @@ -0,0 +1,6 @@ +/* syntax version 1 */ +/* postgres can not */ +/* custom error:Expected tuple type of size: 2, but got: 3*/ +$i, $j = AsTuple(1, 5u, "test"); + +select $i, $j; diff --git a/yql/essentials/tests/sql/suites/case/case_opt_then.sql b/yql/essentials/tests/sql/suites/case/case_opt_then.sql new file mode 100644 index 00000000000..9824a064479 --- /dev/null +++ b/yql/essentials/tests/sql/suites/case/case_opt_then.sql @@ -0,0 +1,5 @@ +select case when true then cast("123" as int) else 100501 end +union all +select case when true then NULL else 100502 end +union all +select case when false then NULL else 100503 end; diff --git a/yql/essentials/tests/sql/suites/datetime/date_tz_impossible_cast.cfg b/yql/essentials/tests/sql/suites/datetime/date_tz_impossible_cast.cfg new file mode 100644 index 00000000000..5dae597903c --- /dev/null +++ b/yql/essentials/tests/sql/suites/datetime/date_tz_impossible_cast.cfg @@ -0,0 +1 @@ +xfail diff --git a/yql/essentials/tests/sql/suites/datetime/date_tz_impossible_cast.sql b/yql/essentials/tests/sql/suites/datetime/date_tz_impossible_cast.sql new file mode 100644 index 00000000000..87057a8bd6a --- /dev/null +++ b/yql/essentials/tests/sql/suites/datetime/date_tz_impossible_cast.sql @@ -0,0 +1,7 @@ +/* custom error:cannot be casted to TzDate*/ +SELECT + CAST(AddTimezone( + /* "1970-01-01T20:59:59Z" */ + CAST(75599 AS DateTime), + "Europe/Moscow" + ) AS TzDate) diff --git a/yql/essentials/tests/sql/suites/expr/as_table_bad_columns_fail.cfg b/yql/essentials/tests/sql/suites/expr/as_table_bad_columns_fail.cfg new file mode 100644 index 00000000000..5dae597903c --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/as_table_bad_columns_fail.cfg @@ -0,0 +1 @@ +xfail diff --git a/yql/essentials/tests/sql/suites/expr/as_table_bad_columns_fail.sql b/yql/essentials/tests/sql/suites/expr/as_table_bad_columns_fail.sql new file mode 100644 index 00000000000..79f45f480cd --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/as_table_bad_columns_fail.sql @@ -0,0 +1,10 @@ +/* postgres can not */ +/* custom error:Member not found: Value3. Did you mean Value1?*/ + +$data = AsList( + AsStruct(1u AS Key, "v1" AS Value1, 100 AS Value2), + AsStruct(2u AS Key, "v2" AS Value1, 200 AS Value2), + AsStruct(3u AS Key, "v3" AS Value1, 300 AS Value2)); + +SELECT Key, Value3 FROM AS_TABLE($data); + diff --git a/yql/essentials/tests/sql/suites/expr/as_table_bad_row_type_fail.cfg b/yql/essentials/tests/sql/suites/expr/as_table_bad_row_type_fail.cfg new file mode 100644 index 00000000000..5dae597903c --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/as_table_bad_row_type_fail.cfg @@ -0,0 +1 @@ +xfail diff --git a/yql/essentials/tests/sql/suites/expr/as_table_bad_row_type_fail.sql b/yql/essentials/tests/sql/suites/expr/as_table_bad_row_type_fail.sql new file mode 100644 index 00000000000..3f972a327f8 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/as_table_bad_row_type_fail.sql @@ -0,0 +1,6 @@ +/* postgres can not */ +/* custom error:Expected struct type, but got: Uint32*/ + +$data = AsList(1u, 2u, 3u); + +SELECT Key, Value3 FROM AS_TABLE($data); diff --git a/yql/essentials/tests/sql/suites/expr/ensure_runtime_fail.cfg b/yql/essentials/tests/sql/suites/expr/ensure_runtime_fail.cfg new file mode 100644 index 00000000000..5dae597903c --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/ensure_runtime_fail.cfg @@ -0,0 +1 @@ +xfail diff --git a/yql/essentials/tests/sql/suites/expr/ensure_runtime_fail.sql b/yql/essentials/tests/sql/suites/expr/ensure_runtime_fail.sql new file mode 100644 index 00000000000..5756dbe688a --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/ensure_runtime_fail.sql @@ -0,0 +1,3 @@ +/* postgres can not */ +/* custom error:message*/ +select Ensure(42, 2 != 2, "message"); diff --git a/yql/essentials/tests/sql/suites/expr/ensure_type_fail.cfg b/yql/essentials/tests/sql/suites/expr/ensure_type_fail.cfg new file mode 100644 index 00000000000..eb2e5315d1e --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/ensure_type_fail.cfg @@ -0,0 +1 @@ +xfail
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/expr/ensure_type_fail.sql b/yql/essentials/tests/sql/suites/expr/ensure_type_fail.sql new file mode 100644 index 00000000000..a0aee93406a --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/ensure_type_fail.sql @@ -0,0 +1,5 @@ +/* syntax version 1 */ +/* postgres can not */ +/* syntax version 1 */ +/* custom error:Mismatch types: Int32 != String (message)*/ +select EnsureType(42, String, "message"); diff --git a/yql/essentials/tests/sql/suites/expr/fail_constraint.cfg b/yql/essentials/tests/sql/suites/expr/fail_constraint.cfg new file mode 100644 index 00000000000..eb2e5315d1e --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/fail_constraint.cfg @@ -0,0 +1 @@ +xfail
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/expr/fail_constraint.sql b/yql/essentials/tests/sql/suites/expr/fail_constraint.sql new file mode 100644 index 00000000000..ac1a7a47b81 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/fail_constraint.sql @@ -0,0 +1,4 @@ +/* custom error:missing Empty constraint in node AsList*/ +pragma warning("disable","4510"); +select Yql::FailMe(AsAtom('constraint')); + diff --git a/yql/essentials/tests/sql/suites/expr/fail_expr.cfg b/yql/essentials/tests/sql/suites/expr/fail_expr.cfg new file mode 100644 index 00000000000..5dae597903c --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/fail_expr.cfg @@ -0,0 +1 @@ +xfail diff --git a/yql/essentials/tests/sql/suites/expr/fail_expr.sql b/yql/essentials/tests/sql/suites/expr/fail_expr.sql new file mode 100644 index 00000000000..3ace8ea83c5 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/fail_expr.sql @@ -0,0 +1,4 @@ +/* custom error:Detected a type error after initial validation*/ +pragma warning("disable","4510"); +select Yql::FailMe(AsAtom('expr')); + diff --git a/yql/essentials/tests/sql/suites/expr/fail_type.cfg b/yql/essentials/tests/sql/suites/expr/fail_type.cfg new file mode 100644 index 00000000000..5dae597903c --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/fail_type.cfg @@ -0,0 +1 @@ +xfail diff --git a/yql/essentials/tests/sql/suites/expr/fail_type.sql b/yql/essentials/tests/sql/suites/expr/fail_type.sql new file mode 100644 index 00000000000..9a4ce9720cf --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/fail_type.sql @@ -0,0 +1,4 @@ +/* custom error:Rewrite error, type should be : String, but it is: Int32 for node Int32*/ +pragma warning("disable","4510"); +select Yql::FailMe(AsAtom('type')); + diff --git a/yql/essentials/tests/sql/suites/expr/implicit_bitcast_fail.cfg b/yql/essentials/tests/sql/suites/expr/implicit_bitcast_fail.cfg new file mode 100644 index 00000000000..5dae597903c --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/implicit_bitcast_fail.cfg @@ -0,0 +1 @@ +xfail diff --git a/yql/essentials/tests/sql/suites/expr/implicit_bitcast_fail.sql b/yql/essentials/tests/sql/suites/expr/implicit_bitcast_fail.sql new file mode 100644 index 00000000000..f609402b29a --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/implicit_bitcast_fail.sql @@ -0,0 +1,7 @@ +/* postgres can not */ +/* custom error:Cannot infer common type for Int64 and Uint64*/ +pragma warning("error", "1107"); +select AsList( +7498311229109140978, +254610204336699107, +11580367904009864964); diff --git a/yql/essentials/tests/sql/suites/expr/list_replicate_fail.cfg b/yql/essentials/tests/sql/suites/expr/list_replicate_fail.cfg new file mode 100644 index 00000000000..5dae597903c --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/list_replicate_fail.cfg @@ -0,0 +1 @@ +xfail diff --git a/yql/essentials/tests/sql/suites/expr/list_replicate_fail.sql b/yql/essentials/tests/sql/suites/expr/list_replicate_fail.sql new file mode 100644 index 00000000000..fe6b10be5c6 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/list_replicate_fail.sql @@ -0,0 +1,4 @@ +/* postgres can not */ +/* custom error:Second argument in ListReplicate = 18446744073709551615 exceeds maximum value = 4294967296*/ +select +ListReplicate(1, -1); diff --git a/yql/essentials/tests/sql/suites/expr/unwrap_runtime_fail.cfg b/yql/essentials/tests/sql/suites/expr/unwrap_runtime_fail.cfg new file mode 100644 index 00000000000..5dae597903c --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/unwrap_runtime_fail.cfg @@ -0,0 +1 @@ +xfail diff --git a/yql/essentials/tests/sql/suites/expr/unwrap_runtime_fail.sql b/yql/essentials/tests/sql/suites/expr/unwrap_runtime_fail.sql new file mode 100644 index 00000000000..9d558559893 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/unwrap_runtime_fail.sql @@ -0,0 +1,3 @@ +/* postgres can not */ +/* custom error:message*/ +select Unwrap(null, "message"); diff --git a/yql/essentials/tests/sql/suites/expr/xor.sql b/yql/essentials/tests/sql/suites/expr/xor.sql new file mode 100644 index 00000000000..3799af42764 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/xor.sql @@ -0,0 +1,17 @@ +/* postgres can not */ +SELECT + o1 XOR o2 AS xor, + o1 XOR o1 AS xor_same_o1, + o2 XOR o2 AS xor_same_o2, + o1 XOR o2 XOR o1 XOR o2 XOR o1 XOR o2 AS xor_triple_dups, + o1 XOR o2 XOR Unwrap(o1) XOR Unwrap(o2) AS xor_with_unwraps, +FROM AS_TABLE([ + <|o1: FALSE, o2: FALSE|>, + <|o1: TRUE, o2: TRUE|>, + <|o1: TRUE, o2: FALSE|>, + <|o1: FALSE, o2: TRUE|>, + <|o1: TRUE, o2: NULL|>, + <|o1: FALSE, o2: NULL|>, + <|o1: NULL, o2: TRUE|>, + <|o1: NULL, o2: FALSE|> +]); diff --git a/yql/essentials/tests/sql/suites/file/parse_file_bad_type_fail.cfg b/yql/essentials/tests/sql/suites/file/parse_file_bad_type_fail.cfg new file mode 100644 index 00000000000..a01a1a3d243 --- /dev/null +++ b/yql/essentials/tests/sql/suites/file/parse_file_bad_type_fail.cfg @@ -0,0 +1,3 @@ +xfail +file keyid.lst keyid.lst +udf file_udf
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/file/parse_file_bad_type_fail.sql b/yql/essentials/tests/sql/suites/file/parse_file_bad_type_fail.sql new file mode 100644 index 00000000000..81fb716b155 --- /dev/null +++ b/yql/essentials/tests/sql/suites/file/parse_file_bad_type_fail.sql @@ -0,0 +1,3 @@ +/* postgres can not */ +/* custom error:User type Uuid is not supported*/ +select ParseFile('uuid', "keyid.lst"); diff --git a/yql/essentials/tests/sql/suites/file/second_pass_parse_file_fail.cfg b/yql/essentials/tests/sql/suites/file/second_pass_parse_file_fail.cfg new file mode 100644 index 00000000000..a3cc236d31e --- /dev/null +++ b/yql/essentials/tests/sql/suites/file/second_pass_parse_file_fail.cfg @@ -0,0 +1,3 @@ +xfail +file keyid.lst keyid.lst +udf file_udf diff --git a/yql/essentials/tests/sql/suites/file/second_pass_parse_file_fail.sql b/yql/essentials/tests/sql/suites/file/second_pass_parse_file_fail.sql new file mode 100644 index 00000000000..14980696a46 --- /dev/null +++ b/yql/essentials/tests/sql/suites/file/second_pass_parse_file_fail.sql @@ -0,0 +1,6 @@ +/* postgres can not */ +/* custom error:The file iterator was already created. To scan file data multiple times please use ListCollect either over ParseFile or over some lazy function over it, e.g. ListMap*/ +$list = ParseFile("int32","keyid.lst"); +select ListExtend( + ListMap($list, ($x)->{return $x + 1 }), + ListMap($list, ($x)->{return $x + 2 })); diff --git a/yql/essentials/tests/sql/suites/in/in_tuple_check0_fail.cfg b/yql/essentials/tests/sql/suites/in/in_tuple_check0_fail.cfg new file mode 100644 index 00000000000..eb2e5315d1e --- /dev/null +++ b/yql/essentials/tests/sql/suites/in/in_tuple_check0_fail.cfg @@ -0,0 +1 @@ +xfail
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/in/in_tuple_check0_fail.sql b/yql/essentials/tests/sql/suites/in/in_tuple_check0_fail.sql new file mode 100644 index 00000000000..f32fb9bcff0 --- /dev/null +++ b/yql/essentials/tests/sql/suites/in/in_tuple_check0_fail.sql @@ -0,0 +1,6 @@ +/* custom error:Can't compare Tuple<Int32,Int32> with Tuple<Int32,Tuple<Int32,Int32>>*/ +select AsTuple(1, 1) in ( + AsTuple(2, 1), + AsTuple(1, 2), + AsTuple(1, AsTuple(1, 2)), -- expect compatible tuple +) diff --git a/yql/essentials/tests/sql/suites/in/in_tuple_check1_fail.cfg b/yql/essentials/tests/sql/suites/in/in_tuple_check1_fail.cfg new file mode 100644 index 00000000000..eb2e5315d1e --- /dev/null +++ b/yql/essentials/tests/sql/suites/in/in_tuple_check1_fail.cfg @@ -0,0 +1 @@ +xfail
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/in/in_tuple_check1_fail.sql b/yql/essentials/tests/sql/suites/in/in_tuple_check1_fail.sql new file mode 100644 index 00000000000..91bda9b22de --- /dev/null +++ b/yql/essentials/tests/sql/suites/in/in_tuple_check1_fail.sql @@ -0,0 +1,6 @@ +/* custom error:Can't compare Tuple<Int32,Int32> with Int32*/ +select AsTuple(1, 1) in ( + AsTuple(2, 1), + AsTuple(1, 2), + 42 -- expect tuple, not data +) diff --git a/yql/essentials/tests/sql/suites/join/inmem_by_uncomparable_structs.sql b/yql/essentials/tests/sql/suites/join/inmem_by_uncomparable_structs.sql new file mode 100644 index 00000000000..ec4ad06056a --- /dev/null +++ b/yql/essentials/tests/sql/suites/join/inmem_by_uncomparable_structs.sql @@ -0,0 +1,23 @@ +/* syntax version 1 */ +$l = [ + <|Key:<|a:1, b:2, c:null|>, Lhs:"1,2,#"|>, + <|Key:<|a:2, b:3, c:null|>, Lhs:"4,5,#"|>, +]; + +$r = [ + <|Key:<|a:1, b:2, c:3|>, Rhs:"1,2,3"|>, + <|Key:<|a:4, b:5, c:6|>, Rhs:"4,5,6"|>, +]; + +select Lhs, Rhs from AS_TABLE($l) as l left join AS_TABLE($r) as r using(Key); +select Lhs from AS_TABLE($l) as l left semi join AS_TABLE($r) as r using(Key); +select Lhs from AS_TABLE($l) as l left only join AS_TABLE($r) as r using(Key); + +select Rhs, Lhs from AS_TABLE($l) as l right join AS_TABLE($r) as r using(Key); +select Rhs from AS_TABLE($l) as l right semi join AS_TABLE($r) as r using(Key); +select Rhs from AS_TABLE($l) as l right only join AS_TABLE($r) as r using(Key); + +select Lhs, Rhs from AS_TABLE($l) as l inner join AS_TABLE($r) as r using(Key); +select Lhs, Rhs from AS_TABLE($l) as l full join AS_TABLE($r) as r using(Key); +select Lhs, Rhs from AS_TABLE($l) as l exclusion join AS_TABLE($r) as r using(Key); + diff --git a/yql/essentials/tests/sql/suites/join/inmem_by_uncomparable_tuples.sql b/yql/essentials/tests/sql/suites/join/inmem_by_uncomparable_tuples.sql new file mode 100644 index 00000000000..18a95e6c496 --- /dev/null +++ b/yql/essentials/tests/sql/suites/join/inmem_by_uncomparable_tuples.sql @@ -0,0 +1,22 @@ +/* syntax version 1 */ +$l = AsList( + AsStruct(AsTuple(1,2,3) as Key, "1,2,3" as Lhs), + AsStruct(AsTuple(1,2,4) as Key, "1,2,4" as Lhs) +); + +$r = AsList( + AsStruct(AsTuple(1,2) as Key, "1,2" as Rhs), + AsStruct(AsTuple(2,3) as Key, "2,3" as Rhs) +); + +select Lhs, Rhs from AS_TABLE($l) as l left join AS_TABLE($r) as r using(Key); +select Lhs from AS_TABLE($l) as l left semi join AS_TABLE($r) as r using(Key); +select Lhs from AS_TABLE($l) as l left only join AS_TABLE($r) as r using(Key); + +select Rhs, Lhs from AS_TABLE($l) as l right join AS_TABLE($r) as r using(Key); +select Rhs from AS_TABLE($l) as l right semi join AS_TABLE($r) as r using(Key); +select Rhs from AS_TABLE($l) as l right only join AS_TABLE($r) as r using(Key); + +select Lhs, Rhs from AS_TABLE($l) as l inner join AS_TABLE($r) as r using(Key); +select Lhs, Rhs from AS_TABLE($l) as l full join AS_TABLE($r) as r using(Key); +select Lhs, Rhs from AS_TABLE($l) as l exclusion join AS_TABLE($r) as r using(Key); diff --git a/yql/essentials/tests/sql/suites/json/json_exists/on_error_exception.cfg b/yql/essentials/tests/sql/suites/json/json_exists/on_error_exception.cfg new file mode 100644 index 00000000000..eb2e5315d1e --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_exists/on_error_exception.cfg @@ -0,0 +1 @@ +xfail
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/json/json_exists/on_error_exception.sql b/yql/essentials/tests/sql/suites/json/json_exists/on_error_exception.sql new file mode 100644 index 00000000000..fbcaabcd77d --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_exists/on_error_exception.sql @@ -0,0 +1,6 @@ +/* custom error:Member not found*/ + +-- Accessing absent object member will cause jsonpath error in strict mode +$json = CAST("{}" as Json); +SELECT + JSON_EXISTS($json, "strict $.key" ERROR ON ERROR); diff --git a/yql/essentials/tests/sql/suites/json/json_exists/passing_exception.cfg b/yql/essentials/tests/sql/suites/json/json_exists/passing_exception.cfg new file mode 100644 index 00000000000..eb2e5315d1e --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_exists/passing_exception.cfg @@ -0,0 +1 @@ +xfail
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/json/json_exists/passing_exception.sql b/yql/essentials/tests/sql/suites/json/json_exists/passing_exception.sql new file mode 100644 index 00000000000..84a75105a91 --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_exists/passing_exception.sql @@ -0,0 +1,13 @@ +/* custom error:Expected data or optional of data, but got: Tuple<Int32,Int32>*/ + +$json = CAST(@@{ + "key": 123 +}@@ as Json); + +-- Tuple type is not supported for variables +SELECT + JSON_EXISTS( + $json, "strict $var" + PASSING + AsTuple(1, 2) as var + ); diff --git a/yql/essentials/tests/sql/suites/json/json_query/on_empty_exception.cfg b/yql/essentials/tests/sql/suites/json/json_query/on_empty_exception.cfg new file mode 100644 index 00000000000..eb2e5315d1e --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_query/on_empty_exception.cfg @@ -0,0 +1 @@ +xfail
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/json/json_query/on_empty_exception.sql b/yql/essentials/tests/sql/suites/json/json_query/on_empty_exception.sql new file mode 100644 index 00000000000..e175a5753f0 --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_query/on_empty_exception.sql @@ -0,0 +1,6 @@ +/* custom error:Empty result*/ + +-- Accessing absent object member will cause empty result in lax mode +$json = CAST("{}" as Json); +SELECT + JSON_QUERY($json, "lax $.key" ERROR ON EMPTY); diff --git a/yql/essentials/tests/sql/suites/json/json_query/on_error_exception.cfg b/yql/essentials/tests/sql/suites/json/json_query/on_error_exception.cfg new file mode 100644 index 00000000000..eb2e5315d1e --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_query/on_error_exception.cfg @@ -0,0 +1 @@ +xfail
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/json/json_query/on_error_exception.sql b/yql/essentials/tests/sql/suites/json/json_query/on_error_exception.sql new file mode 100644 index 00000000000..572988b9be1 --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_query/on_error_exception.sql @@ -0,0 +1,5 @@ +/* custom error:Member not found*/ +-- Accessing absent object member will cause jsonpath error in strict mode +$json = CAST("{}" as Json); +SELECT + JSON_QUERY($json, "strict $.key" ERROR ON ERROR); diff --git a/yql/essentials/tests/sql/suites/json/json_query/passing_exception.cfg b/yql/essentials/tests/sql/suites/json/json_query/passing_exception.cfg new file mode 100644 index 00000000000..eb2e5315d1e --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_query/passing_exception.cfg @@ -0,0 +1 @@ +xfail
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/json/json_query/passing_exception.sql b/yql/essentials/tests/sql/suites/json/json_query/passing_exception.sql new file mode 100644 index 00000000000..151e6dbcd10 --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_query/passing_exception.sql @@ -0,0 +1,16 @@ +/* syntax version 1 */ +/* postgres can not */ +/* custom error:Expected data or optional of data, but got: Tuple<Int32,Int32>*/ + +$json = CAST(@@{ + "key": 123 +}@@ as Json); + +-- Tuple type is not supported for variables +SELECT + JSON_QUERY( + $json, "strict $var" + PASSING + AsTuple(1, 2) as var + WITH UNCONDITIONAL ARRAY WRAPPER + ); diff --git a/yql/essentials/tests/sql/suites/json/json_value/on_empty_cast_default_exception.cfg b/yql/essentials/tests/sql/suites/json/json_value/on_empty_cast_default_exception.cfg new file mode 100644 index 00000000000..eb2e5315d1e --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_value/on_empty_cast_default_exception.cfg @@ -0,0 +1 @@ +xfail
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/json/json_value/on_empty_cast_default_exception.sql b/yql/essentials/tests/sql/suites/json/json_value/on_empty_cast_default_exception.sql new file mode 100644 index 00000000000..8237aef3380 --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_value/on_empty_cast_default_exception.sql @@ -0,0 +1,5 @@ +/* custom error:Failed to cast default value from ON EMPTY clause to target type Optional<Uint32>*/ + +$json = CAST("{}" as Json); +SELECT + JSON_VALUE($json, "lax $.key" RETURNING Uint32 DEFAULT -2 ON EMPTY ERROR ON ERROR); diff --git a/yql/essentials/tests/sql/suites/json/json_value/on_empty_exception.cfg b/yql/essentials/tests/sql/suites/json/json_value/on_empty_exception.cfg new file mode 100644 index 00000000000..eb2e5315d1e --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_value/on_empty_exception.cfg @@ -0,0 +1 @@ +xfail
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/json/json_value/on_empty_exception.sql b/yql/essentials/tests/sql/suites/json/json_value/on_empty_exception.sql new file mode 100644 index 00000000000..d30d3161b0c --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_value/on_empty_exception.sql @@ -0,0 +1,5 @@ +/* custom error:Result is empty*/ + +$json = CAST("{}" as Json); +SELECT + JSON_VALUE($json, "lax $.key" ERROR ON EMPTY); diff --git a/yql/essentials/tests/sql/suites/json/json_value/on_error_cast_default_exception.cfg b/yql/essentials/tests/sql/suites/json/json_value/on_error_cast_default_exception.cfg new file mode 100644 index 00000000000..eb2e5315d1e --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_value/on_error_cast_default_exception.cfg @@ -0,0 +1 @@ +xfail
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/json/json_value/on_error_cast_default_exception.sql b/yql/essentials/tests/sql/suites/json/json_value/on_error_cast_default_exception.sql new file mode 100644 index 00000000000..a7169032d9f --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_value/on_error_cast_default_exception.sql @@ -0,0 +1,8 @@ +/* custom error:Failed to cast default value from ON ERROR clause to target type Optional<Uint16>*/ + +-- Here JsonPath engine returns error result and ON ERROR section must be used. +-- But default value in ON ERROR section is -123 and casting it to Uint16 will fail. +-- In this case exception must be raised. +$json = CAST("{}" as Json); +SELECT + JSON_VALUE($json, "strict $.key" RETURNING Uint16 DEFAULT -123 ON ERROR); diff --git a/yql/essentials/tests/sql/suites/json/json_value/on_error_cast_udf_exception.cfg b/yql/essentials/tests/sql/suites/json/json_value/on_error_cast_udf_exception.cfg new file mode 100644 index 00000000000..eb2e5315d1e --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_value/on_error_cast_udf_exception.cfg @@ -0,0 +1 @@ +xfail
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/json/json_value/on_error_cast_udf_exception.sql b/yql/essentials/tests/sql/suites/json/json_value/on_error_cast_udf_exception.sql new file mode 100644 index 00000000000..a3fb807857c --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_value/on_error_cast_udf_exception.sql @@ -0,0 +1,9 @@ +/* custom error:Cannot convert extracted JSON value to target type*/ + +-- In this case call to Json2::SqlValueNumber will fail because "string" +-- does not represent Number value +$json = CAST(@@{ + "key": "string" +}@@ as Json); +SELECT + JSON_VALUE($json, "strict $.key" RETURNING Uint16 ERROR ON ERROR); diff --git a/yql/essentials/tests/sql/suites/json/json_value/on_error_cast_value_exception.cfg b/yql/essentials/tests/sql/suites/json/json_value/on_error_cast_value_exception.cfg new file mode 100644 index 00000000000..eb2e5315d1e --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_value/on_error_cast_value_exception.cfg @@ -0,0 +1 @@ +xfail
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/json/json_value/on_error_cast_value_exception.sql b/yql/essentials/tests/sql/suites/json/json_value/on_error_cast_value_exception.sql new file mode 100644 index 00000000000..a671f89fca1 --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_value/on_error_cast_value_exception.sql @@ -0,0 +1,9 @@ +/* custom error:Failed to cast extracted JSON value to target type Optional<Uint16>*/ + +-- In this case call to Json2::SqlValueNumber will be successfull, but cast +-- of -123 to Uint16 will fail +$json = CAST(@@{ + "key": -123 +}@@ as Json); +SELECT + JSON_VALUE($json, "strict $.key" RETURNING Uint16 ERROR ON ERROR); diff --git a/yql/essentials/tests/sql/suites/json/json_value/on_error_jsonpath_exception.cfg b/yql/essentials/tests/sql/suites/json/json_value/on_error_jsonpath_exception.cfg new file mode 100644 index 00000000000..eb2e5315d1e --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_value/on_error_jsonpath_exception.cfg @@ -0,0 +1 @@ +xfail
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/json/json_value/on_error_jsonpath_exception.sql b/yql/essentials/tests/sql/suites/json/json_value/on_error_jsonpath_exception.sql new file mode 100644 index 00000000000..ee4467e138e --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_value/on_error_jsonpath_exception.sql @@ -0,0 +1,5 @@ +/* custom error:Member not found*/ + +$json = CAST("{}" as Json); +SELECT + JSON_VALUE($json, "strict $.key" ERROR ON ERROR); diff --git a/yql/essentials/tests/sql/suites/json/json_value/passing_exception.cfg b/yql/essentials/tests/sql/suites/json/json_value/passing_exception.cfg new file mode 100644 index 00000000000..eb2e5315d1e --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_value/passing_exception.cfg @@ -0,0 +1 @@ +xfail
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/json/json_value/passing_exception.sql b/yql/essentials/tests/sql/suites/json/json_value/passing_exception.sql new file mode 100644 index 00000000000..2c6dfcf9b9e --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_value/passing_exception.sql @@ -0,0 +1,15 @@ +/* syntax version 1 */ +/* postgres can not */ +/* custom error:Expected data or optional of data, but got: Tuple<Int32,Int32>*/ + +$json = CAST(@@{ + "key": 123 +}@@ as Json); + +-- Tuple type is not supported for variables +SELECT + JSON_VALUE( + $json, "strict $var" + PASSING + AsTuple(1, 2) as var + ); diff --git a/yql/essentials/tests/sql/suites/lambda/lambda_with_tie_bad_count_fail.cfg b/yql/essentials/tests/sql/suites/lambda/lambda_with_tie_bad_count_fail.cfg new file mode 100644 index 00000000000..5dae597903c --- /dev/null +++ b/yql/essentials/tests/sql/suites/lambda/lambda_with_tie_bad_count_fail.cfg @@ -0,0 +1 @@ +xfail diff --git a/yql/essentials/tests/sql/suites/lambda/lambda_with_tie_bad_count_fail.sql b/yql/essentials/tests/sql/suites/lambda/lambda_with_tie_bad_count_fail.sql new file mode 100644 index 00000000000..b71fe029767 --- /dev/null +++ b/yql/essentials/tests/sql/suites/lambda/lambda_with_tie_bad_count_fail.sql @@ -0,0 +1,8 @@ +/* custom error:Expected tuple type of size: 2, but got: 3*/ +$func = ($x, $y)->{ + $y, $x = AsTuple($x, $y, $x); + return $x || "_" || $y; +}; + +SELECT $func('foo', 'bar'); + diff --git a/yql/essentials/tests/sql/suites/match_recognize/alerts-streaming.sql b/yql/essentials/tests/sql/suites/match_recognize/alerts-streaming.sql new file mode 100644 index 00000000000..f8f13b54113 --- /dev/null +++ b/yql/essentials/tests/sql/suites/match_recognize/alerts-streaming.sql @@ -0,0 +1,65 @@ +$osquery_data = [ +<|dt:1688910000, host:"fqdn1", ev_type:"someEv", ev_status:"", user:"", vpn:false, |>, +<|dt:1688910050, host:"fqdn2", ev_type:"login", ev_status:"success", user:"", vpn:true, |>, +<|dt:1688910100, host:"fqdn1", ev_type:"login", ev_status:"success", user:"", vpn:true, |>, +<|dt:1688910220, host:"fqdn1", ev_type:"login", ev_status:"success", user:"", vpn:false, |>, +<|dt:1688910300, host:"fqdn1", ev_type:"delete_all", ev_status:"", user:"", vpn:false, |>, +<|dt:1688910400, host:"fqdn2", ev_type:"delete_all", ev_status:"", user:"", vpn:false, |>, +<|dt:1688910500, host:"fqdn1", ev_type:"login", ev_status:"failed", user:"user1", vpn:false, |>, +<|dt:1688910500, host:"fqdn1", ev_type:"login", ev_status:"failed", user:"user2", vpn:false, |>, +<|dt:1688910600, host:"fqdn", ev_type:"someEv", ev_status:"", user:"user1", vpn:false, |>, +<|dt:1688910800, host:"fqdn2", ev_type:"login", ev_status:"failed", user:"user1", vpn:false, |>, +<|dt:1688910900, host:"fqdn2", ev_type:"login", ev_status:"failed", user:"user2", vpn:false, |>, +<|dt:1688911000, host:"fqdn2", ev_type:"login", ev_status:"success", user:"user1", vpn:false, |>, +<|dt:1688911001, host:"fqdn2", ev_type:"login", ev_status:"success", user:"user1", vpn:false, |>, +]; + +pragma FeatureR010="prototype"; +pragma config.flags("MatchRecognizeStream", "force"); + +SELECT * +FROM AS_TABLE($osquery_data) MATCH_RECOGNIZE( + ORDER BY CAST(dt as Timestamp) + MEASURES + LAST(LOGIN_SUCCESS_REMOTE.host) as remote_login_host, + LAST(LOGIN_SUCCESS_REMOTE.user) as remote_login_user, + LAST(LOGIN_SUCCESS_REMOTE.dt) as remote_login_dt, + LAST(SUSPICIOUS_ACTION_SOON.dt) as suspicious_action_dt, + FIRST(LOGIN_FAILED_SAME_USER.dt) as brutforce_begin, + FIRST(LOGIN_SUCCESS_SAME_USER.dt) as brutforce_end, + LAST(LOGIN_SUCCESS_SAME_USER.user) as brutforce_login + + ONE ROW PER MATCH + AFTER MATCH SKIP TO NEXT ROW + PATTERN ( + LOGIN_SUCCESS_REMOTE ANY_ROW1* SUSPICIOUS_ACTION_SOON | + (LOGIN_FAILED_SAME_USER ANY_ROW2*){2,} LOGIN_SUCCESS_SAME_USER + ) + DEFINE + LOGIN_SUCCESS_REMOTE as + LOGIN_SUCCESS_REMOTE.ev_type = "login" and + LOGIN_SUCCESS_REMOTE.ev_status = "success" and + LOGIN_SUCCESS_REMOTE.vpn = true and + COALESCE(LOGIN_SUCCESS_REMOTE.dt - FIRST(LOGIN_FAILED_SAME_USER.dt) <= 500, TRUE), + ANY_ROW1 as + COALESCE(ANY_ROW1.dt - FIRST(LOGIN_SUCCESS_REMOTE.dt) <= 500, TRUE), + SUSPICIOUS_ACTION_SOON as + SUSPICIOUS_ACTION_SOON.host = LAST(LOGIN_SUCCESS_REMOTE.host) and + SUSPICIOUS_ACTION_SOON.ev_type = "delete_all" and + COALESCE(SUSPICIOUS_ACTION_SOON.dt - FIRST(LOGIN_SUCCESS_REMOTE.dt) <= 500, TRUE), + LOGIN_FAILED_SAME_USER as + LOGIN_FAILED_SAME_USER.ev_type = "login" and + LOGIN_FAILED_SAME_USER.ev_status <> "success" and + (LAST(LOGIN_FAILED_SAME_USER.user) IS NULL + or LAST(LOGIN_FAILED_SAME_USER.user) = LOGIN_FAILED_SAME_USER.user + ) and COALESCE(LOGIN_FAILED_SAME_USER.dt - FIRST(LOGIN_FAILED_SAME_USER.dt) <= 500, TRUE), + ANY_ROW2 as + COALESCE(ANY_ROW2.dt - FIRST(LOGIN_FAILED_SAME_USER.dt) <= 500, TRUE), + LOGIN_SUCCESS_SAME_USER as + LOGIN_SUCCESS_SAME_USER.ev_type = "login" and + LOGIN_SUCCESS_SAME_USER.ev_status = "success" and + LOGIN_SUCCESS_SAME_USER.user = LAST(LOGIN_FAILED_SAME_USER.user) and + COALESCE(LOGIN_SUCCESS_SAME_USER.dt - FIRST(LOGIN_FAILED_SAME_USER.dt) <= 500, TRUE) +) AS MATCHED +; + diff --git a/yql/essentials/tests/sql/suites/match_recognize/alerts.sql b/yql/essentials/tests/sql/suites/match_recognize/alerts.sql new file mode 100644 index 00000000000..da1aa8ddd4e --- /dev/null +++ b/yql/essentials/tests/sql/suites/match_recognize/alerts.sql @@ -0,0 +1,65 @@ +$osquery_data = [ +<|dt:1688910000, host:"fqdn1", ev_type:"someEv", ev_status:"", user:"", vpn:false, |>, +<|dt:1688910050, host:"fqdn2", ev_type:"login", ev_status:"success", user:"", vpn:true, |>, +<|dt:1688910100, host:"fqdn1", ev_type:"login", ev_status:"success", user:"", vpn:true, |>, +<|dt:1688910220, host:"fqdn1", ev_type:"login", ev_status:"success", user:"", vpn:false, |>, +<|dt:1688910300, host:"fqdn1", ev_type:"delete_all", ev_status:"", user:"", vpn:false, |>, +<|dt:1688910400, host:"fqdn2", ev_type:"delete_all", ev_status:"", user:"", vpn:false, |>, +<|dt:1688910500, host:"fqdn1", ev_type:"login", ev_status:"failed", user:"user1", vpn:false, |>, +<|dt:1688910500, host:"fqdn1", ev_type:"login", ev_status:"failed", user:"user2", vpn:false, |>, +<|dt:1688910600, host:"fqdn", ev_type:"someEv", ev_status:"", user:"user1", vpn:false, |>, +<|dt:1688910800, host:"fqdn2", ev_type:"login", ev_status:"failed", user:"user1", vpn:false, |>, +<|dt:1688910900, host:"fqdn2", ev_type:"login", ev_status:"failed", user:"user2", vpn:false, |>, +<|dt:1688911000, host:"fqdn2", ev_type:"login", ev_status:"success", user:"user1", vpn:false, |>, +<|dt:1688911001, host:"fqdn2", ev_type:"login", ev_status:"success", user:"user1", vpn:false, |>, +]; + +pragma FeatureR010="prototype"; +pragma config.flags("MatchRecognizeStream", "disable"); + +SELECT * +FROM AS_TABLE($osquery_data) MATCH_RECOGNIZE( + ORDER BY CAST(dt as Timestamp) + MEASURES + LAST(LOGIN_SUCCESS_REMOTE.host) as remote_login_host, + LAST(LOGIN_SUCCESS_REMOTE.user) as remote_login_user, + LAST(LOGIN_SUCCESS_REMOTE.dt) as remote_login_dt, + LAST(SUSPICIOUS_ACTION_SOON.dt) as suspicious_action_dt, + FIRST(LOGIN_FAILED_SAME_USER.dt) as brutforce_begin, + FIRST(LOGIN_SUCCESS_SAME_USER.dt) as brutforce_end, + LAST(LOGIN_SUCCESS_SAME_USER.user) as brutforce_login + + ONE ROW PER MATCH + AFTER MATCH SKIP TO NEXT ROW + PATTERN ( + LOGIN_SUCCESS_REMOTE ANY_ROW1* SUSPICIOUS_ACTION_SOON | + (LOGIN_FAILED_SAME_USER ANY_ROW2*){2,} LOGIN_SUCCESS_SAME_USER + ) + DEFINE + LOGIN_SUCCESS_REMOTE as + LOGIN_SUCCESS_REMOTE.ev_type = "login" and + LOGIN_SUCCESS_REMOTE.ev_status = "success" and + LOGIN_SUCCESS_REMOTE.vpn = true and + COALESCE(LOGIN_SUCCESS_REMOTE.dt - FIRST(LOGIN_FAILED_SAME_USER.dt) <= 500, TRUE), + ANY_ROW1 as + COALESCE(ANY_ROW1.dt - FIRST(LOGIN_SUCCESS_REMOTE.dt) <= 500, TRUE), + SUSPICIOUS_ACTION_SOON as + SUSPICIOUS_ACTION_SOON.host = LAST(LOGIN_SUCCESS_REMOTE.host) and + SUSPICIOUS_ACTION_SOON.ev_type = "delete_all" and + COALESCE(SUSPICIOUS_ACTION_SOON.dt - FIRST(LOGIN_SUCCESS_REMOTE.dt) <= 500, TRUE), + LOGIN_FAILED_SAME_USER as + LOGIN_FAILED_SAME_USER.ev_type = "login" and + LOGIN_FAILED_SAME_USER.ev_status <> "success" and + (LAST(LOGIN_FAILED_SAME_USER.user) IS NULL + or LAST(LOGIN_FAILED_SAME_USER.user) = LOGIN_FAILED_SAME_USER.user + ) and COALESCE(LOGIN_FAILED_SAME_USER.dt - FIRST(LOGIN_FAILED_SAME_USER.dt) <= 500, TRUE), + ANY_ROW2 as + COALESCE(ANY_ROW2.dt - FIRST(LOGIN_FAILED_SAME_USER.dt) <= 500, TRUE), + LOGIN_SUCCESS_SAME_USER as + LOGIN_SUCCESS_SAME_USER.ev_type = "login" and + LOGIN_SUCCESS_SAME_USER.ev_status = "success" and + LOGIN_SUCCESS_SAME_USER.user = LAST(LOGIN_FAILED_SAME_USER.user) and + COALESCE(LOGIN_SUCCESS_SAME_USER.dt - FIRST(LOGIN_FAILED_SAME_USER.dt) <= 500, TRUE) +) AS MATCHED +; + diff --git a/yql/essentials/tests/sql/suites/match_recognize/alerts_without_order.sql b/yql/essentials/tests/sql/suites/match_recognize/alerts_without_order.sql new file mode 100644 index 00000000000..006ba67e2e2 --- /dev/null +++ b/yql/essentials/tests/sql/suites/match_recognize/alerts_without_order.sql @@ -0,0 +1,64 @@ +$osquery_data = [ +<|dt:1688910000, host:"fqdn1", ev_type:"someEv", ev_status:"", user:"", vpn:false, |>, +<|dt:1688910050, host:"fqdn2", ev_type:"login", ev_status:"success", user:"", vpn:true, |>, +<|dt:1688910100, host:"fqdn1", ev_type:"login", ev_status:"success", user:"", vpn:true, |>, +<|dt:1688910220, host:"fqdn1", ev_type:"login", ev_status:"success", user:"", vpn:false, |>, +<|dt:1688910300, host:"fqdn1", ev_type:"delete_all", ev_status:"", user:"", vpn:false, |>, +<|dt:1688910400, host:"fqdn2", ev_type:"delete_all", ev_status:"", user:"", vpn:false, |>, +<|dt:1688910500, host:"fqdn1", ev_type:"login", ev_status:"failed", user:"user1", vpn:false, |>, +<|dt:1688910500, host:"fqdn1", ev_type:"login", ev_status:"failed", user:"user2", vpn:false, |>, +<|dt:1688910600, host:"fqdn", ev_type:"someEv", ev_status:"", user:"user1", vpn:false, |>, +<|dt:1688910800, host:"fqdn2", ev_type:"login", ev_status:"failed", user:"user1", vpn:false, |>, +<|dt:1688910900, host:"fqdn2", ev_type:"login", ev_status:"failed", user:"user2", vpn:false, |>, +<|dt:1688911000, host:"fqdn2", ev_type:"login", ev_status:"success", user:"user1", vpn:false, |>, +<|dt:1688911001, host:"fqdn2", ev_type:"login", ev_status:"success", user:"user1", vpn:false, |>, +]; + +pragma FeatureR010="prototype"; +pragma config.flags("MatchRecognizeStream", "disable"); + +SELECT * +FROM AS_TABLE($osquery_data) MATCH_RECOGNIZE( + MEASURES + LAST(LOGIN_SUCCESS_REMOTE.host) as remote_login_host, + LAST(LOGIN_SUCCESS_REMOTE.user) as remote_login_user, + LAST(LOGIN_SUCCESS_REMOTE.dt) as remote_login_dt, + LAST(SUSPICIOUS_ACTION_SOON.dt) as suspicious_action_dt, + FIRST(LOGIN_FAILED_SAME_USER.dt) as brutforce_begin, + FIRST(LOGIN_SUCCESS_SAME_USER.dt) as brutforce_end, + LAST(LOGIN_SUCCESS_SAME_USER.user) as brutforce_login + + ONE ROW PER MATCH + AFTER MATCH SKIP TO NEXT ROW + PATTERN ( + LOGIN_SUCCESS_REMOTE ANY_ROW1* SUSPICIOUS_ACTION_SOON | + (LOGIN_FAILED_SAME_USER ANY_ROW2*){2,} LOGIN_SUCCESS_SAME_USER + ) + DEFINE + LOGIN_SUCCESS_REMOTE as + LOGIN_SUCCESS_REMOTE.ev_type = "login" and + LOGIN_SUCCESS_REMOTE.ev_status = "success" and + LOGIN_SUCCESS_REMOTE.vpn = true and + COALESCE(LOGIN_SUCCESS_REMOTE.dt - FIRST(LOGIN_FAILED_SAME_USER.dt) <= 500, TRUE), + ANY_ROW1 as + COALESCE(ANY_ROW1.dt - FIRST(LOGIN_SUCCESS_REMOTE.dt) <= 500, TRUE), + SUSPICIOUS_ACTION_SOON as + SUSPICIOUS_ACTION_SOON.host = LAST(LOGIN_SUCCESS_REMOTE.host) and + SUSPICIOUS_ACTION_SOON.ev_type = "delete_all" and + COALESCE(SUSPICIOUS_ACTION_SOON.dt - FIRST(LOGIN_SUCCESS_REMOTE.dt) <= 500, TRUE), + LOGIN_FAILED_SAME_USER as + LOGIN_FAILED_SAME_USER.ev_type = "login" and + LOGIN_FAILED_SAME_USER.ev_status <> "success" and + (LAST(LOGIN_FAILED_SAME_USER.user) IS NULL + or LAST(LOGIN_FAILED_SAME_USER.user) = LOGIN_FAILED_SAME_USER.user + ) and COALESCE(LOGIN_FAILED_SAME_USER.dt - FIRST(LOGIN_FAILED_SAME_USER.dt) <= 500, TRUE), + ANY_ROW2 as + COALESCE(ANY_ROW2.dt - FIRST(LOGIN_FAILED_SAME_USER.dt) <= 500, TRUE), + LOGIN_SUCCESS_SAME_USER as + LOGIN_SUCCESS_SAME_USER.ev_type = "login" and + LOGIN_SUCCESS_SAME_USER.ev_status = "success" and + LOGIN_SUCCESS_SAME_USER.user = LAST(LOGIN_FAILED_SAME_USER.user) and + COALESCE(LOGIN_SUCCESS_SAME_USER.dt - FIRST(LOGIN_FAILED_SAME_USER.dt) <= 500, TRUE) +) AS MATCHED +; + diff --git a/yql/essentials/tests/sql/suites/match_recognize/simple_paritioning-streaming.sql b/yql/essentials/tests/sql/suites/match_recognize/simple_paritioning-streaming.sql new file mode 100644 index 00000000000..2c3478956c7 --- /dev/null +++ b/yql/essentials/tests/sql/suites/match_recognize/simple_paritioning-streaming.sql @@ -0,0 +1,44 @@ +pragma FeatureR010="prototype"; +pragma config.flags("MatchRecognizeStream", "force"); + +$data = [ +<|dt:15, host:"fqdn1", key:0|>, +<|dt:16, host:"fqdn1", key:1|>, +<|dt:17, host:"fqdn1", key:2|>, +<|dt:18, host:"fqdn1", key:3|>, +<|dt:19, host:"fqdn1", key:4|>, +<|dt:20, host:"fqdn1", key:5|>, +<|dt:21, host:"fqdn1", key:6|>, +<|dt:22, host:"fqdn1", key:7|>, +<|dt:23, host:"fqdn_2", key:0|>, +<|dt:24, host:"fqdn1", key:8|>, +<|dt:25, host:"fqdn1", key:9|>, +<|dt:26, host:"fqdn1", key:10|>, +<|dt:27, host:"fqdn__3", key:30|>, +<|dt:28, host:"fqdn__3", key:1|>, +<|dt:29, host:"fqdn__3", key:2|>, +<|dt:30, host:"fqdn__3", key:3|>, +<|dt:31, host:"fqdn__3", key:4|>, +<|dt:32, host:"fqdn1", key:11|>, +<|dt:33, host:"fqdn1", key:12|>, +<|dt:34, host:"fqdn1", key:13|>, +<|dt:35, host:"fqdn1", key:14|>, +<|dt:36, host:"fqdn__3", key:15|> +]; + +select * from AS_TABLE($data) MATCH_RECOGNIZE( + PARTITION BY host + ORDER BY CAST(dt as Timestamp) + MEASURES + Last(Q.dt) as T, + First(Y.key) as Key + ONE ROW PER MATCH + AFTER MATCH SKIP TO NEXT ROW + PATTERN ( + (Y Q) + ) + DEFINE + Y as (Y.key) % 3 = 0, + Q as (Q.key) % 3 <> 0 + ) as MR +ORDER BY MR.T; diff --git a/yql/essentials/tests/sql/suites/match_recognize/simple_paritioning.sql b/yql/essentials/tests/sql/suites/match_recognize/simple_paritioning.sql new file mode 100644 index 00000000000..f52c0c6f75a --- /dev/null +++ b/yql/essentials/tests/sql/suites/match_recognize/simple_paritioning.sql @@ -0,0 +1,44 @@ +pragma FeatureR010="prototype"; +pragma config.flags("MatchRecognizeStream", "disable"); + +$data = [ +<|dt:15, host:"fqdn1", key:0|>, +<|dt:16, host:"fqdn1", key:1|>, +<|dt:17, host:"fqdn1", key:2|>, +<|dt:18, host:"fqdn1", key:3|>, +<|dt:19, host:"fqdn1", key:4|>, +<|dt:20, host:"fqdn1", key:5|>, +<|dt:21, host:"fqdn1", key:6|>, +<|dt:22, host:"fqdn1", key:7|>, +<|dt:23, host:"fqdn_2", key:0|>, +<|dt:24, host:"fqdn1", key:8|>, +<|dt:25, host:"fqdn1", key:9|>, +<|dt:26, host:"fqdn1", key:10|>, +<|dt:27, host:"fqdn__3", key:30|>, +<|dt:28, host:"fqdn__3", key:1|>, +<|dt:29, host:"fqdn__3", key:2|>, +<|dt:30, host:"fqdn__3", key:3|>, +<|dt:31, host:"fqdn__3", key:4|>, +<|dt:32, host:"fqdn1", key:11|>, +<|dt:33, host:"fqdn1", key:12|>, +<|dt:34, host:"fqdn1", key:13|>, +<|dt:35, host:"fqdn1", key:14|>, +<|dt:36, host:"fqdn__3", key:15|> +]; + +select * from AS_TABLE($data) MATCH_RECOGNIZE( + PARTITION BY host + ORDER BY dt + MEASURES + Last(Q.dt) as T, + First(Y.key) as Key + ONE ROW PER MATCH + AFTER MATCH SKIP TO NEXT ROW + PATTERN ( + (Y Q) + ) + DEFINE + Y as (Y.key) % 3 = 0, + Q as (Q.key) % 3 <> 0 + ) as MR +ORDER BY MR.T; diff --git a/yql/essentials/tests/sql/suites/match_recognize/test_type-streaming.sql b/yql/essentials/tests/sql/suites/match_recognize/test_type-streaming.sql new file mode 100644 index 00000000000..2b5072434ba --- /dev/null +++ b/yql/essentials/tests/sql/suites/match_recognize/test_type-streaming.sql @@ -0,0 +1,74 @@ +pragma FeatureR010="prototype"; +pragma config.flags("MatchRecognizeStream", "force"); + +$data = [<|dt:4, host:"fqdn1", key:14|>]; + +-- NoPartitionNoMeasure +select * from AS_TABLE($data) MATCH_RECOGNIZE( + ORDER BY CAST(dt as Timestamp) + ONE ROW PER MATCH + AFTER MATCH SKIP TO NEXT ROW + PATTERN ( + Y + ) + DEFINE + Y as NULL + ); + +--NoPartitionStringMeasure +select * from AS_TABLE($data) MATCH_RECOGNIZE( + ORDER BY CAST(dt as Timestamp) + MEASURES + "SomeString" as Measure1 + ONE ROW PER MATCH + AFTER MATCH SKIP TO NEXT ROW + PATTERN ( + Q + ) + DEFINE + Q as TRUE + ); + +--IntPartitionColNoMeasure +select * from AS_TABLE($data) MATCH_RECOGNIZE( + PARTITION BY key + ORDER BY CAST(dt as Timestamp) + ONE ROW PER MATCH + AFTER MATCH SKIP TO NEXT ROW + PATTERN ( + L + ) + DEFINE + L as JUST(TRUE) + ); + +--StringPartitionColStringMeasure +select * from AS_TABLE($data) MATCH_RECOGNIZE( + PARTITION BY host + ORDER BY CAST(dt as Timestamp) + MEASURES + "SomeString" as Measure1 + ONE ROW PER MATCH + AFTER MATCH SKIP TO NEXT ROW + PATTERN ( + Y + ) + DEFINE + Y as TRUE + ); + +--TwoPartitionColsTwoMeasures +select * from AS_TABLE($data) MATCH_RECOGNIZE( + PARTITION BY host, key + ORDER BY CAST(dt as Timestamp) + MEASURES + "SomeString" as S, + 345 as I + ONE ROW PER MATCH + AFTER MATCH SKIP TO NEXT ROW + PATTERN ( + Q + ) + DEFINE + Q as TRUE +); diff --git a/yql/essentials/tests/sql/suites/match_recognize/test_type.sql b/yql/essentials/tests/sql/suites/match_recognize/test_type.sql new file mode 100644 index 00000000000..9125b856bf6 --- /dev/null +++ b/yql/essentials/tests/sql/suites/match_recognize/test_type.sql @@ -0,0 +1,75 @@ +pragma FeatureR010="prototype"; +pragma config.flags("MatchRecognizeStream", "disable"); + +$data = [<|dt:4, host:"fqdn1", key:14|>]; + + +-- NoPartitionNoMeasure +select * from AS_TABLE($data) MATCH_RECOGNIZE( + ORDER BY CAST(dt as Timestamp) + ONE ROW PER MATCH + AFTER MATCH SKIP TO NEXT ROW + PATTERN ( + Y + ) + DEFINE + Y as NULL +); + +--NoPartitionStringMeasure +select * from AS_TABLE($data) MATCH_RECOGNIZE( + ORDER BY CAST(dt as Timestamp) + MEASURES + "SomeString" as Measure1 + ONE ROW PER MATCH + AFTER MATCH SKIP TO NEXT ROW + PATTERN ( + Q + ) + DEFINE + Q as TRUE +); + +--IntPartitionColNoMeasure +select * from AS_TABLE($data) MATCH_RECOGNIZE( + PARTITION BY dt + ORDER BY CAST(dt as Timestamp) + ONE ROW PER MATCH + AFTER MATCH SKIP TO NEXT ROW + PATTERN ( + L + ) + DEFINE + L as JUST(TRUE) +); + +--StringPartitionColStringMeasure +select * from AS_TABLE($data) MATCH_RECOGNIZE( + PARTITION BY host + ORDER BY CAST(dt as Timestamp) + MEASURES + "SomeString" as Measure1 + ONE ROW PER MATCH + AFTER MATCH SKIP TO NEXT ROW + PATTERN ( + Y + ) + DEFINE + Y as TRUE +); + +--TwoPartitionColsTwoMeasures +select * from AS_TABLE($data) MATCH_RECOGNIZE( + PARTITION BY host, dt + ORDER BY CAST(dt as Timestamp) + MEASURES + "SomeString" as S, + 345 as I + ONE ROW PER MATCH + AFTER MATCH SKIP TO NEXT ROW + PATTERN ( + Q + ) + DEFINE + Q as JUST(TRUE) +); diff --git a/yql/essentials/tests/sql/suites/match_recognize/test_type_predicate.cfg b/yql/essentials/tests/sql/suites/match_recognize/test_type_predicate.cfg new file mode 100644 index 00000000000..5dae597903c --- /dev/null +++ b/yql/essentials/tests/sql/suites/match_recognize/test_type_predicate.cfg @@ -0,0 +1 @@ +xfail diff --git a/yql/essentials/tests/sql/suites/match_recognize/test_type_predicate.sql b/yql/essentials/tests/sql/suites/match_recognize/test_type_predicate.sql new file mode 100644 index 00000000000..654c537ea44 --- /dev/null +++ b/yql/essentials/tests/sql/suites/match_recognize/test_type_predicate.sql @@ -0,0 +1,17 @@ +/* custom error:DEFINE expression must be a predicate*/ +pragma FeatureR010="prototype"; +pragma config.flags("MatchRecognizeStream", "disable"); + +$data = [<||>]; + +$BadPredicate = select TableRow() from (select * from AS_TABLE($data) MATCH_RECOGNIZE( + ONE ROW PER MATCH + AFTER MATCH SKIP TO NEXT ROW + PATTERN ( + A + ) + DEFINE + A as 123 -- must fail, Bool expected +)); + +select FormatType(TypeOf($BadPredicate)); diff --git a/yql/essentials/tests/sql/suites/params/missing_param_fail.cfg b/yql/essentials/tests/sql/suites/params/missing_param_fail.cfg new file mode 100644 index 00000000000..6fdcfa62bec --- /dev/null +++ b/yql/essentials/tests/sql/suites/params/missing_param_fail.cfg @@ -0,0 +1,2 @@ +xfail +param $x p_int64.json
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/params/missing_param_fail.sql b/yql/essentials/tests/sql/suites/params/missing_param_fail.sql new file mode 100644 index 00000000000..49614fb221b --- /dev/null +++ b/yql/essentials/tests/sql/suites/params/missing_param_fail.sql @@ -0,0 +1,4 @@ +/* custom error:Missing value for parameter: $y*/ +declare $x as int; +declare $y as int; +select $x + $y; diff --git a/yql/essentials/tests/sql/suites/params/no_params_fail.cfg b/yql/essentials/tests/sql/suites/params/no_params_fail.cfg new file mode 100644 index 00000000000..eb2e5315d1e --- /dev/null +++ b/yql/essentials/tests/sql/suites/params/no_params_fail.cfg @@ -0,0 +1 @@ +xfail
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/params/no_params_fail.sql b/yql/essentials/tests/sql/suites/params/no_params_fail.sql new file mode 100644 index 00000000000..4acbca74095 --- /dev/null +++ b/yql/essentials/tests/sql/suites/params/no_params_fail.sql @@ -0,0 +1,3 @@ +/* custom error:Missing value for parameter: $x*/ +declare $x as int; +select 2 * $x; diff --git a/yql/essentials/tests/sql/suites/params/param_type_mismatch_fail.cfg b/yql/essentials/tests/sql/suites/params/param_type_mismatch_fail.cfg new file mode 100644 index 00000000000..6fdcfa62bec --- /dev/null +++ b/yql/essentials/tests/sql/suites/params/param_type_mismatch_fail.cfg @@ -0,0 +1,2 @@ +xfail +param $x p_int64.json
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/params/param_type_mismatch_fail.sql b/yql/essentials/tests/sql/suites/params/param_type_mismatch_fail.sql new file mode 100644 index 00000000000..e3fd5e0927e --- /dev/null +++ b/yql/essentials/tests/sql/suites/params/param_type_mismatch_fail.sql @@ -0,0 +1,3 @@ +/* custom error:Unexpected symbol "-" at pos 0 in string "-21"*/ +declare $x as UInt32; +select 2 * $x; diff --git a/yql/essentials/tests/sql/suites/pg/distinct_all_projection.sql b/yql/essentials/tests/sql/suites/pg/distinct_all_projection.sql new file mode 100644 index 00000000000..f0453afde4a --- /dev/null +++ b/yql/essentials/tests/sql/suites/pg/distinct_all_projection.sql @@ -0,0 +1,2 @@ +--!syntax_pg +select distinct x from (values (1),(2),(1),(null::int4)) a(x) diff --git a/yql/essentials/tests/sql/suites/pg/is_predicate.sql b/yql/essentials/tests/sql/suites/pg/is_predicate.sql new file mode 100644 index 00000000000..c13e0e4e4ed --- /dev/null +++ b/yql/essentials/tests/sql/suites/pg/is_predicate.sql @@ -0,0 +1,7 @@ +--!syntax_pg +select c, + c is true as "true", c is not true as "~true", + c is false as "false", c is not false as "~false", + c is unknown as "unk", c is not unknown as "~unk" +from (values (true), (false), (null)) as t(c) + diff --git a/yql/essentials/tests/sql/suites/pg/join_using_table_used_order2.cfg b/yql/essentials/tests/sql/suites/pg/join_using_table_used_order2.cfg new file mode 100644 index 00000000000..eb2e5315d1e --- /dev/null +++ b/yql/essentials/tests/sql/suites/pg/join_using_table_used_order2.cfg @@ -0,0 +1 @@ +xfail
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/pg/join_using_table_used_order2.sql b/yql/essentials/tests/sql/suites/pg/join_using_table_used_order2.sql new file mode 100644 index 00000000000..1a99d4d1fc5 --- /dev/null +++ b/yql/essentials/tests/sql/suites/pg/join_using_table_used_order2.sql @@ -0,0 +1,11 @@ +--!syntax_pg +/* custom error:common column name "zoo" appears more than once in left table*/ +select * from + ((select 1 as foo, 1 as bar, 1 as zoo) aa + join + (select 1 as foo, 1 as bar, 1 as zoo) bb + using (foo) + join + (select 1 as bar, 1 as zoo) cc + using (zoo) + ) diff --git a/yql/essentials/tests/sql/suites/pg/select_join_full.sql b/yql/essentials/tests/sql/suites/pg/select_join_full.sql new file mode 100644 index 00000000000..2518366b321 --- /dev/null +++ b/yql/essentials/tests/sql/suites/pg/select_join_full.sql @@ -0,0 +1,3 @@ +--!syntax_pg +select * from (select 1 as x ) u full join (select 1 as y) v on u.x=v.y; +select * from (select 1 as x ) u full join (select 2 as y) v on u.x=v.y; diff --git a/yql/essentials/tests/sql/suites/pg/select_join_full3.sql b/yql/essentials/tests/sql/suites/pg/select_join_full3.sql new file mode 100644 index 00000000000..c00fc629a03 --- /dev/null +++ b/yql/essentials/tests/sql/suites/pg/select_join_full3.sql @@ -0,0 +1,3 @@ +--!syntax_pg +select * from (select 1 as x ) u full join (select 1 as y) v on u.x=v.y full join (select 1 as z) r on v.y=r.z; +select * from (select 1 as x ) u full join (select 2 as y) v on u.x=v.y full join (select 3 as z) r on v.y=r.z; diff --git a/yql/essentials/tests/sql/suites/pg/select_join_full_const.sql b/yql/essentials/tests/sql/suites/pg/select_join_full_const.sql new file mode 100644 index 00000000000..605eb5c89c3 --- /dev/null +++ b/yql/essentials/tests/sql/suites/pg/select_join_full_const.sql @@ -0,0 +1,32 @@ +--!syntax_pg +select * from (values (1,2),(3,4)) as a(x,y) +full join (values (1,2),(2,5)) as b(u,v) +on 1+1=2; + +select * from (select * from (values (1,2),(3,4)) as a(x,y) limit 0) a +full join (values (1,2),(2,5)) as b(u,v) +on 1+1=2; + +select * from (values (1,2),(3,4)) as a(x,y) +full join (select * from (values (1,2),(2,5)) as b(u,v) limit 0) b +on 1+1=2; + +select * from (select * from (values (1,2),(3,4)) as a(x,y) limit 0) a +full join (select * from (values (1,2),(2,5)) as b(u,v) limit 0) b +on 1+1=2; + +select * from (values (1,2),(3,4)) as a(x,y) +full join (values (1,2),(2,5)) as b(u,v) +on 1+1=3; + +select * from (select * from (values (1,2),(3,4)) as a(x,y) limit 0) a +full join (values (1,2),(2,5)) as b(u,v) +on 1+1=3; + +select * from (values (1,2),(3,4)) as a(x,y) +full join (select * from (values (1,2),(2,5)) as b(u,v) limit 0) b +on 1+1=3; + +select * from (select * from (values (1,2),(3,4)) as a(x,y) limit 0) a +full join (select * from (values (1,2),(2,5)) as b(u,v) limit 0) b +on 1+1=3; diff --git a/yql/essentials/tests/sql/suites/pg/select_join_full_equi.sql b/yql/essentials/tests/sql/suites/pg/select_join_full_equi.sql new file mode 100644 index 00000000000..6a1302a61b1 --- /dev/null +++ b/yql/essentials/tests/sql/suites/pg/select_join_full_equi.sql @@ -0,0 +1,8 @@ +--!syntax_pg +select * from (values (1,2),(3,4)) as a(x,y) +full join (values (1,2),(2,5)) as b(u,v) +on a.x = b.u; + +select * from (values (1,2),(3,4)) as a(x,y) +full join (values (1,2),(2,5)) as b(u,v) +on a.x = b.u and b.v = a.y; diff --git a/yql/essentials/tests/sql/suites/pg/select_join_full_equi_and_const.sql b/yql/essentials/tests/sql/suites/pg/select_join_full_equi_and_const.sql new file mode 100644 index 00000000000..05f1ca4b141 --- /dev/null +++ b/yql/essentials/tests/sql/suites/pg/select_join_full_equi_and_const.sql @@ -0,0 +1,8 @@ +--!syntax_pg +select * from (values (1,2),(3,4)) as a(x,y) +full join (values (1,2),(2,5)) as b(u,v) +on a.x = b.u and 1+1=2; + +select * from (values (1,2),(3,4)) as a(x,y) +full join (values (1,2),(2,5)) as b(u,v) +on a.x = b.u and 1+1=3; diff --git a/yql/essentials/tests/sql/suites/pg/select_join_full_equi_and_one.sql b/yql/essentials/tests/sql/suites/pg/select_join_full_equi_and_one.sql new file mode 100644 index 00000000000..f6a40eeb3f2 --- /dev/null +++ b/yql/essentials/tests/sql/suites/pg/select_join_full_equi_and_one.sql @@ -0,0 +1,8 @@ +--!syntax_pg +select * from (values (1,2),(3,4)) as a(x,y) +full join (values (1,2),(2,5)) as b(u,v) +on a.x = b.u and a.x=1; + +select * from (values (1,2),(3,4)) as a(x,y) +full join (values (1,2),(2,5)) as b(u,v) +on a.x = b.u and b.u=1;
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/pg/select_join_full_one.sql b/yql/essentials/tests/sql/suites/pg/select_join_full_one.sql new file mode 100644 index 00000000000..b5cdcb6967f --- /dev/null +++ b/yql/essentials/tests/sql/suites/pg/select_join_full_one.sql @@ -0,0 +1,16 @@ +--!syntax_pg +select * from (values (1,2),(3,4)) as a(x,y) +full join (values (1,2),(2,5)) as b(u,v) +on a.x = 1; + +select * from (values (1,2),(3,4)) as a(x,y) +full join (values (1,2),(2,5)) as b(u,v) +on a.x = 0; + +select * from (values (1,2),(3,4)) as a(x,y) +full join (values (1,2),(2,5)) as b(u,v) +on b.u = 1; + +select * from (values (1,2),(3,4)) as a(x,y) +full join (values (1,2),(2,5)) as b(u,v) +on b.u = 0; diff --git a/yql/essentials/tests/sql/suites/pg/select_join_right_equi.sql b/yql/essentials/tests/sql/suites/pg/select_join_right_equi.sql new file mode 100644 index 00000000000..027ffc83d83 --- /dev/null +++ b/yql/essentials/tests/sql/suites/pg/select_join_right_equi.sql @@ -0,0 +1,8 @@ +--!syntax_pg +select * from (values (1,2),(3,4)) as a(x,y) +right join (values (1,2),(2,5)) as b(u,v) +on a.x = b.u; + +select * from (values (1,2),(3,4)) as a(x,y) +right join (values (1,2),(2,5)) as b(u,v) +on a.x = b.u and b.v = a.y; diff --git a/yql/essentials/tests/sql/suites/pg/select_join_right_equi_and_const.sql b/yql/essentials/tests/sql/suites/pg/select_join_right_equi_and_const.sql new file mode 100644 index 00000000000..0c9e283c0fc --- /dev/null +++ b/yql/essentials/tests/sql/suites/pg/select_join_right_equi_and_const.sql @@ -0,0 +1,8 @@ +--!syntax_pg +select * from (values (1,2),(3,4)) as a(x,y) +right join (values (1,2),(2,5)) as b(u,v) +on a.x = b.u and 1+1=2; + +select * from (values (1,2),(3,4)) as a(x,y) +right join (values (1,2),(2,5)) as b(u,v) +on a.x = b.u and 1+1=3; diff --git a/yql/essentials/tests/sql/suites/pg/select_join_right_equi_and_one.sql b/yql/essentials/tests/sql/suites/pg/select_join_right_equi_and_one.sql new file mode 100644 index 00000000000..a3cb350b252 --- /dev/null +++ b/yql/essentials/tests/sql/suites/pg/select_join_right_equi_and_one.sql @@ -0,0 +1,8 @@ +--!syntax_pg +select * from (values (1,2),(3,4)) as a(x,y) +right join (values (1,2),(2,5)) as b(u,v) +on a.x = b.u and a.x=1; + +select * from (values (1,2),(3,4)) as a(x,y) +right join (values (1,2),(2,5)) as b(u,v) +on a.x = b.u and b.u=1;
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/pg/select_join_right_one.sql b/yql/essentials/tests/sql/suites/pg/select_join_right_one.sql new file mode 100644 index 00000000000..073b28e3345 --- /dev/null +++ b/yql/essentials/tests/sql/suites/pg/select_join_right_one.sql @@ -0,0 +1,16 @@ +--!syntax_pg +select * from (values (1,2),(3,4)) as a(x,y) +right join (values (1,2),(2,5)) as b(u,v) +on a.x = 1; + +select * from (values (1,2),(3,4)) as a(x,y) +right join (values (1,2),(2,5)) as b(u,v) +on a.x = 0; + +select * from (values (1,2),(3,4)) as a(x,y) +right join (values (1,2),(2,5)) as b(u,v) +on b.u = 1; + +select * from (values (1,2),(3,4)) as a(x,y) +right join (values (1,2),(2,5)) as b(u,v) +on b.u = 0; diff --git a/yql/essentials/tests/sql/suites/pg/select_unionall_scalar.sql b/yql/essentials/tests/sql/suites/pg/select_unionall_scalar.sql new file mode 100644 index 00000000000..a7f95a1e48a --- /dev/null +++ b/yql/essentials/tests/sql/suites/pg/select_unionall_scalar.sql @@ -0,0 +1,6 @@ +--!syntax_pg +select * from (select 1 as x, 3 as y) u +union all +select * from (select null::int4 as y, 2 as x) v +union all +select * from (select 4 as y, null::int4 as x) v diff --git a/yql/essentials/tests/sql/suites/pg/select_win_count.sql b/yql/essentials/tests/sql/suites/pg/select_win_count.sql new file mode 100644 index 00000000000..ae8c9cce1c9 --- /dev/null +++ b/yql/essentials/tests/sql/suites/pg/select_win_count.sql @@ -0,0 +1,8 @@ +--!syntax_pg +select x,y,count(x) over w +from (values (1,2),(null::int4,3),(2,4),(2,5)) as a(x,y) +window w as (); + +select x,y,count(x) over w +from (values (1,2),(null::int4,3),(2,4),(2,5)) as a(x,y) +window w as (order by y); diff --git a/yql/essentials/tests/sql/suites/pg/select_win_max_null.sql b/yql/essentials/tests/sql/suites/pg/select_win_max_null.sql new file mode 100644 index 00000000000..69ffb29ab1b --- /dev/null +++ b/yql/essentials/tests/sql/suites/pg/select_win_max_null.sql @@ -0,0 +1,8 @@ +--!syntax_pg +select x,y,max(x) over w +from (values (1,2),(null::int4,3),(2,4),(2,5)) as a(x,y) +window w as (); + +select x,y,max(x) over w +from (values (1,2),(null::int4,3),(2,4),(2,5)) as a(x,y) +window w as (order by y); diff --git a/yql/essentials/tests/sql/suites/pg/select_win_min_null.sql b/yql/essentials/tests/sql/suites/pg/select_win_min_null.sql new file mode 100644 index 00000000000..0fbd5f16910 --- /dev/null +++ b/yql/essentials/tests/sql/suites/pg/select_win_min_null.sql @@ -0,0 +1,8 @@ +--!syntax_pg +select x,y,min(x) over w +from (values (1,2),(null::int4,3),(2,4),(2,5)) as a(x,y) +window w as (); + +select x,y,min(x) over w +from (values (1,2),(null::int4,3),(2,4),(2,5)) as a(x,y) +window w as (order by y); diff --git a/yql/essentials/tests/sql/suites/pg/select_win_sum_null.sql b/yql/essentials/tests/sql/suites/pg/select_win_sum_null.sql new file mode 100644 index 00000000000..f0948aed20a --- /dev/null +++ b/yql/essentials/tests/sql/suites/pg/select_win_sum_null.sql @@ -0,0 +1,8 @@ +--!syntax_pg +select x,y,sum(x) over w +from (values (1,2),(null::int4,3),(2,4),(2,5)) as a(x,y) +window w as (); + +select x,y,sum(x) over w +from (values (1,2),(null::int4,3),(2,4),(2,5)) as a(x,y) +window w as (order by y); diff --git a/yql/essentials/tests/sql/suites/pg_duplicated/ambigous_order_by_from_join.cfg b/yql/essentials/tests/sql/suites/pg_duplicated/ambigous_order_by_from_join.cfg new file mode 100644 index 00000000000..eb2e5315d1e --- /dev/null +++ b/yql/essentials/tests/sql/suites/pg_duplicated/ambigous_order_by_from_join.cfg @@ -0,0 +1 @@ +xfail
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/pg_duplicated/ambigous_order_by_from_join.sql b/yql/essentials/tests/sql/suites/pg_duplicated/ambigous_order_by_from_join.sql new file mode 100644 index 00000000000..587d7298553 --- /dev/null +++ b/yql/essentials/tests/sql/suites/pg_duplicated/ambigous_order_by_from_join.sql @@ -0,0 +1,9 @@ +--!syntax_pg +/* custom error:Column reference is ambiguous: x*/ +select a.x + 1 as y, b.x + 1 as z from + ((select 1 x) a + join + (select 1 x) b + on a.x = b.x) +order by x+1 +-- column reference "x" is ambiguous diff --git a/yql/essentials/tests/sql/suites/pg_duplicated/ambigous_order_by_from_table.cfg b/yql/essentials/tests/sql/suites/pg_duplicated/ambigous_order_by_from_table.cfg new file mode 100644 index 00000000000..eb2e5315d1e --- /dev/null +++ b/yql/essentials/tests/sql/suites/pg_duplicated/ambigous_order_by_from_table.cfg @@ -0,0 +1 @@ +xfail
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/pg_duplicated/ambigous_order_by_from_table.sql b/yql/essentials/tests/sql/suites/pg_duplicated/ambigous_order_by_from_table.sql new file mode 100644 index 00000000000..a5f79a68b35 --- /dev/null +++ b/yql/essentials/tests/sql/suites/pg_duplicated/ambigous_order_by_from_table.sql @@ -0,0 +1,6 @@ +--!syntax_pg +/* custom error:Column reference is ambiguous: x*/ +select a.* from + (select 1 x, 2 x) a +order by x +-- ORDER BY "x" is ambiguous diff --git a/yql/essentials/tests/sql/suites/pg_duplicated/ambigous_order_by_item_expression_from_projection.cfg b/yql/essentials/tests/sql/suites/pg_duplicated/ambigous_order_by_item_expression_from_projection.cfg new file mode 100644 index 00000000000..eb2e5315d1e --- /dev/null +++ b/yql/essentials/tests/sql/suites/pg_duplicated/ambigous_order_by_item_expression_from_projection.cfg @@ -0,0 +1 @@ +xfail
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/pg_duplicated/ambigous_order_by_item_expression_from_projection.sql b/yql/essentials/tests/sql/suites/pg_duplicated/ambigous_order_by_item_expression_from_projection.sql new file mode 100644 index 00000000000..cda9243446f --- /dev/null +++ b/yql/essentials/tests/sql/suites/pg_duplicated/ambigous_order_by_item_expression_from_projection.sql @@ -0,0 +1,5 @@ +--!syntax_pg +/* custom error: ORDER BY column reference 'y' is ambigous*/ +select x + 1 as y, x - 1 as y from + (select 1 x) a +order by y+1 diff --git a/yql/essentials/tests/sql/suites/pg_duplicated/ambigous_order_by_with_duplicates.cfg b/yql/essentials/tests/sql/suites/pg_duplicated/ambigous_order_by_with_duplicates.cfg new file mode 100644 index 00000000000..eb2e5315d1e --- /dev/null +++ b/yql/essentials/tests/sql/suites/pg_duplicated/ambigous_order_by_with_duplicates.cfg @@ -0,0 +1 @@ +xfail
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/pg_duplicated/ambigous_order_by_with_duplicates.sql b/yql/essentials/tests/sql/suites/pg_duplicated/ambigous_order_by_with_duplicates.sql new file mode 100644 index 00000000000..bd8fb0c34b3 --- /dev/null +++ b/yql/essentials/tests/sql/suites/pg_duplicated/ambigous_order_by_with_duplicates.sql @@ -0,0 +1,6 @@ +--!syntax_pg +/* custom error:ORDER BY column reference 'y' is ambigous*/ +select x + 1 as y, x - 1 as y from + (select 1 x) a +order by y +-- order by y is ambigous diff --git a/yql/essentials/tests/sql/suites/pg_duplicated/ambigous_order_by_wo_duplicated_in_projection.cfg b/yql/essentials/tests/sql/suites/pg_duplicated/ambigous_order_by_wo_duplicated_in_projection.cfg new file mode 100644 index 00000000000..eb2e5315d1e --- /dev/null +++ b/yql/essentials/tests/sql/suites/pg_duplicated/ambigous_order_by_wo_duplicated_in_projection.cfg @@ -0,0 +1 @@ +xfail
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/pg_duplicated/ambigous_order_by_wo_duplicated_in_projection.sql b/yql/essentials/tests/sql/suites/pg_duplicated/ambigous_order_by_wo_duplicated_in_projection.sql new file mode 100644 index 00000000000..6e837549259 --- /dev/null +++ b/yql/essentials/tests/sql/suites/pg_duplicated/ambigous_order_by_wo_duplicated_in_projection.sql @@ -0,0 +1,6 @@ +--!syntax_pg +/* custom error:Column reference is ambiguous: x*/ +select z from + (select 1 x, 1 x, 3 z) a +order by x +-- column reference "x" is ambiguous diff --git a/yql/essentials/tests/sql/suites/pg_duplicated/ambigous_ref.cfg b/yql/essentials/tests/sql/suites/pg_duplicated/ambigous_ref.cfg new file mode 100644 index 00000000000..5dae597903c --- /dev/null +++ b/yql/essentials/tests/sql/suites/pg_duplicated/ambigous_ref.cfg @@ -0,0 +1 @@ +xfail diff --git a/yql/essentials/tests/sql/suites/pg_duplicated/ambigous_ref.sql b/yql/essentials/tests/sql/suites/pg_duplicated/ambigous_ref.sql new file mode 100644 index 00000000000..ea0738592de --- /dev/null +++ b/yql/essentials/tests/sql/suites/pg_duplicated/ambigous_ref.sql @@ -0,0 +1,3 @@ +--!syntax_pg +/* custom error:Column reference is ambiguous: x*/ +select a.x from (select 1 x, 2 x) a diff --git a/yql/essentials/tests/sql/suites/pg_duplicated/qualified_star_from_using.sql b/yql/essentials/tests/sql/suites/pg_duplicated/qualified_star_from_using.sql new file mode 100644 index 00000000000..a0045b78631 --- /dev/null +++ b/yql/essentials/tests/sql/suites/pg_duplicated/qualified_star_from_using.sql @@ -0,0 +1,8 @@ +--!syntax_pg +select a.*, b.* from +( + (select 1 x) a + full join + (select 2 x) b + using(x) +)
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/pragma/yson_strict_fail.cfg b/yql/essentials/tests/sql/suites/pragma/yson_strict_fail.cfg new file mode 100644 index 00000000000..cf17d0b8d03 --- /dev/null +++ b/yql/essentials/tests/sql/suites/pragma/yson_strict_fail.cfg @@ -0,0 +1,2 @@ +xfail +udf yson2_udf diff --git a/yql/essentials/tests/sql/suites/pragma/yson_strict_fail.sql b/yql/essentials/tests/sql/suites/pragma/yson_strict_fail.sql new file mode 100644 index 00000000000..df71232b39a --- /dev/null +++ b/yql/essentials/tests/sql/suites/pragma/yson_strict_fail.sql @@ -0,0 +1,5 @@ +/* postgres can not */ +/* custom error:Cannot parse string value from integer*/ +PRAGMA yson.Strict; + +SELECT Yson::ConvertToString(Yson("122")); diff --git a/yql/essentials/tests/sql/suites/udf/wrong_args_fail.cfg b/yql/essentials/tests/sql/suites/udf/wrong_args_fail.cfg new file mode 100644 index 00000000000..f4ea431abcc --- /dev/null +++ b/yql/essentials/tests/sql/suites/udf/wrong_args_fail.cfg @@ -0,0 +1,2 @@ +xfail +udf strings_udf
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/udf/wrong_args_fail.sql b/yql/essentials/tests/sql/suites/udf/wrong_args_fail.sql new file mode 100644 index 00000000000..eed17d55899 --- /dev/null +++ b/yql/essentials/tests/sql/suites/udf/wrong_args_fail.sql @@ -0,0 +1,9 @@ +/* postgres can not */ +/* custom error:Callable expected at most 3 argument(s)*/ + +-- Find has optional args +select String::ReplaceAll(); -- too few +select String::ReplaceAll("abc"); -- too few + +select String::ReplaceAll("abc", "b", 2, 4); -- too many +select String::ReplaceAll("abc" , "b", 2, 4, 44); -- too many diff --git a/yql/essentials/tests/sql/suites/union/union_column_extention.sql b/yql/essentials/tests/sql/suites/union/union_column_extention.sql new file mode 100644 index 00000000000..592b6ba780a --- /dev/null +++ b/yql/essentials/tests/sql/suites/union/union_column_extention.sql @@ -0,0 +1,16 @@ +SELECT + 1 as x +UNION +SELECT + 'a' as y +UNION +SELECT + 1 as x, + 'a' as y +UNION +SELECT + 1 as x +UNION +SELECT + 1 as x, + 'b' as y; diff --git a/yql/essentials/tests/sql/suites/union_all/union_all_null.sql b/yql/essentials/tests/sql/suites/union_all/union_all_null.sql new file mode 100644 index 00000000000..960390755e5 --- /dev/null +++ b/yql/essentials/tests/sql/suites/union_all/union_all_null.sql @@ -0,0 +1,11 @@ +SELECT + 1/2 as jx, + 'a' as x, + NULL as jy, + NULL as y +UNION ALL +SELECT + NULL as jx, + NULL as x, + 2/1 as jy, + 'b' as y; diff --git a/yql/essentials/tests/sql/suites/weak_field/weak_member_string_copy.sql b/yql/essentials/tests/sql/suites/weak_field/weak_member_string_copy.sql new file mode 100644 index 00000000000..1a19c1cc51c --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/weak_member_string_copy.sql @@ -0,0 +1,3 @@ +/* postgres can not */ +$d = AsDict(AsTuple("fld","Россия, Санкт-Петербург, Петроградская набережная, 2-4"),AsTuple("_yql_fld","")); +select Yql::TryWeakMemberFromDict(Just($d), NULL, AsAtom("String"), AsAtom("fld")); |