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/weak_field | |
| 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/weak_field')
81 files changed, 570 insertions, 0 deletions
diff --git a/yql/essentials/tests/sql/suites/weak_field/default.cfg b/yql/essentials/tests/sql/suites/weak_field/default.cfg new file mode 100644 index 0000000000..58878f8945 --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/default.cfg @@ -0,0 +1 @@ +in Input input.txt diff --git a/yql/essentials/tests/sql/suites/weak_field/few_source_different_columns.cfg b/yql/essentials/tests/sql/suites/weak_field/few_source_different_columns.cfg new file mode 100644 index 0000000000..84859da06f --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/few_source_different_columns.cfg @@ -0,0 +1,2 @@ +in Input1 infer_scheme.txt +in Input2 input_other_yson_esc.txt diff --git a/yql/essentials/tests/sql/suites/weak_field/few_source_different_columns.sql b/yql/essentials/tests/sql/suites/weak_field/few_source_different_columns.sql new file mode 100644 index 0000000000..321ffdf307 --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/few_source_different_columns.sql @@ -0,0 +1,12 @@ +/* postgres can not */ +/* syntax version 1 */ +PRAGMA yt.InferSchema="2"; + +USE plato; + +SELECT + WeakField(key, "String", "funny"), + WeakField(subkey, "String", "bunny"), + WeakField(value, "String", "boom") +FROM RANGE(``, ``, ``) +--FROM Concat(Input1, Input2) diff --git a/yql/essentials/tests/sql/suites/weak_field/hor_join_with_mix_weak_access.cfg b/yql/essentials/tests/sql/suites/weak_field/hor_join_with_mix_weak_access.cfg new file mode 100644 index 0000000000..400d2341a2 --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/hor_join_with_mix_weak_access.cfg @@ -0,0 +1 @@ +in Input other1.txt
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/weak_field/hor_join_with_mix_weak_access.sql b/yql/essentials/tests/sql/suites/weak_field/hor_join_with_mix_weak_access.sql new file mode 100644 index 0000000000..74aad3d484 --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/hor_join_with_mix_weak_access.sql @@ -0,0 +1,7 @@ +/* postgres can not */ +/* syntax version 1 */ +use plato; + +select key, WeakField(value1, "String", "funny") as value from Input +union all +select key, _other["value1"] as value from Input; diff --git a/yql/essentials/tests/sql/suites/weak_field/infer_scheme.txt b/yql/essentials/tests/sql/suites/weak_field/infer_scheme.txt new file mode 100644 index 0000000000..ba7745c045 --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/infer_scheme.txt @@ -0,0 +1,4 @@ +{"key"="075";"subkey"="1"}; +{"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/weak_field/infer_scheme.txt.attr b/yql/essentials/tests/sql/suites/weak_field/infer_scheme.txt.attr new file mode 100644 index 0000000000..3c0f7e55c1 --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/infer_scheme.txt.attr @@ -0,0 +1,3 @@ +{ + "infer_schema" = %true +}
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/weak_field/input.txt b/yql/essentials/tests/sql/suites/weak_field/input.txt new file mode 100644 index 0000000000..65949ea745 --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/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/weak_field/input_other_yson.txt b/yql/essentials/tests/sql/suites/weak_field/input_other_yson.txt new file mode 100644 index 0000000000..e447383632 --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/input_other_yson.txt @@ -0,0 +1,5 @@ +{"key"="020";"subkey"="1";"binZ"="\x04" ;"strY"="\1\x08test";"realZ"="\x03\x18-DT\xfb!\t@" ;"uiData"=1u}; +{"key"="075";"subkey"="5";"binZ"="%true" ;"strY"="\1\nfunny" ;"realZ"="\x03iW\x14\x8b\n\xbf\x05@" ;"uiData"=1u}; +{"key"="150";"subkey"="4";"binZ"="\x05" ;"strY"="\1\nbunny" ;"realZ"="\x03\xcd;\x7ff\x9e\xa0\xf6?" ;"uiData"=100500u}; +{"key"="500";"subkey"="2";"binZ"="%false";"strY"="33.33" ;"realZ"="\x03\x00\x00\x00\x00\x00\x00\xf0?" ;"uiData"=#}; +{"key"="800";"subkey"="3";"binZ"="%false";"strY"="\1\x06zzz" ;"realZ"="\x03\x00\x00\x00\x00\x00\x00\xf0\xbf";"uiData"=33u}; diff --git a/yql/essentials/tests/sql/suites/weak_field/input_other_yson.txt.attr b/yql/essentials/tests/sql/suites/weak_field/input_other_yson.txt.attr new file mode 100644 index 0000000000..49d98771a8 --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/input_other_yson.txt.attr @@ -0,0 +1,17 @@ +{ + "_read_schema" = <"strict" = %false>[ + {"type"="string";"name"="key"}; + {"type"="string";"name"="subkey"}; + ]; + "schema" = <"strict" = %false; "unique_keys" = %false> + [ + { + "name" = "key"; + "type" = "string" + }; + { + "name" = "subkey"; + "type" = "string" + } + ] +} diff --git a/yql/essentials/tests/sql/suites/weak_field/input_other_yson_esc.txt b/yql/essentials/tests/sql/suites/weak_field/input_other_yson_esc.txt new file mode 100644 index 0000000000..329f198957 --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/input_other_yson_esc.txt @@ -0,0 +1,7 @@ +{"key"="020";"subkey"="1";"strE1"="hello\\world" ;"value"="find"}; +{"key"="075";"subkey"="5";"strE1"="\nfunny\n" }; +{"key"="150";"subkey"="4";"strE1"="\nbunny\n" ;"value"="the"}; +{"key"="333";"subkey"="9";"strE1"=# }; +{"key"="500";"subkey"="2";"strE1"="33.33" ;"value"="black"}; +{"key"="400";"subkey"="13";"strE1"="test\"funny\"esc"}; +{"key"="800";"subkey"="3";"strE1"="test\\esc" ;"value"="rabbit"}; diff --git a/yql/essentials/tests/sql/suites/weak_field/input_other_yson_esc.txt.attr b/yql/essentials/tests/sql/suites/weak_field/input_other_yson_esc.txt.attr new file mode 100644 index 0000000000..49d98771a8 --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/input_other_yson_esc.txt.attr @@ -0,0 +1,17 @@ +{ + "_read_schema" = <"strict" = %false>[ + {"type"="string";"name"="key"}; + {"type"="string";"name"="subkey"}; + ]; + "schema" = <"strict" = %false; "unique_keys" = %false> + [ + { + "name" = "key"; + "type" = "string" + }; + { + "name" = "subkey"; + "type" = "string" + } + ] +} diff --git a/yql/essentials/tests/sql/suites/weak_field/input_rest.txt b/yql/essentials/tests/sql/suites/weak_field/input_rest.txt new file mode 100644 index 0000000000..e5828cef9e --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/input_rest.txt @@ -0,0 +1,5 @@ +{"key"="020";"subkey"="1";"_rest"={"animal"="wombat";"size"="small";"weightMin"=20.;"weightMax"=35.;"wild"=%true};}; +{"key"="075";"subkey"="5";"_rest"={"animal"="dog";"size"="huge";"weightMin"=5.;"weightMax"=75.;"pet"=%true};}; +{"key"="150";"subkey"="4";"_rest"={"animal"="chipmunk";"size"="small";"weightMin"=0.05;"weightMax"=0.15;"wild"=%true};}; +{"key"="500";"subkey"="2";"_rest"={"animal"="hamster";"size"="verysmall";"weightMin"=0.015;"weightMax"=0.045;"pet"=%true};}; +{"key"="800";"subkey"="3";"_rest"={"animal"="dingo";"size"="huge";"weightMin"=10.;"weightMax"=20.;"wild"=%true};}; diff --git a/yql/essentials/tests/sql/suites/weak_field/input_rest.txt.attr b/yql/essentials/tests/sql/suites/weak_field/input_rest.txt.attr new file mode 100644 index 0000000000..68b8ac17ed --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/input_rest.txt.attr @@ -0,0 +1,11 @@ +{"_yql_row_spec"={ + "Type"=["StructType";[ + ["key";["DataType";"String"]]; + ["subkey";["DataType";"String"]]; + ["_rest";["DictType";["DataType";"String"];["DataType";"Yson"]]] + ]]; + "SortDirections"=[1;1;]; + "SortedBy"=["key";"subkey";]; + "SortedByTypes"=[["DataType";"String";];["DataType";"String";];]; + "SortMembers"=["key";"subkey";]; +}} diff --git a/yql/essentials/tests/sql/suites/weak_field/input_tutorial_email_short_as_other.txt b/yql/essentials/tests/sql/suites/weak_field/input_tutorial_email_short_as_other.txt new file mode 100644 index 0000000000..2faec7887d --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/input_tutorial_email_short_as_other.txt @@ -0,0 +1,5 @@ +{"email"="test@email.com";"yandexuid"=999999999999999999u;}; +{"email"="test00000@email.com";"yandexuid"=999999999999999999u;}; +{"email"="test00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000@email.com";"yandexuid"=999999999999999999u;}; +{"email"="test0000000000000@email.com";"yandexuid"=999999999999999999u;}; +{"email"="test0@email.com";"yandexuid"=999999999999999999u;}; diff --git a/yql/essentials/tests/sql/suites/weak_field/input_tutorial_email_short_as_other.txt.attr b/yql/essentials/tests/sql/suites/weak_field/input_tutorial_email_short_as_other.txt.attr new file mode 100644 index 0000000000..8f81910ca5 --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/input_tutorial_email_short_as_other.txt.attr @@ -0,0 +1,12 @@ +{ + "_read_schema" = <"strict" = %false>[ + {"type"="uint64";"name"="yandexuid"}; + ]; + "schema" = <"strict" = %false; "unique_keys" = %false> + [ + { + "name" = "yandexuid"; + "type" = "uint64" + } + ] +} diff --git a/yql/essentials/tests/sql/suites/weak_field/optimize_weak_fields.txt b/yql/essentials/tests/sql/suites/weak_field/optimize_weak_fields.txt new file mode 100644 index 0000000000..cd303b9b31 --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/optimize_weak_fields.txt @@ -0,0 +1,3 @@ +{"key"="023"}; +{"key"="037";"subkey"=5;"value"="ddd"}; +{"key"="075";"subkey"=1;"value"="abc"}; diff --git a/yql/essentials/tests/sql/suites/weak_field/optimize_weak_fields.txt.attr b/yql/essentials/tests/sql/suites/weak_field/optimize_weak_fields.txt.attr new file mode 100644 index 0000000000..ada1f836f8 --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/optimize_weak_fields.txt.attr @@ -0,0 +1,3 @@ +{ + "infer_schema"=%true +} diff --git a/yql/essentials/tests/sql/suites/weak_field/optimize_weak_fields_combine.cfg b/yql/essentials/tests/sql/suites/weak_field/optimize_weak_fields_combine.cfg new file mode 100644 index 0000000000..548d8860d2 --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/optimize_weak_fields_combine.cfg @@ -0,0 +1 @@ +in Input optimize_weak_fields.txt diff --git a/yql/essentials/tests/sql/suites/weak_field/optimize_weak_fields_combine.sql b/yql/essentials/tests/sql/suites/weak_field/optimize_weak_fields_combine.sql new file mode 100644 index 0000000000..7050c55069 --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/optimize_weak_fields_combine.sql @@ -0,0 +1,4 @@ +/* kikimr can not */ +pragma yt.InferSchema; +USE plato; +SELECT key, min(WeakField(subkey, "Int64")), max(WeakField(value, "String")) FROM Input GROUP BY key ORDER BY key; diff --git a/yql/essentials/tests/sql/suites/weak_field/optimize_weak_fields_filter_combine.cfg b/yql/essentials/tests/sql/suites/weak_field/optimize_weak_fields_filter_combine.cfg new file mode 100644 index 0000000000..548d8860d2 --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/optimize_weak_fields_filter_combine.cfg @@ -0,0 +1 @@ +in Input optimize_weak_fields.txt diff --git a/yql/essentials/tests/sql/suites/weak_field/optimize_weak_fields_filter_combine.sql b/yql/essentials/tests/sql/suites/weak_field/optimize_weak_fields_filter_combine.sql new file mode 100644 index 0000000000..f981ba133b --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/optimize_weak_fields_filter_combine.sql @@ -0,0 +1,4 @@ +/* kikimr can not */ +pragma yt.InferSchema; +USE plato; +SELECT min(key),subkey,max(WeakField(value, "String")) FROM Input where WeakField(subkey, "Int64") > 0 group by WeakField(subkey, "Int64") as subkey order by subkey; diff --git a/yql/essentials/tests/sql/suites/weak_field/optimize_weak_fields_map.cfg b/yql/essentials/tests/sql/suites/weak_field/optimize_weak_fields_map.cfg new file mode 100644 index 0000000000..548d8860d2 --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/optimize_weak_fields_map.cfg @@ -0,0 +1 @@ +in Input optimize_weak_fields.txt diff --git a/yql/essentials/tests/sql/suites/weak_field/optimize_weak_fields_map.sql b/yql/essentials/tests/sql/suites/weak_field/optimize_weak_fields_map.sql new file mode 100644 index 0000000000..6cbe12e979 --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/optimize_weak_fields_map.sql @@ -0,0 +1,4 @@ +/* kikimr can not */ +pragma yt.InferSchema; +USE plato; +SELECT key, WeakField(subkey, "Int64"), WeakField(value, "String") FROM Input; diff --git a/yql/essentials/tests/sql/suites/weak_field/optimize_weak_fields_map_combine.cfg b/yql/essentials/tests/sql/suites/weak_field/optimize_weak_fields_map_combine.cfg new file mode 100644 index 0000000000..548d8860d2 --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/optimize_weak_fields_map_combine.cfg @@ -0,0 +1 @@ +in Input optimize_weak_fields.txt diff --git a/yql/essentials/tests/sql/suites/weak_field/optimize_weak_fields_map_combine.sql b/yql/essentials/tests/sql/suites/weak_field/optimize_weak_fields_map_combine.sql new file mode 100644 index 0000000000..ec5fe0a26f --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/optimize_weak_fields_map_combine.sql @@ -0,0 +1,4 @@ +/* kikimr can not */ +pragma yt.InferSchema; +USE plato; +SELECT min(key),subkey,max(WeakField(value, "String")) FROM Input group by WeakField(subkey, "Int64") as subkey order by subkey; diff --git a/yql/essentials/tests/sql/suites/weak_field/optional.txt b/yql/essentials/tests/sql/suites/weak_field/optional.txt new file mode 100644 index 0000000000..74737be93c --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/optional.txt @@ -0,0 +1 @@ +{"first_num"=1u; "first_null"=#; "second_num"=[1u]; "second_null"=[]; "third_num"=[[1u]]; "third_null"=[#]}; diff --git a/yql/essentials/tests/sql/suites/weak_field/optional.txt.attr b/yql/essentials/tests/sql/suites/weak_field/optional.txt.attr new file mode 100644 index 0000000000..7962199d3e --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/optional.txt.attr @@ -0,0 +1,85 @@ +{ + "_yql_row_spec" = {"Type" = [ + "StructType"; + [ + [ + "first_null"; + [ + "OptionalType"; + [ + "DataType"; + "Uint32" + ] + ] + ]; + [ + "first_num"; + [ + "OptionalType"; + [ + "DataType"; + "Uint32" + ] + ] + ]; + [ + "second_null"; + [ + "OptionalType"; + [ + "OptionalType"; + [ + "DataType"; + "Uint32" + ] + ] + ] + ]; + [ + "second_num"; + [ + "OptionalType"; + [ + "OptionalType"; + [ + "DataType"; + "Uint32" + ] + ] + ] + ]; + [ + "third_null"; + [ + "OptionalType"; + [ + "OptionalType"; + [ + "OptionalType"; + [ + "DataType"; + "Uint32" + ] + ] + ] + ] + ]; + [ + "third_num"; + [ + "OptionalType"; + [ + "OptionalType"; + [ + "OptionalType"; + [ + "DataType"; + "Uint32" + ] + ] + ] + ] + ] + ] + ]}; +} diff --git a/yql/essentials/tests/sql/suites/weak_field/other1.txt b/yql/essentials/tests/sql/suites/weak_field/other1.txt new file mode 100644 index 0000000000..42c5393923 --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/other1.txt @@ -0,0 +1,4 @@ +{"key"="020";"subkey"="3";"value1"="q"}; +{"key"="075";"subkey"="1";"value1"="abc"}; +{"key"="150";"subkey"="4";"value1"="qzz"}; +{"key"="800";"subkey"="2";"value1"="ddd"}; diff --git a/yql/essentials/tests/sql/suites/weak_field/other1.txt.attr b/yql/essentials/tests/sql/suites/weak_field/other1.txt.attr new file mode 100644 index 0000000000..49d98771a8 --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/other1.txt.attr @@ -0,0 +1,17 @@ +{ + "_read_schema" = <"strict" = %false>[ + {"type"="string";"name"="key"}; + {"type"="string";"name"="subkey"}; + ]; + "schema" = <"strict" = %false; "unique_keys" = %false> + [ + { + "name" = "key"; + "type" = "string" + }; + { + "name" = "subkey"; + "type" = "string" + } + ] +} diff --git a/yql/essentials/tests/sql/suites/weak_field/other2.txt b/yql/essentials/tests/sql/suites/weak_field/other2.txt new file mode 100644 index 0000000000..1fa10bf270 --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/other2.txt @@ -0,0 +1,4 @@ +{"key"="020";"subkey"="3";"value2"="q"}; +{"key"="075";"subkey"="1";"value2"="abc"}; +{"key"="150";"subkey"="4";"value2"="qzz"}; +{"key"="800";"subkey"="2";"value2"="ddd"}; diff --git a/yql/essentials/tests/sql/suites/weak_field/other2.txt.attr b/yql/essentials/tests/sql/suites/weak_field/other2.txt.attr new file mode 100644 index 0000000000..49d98771a8 --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/other2.txt.attr @@ -0,0 +1,17 @@ +{ + "_read_schema" = <"strict" = %false>[ + {"type"="string";"name"="key"}; + {"type"="string";"name"="subkey"}; + ]; + "schema" = <"strict" = %false; "unique_keys" = %false> + [ + { + "name" = "key"; + "type" = "string" + }; + { + "name" = "subkey"; + "type" = "string" + } + ] +} diff --git a/yql/essentials/tests/sql/suites/weak_field/other3.txt b/yql/essentials/tests/sql/suites/weak_field/other3.txt new file mode 100644 index 0000000000..d5302f1f4a --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/other3.txt @@ -0,0 +1,5 @@ +{"key"="020";"subkey"="1";"data3"=33;datahole3=3}; +{"key"="075";"subkey"="5";"data3"=100500;datahole3=1}; +{"key"="150";"subkey"="4";"data3"=123;datahole3=#}; +{"key"="500";"subkey"="2";"data3"=42;datahole3=2}; +{"key"="800";"subkey"="3";"data3"=321;datahole3=#}; diff --git a/yql/essentials/tests/sql/suites/weak_field/other3.txt.attr b/yql/essentials/tests/sql/suites/weak_field/other3.txt.attr new file mode 100644 index 0000000000..49d98771a8 --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/other3.txt.attr @@ -0,0 +1,17 @@ +{ + "_read_schema" = <"strict" = %false>[ + {"type"="string";"name"="key"}; + {"type"="string";"name"="subkey"}; + ]; + "schema" = <"strict" = %false; "unique_keys" = %false> + [ + { + "name" = "key"; + "type" = "string" + }; + { + "name" = "subkey"; + "type" = "string" + } + ] +} diff --git a/yql/essentials/tests/sql/suites/weak_field/other4.txt b/yql/essentials/tests/sql/suites/weak_field/other4.txt new file mode 100644 index 0000000000..7b4b7c469d --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/other4.txt @@ -0,0 +1,5 @@ +{"key"="020";"subkey"="1";"data3"=33;datahole3=3u}; +{"key"="075";"subkey"="5";"data3"=100500;datahole3=1u}; +{"key"="150";"subkey"="4";"data3"=123;datahole3=#}; +{"key"="500";"subkey"="2";"data3"=42;datahole3=2u}; +{"key"="800";"subkey"="3";"data3"=321;datahole3=#}; diff --git a/yql/essentials/tests/sql/suites/weak_field/other4.txt.attr b/yql/essentials/tests/sql/suites/weak_field/other4.txt.attr new file mode 100644 index 0000000000..49d98771a8 --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/other4.txt.attr @@ -0,0 +1,17 @@ +{ + "_read_schema" = <"strict" = %false>[ + {"type"="string";"name"="key"}; + {"type"="string";"name"="subkey"}; + ]; + "schema" = <"strict" = %false; "unique_keys" = %false> + [ + { + "name" = "key"; + "type" = "string" + }; + { + "name" = "subkey"; + "type" = "string" + } + ] +} diff --git a/yql/essentials/tests/sql/suites/weak_field/weak_field.cfg b/yql/essentials/tests/sql/suites/weak_field/weak_field.cfg new file mode 100644 index 0000000000..1410268275 --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/weak_field.cfg @@ -0,0 +1,2 @@ +in Input1 other1.txt +in Input2 other2.txt diff --git a/yql/essentials/tests/sql/suites/weak_field/weak_field.sql b/yql/essentials/tests/sql/suites/weak_field/weak_field.sql new file mode 100644 index 0000000000..96e155080d --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/weak_field.sql @@ -0,0 +1,9 @@ +/* postgres can not */ +use plato; +select "input1-value1" as src, key, WeakField(value1, "String", "funny") as ozer from Input1 +union all +select "input2-value1" as src, key, WeakField(value1, "String", "funny") as ozer from Input2 +union all +select "input1-value2" as src, key, WeakField(value2, "String") as ozer from Input1 +union all +select "input2-value2" as src, key, WeakField(value2, "String") as ozer from Input2 diff --git a/yql/essentials/tests/sql/suites/weak_field/weak_field_aggregation.cfg b/yql/essentials/tests/sql/suites/weak_field/weak_field_aggregation.cfg new file mode 100644 index 0000000000..b41a784aca --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/weak_field_aggregation.cfg @@ -0,0 +1 @@ +in Input4 other4.txt diff --git a/yql/essentials/tests/sql/suites/weak_field/weak_field_aggregation.sql b/yql/essentials/tests/sql/suites/weak_field/weak_field_aggregation.sql new file mode 100644 index 0000000000..904f01470d --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/weak_field_aggregation.sql @@ -0,0 +1,10 @@ +/* postgres can not */ +USE plato; + +--INSERT INTO Output +SELECT + odd, + sum(WeakField(data3, "int32") + WeakField(datahole3, "uint32", 999)) as score +FROM Input4 +GROUP BY cast(subkey as uint32) % 2 as odd +ORDER BY odd, score diff --git a/yql/essentials/tests/sql/suites/weak_field/weak_field_data.cfg b/yql/essentials/tests/sql/suites/weak_field/weak_field_data.cfg new file mode 100644 index 0000000000..2c8cfa4101 --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/weak_field_data.cfg @@ -0,0 +1 @@ +in Input3 other3.txt diff --git a/yql/essentials/tests/sql/suites/weak_field/weak_field_data.sql b/yql/essentials/tests/sql/suites/weak_field/weak_field_data.sql new file mode 100644 index 0000000000..e3d3002084 --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/weak_field_data.sql @@ -0,0 +1,3 @@ +/* postgres can not */ +use plato; +select subkey, WeakField(data1, "Int32", 32) as d1, WeakField(data3, "Int32", 32) as d3 from Input3 order by subkey diff --git a/yql/essentials/tests/sql/suites/weak_field/weak_field_esc_string.cfg b/yql/essentials/tests/sql/suites/weak_field/weak_field_esc_string.cfg new file mode 100644 index 0000000000..ec92471037 --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/weak_field_esc_string.cfg @@ -0,0 +1 @@ +in Input input_other_yson_esc.txt diff --git a/yql/essentials/tests/sql/suites/weak_field/weak_field_esc_string.sql b/yql/essentials/tests/sql/suites/weak_field/weak_field_esc_string.sql new file mode 100644 index 0000000000..9c394f9ca9 --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/weak_field_esc_string.sql @@ -0,0 +1,7 @@ +/* postgres can not */ +USE plato; + +--INSERT INTO Output +SELECT WeakField(subkey, 'string'), WeakField(strE1, 'string') +FROM Input +ORDER BY subkey; diff --git a/yql/essentials/tests/sql/suites/weak_field/weak_field_esc_yson.cfg b/yql/essentials/tests/sql/suites/weak_field/weak_field_esc_yson.cfg new file mode 100644 index 0000000000..ec92471037 --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/weak_field_esc_yson.cfg @@ -0,0 +1 @@ +in Input input_other_yson_esc.txt diff --git a/yql/essentials/tests/sql/suites/weak_field/weak_field_esc_yson.sql b/yql/essentials/tests/sql/suites/weak_field/weak_field_esc_yson.sql new file mode 100644 index 0000000000..28482d250f --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/weak_field_esc_yson.sql @@ -0,0 +1,10 @@ +/* postgres can not */ +USE plato; + +--INSERT INTO Output +SELECT + Cast(WeakField(subkey, 'string') as uint32) as subkey, + WeakField(strE1, 'string'), + YQL::FromYsonSimpleType(WeakField(strE1, "Yson"), AsAtom("String")) AS strE1overYson +FROM Input +ORDER BY subkey; diff --git a/yql/essentials/tests/sql/suites/weak_field/weak_field_in_group_by.cfg b/yql/essentials/tests/sql/suites/weak_field/weak_field_in_group_by.cfg new file mode 100644 index 0000000000..b1dd7b3cd5 --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/weak_field_in_group_by.cfg @@ -0,0 +1 @@ +in Input input_other_yson.txt diff --git a/yql/essentials/tests/sql/suites/weak_field/weak_field_in_group_by.sql b/yql/essentials/tests/sql/suites/weak_field/weak_field_in_group_by.sql new file mode 100644 index 0000000000..a1bf197bb5 --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/weak_field_in_group_by.sql @@ -0,0 +1,9 @@ +/* postgres can not */ +USE plato; + +SELECT + sk, + count(*) as count +FROM Input +GROUP BY WeakField(uiData, 'uint64') as sk +ORDER BY sk
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/weak_field/weak_field_infer_scheme.cfg b/yql/essentials/tests/sql/suites/weak_field/weak_field_infer_scheme.cfg new file mode 100644 index 0000000000..4895b73f21 --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/weak_field_infer_scheme.cfg @@ -0,0 +1 @@ +in Input infer_scheme.txt diff --git a/yql/essentials/tests/sql/suites/weak_field/weak_field_infer_scheme.sql b/yql/essentials/tests/sql/suites/weak_field/weak_field_infer_scheme.sql new file mode 100644 index 0000000000..a403fed293 --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/weak_field_infer_scheme.sql @@ -0,0 +1,6 @@ +/* postgres can not */ +/* kikimr can not */ +USE plato; +pragma yt.InferSchema; + +SELECT key, subkey, WeakField(value, "String") as value FROM Input; diff --git a/yql/essentials/tests/sql/suites/weak_field/weak_field_join.cfg b/yql/essentials/tests/sql/suites/weak_field/weak_field_join.cfg new file mode 100644 index 0000000000..1410268275 --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/weak_field_join.cfg @@ -0,0 +1,2 @@ +in Input1 other1.txt +in Input2 other2.txt diff --git a/yql/essentials/tests/sql/suites/weak_field/weak_field_join.sql b/yql/essentials/tests/sql/suites/weak_field/weak_field_join.sql new file mode 100644 index 0000000000..dd834f50a3 --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/weak_field_join.sql @@ -0,0 +1,10 @@ +/* postgres can not */ +use plato; + +SELECT + i1.subkey as sk, + WeakField(i1.value1, "String", "funny") as i1v1, + WeakField(i1.value2, "String", "bunny") as i1v2, + WeakField(i2.value1, "String", "short") as i2v1, + WeakField(i2.value2, "String", "circuit") as i2v2 +FROM Input1 as i1 join Input2 as i2 USING(subkey) ORDER BY sk diff --git a/yql/essentials/tests/sql/suites/weak_field/weak_field_join_condition.cfg b/yql/essentials/tests/sql/suites/weak_field/weak_field_join_condition.cfg new file mode 100644 index 0000000000..1410268275 --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/weak_field_join_condition.cfg @@ -0,0 +1,2 @@ +in Input1 other1.txt +in Input2 other2.txt diff --git a/yql/essentials/tests/sql/suites/weak_field/weak_field_join_condition.sql b/yql/essentials/tests/sql/suites/weak_field/weak_field_join_condition.sql new file mode 100644 index 0000000000..7818c68f11 --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/weak_field_join_condition.sql @@ -0,0 +1,11 @@ +/* postgres can not */ +USE plato; + +--INSERT INTO Output +SELECT + i1.subkey as sk, + WeakField(i1.value1, "String", "funny") as i1v1, + WeakField(i1.value2, "String", "bunny") as i1v2, + WeakField(i2.value1, "String", "short") as i2v1, + WeakField(i2.value2, "String", "circuit") as i2v2 +FROM Input1 as i1 JOIN Input2 as i2 ON WeakField(i1.value1, "String") == WeakField(i2.value2, "String") ORDER BY sk diff --git a/yql/essentials/tests/sql/suites/weak_field/weak_field_join_no_src.sqlx b/yql/essentials/tests/sql/suites/weak_field/weak_field_join_no_src.sqlx new file mode 100644 index 0000000000..1781a93f66 --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/weak_field_join_no_src.sqlx @@ -0,0 +1,8 @@ +/* postgres can not */ +use plato; + +SELECT + i1.subkey as sk, + WeakField(value1, "String", "funny") as v1, -- no idea to check value1 from i1._other or i2._other + WeakField(value2, "String", "bunny") as v2 +FROM Input1 as i1 join Input2 as i2 USING(subkey) ORDER BY sk diff --git a/yql/essentials/tests/sql/suites/weak_field/weak_field_join_where.cfg b/yql/essentials/tests/sql/suites/weak_field/weak_field_join_where.cfg new file mode 100644 index 0000000000..1410268275 --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/weak_field_join_where.cfg @@ -0,0 +1,2 @@ +in Input1 other1.txt +in Input2 other2.txt diff --git a/yql/essentials/tests/sql/suites/weak_field/weak_field_join_where.sql b/yql/essentials/tests/sql/suites/weak_field/weak_field_join_where.sql new file mode 100644 index 0000000000..0807940069 --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/weak_field_join_where.sql @@ -0,0 +1,13 @@ +/* postgres can not */ +USE plato; + +--INSERT INTO Output +SELECT + i1.subkey as sk, + WeakField(i1.value1, "String", "funny") as i1v1, + WeakField(i1.value2, "String", "bunny") as i1v2, + WeakField(i2.value1, "String", "short") as i2v1, + WeakField(i2.value2, "String", "circuit") as i2v2 +FROM Input1 as i1 JOIN Input2 as i2 ON WeakField(i1.value1, "String") == WeakField(i2.value2, "String") +WHERE WeakField(i2.key, "String") == "150" or WeakField(i1.key, "String") == "075" +ORDER BY sk diff --git a/yql/essentials/tests/sql/suites/weak_field/weak_field_list_type.sqlx b/yql/essentials/tests/sql/suites/weak_field/weak_field_list_type.sqlx new file mode 100644 index 0000000000..569608e549 --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/weak_field_list_type.sqlx @@ -0,0 +1,3 @@ +/* postgres can not */ +use plato; +select WeakField(value1, "List<Int32>", "epic type!") as ll_i32 from Input1 diff --git a/yql/essentials/tests/sql/suites/weak_field/weak_field_long_fields.cfg b/yql/essentials/tests/sql/suites/weak_field/weak_field_long_fields.cfg new file mode 100644 index 0000000000..11c3f04b25 --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/weak_field_long_fields.cfg @@ -0,0 +1 @@ +in Input input_tutorial_email_short_as_other.txt diff --git a/yql/essentials/tests/sql/suites/weak_field/weak_field_long_fields.sql b/yql/essentials/tests/sql/suites/weak_field/weak_field_long_fields.sql new file mode 100644 index 0000000000..81cc5bacd9 --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/weak_field_long_fields.sql @@ -0,0 +1,8 @@ +/* postgres can not */ +USE plato; + +--INSERT INTO Output +SELECT + WeakField(email, "String"), + WeakField(yandexuid, "Uint64") +FROM Input diff --git a/yql/essentials/tests/sql/suites/weak_field/weak_field_long_name.cfg b/yql/essentials/tests/sql/suites/weak_field/weak_field_long_name.cfg new file mode 100644 index 0000000000..e85c3febf4 --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/weak_field_long_name.cfg @@ -0,0 +1 @@ +in Input other1.txt diff --git a/yql/essentials/tests/sql/suites/weak_field/weak_field_long_name.sql b/yql/essentials/tests/sql/suites/weak_field/weak_field_long_name.sql new file mode 100644 index 0000000000..ab439e9cc9 --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/weak_field_long_name.sql @@ -0,0 +1,7 @@ +/* postgres can not */ +USE plato; + +--INSERT INTO Output +SELECT + WeakField(long_long_long_column_name, 'string') +FROM Input diff --git a/yql/essentials/tests/sql/suites/weak_field/weak_field_num_access.cfg b/yql/essentials/tests/sql/suites/weak_field/weak_field_num_access.cfg new file mode 100644 index 0000000000..b41a784aca --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/weak_field_num_access.cfg @@ -0,0 +1 @@ +in Input4 other4.txt diff --git a/yql/essentials/tests/sql/suites/weak_field/weak_field_num_access.sql b/yql/essentials/tests/sql/suites/weak_field/weak_field_num_access.sql new file mode 100644 index 0000000000..eb6365bff3 --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/weak_field_num_access.sql @@ -0,0 +1,10 @@ +/* postgres can not */ +USE plato; + +--INSERT INTO Output +SELECT + subkey, + WeakField(data3, "int32") as data3, + WeakField(datahole3, "uint32", 999) as holes3 +FROM Input4 +ORDER BY subkey diff --git a/yql/essentials/tests/sql/suites/weak_field/weak_field_opt.cfg b/yql/essentials/tests/sql/suites/weak_field/weak_field_opt.cfg new file mode 100644 index 0000000000..2c8cfa4101 --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/weak_field_opt.cfg @@ -0,0 +1 @@ +in Input3 other3.txt diff --git a/yql/essentials/tests/sql/suites/weak_field/weak_field_opt.sql b/yql/essentials/tests/sql/suites/weak_field/weak_field_opt.sql new file mode 100644 index 0000000000..8f66299362 --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/weak_field_opt.sql @@ -0,0 +1,3 @@ +/* postgres can not */ +use plato; +select subkey, WeakField(data3, "int32") as data3str, WeakField(datahole3, "int32", 999) as holes3 from Input3 order by subkey diff --git a/yql/essentials/tests/sql/suites/weak_field/weak_field_real_col.sql b/yql/essentials/tests/sql/suites/weak_field/weak_field_real_col.sql new file mode 100644 index 0000000000..3249bbc634 --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/weak_field_real_col.sql @@ -0,0 +1,11 @@ +/* postgres can not */ +USE plato; + +--INSERT INTO Output +SELECT + WeakField(key, "String", 'test'), + WeakField(balu, "String", 'bebe'), + WeakField(value, "String", 'zzz') +FROM Input +WHERE key < "200" +ORDER BY key; diff --git a/yql/essentials/tests/sql/suites/weak_field/weak_field_rest.cfg b/yql/essentials/tests/sql/suites/weak_field/weak_field_rest.cfg new file mode 100644 index 0000000000..2c0d92202d --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/weak_field_rest.cfg @@ -0,0 +1 @@ +in Input input_rest.txt diff --git a/yql/essentials/tests/sql/suites/weak_field/weak_field_rest.sql b/yql/essentials/tests/sql/suites/weak_field/weak_field_rest.sql new file mode 100644 index 0000000000..a330421f3f --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/weak_field_rest.sql @@ -0,0 +1,14 @@ +/* postgres can not */ +USE plato; + +--INSERT INTO Output +SELECT + WeakField(animal, "String"), + WeakField(size, "String") as sizeRating, + WeakField(weightMin, "Float"), + WeakField(weightMax, "Float"), + WeakField(wild, "Bool"), + WeakField(pet, "Bool", false), + WeakField(miss, "Bool", true) +FROM Input +ORDER BY weightMin diff --git a/yql/essentials/tests/sql/suites/weak_field/weak_field_strict.cfg b/yql/essentials/tests/sql/suites/weak_field/weak_field_strict.cfg new file mode 100644 index 0000000000..64b3f61710 --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/weak_field_strict.cfg @@ -0,0 +1 @@ +in Input optional.txt diff --git a/yql/essentials/tests/sql/suites/weak_field/weak_field_strict.sql b/yql/essentials/tests/sql/suites/weak_field/weak_field_strict.sql new file mode 100644 index 0000000000..e156940290 --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/weak_field_strict.sql @@ -0,0 +1,10 @@ +/* postgres can not */ +use plato; +SELECT + WeakField(first_num, "uint32") as num_nodef, + WeakField(first_num, "uint32", 11) as num_def, + WeakField(first_null, "uint32") as null_nodef, + WeakField(first_null, "uint32", 42) as null_def, + WeakField(val, "string") as missed_nodef, + WeakField(val, "string", "no value") as missed_def +FROM Input diff --git a/yql/essentials/tests/sql/suites/weak_field/weak_field_to_yson.cfg b/yql/essentials/tests/sql/suites/weak_field/weak_field_to_yson.cfg new file mode 100644 index 0000000000..b1dd7b3cd5 --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/weak_field_to_yson.cfg @@ -0,0 +1 @@ +in Input input_other_yson.txt diff --git a/yql/essentials/tests/sql/suites/weak_field/weak_field_to_yson.sql b/yql/essentials/tests/sql/suites/weak_field/weak_field_to_yson.sql new file mode 100644 index 0000000000..bc00acf465 --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/weak_field_to_yson.sql @@ -0,0 +1,5 @@ +USE plato; + +SELECT + YQL::FromYsonSimpleType(WeakField(uiData, "Yson"), AsAtom("Uint32")) AS val +FROM Input diff --git a/yql/essentials/tests/sql/suites/weak_field/weak_field_type.sql b/yql/essentials/tests/sql/suites/weak_field/weak_field_type.sql new file mode 100644 index 0000000000..0411ee0b98 --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/weak_field_type.sql @@ -0,0 +1,7 @@ +/* postgres can not */ +use plato; +select + WeakField(key, DataType("String")), + WeakField(subkey, OptionalType(DataType("String"))), + WeakField(value, "String") +from Input; diff --git a/yql/essentials/tests/sql/suites/weak_field/weak_field_wrong_types_fail.cfg b/yql/essentials/tests/sql/suites/weak_field/weak_field_wrong_types_fail.cfg new file mode 100644 index 0000000000..faca17b0b2 --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/weak_field_wrong_types_fail.cfg @@ -0,0 +1,2 @@ +xfail +in Input optional.txt diff --git a/yql/essentials/tests/sql/suites/weak_field/weak_field_wrong_types_fail.sql b/yql/essentials/tests/sql/suites/weak_field/weak_field_wrong_types_fail.sql new file mode 100644 index 0000000000..0d8cf87a35 --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/weak_field_wrong_types_fail.sql @@ -0,0 +1,5 @@ +/* postgres can not */ +use plato; +SELECT + WeakField(first_num, "int32", 42) -- first_num column have another type +FROM Input 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 0000000000..1a19c1cc51 --- /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")); diff --git a/yql/essentials/tests/sql/suites/weak_field/yql-7888_input.txt b/yql/essentials/tests/sql/suites/weak_field/yql-7888_input.txt new file mode 100644 index 0000000000..2a8e728cae --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/yql-7888_input.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/weak_field/yql-7888_input.txt.attr b/yql/essentials/tests/sql/suites/weak_field/yql-7888_input.txt.attr new file mode 100644 index 0000000000..5976cdb9c2 --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/yql-7888_input.txt.attr @@ -0,0 +1,22 @@ +{ + "_read_schema" = <"strict" = %false>[ + {"type"="string";"name"="key"}; + {"type"="string";"name"="subkey"}; + {"type"="string";"name"="value"}; + ]; + "schema" = <"strict" = %false; "unique_keys" = %false> + [ + { + "name" = "key"; + "type" = "string" + }; + { + "name" = "subkey"; + "type" = "string" + }; + { + "name" = "value"; + "type" = "string" + } + ] +} diff --git a/yql/essentials/tests/sql/suites/weak_field/yql-7888_mapfieldsubset.cfg b/yql/essentials/tests/sql/suites/weak_field/yql-7888_mapfieldsubset.cfg new file mode 100644 index 0000000000..b4421ba5f3 --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/yql-7888_mapfieldsubset.cfg @@ -0,0 +1 @@ +in Input yql-7888_input.txt diff --git a/yql/essentials/tests/sql/suites/weak_field/yql-7888_mapfieldsubset.sql b/yql/essentials/tests/sql/suites/weak_field/yql-7888_mapfieldsubset.sql new file mode 100644 index 0000000000..34b7271562 --- /dev/null +++ b/yql/essentials/tests/sql/suites/weak_field/yql-7888_mapfieldsubset.sql @@ -0,0 +1,27 @@ +/* postgres can not */ +/* syntax version 1 */ +use plato; + +define subquery $input() as + select + value, + WeakField(strongest_id, "String") as strongest_id, + WeakField(video_position_sec, "String") as video_position_sec, + key, + subkey + from concat(Input, Input) + where key in ("heartbeat", "show", "click") + and subkey in ("native", "gif"); +end define; + +-- Native: +define subquery $native_show_and_clicks($input) as + select + value, strongest_id, key + from $input() + where subkey == "native" + and key in ("click", "show"); +end define; + +select count(distinct strongest_id) as native_users from $native_show_and_clicks($input); +select count(distinct strongest_id) as native_users_with_click from $native_show_and_clicks($input) where key == "click";
\ No newline at end of file |