http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestJoinOnPartitionedTables/testFilterPushDownPartitionColumnCaseWhen.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinOnPartitionedTables/testFilterPushDownPartitionColumnCaseWhen.sql b/tajo-core-tests/src/test/resources/queries/TestJoinOnPartitionedTables/testFilterPushDownPartitionColumnCaseWhen.sql new file mode 100644 index 0000000..f5f426b --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinOnPartitionedTables/testFilterPushDownPartitionColumnCaseWhen.sql @@ -0,0 +1,6 @@ +select c_custkey, c_nationkey, c_name, o_custkey, (case when a.c_nationkey > 3 then 4 else 3 end) +from customer_parts a +inner join orders b +on a.c_custkey = b.o_custkey +where a.c_custkey = (case when a.c_name like 'Customer%' and a.c_nationkey > 3 then 4 else 3 end) +order by c_custkey \ No newline at end of file
http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestJoinOnPartitionedTables/testMultiplePartitionedBroadcastDataFileWithZeroLength.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinOnPartitionedTables/testMultiplePartitionedBroadcastDataFileWithZeroLength.sql b/tajo-core-tests/src/test/resources/queries/TestJoinOnPartitionedTables/testMultiplePartitionedBroadcastDataFileWithZeroLength.sql new file mode 100644 index 0000000..8e35e26 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinOnPartitionedTables/testMultiplePartitionedBroadcastDataFileWithZeroLength.sql @@ -0,0 +1,3 @@ +select * from customer a + left outer join nation_partitioned b on a.c_nationkey = b.n_nationkey + where b.n_nationkey is not 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/TestJoinOnPartitionedTables/testMultiplePartitionedBroadcastDataFileWithZeroLength2.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinOnPartitionedTables/testMultiplePartitionedBroadcastDataFileWithZeroLength2.sql b/tajo-core-tests/src/test/resources/queries/TestJoinOnPartitionedTables/testMultiplePartitionedBroadcastDataFileWithZeroLength2.sql new file mode 100644 index 0000000..58a4ad2 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinOnPartitionedTables/testMultiplePartitionedBroadcastDataFileWithZeroLength2.sql @@ -0,0 +1,5 @@ +select b.o_orderkey, b.o_orderdate, b.o_custkey, a.c_custkey, a.c_name, c.n_nationkey, c.n_name +from customer a + left outer join orders b on a.c_custkey = b.o_custkey + left outer join nation_partitioned c on a.c_nationkey = c.n_nationkey + where c.n_nationkey is not 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/TestJoinOnPartitionedTables/testNoProjectionJoinQual.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinOnPartitionedTables/testNoProjectionJoinQual.sql b/tajo-core-tests/src/test/resources/queries/TestJoinOnPartitionedTables/testNoProjectionJoinQual.sql new file mode 100644 index 0000000..1db9c1c --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinOnPartitionedTables/testNoProjectionJoinQual.sql @@ -0,0 +1 @@ +select count(*) from customer_parts t1, customer_parts t2 where t1.c_nationkey = t2.c_nationkey; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestJoinOnPartitionedTables/testPartialFilterPushDown.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinOnPartitionedTables/testPartialFilterPushDown.sql b/tajo-core-tests/src/test/resources/queries/TestJoinOnPartitionedTables/testPartialFilterPushDown.sql new file mode 100644 index 0000000..fc9063a --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinOnPartitionedTables/testPartialFilterPushDown.sql @@ -0,0 +1,9 @@ +select + upper(c_name) as c_name, count(1) +from + customer_parts +where + c_name is not null and + c_nationkey = 1 +group by + 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/TestJoinOnPartitionedTables/testPartialFilterPushDownOuterJoin.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinOnPartitionedTables/testPartialFilterPushDownOuterJoin.sql b/tajo-core-tests/src/test/resources/queries/TestJoinOnPartitionedTables/testPartialFilterPushDownOuterJoin.sql new file mode 100644 index 0000000..bbb2c45 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinOnPartitionedTables/testPartialFilterPushDownOuterJoin.sql @@ -0,0 +1,4 @@ +select a.n_nationkey, a.n_name, b.c_custkey, b.c_nationkey, b.c_name +from nation a +left outer join customer_parts b on a.n_nationkey = b.c_custkey +and b.c_nationkey = 1 http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestJoinOnPartitionedTables/testPartialFilterPushDownOuterJoin2.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinOnPartitionedTables/testPartialFilterPushDownOuterJoin2.sql b/tajo-core-tests/src/test/resources/queries/TestJoinOnPartitionedTables/testPartialFilterPushDownOuterJoin2.sql new file mode 100644 index 0000000..b30b2cb --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinOnPartitionedTables/testPartialFilterPushDownOuterJoin2.sql @@ -0,0 +1,5 @@ +-- In the case of no partition directory +select a.n_nationkey, a.n_name, b.c_custkey, b.c_nationkey, b.c_name +from nation a +left outer join customer_parts b on a.n_nationkey = b.c_custkey +and b.c_nationkey = 100 http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestJoinOnPartitionedTables/testPartitionTableJoinSmallTable.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinOnPartitionedTables/testPartitionTableJoinSmallTable.sql b/tajo-core-tests/src/test/resources/queries/TestJoinOnPartitionedTables/testPartitionTableJoinSmallTable.sql new file mode 100644 index 0000000..27e2066 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinOnPartitionedTables/testPartitionTableJoinSmallTable.sql @@ -0,0 +1,11 @@ +select + c_custkey, + c_name, + c_nationkey, + n_nationkey +from + customer_parts, nation +where + c_nationkey = n_nationkey +order by + c_custkey; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestJoinQuery/create_customer_large_ddl.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/create_customer_large_ddl.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/create_customer_large_ddl.sql new file mode 100644 index 0000000..a9122f3 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/create_customer_large_ddl.sql @@ -0,0 +1,7 @@ +-- Large customer Table +-- It is used for broadcast join + +create external table if not exists customer_large ( + c_custkey INT4, c_name TEXT, c_address TEXT, c_nationkey INT4, + c_phone TEXT, c_acctbal FLOAT8, c_mktsegment TEXT, c_comment TEXT) +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/TestJoinQuery/create_lineitem_large_ddl.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/create_lineitem_large_ddl.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/create_lineitem_large_ddl.sql new file mode 100644 index 0000000..f8d574f --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/create_lineitem_large_ddl.sql @@ -0,0 +1,7 @@ +-- Large lineitem Table +-- It is used for broadcast join + +create external table if not exists lineitem_large ( l_orderkey INT4, l_partkey INT4, l_suppkey INT4, l_linenumber INT4, l_quantity FLOAT8, + l_extendedprice FLOAT8, l_discount FLOAT8, l_tax FLOAT8, l_returnflag TEXT, l_linestatus TEXT, l_shipdate TEXT, l_commitdate TEXT, + l_receiptdate TEXT, l_shipinstruct TEXT, l_shipmode TEXT, l_comment TEXT) +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/TestJoinQuery/create_orders_large_ddl.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/create_orders_large_ddl.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/create_orders_large_ddl.sql new file mode 100644 index 0000000..8f99e5e --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/create_orders_large_ddl.sql @@ -0,0 +1,7 @@ +-- Large Orders Table +-- It is used for broadcast join + +create external table if not exists orders_large ( o_orderkey INT4, o_custkey INT4, + o_orderstatus TEXT, o_totalprice FLOAT8, o_orderdate TEXT, + o_orderpriority TEXT, o_clerk TEXT, o_shippriority INT4, o_comment TEXT) +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/TestJoinQuery/customer_partition_ddl.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/customer_partition_ddl.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/customer_partition_ddl.sql new file mode 100644 index 0000000..7d07474 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/customer_partition_ddl.sql @@ -0,0 +1,9 @@ +CREATE TABLE customer_broad_parts ( + c_nationkey INT4, + c_name TEXT, + c_address TEXT, + c_phone TEXT, + c_acctbal FLOAT8, + c_mktsegment TEXT, + c_comment TEXT +) PARTITION BY COLUMN (c_custkey INT4); \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestJoinQuery/insert_into_customer.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/insert_into_customer.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/insert_into_customer.sql new file mode 100644 index 0000000..29152b6 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/insert_into_customer.sql @@ -0,0 +1,11 @@ +INSERT OVERWRITE INTO customer_parts + SELECT + c_custkey, + c_name, + c_address, + c_phone, + c_acctbal, + c_mktsegment, + c_comment, + c_nationkey + FROM customer; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestJoinQuery/insert_into_customer_partition.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/insert_into_customer_partition.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/insert_into_customer_partition.sql new file mode 100644 index 0000000..3a500a1 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/insert_into_customer_partition.sql @@ -0,0 +1,11 @@ +INSERT OVERWRITE INTO customer_broad_parts + SELECT + c_nationkey, + c_name, + c_address, + c_phone, + c_acctbal, + c_mktsegment, + c_comment, + c_custkey + FROM customer; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestJoinQuery/nation_multifile_ddl.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/nation_multifile_ddl.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/nation_multifile_ddl.sql new file mode 100644 index 0000000..c3f595a --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/nation_multifile_ddl.sql @@ -0,0 +1,5 @@ +create table nation_multifile ( + n_nationkey int, + n_name text, + n_regionkey int, + n_comment text); \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestJoinQuery/oj_table1_ddl.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/oj_table1_ddl.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/oj_table1_ddl.sql new file mode 100644 index 0000000..454581a --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/oj_table1_ddl.sql @@ -0,0 +1,3 @@ +create external table testOuterJoinAndCaseWhen1 (id int, name text, score float, type 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/TestJoinQuery/oj_table2_ddl.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/oj_table2_ddl.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/oj_table2_ddl.sql new file mode 100644 index 0000000..ac1e1f6 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/oj_table2_ddl.sql @@ -0,0 +1,3 @@ +create external table testOuterJoinAndCaseWhen2 (id int, name text, score float, type 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/TestJoinQuery/orders_multifile_ddl.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/orders_multifile_ddl.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/orders_multifile_ddl.sql new file mode 100644 index 0000000..64f70f2 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/orders_multifile_ddl.sql @@ -0,0 +1,5 @@ +create table orders_multifile ( + o_orderkey int, + o_custkey int, + o_orderstatus text +); \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestJoinQuery/partitioned_customer_ddl.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/partitioned_customer_ddl.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/partitioned_customer_ddl.sql new file mode 100644 index 0000000..1d2c3fe --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/partitioned_customer_ddl.sql @@ -0,0 +1,19 @@ +CREATE TABLE if not exists customer_parts ( + c_custkey INT4, + c_name TEXT, + c_address TEXT, + c_phone TEXT, + c_acctbal FLOAT8, + c_mktsegment TEXT, + c_comment TEXT +) PARTITION BY COLUMN (c_nationkey INT4) as + SELECT + c_custkey, + c_name, + c_address, + c_phone, + c_acctbal, + c_mktsegment, + c_comment, + c_nationkey + FROM customer; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestJoinQuery/table1_int4_ddl.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/table1_int4_ddl.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/table1_int4_ddl.sql new file mode 100644 index 0000000..c991f71 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/table1_int4_ddl.sql @@ -0,0 +1,3 @@ +create external table ${0} (id int, name text, score float, type 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/TestJoinQuery/table1_int8_ddl.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/table1_int8_ddl.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/table1_int8_ddl.sql new file mode 100644 index 0000000..8f0b8de --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/table1_int8_ddl.sql @@ -0,0 +1,3 @@ +create external table ${0} (id bigint, name text, score float, type 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/TestJoinQuery/testComplexJoinCondition1.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testComplexJoinCondition1.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testComplexJoinCondition1.sql new file mode 100644 index 0000000..b61ad38 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testComplexJoinCondition1.sql @@ -0,0 +1,6 @@ +select + n1.n_nationkey, + n1.n_name, + n2.n_name +from nation n1 join nation n2 on n1.n_name = upper(n2.n_name) +order by n1.n_nationkey; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testComplexJoinCondition2.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testComplexJoinCondition2.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testComplexJoinCondition2.sql new file mode 100644 index 0000000..33effbb --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testComplexJoinCondition2.sql @@ -0,0 +1,6 @@ +select + n1.n_nationkey, + n1.n_name, + upper(n2.n_name) as name +from nation n1 join nation n2 on n1.n_name = upper(n2.n_name) +order by n1.n_nationkey; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testComplexJoinCondition3.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testComplexJoinCondition3.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testComplexJoinCondition3.sql new file mode 100644 index 0000000..5674269 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testComplexJoinCondition3.sql @@ -0,0 +1,6 @@ +select + n1.n_nationkey, + n1.n_name, + n2.n_name +from nation n1 join nation n2 on lower(n1.n_name) = lower(n2.n_name) +order by n1.n_nationkey; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testComplexJoinCondition4.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testComplexJoinCondition4.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testComplexJoinCondition4.sql new file mode 100644 index 0000000..45d8adf --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testComplexJoinCondition4.sql @@ -0,0 +1,6 @@ +select + n1.n_nationkey, + substr(n1.n_name, 1, 4) name1, + substr(n2.n_name, 1, 4) name2 +from nation n1 join nation n2 on substr(n1.n_name, 1, 4) = substr(n2.n_name, 1, 4) +order by n1.n_nationkey; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testComplexJoinCondition5.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testComplexJoinCondition5.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testComplexJoinCondition5.sql new file mode 100644 index 0000000..f604bc7 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testComplexJoinCondition5.sql @@ -0,0 +1,6 @@ +select + n1.n_nationkey, + substr(n1.n_name, 1, 4) name1, + substr(n2.n_name, 1, 4) name2 +from nation n1 join (select * from nation) n2 on substr(n1.n_name, 1, 4) = substr(n2.n_name, 1, 4) +order by n1.n_nationkey; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testComplexJoinCondition6.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testComplexJoinCondition6.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testComplexJoinCondition6.sql new file mode 100644 index 0000000..0ef3143 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testComplexJoinCondition6.sql @@ -0,0 +1,6 @@ +select + n1.n_nationkey, + substr(n1.n_name, 1, 4) name1, + substr(n2.n_name, 1, 4) name2 +from nation n1 join (select * from nation union all select * from nation) n2 on substr(n1.n_name, 1, 4) = substr(n2.n_name, 1, 4) +order by n1.n_nationkey; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testComplexJoinCondition7.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testComplexJoinCondition7.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testComplexJoinCondition7.sql new file mode 100644 index 0000000..4931c83 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testComplexJoinCondition7.sql @@ -0,0 +1,6 @@ +select + n1.n_nationkey, + n1.n_name, + n2.n_name +from nation n1 join (select * from nation union all select * from nation) n2 on substr(n1.n_name, 1, 4) = substr(n2.n_name, 1, 4) +order by n1.n_nationkey,n2.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/TestJoinQuery/testComplexJoinsWithCaseWhen.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testComplexJoinsWithCaseWhen.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testComplexJoinsWithCaseWhen.sql new file mode 100644 index 0000000..b2c49a4 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testComplexJoinsWithCaseWhen.sql @@ -0,0 +1,11 @@ +select + r_name, + case when + s_name is null then 'N/O' + else + s_name + end as s1 +from + region inner join nation on n_regionkey = r_regionkey + left outer join supplier on s_nationkey = n_nationkey +order by r_name, s1; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testComplexJoinsWithCaseWhen2.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testComplexJoinsWithCaseWhen2.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testComplexJoinsWithCaseWhen2.sql new file mode 100644 index 0000000..5e2918f --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testComplexJoinsWithCaseWhen2.sql @@ -0,0 +1,9 @@ +select + r_name, + case when s_name is null then 'N/O' + else s_name end as s1 +from region inner join ( + select * from nation + left outer join supplier on s_nationkey = n_nationkey +) t on n_regionkey = r_regionkey +order by r_name, s1; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testCrossJoinWithEmptyTable1.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testCrossJoinWithEmptyTable1.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testCrossJoinWithEmptyTable1.sql new file mode 100644 index 0000000..1e0cb56 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testCrossJoinWithEmptyTable1.sql @@ -0,0 +1,8 @@ +select + c_custkey, + empty_orders.o_orderkey +from + customer, empty_orders +where c_custkey = o_orderkey +order by + c_custkey, o_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/TestJoinQuery/testCrossJoinWithThetaJoinConditionInWhere.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testCrossJoinWithThetaJoinConditionInWhere.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testCrossJoinWithThetaJoinConditionInWhere.sql new file mode 100644 index 0000000..4e20e16 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testCrossJoinWithThetaJoinConditionInWhere.sql @@ -0,0 +1,2 @@ +select a.r_name as a_name, b.r_name as b_name from region a, region b +where a_name < b_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/TestJoinQuery/testDifferentTypesJoinCondition.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testDifferentTypesJoinCondition.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testDifferentTypesJoinCondition.sql new file mode 100644 index 0000000..6bd0a4c --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testDifferentTypesJoinCondition.sql @@ -0,0 +1 @@ +select * from table20 t3 join table21 t4 on t3.id = t4.id; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testFullOuterJoin1.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testFullOuterJoin1.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testFullOuterJoin1.sql new file mode 100644 index 0000000..ccaa5fb --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testFullOuterJoin1.sql @@ -0,0 +1,8 @@ +select + c_custkey, + orders.o_orderkey +from + orders full outer join customer on c_custkey = o_orderkey +order by + c_custkey, + orders.o_orderkey; http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testFullOuterJoinWithEmptyTable1.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testFullOuterJoinWithEmptyTable1.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testFullOuterJoinWithEmptyTable1.sql new file mode 100644 index 0000000..b9e3efd --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testFullOuterJoinWithEmptyTable1.sql @@ -0,0 +1,8 @@ +select + c_custkey, + empty_orders.o_orderkey +from + empty_orders full outer join customer on c_custkey = o_orderkey +order by + c_custkey, + empty_orders.o_orderkey; http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testInnerJoinAndCaseWhen.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testInnerJoinAndCaseWhen.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testInnerJoinAndCaseWhen.sql new file mode 100644 index 0000000..d058aba --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testInnerJoinAndCaseWhen.sql @@ -0,0 +1,18 @@ +select + r_regionkey, + n_regionkey, + case + when r_regionkey = 1 then 'one' + when r_regionkey = 2 then 'two' + when r_regionkey = 3 then 'three' + when r_regionkey = 4 then 'four' + else 'zero' + end as cond +from + region, + nation +where + r_regionkey = n_regionkey +order by + r_regionkey, + n_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/TestJoinQuery/testInnerJoinWithEmptyTable.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testInnerJoinWithEmptyTable.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testInnerJoinWithEmptyTable.sql new file mode 100644 index 0000000..1e0cb56 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testInnerJoinWithEmptyTable.sql @@ -0,0 +1,8 @@ +select + c_custkey, + empty_orders.o_orderkey +from + customer, empty_orders +where c_custkey = o_orderkey +order by + c_custkey, o_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/TestJoinQuery/testInnerJoinWithThetaJoinConditionInWhere.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testInnerJoinWithThetaJoinConditionInWhere.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testInnerJoinWithThetaJoinConditionInWhere.sql new file mode 100644 index 0000000..90f4822 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testInnerJoinWithThetaJoinConditionInWhere.sql @@ -0,0 +1,3 @@ +select a.r_regionkey, a.r_name, b.r_name from region a join region b +on a.r_regionkey = b.r_regionkey +where a.r_name <= b.r_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/TestJoinQuery/testJoinAsterisk.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testJoinAsterisk.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testJoinAsterisk.sql new file mode 100644 index 0000000..e3de03c --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testJoinAsterisk.sql @@ -0,0 +1,3 @@ +select * +from nation b +join customer a on b.n_nationkey = a.c_nationkey http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testJoinCoReferredEvals1.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testJoinCoReferredEvals1.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testJoinCoReferredEvals1.sql new file mode 100644 index 0000000..3a123bb --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testJoinCoReferredEvals1.sql @@ -0,0 +1,11 @@ +select + r_regionkey, + n_regionkey, + (r_regionkey + n_regionkey) as plus +from + region, + nation +where + r_regionkey = n_regionkey +order by + r_regionkey, n_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/TestJoinQuery/testJoinCoReferredEvalsFilterPushdown.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testJoinCoReferredEvalsFilterPushdown.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testJoinCoReferredEvalsFilterPushdown.sql new file mode 100644 index 0000000..680311d --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testJoinCoReferredEvalsFilterPushdown.sql @@ -0,0 +1,13 @@ +select * from ( +select + r_regionkey, + n_regionkey, + (r_regionkey + n_regionkey) as plus +from + region, + nation +where + r_regionkey = n_regionkey +order by + r_regionkey, n_regionkey +) where plus > 10 \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testJoinCoReferredEvalsWithSameExprs1.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testJoinCoReferredEvalsWithSameExprs1.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testJoinCoReferredEvalsWithSameExprs1.sql new file mode 100644 index 0000000..3638393 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testJoinCoReferredEvalsWithSameExprs1.sql @@ -0,0 +1,14 @@ +select + n_regionkey + n_nationkey as v1, + n_regionkey, + r_regionkey, + (r_regionkey + n_regionkey) as plus1, + (r_regionkey + n_regionkey) as plus2, + ((r_regionkey + n_regionkey) / 2) as result +from + region, + nation +where + r_regionkey = n_regionkey and r_regionkey > 0 +order by + n_regionkey + n_nationkey, n_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/TestJoinQuery/testJoinCoReferredEvalsWithSameExprs2.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testJoinCoReferredEvalsWithSameExprs2.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testJoinCoReferredEvalsWithSameExprs2.sql new file mode 100644 index 0000000..a5b75d4 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testJoinCoReferredEvalsWithSameExprs2.sql @@ -0,0 +1,22 @@ +select + n_regionkey + n_nationkey as v1, + n_regionkey, + r_regionkey, + (r_regionkey + n_regionkey) as plus1, + (r_regionkey + n_regionkey) as plus2, + ((r_regionkey + n_regionkey) / 2) as result, + sum(r_regionkey + n_regionkey) as total +from + region, + nation +where + r_regionkey = n_regionkey and r_regionkey > 0 +group by + n_regionkey + n_nationkey, + n_regionkey, + r_regionkey, + (r_regionkey + n_regionkey), + ((r_regionkey + n_regionkey) / 2) + +order by + n_regionkey + n_nationkey, n_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/TestJoinQuery/testJoinFilterOfRowPreservedTable1.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testJoinFilterOfRowPreservedTable1.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testJoinFilterOfRowPreservedTable1.sql new file mode 100644 index 0000000..50ea371 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testJoinFilterOfRowPreservedTable1.sql @@ -0,0 +1,8 @@ +select + r_name, + r_regionkey, + n_name, + n_regionkey +from + region left outer join nation on n_regionkey = r_regionkey and r_name in ('AMERICA', 'ASIA') +order by r_name,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/TestJoinQuery/testJoinOnMultipleDatabases.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testJoinOnMultipleDatabases.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testJoinOnMultipleDatabases.sql new file mode 100644 index 0000000..eb5a2c5 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testJoinOnMultipleDatabases.sql @@ -0,0 +1,25 @@ +select + s_acctbal, + s_name, + n_name, + JOINS.part_.p_partkey, + p_mfgr, + JOINS.supplier_.s_address, + JOINS.supplier_.s_phone, + s_comment +from + JOINS.part_, + JOINS.supplier_, + partsupp, + nation, + region +where + p_partkey = ps_partkey + and s_suppkey = ps_suppkey + and s_nationkey = n_nationkey + and n_regionkey = r_regionkey +order by + s_acctbal, + s_name, + n_name, + p_partkey; http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testJoinOnMultipleDatabasesWithJson.json ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testJoinOnMultipleDatabasesWithJson.json b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testJoinOnMultipleDatabasesWithJson.json new file mode 100644 index 0000000..874305d --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testJoinOnMultipleDatabasesWithJson.json @@ -0,0 +1,210 @@ +/* + select + s_acctbal, + s_name, + n_name, + JOINS.part_.p_partkey, + p_mfgr, + JOINS.supplier_.s_address, + JOINS.supplier_.s_phone, + s_comment + from + JOINS.part_, + JOINS.supplier_, + partsupp, + nation, + region + where + p_partkey = ps_partkey + and s_suppkey = ps_suppkey + and s_nationkey = n_nationkey + and n_regionkey = r_regionkey + order by + s_acctbal, + s_name, + n_name, + p_partkey; + */ +{ + "IsDistinct": false, + "Projections": [ + { + "Expr": { + "ColumnName": "s_acctbal", + "OpType": "Column" + }, + "OpType": "Target" + }, + { + "Expr": { + "ColumnName": "s_name", + "OpType": "Column" + }, + "OpType": "Target" + }, + { + "Expr": { + "ColumnName": "n_name", + "OpType": "Column" + }, + "OpType": "Target" + }, + { + "Expr": { + "Qualifier": "joins.part_", + "ColumnName": "p_partkey", + "OpType": "Column" + }, + "OpType": "Target" + }, + { + "Expr": { + "ColumnName": "p_mfgr", + "OpType": "Column" + }, + "OpType": "Target" + }, + { + "Expr": { + "Qualifier": "joins.supplier_", + "ColumnName": "s_address", + "OpType": "Column" + }, + "OpType": "Target" + }, + { + "Expr": { + "Qualifier": "joins.supplier_", + "ColumnName": "s_phone", + "OpType": "Column" + }, + "OpType": "Target" + }, + { + "Expr": { + "ColumnName": "s_comment", + "OpType": "Column" + }, + "OpType": "Target" + } + ], + "Expr": { + "SortSpecs": [ + { + "SortKey": { + "ColumnName": "s_acctbal", + "OpType": "Column" + }, + "IsAsc": true, + "IsNullFirst": false + }, + { + "SortKey": { + "ColumnName": "s_name", + "OpType": "Column" + }, + "IsAsc": true, + "IsNullFirst": false + }, + { + "SortKey": { + "ColumnName": "n_name", + "OpType": "Column" + }, + "IsAsc": true, + "IsNullFirst": false + }, + { + "SortKey": { + "ColumnName": "p_partkey", + "OpType": "Column" + }, + "IsAsc": true, + "IsNullFirst": false + } + ], + "Expr": { + "SelectCondition": { + "LeftExpr": { + "LeftExpr": { + "ColumnName": "p_partkey", + "OpType": "Column" + }, + "RightExpr": { + "ColumnName": "ps_partkey", + "OpType": "Column" + }, + "OpType": "Equals" + }, + "RightExpr": { + "LeftExpr": { + "LeftExpr": { + "ColumnName": "s_suppkey", + "OpType": "Column" + }, + "RightExpr": { + "ColumnName": "ps_suppkey", + "OpType": "Column" + }, + "OpType": "Equals" + }, + "RightExpr": { + "LeftExpr": { + "LeftExpr": { + "ColumnName": "s_nationkey", + "OpType": "Column" + }, + "RightExpr": { + "ColumnName": "n_nationkey", + "OpType": "Column" + }, + "OpType": "Equals" + }, + "RightExpr": { + "LeftExpr": { + "ColumnName": "n_regionkey", + "OpType": "Column" + }, + "RightExpr": { + "ColumnName": "r_regionkey", + "OpType": "Column" + }, + "OpType": "Equals" + }, + "OpType": "And" + }, + "OpType": "And" + }, + "OpType": "And" + }, + "Expr": { + "Relations": [ + { + "TableName": "joins.part_", + "OpType": "Relation" + }, + { + "TableName": "joins.supplier_", + "OpType": "Relation" + }, + { + "TableName": "partsupp", + "OpType": "Relation" + }, + { + "TableName": "nation", + "OpType": "Relation" + }, + { + "TableName": "region", + "OpType": "Relation" + } + ], + "OpType": "RelationList" + }, + "OpType": "Filter" + }, + "OpType": "Sort" + }, + "OpType": "Projection" +} http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testJoinWithJson.json ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testJoinWithJson.json b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testJoinWithJson.json new file mode 100644 index 0000000..f6b34cd --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testJoinWithJson.json @@ -0,0 +1,92 @@ +// select length(r_comment) as len, *, c_custkey*10 from customer, region order by len,r_regionkey,r_name +{ + "IsDistinct": false, + "Projections": [ + { + "AliasName": "len", + "Expr": { + "Signature": "length", + "FuncParams": [ + { + "ColumnName": "r_comment", + "OpType": "Column" + } + ], + "OpType": "Function" + }, + "OpType": "Target" + }, + { + "Expr": { + "OpType": "Asterisk" + }, + "OpType": "Target" + }, + { + "Expr": { + "LeftExpr": { + "ColumnName": "c_custkey", + "OpType": "Column" + }, + "RightExpr": { + "Value": "10", + "ValueType": "Unsigned_Integer", + "OpType": "Literal" + }, + "OpType": "Multiply" + }, + "OpType": "Target" + } + ], + "Expr": { + "SortSpecs": [ + { + "SortKey": { + "ColumnName": "len", + "OpType": "Column" + }, + "IsAsc": true, + "IsNullFirst": false + }, + { + "SortKey": { + "ColumnName": "r_regionkey", + "OpType": "Column" + }, + "IsAsc": true, + "IsNullFirst": false + }, + { + "SortKey": { + "ColumnName": "r_name", + "OpType": "Column" + }, + "IsAsc": true, + "IsNullFirst": false + }, + { + "SortKey": { + "ColumnName": "c_custkey", + "OpType": "Column" + }, + "IsAsc": true, + "IsNullFirst": false + } + ], + "Expr": { + "Relations": [ + { + "TableName": "customer", + "OpType": "Relation" + }, + { + "TableName": "region", + "OpType": "Relation" + } + ], + "OpType": "RelationList" + }, + "OpType": "Sort" + }, + "OpType": "Projection" +} http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testJoinWithJson2.json ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testJoinWithJson2.json b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testJoinWithJson2.json new file mode 100644 index 0000000..353636a --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testJoinWithJson2.json @@ -0,0 +1,219 @@ +/* + select t.n_nationkey, t.n_name, t.n_regionkey, t.n_comment, ps.ps_availqty, s.s_suppkey + from ( + select n_nationkey, n_name, n_regionkey, n_comment + from nation n + join region r on (n.n_regionkey = r.r_regionkey) + ) t + join supplier s on (s.s_nationkey = t.n_nationkey) + join partsupp ps on (s.s_suppkey = ps.ps_suppkey) + where t.n_name in ('ARGENTINA','ETHIOPIA', 'MOROCCO'); + */ +{ + "IsDistinct": false, + "Projections": [ + { + "Expr": { + "Qualifier": "t", + "ColumnName": "n_nationkey", + "OpType": "Column" + }, + "OpType": "Target" + }, + { + "Expr": { + "Qualifier": "t", + "ColumnName": "n_name", + "OpType": "Column" + }, + "OpType": "Target" + }, + { + "Expr": { + "Qualifier": "t", + "ColumnName": "n_regionkey", + "OpType": "Column" + }, + "OpType": "Target" + }, + { + "Expr": { + "Qualifier": "t", + "ColumnName": "n_comment", + "OpType": "Column" + }, + "OpType": "Target" + }, + { + "Expr": { + "Qualifier": "ps", + "ColumnName": "ps_availqty", + "OpType": "Column" + }, + "OpType": "Target" + }, + { + "Expr": { + "Qualifier": "s", + "ColumnName": "s_suppkey", + "OpType": "Column" + }, + "OpType": "Target" + } + ], + "Expr": { + "SelectCondition": { + "IsNot": false, + "LeftExpr": { + "Qualifier": "t", + "ColumnName": "n_name", + "OpType": "Column" + }, + "RightExpr": { + "Values": [ + { + "Value": "ARGENTINA", + "ValueType": "String", + "OpType": "Literal" + }, + { + "Value": "ETHIOPIA", + "ValueType": "String", + "OpType": "Literal" + }, + { + "Value": "MOROCCO", + "ValueType": "String", + "OpType": "Literal" + } + ], + "OpType": "ValueList" + }, + "OpType": "InPredicate" + }, + "Expr": { + "Relations": [ + { + "JoinType": "INNER", + "JoinCondition": { + "LeftExpr": { + "Qualifier": "s", + "ColumnName": "s_suppkey", + "OpType": "Column" + }, + "RightExpr": { + "Qualifier": "ps", + "ColumnName": "ps_suppkey", + "OpType": "Column" + }, + "OpType": "Equals" + }, + "IsNatural": false, + "LeftExpr": { + "JoinType": "INNER", + "JoinCondition": { + "LeftExpr": { + "Qualifier": "s", + "ColumnName": "s_nationkey", + "OpType": "Column" + }, + "RightExpr": { + "Qualifier": "t", + "ColumnName": "n_nationkey", + "OpType": "Column" + }, + "OpType": "Equals" + }, + "IsNatural": false, + "LeftExpr": { + "SubPlan": { + "IsDistinct": false, + "Projections": [ + { + "Expr": { + "ColumnName": "n_nationkey", + "OpType": "Column" + }, + "OpType": "Target" + }, + { + "Expr": { + "ColumnName": "n_name", + "OpType": "Column" + }, + "OpType": "Target" + }, + { + "Expr": { + "ColumnName": "n_regionkey", + "OpType": "Column" + }, + "OpType": "Target" + }, + { + "Expr": { + "ColumnName": "n_comment", + "OpType": "Column" + }, + "OpType": "Target" + } + ], + "Expr": { + "Relations": [ + { + "JoinType": "INNER", + "JoinCondition": { + "LeftExpr": { + "Qualifier": "n", + "ColumnName": "n_regionkey", + "OpType": "Column" + }, + "RightExpr": { + "Qualifier": "r", + "ColumnName": "r_regionkey", + "OpType": "Column" + }, + "OpType": "Equals" + }, + "IsNatural": false, + "LeftExpr": { + "TableName": "nation", + "TableAlias": "n", + "OpType": "Relation" + }, + "RightExpr": { + "TableName": "region", + "TableAlias": "r", + "OpType": "Relation" + }, + "OpType": "Join" + } + ], + "OpType": "RelationList" + }, + "OpType": "Projection" + }, + "TableName": "t", + "OpType": "TablePrimaryTableSubQuery" + }, + "RightExpr": { + "TableName": "supplier", + "TableAlias": "s", + "OpType": "Relation" + }, + "OpType": "Join" + }, + "RightExpr": { + "TableName": "partsupp", + "TableAlias": "ps", + "OpType": "Relation" + }, + "OpType": "Join" + } + ], + "OpType": "RelationList" + }, + "OpType": "Filter" + }, + "OpType": "Projection" +} http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testJoinWithMultipleJoinQual1.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testJoinWithMultipleJoinQual1.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testJoinWithMultipleJoinQual1.sql new file mode 100644 index 0000000..9a04dc5 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testJoinWithMultipleJoinQual1.sql @@ -0,0 +1,20 @@ +select + s.s_acctbal, + s.s_name, + n.n_name, + p.p_partkey, + p.p_mfgr, + s.s_address, + s.s_phone, + s.s_comment +from nation n +join region r on (n.n_regionkey = r.r_regionkey) +join supplier s on (s.s_nationkey = n.n_nationkey) +join partsupp ps on (s.s_suppkey = ps.ps_suppkey) +join part p on (p.p_partkey = ps.ps_partkey) +where n.n_regionkey = ps.ps_suppkey +order by + s.s_acctbal, + s.s_name, + n.n_name, + p.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/TestJoinQuery/testJoinWithMultipleJoinQual2.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testJoinWithMultipleJoinQual2.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testJoinWithMultipleJoinQual2.sql new file mode 100644 index 0000000..4a40651 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testJoinWithMultipleJoinQual2.sql @@ -0,0 +1,8 @@ +select t.n_nationkey, t.name, t.n_regionkey, t.n_comment +from ( + select n_nationkey, n_name as name, n_regionkey, n_comment + from nation n + join region r on (n.n_regionkey = r.r_regionkey) +) t +join supplier s on (s.s_nationkey = t.n_nationkey) +where t.name = 'MOROCCO'; http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testJoinWithMultipleJoinQual3.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testJoinWithMultipleJoinQual3.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testJoinWithMultipleJoinQual3.sql new file mode 100644 index 0000000..b34e9de --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testJoinWithMultipleJoinQual3.sql @@ -0,0 +1,9 @@ +select t.n_nationkey, t.n_name, t.n_regionkey, t.n_comment, ps.ps_availqty, s.s_suppkey +from ( + select n_nationkey, n_name, n_regionkey, n_comment + from nation n + join region r on (n.n_regionkey = r.r_regionkey) +) t +join supplier s on (s.s_nationkey = t.n_nationkey) +join partsupp ps on (s.s_suppkey = ps.ps_suppkey) +where t.n_name in ('ARGENTINA','ETHIOPIA', 'MOROCCO'); http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testJoinWithMultipleJoinQual4.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testJoinWithMultipleJoinQual4.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testJoinWithMultipleJoinQual4.sql new file mode 100644 index 0000000..4519de7 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testJoinWithMultipleJoinQual4.sql @@ -0,0 +1,10 @@ +select t.n_nationkey, t.n_name, t.n_regionkey, t.n_comment, ps.ps_availqty, s.s_suppkey +from ( + select n_nationkey, n_name, n_regionkey, n_comment + from nation n + join region r on (n.n_regionkey = r.r_regionkey) +) t +join supplier s on (s.s_nationkey = t.n_nationkey) +join partsupp ps on (s.s_suppkey = ps.ps_suppkey) +where t.n_name in ('ARGENTINA','ETHIOPIA', 'MOROCCO') +and t.n_nationkey > s.s_suppkey ; http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testJoinWithMultipleJoinTypes.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testJoinWithMultipleJoinTypes.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testJoinWithMultipleJoinTypes.sql new file mode 100644 index 0000000..ffb23ce --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testJoinWithMultipleJoinTypes.sql @@ -0,0 +1,4 @@ +select * FROM +customer c +right outer join (select n_nationkey from nation) n on n.n_nationkey = c.c_custkey +join region r on r.r_regionkey = c.c_custkey; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testJoinWithOrPredicates.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testJoinWithOrPredicates.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testJoinWithOrPredicates.sql new file mode 100644 index 0000000..b388233 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testJoinWithOrPredicates.sql @@ -0,0 +1,6 @@ +select + n1.n_nationkey, + n1.n_name, + n2.n_name +from nation n1, nation n2 where n1.n_name = n2.n_name and (n1.n_nationkey in (1, 2) or n2.n_nationkey in (2)) +order by n1.n_nationkey; http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testLeftOuterJoin1.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testLeftOuterJoin1.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testLeftOuterJoin1.sql new file mode 100644 index 0000000..08a68e8 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testLeftOuterJoin1.sql @@ -0,0 +1,9 @@ +select + c_custkey, + orders.o_orderkey, + orders.o_orderstatus, + orders.o_orderdate +from + customer left outer join orders on c_custkey = o_orderkey +order by + c_custkey, o_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/TestJoinQuery/testLeftOuterJoinWithConstantExpr1.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testLeftOuterJoinWithConstantExpr1.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testLeftOuterJoinWithConstantExpr1.sql new file mode 100644 index 0000000..f5b0ba7 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testLeftOuterJoinWithConstantExpr1.sql @@ -0,0 +1,9 @@ +select + c_custkey, + orders.o_orderkey, + 'val' as val +from + customer left outer join orders on c_custkey = o_orderkey +order by + c_custkey, + o_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/TestJoinQuery/testLeftOuterJoinWithConstantExpr2.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testLeftOuterJoinWithConstantExpr2.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testLeftOuterJoinWithConstantExpr2.sql new file mode 100644 index 0000000..7333d54 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testLeftOuterJoinWithConstantExpr2.sql @@ -0,0 +1,9 @@ +select + c_custkey, + o.o_orderkey, + 'val' as val +from + customer left outer join (select * from orders) o on c_custkey = o.o_orderkey +order by + c_custkey, + o_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/TestJoinQuery/testLeftOuterJoinWithConstantExpr3.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testLeftOuterJoinWithConstantExpr3.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testLeftOuterJoinWithConstantExpr3.sql new file mode 100644 index 0000000..f79b18b --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testLeftOuterJoinWithConstantExpr3.sql @@ -0,0 +1,17 @@ +select + a.c_custkey, + 123::INT8 as const_val, + b.min_name +from + customer a +left outer join ( + select + c_custkey, + min(c_name) as min_name + from customer + group by + c_custkey) + b +on a.c_custkey = b.c_custkey +order by + a.c_custkey; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testLeftOuterJoinWithConstantExpr4.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testLeftOuterJoinWithConstantExpr4.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testLeftOuterJoinWithConstantExpr4.sql new file mode 100644 index 0000000..9e3838d --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testLeftOuterJoinWithConstantExpr4.sql @@ -0,0 +1,6 @@ +SELECT + l.l_orderkey, + o.o_orderkey, + '201405' as key1, + '5-LOW' as key2 +from lineitem l left outer join orders o on l.l_orderkey = o.o_orderkey and o_orderpriority = '5-LOW'; http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testLeftOuterJoinWithConstantExpr5.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testLeftOuterJoinWithConstantExpr5.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testLeftOuterJoinWithConstantExpr5.sql new file mode 100644 index 0000000..720a004 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testLeftOuterJoinWithConstantExpr5.sql @@ -0,0 +1,9 @@ +SELECT + l.l_orderkey, + o.o_orderkey, + '201405' as key1, + '5-LOW' as key2 +from + lineitem l left outer join orders o on l.l_orderkey = o.o_orderkey +WHERE + o_orderpriority = '5-LOW' http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testLeftOuterJoinWithEmptyTable1.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testLeftOuterJoinWithEmptyTable1.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testLeftOuterJoinWithEmptyTable1.sql new file mode 100644 index 0000000..27ac8bc --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testLeftOuterJoinWithEmptyTable1.sql @@ -0,0 +1,9 @@ +select + c_custkey, + empty_orders.o_orderkey, + empty_orders.o_orderstatus, + empty_orders.o_orderdate +from + customer left outer join empty_orders on c_custkey = o_orderkey +order by + c_custkey, o_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/TestJoinQuery/testLeftOuterJoinWithEmptyTable2.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testLeftOuterJoinWithEmptyTable2.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testLeftOuterJoinWithEmptyTable2.sql new file mode 100644 index 0000000..b0639ff --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testLeftOuterJoinWithEmptyTable2.sql @@ -0,0 +1,9 @@ +select + c_custkey, + sum(empty_orders.o_orderkey), + max(empty_orders.o_orderstatus), + max(empty_orders.o_orderdate) +from + customer left outer join empty_orders on c_custkey = o_orderkey + group by c_custkey +order by c_custkey ; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testLeftOuterJoinWithEmptyTable3.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testLeftOuterJoinWithEmptyTable3.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testLeftOuterJoinWithEmptyTable3.sql new file mode 100644 index 0000000..6a79e34 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testLeftOuterJoinWithEmptyTable3.sql @@ -0,0 +1,14 @@ +select count(*) +from ( + select + c_custkey, + sum(empty_orders.o_orderkey) as total1, + max(empty_orders.o_orderstatus) as total2, + max(empty_orders.o_orderdate) as total3 + from + customer left outer join empty_orders on c_custkey = o_orderkey + group by c_custkey +) t1 +group by + c_custkey +order by c_custkey ; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testLeftOuterJoinWithEmptyTable4.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testLeftOuterJoinWithEmptyTable4.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testLeftOuterJoinWithEmptyTable4.sql new file mode 100644 index 0000000..f4e9da6 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testLeftOuterJoinWithEmptyTable4.sql @@ -0,0 +1,17 @@ + + select + max(c_custkey), + sum(orders.o_orderkey), + max(orders.o_orderstatus), + max(orders.o_orderdate) + from + customer left outer join orders on c_custkey = o_orderkey + union all + select + max(c_custkey), + sum(empty_orders.o_orderkey), + max(empty_orders.o_orderstatus), + max(empty_orders.o_orderdate) + from + customer left outer join empty_orders on c_custkey = o_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/TestJoinQuery/testLeftOuterJoinWithEmptyTable5.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testLeftOuterJoinWithEmptyTable5.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testLeftOuterJoinWithEmptyTable5.sql new file mode 100644 index 0000000..d4ef649 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testLeftOuterJoinWithEmptyTable5.sql @@ -0,0 +1,11 @@ +select + l_linenumber, + sum(empty_orders.o_orderkey), + max(empty_orders.o_orderstatus), + max(empty_orders.o_orderdate), + avg(l_quantity), + sum(l_quantity) +from + lineitem left outer join empty_orders on l_orderkey = o_orderkey + group by l_linenumber +order 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/TestJoinQuery/testLeftOuterJoinWithNull1.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testLeftOuterJoinWithNull1.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testLeftOuterJoinWithNull1.sql new file mode 100644 index 0000000..5698a74 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testLeftOuterJoinWithNull1.sql @@ -0,0 +1,10 @@ +select + c_custkey, + orders.o_orderkey, + coalesce(orders.o_orderstatus, 'N/A'), + orders.o_orderdate +from + customer left outer join orders on c_custkey = o_orderkey +where o_orderkey is null +order by + c_custkey, o_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/TestJoinQuery/testLeftOuterJoinWithNull2.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testLeftOuterJoinWithNull2.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testLeftOuterJoinWithNull2.sql new file mode 100644 index 0000000..000ab68 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testLeftOuterJoinWithNull2.sql @@ -0,0 +1,11 @@ +select + c_custkey, + orders.o_orderkey, + coalesce(orders.o_orderstatus, 'N/A'), + orders.o_orderdate +from + customer left outer join orders on c_custkey = o_orderkey +where orders.o_orderdate is not null +and orders.o_orderdate like '1996%' +order by + c_custkey, o_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/TestJoinQuery/testLeftOuterJoinWithNull3.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testLeftOuterJoinWithNull3.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testLeftOuterJoinWithNull3.sql new file mode 100644 index 0000000..32a7750 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testLeftOuterJoinWithNull3.sql @@ -0,0 +1,10 @@ +select + c_custkey, + orders.o_orderkey, + coalesce(orders.o_orderstatus, 'N/A'), + orders.o_orderdate +from + customer left outer join orders on c_custkey = o_orderkey +where orders.o_orderkey = 100 +order by + c_custkey, o_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/TestJoinQuery/testLeftOuterJoinWithThetaJoinConditionInWhere.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testLeftOuterJoinWithThetaJoinConditionInWhere.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testLeftOuterJoinWithThetaJoinConditionInWhere.sql new file mode 100644 index 0000000..f9160c5 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testLeftOuterJoinWithThetaJoinConditionInWhere.sql @@ -0,0 +1,3 @@ +select * from region a left outer join customer b +on a.r_regionkey = b.c_custkey +where a.r_name < b.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/TestJoinQuery/testNaturalJoin.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testNaturalJoin.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testNaturalJoin.sql new file mode 100644 index 0000000..fcbdcdc --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testNaturalJoin.sql @@ -0,0 +1,3 @@ +select n1.n_name, n2.n_name +from nation n1 natural join nation n2 +order by n2.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/TestJoinQuery/testOuterJoinAndCaseWhen1.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testOuterJoinAndCaseWhen1.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testOuterJoinAndCaseWhen1.sql new file mode 100644 index 0000000..2071e65 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testOuterJoinAndCaseWhen1.sql @@ -0,0 +1,12 @@ +select + a.id, + a.name, + b.id as id2, + b.name as name2, + case when b.name is null then '9991231' else b.name end as c1, + case when c.name is null then '9991231' else c.name end as c2 +from + testOuterJoinAndCaseWhen1 a left outer join testOuterJoinAndCaseWhen2 b on a.id = b.id left outer join testOuterJoinAndCaseWhen1 c on b.id = c.id +order by + a.id, + a.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/TestJoinQuery/testRightOuterJoin1.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testRightOuterJoin1.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testRightOuterJoin1.sql new file mode 100644 index 0000000..ba4c713 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testRightOuterJoin1.sql @@ -0,0 +1,8 @@ +select + c_custkey, + orders.o_orderkey +from + orders right outer join customer on c_custkey = o_orderkey +order by + c_custkey, + orders.o_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/TestJoinQuery/testRightOuterJoinWithEmptyTable1.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testRightOuterJoinWithEmptyTable1.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testRightOuterJoinWithEmptyTable1.sql new file mode 100644 index 0000000..651a3cf --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testRightOuterJoinWithEmptyTable1.sql @@ -0,0 +1,8 @@ +select + c_custkey, + empty_orders.o_orderkey +from + empty_orders right outer join customer on c_custkey = o_orderkey +order by + c_custkey, + empty_orders.o_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/TestJoinQuery/testRightOuterJoinWithThetaJoinConditionInWhere.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testRightOuterJoinWithThetaJoinConditionInWhere.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testRightOuterJoinWithThetaJoinConditionInWhere.sql new file mode 100644 index 0000000..10d9918 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testRightOuterJoinWithThetaJoinConditionInWhere.sql @@ -0,0 +1,3 @@ +select * from region a right outer join customer b +on a.r_regionkey = b.c_custkey +where a.r_name < b.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/TestJoinQuery/testTPCHQ2Join.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testTPCHQ2Join.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testTPCHQ2Join.sql new file mode 100644 index 0000000..9b7b5b9 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testTPCHQ2Join.sql @@ -0,0 +1,25 @@ +select + s_acctbal, + s_name, + n_name, + p_partkey, + p_mfgr, + s_address, + s_phone, + s_comment +from + part, + supplier, + partsupp, + nation, + region +where + p_partkey = ps_partkey + and s_suppkey = ps_suppkey + and s_nationkey = n_nationkey + and n_regionkey = r_regionkey +order by + s_acctbal, + s_name, + n_name, + p_partkey; http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testWhereClauseJoin1.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testWhereClauseJoin1.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testWhereClauseJoin1.sql new file mode 100644 index 0000000..575456f --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testWhereClauseJoin1.sql @@ -0,0 +1,11 @@ +select + n_name, + r_name, + n_regionkey, + r_regionkey +from + nation, + region +where + n_regionkey = r_regionkey +order by 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/TestJoinQuery/testWhereClauseJoin2.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testWhereClauseJoin2.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testWhereClauseJoin2.sql new file mode 100644 index 0000000..efc07b3 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testWhereClauseJoin2.sql @@ -0,0 +1,9 @@ +select + n_name, + r_name +from + nation, + region +where + n_regionkey = r_regionkey +order by 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/TestJoinQuery/testWhereClauseJoin3.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testWhereClauseJoin3.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testWhereClauseJoin3.sql new file mode 100644 index 0000000..04fd25b --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testWhereClauseJoin3.sql @@ -0,0 +1,10 @@ +select + n_name, + r_name, + n_nationkey + 1 as p1, + r_regionkey + 1 as p2 +from + nation, region +where + n_regionkey = r_regionkey +order by 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/TestJoinQuery/testWhereClauseJoin4.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testWhereClauseJoin4.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testWhereClauseJoin4.sql new file mode 100644 index 0000000..777de8a --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testWhereClauseJoin4.sql @@ -0,0 +1,9 @@ +select + n_name, + r_name, + n_nationkey + r_regionkey +from + nation, region +where + n_regionkey = r_regionkey +order by 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/TestJoinQuery/testWhereClauseJoin5.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testWhereClauseJoin5.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testWhereClauseJoin5.sql new file mode 100644 index 0000000..3494007 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testWhereClauseJoin5.sql @@ -0,0 +1,15 @@ +select + s_acctbal, + s_name, + p_partkey +from + part, + supplier, + partsupp +where + p_partkey = ps_partkey and + s_suppkey = ps_suppkey +order by + s_acctbal, + s_name, + p_partkey; http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testWhereClauseJoin6.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testWhereClauseJoin6.sql b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testWhereClauseJoin6.sql new file mode 100644 index 0000000..62d3b79 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestJoinQuery/testWhereClauseJoin6.sql @@ -0,0 +1,19 @@ +select + s_acctbal, + s_name, + p_partkey, + n_name +from + part, + supplier, + partsupp, + nation +where + p_partkey = ps_partkey and + s_suppkey = ps_suppkey and + s_nationkey = n_nationkey +order by + s_acctbal, + s_name, + p_partkey, + n_name; http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestLogicalPlanner/window1.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestLogicalPlanner/window1.sql b/tajo-core-tests/src/test/resources/queries/TestLogicalPlanner/window1.sql new file mode 100644 index 0000000..9d30145 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestLogicalPlanner/window1.sql @@ -0,0 +1,5 @@ + SELECT + l_orderkey, + sum(l_partkey) over() +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/TestNetTypes/table1_ddl.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestNetTypes/table1_ddl.sql b/tajo-core-tests/src/test/resources/queries/TestNetTypes/table1_ddl.sql new file mode 100644 index 0000000..b4cc87e --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestNetTypes/table1_ddl.sql @@ -0,0 +1,4 @@ +-- It is used in TestNetTypes + +create external table IF NOT EXISTS table1 (id int, name text, score float, type text, addr inet4) 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/TestNetTypes/table2_ddl.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestNetTypes/table2_ddl.sql b/tajo-core-tests/src/test/resources/queries/TestNetTypes/table2_ddl.sql new file mode 100644 index 0000000..40f0464 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestNetTypes/table2_ddl.sql @@ -0,0 +1,4 @@ +-- It is used in TestNetTypes + +create external table IF NOT EXISTS table2 (id int, name text, score float, type text, addr inet4) 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/TestNetTypes/testGroupby.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestNetTypes/testGroupby.sql b/tajo-core-tests/src/test/resources/queries/TestNetTypes/testGroupby.sql new file mode 100644 index 0000000..27353a9 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestNetTypes/testGroupby.sql @@ -0,0 +1,8 @@ +select + name, addr, count(1) +from + table1 +group by + name, addr +order by + name, addr; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestNetTypes/testGroupby2.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestNetTypes/testGroupby2.sql b/tajo-core-tests/src/test/resources/queries/TestNetTypes/testGroupby2.sql new file mode 100644 index 0000000..6c3c357 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestNetTypes/testGroupby2.sql @@ -0,0 +1,9 @@ +select + addr, + count(*) +from + table1 +group by + addr +order by + addr; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestNetTypes/testJoin.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestNetTypes/testJoin.sql b/tajo-core-tests/src/test/resources/queries/TestNetTypes/testJoin.sql new file mode 100644 index 0000000..22c97d5 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestNetTypes/testJoin.sql @@ -0,0 +1 @@ +select t1.*,t2.* from table1 as t1, table2 as t2 where t1.addr = t2.addr order by t1.id, t1.name,t2. 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/TestNetTypes/testSelect.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestNetTypes/testSelect.sql b/tajo-core-tests/src/test/resources/queries/TestNetTypes/testSelect.sql new file mode 100644 index 0000000..1b28f06 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestNetTypes/testSelect.sql @@ -0,0 +1 @@ +select name, addr from 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/TestNetTypes/testSort.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestNetTypes/testSort.sql b/tajo-core-tests/src/test/resources/queries/TestNetTypes/testSort.sql new file mode 100644 index 0000000..2999a02 --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestNetTypes/testSort.sql @@ -0,0 +1 @@ +select * from table1 order by addr; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/resources/queries/TestNetTypes/testSort2.sql ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/resources/queries/TestNetTypes/testSort2.sql b/tajo-core-tests/src/test/resources/queries/TestNetTypes/testSort2.sql new file mode 100644 index 0000000..b613d4a --- /dev/null +++ b/tajo-core-tests/src/test/resources/queries/TestNetTypes/testSort2.sql @@ -0,0 +1 @@ +select addr from table2 order by addr; \ No newline at end of file
