| /* |
| * 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.calcite.test; |
| |
| import org.apache.calcite.adapter.enumerable.CallImplementor; |
| import org.apache.calcite.adapter.java.ReflectiveSchema; |
| import org.apache.calcite.jdbc.CalciteConnection; |
| import org.apache.calcite.linq4j.Ord; |
| import org.apache.calcite.linq4j.function.SemiStrict; |
| import org.apache.calcite.linq4j.tree.Expressions; |
| import org.apache.calcite.linq4j.tree.Types; |
| import org.apache.calcite.rel.type.RelDataType; |
| import org.apache.calcite.rel.type.RelDataTypeFactory; |
| import org.apache.calcite.rel.type.RelProtoDataType; |
| import org.apache.calcite.schema.FunctionParameter; |
| import org.apache.calcite.schema.ImplementableFunction; |
| import org.apache.calcite.schema.ScalarFunction; |
| import org.apache.calcite.schema.SchemaPlus; |
| import org.apache.calcite.schema.impl.AbstractSchema; |
| import org.apache.calcite.schema.impl.ScalarFunctionImpl; |
| import org.apache.calcite.schema.impl.ViewTable; |
| import org.apache.calcite.sql.type.SqlTypeName; |
| import org.apache.calcite.test.schemata.hr.HrSchema; |
| import org.apache.calcite.util.Smalls; |
| |
| import com.google.common.collect.ImmutableList; |
| |
| import org.junit.jupiter.api.Disabled; |
| import org.junit.jupiter.api.Test; |
| |
| import java.lang.reflect.Method; |
| import java.sql.Connection; |
| import java.sql.DriverManager; |
| import java.sql.ResultSet; |
| import java.sql.Statement; |
| import java.util.ArrayList; |
| import java.util.List; |
| import java.util.concurrent.atomic.AtomicInteger; |
| |
| import static org.hamcrest.CoreMatchers.is; |
| import static org.hamcrest.MatcherAssert.assertThat; |
| |
| /** |
| * Tests for user-defined functions; |
| * includes user-defined aggregate functions |
| * but user-defined table functions are in {@link TableFunctionTest}. |
| * |
| * @see Smalls |
| */ |
| class UdfTest { |
| private CalciteAssert.AssertThat withUdf() { |
| final String model = "{\n" |
| + " version: '1.0',\n" |
| + " schemas: [\n" |
| + " {\n" |
| + " name: 'adhoc',\n" |
| + " tables: [\n" |
| + " {\n" |
| + " name: 'EMPLOYEES',\n" |
| + " type: 'custom',\n" |
| + " factory: '" |
| + JdbcTest.EmpDeptTableFactory.class.getName() |
| + "',\n" |
| + " operand: {'foo': true, 'bar': 345}\n" |
| + " }\n" |
| + " ],\n" |
| + " functions: [\n" |
| + " {\n" |
| + " name: 'MY_PLUS',\n" |
| + " className: '" |
| + Smalls.MyPlusFunction.class.getName() |
| + "'\n" |
| + " },\n" |
| + " {\n" |
| + " name: 'MY_DET_PLUS',\n" |
| + " className: '" |
| + Smalls.MyDeterministicPlusFunction.class.getName() |
| + "'\n" |
| + " },\n" |
| + " {\n" |
| + " name: 'MY_LEFT',\n" |
| + " className: '" |
| + Smalls.MyLeftFunction.class.getName() |
| + "'\n" |
| + " },\n" |
| + " {\n" |
| + " name: 'ABCDE',\n" |
| + " className: '" |
| + Smalls.MyAbcdeFunction.class.getName() |
| + "'\n" |
| + " },\n" |
| + " {\n" |
| + " name: 'MY_STR',\n" |
| + " className: '" |
| + Smalls.MyToStringFunction.class.getName() |
| + "'\n" |
| + " },\n" |
| + " {\n" |
| + " name: 'MY_DOUBLE',\n" |
| + " className: '" |
| + Smalls.MyDoubleFunction.class.getName() |
| + "'\n" |
| + " },\n" |
| + " {\n" |
| + " name: 'MY_EXCEPTION',\n" |
| + " className: '" |
| + Smalls.MyExceptionFunction.class.getName() |
| + "'\n" |
| + " },\n" |
| + " {\n" |
| + " name: 'COUNT_ARGS',\n" |
| + " className: '" |
| + Smalls.CountArgs0Function.class.getName() |
| + "'\n" |
| + " },\n" |
| + " {\n" |
| + " name: 'COUNT_ARGS',\n" |
| + " className: '" |
| + Smalls.CountArgs1Function.class.getName() |
| + "'\n" |
| + " },\n" |
| + " {\n" |
| + " name: 'COUNT_ARGS',\n" |
| + " className: '" |
| + Smalls.CountArgs1NullableFunction.class.getName() |
| + "'\n" |
| + " },\n" |
| + " {\n" |
| + " name: 'COUNT_ARGS',\n" |
| + " className: '" |
| + Smalls.CountArgs2Function.class.getName() |
| + "'\n" |
| + " },\n" |
| + " {\n" |
| + " name: 'MY_ABS',\n" |
| + " className: '" |
| + java.lang.Math.class.getName() |
| + "',\n" |
| + " methodName: 'abs'\n" |
| + " },\n" |
| + " {\n" |
| + " name: 'NULL4',\n" |
| + " className: '" |
| + Smalls.Null4Function.class.getName() |
| + "'\n" |
| + " },\n" |
| + " {\n" |
| + " name: 'NULL8',\n" |
| + " className: '" |
| + Smalls.Null8Function.class.getName() |
| + "'\n" |
| + " },\n" |
| + " {\n" |
| + " className: '" |
| + Smalls.MultipleFunction.class.getName() |
| + "',\n" |
| + " methodName: '*'\n" |
| + " },\n" |
| + " {\n" |
| + " className: '" |
| + Smalls.AllTypesFunction.class.getName() |
| + "',\n" |
| + " methodName: '*'\n" |
| + " }\n" |
| + " ]\n" |
| + " }\n" |
| + " ]\n" |
| + "}"; |
| return CalciteAssert.model(model); |
| } |
| |
| /** Tests a user-defined function that is defined in terms of a class with |
| * non-static methods. */ |
| @Disabled("[CALCITE-1561] Intermittent test failures") |
| @Test void testUserDefinedFunction() { |
| final String sql = "select \"adhoc\".my_plus(\"deptno\", 100) as p\n" |
| + "from \"adhoc\".EMPLOYEES"; |
| final AtomicInteger c = Smalls.MyPlusFunction.INSTANCE_COUNT.get(); |
| final int before = c.get(); |
| withUdf().query(sql).returnsUnordered("P=110", |
| "P=120", |
| "P=110", |
| "P=110"); |
| final int after = c.get(); |
| assertThat(after, is(before + 4)); |
| } |
| |
| /** As {@link #testUserDefinedFunction()}, but checks that the class is |
| * instantiated exactly once, per |
| * <a href="https://issues.apache.org/jira/browse/CALCITE-1548">[CALCITE-1548] |
| * Instantiate function objects once per query</a>. */ |
| @Test void testUserDefinedFunctionInstanceCount() { |
| final String sql = "select \"adhoc\".my_det_plus(\"deptno\", 100) as p\n" |
| + "from \"adhoc\".EMPLOYEES"; |
| final AtomicInteger c = Smalls.MyDeterministicPlusFunction.INSTANCE_COUNT.get(); |
| final int before = c.get(); |
| withUdf().query(sql).returnsUnordered("P=110", |
| "P=120", |
| "P=110", |
| "P=110"); |
| final int after = c.get(); |
| assertThat(after, is(before + 1)); |
| } |
| |
| @Test void testUserDefinedFunctionB() { |
| final String sql = "select \"adhoc\".my_double(\"deptno\") as p\n" |
| + "from \"adhoc\".EMPLOYEES"; |
| final String expected = "P=20\n" |
| + "P=40\n" |
| + "P=20\n" |
| + "P=20\n"; |
| withUdf().query(sql).returns(expected); |
| } |
| |
| @Test void testUserDefinedFunctionWithNull() { |
| final String sql = "select \"adhoc\".my_det_plus(\"deptno\", 1 + null) as p\n" |
| + "from \"adhoc\".EMPLOYEES where 1 > 0 or nullif(null, 1) is null"; |
| final AtomicInteger c = Smalls.MyDeterministicPlusFunction.INSTANCE_COUNT.get(); |
| final int before = c.get(); |
| withUdf() |
| .query(sql) |
| .returnsUnordered("P=null", |
| "P=null", |
| "P=null", |
| "P=null"); |
| final int after = c.get(); |
| assertThat(after, is(before + 1)); |
| } |
| |
| /** Test case for |
| * <a href="https://issues.apache.org/jira/browse/CALCITE-3195">[CALCITE-3195] |
| * Handle a UDF that throws checked exceptions in the Enumerable code generator</a>. */ |
| @Test void testUserDefinedFunctionWithException() { |
| final String sql1 = "select \"adhoc\".my_exception(\"deptno\") as p\n" |
| + "from \"adhoc\".EMPLOYEES"; |
| final String expected1 = "P=20\n" |
| + "P=30\n" |
| + "P=20\n" |
| + "P=20\n"; |
| withUdf().query(sql1).returns(expected1); |
| |
| final String sql2 = "select cast(\"adhoc\".my_exception(\"deptno\") as double) as p\n" |
| + "from \"adhoc\".EMPLOYEES"; |
| final String expected2 = "P=20.0\n" |
| + "P=30.0\n" |
| + "P=20.0\n" |
| + "P=20.0\n"; |
| withUdf().query(sql2).returns(expected2); |
| |
| final String sql3 = "select \"adhoc\".my_exception(\"deptno\" * 2 + 11) as p\n" |
| + "from \"adhoc\".EMPLOYEES"; |
| final String expected3 = "P=41\n" |
| + "P=61\n" |
| + "P=41\n" |
| + "P=41\n"; |
| withUdf().query(sql3).returns(expected3); |
| } |
| |
| /** Test case for |
| * <a href="https://issues.apache.org/jira/browse/CALCITE-937">[CALCITE-937] |
| * User-defined function within view</a>. */ |
| @Test void testUserDefinedFunctionInView() throws Exception { |
| Class.forName("org.apache.calcite.jdbc.Driver"); |
| Connection connection = DriverManager.getConnection("jdbc:calcite:"); |
| CalciteConnection calciteConnection = |
| connection.unwrap(CalciteConnection.class); |
| SchemaPlus rootSchema = calciteConnection.getRootSchema(); |
| rootSchema.add("hr", new ReflectiveSchema(new HrSchema())); |
| |
| SchemaPlus post = rootSchema.add("POST", new AbstractSchema()); |
| post.add("MY_INCREMENT", |
| ScalarFunctionImpl.create(Smalls.MyIncrement.class, "eval")); |
| |
| final String viewSql = "select \"empid\" as EMPLOYEE_ID,\n" |
| + " \"name\" || ' ' || \"name\" as EMPLOYEE_NAME,\n" |
| + " \"salary\" as EMPLOYEE_SALARY,\n" |
| + " POST.MY_INCREMENT(\"empid\", 10) as INCREMENTED_SALARY\n" |
| + "from \"hr\".\"emps\""; |
| post.add("V_EMP", |
| ViewTable.viewMacro(post, viewSql, ImmutableList.of(), |
| ImmutableList.of("POST", "V_EMP"), null)); |
| |
| final String result = "" |
| + "EMPLOYEE_ID=100; EMPLOYEE_NAME=Bill Bill;" |
| + " EMPLOYEE_SALARY=10000.0; INCREMENTED_SALARY=110.0\n" |
| + "EMPLOYEE_ID=200; EMPLOYEE_NAME=Eric Eric;" |
| + " EMPLOYEE_SALARY=8000.0; INCREMENTED_SALARY=220.0\n" |
| + "EMPLOYEE_ID=150; EMPLOYEE_NAME=Sebastian Sebastian;" |
| + " EMPLOYEE_SALARY=7000.0; INCREMENTED_SALARY=165.0\n" |
| + "EMPLOYEE_ID=110; EMPLOYEE_NAME=Theodore Theodore;" |
| + " EMPLOYEE_SALARY=11500.0; INCREMENTED_SALARY=121.0\n"; |
| |
| Statement statement = connection.createStatement(); |
| ResultSet resultSet = statement.executeQuery(viewSql); |
| assertThat(CalciteAssert.toString(resultSet), is(result)); |
| resultSet.close(); |
| |
| ResultSet viewResultSet = |
| statement.executeQuery("select * from \"POST\".\"V_EMP\""); |
| assertThat(CalciteAssert.toString(viewResultSet), is(result)); |
| statement.close(); |
| connection.close(); |
| } |
| |
| /** |
| * Tests that IS NULL/IS NOT NULL is properly implemented for non-strict |
| * functions. |
| */ |
| @Test void testNotNullImplementor() { |
| final CalciteAssert.AssertThat with = withUdf(); |
| with.query( |
| "select upper(\"adhoc\".my_str(\"name\")) as p from \"adhoc\".EMPLOYEES") |
| .returns("P=<BILL>\n" |
| + "P=<ERIC>\n" |
| + "P=<SEBASTIAN>\n" |
| + "P=<THEODORE>\n"); |
| with.query("select \"name\" as p from \"adhoc\".EMPLOYEES\n" |
| + "where \"adhoc\".my_str(\"name\") is not null") |
| .returns("P=Bill\n" |
| + "P=Eric\n" |
| + "P=Sebastian\n" |
| + "P=Theodore\n"); |
| with.query("select \"name\" as p from \"adhoc\".EMPLOYEES\n" |
| + "where \"adhoc\".my_str(upper(\"name\")) is not null") |
| .returns("P=Bill\n" |
| + "P=Eric\n" |
| + "P=Sebastian\n" |
| + "P=Theodore\n"); |
| with.query("select \"name\" as p from \"adhoc\".EMPLOYEES\n" |
| + "where upper(\"adhoc\".my_str(\"name\")) is not null") |
| .returns("P=Bill\n" |
| + "P=Eric\n" |
| + "P=Sebastian\n" |
| + "P=Theodore\n"); |
| with.query("select \"name\" as p from \"adhoc\".EMPLOYEES\n" |
| + "where \"adhoc\".my_str(\"name\") is null") |
| .returns(""); |
| with.query("select \"name\" as p from \"adhoc\".EMPLOYEES\n" |
| + "where \"adhoc\".my_str(upper(\"adhoc\".my_str(\"name\")" |
| + ")) ='8'") |
| .returns(""); |
| } |
| |
| /** Tests that we generate the appropriate checks for a "semi-strict" |
| * function. |
| * |
| * <p>The difference between "strict" and "semi-strict" functions is that a |
| * "semi-strict" function might return null even if none of its arguments |
| * are null. (Both always return null if one of their arguments is null.) |
| * Thus, a nasty function is more unpredictable. |
| * |
| * @see SemiStrict */ |
| @Test void testSemiStrict() { |
| final CalciteAssert.AssertThat with = withUdf(); |
| final String sql = "select\n" |
| + " \"adhoc\".null4(upper(\"name\")) as p\n" |
| + " from \"adhoc\".EMPLOYEES"; |
| with.query(sql) |
| .returnsUnordered("P=null", |
| "P=null", |
| "P=SEBASTIAN", |
| "P=THEODORE"); |
| // my_str is non-strict; it must be called when args are null |
| final String sql2 = "select\n" |
| + " \"adhoc\".my_str(upper(\"adhoc\".null4(\"name\"))) as p\n" |
| + " from \"adhoc\".EMPLOYEES"; |
| with.query(sql2) |
| .returnsUnordered("P=<null>", |
| "P=<null>", |
| "P=<SEBASTIAN>", |
| "P=<THEODORE>"); |
| // null8 throws NPE if its argument is null, |
| // so we had better know that null4 might return null |
| final String sql3 = "select\n" |
| + " \"adhoc\".null8(\"adhoc\".null4(\"name\")) as p\n" |
| + " from \"adhoc\".EMPLOYEES"; |
| with.query(sql3) |
| .returnsUnordered("P=null", |
| "P=null", |
| "P=Sebastian", |
| "P=null"); |
| } |
| |
| /** Tests derived return type of user-defined function. */ |
| @Test void testUdfDerivedReturnType() { |
| final CalciteAssert.AssertThat with = withUdf(); |
| with.query( |
| "select max(\"adhoc\".my_double(\"deptno\")) as p from \"adhoc\".EMPLOYEES") |
| .returns("P=40\n"); |
| with.query("select max(\"adhoc\".my_str(\"name\")) as p\n" |
| + "from \"adhoc\".EMPLOYEES\n" |
| + "where \"adhoc\".my_str(\"name\") is null") |
| .returns("P=null\n"); |
| } |
| |
| /** Tests a user-defined function that has multiple overloads. */ |
| @Test void testUdfOverloaded() { |
| final CalciteAssert.AssertThat with = withUdf(); |
| with.query("values (\"adhoc\".count_args(),\n" |
| + " \"adhoc\".count_args(0),\n" |
| + " \"adhoc\".count_args(0, 0))") |
| .returns("EXPR$0=0; EXPR$1=1; EXPR$2=2\n"); |
| with.query("select max(\"adhoc\".count_args()) as p0,\n" |
| + " min(\"adhoc\".count_args(0)) as p1,\n" |
| + " max(\"adhoc\".count_args(0, 0)) as p2\n" |
| + "from \"adhoc\".EMPLOYEES limit 1") |
| .returns("P0=0; P1=1; P2=2\n"); |
| } |
| |
| @Test void testUdfOverloadedNullable() { |
| final CalciteAssert.AssertThat with = withUdf(); |
| with.query("values (\"adhoc\".count_args(),\n" |
| + " \"adhoc\".count_args(cast(null as smallint)),\n" |
| + " \"adhoc\".count_args(0, 0))") |
| .returns("EXPR$0=0; EXPR$1=-1; EXPR$2=2\n"); |
| } |
| |
| /** Tests passing parameters to user-defined function by name. */ |
| @Test void testUdfArgumentName() { |
| final CalciteAssert.AssertThat with = withUdf(); |
| // arguments in physical order |
| with.query("values (\"adhoc\".my_left(\"s\" => 'hello', \"n\" => 3))") |
| .returns("EXPR$0=hel\n"); |
| // arguments in reverse order |
| with.query("values (\"adhoc\".my_left(\"n\" => 3, \"s\" => 'hello'))") |
| .returns("EXPR$0=hel\n"); |
| with.query("values (\"adhoc\".my_left(\"n\" => 1 + 2, \"s\" => 'hello'))") |
| .returns("EXPR$0=hel\n"); |
| // duplicate argument names |
| with.query("values (\"adhoc\".my_left(\"n\" => 3, \"n\" => 2, \"s\" => 'hello'))") |
| .throws_("Duplicate argument name 'n'"); |
| // invalid argument names |
| with.query("values (\"adhoc\".my_left(\"n\" => 3, \"m\" => 2, \"s\" => 'h'))") |
| .throws_("No match found for function signature " |
| + "MY_LEFT(n => <NUMERIC>, m => <NUMERIC>, s => <CHARACTER>)"); |
| // missing arguments |
| with.query("values (\"adhoc\".my_left(\"n\" => 3))") |
| .throws_("No match found for function signature MY_LEFT(n => <NUMERIC>)"); |
| with.query("values (\"adhoc\".my_left(\"s\" => 'hello'))") |
| .throws_("No match found for function signature MY_LEFT(s => <CHARACTER>)"); |
| // arguments of wrong type, will do implicitly type coercion. |
| with.query("values (\"adhoc\".my_left(\"n\" => 'hello', \"s\" => 'x'))") |
| .throws_("java.lang.NumberFormatException: For input string: \"hello\""); |
| with.query("values (\"adhoc\".my_left(\"n\" => '1', \"s\" => 'x'))") |
| .returns("EXPR$0=x\n"); |
| with.query("values (\"adhoc\".my_left(\"n\" => 1, \"s\" => 0))") |
| .returns("EXPR$0=0\n"); |
| } |
| |
| /** Tests calling a user-defined function some of whose parameters are |
| * optional. */ |
| @Test void testUdfArgumentOptional() { |
| final CalciteAssert.AssertThat with = withUdf(); |
| with.query("values (\"adhoc\".abcde(a=>1,b=>2,c=>3,d=>4,e=>5))") |
| .returns("EXPR$0={a: 1, b: 2, c: 3, d: 4, e: 5}\n"); |
| with.query("values (\"adhoc\".abcde(1,2,3,4,CAST(NULL AS INTEGER)))") |
| .returns("EXPR$0={a: 1, b: 2, c: 3, d: 4, e: null}\n"); |
| with.query("values (\"adhoc\".abcde(a=>1,b=>2,c=>3,d=>4))") |
| .returns("EXPR$0={a: 1, b: 2, c: 3, d: 4, e: null}\n"); |
| with.query("values (\"adhoc\".abcde(a=>1,b=>2,c=>3))") |
| .returns("EXPR$0={a: 1, b: 2, c: 3, d: null, e: null}\n"); |
| with.query("values (\"adhoc\".abcde(a=>1,e=>5,c=>3))") |
| .returns("EXPR$0={a: 1, b: null, c: 3, d: null, e: 5}\n"); |
| with.query("values (\"adhoc\".abcde(1,2,3))") |
| .returns("EXPR$0={a: 1, b: 2, c: 3, d: null, e: null}\n"); |
| with.query("values (\"adhoc\".abcde(1,2,3,4))") |
| .returns("EXPR$0={a: 1, b: 2, c: 3, d: 4, e: null}\n"); |
| with.query("values (\"adhoc\".abcde(1,2,3,4,5))") |
| .returns("EXPR$0={a: 1, b: 2, c: 3, d: 4, e: 5}\n"); |
| with.query("values (\"adhoc\".abcde(1,2))") |
| .throws_("No match found for function signature ABCDE(<NUMERIC>, <NUMERIC>)"); |
| with.query("values (\"adhoc\".abcde(1,DEFAULT,3))") |
| .returns("EXPR$0={a: 1, b: null, c: 3, d: null, e: null}\n"); |
| // implicit type coercion. |
| with.query("values (\"adhoc\".abcde(1,DEFAULT,'abcde'))") |
| .throws_("java.lang.NumberFormatException: For input string: \"abcde\""); |
| with.query("values (\"adhoc\".abcde(1,DEFAULT,'123'))") |
| .returns("EXPR$0={a: 1, b: null, c: 123, d: null, e: null}\n"); |
| with.query("values (\"adhoc\".abcde(true))") |
| .throws_("No match found for function signature ABCDE(<BOOLEAN>)"); |
| with.query("values (\"adhoc\".abcde(true,DEFAULT))") |
| .throws_("No match found for function signature ABCDE(<BOOLEAN>, <ANY>)"); |
| with.query("values (\"adhoc\".abcde(1,DEFAULT,3,DEFAULT))") |
| .returns("EXPR$0={a: 1, b: null, c: 3, d: null, e: null}\n"); |
| with.query("values (\"adhoc\".abcde(1,2,DEFAULT))") |
| .throws_("DEFAULT is only allowed for optional parameters"); |
| with.query("values (\"adhoc\".abcde(a=>1,b=>2,c=>DEFAULT))") |
| .throws_("DEFAULT is only allowed for optional parameters"); |
| with.query("values (\"adhoc\".abcde(a=>1,b=>DEFAULT,c=>3))") |
| .returns("EXPR$0={a: 1, b: null, c: 3, d: null, e: null}\n"); |
| } |
| |
| /** Test for |
| * {@link org.apache.calcite.runtime.CalciteResource#requireDefaultConstructor(String)}. */ |
| @Test void testUserDefinedFunction2() { |
| String message = "Declaring class " |
| + "'org.apache.calcite.util.Smalls$AwkwardFunction' of non-static " |
| + "user-defined function must have a public constructor with zero " |
| + "parameters"; |
| withBadUdf(Smalls.AwkwardFunction.class).connectThrows(message); |
| } |
| |
| /** Tests user-defined function, with multiple methods per class. */ |
| @Test void testUserDefinedFunctionWithMethodName() { |
| // java.lang.Math has abs(int) and abs(double). |
| final CalciteAssert.AssertThat with = withUdf(); |
| with.query("values abs(-4)").returnsValue("4"); |
| with.query("values abs(-4.5)").returnsValue("4.5"); |
| |
| // 3 overloads of "fun1", another method "fun2", but method "nonStatic" |
| // cannot be used as a function |
| with.query("values \"adhoc\".\"fun1\"(2)").returnsValue("4"); |
| with.query("values \"adhoc\".\"fun1\"(2, 3)").returnsValue("5"); |
| with.query("values \"adhoc\".\"fun1\"('Foo Bar')").returnsValue("foo bar"); |
| with.query("values \"adhoc\".\"fun2\"(10)").returnsValue("30"); |
| with.query("values \"adhoc\".\"nonStatic\"(2)") |
| .throws_("No match found for function signature nonStatic(<NUMERIC>)"); |
| } |
| |
| /** Tests user-defined aggregate function. */ |
| @Test void testUserDefinedAggregateFunction() { |
| final String empDept = JdbcTest.EmpDeptTableFactory.class.getName(); |
| final String sum = Smalls.MyStaticSumFunction.class.getName(); |
| final String sum2 = Smalls.MySumFunction.class.getName(); |
| final CalciteAssert.AssertThat with = CalciteAssert.model("{\n" |
| + " version: '1.0',\n" |
| + " schemas: [\n" |
| + " {\n" |
| + " name: 'adhoc',\n" |
| + " tables: [\n" |
| + " {\n" |
| + " name: 'EMPLOYEES',\n" |
| + " type: 'custom',\n" |
| + " factory: '" + empDept + "',\n" |
| + " operand: {'foo': true, 'bar': 345}\n" |
| + " }\n" |
| + " ],\n" |
| + " functions: [\n" |
| + " {\n" |
| + " name: 'MY_SUM',\n" |
| + " className: '" + sum + "'\n" |
| + " },\n" |
| + " {\n" |
| + " name: 'MY_SUM2',\n" |
| + " className: '" + sum2 + "'\n" |
| + " }\n" |
| + " ]\n" |
| + " }\n" |
| + " ]\n" |
| + "}") |
| .withDefaultSchema("adhoc"); |
| with.withDefaultSchema(null) |
| .query( |
| "select \"adhoc\".my_sum(\"deptno\") as p from \"adhoc\".EMPLOYEES\n") |
| .returns("P=50\n"); |
| with.query("select my_sum(\"empid\"), \"deptno\" as p from EMPLOYEES\n") |
| .throws_( |
| "Expression 'deptno' is not being grouped"); |
| with.query("select my_sum(\"deptno\") as p from EMPLOYEES\n") |
| .returns("P=50\n"); |
| // implicit type coercion. |
| with.query("select my_sum(\"name\") as p from EMPLOYEES\n") |
| .throws_("java.lang.NumberFormatException: For input string: \"Bill\""); |
| with.query("select my_sum(\"deptno\", 1) as p from EMPLOYEES\n") |
| .throws_( |
| "No match found for function signature MY_SUM(<NUMERIC>, <NUMERIC>)"); |
| with.query("select my_sum() as p from EMPLOYEES\n") |
| .throws_( |
| "No match found for function signature MY_SUM()"); |
| with.query("select \"deptno\", my_sum(\"deptno\") as p from EMPLOYEES\n" |
| + "group by \"deptno\"") |
| .returnsUnordered( |
| "deptno=20; P=20", |
| "deptno=10; P=30"); |
| with.query("select \"deptno\", my_sum2(\"deptno\") as p from EMPLOYEES\n" |
| + "group by \"deptno\"") |
| .returnsUnordered("deptno=20; P=20", "deptno=10; P=30"); |
| } |
| |
| /** Tests user-defined aggregate function. */ |
| @Test void testUserDefinedAggregateFunctionWithMultipleParameters() { |
| final String empDept = JdbcTest.EmpDeptTableFactory.class.getName(); |
| final String sum21 = Smalls.MyTwoParamsSumFunctionFilter1.class.getName(); |
| final String sum22 = Smalls.MyTwoParamsSumFunctionFilter2.class.getName(); |
| final String sum31 = Smalls.MyThreeParamsSumFunctionWithFilter1.class.getName(); |
| final String sum32 = Smalls.MyThreeParamsSumFunctionWithFilter2.class.getName(); |
| final CalciteAssert.AssertThat with = CalciteAssert.model("{\n" |
| + " version: '1.0',\n" |
| + " schemas: [\n" |
| + " {\n" |
| + " name: 'adhoc',\n" |
| + " tables: [\n" |
| + " {\n" |
| + " name: 'EMPLOYEES',\n" |
| + " type: 'custom',\n" |
| + " factory: '" + empDept + "',\n" |
| + " operand: {'foo': true, 'bar': 345}\n" |
| + " }\n" |
| + " ],\n" |
| + " functions: [\n" |
| + " {\n" |
| + " name: 'MY_SUM2',\n" |
| + " className: '" + sum21 + "'\n" |
| + " },\n" |
| + " {\n" |
| + " name: 'MY_SUM2',\n" |
| + " className: '" + sum22 + "'\n" |
| + " },\n" |
| + " {\n" |
| + " name: 'MY_SUM3',\n" |
| + " className: '" + sum31 + "'\n" |
| + " },\n" |
| + " {\n" |
| + " name: 'MY_SUM3',\n" |
| + " className: '" + sum32 + "'\n" |
| + " }\n" |
| + " ]\n" |
| + " }\n" |
| + " ]\n" |
| + "}") |
| .withDefaultSchema("adhoc"); |
| with.withDefaultSchema(null) |
| .query("select \"adhoc\".my_sum3(\"deptno\",\"name\",'Eric') as p\n" |
| + "from \"adhoc\".EMPLOYEES\n") |
| .returns("P=20\n"); |
| with.query("select \"adhoc\".my_sum3(\"empid\",\"deptno\",\"commission\") as p " |
| + "from \"adhoc\".EMPLOYEES\n") |
| .returns("P=330\n"); |
| with.query("select \"adhoc\".my_sum3(\"empid\",\"deptno\",\"commission\"),\n" |
| + " \"name\"\n" |
| + "from \"adhoc\".EMPLOYEES\n") |
| .throws_("Expression 'name' is not being grouped"); |
| with.query("select \"name\",\n" |
| + " \"adhoc\".my_sum3(\"empid\",\"deptno\",\"commission\") as p\n" |
| + "from \"adhoc\".EMPLOYEES\n" |
| + "group by \"name\"") |
| .returnsUnordered("name=Theodore; P=0", |
| "name=Eric; P=220", |
| "name=Bill; P=110", |
| "name=Sebastian; P=0"); |
| // implicit type coercion. |
| with.query("select \"adhoc\".my_sum3(\"empid\",\"deptno\",\"salary\") as p\n" |
| + "from \"adhoc\".EMPLOYEES\n"); |
| with.query("select \"adhoc\".my_sum3(\"empid\",\"deptno\",\"name\") as p\n" |
| + "from \"adhoc\".EMPLOYEES\n"); |
| with.query("select \"adhoc\".my_sum2(\"commission\",250) as p\n" |
| + "from \"adhoc\".EMPLOYEES\n") |
| .returns("P=1500\n"); |
| // implicit type coercion. |
| with.query("select \"adhoc\".my_sum2(\"name\",250) as p\n" |
| + "from \"adhoc\".EMPLOYEES\n") |
| .throws_("java.lang.NumberFormatException: For input string: \"Bill\""); |
| // implicit type coercion. |
| with.query("select \"adhoc\".my_sum2(\"empid\",0.0) as p\n" |
| + "from \"adhoc\".EMPLOYEES\n") |
| .returns("P=560\n"); |
| } |
| |
| /** Test for |
| * {@link org.apache.calcite.runtime.CalciteResource#firstParameterOfAdd(String)}. */ |
| @Test void testUserDefinedAggregateFunction3() { |
| String message = "Caused by: java.lang.RuntimeException: In user-defined " |
| + "aggregate class 'org.apache.calcite.util.Smalls$SumFunctionBadIAdd'" |
| + ", first parameter to 'add' method must be the accumulator (the " |
| + "return type of the 'init' method)"; |
| withBadUdf(Smalls.SumFunctionBadIAdd.class).connectThrows(message); |
| } |
| |
| /** Test case for |
| * <a href="https://issues.apache.org/jira/browse/CALCITE-1434">[CALCITE-1434] |
| * AggregateFunctionImpl doesnt work if the class implements a generic |
| * interface</a>. */ |
| @Test void testUserDefinedAggregateFunctionImplementsInterface() { |
| final String empDept = JdbcTest.EmpDeptTableFactory.class.getName(); |
| final String mySum3 = Smalls.MySum3.class.getName(); |
| final String model = "{\n" |
| + " version: '1.0',\n" |
| + " schemas: [\n" |
| + " {\n" |
| + " name: 'adhoc',\n" |
| + " tables: [\n" |
| + " {\n" |
| + " name: 'EMPLOYEES',\n" |
| + " type: 'custom',\n" |
| + " factory: '" + empDept + "',\n" |
| + " operand: {'foo': true, 'bar': 345}\n" |
| + " }\n" |
| + " ],\n" |
| + " functions: [\n" |
| + " {\n" |
| + " name: 'MY_SUM3',\n" |
| + " className: '" + mySum3 + "'\n" |
| + " }\n" |
| + " ]\n" |
| + " }\n" |
| + " ]\n" |
| + "}"; |
| final CalciteAssert.AssertThat with = CalciteAssert.model(model) |
| .withDefaultSchema("adhoc"); |
| |
| with.query("select my_sum3(\"deptno\") as p from EMPLOYEES\n") |
| .returns("P=50\n"); |
| with.withDefaultSchema(null) |
| .query("select \"adhoc\".my_sum3(\"deptno\") as p\n" |
| + "from \"adhoc\".EMPLOYEES\n") |
| .returns("P=50\n"); |
| with.query("select my_sum3(\"empid\"), \"deptno\" as p from EMPLOYEES\n") |
| .throws_("Expression 'deptno' is not being grouped"); |
| with.query("select my_sum3(\"deptno\") as p from EMPLOYEES\n") |
| .returns("P=50\n"); |
| // implicit type coercion. |
| with.query("select my_sum3(\"name\") as p from EMPLOYEES\n") |
| .throws_("java.lang.NumberFormatException: For input string: \"Bill\""); |
| with.query("select my_sum3(\"deptno\", 1) as p from EMPLOYEES\n") |
| .throws_("No match found for function signature " |
| + "MY_SUM3(<NUMERIC>, <NUMERIC>)"); |
| with.query("select my_sum3() as p from EMPLOYEES\n") |
| .throws_("No match found for function signature MY_SUM3()"); |
| with.query("select \"deptno\", my_sum3(\"deptno\") as p from EMPLOYEES\n" |
| + "group by \"deptno\"") |
| .returnsUnordered("deptno=20; P=20", |
| "deptno=10; P=30"); |
| } |
| |
| private static CalciteAssert.AssertThat withBadUdf(Class<?> clazz) { |
| final String empDept = JdbcTest.EmpDeptTableFactory.class.getName(); |
| final String className = clazz.getName(); |
| return CalciteAssert.model("{\n" |
| + " version: '1.0',\n" |
| + " schemas: [\n" |
| + " {\n" |
| + " name: 'adhoc',\n" |
| + " tables: [\n" |
| + " {\n" |
| + " name: 'EMPLOYEES',\n" |
| + " type: 'custom',\n" |
| + " factory: '" + empDept + "',\n" |
| + " operand: {'foo': true, 'bar': 345}\n" |
| + " }\n" |
| + " ],\n" |
| + " functions: [\n" |
| + " {\n" |
| + " name: 'AWKWARD',\n" |
| + " className: '" + className + "'\n" |
| + " }\n" |
| + " ]\n" |
| + " }\n" |
| + " ]\n" |
| + "}") |
| .withDefaultSchema("adhoc"); |
| } |
| |
| /** Tests user-defined aggregate function with FILTER. |
| * |
| * <p>Also tests that we do not try to push ADAF to JDBC source. */ |
| @Test void testUserDefinedAggregateFunctionWithFilter() { |
| final String sum = Smalls.MyStaticSumFunction.class.getName(); |
| final String sum2 = Smalls.MySumFunction.class.getName(); |
| final CalciteAssert.AssertThat with = CalciteAssert.model("{\n" |
| + " version: '1.0',\n" |
| + " schemas: [\n" |
| + JdbcTest.SCOTT_SCHEMA |
| + ",\n" |
| + " {\n" |
| + " name: 'adhoc',\n" |
| + " functions: [\n" |
| + " {\n" |
| + " name: 'MY_SUM',\n" |
| + " className: '" + sum + "'\n" |
| + " },\n" |
| + " {\n" |
| + " name: 'MY_SUM2',\n" |
| + " className: '" + sum2 + "'\n" |
| + " }\n" |
| + " ]\n" |
| + " }\n" |
| + " ]\n" |
| + "}") |
| .withDefaultSchema("adhoc"); |
| with.query("select deptno, \"adhoc\".my_sum(deptno) as p\n" |
| + "from scott.emp\n" |
| + "group by deptno\n") |
| .returns( |
| "DEPTNO=20; P=100\n" |
| + "DEPTNO=10; P=30\n" |
| + "DEPTNO=30; P=180\n"); |
| |
| with.query("select deptno,\n" |
| + " \"adhoc\".my_sum(deptno) filter (where job = 'CLERK') as c,\n" |
| + " \"adhoc\".my_sum(deptno) filter (where job = 'XXX') as x\n" |
| + "from scott.emp\n" |
| + "group by deptno\n") |
| .returns( |
| "DEPTNO=20; C=40; X=0\n" |
| + "DEPTNO=10; C=10; X=0\n" |
| + "DEPTNO=30; C=30; X=0\n"); |
| } |
| |
| /** Tests resolution of functions using schema paths. */ |
| @Test void testPath() { |
| final String name = Smalls.MyPlusFunction.class.getName(); |
| final CalciteAssert.AssertThat with = CalciteAssert.model("{\n" |
| + " version: '1.0',\n" |
| + " schemas: [\n" |
| + " {\n" |
| + " name: 'adhoc',\n" |
| + " functions: [\n" |
| + " {\n" |
| + " name: 'MY_PLUS',\n" |
| + " className: '" + name + "'\n" |
| + " }\n" |
| + " ]\n" |
| + " },\n" |
| + " {\n" |
| + " name: 'adhoc2',\n" |
| + " functions: [\n" |
| + " {\n" |
| + " name: 'MY_PLUS2',\n" |
| + " className: '" + name + "'\n" |
| + " }\n" |
| + " ]\n" |
| + " },\n" |
| + " {\n" |
| + " name: 'adhoc3',\n" |
| + " path: ['adhoc2','adhoc3'],\n" |
| + " functions: [\n" |
| + " {\n" |
| + " name: 'MY_PLUS3',\n" |
| + " className: '" + name + "'\n" |
| + " }\n" |
| + " ]\n" |
| + " }\n" |
| + " ]\n" |
| + "}"); |
| |
| final String err = "No match found for function signature"; |
| final String res = "EXPR$0=2\n"; |
| |
| // adhoc can see own function MY_PLUS but not adhoc2.MY_PLUS2 unless |
| // qualified |
| final CalciteAssert.AssertThat adhoc = with.withDefaultSchema("adhoc"); |
| adhoc.query("values MY_PLUS(1, 1)").returns(res); |
| adhoc.query("values MY_PLUS2(1, 1)").throws_(err); |
| adhoc.query("values \"adhoc2\".MY_PLUS(1, 1)").throws_(err); |
| adhoc.query("values \"adhoc2\".MY_PLUS2(1, 1)").returns(res); |
| |
| // adhoc2 can see own function MY_PLUS2 but not adhoc2.MY_PLUS unless |
| // qualified |
| final CalciteAssert.AssertThat adhoc2 = with.withDefaultSchema("adhoc2"); |
| adhoc2.query("values MY_PLUS2(1, 1)").returns(res); |
| adhoc2.query("values MY_PLUS(1, 1)").throws_(err); |
| adhoc2.query("values \"adhoc\".MY_PLUS(1, 1)").returns(res); |
| |
| // adhoc3 can see own adhoc2.MY_PLUS2 because in path, with or without |
| // qualification, but can only see adhoc.MY_PLUS with qualification |
| final CalciteAssert.AssertThat adhoc3 = with.withDefaultSchema("adhoc3"); |
| adhoc3.query("values MY_PLUS2(1, 1)").returns(res); |
| adhoc3.query("values MY_PLUS(1, 1)").throws_(err); |
| adhoc3.query("values \"adhoc\".MY_PLUS(1, 1)").returns(res); |
| } |
| |
| /** Test case for |
| * <a href="https://issues.apache.org/jira/browse/CALCITE-986">[CALCITE-986] |
| * User-defined function with Date or Timestamp parameters</a>. */ |
| @Test void testDate() { |
| final CalciteAssert.AssertThat with = withUdf(); |
| with.query("values \"adhoc\".\"dateFun\"(DATE '1970-01-01')") |
| .returnsValue("0"); |
| with.query("values \"adhoc\".\"dateFun\"(DATE '1970-01-02')") |
| .returnsValue("86400000"); |
| with.query("values \"adhoc\".\"dateFun\"(cast(null as date))") |
| .returnsValue("-1"); |
| with.query("values \"adhoc\".\"timeFun\"(TIME '00:00:00')") |
| .returnsValue("0"); |
| with.query("values \"adhoc\".\"timeFun\"(TIME '00:01:30')") |
| .returnsValue("90000"); |
| with.query("values \"adhoc\".\"timeFun\"(cast(null as time))") |
| .returnsValue("-1"); |
| with.query("values \"adhoc\".\"timestampFun\"(TIMESTAMP '1970-01-01 00:00:00')") |
| .returnsValue("0"); |
| with.query("values \"adhoc\".\"timestampFun\"(TIMESTAMP '1970-01-02 00:01:30')") |
| .returnsValue("86490000"); |
| with.query("values \"adhoc\".\"timestampFun\"(cast(null as timestamp))") |
| .returnsValue("-1"); |
| } |
| |
| /** Test case for |
| * <a href="https://issues.apache.org/jira/browse/CALCITE-1041">[CALCITE-1041] |
| * User-defined function returns DATE or TIMESTAMP value</a>. */ |
| @Test void testReturnDate() { |
| final CalciteAssert.AssertThat with = withUdf(); |
| with.query("values \"adhoc\".\"toDateFun\"(0)") |
| .returnsValue("1970-01-01"); |
| with.query("values \"adhoc\".\"toDateFun\"(1)") |
| .returnsValue("1970-01-02"); |
| with.query("values \"adhoc\".\"toDateFun\"(cast(null as bigint))") |
| .returnsValue(null); |
| with.query("values \"adhoc\".\"toTimeFun\"(0)") |
| .returnsValue("00:00:00"); |
| with.query("values \"adhoc\".\"toTimeFun\"(90000)") |
| .returnsValue("00:01:30"); |
| with.query("values \"adhoc\".\"toTimeFun\"(cast(null as bigint))") |
| .returnsValue(null); |
| with.query("values \"adhoc\".\"toTimestampFun\"(0)") |
| .returnsValue("1970-01-01 00:00:00"); |
| with.query("values \"adhoc\".\"toTimestampFun\"(86490000)") |
| .returnsValue("1970-01-02 00:01:30"); |
| with.query("values \"adhoc\".\"toTimestampFun\"(cast(null as bigint))") |
| .returnsValue(null); |
| } |
| |
| /** Test case for |
| * <a href="https://issues.apache.org/jira/browse/CALCITE-1881">[CALCITE-1881] |
| * Can't distinguish overloaded user-defined functions that have DATE and |
| * TIMESTAMP arguments</a>. */ |
| @Test void testDateAndTimestamp() { |
| final CalciteAssert.AssertThat with = withUdf(); |
| with.query("values \"adhoc\".\"toLong\"(DATE '1970-01-15')") |
| .returns("EXPR$0=1209600000\n"); |
| with.query("values \"adhoc\".\"toLong\"(DATE '2002-08-11')") |
| .returns("EXPR$0=1029024000000\n"); |
| with.query("values \"adhoc\".\"toLong\"(DATE '2003-04-11')") |
| .returns("EXPR$0=1050019200000\n"); |
| with.query("values \"adhoc\".\"toLong\"(TIMESTAMP '2003-04-11 00:00:00')") |
| .returns("EXPR$0=1050019200000\n"); |
| with.query("values \"adhoc\".\"toLong\"(TIMESTAMP '2003-04-11 00:00:06')") |
| .returns("EXPR$0=1050019206000\n"); |
| with.query("values \"adhoc\".\"toLong\"(TIMESTAMP '2003-04-18 01:20:00')") |
| .returns("EXPR$0=1050628800000\n"); |
| with.query("values \"adhoc\".\"toLong\"(TIME '00:20:00')") |
| .returns("EXPR$0=1200000\n"); |
| with.query("values \"adhoc\".\"toLong\"(TIME '00:20:10')") |
| .returns("EXPR$0=1210000\n"); |
| with.query("values \"adhoc\".\"toLong\"(TIME '01:20:00')") |
| .returns("EXPR$0=4800000\n"); |
| } |
| |
| /** Test case for |
| * <a href="https://issues.apache.org/jira/browse/CALCITE-2053">[CALCITE-2053] |
| * Overloaded user-defined functions that have Double and BigDecimal arguments |
| * will goes wrong </a>. */ |
| @Test void testBigDecimalAndLong() { |
| final CalciteAssert.AssertThat with = withUdf(); |
| with.query("values \"adhoc\".\"toDouble\"(cast(1.0 as double))") |
| .returns("EXPR$0=1.0\n"); |
| with.query("values \"adhoc\".\"toDouble\"(cast(1.0 as decimal))") |
| .returns("EXPR$0=1.0\n"); |
| with.query("values \"adhoc\".\"toDouble\"(cast(1 as double))") |
| .returns("EXPR$0=1.0\n"); |
| with.query("values \"adhoc\".\"toDouble\"(cast(1 as decimal))") |
| .returns("EXPR$0=1.0\n"); |
| with.query("values \"adhoc\".\"toDouble\"(cast(1 as float))") |
| .returns("EXPR$0=1.0\n"); |
| with.query("values \"adhoc\".\"toDouble\"(cast(1.0 as float))") |
| .returns("EXPR$0=1.0\n"); |
| } |
| |
| /** Test case for |
| * <a href="https://issues.apache.org/jira/browse/CALCITE-1041">[CALCITE-1041] |
| * User-defined function returns DATE or TIMESTAMP value</a>. */ |
| @Test void testReturnDate2() { |
| final CalciteAssert.AssertThat with = withUdf(); |
| with.query("select * from (values 0) as t(c)\n" |
| + "where \"adhoc\".\"toTimestampFun\"(c) in (\n" |
| + " cast('1970-01-01 00:00:00' as timestamp),\n" |
| + " cast('1997-02-01 00:00:00' as timestamp))") |
| .returnsValue("0"); |
| with.query("select * from (values 0) as t(c)\n" |
| + "where \"adhoc\".\"toTimestampFun\"(c) in (\n" |
| + " timestamp '1970-01-01 00:00:00',\n" |
| + " timestamp '1997-02-01 00:00:00')") |
| .returnsValue("0"); |
| with.query("select * from (values 0) as t(c)\n" |
| + "where \"adhoc\".\"toTimestampFun\"(c) in (\n" |
| + " '1970-01-01 00:00:00',\n" |
| + " '1997-02-01 00:00:00')") |
| .returnsValue("0"); |
| } |
| |
| /** |
| * Test case for |
| * <a href="https://issues.apache.org/jira/browse/CALCITE-1834">[CALCITE-1834] |
| * User-defined function for Arrays</a>. |
| */ |
| @Test void testArrayUserDefinedFunction() throws Exception { |
| try (Connection connection = DriverManager.getConnection("jdbc:calcite:")) { |
| CalciteConnection calciteConnection = |
| connection.unwrap(CalciteConnection.class); |
| SchemaPlus rootSchema = calciteConnection.getRootSchema(); |
| rootSchema.add("hr", new ReflectiveSchema(new HrSchema())); |
| |
| SchemaPlus post = rootSchema.add("POST", new AbstractSchema()); |
| post.add("ARRAY_APPEND", new ArrayAppendDoubleFunction()); |
| post.add("ARRAY_APPEND", new ArrayAppendIntegerFunction()); |
| final String sql = "select \"empid\" as EMPLOYEE_ID,\n" |
| + " \"name\" || ' ' || \"name\" as EMPLOYEE_NAME,\n" |
| + " \"salary\" as EMPLOYEE_SALARY,\n" |
| + " POST.ARRAY_APPEND(ARRAY[1,2,3], \"deptno\") as DEPARTMENTS\n" |
| + "from \"hr\".\"emps\""; |
| |
| final String result = "" |
| + "EMPLOYEE_ID=100; EMPLOYEE_NAME=Bill Bill;" |
| + " EMPLOYEE_SALARY=10000.0; DEPARTMENTS=[1, 2, 3, 10]\n" |
| + "EMPLOYEE_ID=200; EMPLOYEE_NAME=Eric Eric;" |
| + " EMPLOYEE_SALARY=8000.0; DEPARTMENTS=[1, 2, 3, 20]\n" |
| + "EMPLOYEE_ID=150; EMPLOYEE_NAME=Sebastian Sebastian;" |
| + " EMPLOYEE_SALARY=7000.0; DEPARTMENTS=[1, 2, 3, 10]\n" |
| + "EMPLOYEE_ID=110; EMPLOYEE_NAME=Theodore Theodore;" |
| + " EMPLOYEE_SALARY=11500.0; DEPARTMENTS=[1, 2, 3, 10]\n"; |
| |
| try (Statement statement = connection.createStatement(); |
| ResultSet resultSet = statement.executeQuery(sql)) { |
| assertThat(CalciteAssert.toString(resultSet), is(result)); |
| } |
| } |
| } |
| |
| /** |
| * Base class for functions that append arrays. |
| */ |
| private abstract static class ArrayAppendScalarFunction |
| implements ScalarFunction, ImplementableFunction { |
| public List<FunctionParameter> getParameters() { |
| final List<FunctionParameter> parameters = new ArrayList<>(); |
| for (final Ord<RelProtoDataType> type : Ord.zip(getParams())) { |
| parameters.add( |
| new FunctionParameter() { |
| public int getOrdinal() { |
| return type.i; |
| } |
| |
| public String getName() { |
| return "arg" + type.i; |
| } |
| |
| public RelDataType getType(RelDataTypeFactory typeFactory) { |
| return type.e.apply(typeFactory); |
| } |
| |
| public boolean isOptional() { |
| return false; |
| } |
| }); |
| } |
| return parameters; |
| } |
| |
| protected abstract List<RelProtoDataType> getParams(); |
| |
| @Override public CallImplementor getImplementor() { |
| return (translator, call, nullAs) -> { |
| Method lookupMethod = |
| Types.lookupMethod(Smalls.AllTypesFunction.class, |
| "arrayAppendFun", List.class, Integer.class); |
| return Expressions.call(lookupMethod, |
| translator.translateList(call.getOperands(), nullAs)); |
| }; |
| } |
| } |
| |
| /** Function with signature "f(ARRAY OF INTEGER, INTEGER) returns ARRAY OF |
| * INTEGER". */ |
| private static class ArrayAppendIntegerFunction |
| extends ArrayAppendScalarFunction { |
| @Override public RelDataType getReturnType(RelDataTypeFactory typeFactory) { |
| return typeFactory.createArrayType( |
| typeFactory.createSqlType(SqlTypeName.INTEGER), -1); |
| } |
| |
| @Override public List<RelProtoDataType> getParams() { |
| return ImmutableList.of( |
| typeFactory -> typeFactory.createArrayType( |
| typeFactory.createSqlType(SqlTypeName.INTEGER), -1), |
| typeFactory -> typeFactory.createSqlType(SqlTypeName.INTEGER)); |
| } |
| } |
| |
| /** Function with signature "f(ARRAY OF DOUBLE, INTEGER) returns ARRAY OF |
| * DOUBLE". */ |
| private static class ArrayAppendDoubleFunction |
| extends ArrayAppendScalarFunction { |
| public RelDataType getReturnType(RelDataTypeFactory typeFactory) { |
| return typeFactory.createArrayType( |
| typeFactory.createSqlType(SqlTypeName.DOUBLE), -1); |
| } |
| |
| public List<RelProtoDataType> getParams() { |
| return ImmutableList.of( |
| typeFactory -> typeFactory.createArrayType( |
| typeFactory.createSqlType(SqlTypeName.DOUBLE), -1), |
| typeFactory -> typeFactory.createSqlType(SqlTypeName.INTEGER)); |
| } |
| } |
| |
| } |
