http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestSortQuery/testSortOnNullColumn.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestSortQuery/testSortOnNullColumn.sql b/tajo-core-tests/src/test/resources/queries/TestSortQuery/testSortOnNullColumn.sql new file mode 100644 index 0000000..6707d27 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestSortQuery/testSortOnNullColumn.sql @@ -0,0 +1,13 @@ +select + * +from ( + select + case when id > 2 then null else id end as col1, + name as col2 + from + nullsort +) a + +order by + col1, + col2; \ No newline at end of file
http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestSortQuery/testSortOnUnicodeTextAsc.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestSortQuery/testSortOnUnicodeTextAsc.sql b/tajo-core-tests/src/test/resources/queries/TestSortQuery/testSortOnUnicodeTextAsc.sql new file mode 100644 index 0000000..6ed1847 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestSortQuery/testSortOnUnicodeTextAsc.sql @@ -0,0 +1,6 @@ +select + * +from + unicode_sort1 +order by + col2 asc; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestSortQuery/testSortOnUnicodeTextDesc.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestSortQuery/testSortOnUnicodeTextDesc.sql b/tajo-core-tests/src/test/resources/queries/TestSortQuery/testSortOnUnicodeTextDesc.sql new file mode 100644 index 0000000..efbb684 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestSortQuery/testSortOnUnicodeTextDesc.sql @@ -0,0 +1,6 @@ +select + * +from + unicode_sort2 +order by + col2 desc; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestSortQuery/testSortWithAlias1.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestSortQuery/testSortWithAlias1.sql b/tajo-core-tests/src/test/resources/queries/TestSortQuery/testSortWithAlias1.sql new file mode 100644 index 0000000..cd8be3e --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestSortQuery/testSortWithAlias1.sql @@ -0,0 +1 @@ +select l_linenumber, l_orderkey as sortkey from lineitem order by sortkey; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestSortQuery/testSortWithAlias2.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestSortQuery/testSortWithAlias2.sql b/tajo-core-tests/src/test/resources/queries/TestSortQuery/testSortWithAlias2.sql new file mode 100644 index 0000000..7a56cac --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestSortQuery/testSortWithAlias2.sql @@ -0,0 +1,9 @@ +select + lineitem.l_orderkey as l_orderkey, +Â count(l_partkey) as cnt +from + lineitem a +group by + lineitem.l_orderkey +order by + lineitem.l_orderkey; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestSortQuery/testSortWithAlias3.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestSortQuery/testSortWithAlias3.sql b/tajo-core-tests/src/test/resources/queries/TestSortQuery/testSortWithAlias3.sql new file mode 100644 index 0000000..446138b --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestSortQuery/testSortWithAlias3.sql @@ -0,0 +1,11 @@ +select + nation.n_nationkey as n_nationkey, + customer.c_name as c_name, + count(nation.n_nationkey) as cnt +from + nation inner join customer on n_nationkey = c_nationkey +group by + nation.n_nationkey, + customer.c_name +order by + n_nationkey, c_name; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestSortQuery/testSortWithAliasButOriginalName.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestSortQuery/testSortWithAliasButOriginalName.sql b/tajo-core-tests/src/test/resources/queries/TestSortQuery/testSortWithAliasButOriginalName.sql new file mode 100644 index 0000000..1d6396a --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestSortQuery/testSortWithAliasButOriginalName.sql @@ -0,0 +1 @@ +select l_linenumber, l_orderkey as sortkey from lineitem order by l_orderkey; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestSortQuery/testSortWithAscDescKeys.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestSortQuery/testSortWithAscDescKeys.sql b/tajo-core-tests/src/test/resources/queries/TestSortQuery/testSortWithAscDescKeys.sql new file mode 100644 index 0000000..2eb9d1c --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestSortQuery/testSortWithAscDescKeys.sql @@ -0,0 +1 @@ +select col1, col2 from table2 order by col1, col2 desc; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestSortQuery/testSortWithConstKeys.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestSortQuery/testSortWithConstKeys.sql b/tajo-core-tests/src/test/resources/queries/TestSortQuery/testSortWithConstKeys.sql new file mode 100644 index 0000000..ddaf84f --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestSortQuery/testSortWithConstKeys.sql @@ -0,0 +1,10 @@ +select + l_orderkey, + l_linenumber, + 1 as key1, + 2 as key2 +from + lineitem +order by + key1, + key2; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestSortQuery/testSortWithDate.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestSortQuery/testSortWithDate.sql b/tajo-core-tests/src/test/resources/queries/TestSortQuery/testSortWithDate.sql new file mode 100644 index 0000000..da00157 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestSortQuery/testSortWithDate.sql @@ -0,0 +1 @@ +select col1, col2, col3 from testSortWithDate order by col1, col2, col3; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestSortQuery/testSortWithExpr1.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestSortQuery/testSortWithExpr1.sql b/tajo-core-tests/src/test/resources/queries/TestSortQuery/testSortWithExpr1.sql new file mode 100644 index 0000000..2aeba26 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestSortQuery/testSortWithExpr1.sql @@ -0,0 +1 @@ +select l_linenumber, l_orderkey as sortkey from lineitem order by l_orderkey + 1; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestSortQuery/testSortWithExpr2.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestSortQuery/testSortWithExpr2.sql b/tajo-core-tests/src/test/resources/queries/TestSortQuery/testSortWithExpr2.sql new file mode 100644 index 0000000..b7247ff --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestSortQuery/testSortWithExpr2.sql @@ -0,0 +1 @@ +select l_linenumber, l_orderkey as sortkey from lineitem order by l_linenumber, l_orderkey, (l_orderkey is null); \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestSortQuery/testSortWithJson.json ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestSortQuery/testSortWithJson.json b/tajo-core-tests/src/test/resources/queries/TestSortQuery/testSortWithJson.json new file mode 100644 index 0000000..efcd0e0 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestSortQuery/testSortWithJson.json @@ -0,0 +1,65 @@ +// select max(l_quantity) as max_quantity, l_orderkey from lineitem group by l_orderkey order by max_quantity; +{ + "IsDistinct": false, + "Projections": [ + { + "AliasName": "max_quantity", + "Expr": { + "IsDistinct": false, + "Signature": "max", + "FuncParams": [ + { + "ColumnName": "l_quantity", + "OpType": "Column" + } + ], + "OpType": "GeneralSetFunction" + }, + "OpType": "Target" + }, + { + "Expr": { + "ColumnName": "l_orderkey", + "OpType": "Column" + }, + "OpType": "Target" + } + ], + "Expr": { + "SortSpecs": [ + { + "SortKey": { + "ColumnName": "max_quantity", + "OpType": "Column" + }, + "IsAsc": true, + "IsNullFirst": false + } + ], + "Expr": { + "Groups": [ + { + "GroupType": "OrdinaryGroup", + "Dimensions": [ + { + "ColumnName": "l_orderkey", + "OpType": "Column" + } + ] + } + ], + "Expr": { + "Relations": [ + { + "TableName": "lineitem", + "OpType": "Relation" + } + ], + "OpType": "RelationList" + }, + "OpType": "Aggregation" + }, + "OpType": "Sort" + }, + "OpType": "Projection" +} http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestSortQuery/testSubQuerySortAfterGroupMultiBlocks.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestSortQuery/testSubQuerySortAfterGroupMultiBlocks.sql b/tajo-core-tests/src/test/resources/queries/TestSortQuery/testSubQuerySortAfterGroupMultiBlocks.sql new file mode 100644 index 0000000..3e7eaab --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestSortQuery/testSubQuerySortAfterGroupMultiBlocks.sql @@ -0,0 +1,5 @@ +select l_orderkey, revenue from ( + select l_orderkey, revenue from ( + select l_orderkey, sum(l_extendedprice*l_discount) as revenue from lineitem group by l_orderkey + ) l1 +) l2 order by l_orderkey \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestSortQuery/testTopK.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestSortQuery/testTopK.sql b/tajo-core-tests/src/test/resources/queries/TestSortQuery/testTopK.sql new file mode 100644 index 0000000..331f3b4 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestSortQuery/testTopK.sql @@ -0,0 +1 @@ +select l_orderkey, l_linenumber from lineitem order by l_orderkey desc limit 3; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestSortQuery/testTopkWithJson.json ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestSortQuery/testTopkWithJson.json b/tajo-core-tests/src/test/resources/queries/TestSortQuery/testTopkWithJson.json new file mode 100644 index 0000000..e3a264f --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestSortQuery/testTopkWithJson.json @@ -0,0 +1,51 @@ +// select l_orderkey, l_linenumber from lineitem order by l_orderkey desc limit 3; +{ + "IsDistinct": false, + "Projections": [ + { + "Expr": { + "ColumnName": "l_orderkey", + "OpType": "Column" + }, + "OpType": "Target" + }, + { + "Expr": { + "ColumnName": "l_linenumber", + "OpType": "Column" + }, + "OpType": "Target" + } + ], + "Expr": { + "Num": { + "Value": "3", + "ValueType": "Unsigned_Integer", + "OpType": "Literal" + }, + "Expr": { + "SortSpecs": [ + { + "SortKey": { + "ColumnName": "l_orderkey", + "OpType": "Column" + }, + "IsAsc": false, + "IsNullFirst": false + } + ], + "Expr": { + "Relations": [ + { + "TableName": "lineitem", + "OpType": "Relation" + } + ], + "OpType": "RelationList" + }, + "OpType": "Sort" + }, + "OpType": "Limit" + }, + "OpType": "Projection" +} http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestTPCH/testFirstJoinInQ7.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestTPCH/testFirstJoinInQ7.sql b/tajo-core-tests/src/test/resources/queries/TestTPCH/testFirstJoinInQ7.sql new file mode 100644 index 0000000..2dfb83d --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestTPCH/testFirstJoinInQ7.sql @@ -0,0 +1,13 @@ +select + n1.n_name as supp_nation, n2.n_name as cust_nation, n1.n_nationkey as s_nationkey, n2.n_nationkey as c_nationkey +from + nation n1 join nation n2 + on + n1.n_name = 'FRANCE' and n2.n_name = 'GERMANY' +UNION ALL +select + n1.n_name as supp_nation, n2.n_name as cust_nation, n1.n_nationkey as s_nationkey, n2.n_nationkey as c_nationkey +from + nation n1 join nation n2 + on + n2.n_name = 'FRANCE' and n1.n_name = 'GERMANY' \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestTPCH/testQ1OrderBy.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestTPCH/testQ1OrderBy.sql b/tajo-core-tests/src/test/resources/queries/TestTPCH/testQ1OrderBy.sql new file mode 100644 index 0000000..5864144 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestTPCH/testQ1OrderBy.sql @@ -0,0 +1,12 @@ +select + l_returnflag, + l_linestatus, + count(*) as count_order +from + lineitem +group by + l_returnflag, + l_linestatus +order by + l_returnflag, + l_linestatus; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestTPCH/testQ2FourJoins.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestTPCH/testQ2FourJoins.sql b/tajo-core-tests/src/test/resources/queries/TestTPCH/testQ2FourJoins.sql new file mode 100644 index 0000000..ffaaf87 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestTPCH/testQ2FourJoins.sql @@ -0,0 +1,18 @@ +select + s_acctbal, + s_name, + n_name, + p_partkey, + p_mfgr, + s_address, + s_phone, + s_comment, + ps_supplycost, + r_name, + p_type, + p_size +from + region join nation on n_regionkey = r_regionkey and r_name = 'AMERICA' + join supplier on s_nationkey = n_nationkey + join partsupp on s_suppkey = ps_suppkey + join part on p_partkey = ps_partkey and p_type like '%BRASS' and p_size = 15; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestTPCH/testTPCH14Expr.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestTPCH/testTPCH14Expr.sql b/tajo-core-tests/src/test/resources/queries/TestTPCH/testTPCH14Expr.sql new file mode 100644 index 0000000..8f6047f --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestTPCH/testTPCH14Expr.sql @@ -0,0 +1,12 @@ +select + 100 * + sum( + case when p_type like 'PROMO%' then l_extendedprice else 0.0 end + ) / + sum( + l_extendedprice * (1 - l_discount) + ) as promo_revenue +from + lineitem, part +where + l_partkey = p_partkey; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestTPCH/testTPCHQ5.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestTPCH/testTPCHQ5.sql b/tajo-core-tests/src/test/resources/queries/TestTPCH/testTPCHQ5.sql new file mode 100644 index 0000000..e7d3d56 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestTPCH/testTPCHQ5.sql @@ -0,0 +1,24 @@ +select + n_name, + sum(l_extendedprice * (1 - l_discount)) as revenue +from + customer, + orders, + lineitem, + supplier, + nation, + region +where + c_custkey = o_custkey and + l_orderkey = o_orderkey and + l_suppkey = s_suppkey and + c_nationkey = s_nationkey and + s_nationkey = n_nationkey and + n_regionkey = r_regionkey and + r_name = 'ASIA' and + o_orderdate >= '1994-01-01' and + o_orderdate < '1995-01-01' +group by + n_name +order by + revenue desc \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestTablePartitions/case1.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestTablePartitions/case1.sql b/tajo-core-tests/src/test/resources/queries/TestTablePartitions/case1.sql new file mode 100644 index 0000000..8c1116e --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestTablePartitions/case1.sql @@ -0,0 +1,16 @@ +select + * +from ( + select + col1, + col2, + null_col, + key + from + testQueryCasesOnColumnPartitionedTable + where + (key = 45.0 or key = 38.0) and null_col is null +) test +order by + col1, col2 +; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestTablePartitions/case10.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestTablePartitions/case10.sql b/tajo-core-tests/src/test/resources/queries/TestTablePartitions/case10.sql new file mode 100644 index 0000000..715d8e8 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestTablePartitions/case10.sql @@ -0,0 +1 @@ +select col1, key, -key as res from testQueryCasesOnColumnPartitionedTable; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestTablePartitions/case11.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestTablePartitions/case11.sql b/tajo-core-tests/src/test/resources/queries/TestTablePartitions/case11.sql new file mode 100644 index 0000000..a5bf1db --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestTablePartitions/case11.sql @@ -0,0 +1 @@ +select key as key_alias from testQueryCasesOnColumnPartitionedTable \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestTablePartitions/case12.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestTablePartitions/case12.sql b/tajo-core-tests/src/test/resources/queries/TestTablePartitions/case12.sql new file mode 100644 index 0000000..2324439 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestTablePartitions/case12.sql @@ -0,0 +1,7 @@ +select + key as key_alias, + count(*) cnt +from + testQueryCasesOnColumnPartitionedTable +group by key_alias +order by key_alias desc \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestTablePartitions/case13.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestTablePartitions/case13.sql b/tajo-core-tests/src/test/resources/queries/TestTablePartitions/case13.sql new file mode 100644 index 0000000..efb7432 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestTablePartitions/case13.sql @@ -0,0 +1,11 @@ +select + key_alias as key, cnt +from ( + select + key as key_alias, + count(*) cnt + from + testQueryCasesOnColumnPartitionedTable + group by key_alias + order by key_alias desc +) a \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestTablePartitions/case14.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestTablePartitions/case14.sql b/tajo-core-tests/src/test/resources/queries/TestTablePartitions/case14.sql new file mode 100644 index 0000000..ac1ad20 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestTablePartitions/case14.sql @@ -0,0 +1,3 @@ +select count(*) as cnt + from + testColumnPartitionedTableWithSmallerExpressions1 \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestTablePartitions/case15.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestTablePartitions/case15.sql b/tajo-core-tests/src/test/resources/queries/TestTablePartitions/case15.sql new file mode 100644 index 0000000..03cf55b --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestTablePartitions/case15.sql @@ -0,0 +1,3 @@ +select count(*) as cnt + from + testColumnPartitionedTableWithSmallerExpressions2 \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestTablePartitions/case2.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestTablePartitions/case2.sql b/tajo-core-tests/src/test/resources/queries/TestTablePartitions/case2.sql new file mode 100644 index 0000000..ee94778 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestTablePartitions/case2.sql @@ -0,0 +1,31 @@ +select + * +from ( + select + col1, + col2, + null_col, + key + from + testQueryCasesOnColumnPartitionedTable + where + (key = 45.0 or key = 38.0) and null_col is null + + union all + + select + col1, + col2, + null_col, + key + from + testQueryCasesOnColumnPartitionedTable + where + (key = 45.0 or key = 38.0) and null_col is null + +) t1 + +order by + col1, + col2 +; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestTablePartitions/case3.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestTablePartitions/case3.sql b/tajo-core-tests/src/test/resources/queries/TestTablePartitions/case3.sql new file mode 100644 index 0000000..6cb1ea1 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestTablePartitions/case3.sql @@ -0,0 +1,8 @@ +select + l.l_orderkey, + p.col1, + key + from lineitem as l, testQueryCasesOnColumnPartitionedTable as p +where + (key = 45.0 or key = 38.0) and l.l_orderkey = p.col1; + http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestTablePartitions/case4.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestTablePartitions/case4.sql b/tajo-core-tests/src/test/resources/queries/TestTablePartitions/case4.sql new file mode 100644 index 0000000..477a456 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestTablePartitions/case4.sql @@ -0,0 +1 @@ +select pow(key, 2) from testQueryCasesOnColumnPartitionedTable \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestTablePartitions/case5.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestTablePartitions/case5.sql b/tajo-core-tests/src/test/resources/queries/TestTablePartitions/case5.sql new file mode 100644 index 0000000..180dd72 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestTablePartitions/case5.sql @@ -0,0 +1 @@ +select round(pow(key + 1, 2)) from testQueryCasesOnColumnPartitionedTable \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestTablePartitions/case6.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestTablePartitions/case6.sql b/tajo-core-tests/src/test/resources/queries/TestTablePartitions/case6.sql new file mode 100644 index 0000000..cd54486 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestTablePartitions/case6.sql @@ -0,0 +1 @@ +select col1, key from testQueryCasesOnColumnPartitionedTable order by pow(key, 2) desc \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestTablePartitions/case7.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestTablePartitions/case7.sql b/tajo-core-tests/src/test/resources/queries/TestTablePartitions/case7.sql new file mode 100644 index 0000000..e2c4d83 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestTablePartitions/case7.sql @@ -0,0 +1 @@ +select col1, key from testQueryCasesOnColumnPartitionedTable WHERE key BETWEEN 35 AND 48; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestTablePartitions/case8.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestTablePartitions/case8.sql b/tajo-core-tests/src/test/resources/queries/TestTablePartitions/case8.sql new file mode 100644 index 0000000..fe57eb8 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestTablePartitions/case8.sql @@ -0,0 +1 @@ +select col1, CASE key WHEN 36 THEN key WHEN 49 THEN key ELSE key END from testQueryCasesOnColumnPartitionedTable; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestTablePartitions/case9.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestTablePartitions/case9.sql b/tajo-core-tests/src/test/resources/queries/TestTablePartitions/case9.sql new file mode 100644 index 0000000..595e533 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestTablePartitions/case9.sql @@ -0,0 +1 @@ +select col1, CAST(key AS INT) from testQueryCasesOnColumnPartitionedTable; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestTablePartitions/lineitemspecial_ddl.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestTablePartitions/lineitemspecial_ddl.sql b/tajo-core-tests/src/test/resources/queries/TestTablePartitions/lineitemspecial_ddl.sql new file mode 100644 index 0000000..ac49b23 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestTablePartitions/lineitemspecial_ddl.sql @@ -0,0 +1,3 @@ +create external table if not exists lineitemspecial ( + l_orderkey INT4, l_shipinstruct TEXT, l_shipmode TEXT) +using text with ('text.delimiter'='|', 'text.null'='NULL') location ${table.path}; http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestTableSubQuery/testGroupBySubQuery.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestTableSubQuery/testGroupBySubQuery.sql b/tajo-core-tests/src/test/resources/queries/TestTableSubQuery/testGroupBySubQuery.sql new file mode 100644 index 0000000..9f86886 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestTableSubQuery/testGroupBySubQuery.sql @@ -0,0 +1 @@ +select sum(l_extendedprice * l_discount) as revenue from (select * from lineitem) as l; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestTableSubQuery/testGroupbySubqueryWithJson.json ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestTableSubQuery/testGroupbySubqueryWithJson.json b/tajo-core-tests/src/test/resources/queries/TestTableSubQuery/testGroupbySubqueryWithJson.json new file mode 100644 index 0000000..f607a0b --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestTableSubQuery/testGroupbySubqueryWithJson.json @@ -0,0 +1,59 @@ +// select sum(l_extendedprice * l_discount) as revenue from (select * from lineitem) as l; +{ + "IsDistinct": false, + "Projections": [ + { + "AliasName": "revenue", + "Expr": { + "IsDistinct": false, + "Signature": "sum", + "FuncParams": [ + { + "LeftExpr": { + "ColumnName": "l_extendedprice", + "OpType": "Column" + }, + "RightExpr": { + "ColumnName": "l_discount", + "OpType": "Column" + }, + "OpType": "Multiply" + } + ], + "OpType": "GeneralSetFunction" + }, + "OpType": "Target" + } + ], + "Expr": { + "Relations": [ + { + "SubPlan": { + "IsDistinct": false, + "Projections": [ + { + "Expr": { + "OpType": "Asterisk" + }, + "OpType": "Target" + } + ], + "Expr": { + "Relations": [ + { + "TableName": "lineitem", + "OpType": "Relation" + } + ], + "OpType": "RelationList" + }, + "OpType": "Projection" + }, + "TableName": "l", + "OpType": "TablePrimaryTableSubQuery" + } + ], + "OpType": "RelationList" + }, + "OpType": "Projection" +} http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestTableSubQuery/testJoinSubQuery.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestTableSubQuery/testJoinSubQuery.sql b/tajo-core-tests/src/test/resources/queries/TestTableSubQuery/testJoinSubQuery.sql new file mode 100644 index 0000000..5944365 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestTableSubQuery/testJoinSubQuery.sql @@ -0,0 +1,5 @@ +SELECT + A.n_regionkey, B.r_regionkey, A.n_name, B.r_name +FROM + (SELECT * FROM nation WHERE n_name LIKE 'A%') A + JOIN region B ON A.n_regionkey=B.r_regionkey; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestTableSubQuery/testJoinSubQuery2.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestTableSubQuery/testJoinSubQuery2.sql b/tajo-core-tests/src/test/resources/queries/TestTableSubQuery/testJoinSubQuery2.sql new file mode 100644 index 0000000..e08b8fe --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestTableSubQuery/testJoinSubQuery2.sql @@ -0,0 +1,4 @@ +SELECT + A.n_regionkey, B.r_regionkey, A.n_name, B.r_name +FROM + (SELECT * FROM nation WHERE n_name LIKE 'A%') A, region B WHERE A.n_regionkey=B.r_regionkey; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestTableSubQuery/testJoinSubqueryWithJson.json ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestTableSubQuery/testJoinSubqueryWithJson.json b/tajo-core-tests/src/test/resources/queries/TestTableSubQuery/testJoinSubqueryWithJson.json new file mode 100644 index 0000000..77f639c --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestTableSubQuery/testJoinSubqueryWithJson.json @@ -0,0 +1,108 @@ +{ + "IsDistinct": false, + "Projections": [ + { + "Expr": { + "Qualifier": "a", + "ColumnName": "n_regionkey", + "OpType": "Column" + }, + "OpType": "Target" + }, + { + "Expr": { + "Qualifier": "b", + "ColumnName": "r_regionkey", + "OpType": "Column" + }, + "OpType": "Target" + }, + { + "Expr": { + "Qualifier": "a", + "ColumnName": "n_name", + "OpType": "Column" + }, + "OpType": "Target" + }, + { + "Expr": { + "Qualifier": "b", + "ColumnName": "r_name", + "OpType": "Column" + }, + "OpType": "Target" + } + ], + "Expr": { + "Relations": [ + { + "JoinType": "INNER", + "JoinCondition": { + "LeftExpr": { + "Qualifier": "a", + "ColumnName": "n_regionkey", + "OpType": "Column" + }, + "RightExpr": { + "Qualifier": "b", + "ColumnName": "r_regionkey", + "OpType": "Column" + }, + "OpType": "Equals" + }, + "IsNatural": false, + "LeftExpr": { + "SubPlan": { + "IsDistinct": false, + "Projections": [ + { + "Expr": { + "OpType": "Asterisk" + }, + "OpType": "Target" + } + ], + "Expr": { + "SelectCondition": { + "IsNot": false, + "IsCaseInsensitive": false, + "LeftExpr": { + "ColumnName": "n_name", + "OpType": "Column" + }, + "RightExpr": { + "Value": "A%", + "ValueType": "String", + "OpType": "Literal" + }, + "OpType": "LikePredicate" + }, + "Expr": { + "Relations": [ + { + "TableName": "nation", + "OpType": "Relation" + } + ], + "OpType": "RelationList" + }, + "OpType": "Filter" + }, + "OpType": "Projection" + }, + "TableName": "a", + "OpType": "TablePrimaryTableSubQuery" + }, + "RightExpr": { + "TableName": "region", + "TableAlias": "b", + "OpType": "Relation" + }, + "OpType": "Join" + } + ], + "OpType": "RelationList" + }, + "OpType": "Projection" +} http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestTableSubQuery/testTableSubquery1.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestTableSubQuery/testTableSubquery1.sql b/tajo-core-tests/src/test/resources/queries/TestTableSubQuery/testTableSubquery1.sql new file mode 100644 index 0000000..95fac4f --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestTableSubQuery/testTableSubquery1.sql @@ -0,0 +1 @@ +select l_orderkey from (select * from lineitem) as l; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestTajoJdbc/create_table_with_date_ddl.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestTajoJdbc/create_table_with_date_ddl.sql b/tajo-core-tests/src/test/resources/queries/TestTajoJdbc/create_table_with_date_ddl.sql new file mode 100644 index 0000000..caeeaf9 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestTajoJdbc/create_table_with_date_ddl.sql @@ -0,0 +1,10 @@ +-- Sort Table +-- It is used in TestSortQuery::testSortWithDate + +create external table table1 ( + col1 timestamp, + col2 date, + col3 time +) using text +with ('text.delimiter'='|', 'text.null'='NULL') +location ${table.path}; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestTaskStatusUpdate/case1.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestTaskStatusUpdate/case1.sql b/tajo-core-tests/src/test/resources/queries/TestTaskStatusUpdate/case1.sql new file mode 100644 index 0000000..f6d0eb3 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestTaskStatusUpdate/case1.sql @@ -0,0 +1 @@ +select l_linenumber, count(1) as unique_key from lineitem group by l_linenumber; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestTaskStatusUpdate/case2.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestTaskStatusUpdate/case2.sql b/tajo-core-tests/src/test/resources/queries/TestTaskStatusUpdate/case2.sql new file mode 100644 index 0000000..c3e09f1 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestTaskStatusUpdate/case2.sql @@ -0,0 +1,5 @@ +select l_returnflag, l_linestatus, sum(l_quantity) as sum_qty, sum(l_extendedprice) as sum_base_price, sum(l_extendedprice*(1-l_discount)) as sum_disc_price, sum +(l_extendedprice*(1-l_discount)*(1+l_tax)) as sum_charge, avg(l_quantity) as avg_qty, avg(l_extendedprice) as avg_price, avg(l_discount) as avg_disc, count(*) as + count_order +from lineitem +group by l_returnflag, l_linestatus order by l_returnflag, l_linestatus; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestTaskStatusUpdate/case3.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestTaskStatusUpdate/case3.sql b/tajo-core-tests/src/test/resources/queries/TestTaskStatusUpdate/case3.sql new file mode 100644 index 0000000..a0f9c78 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestTaskStatusUpdate/case3.sql @@ -0,0 +1,10 @@ +select * +from ( + select a.col1, a.col2, a.key + from ColumnPartitionedTable a + join ColumnPartitionedTable b on a.key = b.key + where + (a.key = 45.0 or a.key = 38.0) +) test +order by + col1, col2 \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestTruncateTable/table1_ddl.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestTruncateTable/table1_ddl.sql b/tajo-core-tests/src/test/resources/queries/TestTruncateTable/table1_ddl.sql new file mode 100644 index 0000000..b6196ec --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestTruncateTable/table1_ddl.sql @@ -0,0 +1 @@ +CREATE TABLE truncate_table1 AS SELECT * FROM default.lineitem; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestTruncateTable/table2_ddl.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestTruncateTable/table2_ddl.sql b/tajo-core-tests/src/test/resources/queries/TestTruncateTable/table2_ddl.sql new file mode 100644 index 0000000..f00f593 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestTruncateTable/table2_ddl.sql @@ -0,0 +1 @@ +CREATE EXTERNAL TABLE ${0} (id int, str text, num int) using text location ${table.path}; http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testComplexUnion1.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testComplexUnion1.sql b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testComplexUnion1.sql new file mode 100644 index 0000000..0ba2909 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testComplexUnion1.sql @@ -0,0 +1,27 @@ +SELECT + l_orderkey, + l_partkey, + query +FROM + ( + SELECT + l_orderkey, + l_partkey, + 'abc' as query + FROM + lineitem + WHERE + l_orderkey = 1 + + UNION ALL + + SELECT + l_orderkey, + l_partkey, + 'bbc' as query + FROM + lineitem + WHERE + l_orderkey = 1 +) result + http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testComplexUnion2.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testComplexUnion2.sql b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testComplexUnion2.sql new file mode 100644 index 0000000..d54ed27 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testComplexUnion2.sql @@ -0,0 +1,35 @@ +SELECT * +FROM +( + SELECT + l_orderkey, + l_partkey, + url + FROM + ( + SELECT + l_orderkey, + l_partkey, + CASE + WHEN + l_partkey IS NOT NULL THEN '' + WHEN l_orderkey = 1 THEN '1' + ELSE + '2' + END AS url + FROM + lineitem + ) res1 + JOIN + ( + SELECT + * + FROM + part + ) res2 + ON l_partkey = p_partkey +) result + + + + http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnion1.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnion1.sql b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnion1.sql new file mode 100644 index 0000000..34ca8d6 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnion1.sql @@ -0,0 +1,19 @@ +select + num +from ( + +select + o_custkey as num +from + orders + +union + +select + c_custkey as num +from + customer +) table1 + +order by + num; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnion10.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnion10.sql b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnion10.sql new file mode 100644 index 0000000..2037564 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnion10.sql @@ -0,0 +1,158 @@ +SELECT * FROM ( + SELECT + * + FROM ( + SELECT + n_nationkey, + n_name + + FROM ( + SELECT + n_nationkey, + n_name + FROM + nation + WHERE + n_regionkey = 0 + + + UNION + + SELECT + n_nationkey, + n_name + FROM + nation + WHERE + n_regionkey = 0 + + ) T1 + GROUP BY + n_nationkey, + n_name + ORDER BY + n_nationkey desc, + n_name desc + + UNION + + SELECT + n_nationkey, + n_name + + FROM ( + SELECT + n_nationkey, + n_name + FROM + nation + WHERE + n_regionkey = 0 + + UNION + + SELECT + n_nationkey, + n_name + FROM + nation + WHERE + n_regionkey = 0 + ) T2 + + GROUP BY + n_nationkey, + n_name + + ORDER BY + n_nationkey desc, + n_name desc + + ) TABLE1 + + ORDER BY + n_nationkey, + n_name + + UNION + + SELECT + * + FROM ( + SELECT + n_nationkey, + n_name + + FROM ( + SELECT + n_nationkey, + n_name + FROM + nation + WHERE + n_regionkey = 0 + + + UNION + + SELECT + n_nationkey, + n_name + FROM + nation + WHERE + n_regionkey = 0 + + ) T3 + GROUP BY + n_nationkey, + n_name + ORDER BY + n_nationkey desc, + n_name desc + + UNION + + SELECT + n_nationkey, + n_name + + FROM ( + SELECT + n_nationkey, + n_name + FROM + nation + WHERE + n_regionkey = 0 + + UNION + + SELECT + n_nationkey, + n_name + FROM + nation + WHERE + n_regionkey = 0 + ) T4 + + GROUP BY + n_nationkey, + n_name + + ORDER BY + n_nationkey desc, + n_name desc + + ) TABLE2 + + ORDER BY + n_nationkey, + n_name + +) TABLE3 + +ORDER BY + n_nationkey, + n_name; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnion11.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnion11.sql b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnion11.sql new file mode 100644 index 0000000..b3b0feb --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnion11.sql @@ -0,0 +1,8 @@ +select col1, col2, col3 +from ( + select L_RETURNFLAG as col1, L_EXTENDEDPRICE as col2, concat(L_RECEIPTDATE, L_LINESTATUS) as col3 from lineitem + union + select P_TYPE as col1, P_RETAILPRICE col2, P_NAME col3 from part +) a +where col3 like '1993%' and col2 > 46796 + http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnion12.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnion12.sql b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnion12.sql new file mode 100644 index 0000000..64ad0a7 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnion12.sql @@ -0,0 +1,14 @@ +select col1, col2, col3 +from ( + select + col1, col2, col3 + from + (select + L_RETURNFLAG as col1, L_EXTENDEDPRICE as col2, concat(L_RECEIPTDATE, L_LINESTATUS) as col3 + from + lineitem) b + union + select P_TYPE as col1, P_RETAILPRICE * 100 col2, concat('1993', P_NAME) col3 from part +) a +where col3 like '1993%' and col2 > 46796 +order by col2; http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnion13.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnion13.sql b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnion13.sql new file mode 100644 index 0000000..763eef9 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnion13.sql @@ -0,0 +1,15 @@ +select col1, col2, col3 +from ( + select + col1, col2, col3 + from + (select + L_RETURNFLAG as col1, L_EXTENDEDPRICE as col2, concat(L_RECEIPTDATE, L_LINESTATUS) as col3 + from + lineitem + where col2 > 46796) b + union + select P_TYPE as col1, P_RETAILPRICE * 100 col2, concat('1993', P_NAME) col3 from part +) a +where col3 like '1993%' +order by col2 http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnion14.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnion14.sql b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnion14.sql new file mode 100644 index 0000000..79c8365 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnion14.sql @@ -0,0 +1,8 @@ +select col1, cnt +from ( + select L_RETURNFLAG as col1, count(*) as cnt from lineitem group by col1 + union + select cast(n_regionkey as TEXT) as col1, count(*) as cnt from nation group by col1 +) a +where a.cnt > 1 +order by a.col1 \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnion15.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnion15.sql b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnion15.sql new file mode 100644 index 0000000..19a7eb7 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnion15.sql @@ -0,0 +1,15 @@ +select col1, sum(cnt) +from ( + select col1, cnt + from ( select l_returnflag col1, count(*) cnt from lineitem + join orders on l_orderkey = o_orderkey and o_custkey > 0 + group by l_returnflag) b + where col1 = 'N' + union + select cast(n_regionkey as TEXT) as col1, count(*) as cnt from nation + where n_regionkey > 2 + group by col1 +) a +where round(cast(a.cnt as FLOAT4)) > 1.0 +group by a.col1 +order by a.col1 http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnion16.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnion16.sql b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnion16.sql new file mode 100644 index 0000000..dd9dca4 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnion16.sql @@ -0,0 +1,15 @@ +select col1, sum(cnt) +from ( + select col1, cnt + from ( select l_returnflag col1, count(distinct l_orderkey) cnt from lineitem + join orders on l_orderkey = o_orderkey and o_custkey > 0 + group by l_returnflag) b + where col1 = 'N' + union + select cast(n_regionkey as TEXT) as col1, count(*) as cnt from nation + where n_regionkey > 2 + group by col1 +) a +where round(cast(a.cnt as FLOAT4)) > 1.0 +group by a.col1 +order by a.col1 http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnion2.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnion2.sql b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnion2.sql new file mode 100644 index 0000000..bcf2433 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnion2.sql @@ -0,0 +1,18 @@ +select + l_orderkey +from ( + select + l_orderkey + from + lineitem l1 + + union + + select + l_orderkey + from + lineitem l2 +) table1 + +order by + l_orderkey; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnion3.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnion3.sql b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnion3.sql new file mode 100644 index 0000000..12a5b64 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnion3.sql @@ -0,0 +1,19 @@ +select + total +from ( + +select + count(*) as total +from + orders + +union + +select + count(*) as total +from + customer +) table1 + +order by + total; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnion4.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnion4.sql b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnion4.sql new file mode 100644 index 0000000..f42325a --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnion4.sql @@ -0,0 +1,16 @@ +select + count(*) +from ( + + select + count(*) as total + from + orders + + union + + select + count(*) as total + from + customer +) table1; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnion5.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnion5.sql b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnion5.sql new file mode 100644 index 0000000..1d83c5c --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnion5.sql @@ -0,0 +1,20 @@ +select + count(*) +from ( + + select + count(*) as total + from + orders + WHERE + o_orderkey > 0 + + union + + select + count(*) as total + from + customer + WHERE + c_custkey > 0 +) table1; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnion6.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnion6.sql b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnion6.sql new file mode 100644 index 0000000..300cb46 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnion6.sql @@ -0,0 +1,15 @@ +SELECT + count(*) +FROM ( + SELECT + * + FROM + lineitem + + UNION + + SELECT + * + FROM + lineitem +) T \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnion7.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnion7.sql b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnion7.sql new file mode 100644 index 0000000..ad3f7d2 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnion7.sql @@ -0,0 +1,18 @@ +SELECT + orderkey +FROM ( + SELECT + l_orderkey as orderkey + FROM + lineitem + + UNION + + SELECT + l_orderkey as orderkey + FROM + lineitem +) T + +order by + orderkey; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnion8.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnion8.sql b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnion8.sql new file mode 100644 index 0000000..aa8333d --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnion8.sql @@ -0,0 +1,24 @@ +SELECT + l_returnflag, + l_linestatus + +FROM ( + SELECT + * + FROM + lineitem + WHERE + l_returnflag = 'R' + + UNION + + SELECT + * + FROM + lineitem + WHERE + l_returnflag = 'R' +) T +GROUP BY + l_returnflag, + l_linestatus; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnion9.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnion9.sql b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnion9.sql new file mode 100644 index 0000000..7b0811a --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnion9.sql @@ -0,0 +1,29 @@ +SELECT + n_nationkey, + n_name + +FROM ( + SELECT + * + FROM + nation + WHERE + n_regionkey = 0 + + + UNION + + SELECT + * + FROM + nation + WHERE + n_regionkey = 0 + +) T +GROUP BY + n_nationkey, + n_name +ORDER BY + n_nationkey desc, + n_name desc; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAll1.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAll1.sql b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAll1.sql new file mode 100644 index 0000000..363512c --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAll1.sql @@ -0,0 +1,19 @@ +select + num +from ( + +select + o_custkey as num +from + orders + +union all + +select + c_custkey as num +from + customer +) table1 + +order by + num; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAll10.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAll10.sql b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAll10.sql new file mode 100644 index 0000000..97c8364 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAll10.sql @@ -0,0 +1,158 @@ +SELECT * FROM ( + SELECT + * + FROM ( + SELECT + n_nationkey, + n_name + + FROM ( + SELECT + n_nationkey, + n_name + FROM + nation + WHERE + n_regionkey = 0 + + + UNION ALL + + SELECT + n_nationkey, + n_name + FROM + nation + WHERE + n_regionkey = 0 + + ) T1 + GROUP BY + n_nationkey, + n_name + ORDER BY + n_nationkey desc, + n_name desc + + UNION all + + SELECT + n_nationkey, + n_name + + FROM ( + SELECT + n_nationkey, + n_name + FROM + nation + WHERE + n_regionkey = 0 + + UNION ALL + + SELECT + n_nationkey, + n_name + FROM + nation + WHERE + n_regionkey = 0 + ) T2 + + GROUP BY + n_nationkey, + n_name + + ORDER BY + n_nationkey desc, + n_name desc + + ) TABLE1 + + ORDER BY + n_nationkey, + n_name + + UNION all + + SELECT + * + FROM ( + SELECT + n_nationkey, + n_name + + FROM ( + SELECT + n_nationkey, + n_name + FROM + nation + WHERE + n_regionkey = 0 + + + UNION ALL + + SELECT + n_nationkey, + n_name + FROM + nation + WHERE + n_regionkey = 0 + + ) T3 + GROUP BY + n_nationkey, + n_name + ORDER BY + n_nationkey desc, + n_name desc + + UNION all + + SELECT + n_nationkey, + n_name + + FROM ( + SELECT + n_nationkey, + n_name + FROM + nation + WHERE + n_regionkey = 0 + + UNION ALL + + SELECT + n_nationkey, + n_name + FROM + nation + WHERE + n_regionkey = 0 + ) T4 + + GROUP BY + n_nationkey, + n_name + + ORDER BY + n_nationkey desc, + n_name desc + + ) TABLE2 + + ORDER BY + n_nationkey, + n_name + +) TABLE3 + +ORDER BY + n_nationkey, + n_name; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAll11.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAll11.sql b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAll11.sql new file mode 100644 index 0000000..ec1a430 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAll11.sql @@ -0,0 +1,8 @@ +select col1, col2, col3 +from ( + select L_RETURNFLAG as col1, L_EXTENDEDPRICE as col2, concat(L_RECEIPTDATE, L_LINESTATUS) as col3 from lineitem + union all + select P_TYPE as col1, P_RETAILPRICE col2, P_NAME col3 from part +) a +where col3 like '1993%' and col2 > 46796 + http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAll12.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAll12.sql b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAll12.sql new file mode 100644 index 0000000..6b6a9ad --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAll12.sql @@ -0,0 +1,13 @@ +select col1, col2, col3 +from ( + select + col1, col2, col3 + from + (select + L_RETURNFLAG as col1, L_EXTENDEDPRICE as col2, concat(L_RECEIPTDATE, L_LINESTATUS) as col3 + from + lineitem) b + union all + select P_TYPE as col1, P_RETAILPRICE * 100 col2, concat('1993', P_NAME) col3 from part +) a +where col3 like '1993%' and col2 > 46796 http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAll13.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAll13.sql b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAll13.sql new file mode 100644 index 0000000..70b0891 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAll13.sql @@ -0,0 +1,14 @@ +select col1, col2, col3 +from ( + select + col1, col2, col3 + from + (select + L_RETURNFLAG as col1, L_EXTENDEDPRICE as col2, concat(L_RECEIPTDATE, L_LINESTATUS) as col3 + from + lineitem + where col2 > 46796) b + union all + select P_TYPE as col1, P_RETAILPRICE * 100 col2, concat('1993', P_NAME) col3 from part +) a +where col3 like '1993%' http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAll14.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAll14.sql b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAll14.sql new file mode 100644 index 0000000..f47510e --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAll14.sql @@ -0,0 +1,8 @@ +select col1, cnt +from ( + select L_RETURNFLAG as col1, count(*) as cnt from lineitem group by col1 + union all + select cast(n_regionkey as TEXT) as col1, count(*) as cnt from nation group by col1 +) a +where a.cnt > 1 +order by a.col1 \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAll15.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAll15.sql b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAll15.sql new file mode 100644 index 0000000..2e382d0 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAll15.sql @@ -0,0 +1,15 @@ +select col1, sum(cnt) +from ( + select col1, cnt + from ( select l_returnflag col1, count(*) cnt from lineitem + join orders on l_orderkey = o_orderkey and o_custkey > 0 + group by l_returnflag) b + where col1 = 'N' + union all + select cast(n_regionkey as TEXT) as col1, count(*) as cnt from nation + where n_regionkey > 2 + group by col1 +) a +where round(cast(a.cnt as FLOAT4)) > 1.0 +group by a.col1 +order by a.col1 http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAll16.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAll16.sql b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAll16.sql new file mode 100644 index 0000000..59e9c1f --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAll16.sql @@ -0,0 +1,15 @@ +select col1, sum(cnt) +from ( + select col1, cnt + from ( select l_returnflag col1, count(distinct l_orderkey) cnt from lineitem + join orders on l_orderkey = o_orderkey and o_custkey > 0 + group by l_returnflag) b + where col1 = 'N' + union all + select cast(n_regionkey as TEXT) as col1, count(*) as cnt from nation + where n_regionkey > 2 + group by col1 +) a +where round(cast(a.cnt as FLOAT4)) > 1.0 +group by a.col1 +order by a.col1 http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAll2.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAll2.sql b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAll2.sql new file mode 100644 index 0000000..e5fa5d2 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAll2.sql @@ -0,0 +1,18 @@ +select + l_orderkey +from ( + select + l_orderkey + from + lineitem l1 + + union all + + select + l_orderkey + from + lineitem l2 +) table1 + +order by + l_orderkey; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAll3.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAll3.sql b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAll3.sql new file mode 100644 index 0000000..68266ec --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAll3.sql @@ -0,0 +1,19 @@ +select + total +from ( + +select + count(*) as total +from + orders + +union all + +select + count(*) as total +from + customer +) table1 + +order by + total; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAll4.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAll4.sql b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAll4.sql new file mode 100644 index 0000000..31dce06 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAll4.sql @@ -0,0 +1,16 @@ +select + count(*) +from ( + + select + count(*) as total + from + orders + + union all + + select + count(*) as total + from + customer +) table1; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAll5.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAll5.sql b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAll5.sql new file mode 100644 index 0000000..8a55799 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAll5.sql @@ -0,0 +1,20 @@ +select + count(*) +from ( + + select + count(*) as total + from + orders + WHERE + o_orderkey > 0 + + union all + + select + count(*) as total + from + customer + WHERE + c_custkey > 0 +) table1; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAll6.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAll6.sql b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAll6.sql new file mode 100644 index 0000000..8052632 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAll6.sql @@ -0,0 +1,15 @@ +SELECT + count(*) +FROM ( + SELECT + * + FROM + lineitem + + UNION ALL + + SELECT + * + FROM + lineitem +) T \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAll7.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAll7.sql b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAll7.sql new file mode 100644 index 0000000..3592a6a --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAll7.sql @@ -0,0 +1,18 @@ +SELECT + orderkey +FROM ( + SELECT + l_orderkey as orderkey + FROM + lineitem + + UNION all + + SELECT + l_orderkey as orderkey + FROM + lineitem +) T + +order by + orderkey; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAll8.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAll8.sql b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAll8.sql new file mode 100644 index 0000000..bf5fbee --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAll8.sql @@ -0,0 +1,24 @@ +SELECT + l_returnflag, + l_linestatus + +FROM ( + SELECT + * + FROM + lineitem + WHERE + l_returnflag = 'R' + + UNION ALL + + SELECT + * + FROM + lineitem + WHERE + l_returnflag = 'R' +) T +GROUP BY + l_returnflag, + l_linestatus; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAll9.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAll9.sql b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAll9.sql new file mode 100644 index 0000000..49ddca8 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAll9.sql @@ -0,0 +1,29 @@ +SELECT + n_nationkey, + n_name + +FROM ( + SELECT + * + FROM + nation + WHERE + n_regionkey = 0 + + + UNION ALL + + SELECT + * + FROM + nation + WHERE + n_regionkey = 0 + +) T +GROUP BY + n_nationkey, + n_name +ORDER BY + n_nationkey desc, + n_name desc; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAllWithDifferentAlias.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAllWithDifferentAlias.sql b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAllWithDifferentAlias.sql new file mode 100644 index 0000000..f41ee32 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAllWithDifferentAlias.sql @@ -0,0 +1,29 @@ +SELECT + col1, + col2 + +FROM ( + SELECT + l_returnflag col1, l_linestatus col2, l_orderkey col3 + FROM + lineitem + WHERE + l_returnflag = 'N' + + UNION ALL + + SELECT + l_returnflag col2, l_linestatus col5, l_orderkey col6 + FROM + lineitem + WHERE + l_returnflag = 'R' +) T + +GROUP BY + col1, + col2 + +ORDER BY + col1, + col2 \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAllWithDifferentAliasAndFunction.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAllWithDifferentAliasAndFunction.sql b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAllWithDifferentAliasAndFunction.sql new file mode 100644 index 0000000..598f399 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAllWithDifferentAliasAndFunction.sql @@ -0,0 +1,29 @@ +SELECT + col1, + col2 + +FROM ( + SELECT + l_returnflag col1, concat(l_linestatus, l_shipdate) col2, l_orderkey col3 + FROM + lineitem + WHERE + l_returnflag = 'N' + + UNION ALL + + SELECT + concat(l_returnflag, l_shipdate) col3, l_linestatus col4, l_orderkey col5 + FROM + lineitem + WHERE + l_returnflag = 'R' +) T + +GROUP BY + col1, + col2 + +ORDER BY + col1, + col2 \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAllWithSameAliasNames.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAllWithSameAliasNames.sql b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAllWithSameAliasNames.sql new file mode 100644 index 0000000..1516697 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionAllWithSameAliasNames.sql @@ -0,0 +1,25 @@ +select + * +from ( + select + l_orderkey + from ( + select + l_orderkey + from + lineitem + ) l1 + + union all + + select + l_orderkey + from ( + select + l_orderkey + from + lineitem + ) l1 +) t1 +order by + l_orderkey; http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionWithDifferentAlias.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionWithDifferentAlias.sql b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionWithDifferentAlias.sql new file mode 100644 index 0000000..9fb0146 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionWithDifferentAlias.sql @@ -0,0 +1,29 @@ +SELECT + col1, + col2 + +FROM ( + SELECT + l_returnflag col1, l_linestatus col2, l_orderkey col3 + FROM + lineitem + WHERE + l_returnflag = 'N' + + UNION + + SELECT + l_returnflag col2, l_linestatus col5, l_orderkey col6 + FROM + lineitem + WHERE + l_returnflag = 'R' +) T + +GROUP BY + col1, + col2 + +ORDER BY + col1, + col2 \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionWithDifferentAliasAndFunction.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionWithDifferentAliasAndFunction.sql b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionWithDifferentAliasAndFunction.sql new file mode 100644 index 0000000..35048cc --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionWithDifferentAliasAndFunction.sql @@ -0,0 +1,29 @@ +SELECT + col1, + col2 + +FROM ( + SELECT + l_returnflag col1, concat(l_linestatus, l_shipdate) col2, l_orderkey col3 + FROM + lineitem + WHERE + l_returnflag = 'N' + + UNION + + SELECT + concat(l_returnflag, l_shipdate) col3, l_linestatus col4, l_orderkey col5 + FROM + lineitem + WHERE + l_returnflag = 'R' +) T + +GROUP BY + col1, + col2 + +ORDER BY + col1, + col2 \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionWithSameAliasNames.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionWithSameAliasNames.sql b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionWithSameAliasNames.sql new file mode 100644 index 0000000..c39940c --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestUnionQuery/testUnionWithSameAliasNames.sql @@ -0,0 +1,25 @@ +select + * +from ( + select + l_orderkey + from ( + select + l_orderkey + from + lineitem + ) l1 + + union + + select + l_orderkey + from ( + select + l_orderkey + from + lineitem + ) l1 +) t1 +order by + l_orderkey; http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestWindowQuery/testComplexOrderBy1.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestWindowQuery/testComplexOrderBy1.sql b/tajo-core-tests/src/test/resources/queries/TestWindowQuery/testComplexOrderBy1.sql new file mode 100644 index 0000000..171815c --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestWindowQuery/testComplexOrderBy1.sql @@ -0,0 +1,5 @@ +select + l_orderkey, + row_number() over (order by l_quantity * (1 - l_discount)) row_num +from + lineitem \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestWindowQuery/testFirstValue1.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestWindowQuery/testFirstValue1.sql b/tajo-core-tests/src/test/resources/queries/TestWindowQuery/testFirstValue1.sql new file mode 100644 index 0000000..e9c9c73 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestWindowQuery/testFirstValue1.sql @@ -0,0 +1,18 @@ +SELECT + l_orderkey, + first_value(l_shipmode) over (PARTITION BY L_ORDERKEY order by l_shipmode ) as shipmode_first, + first_value(l_linenumber) over (PARTITION BY L_ORDERKEY order by l_linenumber ) as linenumber_first, + first_value(l_suppkey_t) over (PARTITION BY L_ORDERKEY order by l_suppkey_t ) as suppkey_first, + first_value(l_shipdate_t) over (PARTITION BY L_ORDERKEY order by l_shipdate_t ) as shipdate_first, + first_value(l_commitdate_t) over (PARTITION BY L_ORDERKEY order by l_commitdate_t ) as commitdate_first, + first_value(l_extendedprice) over (PARTITION BY L_ORDERKEY order by l_extendedprice ) as extendedprice_first, + first_value(l_discount_t) over (PARTITION BY L_ORDERKEY order by l_discount_t ) as discount_first +FROM +( + SELECT + l_orderkey,l_partkey,l_suppkey::INT8 as l_suppkey_t,l_linenumber,l_quantity, + l_extendedprice,l_discount::FLOAT4 as l_discount_t,l_tax,l_returnflag,l_linestatus, + l_shipdate::DATE as l_shipdate_t,l_commitdate::TIMESTAMP as l_commitdate_t,l_receiptdate,l_shipinstruct,l_shipmode,l_comment + FROM + LINEITEM +) xx \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestWindowQuery/testLag1.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestWindowQuery/testLag1.sql b/tajo-core-tests/src/test/resources/queries/TestWindowQuery/testLag1.sql new file mode 100644 index 0000000..1fd1e9e --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestWindowQuery/testLag1.sql @@ -0,0 +1,18 @@ +SELECT + lag(l_shipmode, 1) over (PARTITION BY L_ORDERKEY order by l_shipmode ) as shipmode_lag, + lag(l_linenumber, 1) over (PARTITION BY L_ORDERKEY order by l_shipmode ) as linenumber_lag, + lag(l_suppkey_t, 1) over (PARTITION BY L_ORDERKEY order by l_shipmode ) as suppkey_lag, + lag(l_shipdate_t, 1) over (PARTITION BY L_ORDERKEY order by l_shipmode ) as shipdate_lag, + lag(l_commitdate_t, 1) over (PARTITION BY L_ORDERKEY order by l_shipmode ) as commitdate_lag, + lag(l_extendedprice, 1) over (PARTITION BY L_ORDERKEY order by l_shipmode ) as extendedprice_lag, + lag(l_discount_t, 1) over (PARTITION BY L_ORDERKEY order by l_shipmode ) as discount_lag, + l_orderkey +FROM +( + SELECT + l_orderkey,l_partkey,l_suppkey::INT8 as l_suppkey_t,l_linenumber,l_quantity, + l_extendedprice,l_discount::FLOAT4 as l_discount_t,l_tax,l_returnflag,l_linestatus, + l_shipdate::DATE as l_shipdate_t,l_commitdate::TIMESTAMP as l_commitdate_t,l_receiptdate,l_shipinstruct,l_shipmode,l_comment + FROM + LINEITEM +) xx \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestWindowQuery/testLagWithDefault.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestWindowQuery/testLagWithDefault.sql b/tajo-core-tests/src/test/resources/queries/TestWindowQuery/testLagWithDefault.sql new file mode 100644 index 0000000..3638498 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestWindowQuery/testLagWithDefault.sql @@ -0,0 +1,18 @@ +SELECT + lag(l_shipmode, 1, 'default') over (PARTITION BY L_ORDERKEY order by l_shipmode ) as shipmode_lag, + lag(l_linenumber, 1, 100) over (PARTITION BY L_ORDERKEY order by l_shipmode ) as linenumber_lag, + lag(l_suppkey_t, 1, 1000::int8) over (PARTITION BY L_ORDERKEY order by l_shipmode ) as suppkey_lag, + lag(l_shipdate_t, 1, '15-01-01'::date) over (PARTITION BY L_ORDERKEY order by l_shipmode ) as shipdate_lag, + lag(l_commitdate_t, 1, '15-01-01 12:00:00'::timestamp) over (PARTITION BY L_ORDERKEY order by l_shipmode ) as commitdate_lag, + lag(l_extendedprice, 1, 1.234::float8) over (PARTITION BY L_ORDERKEY order by l_shipmode ) as extendedprice_lag, + lag(l_discount_t, 1, 0.11::float4) over (PARTITION BY L_ORDERKEY order by l_shipmode ) as discount_lag, + l_orderkey +FROM +( + SELECT + l_orderkey,l_partkey,l_suppkey::INT8 as l_suppkey_t,l_linenumber,l_quantity, + l_extendedprice,l_discount::FLOAT4 as l_discount_t,l_tax,l_returnflag,l_linestatus, + l_shipdate::DATE as l_shipdate_t,l_commitdate::TIMESTAMP as l_commitdate_t,l_receiptdate,l_shipinstruct,l_shipmode,l_comment + FROM + LINEITEM +) xx \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestWindowQuery/testLagWithNoArgs.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestWindowQuery/testLagWithNoArgs.sql b/tajo-core-tests/src/test/resources/queries/TestWindowQuery/testLagWithNoArgs.sql new file mode 100644 index 0000000..1d551d6 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestWindowQuery/testLagWithNoArgs.sql @@ -0,0 +1,18 @@ +SELECT + lag(l_shipmode) over (PARTITION BY L_ORDERKEY order by l_shipmode ) as shipmode_lag, + lag(l_linenumber) over (PARTITION BY L_ORDERKEY order by l_shipmode ) as linenumber_lag, + lag(l_suppkey_t) over (PARTITION BY L_ORDERKEY order by l_shipmode ) as suppkey_lag, + lag(l_shipdate_t) over (PARTITION BY L_ORDERKEY order by l_shipmode ) as shipdate_lag, + lag(l_commitdate_t) over (PARTITION BY L_ORDERKEY order by l_shipmode ) as commitdate_lag, + lag(l_extendedprice) over (PARTITION BY L_ORDERKEY order by l_shipmode ) as extendedprice_lag, + lag(l_discount_t) over (PARTITION BY L_ORDERKEY order by l_shipmode ) as discount_lag, + l_orderkey +FROM +( + SELECT + l_orderkey,l_partkey,l_suppkey::INT8 as l_suppkey_t,l_linenumber,l_quantity, + l_extendedprice,l_discount::FLOAT4 as l_discount_t,l_tax,l_returnflag,l_linestatus, + l_shipdate::DATE as l_shipdate_t,l_commitdate::TIMESTAMP as l_commitdate_t,l_receiptdate,l_shipinstruct,l_shipmode,l_comment + FROM + LINEITEM +) xx \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestWindowQuery/testLastValue1.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestWindowQuery/testLastValue1.sql b/tajo-core-tests/src/test/resources/queries/TestWindowQuery/testLastValue1.sql new file mode 100644 index 0000000..150b912 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestWindowQuery/testLastValue1.sql @@ -0,0 +1,18 @@ +SELECT + l_orderkey, + last_value(l_shipmode) over (PARTITION BY L_ORDERKEY order by l_shipmode ) as shipmode_last, + last_value(l_linenumber) over (PARTITION BY L_ORDERKEY order by l_linenumber ) as linenumber_last, + last_value(l_suppkey_t) over (PARTITION BY L_ORDERKEY order by l_suppkey_t ) as suppkey_last, + last_value(l_shipdate_t) over (PARTITION BY L_ORDERKEY order by l_shipdate_t ) as shipdate_last, + last_value(l_commitdate_t) over (PARTITION BY L_ORDERKEY order by l_commitdate_t ) as commitdate_last, + last_value(l_extendedprice) over (PARTITION BY L_ORDERKEY order by l_extendedprice ) as extendedprice_last, + last_value(l_discount_t) over (PARTITION BY L_ORDERKEY order by l_discount_t ) as discount_last +FROM +( + SELECT + l_orderkey,l_partkey,l_suppkey::INT8 as l_suppkey_t,l_linenumber,l_quantity, + l_extendedprice,l_discount::FLOAT4 as l_discount_t,l_tax,l_returnflag,l_linestatus, + l_shipdate::DATE as l_shipdate_t,l_commitdate::TIMESTAMP as l_commitdate_t,l_receiptdate,l_shipinstruct,l_shipmode,l_comment + FROM + LINEITEM +) xx \ No newline at end of file
