http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/java/org/apache/tajo/engine/query/TestOuterJoinQuery.java ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/java/org/apache/tajo/engine/query/TestOuterJoinQuery.java b/tajo-core-tests/src/test/java/org/apache/tajo/engine/query/TestOuterJoinQuery.java new file mode 100644 index 0000000..0b42a6d --- /dev/null +++ b/tajo-core-tests/src/test/java/org/apache/tajo/engine/query/TestOuterJoinQuery.java @@ -0,0 +1,457 @@ +/** + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ + +package org.apache.tajo.engine.query; + +import org.apache.tajo.IntegrationTest; +import org.apache.tajo.NamedTest; +import org.junit.AfterClass; +import org.junit.BeforeClass; +import org.junit.Test; +import org.junit.experimental.categories.Category; +import org.junit.runner.RunWith; +import org.junit.runners.Parameterized; + +import java.sql.SQLException; + +@Category(IntegrationTest.class) +@RunWith(Parameterized.class) +@NamedTest("TestJoinQuery") +public class TestOuterJoinQuery extends TestJoinQuery { + + public TestOuterJoinQuery(String joinOption) throws Exception { + super(joinOption); + } + + @BeforeClass + public static void setup() throws Exception { + TestJoinQuery.setup(); + } + + @AfterClass + public static void classTearDown() throws SQLException { + TestJoinQuery.classTearDown(); + } + + @Test + @Option(withExplain = true, withExplainGlobal = true, parameterized = true) + @SimpleTest() + public final void testLeftOuterJoinWithThetaJoinConditionInWhere() throws Exception { + runSimpleTests(); + } + + @Test + @Option(withExplain = true, withExplainGlobal = true, parameterized = true) + @SimpleTest() + public final void testRightOuterJoinWithThetaJoinConditionInWhere() throws Exception { + runSimpleTests(); + } + + @Test + @Option(withExplain = true, withExplainGlobal = true, parameterized = true) + @SimpleTest() + public final void testLeftOuterJoin1() throws Exception { + runSimpleTests(); + } + + @Test + @Option(withExplain = true, withExplainGlobal = true, parameterized = true) + @SimpleTest() + public final void testLeftOuterJoinWithConstantExpr1() throws Exception { + // outer join with constant projections + // + // select c_custkey, orders.o_orderkey, 'val' as val from customer + // left outer join orders on c_custkey = o_orderkey; + runSimpleTests(); + } + + @Test + @Option(withExplain = true, withExplainGlobal = true, parameterized = true) + @SimpleTest() + public final void testLeftOuterJoinWithConstantExpr4() throws Exception { + // outer join with constant projections + // + // select + // c_custkey, + // orders.o_orderkey, + // 1 as key1 + // from customer left outer join orders on c_custkey = o_orderkey and key1 = 1; + runSimpleTests(); + } + + @Test + @Option(withExplain = true, withExplainGlobal = true, parameterized = true) + @SimpleTest() + public final void testLeftOuterJoinWithConstantExpr5() throws Exception { + // outer join with constant projections + // + // select + // c_custkey, + // orders.o_orderkey, + // 1 as key1 + // from customer left outer join orders on c_custkey = o_orderkey and key1 = 1; + runSimpleTests(); + } + + @Test + @Option(withExplain = true, withExplainGlobal = true, parameterized = true) + @SimpleTest() + public final void testRightOuterJoin1() throws Exception { + runSimpleTests(); + } + + @Test + @Option(withExplain = true, withExplainGlobal = true, parameterized = true) + @SimpleTest() + public final void testFullOuterJoin1() throws Exception { + runSimpleTests(); + } + + @Test + @Option(withExplain = true, withExplainGlobal = true, parameterized = true) + @SimpleTest() + public void testOuterJoinAndCaseWhen1() throws Exception { + executeDDL("oj_table1_ddl.sql", "table1", "testOuterJoinAndCaseWhen1"); + executeDDL("oj_table2_ddl.sql", "table2", "testOuterJoinAndCaseWhen2"); + try { + runSimpleTests(); + } finally { + executeString("DROP TABLE testOuterJoinAndCaseWhen1"); + executeString("DROP TABLE testOuterJoinAndCaseWhen2"); + } + } + + @Test + @Option(withExplain = true, withExplainGlobal = true, parameterized = true) + @SimpleTest() + public final void testLeftOuterJoinWithEmptyTable1() throws Exception { + /* + 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; + */ + + runSimpleTests(); + } + + @Test + @Option(withExplain = true, withExplainGlobal = true, parameterized = true) + @SimpleTest() + public final void testLeftOuterJoinWithEmptyTable2() throws Exception { + runSimpleTests(); + } + + @Test + @Option(withExplain = true, withExplainGlobal = true, parameterized = true) + @SimpleTest() + public final void testLeftOuterJoinWithEmptyTable3() throws Exception { + runSimpleTests(); + } + + @Test + @Option(withExplain = true, withExplainGlobal = true, parameterized = true) + @SimpleTest() + public final void testLeftOuterJoinWithEmptyTable4() throws Exception { + runSimpleTests(); + } + + @Test + @Option(withExplain = true, withExplainGlobal = true, parameterized = true) + @SimpleTest() + public final void testLeftOuterJoinWithEmptyTable5() throws Exception { + runSimpleTests(); + } + + @Test + @Option(withExplain = true, withExplainGlobal = true, parameterized = true) + @SimpleTest() + public final void testRightOuterJoinWithEmptyTable1() throws Exception { + runSimpleTests(); + } + + @Test + @Option(withExplain = true, withExplainGlobal = true, parameterized = true) + @SimpleTest() + public final void testFullOuterJoinWithEmptyTable1() throws Exception { + runSimpleTests(); + } + + @Test + @Option(withExplain = true, withExplainGlobal = true, parameterized = true) + @SimpleTest() + public final void testLeftOuterJoinWithNull1() throws Exception { + runSimpleTests(); + } + + @Test + @Option(withExplain = true, withExplainGlobal = true, parameterized = true) + @SimpleTest() + public final void testLeftOuterJoinWithNull2() throws Exception { + runSimpleTests(); + } + + @Test + @Option(withExplain = true, withExplainGlobal = true, parameterized = true) + @SimpleTest() + public final void testLeftOuterJoinWithNull3() throws Exception { + runSimpleTests(); + } + + @Test + @Option(withExplain = true, withExplainGlobal = true, parameterized = true, sort = true) + @SimpleTest(queries = { + @QuerySpec("select t1.id, t1.name, t2.id, t3.id\n" + + "from jointable11 t1\n" + + "left outer join jointable12 t2\n" + + "on t1.id = t2.id\n" + + "left outer join jointable13 t3\n" + + "on t1.id = t3.id and t2.id = t3.id") + }) + public final void testLeftOuterJoinPredicationCaseByCase1() throws Exception { + runSimpleTests(); + } + + @Test + @Option(withExplain = true, withExplainGlobal = true, parameterized = true) + @SimpleTest(queries = { + @QuerySpec("select t1.id, t1.name, t2.id, t3.id, t4.id\n" + + "from jointable11 t1\n" + + "left outer join jointable12 t2\n" + + "on t1.id = t2.id\n" + + "left outer join jointable13 t3\n" + + "on t2.id = t3.id\n" + + "inner join jointable14 t4\n" + + "on t2.id = t4.id") + }) + public final void testLeftOuterJoinPredicationCaseByCase2() throws Exception { + // outer -> outer -> inner + runSimpleTests(); + } + + @Test + @Option(withExplain = true, withExplainGlobal = true, parameterized = true, sort = true) + @SimpleTest(queries = { + @QuerySpec("select t1.id, t1.name, t2.id, t3.id, t4.id\n" + + "from jointable11 t1\n" + + "inner join jointable14 t4\n" + + "on t1.id = t4.id and t4.id > 1\n" + + "left outer join jointable13 t3\n" + + "on t4.id = t3.id and t3.id = 2\n" + + "left outer join jointable12 t2\n" + + "on t1.id = t2.id \n" + + "where t1.id > 1") + }) + public final void testLeftOuterJoinPredicationCaseByCase2_1() throws Exception { + // inner(on predication) -> outer(on predication) -> outer -> where + runSimpleTests(); + } + + @Test + @Option(withExplain = true, withExplainGlobal = true, parameterized = true, sort = true) + @SimpleTest(queries = { + @QuerySpec("select t1.id, t1.name, t2.id, t3.id\n" + + "from jointable11 t1\n" + + "left outer join jointable12 t2 \n" + + "on t1.id = t2.id and (concat(t1.name, cast(t2.id as TEXT)) = 'table11-11' or concat(t1.name, cast(t2.id as TEXT)) = 'table11-33')\n" + + "left outer join jointable13 t3\n" + + "on t1.id = t3.id ") + }) + public final void testLeftOuterJoinPredicationCaseByCase3() throws Exception { + // https://cwiki.apache.org/confluence/display/Hive/OuterJoinBehavior + // Case J1: Join Predicate on Preserved Row Table + runSimpleTests(); + } + + @Test + @Option(withExplain = true, withExplainGlobal = true, parameterized = true, sort = true) + @SimpleTest(queries = { + @QuerySpec("select t1.id, t1.name, t2.id, t3.id\n" + + "from jointable11 t1\n" + + "left outer join jointable12 t2\n" + + "on t1.id = t2.id and t2.id > 1 \n" + + "left outer join jointable13 t3\n" + + "on t1.id = t3.id") + }) + public final void testLeftOuterJoinPredicationCaseByCase4() throws Exception { + // https://cwiki.apache.org/confluence/display/Hive/OuterJoinBehavior + // Case J2: Join Predicate on Null Supplying Table + runSimpleTests(); + } + + @Test + @Option(withExplain = true, withExplainGlobal = true, parameterized = true, sort = true) + @SimpleTest(queries = { + @QuerySpec("select t1.id, t1.name, t2.id, t3.id\n" + + "from jointable11 t1\n" + + "left outer join jointable12 t2\n" + + "on t1.id = t2.id\n" + + "left outer join jointable13 t3\n" + + "on t1.id = t3.id\n" + + "where t1.name > 'table11-1'") + }) + public final void testLeftOuterJoinPredicationCaseByCase5() throws Exception { + // https://cwiki.apache.org/confluence/display/Hive/OuterJoinBehavior + // Case W1: Where Predicate on Preserved Row Table + runSimpleTests(); + } + + @Test + @Option(withExplain = true, withExplainGlobal = true, parameterized = true) + @SimpleTest(queries = { + @QuerySpec("select t1.id, t1.name, t2.id, t3.id\n" + + "from jointable11 t1\n" + + "left outer join jointable12 t2\n" + + "on t1.id = t2.id\n" + + "left outer join jointable13 t3\n" + + "on t1.id = t3.id\n" + + "where t3.id > 2") + }) + public final void testLeftOuterJoinPredicationCaseByCase6() throws Exception { + // https://cwiki.apache.org/confluence/display/Hive/OuterJoinBehavior + // Case W2: Where Predicate on Null Supplying Table + runSimpleTests(); + } + + @Test + @Option(withExplain = true, withExplainGlobal = true, parameterized = true, sort = true) + @SimpleTest(queries = { + @QuerySpec("select t1.id, t1.name, t2.id\n" + + "from jointable11 t1\n" + + "left outer join jointable15 t2\n" + + "on t1.id = t2.id") + }) + public final void testLeftOuterWithEmptyTable() throws Exception { + // https://cwiki.apache.org/confluence/display/Hive/OuterJoinBehavior + // Case W2: Where Predicate on Null Supplying Table + runSimpleTests(); + } + + @Test + @Option(withExplain = true, withExplainGlobal = true, parameterized = true) + @SimpleTest(queries = { + @QuerySpec("select t1.id, t1.name, t2.id, t3.id\n" + + "from jointable11 t1\n" + + "right outer join jointable12 t2\n" + + "on t1.id = t2.id\n" + + "right outer join jointable13 t3\n" + + "on t1.id = t3.id and t2.id = t3.id") + }) + public final void testRightOuterJoinPredicationCaseByCase1() throws Exception { + runSimpleTests(); + } + + @Test + @Option(withExplain = true, withExplainGlobal = true, parameterized = true) + @SimpleTest(queries = { + @QuerySpec("select t1.id, t1.name, t3.id, t4.id\n" + + "from jointable11 t1\n" + + "inner join jointable14 t4\n" + + "on t1.id = t4.id and t4.id > 1\n" + + "right outer join jointable13 t3\n" + + "on t4.id = t3.id and t3.id = 2\n" + + "where t3.id > 1") + }) + public final void testRightOuterJoinPredicationCaseByCase2() throws Exception { + // inner -> right + // Notice: Join order should be preserved with origin order. + // JoinEdge: t1 -> t4, t3 -> t1,t4 + runSimpleTests(); + } + + @Test + @Option(withExplain = true, withExplainGlobal = true, parameterized = true) + @SimpleTest(queries = { + @QuerySpec("select t1.id, t1.name, t2.id, t3.id\n" + + "from jointable11 t1\n" + + "right outer join jointable12 t2 \n" + + "on t1.id = t2.id and (concat(t1.name, cast(t2.id as TEXT)) = 'table11-11' or concat(t1.name, cast(t2.id as TEXT)) = 'table11-33')\n" + + "right outer join jointable13 t3\n" + + "on t1.id = t3.id ") + }) + public final void testRightOuterJoinPredicationCaseByCase3() throws Exception { + runSimpleTests(); + } + + @Test + @Option(withExplain = true, withExplainGlobal = true, parameterized = true, sort = true) + @SimpleTest(queries = { + @QuerySpec("select t1.id, t1.name, t3.id, t4.id\n" + + "from jointable11 t1\n" + + "full outer join jointable13 t3\n" + + "on t1.id = t3.id\n" + + "full outer join jointable14 t4\n" + + "on t3.id = t4.id \n" + + "order by t4.id") + }) + public final void testFullOuterJoinPredicationCaseByCase1() throws Exception { + runSimpleTests(); + } + + @Test + @Option(withExplain = true, withExplainGlobal = true, parameterized = true) + @SimpleTest() + public final void testJoinFilterOfRowPreservedTable1() throws Exception { + // this test is for join filter of a row preserved table. + runSimpleTests(); + } + + @Test + @Option(withExplain = true, withExplainGlobal = true, parameterized = true) + @SimpleTest() + public final void testLeftOuterJoin2() throws Exception { + // large, large, small, small + runSimpleTests(); + } + + @Test + @Option(withExplain = true, withExplainGlobal = true, parameterized = true) + @SimpleTest() + public final void testLeftOuterJoin3() throws Exception { + // large, large, small, large, small, small + runSimpleTests(); + } + + @Test + @Option(withExplain = true, withExplainGlobal = true, parameterized = true) + @SimpleTest(queries = { + @QuerySpec("select a.id, b.name from jointable1 a left outer join jointable_large b on a.id = b.id order by a.id") + }) + public final void testLeftOuterJoinLeftSideSmallTable() throws Exception { + runSimpleTests(); + } + + @Test + @Option(withExplain = true, withExplainGlobal = true, parameterized = true) + @SimpleTest + public void testMultipleBroadcastDataFileWithZeroLength() throws Exception { + runSimpleTests(); + } + + @Test + @Option(withExplain = true, withExplainGlobal = true, parameterized = true, sort = true) + @SimpleTest + public void testMultipleBroadcastDataFileWithZeroLength2() throws Exception { + runSimpleTests(); + } +}
http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/java/org/apache/tajo/engine/query/TestOuterJoinWithSubQuery.java ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/java/org/apache/tajo/engine/query/TestOuterJoinWithSubQuery.java b/tajo-core-tests/src/test/java/org/apache/tajo/engine/query/TestOuterJoinWithSubQuery.java new file mode 100644 index 0000000..00ce150 --- /dev/null +++ b/tajo-core-tests/src/test/java/org/apache/tajo/engine/query/TestOuterJoinWithSubQuery.java @@ -0,0 +1,143 @@ +/** + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ + +package org.apache.tajo.engine.query; + +import org.apache.tajo.IntegrationTest; +import org.apache.tajo.NamedTest; +import org.apache.tajo.conf.TajoConf; +import org.junit.*; +import org.junit.experimental.categories.Category; +import org.junit.runner.RunWith; +import org.junit.runners.Parameterized; + +import java.sql.SQLException; + +import static org.junit.Assert.assertEquals; + +@Category(IntegrationTest.class) +@RunWith(Parameterized.class) +@NamedTest("TestJoinQuery") +public class TestOuterJoinWithSubQuery extends TestJoinQuery { + + public TestOuterJoinWithSubQuery(String joinOption) throws Exception { + super(joinOption); + } + + @BeforeClass + public static void setup() throws Exception { + TestJoinQuery.setup(); + } + + @AfterClass + public static void classTearDown() throws SQLException { + TestJoinQuery.classTearDown(); + } + + @Test + @Option(withExplain = true, withExplainGlobal = true, parameterized = true) + @SimpleTest() + public final void testLeftOuterJoinWithConstantExpr2() throws Exception { + // outer join with constant projections + // + // select c_custkey, o.o_orderkey, 'val' as val from customer left outer join + // (select * from orders) o on c_custkey = o.o_orderkey + runSimpleTests(); + } + + @Test + @Option(withExplain = true, withExplainGlobal = true, parameterized = true) + @SimpleTest() + public final void testLeftOuterJoinWithConstantExpr3() throws Exception { + // outer join with constant projections + // + // 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; + runSimpleTests(); + } + + @Test + @Option(withExplain = true, withExplainGlobal = true, parameterized = true) + @SimpleTest(queries = { + @QuerySpec("select a.id, b.id from jointable11 a " + + "left outer join (" + + "select jointable12.id from jointable12 inner join lineitem " + + "on jointable12.id = lineitem.l_orderkey and jointable12.id > 10) b " + + "on a.id = b.id order by a.id") + }) + public final void testLeftOuterJoinWithEmptySubquery1() throws Exception { + try { + testingCluster.setAllTajoDaemonConfValue(TajoConf.ConfVars.$TEST_MIN_TASK_NUM.varname, "2"); + runSimpleTests(); + } finally { + testingCluster.setAllTajoDaemonConfValue(TajoConf.ConfVars.$TEST_MIN_TASK_NUM.varname, + TajoConf.ConfVars.$TEST_MIN_TASK_NUM.defaultVal); + } + } + + @Test + @Option(withExplain = true, withExplainGlobal = true, parameterized = true) + @SimpleTest(queries = { + @QuerySpec("select a.id, b.id from " + + "(select jointable12.id, jointable12.name, lineitem.l_shipdate " + + "from jointable12 inner join lineitem on jointable12.id = lineitem.l_orderkey and jointable12.id > 10) a " + + "left outer join jointable11 b on a.id = b.id") + }) + public final void testLeftOuterJoinWithEmptySubquery2() throws Exception { + //Empty Preserved Row table + try { + testingCluster.setAllTajoDaemonConfValue(TajoConf.ConfVars.$TEST_MIN_TASK_NUM.varname, "2"); + runSimpleTests(); + } finally { + testingCluster.setAllTajoDaemonConfValue(TajoConf.ConfVars.$TEST_MIN_TASK_NUM.varname, + TajoConf.ConfVars.$TEST_MIN_TASK_NUM.defaultVal); + } + } + + @Test + @Option(withExplain = true, withExplainGlobal = true, parameterized = true) + @SimpleTest(queries = { + @QuerySpec("select a.l_orderkey \n" + + "from (select * from lineitem where l_orderkey < 0) a\n" + + "full outer join (select * from lineitem where l_orderkey < 0) b\n" + + "on a.l_orderkey = b.l_orderkey") + }) + public void testFullOuterJoinWithEmptyIntermediateData() throws Exception { + runSimpleTests(); + } + + @Test + @Option(withExplain = true, withExplainGlobal = true, parameterized = true) + @SimpleTest(queries = { + @QuerySpec("select count(b.id) " + + "from (select id, count(*) as cnt from jointable_large group by id) a " + + "left outer join (select id, count(*) as cnt from jointable_large where id < 200 group by id) b " + + "on a.id = b.id") + }) + public void testJoinWithDifferentShuffleKey() throws Exception { + int originConfValue = conf.getIntVar(TajoConf.ConfVars.$DIST_QUERY_JOIN_PARTITION_VOLUME); + testingCluster.setAllTajoDaemonConfValue(TajoConf.ConfVars.$DIST_QUERY_JOIN_PARTITION_VOLUME.varname, "1"); + try { + runSimpleTests(); + } finally { + testingCluster.setAllTajoDaemonConfValue(TajoConf.ConfVars.$DIST_QUERY_JOIN_PARTITION_VOLUME.varname, + "" + originConfValue); + } + } +} http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/java/org/apache/tajo/engine/query/TestSelectNestedRecord.java ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/java/org/apache/tajo/engine/query/TestSelectNestedRecord.java b/tajo-core-tests/src/test/java/org/apache/tajo/engine/query/TestSelectNestedRecord.java new file mode 100644 index 0000000..41df0fa --- /dev/null +++ b/tajo-core-tests/src/test/java/org/apache/tajo/engine/query/TestSelectNestedRecord.java @@ -0,0 +1,119 @@ +/** + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ + +package org.apache.tajo.engine.query; + +import org.apache.tajo.QueryTestCaseBase; +import org.apache.tajo.util.TUtil; +import org.junit.Test; + +import java.sql.ResultSet; +import java.util.List; + +import static org.junit.Assert.assertEquals; + +public class TestSelectNestedRecord extends QueryTestCaseBase { + + @Test + public final void testSelect0() throws Exception { + List<String> tables = executeDDL("sample1_ddl.sql", "sample1", "sample1"); + assertEquals(TUtil.newList("sample1"), tables); + + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testSelect1() throws Exception { + List<String> tables = executeDDL("sample1_ddl.sql", "sample1", "sample2"); + assertEquals(TUtil.newList("sample2"), tables); + + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testSelect2() throws Exception { + List<String> tables = executeDDL("tweets_ddl.sql", "tweets", "tweets"); + assertEquals(TUtil.newList("tweets"), tables); + + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testSelect3() throws Exception { + List<String> tables = executeDDL("sample2_ddl.sql", "sample2", "sample5"); + assertEquals(TUtil.newList("sample5"), tables); + + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testNestedFieldAsGroupbyKey1() throws Exception { + List<String> tables = executeDDL("tweets_ddl.sql", "tweets", "tweets"); + assertEquals(TUtil.newList("tweets"), tables); + + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testNestedFieldAsJoinKey1() throws Exception { + List<String> tables = executeDDL("tweets_ddl.sql", "tweets", "tweets"); + assertEquals(TUtil.newList("tweets"), tables); + + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testInsertType1() throws Exception { + // all columns + List<String> tables = executeDDL("sample1_ddl.sql", "sample1", "sample3"); + assertEquals(TUtil.newList("sample3"), tables); + + executeString("CREATE TABLE clone (title TEXT, name RECORD (first_name TEXT, last_name TEXT)) USING JSON;").close(); + + executeString("INSERT INTO clone (title, name.first_name, name.last_name) SELECT title, name.first_name, name.last_name from sample3").close(); + ResultSet res = executeString("select title, name.first_name, name.last_name from clone"); + assertResultSet(res); + res.close(); + } + + @Test + public final void testInsertType2() throws Exception { + // some columns + List<String> tables = executeDDL("sample1_ddl.sql", "sample1", "sample4"); + assertEquals(TUtil.newList("sample4"), tables); + + executeString("CREATE TABLE clone2 (title TEXT, name RECORD (first_name TEXT, last_name TEXT)) USING JSON;").close(); + + executeString("INSERT INTO clone2 (title, name.last_name) SELECT title, name.last_name from sample4").close(); + ResultSet res = executeString("select title, name.first_name, name.last_name from clone2"); + assertResultSet(res); + res.close(); + } +} http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/java/org/apache/tajo/engine/query/TestSelectQuery.java ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/java/org/apache/tajo/engine/query/TestSelectQuery.java b/tajo-core-tests/src/test/java/org/apache/tajo/engine/query/TestSelectQuery.java new file mode 100644 index 0000000..27c2fcd --- /dev/null +++ b/tajo-core-tests/src/test/java/org/apache/tajo/engine/query/TestSelectQuery.java @@ -0,0 +1,778 @@ +/** + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ + +package org.apache.tajo.engine.query; + +import com.google.common.collect.Lists; +import org.apache.tajo.IntegrationTest; +import org.apache.tajo.QueryId; +import org.apache.tajo.QueryTestCaseBase; +import org.apache.tajo.SessionVars; +import org.apache.tajo.TajoConstants; +import org.apache.tajo.TajoProtos.QueryState; +import org.apache.tajo.TajoTestingCluster; +import org.apache.tajo.catalog.CatalogService; +import org.apache.tajo.catalog.Schema; +import org.apache.tajo.catalog.TableDesc; +import org.apache.tajo.client.QueryStatus; +import org.apache.tajo.common.TajoDataTypes.Type; +import org.apache.tajo.conf.TajoConf; +import org.apache.tajo.conf.TajoConf.ConfVars; +import org.apache.tajo.ipc.ClientProtos; +import org.apache.tajo.plan.rewrite.BaseLogicalPlanRewriteRuleProvider; +import org.apache.tajo.plan.rewrite.LogicalPlanRewriteRule; +import org.apache.tajo.storage.StorageConstants; +import org.apache.tajo.util.KeyValueSet; +import org.junit.Test; +import org.junit.experimental.categories.Category; + +import java.sql.ResultSet; +import java.util.*; + +import static org.apache.tajo.TajoConstants.DEFAULT_DATABASE_NAME; +import static org.junit.Assert.*; + +@Category(IntegrationTest.class) +public class TestSelectQuery extends QueryTestCaseBase { + + public TestSelectQuery() { + super(TajoConstants.DEFAULT_DATABASE_NAME); + } + + @Test + public final void testNonQualifiedNames() throws Exception { + // select l_orderkey, l_partkey from lineitem; + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testNonFromSelect1() throws Exception { + // select upper('abc'); + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testSimpleQuery() throws Exception { + // select * from lineitem; + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testSimpleQueryWithLimit() throws Exception { + // select * from lineitem limit 3; + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testSimpleQueryWithLimitPartitionedTable() throws Exception { + // select * from customer_parts limit 10; + executeDDL("customer_ddl.sql", null); + for (int i = 0; i < 5; i++) { + executeFile("insert_into_customer.sql").close(); + } + + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + + executeString("DROP TABLE customer_parts PURGE").close(); + } + + @Test + public final void testExplainSelect() throws Exception { + // explain select l_orderkey, l_partkey from lineitem; + testingCluster.getConfiguration().set(ConfVars.$TEST_PLAN_SHAPE_FIX_ENABLED.varname, "true"); + try { + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } finally { + testingCluster.getConfiguration().set(ConfVars.$TEST_PLAN_SHAPE_FIX_ENABLED.varname, "false"); + } + } + + @Test + @SimpleTest(queries = { + @QuerySpec("explain global select l_orderkey, l_partkey from lineitem"), + @QuerySpec("explain global 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"), + @QuerySpec("explain global select l_linenumber, count(*), count(distinct l_orderkey), sum(distinct l_orderkey) from lineitem " + + "group by l_linenumber having sum(distinct l_orderkey) = 6")}) + public final void testExplainSelectPhysical() throws Exception { + runSimpleTests(); + } + + @Test + public final void testSelect() throws Exception { + // select l_orderkey, l_partkey from lineitem; + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testSelect2() throws Exception { + // select l_orderkey, l_partkey, l_orderkey + l_partkey as plus from lineitem; + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testSelect3() throws Exception { + // select l_orderkey + l_partkey as plus from lineitem; + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testSelectColumnAlias1() throws Exception { + // select l_orderkey as col1, l_orderkey + 1 as col2 from lineitem; + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testSelectColumnAliasExistingInRelation1() throws Exception { + // We intend that 'l_orderkey' in where clause points to "default.lineitem.l_orderkey" + // select (l_orderkey + l_orderkey) l_orderkey from lineitem where l_orderkey > 2; + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testSelectColumnAliasExistingInRelation2() throws Exception { + // We intend that 'l_orderkey' in orderby clause points to (-l_orderkey). + // select (-l_orderkey) as l_orderkey from lineitem order by l_orderkey; + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testSelectColumnAliasExistingInRelation3() throws Exception { + // This is a reproduction code and validator of TAJO-975 Bug + // Please see TAJO-975 in order to know this test in detail. + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + + @Test + public final void testSelectSameConstantsWithDifferentAliases() throws Exception { + // select l_orderkey, '20130819' as date1, '20130819' as date2 from lineitem where l_orderkey > -1; + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testSelectSameConstantsWithDifferentAliases2() throws Exception { + // select l_orderkey, '20130819' as date1, '20130819' as date2, '20130819' as date3, '20130819' as date4 + // from lineitem where l_orderkey > -1; + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testSelectSameConstantsWithDifferentAliases3() throws Exception { + // select l_orderkey, '20130819' as date1, '20130819', '20130819', '20130819' + // from lineitem where l_orderkey > -1; + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testSelectSameExprsWithDifferentAliases() throws Exception { + // select l_orderkey, l_partkey + 1 as plus1, l_partkey + 1 as plus2 from lineitem where l_orderkey > -1; + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testWhereCond1() throws Exception { + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testWhereCond2() throws Exception { + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testWhereCondWithAlias1() throws Exception { + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testWhereCondWithAlias2() throws Exception { + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testSelectAsterisk1() throws Exception { + // select * from lineitem; + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testSelectAsterisk2() throws Exception { + // select * from lineitem where l_orderkey = 2; + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testSelectAsterisk3() throws Exception { + // select * from lineitem where l_orderkey % 2 = 0; + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testSelectAsterisk4() throws Exception { + // select length(l_comment), l_extendedprice * l_discount, *, l_tax * 10 from lineitem; + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testSelectAsterisk5() throws Exception { + // select * from (select l_orderkey, 1 from lineitem where l_orderkey % 2 = 0) t1; + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testSelectDistinct() throws Exception { + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testLikeClause() throws Exception { + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testStringCompare() throws Exception { + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testRealValueCompare() throws Exception { + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testCaseWhen() throws Exception { + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testCaseWhenWithoutElse() throws Exception { + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testNotEqual() throws Exception { + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testInClause() throws Exception { + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testInStrClause() throws Exception { + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testNotInStrClause() throws Exception { + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testNotInClause() throws Exception { + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testCreateAfterSelect() throws Exception { + ResultSet res = testBase.execute( + "create table orderkeys as select l_orderkey from lineitem"); + res.close(); + TajoTestingCluster cluster = testBase.getTestingCluster(); + CatalogService catalog = cluster.getMaster().getCatalog(); + assertTrue(catalog.existsTable(DEFAULT_DATABASE_NAME, "orderkeys")); + TableDesc orderKeys = catalog.getTableDesc(DEFAULT_DATABASE_NAME, "orderkeys"); + if (!cluster.isHiveCatalogStoreRunning()) { + assertEquals(5, orderKeys.getStats().getNumRows().intValue()); + } + } + + @Test + public final void testLimit() throws Exception { + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testSelectWithJson() throws Exception { + // select l_orderkey, l_partkey + 1 as plus1, l_partkey + 1 as plus2 from lineitem where l_orderkey > -1; + ResultSet res = executeJsonQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testDatabaseRef() throws Exception { + if (!testingCluster.isHiveCatalogStoreRunning()) { + executeString("CREATE DATABASE \"TestSelectQuery\"").close(); + executeString("CREATE TABLE \"TestSelectQuery\".\"LineItem\" AS SELECT * FROM default.lineitem" ).close(); + + ResultSet res = executeFile("testDatabaseRef1.sql"); + assertResultSet(res, "testDatabaseRef.result"); + cleanupQuery(res); + + res = executeFile("testDatabaseRef2.sql"); + assertResultSet(res, "testDatabaseRef.result"); + cleanupQuery(res); + + res = executeFile("testDatabaseRef3.sql"); + assertResultSet(res, "testDatabaseRef.result"); + cleanupQuery(res); + + executeString("DROP DATABASE \"TestSelectQuery\"").close(); + } + } + + @Test + public final void testSumIntOverflow() throws Exception { + // Test data's min value is 17 and number of rows is 5. + // 25264513 = 2147483647/17/5 + // result is 116,848,374,845 ==> int overflow + // select sum(cast(l_quantity * 25264513 as INT4)) from lineitem where l_quantity > 0; + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testSumFloatOverflow() throws Exception { + // Test data's min value is 21168.23 and number of rows is 5. + // 3.21506374375027E33 = 3.40282346638529E38/21168/ 5 + // result is 6.838452478692677E38 ==> float4 overflow + // select sum(cast(L_EXTENDEDPRICE * 3.21506374375027E33 as FLOAT4)) from lineitem where l_quantity > 0; + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + public static class RulesForErrorInjection extends BaseLogicalPlanRewriteRuleProvider { + public RulesForErrorInjection(TajoConf conf) { + super(conf); + } + + @Override + public Collection<Class<? extends LogicalPlanRewriteRule>> getPostRules() { + List<Class<? extends LogicalPlanRewriteRule>> addedRules = Lists.newArrayList(super.getPostRules()); + return addedRules; + } + } + + @Test + public final void testQueryMasterTaskInitError() throws Exception { + // In this testcase we can check that a TajoClient receives QueryMasterTask's init error message. + testingCluster.setAllWorkersConfValue(ConfVars.LOGICAL_PLAN_REWRITE_RULE_PROVIDER_CLASS.name(), + RulesForErrorInjection.class.getCanonicalName()); + + try { + // If client can't receive error status, thread runs forever. + Thread t = new Thread() { + public void run() { + try { + ClientProtos.SubmitQueryResponse response = client.executeQuery("select l_orderkey from lineitem"); + QueryStatus status = client.getQueryStatus(new QueryId(response.getQueryId())); + assertEquals(QueryState.QUERY_ERROR, status.getState()); + assertEquals(NullPointerException.class.getName(), status.getErrorMessage()); + } catch (Exception e) { + fail(e.getMessage()); + } + } + }; + + t.start(); + + for (int i = 0; i < 10; i++) { + Thread.sleep(1 * 1000); + if (!t.isAlive()) { + break; + } + } + + // If query runs more than 10 secs, test is fail. + assertFalse(t.isAlive()); + } finally { + // recover the rewrite rule provider to default + testingCluster.setAllWorkersConfValue(ConfVars.LOGICAL_PLAN_REWRITE_RULE_PROVIDER_CLASS.name(), ""); + } + } + + @Test + public final void testNowInMultipleTasks() throws Exception { + KeyValueSet tableOptions = new KeyValueSet(); + tableOptions.set(StorageConstants.TEXT_DELIMITER, StorageConstants.DEFAULT_FIELD_DELIMITER); + tableOptions.set(StorageConstants.TEXT_NULL, "\\\\N"); + + Schema schema = new Schema(); + schema.addColumn("id", Type.INT4); + schema.addColumn("name", Type.TEXT); + String[] data = new String[]{ "1|table11-1", "2|table11-2", "3|table11-3", "4|table11-4", "5|table11-5" }; + TajoTestingCluster.createTable("testNowInMultipleTasks".toLowerCase(), schema, tableOptions, data, 2); + + try { + testingCluster.setAllTajoDaemonConfValue(ConfVars.$TEST_MIN_TASK_NUM.varname, "2"); + + ResultSet res = executeString("select concat(substr(to_char(now(),'yyyymmddhh24miss'), 1, 14), 'aaa'), sleep(1) " + + "from testNowInMultipleTasks"); + + String nowValue = null; + int numRecords = 0; + while (res.next()) { + String currentNowValue = res.getString(1); + if (nowValue != null) { + assertTrue(nowValue + " is different to " + currentNowValue, nowValue.equals(currentNowValue)); + } + nowValue = currentNowValue; + numRecords++; + } + assertEquals(5, numRecords); + + res.close(); + + res = executeString("select concat(substr(to_char(current_timestamp,'yyyymmddhh24miss'), 1, 14), 'aaa'), sleep(1) " + + "from testNowInMultipleTasks"); + + nowValue = null; + numRecords = 0; + while (res.next()) { + String currentNowValue = res.getString(1); + if (nowValue != null) { + assertTrue(nowValue.equals(currentNowValue)); + } + nowValue = currentNowValue; + numRecords++; + } + assertEquals(5, numRecords); + } finally { + testingCluster.setAllTajoDaemonConfValue(ConfVars.$TEST_MIN_TASK_NUM.varname, + ConfVars.$TEST_MIN_TASK_NUM.defaultVal); + executeString("DROP TABLE testNowInMultipleTasks PURGE"); + } + } + + @Test + public void testCaseWhenRound() throws Exception { + /* + select * + from (select n_nationkey as key, + case + when n_nationkey > 6 then round((n_nationkey * 100 / 2.123) / (n_regionkey * 50 / 2.123), 2) else 100.0 end as val + from + nation + where + n_regionkey > 0 and n_nationkey > 0 + ) a + order by + a.key + */ + + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public void testColumnEqualityButNotJoinCondition1() throws Exception { + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public void testColumnEqualityButNotJoinCondition2() throws Exception { + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public void testTimezonedTable1() throws Exception { + // Table - GMT (No table property or no system timezone) + // Client - GMT (default client time zone is used if no TIME ZONE session variable is given.) + try { + executeDDL("datetime_table_ddl.sql", "timezoned", new String[]{"timezoned1"}); + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } finally { + executeString("DROP TABLE IF EXISTS timezoned1"); + } + } + + @Test + public void testTimezonedTable2() throws Exception { + // Table - timezone = GMT+9 + // Client - GMT (SET TIME ZONE 'GMT';) + try { + executeDDL("datetime_table_timezoned_ddl.sql", "timezoned", new String[]{"timezoned2"}); + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } finally { + executeString("DROP TABLE IF EXISTS timezoned2"); + } + } + + @Test + public void testTimezonedTable3() throws Exception { + // Table - timezone = GMT+9 + // Client - GMT+9 through TajoClient API + + Map<String,String> sessionVars = new HashMap<String, String>(); + sessionVars.put(SessionVars.TIMEZONE.name(), "GMT+9"); + getClient().updateSessionVariables(sessionVars); + + try { + executeDDL("datetime_table_timezoned_ddl.sql", "timezoned", new String[]{"timezoned3"}); + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } finally { + executeString("DROP TABLE IF EXISTS timezoned3"); + } + + getClient().unsetSessionVariables(Lists.newArrayList("TIMEZONE")); + } + + @Test + public void testTimezonedTable4() throws Exception { + // Table - timezone = GMT+9 + // Client - GMT+9 (SET TIME ZONE 'GMT+9';) + + try { + executeDDL("datetime_table_timezoned_ddl.sql", "timezoned", new String[]{"timezoned4"}); + ResultSet res = executeQuery(); + assertResultSet(res, "testTimezonedTable3.result"); + cleanupQuery(res); + } finally { + executeString("DROP TABLE IF EXISTS timezoned4"); + } + } + + @Test + public void testTimezonedTable5() throws Exception { + // Table - timezone = GMT+9 (by a specified system timezone) + // TajoClient uses JVM default timezone (GMT+9) + + try { + testingCluster.getConfiguration().setSystemTimezone(TimeZone.getTimeZone("GMT+9")); + + executeDDL("datetime_table_ddl.sql", "timezoned", new String[]{"timezoned5"}); + ResultSet res = executeQuery(); + assertResultSet(res, "testTimezonedTable3.result"); + cleanupQuery(res); + } finally { + executeString("DROP TABLE IF EXISTS timezoned5"); + + // restore the config + testingCluster.getConfiguration().setSystemTimezone(TimeZone.getTimeZone("GMT")); + } + } + + @Test + public void testMultiBytesDelimiter1() throws Exception { + executeDDL("multibytes_delimiter_table1_ddl.sql", "multibytes_delimiter1"); + try { + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } finally { + executeString("DROP TABLE table1"); + } + } + + @Test + public void testMultiBytesDelimiter2() throws Exception { + executeDDL("multibytes_delimiter_table2_ddl.sql", "multibytes_delimiter2"); + try { + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } finally { + executeString("DROP TABLE table2"); + } + } + + @Test + public void testMultiBytesDelimiter3() throws Exception { + executeDDL("multibytes_delimiter_table3_ddl.sql", "multibytes_delimiter1"); + try { + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } finally { + executeString("DROP TABLE table1"); + } + } + + @Test + public void testMultiBytesDelimiter4() throws Exception { + executeDDL("multibytes_delimiter_table4_ddl.sql", "multibytes_delimiter2"); + try { + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } finally { + executeString("DROP TABLE table2"); + } + } + + @Test + public void testSelectPythonFuncs() throws Exception { + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public void testSelectWithPredicateOnPythonFunc() throws Exception { + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public void testNestedPythonFunction() throws Exception { + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public void testSelectWithParentheses1() throws Exception { + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public void testSelectWithParentheses2() throws Exception { + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public void testSelectOnSessionTable() throws Exception { + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + @Option(withExplain = true) + @SimpleTest ( + queries = @QuerySpec("select * from nation where (n_regionkey = 1 or n_name is not null) and (n_regionkey = 1 or n_comment is not null)") + ) + public void testSelectWithCommonQuals1() throws Exception { + runSimpleTests(); + } + + @Test + @Option(withExplain = true) + @SimpleTest( + queries = @QuerySpec("select * from nation where (n_regionkey = 1 or n_name is not null) and (n_regionkey = 1 or n_name is not null)") + ) + public void testSelectWithCommonQuals2() throws Exception { + runSimpleTests(); + } + + @Test + @Option(withExplain = true) + @SimpleTest( + queries = @QuerySpec("select * from nation where (n_regionkey = 1 and n_name is not null) or (n_regionkey = 1 and n_comment is not null)") + ) + public void testSelectWithCommonQuals3() throws Exception { + runSimpleTests(); + } +} \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/java/org/apache/tajo/engine/query/TestSetSessionQuery.java ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/java/org/apache/tajo/engine/query/TestSetSessionQuery.java b/tajo-core-tests/src/test/java/org/apache/tajo/engine/query/TestSetSessionQuery.java new file mode 100644 index 0000000..358afbc --- /dev/null +++ b/tajo-core-tests/src/test/java/org/apache/tajo/engine/query/TestSetSessionQuery.java @@ -0,0 +1,80 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ + +package org.apache.tajo.engine.query; + +import org.apache.tajo.IntegrationTest; +import org.apache.tajo.QueryTestCaseBase; +import org.apache.tajo.TajoConstants; +import org.junit.Test; +import org.junit.experimental.categories.Category; + +import static org.junit.Assert.assertEquals; +import static org.junit.Assert.assertFalse; +import static org.junit.Assert.assertTrue; + +@Category(IntegrationTest.class) +public class TestSetSessionQuery extends QueryTestCaseBase { + + public TestSetSessionQuery() { + super(TajoConstants.DEFAULT_DATABASE_NAME); + } + + @Test + public final void testSetCatalog1() throws Exception { + executeString("CREATE DATABASE testsetcatalog;").close(); + assertEquals(TajoConstants.DEFAULT_DATABASE_NAME, getClient().getCurrentDatabase()); + executeString("SET CATALOG testsetcatalog").close(); + assertEquals("testsetcatalog", getClient().getCurrentDatabase()); + executeString("SET CATALOG \"default\"").close(); + executeString("DROP DATABASE testsetcatalog;").close(); + } + + @Test + public final void testSetCatalog2() throws Exception { + executeString("CREATE DATABASE \"testSetCatalog\";").close(); + assertEquals(TajoConstants.DEFAULT_DATABASE_NAME, getClient().getCurrentDatabase()); + executeString("SET CATALOG \"testSetCatalog\"").close(); + assertEquals("testSetCatalog", getClient().getCurrentDatabase()); + executeString("SET CATALOG \"default\"").close(); + executeString("DROP DATABASE \"testSetCatalog\";").close(); + } + + @Test + public final void testSetTimezone() throws Exception { + assertFalse(getClient().existSessionVariable("TIMEZONE")); + executeString("SET TIME ZONE 'GMT+9'").close(); + assertTrue(getClient().existSessionVariable("TIMEZONE")); + executeString("SET TIME ZONE to DEFAULT").close(); + } + + @Test + public final void testSetSession1() throws Exception { + assertFalse(getClient().existSessionVariable("key1")); + executeString("SET SESSION key1 to true").close(); + assertTrue(getClient().existSessionVariable("key1")); + + executeString("SET SESSION key1 to true").close(); + executeString("SET SESSION key2 to 'val1'").close(); + assertTrue(getClient().existSessionVariable("key1")); + assertTrue(getClient().existSessionVariable("key2")); + executeString("RESET key1").close(); + executeString("SET SESSION key2 to DEFAULT").close(); + assertFalse(getClient().existSessionVariable("key2")); + } +} \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/java/org/apache/tajo/engine/query/TestSortQuery.java ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/java/org/apache/tajo/engine/query/TestSortQuery.java b/tajo-core-tests/src/test/java/org/apache/tajo/engine/query/TestSortQuery.java new file mode 100644 index 0000000..9e632ab --- /dev/null +++ b/tajo-core-tests/src/test/java/org/apache/tajo/engine/query/TestSortQuery.java @@ -0,0 +1,377 @@ +/** + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ + +package org.apache.tajo.engine.query; + +import org.apache.tajo.IntegrationTest; +import org.apache.tajo.QueryTestCaseBase; +import org.apache.tajo.TajoConstants; +import org.apache.tajo.TajoTestingCluster; +import org.apache.tajo.catalog.Schema; +import org.apache.tajo.common.TajoDataTypes.Type; +import org.apache.tajo.conf.TajoConf.ConfVars; +import org.apache.tajo.storage.StorageConstants; +import org.apache.tajo.util.KeyValueSet; +import org.junit.Test; +import org.junit.experimental.categories.Category; + +import java.sql.ResultSet; + +import static org.junit.Assert.assertEquals; + +@Category(IntegrationTest.class) +public class TestSortQuery extends QueryTestCaseBase { + + public TestSortQuery() { + super(TajoConstants.DEFAULT_DATABASE_NAME); + } + + @Test + public final void testSort() throws Exception { + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testSortWithAlias1() throws Exception { + // select l_linenumber, l_orderkey as sortkey from lineitem order by sortkey; + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testSortWithAlias2() throws Exception { + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testSortWithAlias3() throws Exception { + ResultSet res = executeQuery(); + cleanupQuery(res); + } + + @Test + public final void testSortWithExpr1() throws Exception { + // select l_linenumber, l_orderkey as sortkey from lineitem order by l_orderkey + 1; + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testSortWithExpr2() throws Exception { + // select l_linenumber, l_orderkey as sortkey from lineitem order by l_linenumber, l_orderkey, (l_orderkey is null); + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testSortWithAliasButOriginalName() throws Exception { + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testSortDesc() throws Exception { + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + + @Test + public final void testSortFirstDesc() throws Exception { + try { + testingCluster.setAllTajoDaemonConfValue(ConfVars.$TEST_MIN_TASK_NUM.varname, "2"); + KeyValueSet tableOptions = new KeyValueSet(); + tableOptions.set(StorageConstants.TEXT_DELIMITER, StorageConstants.DEFAULT_FIELD_DELIMITER); + tableOptions.set(StorageConstants.TEXT_NULL, "\\\\N"); + + Schema schema = new Schema(); + schema.addColumn("col1", Type.INT4); + schema.addColumn("col2", Type.TEXT); + String[] data = new String[]{ + "1|abc", + "3|dfa", + "3|das", + "1|abb", + "1|abc", + "3|dfb", + "3|dat", + "1|abe" + }; + TajoTestingCluster.createTable("sortfirstdesc", schema, tableOptions, data, 2); + + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } finally { + testingCluster.setAllTajoDaemonConfValue(ConfVars.$TEST_MIN_TASK_NUM.varname, "0"); + executeString("DROP TABLE sortfirstdesc PURGE;").close(); + } + } + + + @Test + public final void testTopK() throws Exception { + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testSortAfterGroupby() throws Exception { + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testSortAfterGroupbyWithAlias() throws Exception { + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testSortWithDate() throws Exception { + // skip this test if catalog uses HiveCatalogStore. + // It is because HiveCatalogStore does not support Time data type. + + if (!testingCluster.isHiveCatalogStoreRunning()) { + // create external table table1 (col1 timestamp, col2 date, col3 time) ... + executeDDL("create_table_with_date_ddl.sql", "table1"); + + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + } + + @Test + public final void testAsterisk() throws Exception { + //select *, length(l_comment) as len_comment from lineitem order by len_comment; + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testSortWithAscDescKeys() throws Exception { + executeDDL("create_table_with_asc_desc_keys.sql", "table2"); + + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testSortWithJson() throws Exception { + // select max(l_quantity) as max_quantity, l_orderkey from lineitem group by l_orderkey order by max_quantity; + ResultSet res = executeJsonQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testTopkWithJson() throws Exception { + // select l_orderkey, l_linenumber from lineitem order by l_orderkey desc limit 3; + ResultSet res = executeJsonQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testSortOnNullColumn() throws Exception { + try { + testingCluster.setAllTajoDaemonConfValue(ConfVars.$TEST_MIN_TASK_NUM.varname, "2"); + KeyValueSet tableOptions = new KeyValueSet(); + tableOptions.set(StorageConstants.TEXT_DELIMITER, StorageConstants.DEFAULT_FIELD_DELIMITER); + tableOptions.set(StorageConstants.TEXT_NULL, "\\\\N"); + + Schema schema = new Schema(); + schema.addColumn("id", Type.INT4); + schema.addColumn("name", Type.TEXT); + String[] data = new String[]{ + "1|BRAZIL", + "2|ALGERIA", + "3|ARGENTINA", + "4|CANADA" + }; + TajoTestingCluster.createTable("nullsort", schema, tableOptions, data, 2); + + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } finally { + testingCluster.setAllTajoDaemonConfValue(ConfVars.$TEST_MIN_TASK_NUM.varname, "0"); + executeString("DROP TABLE nullsort PURGE;").close(); + } + } + + @Test + public final void testSortOnNullColumn2() throws Exception { + KeyValueSet tableOptions = new KeyValueSet(); + tableOptions.set(StorageConstants.TEXT_DELIMITER, StorageConstants.DEFAULT_FIELD_DELIMITER); + tableOptions.set(StorageConstants.TEXT_NULL, "\\\\N"); + + Schema schema = new Schema(); + schema.addColumn("id", Type.INT4); + schema.addColumn("name", Type.TEXT); + String[] data = new String[]{ "1|111", "2|\\N", "3|333" }; + TajoTestingCluster.createTable("testSortOnNullColumn2".toLowerCase(), schema, tableOptions, data, 1); + + try { + ResultSet res = executeString("select * from testSortOnNullColumn2 order by name asc"); + String ascExpected = "id,name\n" + + "-------------------------------\n" + + "1,111\n" + + "3,333\n" + + "2,null\n"; + + assertEquals(ascExpected, resultSetToString(res)); + res.close(); + + res = executeString("select * from testSortOnNullColumn2 order by name desc"); + String descExpected = "id,name\n" + + "-------------------------------\n" + + "2,null\n" + + "3,333\n" + + "1,111\n"; + + assertEquals(descExpected, resultSetToString(res)); + res.close(); + } finally { + executeString("DROP TABLE testSortOnNullColumn2 PURGE"); + } + } + + @Test + public final void testSortOnNullColumn3() throws Exception { + KeyValueSet tableOptions = new KeyValueSet(); + tableOptions.set(StorageConstants.TEXT_DELIMITER, StorageConstants.DEFAULT_FIELD_DELIMITER); + tableOptions.set(StorageConstants.TEXT_NULL, "\\\\N"); + + Schema schema = new Schema(); + schema.addColumn("id", Type.INT4); + schema.addColumn("name", Type.TEXT); + String[] data = new String[]{ "1|111", "2|\\N", "3|333" }; + TajoTestingCluster.createTable("testSortOnNullColumn3".toLowerCase(), schema, tableOptions, data, 1); + + try { + ResultSet res = executeString("select * from testSortOnNullColumn3 order by name null first"); + String ascExpected = "id,name\n" + + "-------------------------------\n" + + "2,null\n" + + "1,111\n" + + "3,333\n"; + + assertEquals(ascExpected, resultSetToString(res)); + res.close(); + + } finally { + executeString("DROP TABLE testSortOnNullColumn3 PURGE"); + } + } + + @Test + public final void testSortOnUnicodeTextAsc() throws Exception { + try { + testingCluster.setAllTajoDaemonConfValue(ConfVars.$TEST_MIN_TASK_NUM.varname, "2"); + KeyValueSet tableOptions = new KeyValueSet(); + tableOptions.set(StorageConstants.TEXT_DELIMITER, StorageConstants.DEFAULT_FIELD_DELIMITER); + tableOptions.set(StorageConstants.TEXT_NULL, "\\\\N"); + + Schema schema = new Schema(); + schema.addColumn("col1", Type.INT4); + schema.addColumn("col2", Type.TEXT); + String[] data = new String[]{ + "1|ííí", + "2|캬캬캬", + "3|ê°ê°ê°", + "4|ëíí" + }; + TajoTestingCluster.createTable("unicode_sort1", schema, tableOptions, data, 2); + + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } finally { + testingCluster.setAllTajoDaemonConfValue(ConfVars.$TEST_MIN_TASK_NUM.varname, "0"); + executeString("DROP TABLE unicode_sort1 PURGE;").close(); + } + } + + @Test + public final void testSortOnUnicodeTextDesc() throws Exception { + try { + testingCluster.setAllTajoDaemonConfValue(ConfVars.$TEST_MIN_TASK_NUM.varname, "2"); + KeyValueSet tableOptions = new KeyValueSet(); + tableOptions.set(StorageConstants.TEXT_DELIMITER, StorageConstants.DEFAULT_FIELD_DELIMITER); + tableOptions.set(StorageConstants.TEXT_NULL, "\\\\N"); + + Schema schema = new Schema(); + schema.addColumn("col1", Type.INT4); + schema.addColumn("col2", Type.TEXT); + String[] data = new String[]{ + "1|ííí", + "2|캬캬캬", + "3|ê°ê°ê°", + "4|ëíí" + }; + TajoTestingCluster.createTable("unicode_sort2", schema, tableOptions, data, 2); + + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } finally { + testingCluster.setAllTajoDaemonConfValue(ConfVars.$TEST_MIN_TASK_NUM.varname, "0"); + executeString("DROP TABLE unicode_sort2 PURGE;").close(); + } + } + + @Test + public final void testSortWithConstKeys() throws Exception { + // select + // l_orderkey, + // l_linenumber, + // 1 as key1, + // 2 as key2 + // from + // lineitem + // order by + // key1, + // key2; + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + @Option(withExplain = true, withExplainGlobal = true) + @SimpleTest() + public final void testSubQuerySortAfterGroupMultiBlocks() throws Exception { + runSimpleTests(); + } +}
