diff options
| author | udovichenko-r <udovichenko-r@yandex-team.com> | 2024-11-19 14:58:38 +0300 |
|---|---|---|
| committer | udovichenko-r <udovichenko-r@yandex-team.com> | 2024-11-19 15:16:27 +0300 |
| commit | 24521403b1c44303e043ba540c09b1fe991c7474 (patch) | |
| tree | 341d1e7206bc7c143d04d2d96f05b6dc0655606d /yql/essentials/tests/sql/suites/insert | |
| parent | 72b3cd51dc3fb9d16975d353ea82fd85701393cc (diff) | |
| download | ydb-24521403b1c44303e043ba540c09b1fe991c7474.tar.gz | |
YQL-19206 Move contrib/ydb/library/yql/tests/sql/suites -> yql/essentials/tests/sql/suites
commit_hash:d0ef1f92b09c94db7c2408f946d2a4c62b603f00
Diffstat (limited to 'yql/essentials/tests/sql/suites/insert')
114 files changed, 851 insertions, 0 deletions
diff --git a/yql/essentials/tests/sql/suites/insert/after_group_by.sql b/yql/essentials/tests/sql/suites/insert/after_group_by.sql new file mode 100644 index 0000000000..c2abecae36 --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/after_group_by.sql @@ -0,0 +1,2 @@ +-- kikimr only: pragma kikimr.UnwrapReadTableValues = "false"; create table plato.Output (key varchar null, subkey varchar null, value varchar null, primary key (key)); commit; +insert into plato.Output (key, subkey, value) select key, subkey, max(value) from plato.Input group by key, subkey order by key; diff --git a/yql/essentials/tests/sql/suites/insert/anonymous_tables.sql b/yql/essentials/tests/sql/suites/insert/anonymous_tables.sql new file mode 100644 index 0000000000..93e6b3b041 --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/anonymous_tables.sql @@ -0,0 +1,14 @@ +/* postgres can not */ +use plato; +pragma yt.MapJoinLimit="1M"; + +insert into @a +select * from Input; + +commit; + +select count(*) from @a; +select * from @a limit 2; +select count(*) from @a as a cross join @a as b; + +commit; diff --git a/yql/essentials/tests/sql/suites/insert/append-proto_fail.cfg b/yql/essentials/tests/sql/suites/insert/append-proto_fail.cfg new file mode 100644 index 0000000000..6d87958c65 --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/append-proto_fail.cfg @@ -0,0 +1,4 @@ +xfail +in Input input.txt +out Output input_proto.txt +udf protobuf_udf diff --git a/yql/essentials/tests/sql/suites/insert/append-view_fail.cfg b/yql/essentials/tests/sql/suites/insert/append-view_fail.cfg new file mode 100644 index 0000000000..db398a8d2f --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/append-view_fail.cfg @@ -0,0 +1,5 @@ +xfail +in Input input.txt +in Input1 input.txt +in Input2 input2.txt +out Output input_view.txt diff --git a/yql/essentials/tests/sql/suites/insert/append-with_read_udf_fail.cfg b/yql/essentials/tests/sql/suites/insert/append-with_read_udf_fail.cfg new file mode 100644 index 0000000000..1d043c6420 --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/append-with_read_udf_fail.cfg @@ -0,0 +1,4 @@ +xfail +in Input input.txt +out Output input_with_read_udf.txt +udf dsv_udf diff --git a/yql/essentials/tests/sql/suites/insert/append-with_view.cfg b/yql/essentials/tests/sql/suites/insert/append-with_view.cfg new file mode 100644 index 0000000000..4feca3f4ed --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/append-with_view.cfg @@ -0,0 +1,4 @@ +in Input input.txt +out Output input_with_view.txt +udf python2_udf +providers yt diff --git a/yql/essentials/tests/sql/suites/insert/append.cfg b/yql/essentials/tests/sql/suites/insert/append.cfg new file mode 100644 index 0000000000..ad52c79527 --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/append.cfg @@ -0,0 +1,2 @@ +in Input input.txt +out Output output.txt diff --git a/yql/essentials/tests/sql/suites/insert/append.sql b/yql/essentials/tests/sql/suites/insert/append.sql new file mode 100644 index 0000000000..e4ce8e6422 --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/append.sql @@ -0,0 +1,5 @@ +/* postgres can not */ +/* multirun can not */ +insert into plato.Output +select * from plato.Input; + diff --git a/yql/essentials/tests/sql/suites/insert/append_after_replace.sql b/yql/essentials/tests/sql/suites/insert/append_after_replace.sql new file mode 100644 index 0000000000..96894b317c --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/append_after_replace.sql @@ -0,0 +1,6 @@ +/* postgres can not */ +/* multirun can not */ +-- kikimr only: pragma kikimr.UnwrapReadTableValues = "false"; create table plato.Output (key varchar null, subkey varchar null, value varchar null, primary key (key)); commit; +insert into plato.Output with truncate (key, subkey, value) select key, subkey, value from plato.Input; +commit; +insert into plato.Output (key, subkey, value) select key, subkey, value from plato.Output; diff --git a/yql/essentials/tests/sql/suites/insert/append_missing_null.sql b/yql/essentials/tests/sql/suites/insert/append_missing_null.sql new file mode 100644 index 0000000000..e92b426132 --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/append_missing_null.sql @@ -0,0 +1,14 @@ +/* postgres can not */ +use plato; + +insert into @foo +select null as x, 1 as y; + +commit; + +insert into @foo +select 2 as y; + +commit; + +select * from @foo; diff --git a/yql/essentials/tests/sql/suites/insert/append_sorted-to_sorted.cfg b/yql/essentials/tests/sql/suites/insert/append_sorted-to_sorted.cfg new file mode 100644 index 0000000000..1f640b66af --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/append_sorted-to_sorted.cfg @@ -0,0 +1,9 @@ +in Input input.txt +out Output1 sorted.txt +out Output2 sorted.txt +out Output3 sorted.txt +out Output4 sorted.txt +out Output5 sorted.txt +out Output6 sorted.txt +out Output7 sorted.txt +out Output8 sorted.txt diff --git a/yql/essentials/tests/sql/suites/insert/append_sorted-to_sorted_calc.cfg b/yql/essentials/tests/sql/suites/insert/append_sorted-to_sorted_calc.cfg new file mode 100644 index 0000000000..a47ebbb46d --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/append_sorted-to_sorted_calc.cfg @@ -0,0 +1,9 @@ +in Input input.txt +out Output1 sorted_calc.txt +out Output2 sorted_calc.txt +out Output3 sorted_calc.txt +out Output4 sorted_calc.txt +out Output5 sorted_calc.txt +out Output6 sorted_calc.txt +out Output7 sorted_calc.txt +out Output8 sorted_calc.txt diff --git a/yql/essentials/tests/sql/suites/insert/append_sorted-to_sorted_desc.cfg b/yql/essentials/tests/sql/suites/insert/append_sorted-to_sorted_desc.cfg new file mode 100644 index 0000000000..e337a07d80 --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/append_sorted-to_sorted_desc.cfg @@ -0,0 +1,9 @@ +in Input input.txt +out Output1 sorted_desc.txt +out Output2 sorted_desc.txt +out Output3 sorted_desc.txt +out Output4 sorted_desc.txt +out Output5 sorted_desc.txt +out Output6 sorted_desc.txt +out Output7 sorted_desc.txt +out Output8 sorted_desc.txt diff --git a/yql/essentials/tests/sql/suites/insert/append_sorted.cfg b/yql/essentials/tests/sql/suites/insert/append_sorted.cfg new file mode 100644 index 0000000000..8014b80c06 --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/append_sorted.cfg @@ -0,0 +1,9 @@ +in Input input.txt +out Output1 input.txt +out Output2 input.txt +out Output3 input.txt +out Output4 input.txt +out Output5 input.txt +out Output6 input.txt +out Output7 input.txt +out Output8 input.txt diff --git a/yql/essentials/tests/sql/suites/insert/append_sorted.sql b/yql/essentials/tests/sql/suites/insert/append_sorted.sql new file mode 100644 index 0000000000..cefc494453 --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/append_sorted.sql @@ -0,0 +1,25 @@ +/* multirun can not */ + +insert into plato.Output1 +select * from plato.Input order by key; + +insert into plato.Output2 +select * from plato.Input order by key, subkey; + +insert into plato.Output3 +select * from plato.Input order by key, subkey, value; + +insert into plato.Output4 +select * from plato.Input; + +insert into plato.Output5 +select * from plato.Input order by subkey; + +insert into plato.Output6 +select * from plato.Input order by key desc; + +insert into plato.Output7 +select * from plato.Input order by key desc, subkey; + +insert into plato.Output8 +select * from plato.Input order by key || subkey; diff --git a/yql/essentials/tests/sql/suites/insert/default.cfg b/yql/essentials/tests/sql/suites/insert/default.cfg new file mode 100644 index 0000000000..ad52c79527 --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/default.cfg @@ -0,0 +1,2 @@ +in Input input.txt +out Output output.txt diff --git a/yql/essentials/tests/sql/suites/insert/double_append_to_anonymous.cfg b/yql/essentials/tests/sql/suites/insert/double_append_to_anonymous.cfg new file mode 100644 index 0000000000..00dec6c3f8 --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/double_append_to_anonymous.cfg @@ -0,0 +1 @@ +res result.txt diff --git a/yql/essentials/tests/sql/suites/insert/double_append_to_anonymous.sql b/yql/essentials/tests/sql/suites/insert/double_append_to_anonymous.sql new file mode 100644 index 0000000000..af6aba3132 --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/double_append_to_anonymous.sql @@ -0,0 +1,14 @@ +/* postgres can not */ +use plato; + +INSERT INTO @tmp +SELECT 1 as id, "qwer" as val; +COMMIT; + +INSERT INTO @tmp +SELECT 2 as id, "asdf" as val; + +COMMIT; + +SELECT * +FROM @tmp;
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/insert/drop_sortness-calc.cfg b/yql/essentials/tests/sql/suites/insert/drop_sortness-calc.cfg new file mode 100644 index 0000000000..da3506e2e7 --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/drop_sortness-calc.cfg @@ -0,0 +1,2 @@ +in Input sorted_calc.txt +out Output output.txt diff --git a/yql/essentials/tests/sql/suites/insert/drop_sortness-desc.cfg b/yql/essentials/tests/sql/suites/insert/drop_sortness-desc.cfg new file mode 100644 index 0000000000..803556470a --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/drop_sortness-desc.cfg @@ -0,0 +1,2 @@ +in Input sorted_desc.txt +out Output output.txt diff --git a/yql/essentials/tests/sql/suites/insert/drop_sortness.cfg b/yql/essentials/tests/sql/suites/insert/drop_sortness.cfg new file mode 100644 index 0000000000..66737248b8 --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/drop_sortness.cfg @@ -0,0 +1,2 @@ +in Input sorted.txt +out Output output.txt diff --git a/yql/essentials/tests/sql/suites/insert/drop_sortness.sql b/yql/essentials/tests/sql/suites/insert/drop_sortness.sql new file mode 100644 index 0000000000..ccd7972ca6 --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/drop_sortness.sql @@ -0,0 +1,5 @@ +/* postgres can not */ +insert into plato.Output with truncate +SELECT + value +FROM plato.Input;
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/insert/fail_read_view_after_modify.cfg b/yql/essentials/tests/sql/suites/insert/fail_read_view_after_modify.cfg new file mode 100644 index 0000000000..a26fadcaa7 --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/fail_read_view_after_modify.cfg @@ -0,0 +1,3 @@ +xfail +in Input input.txt +out Output output.txt diff --git a/yql/essentials/tests/sql/suites/insert/fail_read_view_after_modify.sql b/yql/essentials/tests/sql/suites/insert/fail_read_view_after_modify.sql new file mode 100644 index 0000000000..aa2f928459 --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/fail_read_view_after_modify.sql @@ -0,0 +1,8 @@ +/* postgres can not */ +/* syntax version 1 */ +insert into plato.Output with truncate +select * from plato.Input; + +commit; + +select * from plato.Output view raw; diff --git a/yql/essentials/tests/sql/suites/insert/from_two_sorted_by_calc.sql b/yql/essentials/tests/sql/suites/insert/from_two_sorted_by_calc.sql new file mode 100644 index 0000000000..d2c42ab47e --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/from_two_sorted_by_calc.sql @@ -0,0 +1,15 @@ +/* postgres can not */ +use plato; + +insert into @f1 select * from Input order by key || "1"; +insert into @f2 select * from Input order by key || "2"; + +commit; + +insert into Output +select * from +( + select * from @f1 + union all + select * from @f2 +); diff --git a/yql/essentials/tests/sql/suites/insert/inferschema.txt b/yql/essentials/tests/sql/suites/insert/inferschema.txt new file mode 100644 index 0000000000..740bf0ddd9 --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/inferschema.txt @@ -0,0 +1,5 @@ +{"a"=1;"b"=1u;"c"=0.1;"d"="x";"e"=%false;"f"={"a"="b";"c"="d";"e"=[0;1;2]}}; +{"a"=2;"b"=2u;"c"=2.5;"d"="xx";"e"=%true;"f"=0.123}; +{"a"=-5;"b"=8u;"c"=10.;"d"="xxx";"f"="xyz"}; +{"b"=5u;"d"="skdjfnsdf"}; +{}; diff --git a/yql/essentials/tests/sql/suites/insert/inferschema.txt.attr b/yql/essentials/tests/sql/suites/insert/inferschema.txt.attr new file mode 100644 index 0000000000..ada1f836f8 --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/inferschema.txt.attr @@ -0,0 +1,3 @@ +{ + "infer_schema"=%true +} diff --git a/yql/essentials/tests/sql/suites/insert/input.txt b/yql/essentials/tests/sql/suites/insert/input.txt new file mode 100644 index 0000000000..65949ea745 --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/input.txt @@ -0,0 +1,4 @@ +{"key"="075";"subkey"="1";"value"="abc"}; +{"key"="800";"subkey"="2";"value"="ddd"}; +{"key"="020";"subkey"="3";"value"="q"}; +{"key"="150";"subkey"="4";"value"="qzz"}; diff --git a/yql/essentials/tests/sql/suites/insert/input.txt.attr b/yql/essentials/tests/sql/suites/insert/input.txt.attr new file mode 100644 index 0000000000..36b3c00c90 --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/input.txt.attr @@ -0,0 +1,5 @@ +{ + "_yql_row_spec"={ + "Type"=["StructType";[["key";["DataType";"String";];];["subkey";["DataType";"String";];];["value";["DataType";"String";];];];]; + } +} diff --git a/yql/essentials/tests/sql/suites/insert/input2.txt b/yql/essentials/tests/sql/suites/insert/input2.txt new file mode 100644 index 0000000000..92aa4a0344 --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/input2.txt @@ -0,0 +1 @@ +{"key"="999";"subkey"="9";"value"="xxx"}; diff --git a/yql/essentials/tests/sql/suites/insert/input_proto.txt b/yql/essentials/tests/sql/suites/insert/input_proto.txt new file mode 100644 index 0000000000..d2eaef7f29 --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/input_proto.txt @@ -0,0 +1 @@ +{"key"="1";"subkey"="\n\x10\x08\7\x12\x0C\n\ntext value";"value"="\n\x10\x08\7\x12\x0C\n\ntext value"}; diff --git a/yql/essentials/tests/sql/suites/insert/input_proto.txt.attr b/yql/essentials/tests/sql/suites/insert/input_proto.txt.attr new file mode 100644 index 0000000000..c924675835 --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/input_proto.txt.attr @@ -0,0 +1,32 @@ +{ + "_yql_proto_field_value"="#TA+H4sIAAAAAAAAA42Puw6CQBBFszw0DEp0Y4E2GmyolriJHyAPS6rVflFMTBCUR8Hn+KcuD0ui3WRy59wzcIJ1co9yntfOM8/KLKpuTv1KnKp0rqTdWBQk5uM5SEFhKhvZNqhKmE8CPAHF5yU30QbZmqUDCvAIpONuhmA/jL30WCywXsMI+SPuGJAOn0XdGVZDFhfl6qf1VuBdPAX1zJOq46vYAOSbkhh1Kosf4I2GC/mfhb3YcOD78EoYHfACkNva6FQjLZow11rCmJ3SqoivjaPXB4Sj9wEBmoL3ogEAAA=="; + "_yql_proto_field_subkey"="#TA+H4sIAAAAAAAAA42Puw6CQBBFszw0DEp0Y4E2GmyolriJHyAPS6rVflFMTBCUR8Hn+KcuD0ui3WRy59wzcIJ1co9yntfOM8/KLKpuTv1KnKp0rqTdWBQk5uM5SEFhKhvZNqhKmE8CPAHF5yU30QbZmqUDCvAIpONuhmA/jL30WCywXsMI+SPuGJAOn0XdGVZDFhfl6qf1VuBdPAX1zJOq46vYAOSbkhh1Kosf4I2GC/mfhb3YcOD78EoYHfACkNva6FQjLZow11rCmJ3SqoivjaPXB4Sj9wEBmoL3ogEAAA=="; + "_yql_row_spec" = { + "Type" = [ + "StructType"; + [ + [ + "key"; + [ + "DataType"; + "String" + ] + ]; + [ + "subkey"; + [ + "DataType"; + "String" + ] + ]; + [ + "value"; + [ + "DataType"; + "String" + ] + ] + ] + ] + } +} diff --git a/yql/essentials/tests/sql/suites/insert/input_view.txt b/yql/essentials/tests/sql/suites/insert/input_view.txt new file mode 100644 index 0000000000..068860024c --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/input_view.txt @@ -0,0 +1,3 @@ +select key,1 as `num` from Input1 +union all +select key,2 as `num` from Input2; diff --git a/yql/essentials/tests/sql/suites/insert/input_view.txt.attr b/yql/essentials/tests/sql/suites/insert/input_view.txt.attr new file mode 100644 index 0000000000..41ea51e276 --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/input_view.txt.attr @@ -0,0 +1,4 @@ +{ + "_yql_type"="view"; + "type"="document"; +} diff --git a/yql/essentials/tests/sql/suites/insert/input_with_read_udf.txt b/yql/essentials/tests/sql/suites/insert/input_with_read_udf.txt new file mode 100644 index 0000000000..ad6793f93c --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/input_with_read_udf.txt @@ -0,0 +1,4 @@ +{"key"="075";"subkey"="1";"value"="a=B@@b=a@@c=a"}; +{"key"="800";"subkey"="2";"value"="b=1@@c=2@@d=3"}; +{"key"="020";"subkey"="3";"value"="a=i@@c=j"}; +{"key"="150";"subkey"="4";"value"="a=A"}; diff --git a/yql/essentials/tests/sql/suites/insert/input_with_read_udf.txt.attr b/yql/essentials/tests/sql/suites/insert/input_with_read_udf.txt.attr new file mode 100644 index 0000000000..14544c6d7b --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/input_with_read_udf.txt.attr @@ -0,0 +1,32 @@ +{ + "_yql_read_udf"="Dsv.ReadRecord"; + "_yql_read_udf_run_config"="@@"; + "_yql_row_spec" = { + "Type" = [ + "StructType"; + [ + [ + "key"; + [ + "DataType"; + "String" + ] + ]; + [ + "subkey"; + [ + "DataType"; + "String" + ] + ]; + [ + "value"; + [ + "DataType"; + "String" + ] + ] + ] + ] + } +} diff --git a/yql/essentials/tests/sql/suites/insert/input_with_view.txt b/yql/essentials/tests/sql/suites/insert/input_with_view.txt new file mode 100644 index 0000000000..d15f960f63 --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/input_with_view.txt @@ -0,0 +1,10 @@ +{"key"="0";"subkey"="-";"value"="zero"}; +{"key"="1";"subkey"="-";"value"="one"}; +{"key"="2";"subkey"="-";"value"="two"}; +{"key"="3";"subkey"="-";"value"="three"}; +{"key"="4";"subkey"="-";"value"="four"}; +{"key"="5";"subkey"="-";"value"="five"}; +{"key"="6";"subkey"="-";"value"="six"}; +{"key"="7";"subkey"="-";"value"="seven"}; +{"key"="8";"subkey"="-";"value"="eight"}; +{"key"="9";"subkey"="-";"value"="nine"}; diff --git a/yql/essentials/tests/sql/suites/insert/input_with_view.txt.attr b/yql/essentials/tests/sql/suites/insert/input_with_view.txt.attr new file mode 100644 index 0000000000..501fdca6db --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/input_with_view.txt.attr @@ -0,0 +1,31 @@ +{ + "_yql_view_my"="$udfScript=\"def f(row):\n\treturn {'myvalue':b'm' + row.value}\n\";$udf=Python::f(Callable<(Struct<key:String,subkey:String,value:String>)->Struct<myvalue:String>>, $udfScript);PROCESS self USING $udf(TableRow());"; + "_yql_row_spec" = { + "Type" = [ + "StructType"; + [ + [ + "key"; + [ + "DataType"; + "String" + ] + ]; + [ + "subkey"; + [ + "DataType"; + "String" + ] + ]; + [ + "value"; + [ + "DataType"; + "String" + ] + ] + ] + ] + } +} diff --git a/yql/essentials/tests/sql/suites/insert/insert_from_other.cfg b/yql/essentials/tests/sql/suites/insert/insert_from_other.cfg new file mode 100644 index 0000000000..7cc1222553 --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/insert_from_other.cfg @@ -0,0 +1,2 @@ +in Input other.txt +out Output output.txt diff --git a/yql/essentials/tests/sql/suites/insert/insert_from_other.sql b/yql/essentials/tests/sql/suites/insert/insert_from_other.sql new file mode 100644 index 0000000000..9c295718ba --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/insert_from_other.sql @@ -0,0 +1,4 @@ +/* postgres can not */ +insert into plato.Output with truncate +select * from plato.Input; + diff --git a/yql/essentials/tests/sql/suites/insert/insert_null.sql b/yql/essentials/tests/sql/suites/insert/insert_null.sql new file mode 100644 index 0000000000..3aafd6c5a8 --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/insert_null.sql @@ -0,0 +1,4 @@ +use plato; + +insert into Output +select NULL as x; diff --git a/yql/essentials/tests/sql/suites/insert/insert_relabeled.sql b/yql/essentials/tests/sql/suites/insert/insert_relabeled.sql new file mode 100644 index 0000000000..cbe93546dd --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/insert_relabeled.sql @@ -0,0 +1,3 @@ +/* postgres can not */ +-- kikimr only: pragma kikimr.UnwrapReadTableValues = "false"; create table plato.Output (key varchar null, subkey varchar null, value varchar null, primary key (key)); commit; +insert into plato.Output (key, subkey, new_value, one_more_value) select key, subkey, value as new_value, "x" from plato.Input; diff --git a/yql/essentials/tests/sql/suites/insert/keepmeta-nonstrict_fail.cfg b/yql/essentials/tests/sql/suites/insert/keepmeta-nonstrict_fail.cfg new file mode 100644 index 0000000000..1f376d1f93 --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/keepmeta-nonstrict_fail.cfg @@ -0,0 +1,3 @@ +xfail +in Input input.txt +out Output other.txt diff --git a/yql/essentials/tests/sql/suites/insert/keepmeta-proto_fail.cfg b/yql/essentials/tests/sql/suites/insert/keepmeta-proto_fail.cfg new file mode 100644 index 0000000000..6d87958c65 --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/keepmeta-proto_fail.cfg @@ -0,0 +1,4 @@ +xfail +in Input input.txt +out Output input_proto.txt +udf protobuf_udf diff --git a/yql/essentials/tests/sql/suites/insert/keepmeta-view_fail.cfg b/yql/essentials/tests/sql/suites/insert/keepmeta-view_fail.cfg new file mode 100644 index 0000000000..db398a8d2f --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/keepmeta-view_fail.cfg @@ -0,0 +1,5 @@ +xfail +in Input input.txt +in Input1 input.txt +in Input2 input2.txt +out Output input_view.txt diff --git a/yql/essentials/tests/sql/suites/insert/keepmeta-with_read_udf_fail.cfg b/yql/essentials/tests/sql/suites/insert/keepmeta-with_read_udf_fail.cfg new file mode 100644 index 0000000000..1d043c6420 --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/keepmeta-with_read_udf_fail.cfg @@ -0,0 +1,4 @@ +xfail +in Input input.txt +out Output input_with_read_udf.txt +udf dsv_udf diff --git a/yql/essentials/tests/sql/suites/insert/keepmeta-with_view.cfg b/yql/essentials/tests/sql/suites/insert/keepmeta-with_view.cfg new file mode 100644 index 0000000000..4feca3f4ed --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/keepmeta-with_view.cfg @@ -0,0 +1,4 @@ +in Input input.txt +out Output input_with_view.txt +udf python2_udf +providers yt diff --git a/yql/essentials/tests/sql/suites/insert/keepmeta.cfg b/yql/essentials/tests/sql/suites/insert/keepmeta.cfg new file mode 100644 index 0000000000..ad52c79527 --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/keepmeta.cfg @@ -0,0 +1,2 @@ +in Input input.txt +out Output output.txt diff --git a/yql/essentials/tests/sql/suites/insert/keepmeta.sql b/yql/essentials/tests/sql/suites/insert/keepmeta.sql new file mode 100644 index 0000000000..360c609748 --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/keepmeta.sql @@ -0,0 +1,5 @@ +/* postgres can not */ +/* multirun can not */ +insert into plato.Output with (truncate, keepmeta) +select * from plato.Input; + diff --git a/yql/essentials/tests/sql/suites/insert/literals_to_string.sql b/yql/essentials/tests/sql/suites/insert/literals_to_string.sql new file mode 100644 index 0000000000..d3cb1ae817 --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/literals_to_string.sql @@ -0,0 +1,3 @@ +/* postgres can not */ +-- kikimr only: pragma kikimr.UnwrapReadTableValues = "false"; create table plato.Output (key varchar null, subkey varchar null, value varchar null, primary key (key)); commit; +insert into plato.Output (key, subkey, value) values (YQL::ToString(1), YQL::ToString(2), "3"); diff --git a/yql/essentials/tests/sql/suites/insert/merge_publish.cfg b/yql/essentials/tests/sql/suites/insert/merge_publish.cfg new file mode 100644 index 0000000000..1abba632a5 --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/merge_publish.cfg @@ -0,0 +1,3 @@ +in Input input.txt +out Output1 output1.txt +out Output2 input.txt diff --git a/yql/essentials/tests/sql/suites/insert/merge_publish.sql b/yql/essentials/tests/sql/suites/insert/merge_publish.sql new file mode 100644 index 0000000000..741b5dc366 --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/merge_publish.sql @@ -0,0 +1,20 @@ +/* postgres can not */ +/* kikimr can not */ +/* ignore plan diff */ +use plato; + +pragma yt.ScriptCpu="1.0"; + +insert into Output1 select "1" as key, subkey, value from Input; + +insert into Output2 select "2" as key, subkey, value from Input; + +insert into Output2 select "3" as key, subkey, value from Input; + +insert into Output1 select "4" as key, subkey, value from Input; + +pragma yt.ScriptCpu="2.0"; + +insert into Output1 select "5" as key, subkey, value from Input; + +insert into Output1 select "6" as key, subkey, value from Input; diff --git a/yql/essentials/tests/sql/suites/insert/multiappend_sorted.sql b/yql/essentials/tests/sql/suites/insert/multiappend_sorted.sql new file mode 100644 index 0000000000..bd33297afb --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/multiappend_sorted.sql @@ -0,0 +1,63 @@ +/* postgres can not */ +/* multirun can not */ + +insert into plato.Output with truncate +select * from plato.Input order by key; + +insert into plato.Output +select * from plato.Input; + +commit; +---------------------------------------- + +insert into plato.Output with truncate +select * from plato.Input; + +insert into plato.Output +select * from plato.Input order by key; + +commit; +---------------------------------------- + +insert into plato.Output with truncate +select * from plato.Input order by key, subkey; + +insert into plato.Output +select * from plato.Input order by key, subkey; + +commit; +---------------------------------------- + +insert into plato.Output with truncate +select * from plato.Input order by key; + +insert into plato.Output +select * from plato.Input order by key desc; + +commit; +---------------------------------------- + +insert into plato.Output with truncate +select * from plato.Input order by key; + +insert into plato.Output +select * from plato.Input order by key || subkey; + +commit; +---------------------------------------- + +insert into plato.Output with truncate +select * from plato.Input order by key desc; + +insert into plato.Output +select * from plato.Input order by key desc; + +commit; +---------------------------------------- + +insert into plato.Output with truncate +select * from plato.Input order by key || subkey; + +insert into plato.Output +select * from plato.Input order by key || subkey; + diff --git a/yql/essentials/tests/sql/suites/insert/other.txt b/yql/essentials/tests/sql/suites/insert/other.txt new file mode 100644 index 0000000000..004ddc583b --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/other.txt @@ -0,0 +1,4 @@ +{"key"="020";"subkey"="3";"value"="q"}; +{"key"="075";"subkey"="1";"value"="abc"}; +{"key"="150";"subkey"="4";"value"="qzz"}; +{"key"="800";"subkey"="2";"value"="ddd"}; diff --git a/yql/essentials/tests/sql/suites/insert/other.txt.attr b/yql/essentials/tests/sql/suites/insert/other.txt.attr new file mode 100644 index 0000000000..e6092cd69d --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/other.txt.attr @@ -0,0 +1,13 @@ +{ + "_read_schema" = <"strict" = %false>[ + {"type"="string";"name"="key"} + ]; + "schema" = <"strict" = %false; "unique_keys" = %false> + [ + { + "name" = "key"; + "sort_order" = "ascending"; + "type" = "string" + } + ] +} diff --git a/yql/essentials/tests/sql/suites/insert/override-from_sorted.cfg b/yql/essentials/tests/sql/suites/insert/override-from_sorted.cfg new file mode 100644 index 0000000000..66737248b8 --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/override-from_sorted.cfg @@ -0,0 +1,2 @@ +in Input sorted.txt +out Output output.txt diff --git a/yql/essentials/tests/sql/suites/insert/override-from_sorted_calc.cfg b/yql/essentials/tests/sql/suites/insert/override-from_sorted_calc.cfg new file mode 100644 index 0000000000..da3506e2e7 --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/override-from_sorted_calc.cfg @@ -0,0 +1,2 @@ +in Input sorted_calc.txt +out Output output.txt diff --git a/yql/essentials/tests/sql/suites/insert/override-from_sorted_desc.cfg b/yql/essentials/tests/sql/suites/insert/override-from_sorted_desc.cfg new file mode 100644 index 0000000000..803556470a --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/override-from_sorted_desc.cfg @@ -0,0 +1,2 @@ +in Input sorted_desc.txt +out Output output.txt diff --git a/yql/essentials/tests/sql/suites/insert/override-proto.cfg b/yql/essentials/tests/sql/suites/insert/override-proto.cfg new file mode 100644 index 0000000000..499362d95f --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/override-proto.cfg @@ -0,0 +1,3 @@ +in Input input.txt +out Output input_proto.txt +udf protobuf_udf diff --git a/yql/essentials/tests/sql/suites/insert/override-view_fail.cfg b/yql/essentials/tests/sql/suites/insert/override-view_fail.cfg new file mode 100644 index 0000000000..db398a8d2f --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/override-view_fail.cfg @@ -0,0 +1,5 @@ +xfail +in Input input.txt +in Input1 input.txt +in Input2 input2.txt +out Output input_view.txt diff --git a/yql/essentials/tests/sql/suites/insert/override-with_read_udf.cfg b/yql/essentials/tests/sql/suites/insert/override-with_read_udf.cfg new file mode 100644 index 0000000000..8edc95b521 --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/override-with_read_udf.cfg @@ -0,0 +1,3 @@ +in Input input.txt +out Output input_with_read_udf.txt +udf dsv_udf diff --git a/yql/essentials/tests/sql/suites/insert/override-with_view.cfg b/yql/essentials/tests/sql/suites/insert/override-with_view.cfg new file mode 100644 index 0000000000..4feca3f4ed --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/override-with_view.cfg @@ -0,0 +1,4 @@ +in Input input.txt +out Output input_with_view.txt +udf python2_udf +providers yt diff --git a/yql/essentials/tests/sql/suites/insert/override.cfg b/yql/essentials/tests/sql/suites/insert/override.cfg new file mode 100644 index 0000000000..ad52c79527 --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/override.cfg @@ -0,0 +1,2 @@ +in Input input.txt +out Output output.txt diff --git a/yql/essentials/tests/sql/suites/insert/override.sql b/yql/essentials/tests/sql/suites/insert/override.sql new file mode 100644 index 0000000000..9c295718ba --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/override.sql @@ -0,0 +1,4 @@ +/* postgres can not */ +insert into plato.Output with truncate +select * from plato.Input; + diff --git a/yql/essentials/tests/sql/suites/insert/part_sortness-desc.cfg b/yql/essentials/tests/sql/suites/insert/part_sortness-desc.cfg new file mode 100644 index 0000000000..803556470a --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/part_sortness-desc.cfg @@ -0,0 +1,2 @@ +in Input sorted_desc.txt +out Output output.txt diff --git a/yql/essentials/tests/sql/suites/insert/part_sortness.cfg b/yql/essentials/tests/sql/suites/insert/part_sortness.cfg new file mode 100644 index 0000000000..66737248b8 --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/part_sortness.cfg @@ -0,0 +1,2 @@ +in Input sorted.txt +out Output output.txt diff --git a/yql/essentials/tests/sql/suites/insert/part_sortness.sql b/yql/essentials/tests/sql/suites/insert/part_sortness.sql new file mode 100644 index 0000000000..291ae5ae7e --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/part_sortness.sql @@ -0,0 +1,5 @@ +/* postgres can not */ +insert into plato.Output with truncate +SELECT + key,value +FROM plato.Input;
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/insert/read_schema.txt b/yql/essentials/tests/sql/suites/insert/read_schema.txt new file mode 100644 index 0000000000..851703e9a0 --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/read_schema.txt @@ -0,0 +1,5 @@ +{"a"=1;"b"=1u;"c"=0.1;"d"="x";"e"=%false;"f"={"a"="b";"c"="d";"e"=[0;1;2]}}; +{"a"=2;"b"=2u;"c"=2.5;"d"="xx";"e"=%true;"f"=0.123}; +{"a"=-5;"b"=8u;"c"=10.;"d"="xxx";"f"="xyz"}; +{"b"=5u;"d"="skdjfnsdf";"e"=%false}; +{}; diff --git a/yql/essentials/tests/sql/suites/insert/read_schema.txt.attr b/yql/essentials/tests/sql/suites/insert/read_schema.txt.attr new file mode 100644 index 0000000000..3176be619e --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/read_schema.txt.attr @@ -0,0 +1,3 @@ +{ + "_read_schema"=[{"type"="int64";"name"="a"};{"type"="uint64";"name"="b"};{"type"="double";"name"="c"};{"type"="string";"name"="d"};{"type"="boolean";name="e"};{"type"="any";"name"="f"}] +} diff --git a/yql/essentials/tests/sql/suites/insert/replace_inferred.cfg b/yql/essentials/tests/sql/suites/insert/replace_inferred.cfg new file mode 100644 index 0000000000..b5b1813964 --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/replace_inferred.cfg @@ -0,0 +1 @@ +out Output inferschema.txt diff --git a/yql/essentials/tests/sql/suites/insert/replace_inferred.sql b/yql/essentials/tests/sql/suites/insert/replace_inferred.sql new file mode 100644 index 0000000000..ec481c4771 --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/replace_inferred.sql @@ -0,0 +1,9 @@ +/* postgres can not */ +/* kikimr can not */ +use plato; + +pragma yt.InferSchema; +pragma yt.InferSchemaTableCountThreshold="100000"; + +insert into Output with truncate +select * from Output; diff --git a/yql/essentials/tests/sql/suites/insert/replace_inferred_op.cfg b/yql/essentials/tests/sql/suites/insert/replace_inferred_op.cfg new file mode 100644 index 0000000000..b5b1813964 --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/replace_inferred_op.cfg @@ -0,0 +1 @@ +out Output inferschema.txt diff --git a/yql/essentials/tests/sql/suites/insert/replace_inferred_op.sql b/yql/essentials/tests/sql/suites/insert/replace_inferred_op.sql new file mode 100644 index 0000000000..40e1d5e3cd --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/replace_inferred_op.sql @@ -0,0 +1,9 @@ +/* postgres can not */ +/* kikimr can not */ +use plato; + +pragma yt.InferSchema; +pragma yt.InferSchemaTableCountThreshold="0"; + +insert into Output with truncate +select * from Output; diff --git a/yql/essentials/tests/sql/suites/insert/replace_ordered_by_key.sql b/yql/essentials/tests/sql/suites/insert/replace_ordered_by_key.sql new file mode 100644 index 0000000000..02057e2951 --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/replace_ordered_by_key.sql @@ -0,0 +1,3 @@ +/* postgres can not */ +-- kikimr only: pragma kikimr.UnwrapReadTableValues = "false"; create table plato.Output (key varchar null, subkey varchar null, value varchar null, primary key (key)); commit; +insert into plato.Output with truncate (key, subkey, value) select key, subkey, value from plato.Input order by key; diff --git a/yql/essentials/tests/sql/suites/insert/replace_ordered_by_key_desc.sql b/yql/essentials/tests/sql/suites/insert/replace_ordered_by_key_desc.sql new file mode 100644 index 0000000000..46f2024768 --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/replace_ordered_by_key_desc.sql @@ -0,0 +1,3 @@ +/* postgres can not */ +-- kikimr only: pragma kikimr.UnwrapReadTableValues = "false"; create table plato.Output (key varchar null, subkey varchar null, value varchar null, primary key (key)); commit; +insert into plato.Output with truncate (key, subkey, value) select key, subkey, value from plato.Input order by key desc; diff --git a/yql/essentials/tests/sql/suites/insert/select_after_insert_relabeled.sql b/yql/essentials/tests/sql/suites/insert/select_after_insert_relabeled.sql new file mode 100644 index 0000000000..774cea8db9 --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/select_after_insert_relabeled.sql @@ -0,0 +1,6 @@ +/* postgres can not */ +/* multirun can not */ +-- kikimr only: pragma kikimr.UnwrapReadTableValues = "false"; create table plato.Output (key varchar null, subkey varchar null, value varchar null, primary key (key)); commit; +insert into plato.Output (key, subkey, new_value, one_more_value) select key, subkey, value as new_value, "x" from plato.Input; +commit; +select key, subkey, new_value, one_more_value from plato.Output; diff --git a/yql/essentials/tests/sql/suites/insert/select_after_replace.sql b/yql/essentials/tests/sql/suites/insert/select_after_replace.sql new file mode 100644 index 0000000000..6c176e12be --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/select_after_replace.sql @@ -0,0 +1,6 @@ +/* postgres can not */ +/* multirun can not */ +-- kikimr only: pragma kikimr.UnwrapReadTableValues = "false"; create table plato.Output (key varchar null, subkey varchar null, value varchar null, primary key (key)); commit; +insert into plato.Output with truncate (key, subkey, value) select key, subkey, value from plato.Input; +commit; +select key, subkey, value from plato.Output; diff --git a/yql/essentials/tests/sql/suites/insert/select_after_replace_unwrap.sql b/yql/essentials/tests/sql/suites/insert/select_after_replace_unwrap.sql new file mode 100644 index 0000000000..d16e715ff9 --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/select_after_replace_unwrap.sql @@ -0,0 +1,6 @@ +/* postgres can not */ +/* multirun can not */ +-- kikimr only: pragma kikimr.UnwrapReadTableValues = "false"; create table plato.Output (key varchar null, subkey varchar null, value varchar null, primary key (key)); commit; +insert into plato.Output with truncate (key, subkey, value) select key, subkey, value from plato.Input; +commit; +select coalesce(key, ""), coalesce(subkey, ""), coalesce(value, "") from plato.Output; diff --git a/yql/essentials/tests/sql/suites/insert/select_operate_with_columns.cfg b/yql/essentials/tests/sql/suites/insert/select_operate_with_columns.cfg new file mode 100644 index 0000000000..9421c82dab --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/select_operate_with_columns.cfg @@ -0,0 +1,3 @@ +in Input read_schema.txt +out Output output.txt + diff --git a/yql/essentials/tests/sql/suites/insert/select_operate_with_columns.sql b/yql/essentials/tests/sql/suites/insert/select_operate_with_columns.sql new file mode 100644 index 0000000000..12fe8035a2 --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/select_operate_with_columns.sql @@ -0,0 +1,4 @@ +/* postgres can not */ +insert into plato.Output with truncate +select a + b + c as a, coalesce(d, "") as b, f as f, cast(coalesce(e, true) as varchar) as e from plato.Input; + diff --git a/yql/essentials/tests/sql/suites/insert/select_relabel.sql b/yql/essentials/tests/sql/suites/insert/select_relabel.sql new file mode 100644 index 0000000000..71b5507666 --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/select_relabel.sql @@ -0,0 +1,3 @@ +/* postgres can not */ +-- kikimr only: pragma kikimr.UnwrapReadTableValues = "false"; create table plato.Output (key varchar null, subkey varchar null, value varchar null, primary key (key)); commit; +insert into plato.Output with truncate (key, subkey, value) select key, subkey, "1" as value from plato.Input; diff --git a/yql/essentials/tests/sql/suites/insert/select_subquery.cfg b/yql/essentials/tests/sql/suites/insert/select_subquery.cfg new file mode 100644 index 0000000000..ad52c79527 --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/select_subquery.cfg @@ -0,0 +1,2 @@ +in Input input.txt +out Output output.txt diff --git a/yql/essentials/tests/sql/suites/insert/select_subquery.sql b/yql/essentials/tests/sql/suites/insert/select_subquery.sql new file mode 100644 index 0000000000..08015031cc --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/select_subquery.sql @@ -0,0 +1,6 @@ +/* postgres can not */ +use plato; + +$a = (select key from Input order by key limit 1); + +insert into Output (key) select $a; diff --git a/yql/essentials/tests/sql/suites/insert/select_with_sort_limit.sql b/yql/essentials/tests/sql/suites/insert/select_with_sort_limit.sql new file mode 100644 index 0000000000..97a8f11be7 --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/select_with_sort_limit.sql @@ -0,0 +1,3 @@ +-- kikimr only: pragma kikimr.UnwrapReadTableValues = "false"; create table plato.Output (key varchar null, subkey varchar null, value varchar null, primary key (key)); commit; +insert into plato.Output +select * from plato.Input order by key limit 2; diff --git a/yql/essentials/tests/sql/suites/insert/sorted.txt b/yql/essentials/tests/sql/suites/insert/sorted.txt new file mode 100644 index 0000000000..2a8e728cae --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/sorted.txt @@ -0,0 +1,4 @@ +{"key"="023";"subkey"="3";"value"="aaa"}; +{"key"="037";"subkey"="5";"value"="ddd"}; +{"key"="075";"subkey"="1";"value"="abc"}; +{"key"="150";"subkey"="1";"value"="aaa"}; diff --git a/yql/essentials/tests/sql/suites/insert/sorted.txt.attr b/yql/essentials/tests/sql/suites/insert/sorted.txt.attr new file mode 100644 index 0000000000..ed13e20223 --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/sorted.txt.attr @@ -0,0 +1,11 @@ +{"_yql_row_spec"={ + "Type"=["StructType";[ + ["key";["DataType";"String"]]; + ["subkey";["DataType";"String"]]; + ["value";["DataType";"String"]] + ]]; + "SortDirections"=[1;1;]; + "SortedBy"=["key";"subkey";]; + "SortedByTypes"=[["DataType";"String";];["DataType";"String";];]; + "SortMembers"=["key";"subkey";]; +}} diff --git a/yql/essentials/tests/sql/suites/insert/sorted_calc.txt b/yql/essentials/tests/sql/suites/insert/sorted_calc.txt new file mode 100644 index 0000000000..1e60677ebb --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/sorted_calc.txt @@ -0,0 +1,4 @@ +{"_yql_column_0"="0233";"key"="023";"subkey"="3";"value"="aaa"}; +{"_yql_column_0"="0375";"key"="037";"subkey"="5";"value"="ddd"}; +{"_yql_column_0"="0751";"key"="075";"subkey"="1";"value"="abc"}; +{"_yql_column_0"="1501";"key"="150";"subkey"="1";"value"="aaa"}; diff --git a/yql/essentials/tests/sql/suites/insert/sorted_calc.txt.attr b/yql/essentials/tests/sql/suites/insert/sorted_calc.txt.attr new file mode 100644 index 0000000000..60103163df --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/sorted_calc.txt.attr @@ -0,0 +1,44 @@ +{ + "_yql_row_spec" = { + "SortDirections" = [ + 1 + ]; + "UniqueKeys" = %false; + "SortedByTypes" = [ + [ + "DataType"; + "String" + ] + ]; + "StrictSchema" = %true; + "Type" = [ + "StructType"; + [ + [ + "key"; + [ + "DataType"; + "String" + ] + ]; + [ + "subkey"; + [ + "DataType"; + "String" + ] + ]; + [ + "value"; + [ + "DataType"; + "String" + ] + ] + ] + ]; + "SortedBy" = [ + "_yql_column_0" + ] + } +}
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/insert/sorted_desc.txt b/yql/essentials/tests/sql/suites/insert/sorted_desc.txt new file mode 100644 index 0000000000..235fc7cd49 --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/sorted_desc.txt @@ -0,0 +1,10 @@ +{"_yql_column_0"="\xE0\xC6\xCE\xCE\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFC";"key"="911";"subkey"="2";"value"="kkk"}; +{"_yql_column_0"="\xE0\xC8\xC9\xCE\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFC";"key"="761";"subkey"="6";"value"="ccc"}; +{"_yql_column_0"="\xE0\xCA\xCD\xC8\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFC";"key"="527";"subkey"="4";"value"="bbb"}; +{"_yql_column_0"="\xE0\xCD\xCF\xCF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFC";"key"="200";"subkey"="7";"value"="qqq"}; +{"_yql_column_0"="\xE0\xCE\xCA\xCF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFC";"key"="150";"subkey"="1";"value"="aaa"}; +{"_yql_column_0"="\xE0\xCE\xCA\xCF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFC";"key"="150";"subkey"="3";"value"="iii"}; +{"_yql_column_0"="\xE0\xCE\xCA\xCF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFC";"key"="150";"subkey"="8";"value"="zzz"}; +{"_yql_column_0"="\xE0\xCF\xC8\xCA\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFC";"key"="075";"subkey"="1";"value"="abc"}; +{"_yql_column_0"="\xE0\xCF\xCC\xC8\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFC";"key"="037";"subkey"="5";"value"="ddd"}; +{"_yql_column_0"="\xE0\xCF\xCD\xCC\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFC";"key"="023";"subkey"="3";"value"="aaa"}; diff --git a/yql/essentials/tests/sql/suites/insert/sorted_desc.txt.attr b/yql/essentials/tests/sql/suites/insert/sorted_desc.txt.attr new file mode 100644 index 0000000000..5a6ca5761e --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/sorted_desc.txt.attr @@ -0,0 +1,47 @@ +{ + "_yql_row_spec" = { + "SortMembers" = [ + "key" + ]; + "SortDirections" = [ + 0 + ]; + "UniqueKeys" = %false; + "SortedByTypes" = [ + [ + "DataType"; + "String" + ] + ]; + "StrictSchema" = %true; + "Type" = [ + "StructType"; + [ + [ + "key"; + [ + "DataType"; + "String" + ] + ]; + [ + "subkey"; + [ + "DataType"; + "String" + ] + ]; + [ + "value"; + [ + "DataType"; + "String" + ] + ] + ] + ]; + "SortedBy" = [ + "_yql_column_0" + ] + } +}
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/insert/sorted_empty.txt b/yql/essentials/tests/sql/suites/insert/sorted_empty.txt new file mode 100644 index 0000000000..e69de29bb2 --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/sorted_empty.txt diff --git a/yql/essentials/tests/sql/suites/insert/sorted_empty.txt.attr b/yql/essentials/tests/sql/suites/insert/sorted_empty.txt.attr new file mode 100644 index 0000000000..ed13e20223 --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/sorted_empty.txt.attr @@ -0,0 +1,11 @@ +{"_yql_row_spec"={ + "Type"=["StructType";[ + ["key";["DataType";"String"]]; + ["subkey";["DataType";"String"]]; + ["value";["DataType";"String"]] + ]]; + "SortDirections"=[1;1;]; + "SortedBy"=["key";"subkey";]; + "SortedByTypes"=[["DataType";"String";];["DataType";"String";];]; + "SortMembers"=["key";"subkey";]; +}} diff --git a/yql/essentials/tests/sql/suites/insert/sorted_unique.txt b/yql/essentials/tests/sql/suites/insert/sorted_unique.txt new file mode 100644 index 0000000000..2a8e728cae --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/sorted_unique.txt @@ -0,0 +1,4 @@ +{"key"="023";"subkey"="3";"value"="aaa"}; +{"key"="037";"subkey"="5";"value"="ddd"}; +{"key"="075";"subkey"="1";"value"="abc"}; +{"key"="150";"subkey"="1";"value"="aaa"}; diff --git a/yql/essentials/tests/sql/suites/insert/sorted_unique.txt.attr b/yql/essentials/tests/sql/suites/insert/sorted_unique.txt.attr new file mode 100644 index 0000000000..eeee4c32ca --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/sorted_unique.txt.attr @@ -0,0 +1,21 @@ +{ + "schema"=< + "strict" = %true; + "unique_keys" = %true + >[ + { + "name" = "key"; + "type" = "string"; + "sort_order" = "ascending"; + }; + { + "name" = "subkey"; + "type" = "string"; + "sort_order" = "ascending"; + }; + { + "name" = "value"; + "type" = "string"; + }; + ] +}
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/insert/trivial_literals.sql b/yql/essentials/tests/sql/suites/insert/trivial_literals.sql new file mode 100644 index 0000000000..5d0a0e557a --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/trivial_literals.sql @@ -0,0 +1,2 @@ +-- kikimr only: pragma kikimr.UnwrapReadTableValues = "false"; create table plato.Output (key varchar null, subkey varchar null, value varchar null, primary key (key)); commit; +insert into plato.Output (key, subkey, value) values ("1", "2", "3"); diff --git a/yql/essentials/tests/sql/suites/insert/trivial_literals_multirow.sql b/yql/essentials/tests/sql/suites/insert/trivial_literals_multirow.sql new file mode 100644 index 0000000000..8ea32ffc35 --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/trivial_literals_multirow.sql @@ -0,0 +1,4 @@ +-- kikimr only: pragma kikimr.UnwrapReadTableValues = "false"; create table plato.Output (key varchar null, subkey varchar null, value varchar null, primary key (key)); commit; +/* postgres can not */ +insert into plato.Output (key, subkey, value) +values ("1", "2", "3"), ("4", "5", "6"), ("7", "8", "9"); diff --git a/yql/essentials/tests/sql/suites/insert/trivial_select.sql b/yql/essentials/tests/sql/suites/insert/trivial_select.sql new file mode 100644 index 0000000000..26b1075133 --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/trivial_select.sql @@ -0,0 +1,2 @@ +-- kikimr only: pragma kikimr.UnwrapReadTableValues = "false"; create table plato.Output (key varchar null, subkey varchar null, value varchar null, primary key (key)); commit; +insert into plato.Output (key, subkey, value) select key, subkey, value from plato.Input; diff --git a/yql/essentials/tests/sql/suites/insert/two_input_tables.cfg b/yql/essentials/tests/sql/suites/insert/two_input_tables.cfg new file mode 100644 index 0000000000..369f783dcd --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/two_input_tables.cfg @@ -0,0 +1,4 @@ +in Input input.txt +in Input2 input2.txt +out Output output.txt + diff --git a/yql/essentials/tests/sql/suites/insert/two_input_tables.sql b/yql/essentials/tests/sql/suites/insert/two_input_tables.sql new file mode 100644 index 0000000000..9a02779e6d --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/two_input_tables.sql @@ -0,0 +1,3 @@ +-- kikimr only: pragma kikimr.UnwrapReadTableValues = "false"; create table plato.Output (key varchar null, subkey varchar null, value varchar null, primary key (key)); commit; +insert into plato.Output (key, subkey, value) select key, subkey, value from plato.Input; +insert into plato.Output (key, subkey, value) select key, subkey, value from plato.Input2; diff --git a/yql/essentials/tests/sql/suites/insert/udf_empty.cfg b/yql/essentials/tests/sql/suites/insert/udf_empty.cfg new file mode 100644 index 0000000000..57571ef968 --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/udf_empty.cfg @@ -0,0 +1,3 @@ +in Input input.txt +out Output output.txt +udf simple_udf diff --git a/yql/essentials/tests/sql/suites/insert/udf_empty.sql b/yql/essentials/tests/sql/suites/insert/udf_empty.sql new file mode 100644 index 0000000000..65823f12cd --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/udf_empty.sql @@ -0,0 +1,3 @@ +/* postgres can not */ +-- kikimr only: pragma kikimr.UnwrapReadTableValues = "false"; create table plato.Output (key varchar null, subkey varchar null, value varchar null, primary key (key)); commit; +insert into plato.Output(key, subkey, value) values (SimpleUdf::ReturnNull(""), SimpleUdf::ReturnVoid(""), coalesce(SimpleUdf::ReturnEmpty(""), "!")); diff --git a/yql/essentials/tests/sql/suites/insert/unique_distinct_hints.cfg b/yql/essentials/tests/sql/suites/insert/unique_distinct_hints.cfg new file mode 100644 index 0000000000..eddd32c5cb --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/unique_distinct_hints.cfg @@ -0,0 +1,10 @@ +in Input input.txt +out Output1 output1.txt +out Output2 output2.txt +out Output3 output3.txt +out Output4 output4.txt +out Output5 output5.txt +out Output6 output6.txt +out Output7 output7.txt +out Output8 output8.txt + diff --git a/yql/essentials/tests/sql/suites/insert/unique_distinct_hints.sql b/yql/essentials/tests/sql/suites/insert/unique_distinct_hints.sql new file mode 100644 index 0000000000..9029434ebc --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/unique_distinct_hints.sql @@ -0,0 +1,15 @@ +/* postgres can not */ +USE plato; + +INSERT INTO Output1 WITH TRUNCATE SELECT /*+ unique() */ * FROM Input; +INSERT INTO Output2 WITH TRUNCATE SELECT /*+ distinct() */ * FROM Input; + +INSERT INTO Output3 WITH TRUNCATE SELECT /*+ distinct(key subkey) unique(value) */ * FROM Input; +INSERT INTO Output4 WITH TRUNCATE SELECT /*+ unique(key) distinct(subkey value) */ * FROM Input; +INSERT INTO Output5 WITH TRUNCATE SELECT /*+ unique(key value) unique(subkey) */ * FROM Input; +INSERT INTO Output6 WITH TRUNCATE SELECT /*+ distinct(key) distinct(subkey) */ * FROM Input; + +-- Bad case: missed column - ignore hint with warning. +INSERT INTO Output7 WITH TRUNCATE SELECT /*+ unique(subkey value) */ key, value FROM Input; + +INSERT INTO Output8 WITH TRUNCATE SELECT /*+ distinct(key subkey) */ key, subkey FROM Input ORDER BY key, subkey; diff --git a/yql/essentials/tests/sql/suites/insert/use_anon_table_before_commit_fail.cfg b/yql/essentials/tests/sql/suites/insert/use_anon_table_before_commit_fail.cfg new file mode 100644 index 0000000000..4f32e1efb8 --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/use_anon_table_before_commit_fail.cfg @@ -0,0 +1,2 @@ +xfail +in Input input.txt diff --git a/yql/essentials/tests/sql/suites/insert/use_anon_table_before_commit_fail.sql b/yql/essentials/tests/sql/suites/insert/use_anon_table_before_commit_fail.sql new file mode 100644 index 0000000000..acf1795e35 --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/use_anon_table_before_commit_fail.sql @@ -0,0 +1,8 @@ +/* postgres can not */ +use plato; + +insert into @a +select * from Input; + +select * from @a; + diff --git a/yql/essentials/tests/sql/suites/insert/use_anon_table_without_fill_fail.cfg b/yql/essentials/tests/sql/suites/insert/use_anon_table_without_fill_fail.cfg new file mode 100644 index 0000000000..4f32e1efb8 --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/use_anon_table_without_fill_fail.cfg @@ -0,0 +1,2 @@ +xfail +in Input input.txt diff --git a/yql/essentials/tests/sql/suites/insert/use_anon_table_without_fill_fail.sql b/yql/essentials/tests/sql/suites/insert/use_anon_table_without_fill_fail.sql new file mode 100644 index 0000000000..5d78422272 --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/use_anon_table_without_fill_fail.sql @@ -0,0 +1,5 @@ +/* postgres can not */ +use plato; + +select * from @a; + diff --git a/yql/essentials/tests/sql/suites/insert/values_subquery.cfg b/yql/essentials/tests/sql/suites/insert/values_subquery.cfg new file mode 100644 index 0000000000..38b2bc62d7 --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/values_subquery.cfg @@ -0,0 +1,3 @@ +in Input input.txt +out Output output.txt +providers yt
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/insert/values_subquery.sql b/yql/essentials/tests/sql/suites/insert/values_subquery.sql new file mode 100644 index 0000000000..a409e91d8e --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/values_subquery.sql @@ -0,0 +1,6 @@ +/* postgres can not */ +use plato; + +$a = (select key from Input order by key limit 1); + +insert into Output (key) values ($a); diff --git a/yql/essentials/tests/sql/suites/insert/yql-13083-existig.cfg b/yql/essentials/tests/sql/suites/insert/yql-13083-existig.cfg new file mode 100644 index 0000000000..97aa89b9bd --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/yql-13083-existig.cfg @@ -0,0 +1,3 @@ +in Input input.txt +out Output1 sorted.txt +out Output2 sorted.txt
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/insert/yql-13083.cfg b/yql/essentials/tests/sql/suites/insert/yql-13083.cfg new file mode 100644 index 0000000000..09952ac4de --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/yql-13083.cfg @@ -0,0 +1,3 @@ +in Input input.txt +out Output1 output.txt +out Output2 output.txt
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/insert/yql-13083.sql b/yql/essentials/tests/sql/suites/insert/yql-13083.sql new file mode 100644 index 0000000000..92c8c6ec79 --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/yql-13083.sql @@ -0,0 +1,32 @@ +/* postgres can not */ +use plato; + +INSERT INTO Output1 +SELECT + key as key, + "" as subkey, + "value:" || value as value +FROM Input +WHERE key < "100" +ORDER BY key; + +INSERT INTO Output2 +SELECT + key as key, + "" as subkey, + "value:" || value as value +FROM Input +WHERE key < "200" +ORDER BY key; + +INSERT INTO Output1 +SELECT + * +FROM Input +ORDER BY key; + +INSERT INTO Output2 +SELECT + * +FROM Input +ORDER BY key;
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/insert/yql-14538.cfg b/yql/essentials/tests/sql/suites/insert/yql-14538.cfg new file mode 100644 index 0000000000..ad52c79527 --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/yql-14538.cfg @@ -0,0 +1,2 @@ +in Input input.txt +out Output output.txt diff --git a/yql/essentials/tests/sql/suites/insert/yql-14538.sql b/yql/essentials/tests/sql/suites/insert/yql-14538.sql new file mode 100644 index 0000000000..73c2c6267a --- /dev/null +++ b/yql/essentials/tests/sql/suites/insert/yql-14538.sql @@ -0,0 +1,18 @@ +use plato; + +INSERT INTO Output +SELECT + key as key, + "" as subkey, + "value:" || value as value +FROM Input +WHERE key < "100" +ORDER BY key +LIMIT 5; + +INSERT INTO Output +SELECT + * +FROM Input +ORDER BY key +LIMIT 4; |