http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/java/org/apache/tajo/engine/function/TestMathFunctions.java ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/java/org/apache/tajo/engine/function/TestMathFunctions.java b/tajo-core-tests/src/test/java/org/apache/tajo/engine/function/TestMathFunctions.java new file mode 100644 index 0000000..4b0303f --- /dev/null +++ b/tajo-core-tests/src/test/java/org/apache/tajo/engine/function/TestMathFunctions.java @@ -0,0 +1,470 @@ +/** + * 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.Schema; +import org.apache.tajo.engine.eval.ExprTestBase; +import org.apache.tajo.exception.TajoException; +import org.junit.Test; + +import static org.apache.tajo.common.TajoDataTypes.Type.*; + +public class TestMathFunctions extends ExprTestBase { + @Test + public void testRound() throws TajoException { + testSimpleEval("select round(5.1) as col1 ", new String[]{"5"}); + testSimpleEval("select round(5.5) as col1 ", new String[]{"6"}); + testSimpleEval("select round(5.6) as col1 ", new String[]{"6"}); + + testSimpleEval("select round(-5.1) as col1 ", new String[]{"-5"}); + testSimpleEval("select round(-5.5) as col1 ", new String[]{"-6"}); + testSimpleEval("select round(-5.6) as col1 ", new String[]{"-6"}); + + Schema schema = new Schema(); + schema.addColumn("col1", FLOAT8); + schema.addColumn("col2", FLOAT8); + schema.addColumn("col3", FLOAT8); + + testEval(schema, "table1", "1.0, 0.2, 0.4", "select round(col1 + col2 + col3) from table1", + new String[]{"2"}); + + Schema schema2 = new Schema(); + schema2.addColumn("col1", INT4); + schema2.addColumn("col2", INT8); + schema2.addColumn("col3", FLOAT4); + schema2.addColumn("col4", FLOAT8); + + testEval(schema2, "table1", "9,9,9.5,9.5", + "select round(col1), round (col2), round(col3), round(col4) from table1", + new String [] {"9", "9", "10", "10"}); + } + + @Test + public void testFloor() throws TajoException { + testSimpleEval("select floor(5.1) as col1 ", new String[]{"5"}); + testSimpleEval("select floor(5.5) as col1 ", new String[]{"5"}); + testSimpleEval("select floor(5.6) as col1 ", new String[]{"5"}); + + testSimpleEval("select floor(-5.1) as col1 ", new String[]{"-6"}); + testSimpleEval("select floor(-5.6) as col1 ", new String[]{"-6"}); + + Schema schema = new Schema(); + schema.addColumn("col1", FLOAT8); + schema.addColumn("col2", FLOAT8); + schema.addColumn("col3", FLOAT8); + + testEval(schema, "table1", "1.0, 0.2, 0.4", "select floor(col1 + col2 + col3) from table1", + new String[]{"1"}); + } + + @Test + public void testCeil() throws TajoException { + testSimpleEval("select ceil(5.0) as col1 ", new String[]{"5"}); + testSimpleEval("select ceil(5.1) as col1 ", new String[]{"6"}); + testSimpleEval("select ceil(5.5) as col1 ", new String[]{"6"}); + testSimpleEval("select ceil(5.6) as col1 ", new String[]{"6"}); + + testSimpleEval("select ceil(-5.1) as col1 ", new String[]{"-5"}); + testSimpleEval("select ceil(-5.6) as col1 ", new String[]{"-5"}); + + Schema schema = new Schema(); + schema.addColumn("col1", FLOAT8); + schema.addColumn("col2", FLOAT8); + schema.addColumn("col3", FLOAT8); + + testEval(schema, "table1", "1.0, 0.2, 0.1", "select ceil(col1 + col2 + col3) from table1", + new String[]{"2"}); + } + + @Test + public void testCeiling() throws TajoException { + testSimpleEval("select ceiling(5.0) as col1 ", new String[]{"5"}); + testSimpleEval("select ceiling(5.1) as col1 ", new String[]{"6"}); + testSimpleEval("select ceiling(5.5) as col1 ", new String[]{"6"}); + testSimpleEval("select ceiling(5.6) as col1 ", new String[]{"6"}); + + testSimpleEval("select ceiling(-5.1) as col1 ", new String[]{"-5"}); + testSimpleEval("select ceiling(-5.6) as col1 ", new String[]{"-5"}); + + Schema schema = new Schema(); + schema.addColumn("col1", FLOAT8); + schema.addColumn("col2", FLOAT8); + schema.addColumn("col3", FLOAT8); + + testEval(schema, "table1", "1.0, 0.2, 0.1", "select ceiling(col1 + col2 + col3) from table1", + new String[]{"2"}); + } + + @Test + public void testSin() throws TajoException { + testSimpleEval("select sin(0.0) as col1 ", new String[]{"0.0"}); + testSimpleEval("select sin(0.7) as col1 ", new String[]{"0.644217687237691"}); + testSimpleEval("select sin(1.2) as col1 ", new String[]{"0.9320390859672263"}); + testSimpleEval("select sin(-0.5) as col1 ", new String[]{"-0.479425538604203"}); + + Schema schema = new Schema(); + schema.addColumn("col1", FLOAT8); + schema.addColumn("col2", FLOAT8); + schema.addColumn("col3", FLOAT8); + + testEval(schema, "table1", "1.0, 0.2, 0.1", "select sin(col1 + col2 + col3) from table1", + new String[]{"0.963558185417193"}); + } + + + @Test + public void testCos() throws TajoException { + testSimpleEval("select cos(0.0) as col1 ", new String[]{"1.0"}); + testSimpleEval("select cos(0.7) as col1 ", new String[]{"0.7648421872844885"}); + testSimpleEval("select cos(1.2) as col1 ", new String[]{"0.3623577544766736"}); + testSimpleEval("select cos(-0.5) as col1 ", new String[]{"0.8775825618903728"}); + + Schema schema = new Schema(); + schema.addColumn("col1", FLOAT8); + schema.addColumn("col2", FLOAT8); + schema.addColumn("col3", FLOAT8); + + testEval(schema, "table1", "1.0, 0.2, 0.1", "select cos(col1 + col2 + col3) from table1", + new String[]{"0.26749882862458735"}); + } + + @Test + public void testTan() throws TajoException { + testSimpleEval("select tan(0.0) as col1 ", new String[]{"0.0"}); + testSimpleEval("select tan(0.3) as col1 ", new String[]{"0.30933624960962325"}); + testSimpleEval("select tan(0.8) as col1 ", new String[]{"1.0296385570503641"}); + testSimpleEval("select tan(-0.5) as col1 ", new String[]{"-0.5463024898437905"}); + + Schema schema = new Schema(); + schema.addColumn("col1", FLOAT8); + schema.addColumn("col2", FLOAT8); + schema.addColumn("col3", FLOAT8); + + testEval(schema, "table1", "1.0, 0.2, 0.1", "select tan(col1 - col2 - col3) from table1", + new String[]{"0.8422883804630795"}); + } + + @Test + public void testAsin() throws TajoException { + testSimpleEval("select asin(0.0) as col1 ", new String[]{"0.0"}); + testSimpleEval("select asin(0.3) as col1 ", new String[]{"0.3046926540153975"}); + testSimpleEval("select asin(0.8) as col1 ", new String[]{"0.9272952180016123"}); + testSimpleEval("select asin(-0.5) as col1 ", new String[]{"-0.5235987755982989"}); + + Schema schema = new Schema(); + schema.addColumn("col1", FLOAT8); + schema.addColumn("col2", FLOAT8); + schema.addColumn("col3", FLOAT8); + + testEval(schema, "table1", "1.0, 0.2, 0.1", "select asin(col1 - col2 - col3) from table1", + new String[]{"0.7753974966107532"}); + } + + @Test + public void testAcos() throws TajoException { + testSimpleEval("select acos(0.0) as col1 ", new String[]{"1.5707963267948966"}); + testSimpleEval("select acos(0.3) as col1 ", new String[]{"1.2661036727794992"}); + testSimpleEval("select acos(0.8) as col1 ", new String[]{"0.6435011087932843"}); + testSimpleEval("select acos(-0.5) as col1 ", new String[]{"2.0943951023931957"}); + + Schema schema = new Schema(); + schema.addColumn("col1", FLOAT8); + schema.addColumn("col2", FLOAT8); + schema.addColumn("col3", FLOAT8); + + testEval(schema, "table1", "1.0, 0.2, 0.1", "select acos(col1 - col2 - col3) from table1", + new String[]{"0.7953988301841435"}); + } + + @Test + public void testAtan() throws TajoException { + testSimpleEval("select atan(0.0) as col1 ", new String[]{"0.0"}); + testSimpleEval("select atan(0.8) as col1 ", new String[]{"0.6747409422235527"}); + testSimpleEval("select atan(1.2) as col1 ", new String[]{"0.8760580505981934"}); + testSimpleEval("select atan(-0.5) as col1 ", new String[]{"-0.4636476090008061"}); + + Schema schema = new Schema(); + schema.addColumn("col1", FLOAT8); + schema.addColumn("col2", FLOAT8); + schema.addColumn("col3", FLOAT8); + + testEval(schema, "table1", "1.0, 0.2, 0.1", "select atan(col1 + col2 + col3) from table1", + new String[]{"0.9151007005533605"}); + } + + @Test + public void testAtan2() throws TajoException { + testSimpleEval("select atan2(0.8, 0.0) as col1 ", new String[]{"1.5707963267948966"}); + testSimpleEval("select atan2(0.8, 1.1) as col1 ", new String[]{"0.628796286415433"}); + testSimpleEval("select atan2(2.7, 0.3) as col1 ", new String[]{"1.460139105621001"}); + testSimpleEval("select atan2(-0.5, 0.3) as col1 ", new String[]{"-1.0303768265243125"}); + testSimpleEval("select atan2(-0.2, -1.3) as col1 ", new String[]{"-2.988943325194528"}); + + Schema schema = new Schema(); + schema.addColumn("col1", FLOAT8); + schema.addColumn("col2", FLOAT8); + schema.addColumn("col3", FLOAT8); + + testEval(schema, "table1", "1.0, 0.2, 0.1", "select atan2(col1 + col2, col3) from table1", + new String[]{"1.4876550949064553"}); + } + + @Test + public void testMod() throws TajoException { + testSimpleEval("select mod(9,4) as col1 ", new String[]{"1"}); + testSimpleEval("select mod(200000000001,200000000000) as col1 ", new String[]{"1"}); + testSimpleEval("select mod(200000000000,2) as col1 ", new String[]{"0"}); + testSimpleEval("select mod(2,200000000000) as col1 ", new String[]{"2"}); + + Schema schema = new Schema(); + schema.addColumn("col1", INT8); + schema.addColumn("col2", INT8); + schema.addColumn("col3", INT8); + + testEval(schema, "table1", "9,2,3", "select mod(col1 + col2, col3) from table1", + new String[]{"2"}); + } + + @Test + public void testDiv() throws TajoException { + testSimpleEval("select div(9,4) as col1 ", new String[]{"2"}); + testSimpleEval("select div(200000000001,200000000000) as col1 ", new String[]{"1"}); + testSimpleEval("select div(200000000000,2) as col1 ", new String[]{"100000000000"}); + testSimpleEval("select div(2,200000000000) as col1 ", new String[]{"0"}); + + Schema schema = new Schema(); + schema.addColumn("col1", INT8); + schema.addColumn("col2", INT8); + schema.addColumn("col3", INT8); + + testEval(schema, "table1", "9,2,3", "select div(col1 + col2, col3) from table1", + new String[]{"3"}); + } + + @Test + public void testSign() throws TajoException { + testSimpleEval("select sign(2) as col1 ", new String[]{"1.0"}); + testSimpleEval("select sign(2.345) as col1 ", new String[]{"1.0"}); + testSimpleEval("select sign(0.3) as col1 ", new String[]{"1.0"}); + + + Schema schema = new Schema(); + schema.addColumn("col1", FLOAT4); + schema.addColumn("col2", FLOAT4); + schema.addColumn("col3", FLOAT4); + + testEval(schema, "table1", "1.0, 0.2, 0.1", "select sign(col1 + col2 + col3) from table1", + new String[]{"1.0"}); + + + Schema schema2 = new Schema(); + schema2.addColumn("col1", FLOAT8); + schema2.addColumn("col2", FLOAT8); + schema2.addColumn("col3", FLOAT8); + + testEval(schema2, "table1", "1.0, 0.2, 0.1", "select sign(col1 + col2 + col3) from table1", + new String[]{"1.0"}); + } + + @Test + public void testSqrt() throws TajoException { + testSimpleEval("select sqrt(27.0) as col1 ", new String[]{"5.196152422706632"}); + testSimpleEval("select sqrt(64.0) as col1 ", new String[]{"8.0"}); + testSimpleEval("select sqrt(8.0) as col1 ", new String[]{"2.8284271247461903"}); + + + Schema schema = new Schema(); + schema.addColumn("col1", FLOAT4); + schema.addColumn("col2", FLOAT4); + schema.addColumn("col3", FLOAT4); + + testEval(schema, "table1", "1.0, 0.2, 0.1", "select sqrt(col1 + col2 + col3) from table1", + new String[]{"1.1401754564651765"}); + + + Schema schema2 = new Schema(); + schema2.addColumn("col1", FLOAT8); + schema2.addColumn("col2", FLOAT8); + schema2.addColumn("col3", FLOAT8); + + testEval(schema2, "table1", "1.0, 0.2, 0.1", "select sqrt(col1 + col2 + col3) from table1", + new String[]{"1.140175425099138"}); + } + + @Test + public void testExp() throws TajoException { + testSimpleEval("select exp(1.0) as col1 ", new String[]{String.valueOf(Math.exp(1.0d))}); + testSimpleEval("select exp(1.1) as col1 ", new String[]{String.valueOf(Math.exp(1.1d))}); + testSimpleEval("select exp(1.2) as col1 ", new String[]{String.valueOf(Math.exp(1.2d))}); + + + Schema schema = new Schema(); + schema.addColumn("col1", FLOAT4); + + testEval(schema, "table1", "1.123", "select exp(col1) from table1", + new String[]{String.valueOf(Math.exp(1.123f))}); + + Schema schema2 = new Schema(); + schema2.addColumn("col1", FLOAT8); + + testEval(schema2, "table1", "1.123", "select exp(col1) from table1", + new String[]{String.valueOf(Math.exp(1.123d))}); + } + + + @Test + public void testAbs() throws TajoException { + testSimpleEval("select abs(9) as col1 ", new String[]{"9"}); + testSimpleEval("select abs(-9) as col1 ", new String[]{"9"}); + testSimpleEval("select abs(200000000000) as col1 ", new String[]{"200000000000"}); + testSimpleEval("select abs(-200000000000) as col1 ", new String[]{"200000000000"}); + testSimpleEval("select abs(2.0) as col1 ", new String[]{"2.0"}); + testSimpleEval("select abs(-2.0) as col1 ", new String[]{"2.0"}); + + Schema schema = new Schema(); + schema.addColumn("col1", FLOAT4); + schema.addColumn("col2", FLOAT4); + testEval(schema, "table1", "0.39,-0.39", "select abs(col1), abs(col2) from table1", new String[]{"0.39", "0.39"}); + + Schema schema2 = new Schema(); + schema2.addColumn("col1", FLOAT8); + schema2.addColumn("col2", FLOAT8); + testEval(schema2, "table1", "0.033312347,-0.033312347", "select abs(col1), abs(col2) from table1", + new String[]{"0.033312347", "0.033312347"}); + } + + @Test + public void testCbrt() throws TajoException { + testSimpleEval("select cbrt(27.0) as col1 ", new String[]{"3.0"}); + testSimpleEval("select cbrt(64.0) as col1 ", new String[]{"4.0"}); + testSimpleEval("select cbrt(8.0) as col1 ", new String[]{"2.0"}); + + + Schema schema = new Schema(); + schema.addColumn("col1", FLOAT4); + schema.addColumn("col2", FLOAT4); + schema.addColumn("col3", FLOAT4); + testEval(schema, "table1", "1.0, 0.2, 0.1", "select cbrt(col1 + col2 + col3) from table1", + new String[]{"1.0913929030771317"}); + + Schema schema2 = new Schema(); + schema2.addColumn("col1", FLOAT8); + schema2.addColumn("col2", FLOAT8); + schema2.addColumn("col3", FLOAT8); + testEval(schema2, "table1", "1.0, 0.2, 0.1", "select cbrt(col1 + col2 + col3) from table1", + new String[]{"1.091392883061106"}); + } + + @Test + public void testDegrees() throws TajoException { + testSimpleEval("select degrees(0.0) as col1 ", new String[]{String.valueOf(Math.toDegrees(0.0))}); + testSimpleEval("select degrees(0.8) as col1 ", new String[]{String.valueOf(Math.toDegrees(0.8))}); + testSimpleEval("select degrees(2.7) as col1 ", new String[]{String.valueOf(Math.toDegrees(2.7))}); + testSimpleEval("select degrees(-0.8) as col1 ", new String[]{String.valueOf(Math.toDegrees(-0.8))}); + + Schema schema = new Schema(); + schema.addColumn("col1", FLOAT4); + schema.addColumn("col2", FLOAT8); + schema.addColumn("col3", FLOAT8); + + testEval(schema, "table1", "0.8,2.7,-0.8", "select degrees(col1), degrees(col2), degrees(col3) from table1", + new String[]{ + String.valueOf(Math.toDegrees((float)0.8)), + String.valueOf(Math.toDegrees(2.7)), + String.valueOf(Math.toDegrees(-0.8)) + }); + } + + @Test + public void testPow() throws TajoException { + testSimpleEval("select pow(9,3) as col1 ", new String[]{String.valueOf(Math.pow(9, 3))}); + testSimpleEval("select pow(1.0,3) as col1 ", new String[]{String.valueOf(Math.pow(1.0, 3))}); + testSimpleEval("select pow(20.1,3.1) as col1 ", new String[]{String.valueOf(Math.pow(20.1, 3.1))}); + testSimpleEval("select pow(null,3.1) as col1 ", new String[]{""}); + testSimpleEval("select pow(20.1,null) as col1 ", new String[]{""}); + + Schema schema = new Schema(); + schema.addColumn("col1", FLOAT4); + schema.addColumn("col2", FLOAT8); + schema.addColumn("col3", INT4); + schema.addColumn("col4", INT8); + + testEval(schema, "table1", "0.4,2.7,3,2", "select pow(col1, col2), pow(col3, col4) from table1", + new String[]{ + String.valueOf(Math.pow((float) 0.4, 2.7)), + String.valueOf(Math.pow(3, 2)) + }); + } + + @Test + public void testRadians() throws TajoException { + testSimpleEval("select radians(0.0) as col1 ", new String[]{String.valueOf(Math.toRadians(0.0))}); + testSimpleEval("select radians(0.8) as col1 ", new String[]{String.valueOf(Math.toRadians(0.8))}); + testSimpleEval("select radians(2.7) as col1 ", new String[]{String.valueOf(Math.toRadians(2.7))}); + testSimpleEval("select radians(-0.8) as col1 ", new String[]{String.valueOf(Math.toRadians(-0.8))}); + + Schema schema = new Schema(); + schema.addColumn("col1", FLOAT4); + schema.addColumn("col2", FLOAT8); + schema.addColumn("col3", FLOAT8); + + testEval(schema, "table1", "0.8,2.7,-0.8", "select radians(col1), radians(col2), radians(col3) from table1", + new String[]{ + String.valueOf(Math.toRadians((float)0.8)), + String.valueOf(Math.toRadians(2.7)), + String.valueOf(Math.toRadians(-0.8)) + }); + } + + @Test + public void testPi() throws TajoException { + testSimpleEval("select pi() as col1 ", new String[]{String.valueOf(Math.PI)}); + } + + @Test + public void testRoundWithSpecifiedPrecision() throws TajoException { + // TODO - in order to make this test possible, testSimpleEval should take session variables. Now, we disable it. + // divide zero +// try { +// testSimpleEval("select round(10.0/0.0,2) ", new String[]{""}); +// fail("10.0/0 should throw InvalidOperationException"); +// } catch (InvalidOperationException e) { +// //success +// } + + testSimpleEval("select round(42.4382,2) ", new String[]{"42.44"}); + testSimpleEval("select round(-42.4382,2) ", new String[]{"-42.44"}); + testSimpleEval("select round(-425,2) ", new String[]{"-425.0"}); + testSimpleEval("select round(425,2) ", new String[]{"425.0"}); + + testSimpleEval("select round(1234567890,0) ", new String[]{"1.23456789E9"}); + testSimpleEval("select round(1234567890,1) ", new String[]{"1.23456789E9"}); + testSimpleEval("select round(1234567890,2) ", new String[]{"1.23456789E9"}); + + testSimpleEval("select round(1.2345678901234567,13) ", new String[]{"1.2345678901235"}); + testSimpleEval("select round(1234567890.1234567,3) ", new String[]{"1.234567890123E9"}); + testSimpleEval("select round(1234567890.1234567,5) ", new String[]{"1.23456789012346E9"}); + + Schema schema = new Schema(); + schema.addColumn("col1", FLOAT8); + schema.addColumn("col2", INT4); + + testEval(schema, "table1", ",", "select round(col1, col2) from table1", new String[]{""}); + } +}
http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/java/org/apache/tajo/engine/function/TestPatternMatchingPredicates.java ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/java/org/apache/tajo/engine/function/TestPatternMatchingPredicates.java b/tajo-core-tests/src/test/java/org/apache/tajo/engine/function/TestPatternMatchingPredicates.java new file mode 100644 index 0000000..50a8f35 --- /dev/null +++ b/tajo-core-tests/src/test/java/org/apache/tajo/engine/function/TestPatternMatchingPredicates.java @@ -0,0 +1,139 @@ +/** + * 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.Schema; +import org.apache.tajo.engine.eval.ExprTestBase; +import org.apache.tajo.exception.TajoException; +import org.junit.Test; + +import static org.apache.tajo.common.TajoDataTypes.Type.TEXT; + +public class TestPatternMatchingPredicates extends ExprTestBase { + + @Test + public void testLike() throws TajoException { + Schema schema = new Schema(); + schema.addColumn("col1", TEXT); + + // test for null values + testEval(schema, "table1", ",", "select col1 like 'a%' from table1", new String[]{""}); + testSimpleEval("select null like 'a%'", new String[]{""}); + + testEval(schema, "table1", "abc", "select col1 like '%c' from table1", new String[]{"t"}); + testEval(schema, "table1", "abc", "select col1 like 'a%' from table1", new String[]{"t"}); + testEval(schema, "table1", "abc", "select col1 like '_bc' from table1", new String[]{"t"}); + testEval(schema, "table1", "abc", "select col1 like 'ab_' from table1", new String[]{"t"}); + testEval(schema, "table1", "abc", "select col1 like '_b_' from table1", new String[]{"t"}); + testEval(schema, "table1", "abc", "select col1 like '%b%' from table1", new String[]{"t"}); + + // test for escaping regular expressions + testEval(schema, "table1", "abc", "select col1 not like '.bc' from table1", new String[]{"t"}); + testEval(schema, "table1", "abc", "select col1 not like '.*bc' from table1", new String[]{"t"}); + testEval(schema, "table1", "abc", "select col1 not like '.bc' from table1", new String[]{"t"}); + testEval(schema, "table1", "abc", "select col1 not like '*bc' from table1", new String[]{"t"}); + + // test for case sensitive + testEval(schema, "table1", "abc", "select col1 not like '%C' from table1", new String[]{"t"}); + testEval(schema, "table1", "abc", "select col1 not like 'A%' from table1", new String[]{"t"}); + testEval(schema, "table1", "abc", "select col1 not like '_BC' from table1", new String[]{"t"}); + testEval(schema, "table1", "abc", "select col1 not like '_C_' from table1", new String[]{"t"}); + } + + @Test + public void testILike() throws TajoException { + testSimpleEval("select 'abc' ilike '%c'", new String[]{"t"}); + testSimpleEval("select 'abc' ilike 'a%'", new String[]{"t"}); + testSimpleEval("select 'abc' ilike '_bc'", new String[]{"t"}); + testSimpleEval("select 'abc' ilike 'ab_'", new String[]{"t"}); + testSimpleEval("select 'abc' ilike '_b_'", new String[]{"t"}); + testSimpleEval("select 'abc' ilike '%b%'", new String[]{"t"}); + + // test for escaping regular expressions + testSimpleEval("select 'abc' not like '.bc'", new String[]{"t"}); + testSimpleEval("select 'abc' not like '.*bc'", new String[]{"t"}); + testSimpleEval("select 'abc' not like '.bc'", new String[]{"t"}); + testSimpleEval("select 'abc' not like '*bc'", new String[]{"t"}); + + // test for case insensitive + testSimpleEval("select 'abc' ilike '%C'", new String[]{"t"}); + testSimpleEval("select 'abc' ilike 'A%'", new String[]{"t"}); + testSimpleEval("select 'abc' ilike '_BC'", new String[]{"t"}); + testSimpleEval("select 'abc' ilike '_B_'", new String[]{"t"}); + } + + @Test + public void testSimilarToLike() throws TajoException { + testSimpleEval("select 'abc' similar to '%c'", new String[]{"t"}); + testSimpleEval("select 'abc' similar to 'a%'", new String[]{"t"}); + testSimpleEval("select 'abc' similar to '_bc'", new String[]{"t"}); + testSimpleEval("select 'abc' similar to 'ab_'", new String[]{"t"}); + testSimpleEval("select 'abc' similar to '_b_'", new String[]{"t"}); + testSimpleEval("select 'abc' similar to '%b%'", new String[]{"t"}); + + // test for now allowed + testSimpleEval("select 'abc' not similar to '.bc'", new String[]{"t"}); + testSimpleEval("select 'abc' not similar to 'ab.'", new String[]{"t"}); + + // test for escaping regular expressions + testSimpleEval("select 'abc' similar to '(a|f)b%'", new String[]{"t"}); + testSimpleEval("select 'abc' similar to '[a-z]b%'", new String[]{"t"}); + testSimpleEval("select 'abc' similar to '_+bc'", new String[]{"t"}); + testSimpleEval("select 'abc' similar to 'abc'", new String[]{"t"}); + + // test for case sensitive + testSimpleEval("select 'abc' not similar to '%C'", new String[]{"t"}); + testSimpleEval("select 'abc' not similar to '_Bc'", new String[]{"t"}); + } + + @Test + public void testRegexWithSimilarOperator() throws TajoException { + testSimpleEval("select 'abc' ~ '.*c'", new String[]{"t"}); + testSimpleEval("select 'abc' ~ '.*c$'", new String[]{"t"}); + testSimpleEval("select 'aaabc' ~ '([a-z]){3}bc'", new String[]{"t"}); + + // for negative condition + testSimpleEval("select 'abc' !~ '.*c$'", new String[]{"f"}); + + // for case sensitivity + testSimpleEval("select 'abc' ~ '.*C'", new String[]{"f"}); + + // for case insensitivity + testSimpleEval("select 'abc' ~* '.*C'", new String[]{"t"}); + testSimpleEval("select 'abc' !~* '.*C'", new String[]{"f"}); + } + + @Test + public void testRegexp() throws TajoException { + testSimpleEval("select 'abc' regexp '.*c'", new String[]{"t"}); + testSimpleEval("select 'abc' regexp '.*c$'", new String[]{"t"}); + + // for negative condition + testSimpleEval("select 'abc' not regexp '.*c$'", new String[]{"f"}); + } + + @Test + public void testRLike() throws TajoException { + testSimpleEval("select 'abc' rlike '.*c'", new String[]{"t"}); + testSimpleEval("select 'abc' rlike '.*c$'", new String[]{"t"}); + + // for negative condition + testSimpleEval("select 'abc' not rlike '.*c$'", new String[]{"f"}); + } +} http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/java/org/apache/tajo/engine/function/TestPythonFunctions.java ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/java/org/apache/tajo/engine/function/TestPythonFunctions.java b/tajo-core-tests/src/test/java/org/apache/tajo/engine/function/TestPythonFunctions.java new file mode 100644 index 0000000..1b29743 --- /dev/null +++ b/tajo-core-tests/src/test/java/org/apache/tajo/engine/function/TestPythonFunctions.java @@ -0,0 +1,43 @@ +/* + * 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 TestPythonFunctions extends ExprTestBase { + + @Test + public void testFunctions() throws TajoException { + testSimpleEval("select return_one()", new String[]{"1"}); + testSimpleEval("select helloworld()", new String[]{"Hello, World"}); + testSimpleEval("select concat_py('1')", new String[]{"11"}); + testSimpleEval("select comma_format(12345)", new String[]{"12,345"}); + testSimpleEval("select add_py(1,2)", new String[]{"3"}); + testSimpleEval("select percent(386, 1000)", new String[]{"38.6"}); + testSimpleEval("select concat4('Tajo', 'is', 'awesome', '!')", new String[]{"Tajo is awesome !"}); + } + + @Test + public void testNestedFunctions() throws TajoException { + testSimpleEval("select add_py(3, return_one())", new String[]{"4"}); + testSimpleEval("select concat_py(helloworld())", new String[]{"Hello, WorldHello, World"}); + } +} http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/java/org/apache/tajo/engine/function/TestStringOperatorsAndFunctions.java ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/java/org/apache/tajo/engine/function/TestStringOperatorsAndFunctions.java b/tajo-core-tests/src/test/java/org/apache/tajo/engine/function/TestStringOperatorsAndFunctions.java new file mode 100644 index 0000000..366d8ed --- /dev/null +++ b/tajo-core-tests/src/test/java/org/apache/tajo/engine/function/TestStringOperatorsAndFunctions.java @@ -0,0 +1,615 @@ +/** + * 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.commons.lang.StringEscapeUtils; +import org.apache.tajo.catalog.Schema; +import org.apache.tajo.engine.eval.ExprTestBase; +import org.apache.tajo.exception.TajoException; +import org.junit.Test; + +import static org.apache.tajo.common.TajoDataTypes.Type.*; + +public class TestStringOperatorsAndFunctions extends ExprTestBase { + + @Test + public void testConcatenateOnLiteral() throws TajoException { + testSimpleEval("select ('abc' || 'def') col1 ", new String[]{"abcdef"}); + testSimpleEval("select 'abc' || 'def' as col1 ", new String[]{"abcdef"}); + testSimpleEval("select 1 || 'def' as col1 ", new String[]{"1def"}); + testSimpleEval("select 'abc' || 2 as col1 ", new String[]{"abc2"}); + } + + @Test + public void testConcatenateOnExpressions() throws TajoException { + Schema schema = new Schema(); + schema.addColumn("col1", TEXT); + schema.addColumn("col2", INT4); + schema.addColumn("col3", FLOAT8); + + testSimpleEval("select (1+3) || 2 as col1 ", new String[]{"42"}); + + testEval(schema, "table1", "abc,2,3.14", "select col1 || col2 || col3 from table1", new String[]{"abc23.14"}); + testEval(schema, "table1", "abc,2,3.14", "select col1 || '---' || col3 from table1", new String[]{"abc---3.14"}); + } + + @Test + public void testFunctionCallIngoreCases() throws TajoException { + testSimpleEval("select ltrim(' trim') ", new String[]{"trim"}); + testSimpleEval("select LTRIM(' trim') ", new String[]{"trim"}); + testSimpleEval("select lTRim(' trim') ", new String[]{"trim"}); + testSimpleEval("select ltrIM(' trim') ", new String[]{"trim"}); + } + + @Test + public void testLTrim() throws TajoException { + Schema schema = new Schema(); + schema.addColumn("col1", TEXT); + schema.addColumn("col2", TEXT); + + testSimpleEval("select ltrim(' trim') ", new String[]{"trim"}); + testSimpleEval("select ltrim('xxtrim', 'xx') ", new String[]{"trim"}); + + testSimpleEval("select trim(leading 'xx' from 'xxtrim') ", new String[]{"trim"}); + testSimpleEval("select trim(leading from ' trim') ", new String[]{"trim"}); + testSimpleEval("select trim(' trim') ", new String[]{"trim"}); + + testEval(schema, "table1", " trim,abc", "select ltrim(col1) from table1", new String[]{"trim"}); + testEval(schema, "table1", "xxtrim,abc", "select ltrim(col1, 'xx') from table1", new String[]{"trim"}); + testEval(schema, "table1", "xxtrim,abc", "select trim(leading 'xx' from col1) from table1", new String[]{"trim"}); + + testEval(schema, "table1", " trim, abc", "select ltrim(col1) || ltrim(col2) from table1", + new String[]{"trimabc"}); + } + + @Test + public void testRTrim() throws TajoException { + Schema schema = new Schema(); + schema.addColumn("col1", TEXT); + schema.addColumn("col2", TEXT); + + testSimpleEval("select rtrim('trim ') ", new String[]{"trim"}); + testSimpleEval("select rtrim('trimxx', 'xx') ", new String[]{"trim"}); + + testSimpleEval("select trim(trailing 'xx' from 'trimxx') ", new String[]{"trim"}); + testSimpleEval("select trim(trailing from 'trim ') ", new String[]{"trim"}); + testSimpleEval("select trim('trim ') ", new String[]{"trim"}); + + testEval(schema, "table1", "trim ,abc", "select rtrim(col1) from table1", new String[]{"trim"}); + testEval(schema, "table1", "trimxx,abc", "select rtrim(col1, 'xx') from table1", new String[]{"trim"}); + testEval(schema, "table1", "trimxx,abc", "select trim(trailing 'xx' from col1) from table1", new String[]{"trim"}); + + testEval(schema, "table1", "trim ,abc ", "select rtrim(col1) || rtrim(col2) from table1", + new String[]{"trimabc"}); + } + + @Test + public void testTrim() throws TajoException { + Schema schema = new Schema(); + schema.addColumn("col1", TEXT); + schema.addColumn("col2", TEXT); + + testSimpleEval("select trim(' trim ') ", new String[]{"trim"}); + testSimpleEval("select btrim('xxtrimxx', 'xx') ", new String[]{"trim"}); + + testSimpleEval("select trim(both 'xx' from 'xxtrimxx') ", new String[]{"trim"}); + testSimpleEval("select trim(both from ' trim ') ", new String[]{"trim"}); + testSimpleEval("select trim(' trim ') ", new String[]{"trim"}); + + testEval(schema, "table1", " trim ,abc", "select trim(col1) from table1", new String[]{"trim"}); + testEval(schema, "table1", "xxtrimxx,abc", "select trim(col1, 'xx') from table1", new String[]{"trim"}); + testEval(schema, "table1", "xxtrimxx,abc", "select trim(both 'xx' from col1) from table1", new String[]{"trim"}); + + testEval(schema, "table1", " trim ,xxabcxx", "select trim(col1) || trim(col2,'xx') from table1", + new String[]{"trimabc"}); + } + + @Test + public void testRegexReplace() throws TajoException { + testSimpleEval("select regexp_replace('abcdef','bc','--') as col1 ", new String[]{"a--def"}); + + // null test + testSimpleEval("select regexp_replace(null, 'bc', '--') as col1 ", new String[]{""}); + testSimpleEval("select regexp_replace('abcdef', null, '--') as col1 ", new String[]{""}); + testSimpleEval("select regexp_replace('abcdef','bc', null) as col1 ", new String[]{""}); + + Schema schema = new Schema(); + schema.addColumn("col1", TEXT); + schema.addColumn("col2", TEXT); + schema.addColumn("col3", TEXT); + + // find matches and replace from column values + testEval(schema, "table1", "------,(^--|--$),ab", "select regexp_replace(col1, col2, col3) as str from table1", + new String[]{"ab--ab"}); + + // null test from a table + testEval(schema, "table1", ",(^--|--$),ab", "select regexp_replace(col1, col2, col3) as str from table1", + new String[]{""}); + testEval(schema, "table1", "------,(^--|--$),", "select regexp_replace(col1, col2, col3) as str from table1", + new String[]{""}); + } + + @Test + public void testLeft() throws TajoException { + testSimpleEval("select left('abcdef',1) as col1 ", new String[]{"a"}); + testSimpleEval("select left('abcdef',2) as col1 ", new String[]{"ab"}); + testSimpleEval("select left('abcdef',3) as col1 ", new String[]{"abc"}); + testSimpleEval("select left('abcdef',4) as col1 ", new String[]{"abcd"}); + testSimpleEval("select left('abcdef',5) as col1 ", new String[]{"abcde"}); + testSimpleEval("select left('abcdef',6) as col1 ", new String[]{"abcdef"}); + testSimpleEval("select left('abcdef',7) as col1 ", new String[]{"abcdef"}); + + testSimpleEval("select left('abcdef',-1) as col1 ", new String[]{"abcde"}); + testSimpleEval("select left('abcdef',-2) as col1 ", new String[]{"abcd"}); + testSimpleEval("select left('abcdef',-3) as col1 ", new String[]{"abc"}); + testSimpleEval("select left('abcdef',-4) as col1 ", new String[]{"ab"}); + testSimpleEval("select left('abcdef',-5) as col1 ", new String[]{"a"}); + testSimpleEval("select left('abcdef',-6) as col1 ", new String[]{""}); + + Schema schema = new Schema(); + schema.addColumn("col1", TEXT); + schema.addColumn("col2", INT4); + schema.addColumn("col3", TEXT); + + // for null tests + testEval(schema, "table1", ",1,ghi", "select left(col1,1) is null from table1", new String[]{"t"}); + testEval(schema, "table1", "abc,,ghi", "select left(col1,col2) is null from table1", new String[]{"t"}); + + testEval(schema, "table1", "abc,1,ghi", "select left(col1,1) || left(col3,3) from table1", new String[]{"aghi"}); + } + + @Test + public void testRight() throws TajoException { + testSimpleEval("select right('abcdef',1) as col1 ", new String[]{"f"}); + testSimpleEval("select right('abcdef',2) as col1 ", new String[]{"ef"}); + testSimpleEval("select right('abcdef',3) as col1 ", new String[]{"def"}); + testSimpleEval("select right('abcdef',4) as col1 ", new String[]{"cdef"}); + testSimpleEval("select right('abcdef',5) as col1 ", new String[]{"bcdef"}); + testSimpleEval("select right('abcdef',6) as col1 ", new String[]{"abcdef"}); + testSimpleEval("select right('abcdef',7) as col1 ", new String[]{"abcdef"}); + + testSimpleEval("select right('abcdef',-1) as col1 ", new String[]{"bcdef"}); + testSimpleEval("select right('abcdef',-2) as col1 ", new String[]{"cdef"}); + testSimpleEval("select right('abcdef',-3) as col1 ", new String[]{"def"}); + testSimpleEval("select right('abcdef',-4) as col1 ", new String[]{"ef"}); + testSimpleEval("select right('abcdef',-5) as col1 ", new String[]{"f"}); + testSimpleEval("select right('abcdef',-6) as col1 ", new String[]{""}); + + Schema schema = new Schema(); + schema.addColumn("col1", TEXT); + schema.addColumn("col2", INT4); + schema.addColumn("col3", TEXT); + + // for null tests + testEval(schema, "table1", ",1,ghi", "select right(col1,1) is null from table1", new String[]{"t"}); + testEval(schema, "table1", "abc,,ghi", "select right(col1,col2) is null from table1", new String[]{"t"}); + + testEval(schema, "table1", "abc,1,ghi", "select right(col1,1) || right(col3,3) from table1", new String[]{"cghi"}); + } + + @Test + public void testReverse() throws TajoException { + testSimpleEval("select reverse('abcdef') as col1 ", new String[]{"fedcba"}); + testSimpleEval("select reverse('ê°') as col1 ", new String[]{"ê°"}); + + Schema schema = new Schema(); + schema.addColumn("col1", TEXT); + schema.addColumn("col2", TEXT); + schema.addColumn("col3", TEXT); + testEval(schema, "table1", "abc,efg,3.14", "select reverse(col1) || reverse(col2) from table1", + new String[]{"cbagfe"}); + } + + @Test + public void testRepeat() throws TajoException { + testSimpleEval("select repeat('ab',4) as col1 ", new String[]{"abababab"}); + testSimpleEval("select repeat('ê°',3) as col1 ", new String[]{"ê°ê°ê°"}); + testSimpleEval("select repeat('a',2) as col1 ", new String[]{"aa"}); + + Schema schema = new Schema(); + schema.addColumn("col1", TEXT); + schema.addColumn("col2", TEXT); + schema.addColumn("col3", TEXT); + testEval(schema, "table1", "abc,efg,3.14", "select repeat(col1,2) from table1", new String[]{"abcabc"}); + } + + + @Test + public void testUpper() throws TajoException { + testSimpleEval("select upper('abcdef') as col1 ", new String[]{"ABCDEF"}); + + Schema schema = new Schema(); + schema.addColumn("col1", TEXT); + schema.addColumn("col2", TEXT); + schema.addColumn("col3", TEXT); + testEval(schema, "table1", "abc,efg,3.14", "select upper(col1), upper(col2) from table1", + new String[]{"ABC", "EFG"}); + testEval(schema, "table1", "abc,efg,3.14", "select upper(col1) || upper(col2) from table1", new String[]{"ABCEFG"}); + } + + @Test + public void testLower() throws TajoException { + testSimpleEval("select lower('ABCdEF') as col1 ", new String[]{"abcdef"}); + + Schema schema = new Schema(); + schema.addColumn("col1", TEXT); + schema.addColumn("col2", TEXT); + schema.addColumn("col3", TEXT); + testEval(schema, "table1", "ABC,DEF,3.14", "select lower(col1), lower(col2) from table1", + new String[]{"abc", "def"}); + testEval(schema, "table1", "ABC,DEF,3.14", "select lower(col1) || lower(col2) from table1", new String[]{"abcdef"}); + } + + @Test + public void testCharLength() throws TajoException { + testSimpleEval("select char_length('123456') as col1 ", new String[]{"6"}); + + Schema schema = new Schema(); + schema.addColumn("col1", TEXT); + schema.addColumn("col2", TEXT); + schema.addColumn("col3", TEXT); + testEval(schema, "table1", "ABC,DEF,3.14", "select character_length(lower(col1) || lower(col2)) from table1", + new String[]{"6"}); + } + + @Test + public void testLength() throws TajoException { + testSimpleEval("select length('123456') as col1 ", new String[]{"6"}); + + Schema schema = new Schema(); + schema.addColumn("col1", TEXT); + schema.addColumn("col2", TEXT); + schema.addColumn("col3", TEXT); + testEval(schema, "table1", "ABC,DEF,3.14", "select length(lower(col1) || lower(col2)) from table1", + new String[]{"6"}); + } + + @Test + public void testMd5() throws TajoException { + testSimpleEval("select md5('1') as col1 ", new String[]{"c4ca4238a0b923820dcc509a6f75849b"}); + testSimpleEval("select md5('tajo') as col1 ", new String[]{"742721b3a79f71a9491681b8e8a7ce85"}); + + Schema schema = new Schema(); + schema.addColumn("col1", TEXT); + schema.addColumn("col2", TEXT); + schema.addColumn("col3", TEXT); + testEval(schema, "table1", "abc,efg,3.14", "select md5(col1) from table1", + new String[]{"900150983cd24fb0d6963f7d28e17f72"}); + } + + @Test + public void testDigest() throws TajoException { + testSimpleEval("select digest('tajo', 'md2') as col1 ", new String[]{"bf523bce8241982f6bea9af0f7fd37ff"}); + testSimpleEval("select digest('tajo', 'md5') as col1 ", new String[]{"742721b3a79f71a9491681b8e8a7ce85"}); + testSimpleEval("select digest('tajo', 'sha1') as col1 ", new String[]{"02b0e20540b89f0b735092bbac8093eb2e3804cf"}); + testSimpleEval("select digest('tajo', 'sha256') as col1 ", + new String[]{"6440083be076869a9f9d0271a4bf298d98c8aa3ecb49df841895fbcddbb04a70"}); + testSimpleEval("select digest('tajo', 'sha384') as col1 ", + new String[]{"59ff99b0e274eb3d8e10f221b6b949bfc1244d2a1226c5c720062fb03d82272be633e4a0f2babccffbfdff7cc1cb06fb"}); + testSimpleEval("select digest('tajo', 'sha512') as col1 ", + new String[]{"ee8ba254d331ddfb1bca9aaf0c4b8c58aea5331928cbd20168c87828afb853b0c096af71ec69a23b669217a1dddd2934edaac33b1296fe526b22abd28a15c4b3"}); + testSimpleEval("select digest('tajo', 'not') as col1 ", new String[]{""}); + } + + @Test + public void testHex() throws TajoException { + testSimpleEval("select to_hex(1) as col1 ", new String[]{"1"}); + testSimpleEval("select to_hex(10) as col1 ", new String[]{"a"}); + testSimpleEval("select to_hex(1234) as col1 ", new String[]{"4d2"}); + testSimpleEval("select to_hex(1023456788888888) as col1 ", new String[]{"3a2d41a583d38"}); + + Schema schema = new Schema(); + schema.addColumn("col1", TEXT); + schema.addColumn("col2", TEXT); + schema.addColumn("col3", TEXT); + testEval(schema, "table1", ",abcdef,3.14", "select to_hex(10) from table1", + new String[]{"a"}); + } + + @Test + public void testBin() throws TajoException { + testSimpleEval("select to_bin(1) as col1 ", new String[]{"1"}); + testSimpleEval("select to_bin(10) as col1 ", new String[]{"1010"}); + testSimpleEval("select to_bin(1234) as col1 ", new String[]{"10011010010"}); + + Schema schema = new Schema(); + schema.addColumn("col1", TEXT); + schema.addColumn("col2", TEXT); + schema.addColumn("col3", TEXT); + testEval(schema, "table1", ",abcdef,3.14", "select to_bin(20) from table1", + new String[]{"10100"}); + } + + @Test + public void testOctetLength() throws TajoException { + testSimpleEval("select octet_length('123456') as col1 ", new String[]{"6"}); + testSimpleEval("select octet_length('1') as col1 ", new String[]{"1"}); + testSimpleEval("select octet_length('ê°') as col1 ", new String[]{"3"}); + + Schema schema = new Schema(); + schema.addColumn("col1", TEXT); + schema.addColumn("col2", TEXT); + schema.addColumn("col3", TEXT); + testEval(schema, "table1", "ABC,DEF,3.14", "select octet_length(lower(col1) || lower(col2)) from table1", + new String[]{"6"}); + } + + @Test + public void testSplitPart() throws TajoException { + testSimpleEval("select split_part('1386577650.123', '.', 1) as col1 ", new String[]{"1386577650"}); + testSimpleEval("select split_part('1386577650.123', '.', 2) as col1 ", new String[]{"123"}); + // If part is larger than the number of string portions, it will returns NULL. + testSimpleEval("select split_part('1386577650.123', '.', 3) is null", new String[]{"t"}); + + // null handling tests + Schema schema = new Schema(); + schema.addColumn("col1", TEXT); + schema.addColumn("col2", TEXT); + schema.addColumn("col3", TEXT); + testEval(schema, "t1", ",.,1", "select split_part(col1, col2, col3::int) is null from t1", new String[]{"t"}); + testEval(schema, "t1", "1386577650.123,,1", "select split_part(col1, col2, col3::int) from t1", + new String[]{"1386577650.123"}); + testEval(schema, "t1", "1386577650.123,.,", "select split_part(col1, col2, col3::int) is null from t1", + new String[]{"t"}); + } + + @Test + public void testSubstr() throws TajoException { + testSimpleEval("select substr('abcdef', 3, 2) as col1 ", new String[]{"cd"}); + testSimpleEval("select substr('abcdef', 3) as col1 ", new String[]{"cdef"}); + testSimpleEval("select substr('abcdef', 1, 1) as col1 ", new String[]{"a"}); + testSimpleEval("select substr('abcdef', 0, 1) as col1 ", new String[]{""}); + testSimpleEval("select substr('abcdef', 0, 2) as col1 ", new String[]{"a"}); + testSimpleEval("select substr('abcdef', 0) as col1 ", new String[]{"abcdef"}); + testSimpleEval("select substr('abcdef', 1, 100) as col1 ", new String[]{"abcdef"}); + testSimpleEval("select substr('abcdef', 0, 100) as col1 ", new String[]{"abcdef"}); + testSimpleEval("select substr('ì¼ì´ì¼ì¬ì¤', 2, 2) as col1 ", new String[]{"ì´ì¼"}); + testSimpleEval("select substr('ì¼ì´ì¼ì¬ì¤', 3) as col1 ", new String[]{"ì¼ì¬ì¤"}); + + testSimpleEval("select substr('abcdef', -1) as col1 ", new String[]{"abcdef"}); + testSimpleEval("select substr('abcdef', -1, 100) as col1 ", new String[]{"abcdef"}); + testSimpleEval("select substr('abcdef', -1, 3) as col1 ", new String[]{"a"}); + testSimpleEval("select substr('abcdef', -1, 1) as col1 ", new String[]{""}); + + Schema schema = new Schema(); + schema.addColumn("col1", TEXT); + schema.addColumn("col2", TEXT); + schema.addColumn("col3", TEXT); + testEval(schema, "table1", ",abcdef,3.14", "select substr(lower(col2), 2, 3) from table1", + new String[]{"bcd"}); + } + + @Test + public void testLocate() throws TajoException { + // normal case + testSimpleEval("select locate('abcdef', 'a') as col1 ", new String[]{"1"}); + testSimpleEval("select locate('abcdef', 'a', 0) as col1 ", new String[]{"1"}); + testSimpleEval("select locate('abcdef', 'a', 1) as col1 ", new String[]{"1"}); + testSimpleEval("select locate('abcdef', 'z') as col1 ", new String[]{"0"}); + testSimpleEval("select locate('abcdef', 'z', 1) as col1 ", new String[]{"0"}); + testSimpleEval("select locate('foobarbar', 'bar') as col1 ", new String[]{"4"}); + testSimpleEval("select locate('foobarbar', 'bar', 0) as col1 ", new String[]{"4"}); + testSimpleEval("select locate('foobarbar', 'bar', 1) as col1 ", new String[]{"4"}); + testSimpleEval("select locate('foobarbar', 'bar', 5) as col1 ", new String[]{"7"}); + testSimpleEval("select locate('foobarbar', 'bar', 9) as col1 ", new String[]{"0"}); + testSimpleEval("select locate('ê°ëë¤ë¼ë§ë¼ë§', 'ë¼ë§') as col1 ", new String[]{"4"}); + testSimpleEval("select locate('ê°ëë¤ë¼ë§ë¼ë§', 'ë¼ë§', 5) as col1 ", new String[]{"6"}); + // empty string + testSimpleEval("select locate('abcdef', '') as col1 ", new String[]{"1"}); + testSimpleEval("select locate('abcdef', '', 2) as col1 ", new String[]{"2"}); + // pos = last index of string (expected value(6) is tested on mysql) + testSimpleEval("select locate('abcdef', '', 6) as col1 ", new String[]{"6"}); + // pos = last index + 1 (expected value(7) is tested on mysql) + testSimpleEval("select locate('abcdef', '', 7) as col1 ", new String[]{"7"}); + // pos = greater then last index + 1 (expected value(0) is tested on mysql) + testSimpleEval("select locate('abcdef', '', 8) as col1 ", new String[]{"0"}); + // pos = greater then last index + 1 (expected value(0) is tested on mysql) + testSimpleEval("select locate('abcdef', '', 9) as col1 ", new String[]{"0"}); + testSimpleEval("select locate('ê°ëë¤ë¼', '', 2) as col1 ", new String[]{"2"}); + testSimpleEval("select locate('ê°ëë¤ë¼', '', 4) as col1 ", new String[]{"4"}); + testSimpleEval("select locate('ê°ëë¤ë¼', '', 5) as col1 ", new String[]{"5"}); + testSimpleEval("select locate('ê°ëë¤ë¼', '', 6) as col1 ", new String[]{"0"}); + + // negative pos + testSimpleEval("select locate('abcdef', 'a', -1) as col1 ", new String[]{"0"}); + testSimpleEval("select locate('abcdef', 'a', -5) as col1 ", new String[]{"0"}); + + Schema schema = new Schema(); + schema.addColumn("col1", TEXT); + schema.addColumn("col2", TEXT); + schema.addColumn("col3", TEXT); + testEval(schema, "table1", ",abcdef,3.14", "select locate(col2, 'cd') from table1", new String[]{"3"}); + testEval(schema, "table1", ",abcdef,3.14", "select locate(col2, 'cd', 1) from table1", new String[]{"3"}); + testEval(schema, "table1", ",abcdef,3.14", "select locate(col2, 'cd', 4) from table1", new String[]{"0"}); + testEval(schema, "table1", ",abcdef,3.14", "select locate(col2, 'xy') from table1", new String[]{"0"}); + // null string + testEval(schema, "table1", ",abcdef,3.14", "select locate(col1, 'cd') is null from table1", new String[]{"t"}); + // nul substring + testEval(schema, "table1", ",abcdef,3.14", "select locate('cd', col1) is null from table1", new String[]{"t"}); + } + + @Test + public void testBitLength() throws TajoException { + testSimpleEval("select bit_length('123456') as col1 ", new String[]{"48"}); + + Schema schema = new Schema(); + schema.addColumn("col1", TEXT); + schema.addColumn("col2", TEXT); + schema.addColumn("col3", TEXT); + testEval(schema, "table1", "ABC,DEF,3.14", "select bit_length(lower(col1) || lower(col2)) from table1", + new String[]{"48"}); + } + + @Test + public void testStrpos() throws TajoException { + testSimpleEval("select strpos('tajo','jo') as col1 ", new String[]{"3"}); + testSimpleEval("select strpos('tajo','') as col1 ", new String[]{"1"}); + testSimpleEval("select strpos('tajo','abcdef') as col1 ", new String[]{"0"}); + testSimpleEval("select strpos('ì¼ì´ì¼ì¬ì¤ì¡','ì¼ì¬') as col1 ", new String[]{"3"}); + testSimpleEval("select strpos('ì¼ì´ì¼ì¬ì¤ì¡','ì¼ì´ì¼') as col1 ", new String[]{"1"}); + + Schema schema = new Schema(); + schema.addColumn("col1", TEXT); + schema.addColumn("col2", TEXT); + schema.addColumn("col3", TEXT); + testEval(schema, "table1", "ABCDEF,HIJKLMN,3.14", "select strpos(lower(col1) || lower(col2), 'fh') from table1", + new String[]{"6"}); + } + + @Test + public void testStrposb() throws TajoException { + testSimpleEval("select strposb('tajo','jo') as col1 ", new String[]{"3"}); + testSimpleEval("select strposb('tajo','') as col1 ", new String[]{"1"}); + testSimpleEval("select strposb('tajo','abcdef') as col1 ", new String[]{"0"}); + testSimpleEval("select strposb('ì¼ì´ì¼ì¬ì¤ì¡','ì¼ì¬') as col1 ", new String[]{"7"}); //utf8 1 korean word = 3 chars + testSimpleEval("select strposb('ì¼ì´ì¼ì¬ì¤ì¡','ì¼ì¬ì¼') as col1 ", new String[]{"0"}); + + Schema schema = new Schema(); + schema.addColumn("col1", TEXT); + schema.addColumn("col2", TEXT); + schema.addColumn("col3", TEXT); + testEval(schema, "table1", "ABCDEF,HIJKLMN,3.14", "select strposb(lower(col1) || lower(col2), 'fh') from table1", + new String[]{"6"}); + } + + @Test + public void testInitcap() throws TajoException { + testSimpleEval("select initcap('hi bro') ", new String[]{"Hi Bro"}); + testSimpleEval("select initcap('HI BRO') ", new String[]{"Hi Bro"}); + } + + @Test + public void testAscii() throws TajoException { + testSimpleEval("select ascii('abc') as col1 ", new String[]{"97"}); + + Schema schema = new Schema(); + schema.addColumn("col1", TEXT); + testEval(schema, "table1", "abc", "select ascii(col1) from table1", + new String[]{"97"}); + testEval(schema, "table1", "12", "select ascii(col1) from table1", + new String[]{"49"}); + + } + + @Test + public void testChr() throws TajoException { + testSimpleEval("select chr(48) as col1 ", new String[]{"0"}); + testSimpleEval("select chr(49) as col1 ", new String[]{"1"}); + testSimpleEval("select chr(50) as col1 ", new String[]{"2"}); + testSimpleEval("select chr(64) as col1 ", new String[]{"@"}); + + Schema schema = new Schema(); + schema.addColumn("col1", INT4); + testEval(schema, "table1", "65", "select chr(col1) from table1", new String[]{"A"}); + testEval(schema, "table1", "66", "select chr(col1) from table1", new String[]{"B"}); + testEval(schema, "table1", "52512", "select chr(col1) from table1", new String[]{"ì´ "}); + } + + @Test + public void testLpad() throws TajoException { + testSimpleEval("select lpad('hi', 5, 'xy') ", new String[]{"xyxhi"}); + testSimpleEval("select LPAD('hello', 7, 'xy') ", new String[]{"xyhello"}); + testSimpleEval("select LPAD('hello', 3, 'xy') ", new String[]{"hel"}); + testSimpleEval("select lPAD('hello', 7) ", new String[]{" hello"}); + testSimpleEval("select lPAD('ê°ëë¤ë¼', 3) ", new String[]{"ê°ëë¤"}); + + } + + @Test + public void testRpad() throws TajoException { + testSimpleEval("select rpad('hi', 5, 'xy') ", new String[]{"hixyx"}); + testSimpleEval("select RPAD('hello', 7, 'xy') ", new String[]{"helloxy"}); + testSimpleEval("select RPAD('hello', 3, 'xy') ", new String[]{"hel"}); + testSimpleEval("select rPAD('hello', 7) ", new String[]{"hello "}); + testSimpleEval("select rPAD('ê°ëë¤ë¼', 3) ", new String[]{"ê°ëë¤"}); + + } + + @Test + public void testQuote_ident() throws TajoException { + testSimpleEval("select quote_ident('Foo bar') ", new String[]{"\"Foo bar\""}); + testSimpleEval("select QUOTE_IDENT('Tajo Function') ", new String[]{"\"Tajo Function\""}); + } + + @Test + public void testEncode() throws TajoException { + testSimpleEval("select encode('Hello\nworld', 'base64') ", new String[]{"SGVsbG8Kd29ybGQ="}); + testSimpleEval("select encode('Hello\nworld', 'hex') ", + new String[]{"0x480x650x6c0x6c0x6f0x0a0x770x6f0x720x6c0x64"}); + testSimpleEval("select encode('íê¸', 'base64') ", new String[]{"7ZWc6riA"}); + testSimpleEval("select encode('íê¸', 'hex') ", new String[]{"0xd55c0xae00"}); + testSimpleEval("select encode('íê¸\ní ì¤í¸\tì ëë¤.', 'hex') ", + new String[]{"0xd55c0xae000x0a0xd14c0xc2a40xd2b80x090xc7850xb2c80xb2e40x2e"}); + } + + + @Test + public void testDecode() throws TajoException { + testSimpleEval("select decode('SGVsbG8Kd29ybGQ=', 'base64') ", + new String[]{StringEscapeUtils.escapeJava("Hello\nworld")}); + testSimpleEval("select decode('0x480x650x6c0x6c0x6f0x0a0x770x6f0x720x6c0x64', 'hex') ", + new String[]{StringEscapeUtils.escapeJava("Hello\nworld")}); + testSimpleEval("select decode('7ZWc6riA', 'base64') ", new String[]{StringEscapeUtils.escapeJava("íê¸")}); + testSimpleEval("select decode('0xd55c0xae00', 'hex') ", new String[]{StringEscapeUtils.escapeJava("íê¸")}); + testSimpleEval("select decode('0xd55c0xae000x0a0xd14c0xc2a40xd2b80x090xc7850xb2c80xb2e40x2e', 'hex') ", + new String[]{StringEscapeUtils.escapeJava("íê¸\n" + "í ì¤í¸\tì ëë¤.")}); + } + + @Test + public void testFindInSet() throws TajoException { + // abnormal cases + testSimpleEval("select find_in_set('cr','crt') as col1 ", new String[]{"0"}); // there is no matched string + testSimpleEval("select find_in_set('c,r','crt,c,cr,c,def') as col1 ", new String[]{"0"}); // abnormal parameter + + // normal cases + testSimpleEval("select find_in_set('crt','crt,c,cr,d,def') as col1 ", new String[]{"1"}); + testSimpleEval("select find_in_set('c','crt,c,cr,d,def') as col1 ", new String[]{"2"}); + testSimpleEval("select find_in_set('def','crt,c,cr,d,def') as col1 ", new String[]{"5"}); + // unicode test + testSimpleEval("select find_in_set('ë¸ê¸°','ì¬ê³¼,ë°°,ì¥ìì,ê°ì,ë¸ê¸°,ìë°') as col1 ", new String[]{"5"}); + + // null test + Schema schema = new Schema(); + schema.addColumn("col1", TEXT); + schema.addColumn("col2", TEXT); + testEval(schema, "table1", "|crt,c,cr,c,def", "select find_in_set(col1, col2) is null from table1", + new String[]{"t"}, '|', true); + testEval(schema, "table1", "cr|", "select find_in_set(col1, col2) is null from table1", + new String[]{"t"}, '|', true); + } + + @Test + public void testConcat() throws TajoException { + testSimpleEval("select concat('333', '22') ", new String[]{"33322"}); + testSimpleEval("select concat('íê¸', '22') ", new String[]{"íê¸22"}); + testSimpleEval("select concat(null, '22') ", new String[]{"22"}); + testSimpleEval("select concat('22',null) ", new String[]{"22"}); + testSimpleEval("select concat('22',null, '33', null, '44') ", new String[]{"223344"}); + } + + @Test + public void testConcat_ws() throws TajoException { + testSimpleEval("select concat_ws(',', '333', '22') ", new String[]{"333,22"}); + testSimpleEval("select concat_ws(',', 'íê¸', '22') ", new String[]{"íê¸,22"}); + testSimpleEval("select concat_ws(',', '22', null) ", new String[]{"22"}); + testSimpleEval("select concat_ws(',', '22', '33', '33') ", new String[]{"22,33,33"}); + testSimpleEval("select concat_ws(',', null, '22') ", new String[]{"22"}); + } +}
