http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/java/org/apache/tajo/engine/function/TestBuiltinFunctions.java ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/java/org/apache/tajo/engine/function/TestBuiltinFunctions.java b/tajo-core-tests/src/test/java/org/apache/tajo/engine/function/TestBuiltinFunctions.java new file mode 100644 index 0000000..72fdd6f --- /dev/null +++ b/tajo-core-tests/src/test/java/org/apache/tajo/engine/function/TestBuiltinFunctions.java @@ -0,0 +1,824 @@ +/** + * 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.function; + +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; +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 TestBuiltinFunctions extends QueryTestCaseBase { + + public TestBuiltinFunctions() { + super(TajoConstants.DEFAULT_DATABASE_NAME); + } + + @Test + public void testMaxLong() throws Exception { + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public void testMaxLongWithNull() 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", TajoDataTypes.Type.INT4); + schema.addColumn("value", TajoDataTypes.Type.INT8); + String[] data = new String[]{ "1|-111", "2|\\N", "3|-333" }; + TajoTestingCluster.createTable("testbuiltin11", schema, tableOptions, data, 1); + + try { + ResultSet res = executeString("select max(value) as max_value from testbuiltin11"); + String ascExpected = "max_value\n" + + "-------------------------------\n" + + "-111\n"; + + assertEquals(ascExpected, resultSetToString(res)); + res.close(); + } finally { + executeString("DROP TABLE testbuiltin11 PURGE"); + } + + } + + @Test + public void testMinMaxDate() 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("value", TajoDataTypes.Type.DATE); + String[] data = new String[]{ "2014-01-02", "2014-12-01", "2015-01-01", "1999-08-09", "2000-03-01" }; + TajoTestingCluster.createTable("testbuiltin11", schema, tableOptions, data, 1); + + try { + ResultSet res = executeString("select min(value) as min_value, max(value) as max_value from testbuiltin11"); + String ascExpected = "min_value,max_value\n" + + "-------------------------------\n" + + "1999-08-09,2015-01-01\n"; + + assertEquals(ascExpected, resultSetToString(res)); + res.close(); + } finally { + executeString("DROP TABLE testbuiltin11 PURGE"); + } + } + + @Test + public void testMinMaxDateWithNull() 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("value", TajoDataTypes.Type.DATE); + String[] data = new String[]{ "2014-01-02", "2014-12-01", "\\N", "\\N", "2000-03-01" }; + TajoTestingCluster.createTable("testbuiltin11", schema, tableOptions, data, 1); + + try { + ResultSet res = executeString("select min(value) as min_value, max(value) as max_value from testbuiltin11"); + String ascExpected = "min_value,max_value\n" + + "-------------------------------\n" + + "2000-03-01,2014-12-01\n"; + + assertEquals(ascExpected, resultSetToString(res)); + res.close(); + } finally { + executeString("DROP TABLE testbuiltin11 PURGE"); + } + } + + @Test + public void testMinMaxTime() 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("value", TajoDataTypes.Type.TIME); + String[] data = new String[]{ "11:11:11", "23:12:50", "00:00:01", "09:59:59", "12:13:14" }; + TajoTestingCluster.createTable("testbuiltin11", schema, tableOptions, data, 1); + + try { + ResultSet res = executeString("select min(value) as min_value, max(value) as max_value from testbuiltin11"); + String ascExpected = "min_value,max_value\n" + + "-------------------------------\n" + + "00:00:01,23:12:50\n"; + + assertEquals(ascExpected, resultSetToString(res)); + res.close(); + } finally { + executeString("DROP TABLE testbuiltin11 PURGE"); + } + } + + @Test + public void testMinMaxTimeWithNull() 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("value", TajoDataTypes.Type.TIME); + String[] data = new String[]{ "11:11:11", "\\N", "\\N", "09:59:59", "12:13:14" }; + TajoTestingCluster.createTable("testbuiltin11", schema, tableOptions, data, 1); + + try { + ResultSet res = executeString("select min(value) as min_value, max(value) as max_value from testbuiltin11"); + String ascExpected = "min_value,max_value\n" + + "-------------------------------\n" + + "09:59:59,12:13:14\n"; + + assertEquals(ascExpected, resultSetToString(res)); + res.close(); + } finally { + executeString("DROP TABLE testbuiltin11 PURGE"); + } + } + + @Test + public void testMinMaxTimestamp() 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("value", TajoDataTypes.Type.TIMESTAMP); + String[] data = new String[]{ "1999-01-01 11:11:11", "2015-01-01 23:12:50", "2016-12-24 00:00:01", + "1977-05-04 09:59:59", "2002-11-21 12:13:14" }; + TajoTestingCluster.createTable("testbuiltin11", schema, tableOptions, data, 1); + + try { + ResultSet res = executeString("select min(value) as min_value, max(value) as max_value from testbuiltin11"); + String ascExpected = "min_value,max_value\n" + + "-------------------------------\n" + + "1977-05-04 09:59:59,2016-12-24 00:00:01\n"; + + assertEquals(ascExpected, resultSetToString(res)); + res.close(); + } finally { + executeString("DROP TABLE testbuiltin11 PURGE"); + } + } + + @Test + public void testMinMaxTimestampWithNull() 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("value", TajoDataTypes.Type.TIMESTAMP); + String[] data = new String[]{ "1999-01-01 11:11:11", "2015-01-01 23:12:50", "\\N", + "\\N", "2002-11-21 12:13:14" }; + TajoTestingCluster.createTable("testbuiltin11", schema, tableOptions, data, 1); + + try { + ResultSet res = executeString("select min(value) as min_value, max(value) as max_value from testbuiltin11"); + String ascExpected = "min_value,max_value\n" + + "-------------------------------\n" + + "1999-01-01 11:11:11,2015-01-01 23:12:50\n"; + + assertEquals(ascExpected, resultSetToString(res)); + res.close(); + } finally { + executeString("DROP TABLE testbuiltin11 PURGE"); + } + } + + @Test + public void testMinLong() throws Exception { + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public void testMinLongWithNull() 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", TajoDataTypes.Type.INT4); + schema.addColumn("value", TajoDataTypes.Type.INT8); + String[] data = new String[]{ "1|111", "2|\\N", "3|333" }; + TajoTestingCluster.createTable("testbuiltin11", schema, tableOptions, data, 1); + + try { + ResultSet res = executeString("select min(value) as min_value from testbuiltin11"); + String ascExpected = "min_value\n" + + "-------------------------------\n" + + "111\n"; + + assertEquals(ascExpected, resultSetToString(res)); + res.close(); + } finally { + executeString("DROP TABLE testbuiltin11 PURGE"); + } + + } + + @Test + public void testMaxString() throws Exception { + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public void testMaxStringWithNull() 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", TajoDataTypes.Type.INT4); + schema.addColumn("name", TajoDataTypes.Type.TEXT); + String[] data = new String[]{ "1|\\N", "2|\\N", "3|\\N" }; + TajoTestingCluster.createTable("testbuiltin11", schema, tableOptions, data, 1); + + try { + ResultSet res = executeString("select max(name) as max_name from testbuiltin11"); + String ascExpected = "max_name\n" + + "-------------------------------\n" + + "null\n"; + + assertEquals(ascExpected, resultSetToString(res)); + res.close(); + } finally { + executeString("DROP TABLE testbuiltin11 PURGE"); + } + + } + + @Test + public void testMinString() throws Exception { + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public void testMinStringWithNull() 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", TajoDataTypes.Type.INT4); + schema.addColumn("name", TajoDataTypes.Type.TEXT); + String[] data = new String[]{ "1|def", "2|\\N", "3|abc" }; + TajoTestingCluster.createTable("testbuiltin11", schema, tableOptions, data, 1); + + try { + ResultSet res = executeString("select min(name) as min_name from testbuiltin11"); + String ascExpected = "min_name\n" + + "-------------------------------\n" + + "abc\n"; + + assertEquals(ascExpected, resultSetToString(res)); + res.close(); + } finally { + executeString("DROP TABLE testbuiltin11 PURGE"); + } + + } + + @Test + public void testCount() throws Exception { + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public void testAvgDouble() throws Exception { + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public void testAvgLong() throws Exception { + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public void testAvgInt() throws Exception { + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public void testAvgLongOverflow() throws Exception { + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public void testAvgWithNull() 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", TajoDataTypes.Type.INT4); + schema.addColumn("value_int", TajoDataTypes.Type.INT4); + schema.addColumn("value_long", TajoDataTypes.Type.INT8); + schema.addColumn("value_float", TajoDataTypes.Type.FLOAT4); + schema.addColumn("value_double", TajoDataTypes.Type.FLOAT8); + String[] data = new String[]{ "1|\\N|-111|1.2|-50.5", "2|1|\\N|\\N|52.5", "3|2|-333|2.8|\\N" }; + TajoTestingCluster.createTable("testbuiltin11", schema, tableOptions, data, 1); + + try { + ResultSet res = executeString("select avg(value_int) as avg_int, avg(value_long) as avg_long, avg(value_float) as avg_float, avg(value_double) as avg_double from testbuiltin11"); + String ascExpected = "avg_int,avg_long,avg_float,avg_double\n" + + "-------------------------------\n" + + "1.5,-222.0,2.0,1.0\n"; + + assertEquals(ascExpected, resultSetToString(res)); + res.close(); + } finally { + executeString("DROP TABLE testbuiltin11 PURGE"); + } + + } + + @Test + public void testAvgWithAllNulls() 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", TajoDataTypes.Type.INT4); + schema.addColumn("value_int", TajoDataTypes.Type.INT4); + schema.addColumn("value_long", TajoDataTypes.Type.INT8); + schema.addColumn("value_float", TajoDataTypes.Type.FLOAT4); + schema.addColumn("value_double", TajoDataTypes.Type.FLOAT8); + String[] data = new String[]{ "1|\\N|\\N|\\N|\\N", "2|\\N|\\N|\\N|\\N", "3|\\N|\\N|\\N|\\N" }; + TajoTestingCluster.createTable("testbuiltin11", schema, tableOptions, data, 1); + + try { + ResultSet res = executeString("select avg(value_int) as avg_int, avg(value_long) as avg_long, avg(value_float) as avg_float, avg(value_double) as avg_double from testbuiltin11"); + String ascExpected = "avg_int,avg_long,avg_float,avg_double\n" + + "-------------------------------\n" + + "null,null,null,null\n"; + + assertEquals(ascExpected, resultSetToString(res)); + res.close(); + } finally { + executeString("DROP TABLE testbuiltin11 PURGE"); + } + + } + + @Test + public void testSumWithNull() 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", TajoDataTypes.Type.INT4); + schema.addColumn("value_int", TajoDataTypes.Type.INT4); + schema.addColumn("value_long", TajoDataTypes.Type.INT8); + schema.addColumn("value_float", TajoDataTypes.Type.FLOAT4); + schema.addColumn("value_double", TajoDataTypes.Type.FLOAT8); + String[] data = new String[]{ "1|\\N|-111|1.2|-50.5", "2|1|\\N|\\N|52.5", "3|2|-333|2.8|\\N" }; + TajoTestingCluster.createTable("testbuiltin11", schema, tableOptions, data, 1); + + try { + ResultSet res = executeString("select sum(value_int) as sum_int, sum(value_long) as sum_long, sum(value_float) as sum_float, sum(value_double) as sum_double from testbuiltin11"); + String ascExpected = "sum_int,sum_long,sum_float,sum_double\n" + + "-------------------------------\n" + + "3,-444,4.0,2.0\n"; + + assertEquals(ascExpected, resultSetToString(res)); + res.close(); + } finally { + executeString("DROP TABLE testbuiltin11 PURGE"); + } + + } + + @Test + public void testSumWithAllNulls() 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", TajoDataTypes.Type.INT4); + schema.addColumn("value_int", TajoDataTypes.Type.INT4); + schema.addColumn("value_long", TajoDataTypes.Type.INT8); + schema.addColumn("value_float", TajoDataTypes.Type.FLOAT4); + schema.addColumn("value_double", TajoDataTypes.Type.FLOAT8); + String[] data = new String[]{ "1|\\N|\\N|\\N|\\N", "2|\\N|\\N|\\N|\\N", "3|\\N|\\N|\\N|\\N" }; + TajoTestingCluster.createTable("testbuiltin11", schema, tableOptions, data, 1); + + try { + ResultSet res = executeString("select sum(value_int) as sum_int, sum(value_long) as sum_long, sum(value_float) as sum_float, sum(value_double) as sum_double from testbuiltin11"); + String ascExpected = "sum_int,sum_long,sum_float,sum_double\n" + + "-------------------------------\n" + + "null,null,null,null\n"; + + assertEquals(ascExpected, resultSetToString(res)); + res.close(); + } finally { + executeString("DROP TABLE testbuiltin11 PURGE"); + } + + } + + @Test + public void testStdDevSamp() 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", TajoDataTypes.Type.INT4); + schema.addColumn("value_int", TajoDataTypes.Type.INT4); + schema.addColumn("value_long", TajoDataTypes.Type.INT8); + schema.addColumn("value_float", TajoDataTypes.Type.FLOAT4); + schema.addColumn("value_double", TajoDataTypes.Type.FLOAT8); + String[] data = new String[]{ + "1|\\N|-111|1.2|-50.5", + "2|1|\\N|\\N|52.5", + "3|2|-333|2.8|\\N" }; + TajoTestingCluster.createTable("testbuiltin11", schema, tableOptions, data, 1); + + try { + ResultSet res = executeString("select stddev_samp(value_int) as sdsamp_int, stddev_samp(value_long) as sdsamp_long, stddev_samp(value_float) as sdsamp_float, stddev_samp(value_double) as sdsamp_double from testbuiltin11"); + String ascExpected = "sdsamp_int,sdsamp_long,sdsamp_float,sdsamp_double\n" + + "-------------------------------\n" + + "0.7071067811865476,156.97770542341354,1.1313707824635184,72.8319984622144\n"; + + assertEquals(ascExpected, resultSetToString(res)); + res.close(); + } finally { + executeString("DROP TABLE testbuiltin11 PURGE"); + } + + } + + @Test + public void testStdDevSampWithFewNumbers() 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", TajoDataTypes.Type.INT4); + schema.addColumn("value_int", TajoDataTypes.Type.INT4); + schema.addColumn("value_long", TajoDataTypes.Type.INT8); + schema.addColumn("value_float", TajoDataTypes.Type.FLOAT4); + schema.addColumn("value_double", TajoDataTypes.Type.FLOAT8); + String[] data = new String[]{ + "1|\\N|\\N|\\N|-50.5", + "2|1|\\N|\\N|\\N", + "3|\\N|\\N|\\N|\\N" }; + TajoTestingCluster.createTable("testbuiltin11", schema, tableOptions, data, 1); + + try { + ResultSet res = executeString("select stddev_samp(value_int) as sdsamp_int, stddev_samp(value_long) as sdsamp_long, stddev_samp(value_float) as sdsamp_float, stddev_samp(value_double) as sdsamp_double from testbuiltin11"); + String ascExpected = "sdsamp_int,sdsamp_long,sdsamp_float,sdsamp_double\n" + + "-------------------------------\n" + + "null,null,null,null\n"; + + assertEquals(ascExpected, resultSetToString(res)); + res.close(); + } finally { + executeString("DROP TABLE testbuiltin11 PURGE"); + } + + } + + @Test + public void testStdDevPop() 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", TajoDataTypes.Type.INT4); + schema.addColumn("value_int", TajoDataTypes.Type.INT4); + schema.addColumn("value_long", TajoDataTypes.Type.INT8); + schema.addColumn("value_float", TajoDataTypes.Type.FLOAT4); + schema.addColumn("value_double", TajoDataTypes.Type.FLOAT8); + String[] data = new String[]{ + "1|\\N|-111|1.2|-50.5", + "2|1|\\N|\\N|52.5", + "3|2|-333|2.8|\\N" }; + TajoTestingCluster.createTable("testbuiltin11", schema, tableOptions, data, 1); + + try { + ResultSet res = executeString("select stddev_pop(value_int) as sdpop_int, stddev_pop(value_long) as sdpop_long, stddev_pop(value_float) as sdpop_float, stddev_pop(value_double) as sdpop_double from testbuiltin11"); + String ascExpected = "sdpop_int,sdpop_long,sdpop_float,sdpop_double\n" + + "-------------------------------\n" + + "0.5,111.0,0.7999999523162842,51.5\n"; + + assertEquals(ascExpected, resultSetToString(res)); + res.close(); + } finally { + executeString("DROP TABLE testbuiltin11 PURGE"); + } + + } + + @Test + public void testStdDevPopWithFewNumbers() 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", TajoDataTypes.Type.INT4); + schema.addColumn("value_int", TajoDataTypes.Type.INT4); + schema.addColumn("value_long", TajoDataTypes.Type.INT8); + schema.addColumn("value_float", TajoDataTypes.Type.FLOAT4); + schema.addColumn("value_double", TajoDataTypes.Type.FLOAT8); + String[] data = new String[]{ + "1|\\N|\\N|\\N|-50.5", + "2|1|\\N|\\N|\\N", + "3|\\N|\\N|\\N|\\N" }; + TajoTestingCluster.createTable("testbuiltin11", schema, tableOptions, data, 1); + + try { + ResultSet res = executeString("select stddev_pop(value_int) as sdpop_int, stddev_pop(value_long) as sdpop_long, stddev_pop(value_float) as sdpop_float, stddev_pop(value_double) as sdpop_double from testbuiltin11"); + String ascExpected = "sdpop_int,sdpop_long,sdpop_float,sdpop_double\n" + + "-------------------------------\n" + + "0.0,null,null,0.0\n"; + + assertEquals(ascExpected, resultSetToString(res)); + res.close(); + } finally { + executeString("DROP TABLE testbuiltin11 PURGE"); + } + + } + + @Test + public void testVarSamp() 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", TajoDataTypes.Type.INT4); + schema.addColumn("value_int", TajoDataTypes.Type.INT4); + schema.addColumn("value_long", TajoDataTypes.Type.INT8); + schema.addColumn("value_float", TajoDataTypes.Type.FLOAT4); + schema.addColumn("value_double", TajoDataTypes.Type.FLOAT8); + String[] data = new String[]{ + "1|\\N|-111|1.2|-50.5", + "2|1|\\N|\\N|52.5", + "3|2|-333|2.8|\\N" }; + TajoTestingCluster.createTable("testbuiltin11", schema, tableOptions, data, 1); + + try { + ResultSet res = executeString("select var_samp(value_int) as vs_int, var_samp(value_long) as vs_long, var_samp(value_float) as vs_float, var_samp(value_double) as vs_double from testbuiltin11"); + String ascExpected = "vs_int,vs_long,vs_float,vs_double\n" + + "-------------------------------\n" + + "0.5,24642.0,1.279999847412114,5304.5\n"; + + assertEquals(ascExpected, resultSetToString(res)); + res.close(); + } finally { + executeString("DROP TABLE testbuiltin11 PURGE"); + } + } + + @Test + public void testVarSampWithFewNumbers() 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", TajoDataTypes.Type.INT4); + schema.addColumn("value_int", TajoDataTypes.Type.INT4); + schema.addColumn("value_long", TajoDataTypes.Type.INT8); + schema.addColumn("value_float", TajoDataTypes.Type.FLOAT4); + schema.addColumn("value_double", TajoDataTypes.Type.FLOAT8); + String[] data = new String[]{ + "1|\\N|\\N|\\N|-50.5", + "2|1|\\N|\\N|\\N", + "3|\\N|\\N|\\N|\\N" }; + TajoTestingCluster.createTable("testbuiltin11", schema, tableOptions, data, 1); + + try { + ResultSet res = executeString("select var_samp(value_int) as vsamp_int, var_samp(value_long) as vsamp_long, var_samp(value_float) as vsamp_float, var_samp(value_double) as vsamp_double from testbuiltin11"); + String ascExpected = "vsamp_int,vsamp_long,vsamp_float,vsamp_double\n" + + "-------------------------------\n" + + "null,null,null,null\n"; + + assertEquals(ascExpected, resultSetToString(res)); + res.close(); + } finally { + executeString("DROP TABLE testbuiltin11 PURGE"); + } + } + + @Test + public void testVarPop() 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", TajoDataTypes.Type.INT4); + schema.addColumn("value_int", TajoDataTypes.Type.INT4); + schema.addColumn("value_long", TajoDataTypes.Type.INT8); + schema.addColumn("value_float", TajoDataTypes.Type.FLOAT4); + schema.addColumn("value_double", TajoDataTypes.Type.FLOAT8); + String[] data = new String[]{ + "1|\\N|-111|1.2|-50.5", + "2|1|\\N|\\N|52.5", + "3|2|-333|2.8|\\N" }; + TajoTestingCluster.createTable("testbuiltin11", schema, tableOptions, data, 1); + + try { + ResultSet res = executeString("select var_pop(value_int) as vpop_int, var_pop(value_long) as vpop_long, var_pop(value_float) as vpop_float, var_pop(value_double) as vpop_double from testbuiltin11"); + String ascExpected = "vpop_int,vpop_long,vpop_float,vpop_double\n" + + "-------------------------------\n" + + "0.25,12321.0,0.639999923706057,2652.25\n"; + + assertEquals(ascExpected, resultSetToString(res)); + res.close(); + } finally { + executeString("DROP TABLE testbuiltin11 PURGE"); + } + } + + @Test + public void testVarPopWithFewNumbers() 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", TajoDataTypes.Type.INT4); + schema.addColumn("value_int", TajoDataTypes.Type.INT4); + schema.addColumn("value_long", TajoDataTypes.Type.INT8); + schema.addColumn("value_float", TajoDataTypes.Type.FLOAT4); + schema.addColumn("value_double", TajoDataTypes.Type.FLOAT8); + String[] data = new String[]{ + "1|\\N|\\N|\\N|-50.5", + "2|1|\\N|\\N|\\N", + "3|\\N|\\N|\\N|\\N" }; + TajoTestingCluster.createTable("testbuiltin11", schema, tableOptions, data, 1); + + try { + ResultSet res = executeString("select var_pop(value_int) as vpop_int, var_pop(value_long) as vpop_long, var_pop(value_float) as vpop_float, var_pop(value_double) as vpop_double from testbuiltin11"); + String ascExpected = "vpop_int,vpop_long,vpop_float,vpop_double\n" + + "-------------------------------\n" + + "0.0,null,null,0.0\n"; + + assertEquals(ascExpected, resultSetToString(res)); + res.close(); + } finally { + executeString("DROP TABLE testbuiltin11 PURGE"); + } + } + +// @Test +// public void testRandom() throws Exception { +// ResultSet res = executeQuery(); +// while(res.next()) { +// assertTrue(res.getInt(2) >= 0 && res.getInt(2) < 3); +// } +// cleanupQuery(res); +// } + + @Test + public void testSplitPart() throws Exception { + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public void testSplitPartByString() throws Exception { + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public void testSplitPartNested() throws Exception { + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public void testRankWithTwoTables() 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", TajoDataTypes.Type.INT4); + String[] data = new String[] {"1", "3", "2", "4"}; + TajoTestingCluster.createTable("rank_table1", schema, tableOptions, data, 1); + schema = new Schema(); + schema.addColumn("refid", TajoDataTypes.Type.INT4); + schema.addColumn("value", TajoDataTypes.Type.TEXT); + data = new String[] {"1|efgh", "2|abcd", "4|erjk", "8|dfef"}; + TajoTestingCluster.createTable("rank_table2", schema, tableOptions, data, 1); + ResultSet res = null; + + try { + res = executeString("select rank() over (order by id) from rank_table1 a, rank_table2 b " + + " where a.id = b.refid"); + String expectedString = "?windowfunction\n" + + "-------------------------------\n" + + "1\n" + + "2\n" + + "3\n"; + + assertEquals(expectedString, resultSetToString(res)); + } finally { + if (res != null) { + try { + res.close(); + } catch(Throwable ignored) {} + } + executeString("DROP TABLE rank_table1 PURGE"); + executeString("DROP TABLE rank_table2 PURGE"); + } + } + + @Test + public void testCorr() 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", TajoDataTypes.Type.INT4); + schema.addColumn("value_int", TajoDataTypes.Type.INT4); + schema.addColumn("value_long", TajoDataTypes.Type.INT8); + schema.addColumn("value_float", TajoDataTypes.Type.FLOAT4); + schema.addColumn("value_double", TajoDataTypes.Type.FLOAT8); + String[] data = new String[]{ + "1|\\N|-111|1.2|-50.5", + "2|1|\\N|\\N|52.5", + "3|2|-333|2.8|\\N", + "4|3|-555|2.8|43.2", + "5|4|-111|1.1|10.2",}; + TajoTestingCluster.createTable("testbuiltin11", schema, tableOptions, data, 1); + + try { + ResultSet res = executeString("select corr(value_int, value_long) as corr1, corr(value_long, value_float) as corr2, corr(value_float, value_double) as corr3, corr(value_double, value_int) as corr4 from testbuiltin11"); + String ascExpected = "corr1,corr2,corr3,corr4\n" + + "-------------------------------\n" + + "0.5,-0.9037045658322675,0.7350290063698216,-0.8761489936497805\n"; + + assertEquals(ascExpected, resultSetToString(res)); + res.close(); + } finally { + executeString("DROP TABLE testbuiltin11 PURGE"); + } + } +}
http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/java/org/apache/tajo/engine/function/TestConditionalExpressions.java ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/java/org/apache/tajo/engine/function/TestConditionalExpressions.java b/tajo-core-tests/src/test/java/org/apache/tajo/engine/function/TestConditionalExpressions.java new file mode 100644 index 0000000..c214f66 --- /dev/null +++ b/tajo-core-tests/src/test/java/org/apache/tajo/engine/function/TestConditionalExpressions.java @@ -0,0 +1,242 @@ +/** + * 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.function; + +import org.apache.tajo.catalog.CatalogUtil; +import org.apache.tajo.catalog.Schema; +import org.apache.tajo.exception.UndefinedFunctionException; +import org.apache.tajo.common.TajoDataTypes; +import org.apache.tajo.engine.eval.ExprTestBase; +import org.apache.tajo.exception.TajoException; +import org.junit.Test; + +import static org.junit.Assert.fail; + +public class TestConditionalExpressions extends ExprTestBase { + @Test + public void testCaseWhens1() throws TajoException { + Schema schema = new Schema(); + schema.addColumn("col1", TajoDataTypes.Type.INT1); + schema.addColumn("col2", TajoDataTypes.Type.INT2); + schema.addColumn("col3", TajoDataTypes.Type.INT4); + schema.addColumn("col4", TajoDataTypes.Type.INT8); + schema.addColumn("col5", TajoDataTypes.Type.FLOAT4); + schema.addColumn("col6", TajoDataTypes.Type.FLOAT8); + schema.addColumn("col7", TajoDataTypes.Type.TEXT); + schema.addColumn("col8", CatalogUtil.newDataType(TajoDataTypes.Type.CHAR, "", 3)); + schema.addColumn("col9", TajoDataTypes.Type.INT4); + + testEval(schema, "table1", "1,2,3,4,5.0,6.0,text,abc,", + "select case when col1 between 1 and 3 then 10 else 100 end from table1;", + new String [] {"10"}); + testEval(schema, "table1", "1,2,3,4,5.0,6.0,text,abc,", + "select case when col1 > 1 then 10 when col1 > 2 then 20 else 100 end from table1;", + new String [] {"100"}); + testEval(schema, "table1", "1,2,3,4,5.0,6.0,text,abc,", + "select case col1 when 1 then 10 when 2 then 20 else 100 end from table1;", + new String [] {"10"}); + testEval(schema, "table1", "1,2,3,4,5.0,6.0,text,abc,", + "select case col9 when 1 then 10 when 2 then 20 else 100 end is null from table1;", + new String [] {"f"}); + } + + @Test + public void testCaseWhensWithNullReturn() throws TajoException { + Schema schema = new Schema(); + schema.addColumn("col1", TajoDataTypes.Type.TEXT); + schema.addColumn("col2", TajoDataTypes.Type.TEXT); + + testEval(schema, "table1", "str1,str2", + "SELECT CASE WHEN col1 IS NOT NULL THEN col2 ELSE NULL END FROM table1", + new String[]{"str2"}); + testEval(schema, "table1", ",str2", + "SELECT CASE WHEN col1 IS NOT NULL THEN col2 ELSE NULL END FROM table1", + new String[]{""}); + } + + @Test + public void testCaseWhensWithCommonExpression() throws TajoException { + Schema schema = new Schema(); + schema.addColumn("col1", TajoDataTypes.Type.INT4); + schema.addColumn("col2", TajoDataTypes.Type.INT4); + schema.addColumn("col3", TajoDataTypes.Type.INT4); + + testEval(schema, "table1", "1,2,3", + "SELECT CASE WHEN col1 = 1 THEN 1 WHEN col1 = 2 THEN 2 ELSE 3 END FROM table1", + new String [] {"1"}); + testEval(schema, "table1", "1,2,3", + "SELECT CASE WHEN col2 = 1 THEN 1 WHEN col2 = 2 THEN 2 ELSE 3 END FROM table1", + new String [] {"2"}); + testEval(schema, "table1", "1,2,3", + "SELECT CASE WHEN col3 = 1 THEN 1 WHEN col3 = 2 THEN 2 ELSE 3 END FROM table1", + new String [] {"3"}); + + testEval(schema, "table1", "1,2,3", + "SELECT CASE col1 WHEN 1 THEN 1 WHEN 2 THEN 2 ELSE 3 END FROM table1", + new String [] {"1"}); + testEval(schema, "table1", "1,2,3", + "SELECT CASE col2 WHEN 1 THEN 1 WHEN 2 THEN 2 ELSE 3 END FROM table1", + new String [] {"2"}); + testEval(schema, "table1", "1,2,3", + "SELECT CASE col3 WHEN 1 THEN 1 WHEN 2 THEN 2 ELSE 3 END FROM table1", + new String [] {"3"}); + + testEval(schema, "table1", "1,2,3", + "SELECT CASE col1 WHEN 1 THEN 'aaa' WHEN 2 THEN 'bbb' ELSE 'ccc' END FROM table1", + new String [] {"aaa"}); + testEval(schema, "table1", "1,2,3", + "SELECT CASE col2 WHEN 1 THEN 'aaa' WHEN 2 THEN 'bbb' ELSE 'ccc' END FROM table1", + new String [] {"bbb"}); + testEval(schema, "table1", "1,2,3", + "SELECT CASE col3 WHEN 1 THEN 'aaa' WHEN 2 THEN 'bbb' ELSE 'ccc' END FROM table1", + new String [] {"ccc"}); + } + + @Test + public void testCaseWhensWithCommonExpressionAndNull() throws TajoException { + Schema schema = new Schema(); + schema.addColumn("col1", TajoDataTypes.Type.INT4); + schema.addColumn("col2", TajoDataTypes.Type.INT4); + schema.addColumn("col3", TajoDataTypes.Type.INT4); + + testEval(schema, "table1", "1,2,3", + "SELECT CASE col1 WHEN 1 THEN NULL WHEN 2 THEN 2 ELSE 3 END FROM table1", + new String [] {""}); + testEval(schema, "table1", "1,2,3", + "SELECT CASE col2 WHEN 1 THEN NULL WHEN 2 THEN 2 ELSE 3 END FROM table1", + new String [] {"2"}); + testEval(schema, "table1", "1,2,3", + "SELECT CASE col3 WHEN 1 THEN NULL WHEN 2 THEN 2 ELSE 3 END FROM table1", + new String [] {"3"}); + + testEval(schema, "table1", "1,2,3", + "SELECT CASE col1 WHEN 1 THEN 1 WHEN 2 THEN 2 ELSE NULL END FROM table1", + new String [] {"1"}); + testEval(schema, "table1", "1,2,3", + "SELECT CASE col2 WHEN 1 THEN NULL WHEN 2 THEN 2 ELSE NULL END FROM table1", + new String [] {"2"}); + testEval(schema, "table1", "1,2,3", + "SELECT CASE col3 WHEN 1 THEN NULL WHEN 2 THEN 2 ELSE NULL END FROM table1", + new String [] {""}); + } + + @Test + public void testCoalesceText() throws Exception { + testSimpleEval("select coalesce('value1', 'value2');", new String[]{"value1"}); + testSimpleEval("select coalesce(null, 'value2');", new String[]{"value2"}); + testSimpleEval("select coalesce(null, null, 'value3');", new String[]{"value3"}); + testSimpleEval("select coalesce('value1', null, 'value3');", new String[]{"value1"}); + testSimpleEval("select coalesce(null, 'value2', 'value3');", new String[]{"value2"}); + testSimpleEval("select coalesce('value1');", new String[]{"value1"}); + testSimpleEval("select coalesce(null);", new String[]{""}); + + //no matched function + try { + testSimpleEval("select coalesce(null, 2, 'value3');", new String[]{"2"}); + fail("coalesce(NULL, INT, TEXT) not defined. So should throw exception."); + } catch (UndefinedFunctionException e) { + //success + } + } + + @Test + public void testCoalesceLong() throws Exception { + testSimpleEval("select coalesce(1, 2);", new String[]{"1"}); + testSimpleEval("select coalesce(null, 2);", new String[]{"2"}); + testSimpleEval("select coalesce(null, null, 3);", new String[]{"3"}); + testSimpleEval("select coalesce(1, null, 3);", new String[]{"1"}); + testSimpleEval("select coalesce(null, 2, 3);", new String[]{"2"}); + testSimpleEval("select coalesce(1);", new String[]{"1"}); + testSimpleEval("select coalesce(null);", new String[]{""}); + + //no matched function + try { + testSimpleEval("select coalesce(null, 'value2', 3);", new String[]{"2"}); + fail("coalesce(NULL, TEXT, INT) not defined. So should throw exception."); + } catch (UndefinedFunctionException e) { + //success + } + } + + @Test + public void testCoalesceDouble() throws Exception { + testSimpleEval("select coalesce(1.0, 2.0);", new String[]{"1.0"}); + testSimpleEval("select coalesce(null, 2.0);", new String[]{"2.0"}); + testSimpleEval("select coalesce(null, null, 3.0);", new String[]{"3.0"}); + testSimpleEval("select coalesce(1.0, null, 3.0);", new String[]{"1.0"}); + testSimpleEval("select coalesce(null, 2.0, 3.0);", new String[]{"2.0"}); + testSimpleEval("select coalesce(1.0);", new String[]{"1.0"}); + testSimpleEval("select coalesce(null);", new String[]{""}); + + //no matched function + try { + testSimpleEval("select coalesce('value1', null, 3.0);", new String[]{"1.0"}); + fail("coalesce(TEXT, NULL, FLOAT8) not defined. So should throw exception."); + } catch (UndefinedFunctionException e) { + // success + } + + try { + testSimpleEval("select coalesce(null, 'value2', 3.0);", new String[]{"2.0"}); + fail("coalesce(NULL, TEXT, FLOAT8) not defined. So should throw exception."); + } catch (UndefinedFunctionException e) { + //success + } + } + + @Test + public void testCoalesceBoolean() throws Exception { + testSimpleEval("select coalesce(null, false);", new String[]{"f"}); + testSimpleEval("select coalesce(null, null, true);", new String[]{"t"}); + testSimpleEval("select coalesce(true, null, false);", new String[]{"t"}); + testSimpleEval("select coalesce(null, true, false);", new String[]{"t"}); + } + + @Test + public void testCoalesceTimestamp() throws Exception { + testSimpleEval("select coalesce(null, timestamp '2014-01-01 00:00:00');", + new String[]{"2014-01-01 00:00:00"}); + testSimpleEval("select coalesce(null, null, timestamp '2014-01-01 00:00:00');", + new String[]{"2014-01-01 00:00:00"}); + testSimpleEval("select coalesce(timestamp '2014-01-01 00:00:00', null, timestamp '2014-01-02 00:00:00');", + new String[]{"2014-01-01 00:00:00"}); + testSimpleEval("select coalesce(null, timestamp '2014-01-01 00:00:00', timestamp '2014-02-01 00:00:00');", + new String[]{"2014-01-01 00:00:00"}); + } + + @Test + public void testCoalesceTime() throws Exception { + testSimpleEval("select coalesce(null, time '12:00:00');", + new String[]{"12:00:00"}); + testSimpleEval("select coalesce(null, null, time '12:00:00');", + new String[]{"12:00:00"}); + testSimpleEval("select coalesce(time '12:00:00', null, time '13:00:00');", + new String[]{"12:00:00"}); + testSimpleEval("select coalesce(null, time '12:00:00', time '13:00:00');", + new String[]{"12:00:00"}); + } + + @Test + public void testCoalesceDate() throws Exception { + testSimpleEval("select coalesce(null, date '2014-01-01');", new String[]{"2014-01-01"}); + testSimpleEval("select coalesce(null, null, date '2014-01-01');", new String[]{"2014-01-01"}); + testSimpleEval("select coalesce(date '2014-01-01', null, date '2014-02-01');", new String[]{"2014-01-01"}); + testSimpleEval("select coalesce(null, date '2014-01-01', date '2014-02-01');", new String[]{"2014-01-01"}); + } +} http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/java/org/apache/tajo/engine/function/TestDateTimeFunctions.java ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/java/org/apache/tajo/engine/function/TestDateTimeFunctions.java b/tajo-core-tests/src/test/java/org/apache/tajo/engine/function/TestDateTimeFunctions.java new file mode 100644 index 0000000..9dd8653 --- /dev/null +++ b/tajo-core-tests/src/test/java/org/apache/tajo/engine/function/TestDateTimeFunctions.java @@ -0,0 +1,483 @@ +/** + * 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.function; + + +import org.apache.tajo.SessionVars; +import org.apache.tajo.catalog.Schema; +import org.apache.tajo.datum.DatumFactory; +import org.apache.tajo.datum.TimestampDatum; +import org.apache.tajo.engine.eval.ExprTestBase; +import org.apache.tajo.engine.query.QueryContext; +import org.apache.tajo.exception.TajoException; +import org.apache.tajo.util.datetime.DateTimeUtil; +import org.apache.tajo.util.datetime.TimeMeta; +import org.junit.Test; + +import java.text.SimpleDateFormat; +import java.util.Date; +import java.util.TimeZone; + +import static org.apache.tajo.common.TajoDataTypes.Type.*; + +public class TestDateTimeFunctions extends ExprTestBase { + @Test + public void testToTimestamp() throws TajoException { + long expectedTimestamp = System.currentTimeMillis(); + TimestampDatum expected = DatumFactory.createTimestmpDatumWithUnixTime((int)(expectedTimestamp/ 1000)); + + // (expectedTimestamp / 1000) means the translation from millis seconds to unix timestamp + String q1 = String.format("select to_timestamp(%d);", (expectedTimestamp / 1000)); + testSimpleEval(q1, new String[]{expected.toString()}); + + testSimpleEval("select to_timestamp('1997-12-30 11:40:50.345', 'YYYY-MM-DD HH24:MI:SS.MS');", + new String[]{"1997-12-30 11:40:50.345"}); + testSimpleEval("select to_timestamp('1997-12-30 11:40:50.345 PM', 'YYYY-MM-DD HH24:MI:SS.MS PM');", + new String[]{"1997-12-30 23:40:50.345"}); + testSimpleEval("select to_timestamp('0097/Feb/16 --> 08:14:30', 'YYYY/Mon/DD --> HH:MI:SS');", + new String[]{"0097-02-16 08:14:30"}); + testSimpleEval("select to_timestamp('97/2/16 8:14:30', 'FMYYYY/FMMM/FMDD FMHH:FMMI:FMSS');", + new String[]{"0097-02-16 08:14:30"}); + testSimpleEval("select to_timestamp('1985 September 12', 'YYYY FMMonth DD');", + new String[]{"1985-09-12 00:00:00"}); + testSimpleEval("select to_timestamp('1,582nd VIII 21', 'Y,YYYth FMRM DD');", + new String[]{"1582-08-21 00:00:00"}); + testSimpleEval("select to_timestamp('05121445482000', 'MMDDHH24MISSYYYY');", + new String[]{"2000-05-12 14:45:48"}); + testSimpleEval("select to_timestamp('2000January09Sunday', 'YYYYFMMonthDDFMDay');", + new String[]{"2000-01-09 00:00:00"}); + testSimpleEval("select to_timestamp('97/Feb/16', 'YY/Mon/DD');", + new String[]{"1997-02-16 00:00:00"}); + testSimpleEval("select to_timestamp('19971116', 'YYYYMMDD');", + new String[]{"1997-11-16 00:00:00"}); + testSimpleEval("select to_timestamp('20000-1116', 'YYYY-MMDD');", + new String[]{"20000-11-16 00:00:00"}); + testSimpleEval("select to_timestamp('9-1116', 'Y-MMDD');", + new String[]{"2009-11-16 00:00:00"}); + testSimpleEval("select to_timestamp('95-1116', 'YY-MMDD');", + new String[]{"1995-11-16 00:00:00"}); + testSimpleEval("select to_timestamp('995-1116', 'YYY-MMDD');", + new String[]{"1995-11-16 00:00:00"}); + testSimpleEval("select to_timestamp('2005426', 'YYYYWWD');", + new String[]{"2005-10-15 00:00:00"}); + testSimpleEval("select to_timestamp('2005300', 'YYYYDDD');", + new String[]{"2005-10-27 00:00:00"}); + testSimpleEval("select to_timestamp('2005527', 'IYYYIWID');", + new String[]{"2006-01-01 00:00:00"}); + testSimpleEval("select to_timestamp('005527', 'IYYIWID');", + new String[]{"2006-01-01 00:00:00"}); + testSimpleEval("select to_timestamp('05527', 'IYIWID');", + new String[]{"2006-01-01 00:00:00"}); + testSimpleEval("select to_timestamp('5527', 'IIWID');", + new String[]{"2006-01-01 00:00:00"}); + testSimpleEval("select to_timestamp('2005364', 'IYYYIDDD');", + new String[]{"2006-01-01 00:00:00"}); + testSimpleEval("select to_timestamp('20050302', 'YYYYMMDD');", + new String[]{"2005-03-02 00:00:00"}); + testSimpleEval("select to_timestamp('2005 03 02', 'YYYYMMDD');", + new String[]{"2005-03-02 00:00:00"}); + testSimpleEval("select to_timestamp(' 2005 03 02', 'YYYYMMDD');", + new String[]{"2005-03-02 00:00:00"}); + testSimpleEval("select to_timestamp(' 20050302', 'YYYYMMDD');", + new String[]{"2005-03-02 00:00:00"}); + } + + @Test + public void testToChar() throws TajoException { + long expectedTimestamp = System.currentTimeMillis(); + TimeMeta tm = new TimeMeta(); + DateTimeUtil.toJulianTimeMeta(DateTimeUtil.javaTimeToJulianTime(expectedTimestamp), tm); + // (expectedTimestamp / 1000) means the translation from millis seconds to unix timestamp + String q = String.format("select to_char(to_timestamp(%d), 'yyyy-MM');", (expectedTimestamp / 1000)); + testSimpleEval(q, new String[]{String.format("%04d-%02d", tm.years, tm.monthOfYear)}); + + q = "select to_char(to_timestamp('1997-12-30 11:40:00', 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS')"; + testSimpleEval(q, new String[]{"1997-12-30 11:40:00"}); + + q = "select to_char(to_timestamp('1997-12-30 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS')"; + testSimpleEval(q, new String[]{"1997-12-30 00:00:00"}); + } + + @Test + public void testExtract() throws TajoException { + TimeZone GMT = TimeZone.getTimeZone("GMT"); + TimeZone PST = TimeZone.getTimeZone("PST"); + + Schema schema2 = new Schema(); + schema2.addColumn("col1", TIMESTAMP); + testEval(schema2, "table1", + "1970-01-17 10:09:37", + "select extract(year from col1), extract(month from col1), extract(day from col1) from table1;", + new String[]{"1970.0", "1.0", "17.0"}); + testEval(schema2, "table1", + "1970-01-17 10:09:37" + getUserTimeZoneDisplay(GMT), + "select extract(year from col1), extract(month from col1), extract(day from col1) from table1;", + new String[]{"1970.0", "1.0", "17.0"}); + testEval(schema2, "table1", + "1970-01-17 10:09:37" + getUserTimeZoneDisplay(PST), + "select extract(year from col1), extract(month from col1), extract(day from col1) from table1;", + new String[]{"1970.0", "1.0", "17.0"}); + + // Currently TIME type can be loaded with INT8 type. + Schema schema3 = new Schema(); + schema3.addColumn("col1", TIME); + testEval(schema3, "table1", + "10:09:37.5", + "select extract(hour from col1), extract(minute from col1), extract(second from col1) from table1;", + new String[]{"10.0", "9.0", "37.5"}); + testEval(schema3, "table1", + "10:09:37.5" + getUserTimeZoneDisplay(GMT), + "select extract(hour from col1), extract(minute from col1), extract(second from col1) from table1;", + new String[]{"10.0", "9.0", "37.5"}); + testEval(schema3, "table1", + "10:09:37.5" + getUserTimeZoneDisplay(PST), + "select extract(hour from col1), extract(minute from col1), extract(second from col1) from table1;", + new String[]{"18.0", "9.0", "37.5"}); + + Schema schema4 = new Schema(); + schema4.addColumn("col1", DATE); + testEval(schema4, "table1", + "1970-01-17", + "select extract(year from col1), extract(month from col1), extract(day from col1) from table1;", + new String[]{"1970.0", "1.0", "17.0"}); + + testSimpleEval("select extract(century from TIMESTAMP '1970-01-17 10:09:37');", new String[]{"20.0"}); + + testSimpleEval("select extract(century from DATE '1970-01-17');", new String[]{"20.0"}); + + testSimpleEval("select extract(decade from TIMESTAMP '1970-01-17 10:09:37');", new String[]{"197.0"}); + + testSimpleEval("select extract(decade from DATE '1970-01-17');", new String[]{"197.0"}); + + testSimpleEval("select extract(millennium from TIMESTAMP '2001-02-16 10:09:37');", new String[]{"3.0"}); + testSimpleEval("select extract(millennium from TIMESTAMP '2000-02-16 10:09:37');", new String[]{"2.0"}); + + testSimpleEval("select extract(millennium from DATE '2001-02-16');", new String[]{"3.0"}); + testSimpleEval("select extract(millennium from DATE '2000-02-16');", new String[]{"2.0"}); + + testSimpleEval("select extract(year from TIMESTAMP '1970-01-17 10:09:37');", new String[]{"1970.0"}); + testSimpleEval("select extract(month from TIMESTAMP '1970-01-17 10:09:37');", new String[]{"1.0"}); + testSimpleEval("select extract(day from TIMESTAMP '1970-01-17 10:09:37');", new String[]{"17.0"}); + + testSimpleEval("select extract(hour from TIMESTAMP '1970-01-17 10:09:37');", new String[]{"10.0"}); + testSimpleEval("select extract(minute from TIMESTAMP '1970-01-17 10:09:37');", new String[]{"9.0"}); + testSimpleEval("select extract(second from TIMESTAMP '1970-01-17 10:09:37');", new String[]{"37.0"}); + testSimpleEval("select extract(second from TIMESTAMP '1970-01-17 10:09:37.5');", new String[]{"37.5"}); + + testSimpleEval("select extract(hour from TIME '10:09:37');", new String[]{"10.0"}); + testSimpleEval("select extract(minute from TIME '10:09:37');", new String[]{"9.0"}); + testSimpleEval("select extract(second from TIME '10:09:37');", new String[]{"37.0"}); + testSimpleEval("select extract(second from TIME '10:09:37.5');", new String[]{"37.5"}); + + testSimpleEval("select extract(year from DATE '1970-01-17');", new String[]{"1970.0"}); + testSimpleEval("select extract(month from DATE '1970-01-17');", new String[]{"1.0"}); + testSimpleEval("select extract(day from DATE '1970-01-17');", new String[]{"17.0"}); + + testSimpleEval("select extract(milliseconds from TIMESTAMP '1970-01-17 10:09:37.5');", new String[]{"37500.0"}); + testSimpleEval("select extract(milliseconds from TIME '10:09:37.123');", new String[]{"37123.0"}); + + testSimpleEval("select extract(microseconds from TIMESTAMP '1970-01-17 10:09:37.5');", new String[]{"3.75E7"}); + testSimpleEval("select extract(microseconds from TIME '10:09:37.123');", new String[]{"3.7123E7"}); + + testSimpleEval("select extract(dow from TIMESTAMP '1970-01-17 10:09:37');", new String[]{"6.0"}); + testSimpleEval("select extract(dow from TIMESTAMP '1970-01-18 10:09:37');", new String[]{"0.0"}); + testSimpleEval("select extract(isodow from TIMESTAMP '1970-01-17 10:09:37');", new String[]{"6.0"}); + testSimpleEval("select extract(isodow from TIMESTAMP '1970-01-18 10:09:37');", new String[]{"7.0"}); + + testSimpleEval("select extract(year from TIMESTAMP '2006-01-02 10:09:37');", new String[]{"2006.0"}); + testSimpleEval("select extract(year from TIMESTAMP '2006-01-01 10:09:37');", new String[]{"2006.0"}); + testSimpleEval("select extract(isoyear from TIMESTAMP '2006-01-02 10:09:37');", new String[]{"2006.0"}); + testSimpleEval("select extract(isoyear from TIMESTAMP '2006-01-01 10:09:37');", new String[]{"2005.0"}); + + testSimpleEval("select extract(quarter from TIMESTAMP '2006-02-01 10:09:37');", new String[]{"1.0"}); + testSimpleEval("select extract(quarter from TIMESTAMP '2006-04-01 10:09:37');", new String[]{"2.0"}); + testSimpleEval("select extract(quarter from TIMESTAMP '2006-07-01 10:09:37');", new String[]{"3.0"}); + testSimpleEval("select extract(quarter from TIMESTAMP '2006-12-01 10:09:37');", new String[]{"4.0"}); + + testSimpleEval("select extract(week from TIMESTAMP '1970-01-17 10:09:37');", new String[]{"3.0"}); + + testSimpleEval("select extract(dow from DATE '1970-01-17');", new String[]{"6.0"}); + testSimpleEval("select extract(dow from DATE '1970-01-18');", new String[]{"0.0"}); + testSimpleEval("select extract(isodow from DATE '1970-01-17');", new String[]{"6.0"}); + testSimpleEval("select extract(isodow from DATE '1970-01-18');", new String[]{"7.0"}); + + testSimpleEval("select extract(year from DATE '2006-01-02');", new String[]{"2006.0"}); + testSimpleEval("select extract(year from DATE '2006-01-01');", new String[]{"2006.0"}); + testSimpleEval("select extract(isoyear from DATE '2006-01-02');", new String[]{"2006.0"}); + testSimpleEval("select extract(isoyear from DATE '2006-01-01');", new String[]{"2005.0"}); + + testSimpleEval("select extract(quarter from DATE '2006-02-01');", new String[]{"1.0"}); + testSimpleEval("select extract(quarter from DATE '2006-04-01');", new String[]{"2.0"}); + testSimpleEval("select extract(quarter from DATE '2006-07-01');", new String[]{"3.0"}); + testSimpleEval("select extract(quarter from DATE '2006-12-01');", new String[]{"4.0"}); + + testSimpleEval("select extract(week from DATE '1970-01-17');", new String[]{"3.0"}); + } + + @Test + public void testDatePart() throws TajoException { + TimeZone GMT = TimeZone.getTimeZone("GMT"); + TimeZone PST = TimeZone.getTimeZone("PST"); + + Schema schema2 = new Schema(); + schema2.addColumn("col1", TIMESTAMP); + + testEval(schema2, "table1", + "1970-01-17 22:09:37", + "select date_part('year', col1), date_part('month', col1), date_part('day', col1) from table1;", + new String[]{"1970.0", "1.0", "17.0"}); + testEval(schema2, "table1", + "1970-01-17 22:09:37" + getUserTimeZoneDisplay(GMT), + "select date_part('year', col1), date_part('month', col1), date_part('day', col1) from table1;", + new String[]{"1970.0", "1.0", "17.0"}); + testEval(schema2, "table1", + "1970-01-17 22:09:37" + getUserTimeZoneDisplay(PST), + "select date_part('year', col1), date_part('month', col1), date_part('day', col1) from table1;", + new String[]{"1970.0", "1.0", "18.0"}); + + Schema schema3 = new Schema(); + schema3.addColumn("col1", TIME); + testEval(schema3, "table1", "10:09:37.5", + "select date_part('hour', col1), date_part('minute', col1), date_part('second', col1) from table1;", + new String[]{"10.0", "9.0", "37.5"}); + testEval(schema3, "table1", "10:09:37.5" + getUserTimeZoneDisplay(GMT), + "select date_part('hour', col1), date_part('minute', col1), date_part('second', col1) from table1;", + new String[]{"10.0", "9.0", "37.5"}); + testEval(schema3, "table1", "10:09:37.5" + getUserTimeZoneDisplay(PST), + "select date_part('hour', col1), date_part('minute', col1), date_part('second', col1) from table1;", + new String[]{"18.0", "9.0", "37.5"}); + + Schema schema4 = new Schema(); + schema4.addColumn("col1", DATE); + testEval(schema4, "table1", + "1970-01-17", + "select date_part('year', col1), date_part('month', col1), date_part('day', col1) from table1;", + new String[]{"1970.0", "1.0", "17.0"}); + + testSimpleEval("select date_part('century', TIMESTAMP '1970-01-17 10:09:37');", new String[]{"20.0"}); + + testSimpleEval("select date_part('century', DATE '1970-01-17');", new String[]{"20.0"}); + + testSimpleEval("select date_part('decade', TIMESTAMP '1970-01-17 10:09:37');", new String[]{"197.0"}); + + testSimpleEval("select date_part('decade', DATE '1970-01-17');", new String[]{"197.0"}); + + testSimpleEval("select date_part('millennium', TIMESTAMP '2001-02-16 10:09:37');", new String[]{"3.0"}); + testSimpleEval("select date_part('millennium', TIMESTAMP '2000-02-16 10:09:37');", new String[]{"2.0"}); + + testSimpleEval("select date_part('millennium', DATE '2001-02-16');", new String[]{"3.0"}); + testSimpleEval("select date_part('millennium', DATE '2000-02-16');", new String[]{"2.0"}); + + testSimpleEval("select date_part('year', TIMESTAMP '1970-01-17 10:09:37');", new String[]{"1970.0"}); + testSimpleEval("select date_part('month', TIMESTAMP '1970-01-17 10:09:37');", new String[]{"1.0"}); + testSimpleEval("select date_part('day', TIMESTAMP '1970-01-17 10:09:37');", new String[]{"17.0"}); + + testSimpleEval("select date_part('hour', TIMESTAMP '1970-01-17 10:09:37');", new String[]{"10.0"}); + testSimpleEval("select date_part('minute', TIMESTAMP '1970-01-17 10:09:37');", new String[]{"9.0"}); + testSimpleEval("select date_part('second', TIMESTAMP '1970-01-17 10:09:37');", new String[]{"37.0"}); + testSimpleEval("select date_part('second', TIMESTAMP '1970-01-17 10:09:37.5');", new String[]{"37.5"}); + + testSimpleEval("select date_part('hour', TIME '10:09:37');", new String[]{"10.0"}); + testSimpleEval("select date_part('minute', TIME '10:09:37');", new String[]{"9.0"}); + testSimpleEval("select date_part('second', TIME '10:09:37');", new String[]{"37.0"}); + testSimpleEval("select date_part('second', TIME '10:09:37.5');", new String[]{"37.5"}); + + testSimpleEval("select date_part('year', DATE '1970-01-17');", new String[]{"1970.0"}); + testSimpleEval("select date_part('month', DATE '1970-01-17');", new String[]{"1.0"}); + testSimpleEval("select date_part('day', DATE '1970-01-17');", new String[]{"17.0"}); + + testSimpleEval("select date_part('milliseconds', TIMESTAMP '1970-01-17 10:09:37.5');", new String[]{"37500.0"}); + testSimpleEval("select date_part('milliseconds', TIME '10:09:37.123');", new String[]{"37123.0"}); + + testSimpleEval("select date_part('microseconds', TIMESTAMP '1970-01-17 10:09:37.5');", new String[]{"3.75E7"}); + testSimpleEval("select date_part('microseconds', TIME '10:09:37.123');", new String[]{"3.7123E7"}); + + testSimpleEval("select date_part('dow', TIMESTAMP '1970-01-17 10:09:37');", new String[]{"6.0"}); + testSimpleEval("select date_part('dow', TIMESTAMP '1970-01-18 10:09:37');", new String[]{"0.0"}); + testSimpleEval("select date_part('isodow', TIMESTAMP '1970-01-17 10:09:37');", new String[]{"6.0"}); + testSimpleEval("select date_part('isodow', TIMESTAMP '1970-01-18 10:09:37');", new String[]{"7.0"}); + + testSimpleEval("select date_part('year', TIMESTAMP '2006-01-02 10:09:37');", new String[]{"2006.0"}); + testSimpleEval("select date_part('year', TIMESTAMP '2006-01-01 10:09:37');", new String[]{"2006.0"}); + testSimpleEval("select date_part('isoyear', TIMESTAMP '2006-01-02 10:09:37');", new String[]{"2006.0"}); + testSimpleEval("select date_part('isoyear', TIMESTAMP '2006-01-01 10:09:37');", new String[]{"2005.0"}); + + testSimpleEval("select date_part('quarter', TIMESTAMP '2006-02-01 10:09:37');", new String[]{"1.0"}); + testSimpleEval("select date_part('quarter', TIMESTAMP '2006-04-01 10:09:37');", new String[]{"2.0"}); + testSimpleEval("select date_part('quarter', TIMESTAMP '2006-07-01 10:09:37');", new String[]{"3.0"}); + testSimpleEval("select date_part('quarter', TIMESTAMP '2006-12-01 10:09:37');", new String[]{"4.0"}); + + testSimpleEval("select date_part('week', TIMESTAMP '1970-01-17 10:09:37');", new String[]{"3.0"}); + + testSimpleEval("select date_part('dow', DATE '1970-01-17');", new String[]{"6.0"}); + testSimpleEval("select date_part('dow', DATE '1970-01-18');", new String[]{"0.0"}); + testSimpleEval("select date_part('isodow', DATE '1970-01-17');", new String[]{"6.0"}); + testSimpleEval("select date_part('isodow', DATE '1970-01-18');", new String[]{"7.0"}); + + testSimpleEval("select date_part('year', DATE '2006-01-02');", new String[]{"2006.0"}); + testSimpleEval("select date_part('year', DATE '2006-01-01');", new String[]{"2006.0"}); + testSimpleEval("select date_part('isoyear', DATE '2006-01-02');", new String[]{"2006.0"}); + testSimpleEval("select date_part('isoyear', DATE '2006-01-01');", new String[]{"2005.0"}); + + testSimpleEval("select date_part('quarter', DATE '2006-02-01');", new String[]{"1.0"}); + testSimpleEval("select date_part('quarter', DATE '2006-04-01');", new String[]{"2.0"}); + testSimpleEval("select date_part('quarter', DATE '2006-07-01');", new String[]{"3.0"}); + testSimpleEval("select date_part('quarter', DATE '2006-12-01');", new String[]{"4.0"}); + + testSimpleEval("select date_part('week', DATE '1970-01-17');", new String[]{"3.0"}); + } + + @Test + public void testUtcUsecTo() throws TajoException { + testSimpleEval("select utc_usec_to('day' ,1274259481071200);", new String[]{1274227200000000L+""}); + testSimpleEval("select utc_usec_to('hour' ,1274259481071200);", new String[]{1274256000000000L+""}); + testSimpleEval("select utc_usec_to('month' ,1274259481071200);", new String[]{1272672000000000L+""}); + testSimpleEval("select utc_usec_to('year' ,1274259481071200);", new String[]{1262304000000000L+""}); + testSimpleEval("select utc_usec_to('week' ,1207929480000000, 2);", new String[]{1207612800000000L+""}); + } + + @Test + public void testToDate() throws TajoException { + testSimpleEval("select to_date('2014-01-04', 'YYYY-MM-DD')", new String[]{"2014-01-04"}); + testSimpleEval("select to_date('2014-01-04', 'YYYY-MM-DD') + interval '1 day'", + new String[]{"2014-01-05 00:00:00"}); + + testSimpleEval("SELECT to_date('201404', 'yyyymm');", new String[]{"2014-04-01"}); + } + + @Test + public void testAddMonths() throws Exception { + testSimpleEval("SELECT add_months(date '2013-12-17', 2::INT2);", + new String[]{"2014-02-17 00:00:00"}); + testSimpleEval("SELECT add_months(date '2013-12-17', 2::INT4);", + new String[]{"2014-02-17 00:00:00"}); + testSimpleEval("SELECT add_months(date '2013-12-17', 2::INT8);", + new String[]{"2014-02-17 00:00:00"}); + + testSimpleEval("SELECT add_months(timestamp '2013-12-17 12:10:20', 2::INT2);", + new String[]{"2014-02-17 12:10:20"}); + testSimpleEval("SELECT add_months(timestamp '2013-12-17 12:10:20', 2::INT4);", + new String[]{"2014-02-17 12:10:20"}); + testSimpleEval("SELECT add_months(timestamp '2013-12-17 12:10:20', 2::INT8);", + new String[]{"2014-02-17 12:10:20"}); + + testSimpleEval("SELECT add_months(date '2014-02-05', -3::INT2);", + new String[]{"2013-11-05 00:00:00"}); + testSimpleEval("SELECT add_months(date '2014-02-05', -3::INT4);", + new String[]{"2013-11-05 00:00:00"}); + testSimpleEval("SELECT add_months(date '2014-02-05', -3::INT8);", + new String[]{"2013-11-05 00:00:00"}); + + testSimpleEval("SELECT add_months(timestamp '2014-02-05 12:10:20', -3::INT2);", + new String[]{"2013-11-05 12:10:20"}); + testSimpleEval("SELECT add_months(timestamp '2014-02-05 12:10:20', -3::INT4);", + new String[]{"2013-11-05 12:10:20"}); + testSimpleEval("SELECT add_months(timestamp '2014-02-05 12:10:20', -3::INT8);", + new String[]{"2013-11-05 12:10:20"}); + } + + @Test + public void testAddDays() throws TajoException { + testSimpleEval("SELECT add_days(date '2013-12-30', 5::INT2);", + new String[]{"2014-01-04 00:00:00"}); + testSimpleEval("SELECT add_days(date '2013-12-30', 5::INT4);", + new String[]{"2014-01-04 00:00:00"}); + testSimpleEval("SELECT add_days(date '2013-12-30', 5::INT8);", + new String[]{"2014-01-04 00:00:00"}); + + testSimpleEval("SELECT add_days(timestamp '2013-12-30 12:10:20', 5::INT2);", + new String[]{"2014-01-04 12:10:20"}); + testSimpleEval("SELECT add_days(timestamp '2013-12-30 12:10:20', 5::INT4);", + new String[]{"2014-01-04 12:10:20"}); + testSimpleEval("SELECT add_days(timestamp '2013-12-30 12:10:20', 5::INT8);", + new String[]{"2014-01-04 12:10:20"}); + + testSimpleEval("SELECT add_days(date '2013-12-05', -7::INT2);", + new String[]{"2013-11-28 00:00:00"}); + testSimpleEval("SELECT add_days(date '2013-12-05', -7::INT4);", + new String[]{"2013-11-28 00:00:00"}); + testSimpleEval("SELECT add_days(date '2013-12-05', -7::INT8);", + new String[]{"2013-11-28 00:00:00"}); + + testSimpleEval("SELECT add_days(timestamp '2013-12-05 12:10:20', -7::INT2);", + new String[]{"2013-11-28 12:10:20"}); + testSimpleEval("SELECT add_days(timestamp '2013-12-05 12:10:20', -7::INT4);", + new String[]{"2013-11-28 12:10:20"}); + testSimpleEval("SELECT add_days(timestamp '2013-12-05 12:10:20', -7::INT8);", + new String[]{"2013-11-28 12:10:20"}); + } + + @Test + public void testDateTimeNow() throws TajoException { + TimeZone originalTimezone = TimeZone.getDefault(); + TimeZone.setDefault(TimeZone.getTimeZone("GMT-6")); + + QueryContext context = new QueryContext(getConf()); + context.put(SessionVars.TIMEZONE, "GMT-6"); + + try { + Date expectedDate = new Date(System.currentTimeMillis()); + + testSimpleEval(context, "select to_char(now(), 'yyyy-MM-dd');", + new String[]{dateFormat(expectedDate, "yyyy-MM-dd")}); + testSimpleEval(context, "select cast(extract(year from now()) as INT4);", + new String[]{dateFormat(expectedDate, "yyyy")}); + testSimpleEval(context, "select current_date();", + new String[]{dateFormat(expectedDate, "yyyy-MM-dd")}); + testSimpleEval(context, "select cast(extract(hour from current_time()) as INT4);", + new String[]{String.valueOf(Integer.parseInt(dateFormat(expectedDate, "HH")))}); + + expectedDate.setDate(expectedDate.getDate() + 1); + + testSimpleEval(context, "select current_date() + 1;", + new String[]{dateFormat(expectedDate, "yyyy-MM-dd")}); + } finally { + TimeZone.setDefault(originalTimezone); + } + } + + @Test + public void testTimeValueKeyword() throws TajoException { + TimeZone originTimeZone = TimeZone.getDefault(); + TimeZone.setDefault(TimeZone.getTimeZone("GMT-6")); + + QueryContext context = new QueryContext(getConf()); + context.put(SessionVars.TIMEZONE, "GMT-6"); + + try { + Date expectedDate = new Date(System.currentTimeMillis()); + + testSimpleEval(context, "select to_char(current_timestamp, 'yyyy-MM-dd');", + new String[]{dateFormat(expectedDate, "yyyy-MM-dd")}); + testSimpleEval(context, "select cast(extract(year from current_timestamp) as INT4);", + new String[]{dateFormat(expectedDate, "yyyy")}); + testSimpleEval(context, "select current_date;", + new String[]{dateFormat(expectedDate, "yyyy-MM-dd")}); + testSimpleEval(context, "select cast(extract(hour from current_time) as INT4);", + new String[]{String.valueOf(Integer.parseInt(dateFormat(expectedDate, "HH")))}); + } finally { + TimeZone.setDefault(originTimeZone); + } + } + + private String dateFormat(Date date, String format) { + SimpleDateFormat df = new SimpleDateFormat(format); + return df.format(date); + } +} http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/java/org/apache/tajo/engine/function/TestFunctionLoader.java ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/java/org/apache/tajo/engine/function/TestFunctionLoader.java b/tajo-core-tests/src/test/java/org/apache/tajo/engine/function/TestFunctionLoader.java new file mode 100644 index 0000000..cf34c33 --- /dev/null +++ b/tajo-core-tests/src/test/java/org/apache/tajo/engine/function/TestFunctionLoader.java @@ -0,0 +1,45 @@ +/*** + * 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.function; + +import com.google.common.collect.Lists; +import org.apache.tajo.catalog.FunctionDesc; +import org.apache.tajo.util.StringUtils; +import org.junit.Test; + +import java.io.IOException; +import java.util.Collections; +import java.util.List; + +import static org.apache.tajo.LocalTajoTestingUtility.getResultText; +import static org.junit.Assert.assertEquals; + + +public class TestFunctionLoader { + + @Test + public void testFindScalarFunctions() throws IOException { + List<FunctionDesc> collections = Lists.newArrayList(FunctionLoader.findScalarFunctions()); + Collections.sort(collections); + String functionList = StringUtils.join(collections, "\n"); + + String result = getResultText(TestFunctionLoader.class, "testFindScalarFunctions.result"); + assertEquals(result.trim(), functionList.trim()); + } +} \ 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/function/TestGeneralFunction.java ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/java/org/apache/tajo/engine/function/TestGeneralFunction.java b/tajo-core-tests/src/test/java/org/apache/tajo/engine/function/TestGeneralFunction.java new file mode 100644 index 0000000..81ddba5 --- /dev/null +++ b/tajo-core-tests/src/test/java/org/apache/tajo/engine/function/TestGeneralFunction.java @@ -0,0 +1,49 @@ +/** + * 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.function; + +import org.junit.Test; +import org.apache.tajo.datum.Datum; +import org.apache.tajo.datum.Int8Datum; +import org.apache.tajo.datum.TextDatum; +import org.apache.tajo.engine.function.builtin.Date; +import org.apache.tajo.storage.Tuple; +import org.apache.tajo.storage.VTuple; + +import java.util.Calendar; + +import static org.junit.Assert.assertEquals; + +public class TestGeneralFunction { + + @Test + public void testDate() { + Date date = new Date(); + Tuple tuple = new VTuple(new Datum[] {new TextDatum("25/12/2012 00:00:00")}); + Int8Datum unixtime = (Int8Datum) date.eval(tuple); + Calendar c = Calendar.getInstance(); + c.setTimeInMillis(unixtime.asInt8()); + assertEquals(2012, c.get(Calendar.YEAR)); + assertEquals(11, c.get(Calendar.MONTH)); + assertEquals(25, c.get(Calendar.DAY_OF_MONTH)); + assertEquals(0, c.get(Calendar.HOUR_OF_DAY)); + assertEquals(0, c.get(Calendar.MINUTE)); + assertEquals(0, c.get(Calendar.SECOND)); + } +} http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/java/org/apache/tajo/engine/function/TestJsonFunctions.java ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/java/org/apache/tajo/engine/function/TestJsonFunctions.java b/tajo-core-tests/src/test/java/org/apache/tajo/engine/function/TestJsonFunctions.java new file mode 100644 index 0000000..1792075 --- /dev/null +++ b/tajo-core-tests/src/test/java/org/apache/tajo/engine/function/TestJsonFunctions.java @@ -0,0 +1,66 @@ +/** + * 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.function; + + +import org.apache.tajo.engine.eval.ExprTestBase; +import org.apache.tajo.exception.TajoException; +import org.junit.Test; + +public class TestJsonFunctions extends ExprTestBase { + static final String JSON_DOCUMENT = "{\"map\" : {\"name\" : \"tajo\"}, \"array\" : [1,2,3]}"; + static final String JSON_ARRAY = "[100,200,300,400,500]"; + static final String JSON_COMPLEX_ARRAY = "[100, \"test\", \"2015-08-13 11:58:59\", 0.899999999999]"; + static final String JSON_EMPTY_ARRAY = "[]"; + + @Test + public void testJsonExtractPathText() throws TajoException { + testSimpleEval("select json_extract_path_text('" + JSON_DOCUMENT + "', '$.map.name') ", new String[]{"tajo"}); + testSimpleEval("select json_extract_path_text('" + JSON_DOCUMENT + "', '$.array[1]') ", new String[]{"2"}); + } + + @Test + public void testJsonArrayGet() throws TajoException { + testSimpleEval("select json_array_get('" + JSON_ARRAY + "', 0)", new String[]{"100"}); + testSimpleEval("select json_array_get('" + JSON_ARRAY + "', 2)", new String[]{"300"}); + testSimpleEval("select json_array_get('" + JSON_ARRAY + "', -1)", new String[]{"500"}); + testSimpleEval("select json_array_get('" + JSON_ARRAY + "', -2)", new String[]{"400"}); + testSimpleEval("select json_array_get('" + JSON_ARRAY + "', 10)", new String[]{""}); + testSimpleEval("select json_array_get('" + JSON_ARRAY + "', -10)", new String[]{""}); + testSimpleEval("select json_array_get('" + JSON_EMPTY_ARRAY + "', 0)", new String[]{""}); + } + + @Test + public void testJsonArrayContains() throws TajoException { + testSimpleEval("select json_array_contains('" + JSON_COMPLEX_ARRAY + "', 100)", new String[]{"t"}); + testSimpleEval("select json_array_contains('" + JSON_COMPLEX_ARRAY + "', 'test')", new String[]{"t"}); + testSimpleEval("select json_array_contains('" + JSON_COMPLEX_ARRAY + "', '2015-08-13 11:58:59'::timestamp)", + new String[]{"t"}); + testSimpleEval("select json_array_contains('" + JSON_COMPLEX_ARRAY + "', '2015-08-13 11:58:59'::date)", + new String[]{"f"}); + testSimpleEval("select json_array_contains('" + JSON_COMPLEX_ARRAY + "', 1000)", new String[]{"f"}); + testSimpleEval("select json_array_contains('" + JSON_COMPLEX_ARRAY + "', 0.899999999999)", new String[]{"t"}); + } + + @Test + public void testJsonArrayLength() throws TajoException { + testSimpleEval("select json_array_length('" + JSON_ARRAY + "')", new String[]{"5"}); + testSimpleEval("select json_array_length('" + JSON_EMPTY_ARRAY + "')", new String[]{"0"}); + } +}
