Repository: calcite Updated Branches: refs/heads/master bdaa33f9c -> 45b405c4c
[CALCITE-1907] Table function with 1 column gives ClassCastException Create a new test class, TableFunctionTest. Move some existing tests for user-defined table functions from JdbcTest and UdfTest into TableFunctionTest, and add some new ones. StdinTableFunction, to be added in [CALCITE-1896], also relies on this fix. Project: http://git-wip-us.apache.org/repos/asf/calcite/repo Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/a473eca5 Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/a473eca5 Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/a473eca5 Branch: refs/heads/master Commit: a473eca597300395ff89f949401505ec122a9774 Parents: bdaa33f Author: Julian Hyde <[email protected]> Authored: Thu Jul 27 15:52:51 2017 -0700 Committer: Julian Hyde <[email protected]> Committed: Thu Jul 27 16:02:54 2017 -0700 ---------------------------------------------------------------------- .../enumerable/EnumerableRelImplementor.java | 26 +- .../enumerable/EnumerableTableFunctionScan.java | 11 +- .../calcite/sql/validate/SqlValidatorImpl.java | 17 +- .../org/apache/calcite/test/CalciteSuite.java | 1 + .../java/org/apache/calcite/test/JdbcTest.java | 249 ----------- .../apache/calcite/test/TableFunctionTest.java | 413 +++++++++++++++++++ .../java/org/apache/calcite/test/UdfTest.java | 5 +- .../java/org/apache/calcite/util/Smalls.java | 65 +++ 8 files changed, 517 insertions(+), 270 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/calcite/blob/a473eca5/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableRelImplementor.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableRelImplementor.java b/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableRelImplementor.java index 6e9033c..288652f 100644 --- a/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableRelImplementor.java +++ b/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableRelImplementor.java @@ -28,6 +28,7 @@ import org.apache.calcite.linq4j.tree.ConditionalStatement; import org.apache.calcite.linq4j.tree.ConstantExpression; import org.apache.calcite.linq4j.tree.Expression; import org.apache.calcite.linq4j.tree.Expressions; +import org.apache.calcite.linq4j.tree.GotoStatement; import org.apache.calcite.linq4j.tree.MemberDeclaration; import org.apache.calcite.linq4j.tree.MethodCallExpression; import org.apache.calcite.linq4j.tree.NewArrayExpression; @@ -105,7 +106,30 @@ public class EnumerableRelImplementor extends JavaRelImplementor { public ClassDeclaration implementRoot(EnumerableRel rootRel, EnumerableRel.Prefer prefer) { - final EnumerableRel.Result result = rootRel.implement(this, prefer); + EnumerableRel.Result result = rootRel.implement(this, prefer); + switch (prefer) { + case ARRAY: + if (result.physType.getFormat() == JavaRowFormat.ARRAY + && rootRel.getRowType().getFieldCount() == 1) { + BlockBuilder bb = new BlockBuilder(); + Expression e = null; + for (Statement statement : result.block.statements) { + if (statement instanceof GotoStatement) { + e = bb.append("v", ((GotoStatement) statement).expression); + } else { + bb.add(statement); + } + } + if (e != null) { + bb.add( + Expressions.return_(null, + Expressions.call(null, BuiltInMethod.SLICE0.method, e))); + } + result = new EnumerableRel.Result(bb.toBlock(), result.physType, + JavaRowFormat.SCALAR); + } + } + final List<MemberDeclaration> memberDeclarations = new ArrayList<>(); new TypeRegistrar(memberDeclarations).go(result); http://git-wip-us.apache.org/repos/asf/calcite/blob/a473eca5/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableTableFunctionScan.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableTableFunctionScan.java b/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableTableFunctionScan.java index 90892fa..0a0b810 100644 --- a/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableTableFunctionScan.java +++ b/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableTableFunctionScan.java @@ -18,7 +18,6 @@ package org.apache.calcite.adapter.enumerable; import org.apache.calcite.adapter.java.JavaTypeFactory; import org.apache.calcite.linq4j.tree.BlockBuilder; -import org.apache.calcite.linq4j.tree.Expression; import org.apache.calcite.linq4j.tree.Expressions; import org.apache.calcite.plan.RelOptCluster; import org.apache.calcite.plan.RelTraitSet; @@ -31,7 +30,6 @@ import org.apache.calcite.rex.RexNode; import org.apache.calcite.schema.QueryableTable; import org.apache.calcite.schema.impl.TableFunctionImpl; import org.apache.calcite.sql.validate.SqlUserDefinedTableFunction; -import org.apache.calcite.util.BuiltInMethod; import java.lang.reflect.Method; import java.lang.reflect.Type; @@ -66,14 +64,12 @@ public class EnumerableTableFunctionScan extends TableFunctionScan BlockBuilder bb = new BlockBuilder(); // Non-array user-specified types are not supported yet final JavaRowFormat format; - boolean array = false; if (getElementType() == null) { format = JavaRowFormat.ARRAY; } else if (rowType.getFieldCount() == 1 && isQueryable()) { format = JavaRowFormat.SCALAR; } else if (getElementType() instanceof Class && Object[].class.isAssignableFrom((Class) getElementType())) { - array = true; format = JavaRowFormat.ARRAY; } else { format = JavaRowFormat.CUSTOM; @@ -84,12 +80,7 @@ public class EnumerableTableFunctionScan extends TableFunctionScan RexToLixTranslator t = RexToLixTranslator.forAggregation( (JavaTypeFactory) getCluster().getTypeFactory(), bb, null); t = t.setCorrelates(implementor.allCorrelateVariables); - Expression translated = t.translate(getCall()); - if (array && rowType.getFieldCount() == 1) { - translated = - Expressions.call(null, BuiltInMethod.SLICE0.method, translated); - } - bb.add(Expressions.return_(null, translated)); + bb.add(Expressions.return_(null, t.translate(getCall()))); return implementor.result(physType, bb.toBlock()); } http://git-wip-us.apache.org/repos/asf/calcite/blob/a473eca5/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java index 78ce39b..c2dc999 100644 --- a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java +++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java @@ -1005,18 +1005,19 @@ public class SqlValidatorImpl implements SqlValidatorWithHints { return getNamespace(id, idScope); } else if (node instanceof SqlCall) { // Handle extended identifiers. - final SqlCall sqlCall = (SqlCall) node; - final SqlKind sqlKind = sqlCall.getOperator().getKind(); - if (sqlKind.equals(SqlKind.EXTEND)) { - final SqlIdentifier id = (SqlIdentifier) sqlCall.getOperandList().get(0); + final SqlCall call = (SqlCall) node; + switch (call.getOperator().getKind()) { + case EXTEND: + final SqlIdentifier id = (SqlIdentifier) call.getOperandList().get(0); final DelegatingScope idScope = (DelegatingScope) scope; return getNamespace(id, idScope); - } else { - final SqlNode nested = sqlCall.getOperandList().get(0); - if (sqlKind.equals(SqlKind.AS) - && nested.getKind().equals(SqlKind.EXTEND)) { + case AS: + final SqlNode nested = call.getOperandList().get(0); + switch (nested.getKind()) { + case EXTEND: return getNamespace(nested, scope); } + break; } } return getNamespace(node); http://git-wip-us.apache.org/repos/asf/calcite/blob/a473eca5/core/src/test/java/org/apache/calcite/test/CalciteSuite.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/test/CalciteSuite.java b/core/src/test/java/org/apache/calcite/test/CalciteSuite.java index b56f73e..6853644 100644 --- a/core/src/test/java/org/apache/calcite/test/CalciteSuite.java +++ b/core/src/test/java/org/apache/calcite/test/CalciteSuite.java @@ -144,6 +144,7 @@ import org.junit.runners.Suite; // slow tests (above 1s) UdfTest.class, + TableFunctionTest.class, PlannerTest.class, RelBuilderTest.class, PigRelBuilderTest.class, http://git-wip-us.apache.org/repos/asf/calcite/blob/a473eca5/core/src/test/java/org/apache/calcite/test/JdbcTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/test/JdbcTest.java b/core/src/test/java/org/apache/calcite/test/JdbcTest.java index 8496e42..d848592 100644 --- a/core/src/test/java/org/apache/calcite/test/JdbcTest.java +++ b/core/src/test/java/org/apache/calcite/test/JdbcTest.java @@ -61,7 +61,6 @@ import org.apache.calcite.runtime.SqlFunctions; import org.apache.calcite.schema.ModifiableTable; import org.apache.calcite.schema.ModifiableView; import org.apache.calcite.schema.QueryableTable; -import org.apache.calcite.schema.ScannableTable; import org.apache.calcite.schema.Schema; import org.apache.calcite.schema.SchemaFactory; import org.apache.calcite.schema.SchemaPlus; @@ -73,7 +72,6 @@ import org.apache.calcite.schema.TranslatableTable; import org.apache.calcite.schema.impl.AbstractSchema; import org.apache.calcite.schema.impl.AbstractTable; import org.apache.calcite.schema.impl.AbstractTableQueryable; -import org.apache.calcite.schema.impl.TableFunctionImpl; import org.apache.calcite.schema.impl.TableMacroImpl; import org.apache.calcite.schema.impl.ViewTable; import org.apache.calcite.sql.SqlCall; @@ -403,254 +401,7 @@ public class JdbcTest { } } - /** - * Tests a table function with literal arguments. - */ - @Test public void testTableFunction() - throws SQLException, ClassNotFoundException { - Connection connection = - DriverManager.getConnection("jdbc:calcite:"); - CalciteConnection calciteConnection = - connection.unwrap(CalciteConnection.class); - SchemaPlus rootSchema = calciteConnection.getRootSchema(); - SchemaPlus schema = rootSchema.add("s", new AbstractSchema()); - final TableFunction table = - TableFunctionImpl.create(Smalls.GENERATE_STRINGS_METHOD); - schema.add("GenerateStrings", table); - ResultSet resultSet = connection.createStatement().executeQuery("select *\n" - + "from table(\"s\".\"GenerateStrings\"(5)) as t(n, c)\n" - + "where char_length(c) > 3"); - assertThat(CalciteAssert.toString(resultSet), - equalTo("N=4; C=abcd\n")); - } - - /** - * Tests a table function that implements {@link ScannableTable} and returns - * a single column. - */ - @Test public void testScannableTableFunction() - throws SQLException, ClassNotFoundException { - Connection connection = DriverManager.getConnection("jdbc:calcite:"); - CalciteConnection calciteConnection = - connection.unwrap(CalciteConnection.class); - SchemaPlus rootSchema = calciteConnection.getRootSchema(); - SchemaPlus schema = rootSchema.add("s", new AbstractSchema()); - final TableFunction table = TableFunctionImpl.create(Smalls.MAZE_METHOD); - schema.add("Maze", table); - final String sql = "select *\n" - + "from table(\"s\".\"Maze\"(5, 3, 1))"; - ResultSet resultSet = connection.createStatement().executeQuery(sql); - final String result = "S=abcde\n" - + "S=xyz\n" - + "S=generate(w=5, h=3, s=1)\n"; - assertThat(CalciteAssert.toString(resultSet), is(result)); - } - - /** As {@link #testScannableTableFunction()} but with named parameters. */ - @Test public void testScannableTableFunctionWithNamedParameters() - throws SQLException, ClassNotFoundException { - Connection connection = DriverManager.getConnection("jdbc:calcite:"); - CalciteConnection calciteConnection = - connection.unwrap(CalciteConnection.class); - SchemaPlus rootSchema = calciteConnection.getRootSchema(); - SchemaPlus schema = rootSchema.add("s", new AbstractSchema()); - final TableFunction table = TableFunctionImpl.create(Smalls.MAZE2_METHOD); - schema.add("Maze", table); - final String sql = "select *\n" - + "from table(\"s\".\"Maze\"(5, 3, 1))"; - final Statement statement = connection.createStatement(); - ResultSet resultSet = statement.executeQuery(sql); - final String result = "S=abcde\n" - + "S=xyz\n"; - assertThat(CalciteAssert.toString(resultSet), - is(result + "S=generate2(w=5, h=3, s=1)\n")); - - final String sql2 = "select *\n" - + "from table(\"s\".\"Maze\"(WIDTH => 5, HEIGHT => 3, SEED => 1))"; - resultSet = statement.executeQuery(sql2); - assertThat(CalciteAssert.toString(resultSet), - is(result + "S=generate2(w=5, h=3, s=1)\n")); - - final String sql3 = "select *\n" - + "from table(\"s\".\"Maze\"(HEIGHT => 3, WIDTH => 5))"; - resultSet = statement.executeQuery(sql3); - assertThat(CalciteAssert.toString(resultSet), - is(result + "S=generate2(w=5, h=3, s=null)\n")); - connection.close(); - } - - /** As {@link #testScannableTableFunction()} but with named parameters. */ - @Test public void testMultipleScannableTableFunctionWithNamedParameters() - throws SQLException, ClassNotFoundException { - Connection connection = DriverManager.getConnection("jdbc:calcite:"); - CalciteConnection calciteConnection = - connection.unwrap(CalciteConnection.class); - SchemaPlus rootSchema = calciteConnection.getRootSchema(); - SchemaPlus schema = rootSchema.add("s", new AbstractSchema()); - final TableFunction table1 = TableFunctionImpl.create(Smalls.MAZE_METHOD); - schema.add("Maze", table1); - final TableFunction table2 = TableFunctionImpl.create(Smalls.MAZE2_METHOD); - schema.add("Maze", table2); - final TableFunction table3 = TableFunctionImpl.create(Smalls.MAZE3_METHOD); - schema.add("Maze", table3); - final String sql = "select *\n" - + "from table(\"s\".\"Maze\"(5, 3, 1))"; - final Statement statement = connection.createStatement(); - ResultSet resultSet = statement.executeQuery(sql); - final String result = "S=abcde\n" - + "S=xyz\n"; - assertThat(CalciteAssert.toString(resultSet), - is(result + "S=generate(w=5, h=3, s=1)\n")); - - final String sql2 = "select *\n" - + "from table(\"s\".\"Maze\"(WIDTH => 5, HEIGHT => 3, SEED => 1))"; - resultSet = statement.executeQuery(sql2); - assertThat(CalciteAssert.toString(resultSet), - is(result + "S=generate2(w=5, h=3, s=1)\n")); - - final String sql3 = "select *\n" - + "from table(\"s\".\"Maze\"(HEIGHT => 3, WIDTH => 5))"; - resultSet = statement.executeQuery(sql3); - assertThat(CalciteAssert.toString(resultSet), - is(result + "S=generate2(w=5, h=3, s=null)\n")); - - final String sql4 = "select *\n" - + "from table(\"s\".\"Maze\"(FOO => 'a'))"; - resultSet = statement.executeQuery(sql4); - assertThat(CalciteAssert.toString(resultSet), - is(result + "S=generate3(foo=a)\n")); - connection.close(); - } - - /** - * Tests a table function that returns different row type based on - * actual call arguments. - */ - @Test public void testTableFunctionDynamicStructure() - throws SQLException, ClassNotFoundException { - Connection connection = getConnectionWithMultiplyFunction(); - final PreparedStatement ps = connection.prepareStatement("select *\n" - + "from table(\"s\".\"multiplication\"(4, 3, ?))\n"); - ps.setInt(1, 100); - ResultSet resultSet = ps.executeQuery(); - assertThat(CalciteAssert.toString(resultSet), - equalTo("row_name=row 0; c1=101; c2=102; c3=103; c4=104\n" - + "row_name=row 1; c1=102; c2=104; c3=106; c4=108\n" - + "row_name=row 2; c1=103; c2=106; c3=109; c4=112\n")); - } - - /** - * Tests that non-nullable arguments of a table function must be provided - * as literals. - */ - @Ignore("SQLException does not include message from nested exception") - @Test public void testTableFunctionNonNullableMustBeLiterals() - throws SQLException, ClassNotFoundException { - Connection connection = getConnectionWithMultiplyFunction(); - try { - final PreparedStatement ps = connection.prepareStatement("select *\n" - + "from table(\"s\".\"multiplication\"(?, 3, 100))\n"); - ps.setInt(1, 100); - ResultSet resultSet = ps.executeQuery(); - fail("Should fail, got " + resultSet); - } catch (SQLException e) { - assertThat(e.getMessage(), - containsString("Wrong arguments for table function 'public static " - + "org.apache.calcite.schema.QueryableTable " - + "org.apache.calcite.test.JdbcTest" - + ".multiplicationTable(int,int,java.lang.Integer)'" - + " call. Expected '[int, int, class" - + "java.lang.Integer]', actual '[null, 3, 100]'")); - } - } - - private Connection getConnectionWithMultiplyFunction() - throws ClassNotFoundException, SQLException { - Connection connection = - DriverManager.getConnection("jdbc:calcite:"); - CalciteConnection calciteConnection = - connection.unwrap(CalciteConnection.class); - SchemaPlus rootSchema = calciteConnection.getRootSchema(); - SchemaPlus schema = rootSchema.add("s", new AbstractSchema()); - final TableFunction table = - TableFunctionImpl.create(Smalls.MULTIPLICATION_TABLE_METHOD); - schema.add("multiplication", table); - return connection; - } - - /** - * Tests a table function that takes cursor input. - */ - @Ignore("CannotPlanException: Node [rel#18:Subset#4.ENUMERABLE.[]] " - + "could not be implemented") - @Test public void testTableFunctionCursorInputs() - throws SQLException, ClassNotFoundException { - Connection connection = - DriverManager.getConnection("jdbc:calcite:"); - CalciteConnection calciteConnection = - connection.unwrap(CalciteConnection.class); - SchemaPlus rootSchema = calciteConnection.getRootSchema(); - SchemaPlus schema = rootSchema.add("s", new AbstractSchema()); - final TableFunction table = - TableFunctionImpl.create(Smalls.GENERATE_STRINGS_METHOD); - schema.add("GenerateStrings", table); - final TableFunction add = - TableFunctionImpl.create(Smalls.PROCESS_CURSOR_METHOD); - schema.add("process", add); - final PreparedStatement ps = connection.prepareStatement("select *\n" - + "from table(\"s\".\"process\"(2,\n" - + "cursor(select * from table(\"s\".\"GenerateStrings\"(?)))\n" - + ")) as t(u)\n" - + "where u > 3"); - ps.setInt(1, 5); - ResultSet resultSet = ps.executeQuery(); - // GenerateStrings returns 0..4, then 2 is added (process function), - // thus 2..6, finally where u > 3 leaves just 4..6 - assertThat(CalciteAssert.toString(resultSet), - equalTo("u=4\n" - + "u=5\n" - + "u=6\n")); - } - /** - * Tests a table function that takes multiple cursor inputs. - */ - @Ignore("CannotPlanException: Node [rel#24:Subset#6.ENUMERABLE.[]] " - + "could not be implemented") - @Test public void testTableFunctionCursorsInputs() - throws SQLException, ClassNotFoundException { - Connection connection = - getConnectionWithMultiplyFunction(); - CalciteConnection calciteConnection = - connection.unwrap(CalciteConnection.class); - SchemaPlus rootSchema = calciteConnection.getRootSchema(); - SchemaPlus schema = rootSchema.getSubSchema("s"); - final TableFunction table = - TableFunctionImpl.create(Smalls.GENERATE_STRINGS_METHOD); - schema.add("GenerateStrings", table); - final TableFunction add = - TableFunctionImpl.create(Smalls.PROCESS_CURSORS_METHOD); - schema.add("process", add); - final PreparedStatement ps = connection.prepareStatement("select *\n" - + "from table(\"s\".\"process\"(2,\n" - + "cursor(select * from table(\"s\".\"multiplication\"(5,5,0))),\n" - + "cursor(select * from table(\"s\".\"GenerateStrings\"(?)))\n" - + ")) as t(u)\n" - + "where u > 3"); - ps.setInt(1, 5); - ResultSet resultSet = ps.executeQuery(); - // GenerateStrings produce 0..4 - // multiplication produce 1..5 - // process sums and adds 2 - // sum is 2 + 1..9 == 3..9 - assertThat(CalciteAssert.toString(resultSet), - equalTo("u=4\n" - + "u=5\n" - + "u=6\n" - + "u=7\n" - + "u=8\n" - + "u=9\n")); - } /** * Tests {@link org.apache.calcite.sql.advise.SqlAdvisorGetHintsFunction}. http://git-wip-us.apache.org/repos/asf/calcite/blob/a473eca5/core/src/test/java/org/apache/calcite/test/TableFunctionTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/test/TableFunctionTest.java b/core/src/test/java/org/apache/calcite/test/TableFunctionTest.java new file mode 100644 index 0000000..12392b3 --- /dev/null +++ b/core/src/test/java/org/apache/calcite/test/TableFunctionTest.java @@ -0,0 +1,413 @@ +/* + * 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.jdbc.CalciteConnection; +import org.apache.calcite.schema.ScannableTable; +import org.apache.calcite.schema.SchemaPlus; +import org.apache.calcite.schema.TableFunction; +import org.apache.calcite.schema.impl.AbstractSchema; +import org.apache.calcite.schema.impl.TableFunctionImpl; +import org.apache.calcite.util.Smalls; + +import com.google.common.base.Function; + +import org.junit.Ignore; +import org.junit.Test; + +import java.sql.Connection; +import java.sql.DriverManager; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.sql.Statement; +import java.util.ArrayList; +import java.util.List; + +import static org.hamcrest.CoreMatchers.containsString; +import static org.hamcrest.CoreMatchers.equalTo; +import static org.hamcrest.CoreMatchers.is; +import static org.junit.Assert.assertThat; +import static org.junit.Assert.fail; + +/** + * Tests for user-defined table functions. + * + * @see UdfTest + * @see Smalls + */ +public class TableFunctionTest { + private CalciteAssert.AssertThat with() { + final String c = Smalls.class.getName(); + final String m = Smalls.MULTIPLICATION_TABLE_METHOD.getName(); + final String m2 = Smalls.FIBONACCI_TABLE_METHOD.getName(); + final String m3 = Smalls.FIBONACCI2_TABLE_METHOD.getName(); + return CalciteAssert.model("{\n" + + " version: '1.0',\n" + + " schemas: [\n" + + " {\n" + + " name: 's',\n" + + " functions: [\n" + + " {\n" + + " name: 'multiplication',\n" + + " className: '" + c + "',\n" + + " methodName: '" + m + "'\n" + + " }, {\n" + + " name: 'fibonacci',\n" + + " className: '" + c + "',\n" + + " methodName: '" + m2 + "'\n" + + " }, {\n" + + " name: 'fibonacci2',\n" + + " className: '" + c + "',\n" + + " methodName: '" + m3 + "'\n" + + " }\n" + + " ]\n" + + " }\n" + + " ]\n" + + "}") + .withDefaultSchema("s"); + } + + /** + * Tests a table function with literal arguments. + */ + @Test public void testTableFunction() + throws SQLException, ClassNotFoundException { + Connection connection = + DriverManager.getConnection("jdbc:calcite:"); + CalciteConnection calciteConnection = + connection.unwrap(CalciteConnection.class); + SchemaPlus rootSchema = calciteConnection.getRootSchema(); + SchemaPlus schema = rootSchema.add("s", new AbstractSchema()); + final TableFunction table = + TableFunctionImpl.create(Smalls.GENERATE_STRINGS_METHOD); + schema.add("GenerateStrings", table); + ResultSet resultSet = connection.createStatement().executeQuery("select *\n" + + "from table(\"s\".\"GenerateStrings\"(5)) as t(n, c)\n" + + "where char_length(c) > 3"); + assertThat(CalciteAssert.toString(resultSet), + equalTo("N=4; C=abcd\n")); + } + + /** + * Tests a table function that implements {@link ScannableTable} and returns + * a single column. + */ + @Test public void testScannableTableFunction() + throws SQLException, ClassNotFoundException { + Connection connection = DriverManager.getConnection("jdbc:calcite:"); + CalciteConnection calciteConnection = + connection.unwrap(CalciteConnection.class); + SchemaPlus rootSchema = calciteConnection.getRootSchema(); + SchemaPlus schema = rootSchema.add("s", new AbstractSchema()); + final TableFunction table = TableFunctionImpl.create(Smalls.MAZE_METHOD); + schema.add("Maze", table); + final String sql = "select *\n" + + "from table(\"s\".\"Maze\"(5, 3, 1))"; + ResultSet resultSet = connection.createStatement().executeQuery(sql); + final String result = "S=abcde\n" + + "S=xyz\n" + + "S=generate(w=5, h=3, s=1)\n"; + assertThat(CalciteAssert.toString(resultSet), is(result)); + } + + /** As {@link #testScannableTableFunction()} but with named parameters. */ + @Test public void testScannableTableFunctionWithNamedParameters() + throws SQLException, ClassNotFoundException { + Connection connection = DriverManager.getConnection("jdbc:calcite:"); + CalciteConnection calciteConnection = + connection.unwrap(CalciteConnection.class); + SchemaPlus rootSchema = calciteConnection.getRootSchema(); + SchemaPlus schema = rootSchema.add("s", new AbstractSchema()); + final TableFunction table = TableFunctionImpl.create(Smalls.MAZE2_METHOD); + schema.add("Maze", table); + final String sql = "select *\n" + + "from table(\"s\".\"Maze\"(5, 3, 1))"; + final Statement statement = connection.createStatement(); + ResultSet resultSet = statement.executeQuery(sql); + final String result = "S=abcde\n" + + "S=xyz\n"; + assertThat(CalciteAssert.toString(resultSet), + is(result + "S=generate2(w=5, h=3, s=1)\n")); + + final String sql2 = "select *\n" + + "from table(\"s\".\"Maze\"(WIDTH => 5, HEIGHT => 3, SEED => 1))"; + resultSet = statement.executeQuery(sql2); + assertThat(CalciteAssert.toString(resultSet), + is(result + "S=generate2(w=5, h=3, s=1)\n")); + + final String sql3 = "select *\n" + + "from table(\"s\".\"Maze\"(HEIGHT => 3, WIDTH => 5))"; + resultSet = statement.executeQuery(sql3); + assertThat(CalciteAssert.toString(resultSet), + is(result + "S=generate2(w=5, h=3, s=null)\n")); + connection.close(); + } + + /** As {@link #testScannableTableFunction()} but with named parameters. */ + @Test public void testMultipleScannableTableFunctionWithNamedParameters() + throws SQLException, ClassNotFoundException { + Connection connection = DriverManager.getConnection("jdbc:calcite:"); + CalciteConnection calciteConnection = + connection.unwrap(CalciteConnection.class); + SchemaPlus rootSchema = calciteConnection.getRootSchema(); + SchemaPlus schema = rootSchema.add("s", new AbstractSchema()); + final TableFunction table1 = TableFunctionImpl.create(Smalls.MAZE_METHOD); + schema.add("Maze", table1); + final TableFunction table2 = TableFunctionImpl.create(Smalls.MAZE2_METHOD); + schema.add("Maze", table2); + final TableFunction table3 = TableFunctionImpl.create(Smalls.MAZE3_METHOD); + schema.add("Maze", table3); + final String sql = "select *\n" + + "from table(\"s\".\"Maze\"(5, 3, 1))"; + final Statement statement = connection.createStatement(); + ResultSet resultSet = statement.executeQuery(sql); + final String result = "S=abcde\n" + + "S=xyz\n"; + assertThat(CalciteAssert.toString(resultSet), + is(result + "S=generate(w=5, h=3, s=1)\n")); + + final String sql2 = "select *\n" + + "from table(\"s\".\"Maze\"(WIDTH => 5, HEIGHT => 3, SEED => 1))"; + resultSet = statement.executeQuery(sql2); + assertThat(CalciteAssert.toString(resultSet), + is(result + "S=generate2(w=5, h=3, s=1)\n")); + + final String sql3 = "select *\n" + + "from table(\"s\".\"Maze\"(HEIGHT => 3, WIDTH => 5))"; + resultSet = statement.executeQuery(sql3); + assertThat(CalciteAssert.toString(resultSet), + is(result + "S=generate2(w=5, h=3, s=null)\n")); + + final String sql4 = "select *\n" + + "from table(\"s\".\"Maze\"(FOO => 'a'))"; + resultSet = statement.executeQuery(sql4); + assertThat(CalciteAssert.toString(resultSet), + is(result + "S=generate3(foo=a)\n")); + connection.close(); + } + + /** + * Tests a table function that returns different row type based on + * actual call arguments. + */ + @Test public void testTableFunctionDynamicStructure() + throws SQLException, ClassNotFoundException { + Connection connection = getConnectionWithMultiplyFunction(); + final PreparedStatement ps = connection.prepareStatement("select *\n" + + "from table(\"s\".\"multiplication\"(4, 3, ?))\n"); + ps.setInt(1, 100); + ResultSet resultSet = ps.executeQuery(); + assertThat(CalciteAssert.toString(resultSet), + equalTo("row_name=row 0; c1=101; c2=102; c3=103; c4=104\n" + + "row_name=row 1; c1=102; c2=104; c3=106; c4=108\n" + + "row_name=row 2; c1=103; c2=106; c3=109; c4=112\n")); + } + + /** + * Tests that non-nullable arguments of a table function must be provided + * as literals. + */ + @Ignore("SQLException does not include message from nested exception") + @Test public void testTableFunctionNonNullableMustBeLiterals() + throws SQLException, ClassNotFoundException { + Connection connection = getConnectionWithMultiplyFunction(); + try { + final PreparedStatement ps = connection.prepareStatement("select *\n" + + "from table(\"s\".\"multiplication\"(?, 3, 100))\n"); + ps.setInt(1, 100); + ResultSet resultSet = ps.executeQuery(); + fail("Should fail, got " + resultSet); + } catch (SQLException e) { + assertThat(e.getMessage(), + containsString("Wrong arguments for table function 'public static " + + "org.apache.calcite.schema.QueryableTable " + + "org.apache.calcite.test.JdbcTest" + + ".multiplicationTable(int,int,java.lang.Integer)'" + + " call. Expected '[int, int, class" + + "java.lang.Integer]', actual '[null, 3, 100]'")); + } + } + + private Connection getConnectionWithMultiplyFunction() + throws ClassNotFoundException, SQLException { + Connection connection = + DriverManager.getConnection("jdbc:calcite:"); + CalciteConnection calciteConnection = + connection.unwrap(CalciteConnection.class); + SchemaPlus rootSchema = calciteConnection.getRootSchema(); + SchemaPlus schema = rootSchema.add("s", new AbstractSchema()); + final TableFunction table = + TableFunctionImpl.create(Smalls.MULTIPLICATION_TABLE_METHOD); + schema.add("multiplication", table); + return connection; + } + + /** + * Tests a table function that takes cursor input. + */ + @Ignore("CannotPlanException: Node [rel#18:Subset#4.ENUMERABLE.[]] " + + "could not be implemented") + @Test public void testTableFunctionCursorInputs() + throws SQLException, ClassNotFoundException { + Connection connection = + DriverManager.getConnection("jdbc:calcite:"); + CalciteConnection calciteConnection = + connection.unwrap(CalciteConnection.class); + SchemaPlus rootSchema = calciteConnection.getRootSchema(); + SchemaPlus schema = rootSchema.add("s", new AbstractSchema()); + final TableFunction table = + TableFunctionImpl.create(Smalls.GENERATE_STRINGS_METHOD); + schema.add("GenerateStrings", table); + final TableFunction add = + TableFunctionImpl.create(Smalls.PROCESS_CURSOR_METHOD); + schema.add("process", add); + final PreparedStatement ps = connection.prepareStatement("select *\n" + + "from table(\"s\".\"process\"(2,\n" + + "cursor(select * from table(\"s\".\"GenerateStrings\"(?)))\n" + + ")) as t(u)\n" + + "where u > 3"); + ps.setInt(1, 5); + ResultSet resultSet = ps.executeQuery(); + // GenerateStrings returns 0..4, then 2 is added (process function), + // thus 2..6, finally where u > 3 leaves just 4..6 + assertThat(CalciteAssert.toString(resultSet), + equalTo("u=4\n" + + "u=5\n" + + "u=6\n")); + } + + /** + * Tests a table function that takes multiple cursor inputs. + */ + @Ignore("CannotPlanException: Node [rel#24:Subset#6.ENUMERABLE.[]] " + + "could not be implemented") + @Test public void testTableFunctionCursorsInputs() + throws SQLException, ClassNotFoundException { + Connection connection = + getConnectionWithMultiplyFunction(); + CalciteConnection calciteConnection = + connection.unwrap(CalciteConnection.class); + SchemaPlus rootSchema = calciteConnection.getRootSchema(); + SchemaPlus schema = rootSchema.getSubSchema("s"); + final TableFunction table = + TableFunctionImpl.create(Smalls.GENERATE_STRINGS_METHOD); + schema.add("GenerateStrings", table); + final TableFunction add = + TableFunctionImpl.create(Smalls.PROCESS_CURSORS_METHOD); + schema.add("process", add); + final PreparedStatement ps = connection.prepareStatement("select *\n" + + "from table(\"s\".\"process\"(2,\n" + + "cursor(select * from table(\"s\".\"multiplication\"(5,5,0))),\n" + + "cursor(select * from table(\"s\".\"GenerateStrings\"(?)))\n" + + ")) as t(u)\n" + + "where u > 3"); + ps.setInt(1, 5); + ResultSet resultSet = ps.executeQuery(); + // GenerateStrings produce 0..4 + // multiplication produce 1..5 + // process sums and adds 2 + // sum is 2 + 1..9 == 3..9 + assertThat(CalciteAssert.toString(resultSet), + equalTo("u=4\n" + + "u=5\n" + + "u=6\n" + + "u=7\n" + + "u=8\n" + + "u=9\n")); + } + + @Test public void testUserDefinedTableFunction() { + final String q = "select *\n" + + "from table(\"s\".\"multiplication\"(2, 3, 100))\n"; + with().query(q) + .returnsUnordered( + "row_name=row 0; c1=101; c2=102", + "row_name=row 1; c1=102; c2=104", + "row_name=row 2; c1=103; c2=106"); + } + + @Test public void testUserDefinedTableFunction2() { + final String q = "select c1\n" + + "from table(\"s\".\"multiplication\"(2, 3, 100))\n" + + "where c1 + 2 < c2"; + with().query(q) + .throws_("Column 'C1' not found in any table; did you mean 'c1'?"); + } + + @Test public void testUserDefinedTableFunction3() { + final String q = "select \"c1\"\n" + + "from table(\"s\".\"multiplication\"(2, 3, 100))\n" + + "where \"c1\" + 2 < \"c2\""; + with().query(q).returnsUnordered("c1=103"); + } + + @Test public void testUserDefinedTableFunction4() { + final String q = "select *\n" + + "from table(\"s\".\"multiplication\"('2', 3, 100))\n" + + "where c1 + 2 < c2"; + final String e = "No match found for function signature " + + "multiplication(<CHARACTER>, <NUMERIC>, <NUMERIC>)"; + with().query(q).throws_(e); + } + + @Test public void testUserDefinedTableFunction5() { + final String q = "select *\n" + + "from table(\"s\".\"multiplication\"(3, 100))\n" + + "where c1 + 2 < c2"; + final String e = "No match found for function signature " + + "multiplication(<NUMERIC>, <NUMERIC>)"; + with().query(q).throws_(e); + } + + @Test public void testUserDefinedTableFunction6() { + final String q = "select *\n" + + "from table(\"s\".\"fibonacci\"())"; + with().query(q) + .returns( + new Function<ResultSet, Void>() { + public Void apply(ResultSet r) { + try { + final List<Long> numbers = new ArrayList<>(); + while (r.next() && numbers.size() < 13) { + numbers.add(r.getLong(1)); + } + assertThat(numbers.toString(), + is("[1, 1, 2, 3, 5, 8, 13, 21, 34, 55, 89, 144, 233]")); + return null; + } catch (SQLException e) { + throw new RuntimeException(e); + } + } + }); + } + + @Test public void testUserDefinedTableFunction7() { + final String q = "select *\n" + + "from table(\"s\".\"fibonacci2\"(20))\n" + + "where n > 7"; + with().query(q).returnsUnordered("N=13", "N=8"); + } + + @Test public void testUserDefinedTableFunction8() { + final String q = "select count(*) as c\n" + + "from table(\"s\".\"fibonacci2\"(20))"; + with().query(q).returnsUnordered("C=7"); + } +} + +// End TableFunctionTest.java http://git-wip-us.apache.org/repos/asf/calcite/blob/a473eca5/core/src/test/java/org/apache/calcite/test/UdfTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/test/UdfTest.java b/core/src/test/java/org/apache/calcite/test/UdfTest.java index 0c1de28..2cdc449 100644 --- a/core/src/test/java/org/apache/calcite/test/UdfTest.java +++ b/core/src/test/java/org/apache/calcite/test/UdfTest.java @@ -57,8 +57,9 @@ import static org.hamcrest.CoreMatchers.is; import static org.junit.Assert.assertThat; /** - * Tests for user-defined functions (including user-defined table functions - * and user-defined aggregate functions). + * Tests for user-defined functions; + * includes user-defined aggregate functions + * but user-defined table functions are in {@link TableFunctionTest}. * * @see Smalls */ http://git-wip-us.apache.org/repos/asf/calcite/blob/a473eca5/core/src/test/java/org/apache/calcite/util/Smalls.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/util/Smalls.java b/core/src/test/java/org/apache/calcite/util/Smalls.java index ba7bb8a..42f0de5 100644 --- a/core/src/test/java/org/apache/calcite/util/Smalls.java +++ b/core/src/test/java/org/apache/calcite/util/Smalls.java @@ -18,6 +18,7 @@ package org.apache.calcite.util; import org.apache.calcite.DataContext; import org.apache.calcite.adapter.java.AbstractQueryableTable; +import org.apache.calcite.linq4j.AbstractEnumerable; import org.apache.calcite.linq4j.BaseQueryable; import org.apache.calcite.linq4j.Enumerable; import org.apache.calcite.linq4j.Enumerator; @@ -36,7 +37,10 @@ import org.apache.calcite.rex.RexLiteral; import org.apache.calcite.runtime.SqlFunctions; import org.apache.calcite.schema.QueryableTable; import org.apache.calcite.schema.ScannableTable; +import org.apache.calcite.schema.Schema; import org.apache.calcite.schema.SchemaPlus; +import org.apache.calcite.schema.Statistic; +import org.apache.calcite.schema.Statistics; import org.apache.calcite.schema.TranslatableTable; import org.apache.calcite.schema.impl.AbstractTable; import org.apache.calcite.schema.impl.ViewTable; @@ -77,6 +81,10 @@ public class Smalls { public static final Method MULTIPLICATION_TABLE_METHOD = Types.lookupMethod(Smalls.class, "multiplicationTable", int.class, int.class, Integer.class); + public static final Method FIBONACCI_TABLE_METHOD = + Types.lookupMethod(Smalls.class, "fibonacciTable"); + public static final Method FIBONACCI2_TABLE_METHOD = + Types.lookupMethod(Smalls.class, "fibonacciTableWithLimit", long.class); public static final Method VIEW_METHOD = Types.lookupMethod(Smalls.class, "view", String.class); public static final Method STR_METHOD = @@ -210,6 +218,63 @@ public class Smalls { }; } + /** A function that generates the Fibonacci sequence. + * Interesting because it has one column and no arguments. */ + public static ScannableTable fibonacciTable() { + return fibonacciTableWithLimit(-1L); + } + + /** A function that generates the Fibonacci sequence. + * Interesting because it has one column and no arguments. */ + public static ScannableTable fibonacciTableWithLimit(final long limit) { + return new ScannableTable() { + public RelDataType getRowType(RelDataTypeFactory typeFactory) { + return typeFactory.builder().add("N", SqlTypeName.BIGINT).build(); + } + + public Enumerable<Object[]> scan(DataContext root) { + return new AbstractEnumerable<Object[]>() { + public Enumerator<Object[]> enumerator() { + return new Enumerator<Object[]>() { + private long prev = 1; + private long current = 0; + + public Object[] current() { + return new Object[] {current}; + } + + public boolean moveNext() { + final long next = current + prev; + if (limit >= 0 && next > limit) { + return false; + } + prev = current; + current = next; + return true; + } + + public void reset() { + prev = 0; + current = 1; + } + + public void close() { + } + }; + } + }; + } + + public Statistic getStatistic() { + return Statistics.UNKNOWN; + } + + public Schema.TableType getJdbcTableType() { + return Schema.TableType.TABLE; + } + }; + } + /** * A function that adds a number to the first column of input cursor */
