Repository: calcite Updated Branches: refs/heads/master d012b245e -> 1594ed571
Following [CALCITE-941], support named arguments when calling table functions and table macros Project: http://git-wip-us.apache.org/repos/asf/calcite/repo Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/1594ed57 Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/1594ed57 Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/1594ed57 Branch: refs/heads/master Commit: 1594ed571a699fa239c476add44970177663c68e Parents: d012b24 Author: Julian Hyde <[email protected]> Authored: Mon Nov 2 11:47:05 2015 -0800 Committer: Julian Hyde <[email protected]> Committed: Mon Nov 2 11:49:20 2015 -0800 ---------------------------------------------------------------------- core/src/main/codegen/templates/Parser.jj | 120 +++++++++++-------- .../calcite/prepare/CalciteCatalogReader.java | 38 +++--- .../calcite/sql/fun/SqlDefaultOperator.java | 2 +- .../sql/validate/ProcedureNamespace.java | 7 +- .../sql/validate/SqlUserDefinedTableMacro.java | 16 ++- .../calcite/sql2rel/SqlToRelConverter.java | 8 +- .../java/org/apache/calcite/test/JdbcTest.java | 98 ++++++++++++++- site/_docs/reference.md | 73 +++++++---- 8 files changed, 258 insertions(+), 104 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/calcite/blob/1594ed57/core/src/main/codegen/templates/Parser.jj ---------------------------------------------------------------------- diff --git a/core/src/main/codegen/templates/Parser.jj b/core/src/main/codegen/templates/Parser.jj index 1e27217..121de48 100644 --- a/core/src/main/codegen/templates/Parser.jj +++ b/core/src/main/codegen/templates/Parser.jj @@ -798,11 +798,40 @@ List FunctionParameterList( { SqlNode e = null; List list = new ArrayList(); - ExprContext firstExprContext = exprContext; - SqlIdentifier name = null; } { <LPAREN> + [ + <DISTINCT> { + e = SqlSelectKeyword.DISTINCT.symbol(getPos()); + } + | + <ALL> { + e = SqlSelectKeyword.ALL.symbol(getPos()); + } + ] + { + list.add(e); + } + Arg0(list, exprContext) + ( + <COMMA> { + // a comma-list can't appear where only a query is expected + checkNonQueryExpression(exprContext); + } + Arg(list, exprContext) + )* + <RPAREN> + { + return list; + } +} + +void Arg0(List list, ExprContext exprContext) : +{ + SqlIdentifier name = null; + SqlNode e = null; + final ExprContext firstExprContext; { // we've now seen left paren, so queries inside should // be allowed as subqueries @@ -813,21 +842,13 @@ List FunctionParameterList( case ACCEPT_CURSOR: firstExprContext = ExprContext.ACCEPT_ALL; break; + default: + firstExprContext = exprContext; + break; } } - [ <DISTINCT> - { - e = SqlSelectKeyword.DISTINCT.symbol(getPos()); - } - | - <ALL> - { - e = SqlSelectKeyword.ALL.symbol(getPos()); - } - ] - { - list.add(e); - } +} +{ [ name = SimpleIdentifier() <NAMED_ARGUMENT_ASSIGNMENT> ] @@ -847,37 +868,34 @@ List FunctionParameterList( } list.add(e); } - name = null; } +} + +void Arg(List list, ExprContext exprContext) : +{ + SqlIdentifier name = null; + SqlNode e = null; +} +{ + [ + name = SimpleIdentifier() <NAMED_ARGUMENT_ASSIGNMENT> + ] ( - <COMMA> - { - // a comma-list can't appear where only a query is expected - checkNonQueryExpression(exprContext); + <DEFAULT_KW> { + e = SqlStdOperatorTable.DEFAULT.createCall(getPos()); } - [ - name = SimpleIdentifier() <NAMED_ARGUMENT_ASSIGNMENT> - ] - ( - <DEFAULT_KW> { - e = SqlStdOperatorTable.DEFAULT.createCall(getPos()); - } - | - e = Expression(exprContext) - ) - { + | + e = Expression(exprContext) + ) + { + if (e != null) { if (name != null) { e = SqlStdOperatorTable.ARGUMENT_ASSIGNMENT.createCall( name.getParserPosition().plus(e.getParserPosition()), e, name); - name = null; } list.add(e); } - ) * - <RPAREN> - { - return list; } } @@ -1134,26 +1152,28 @@ SqlNode NamedRoutineCall( ExprContext exprContext) : { SqlIdentifier name; - SqlNodeList args; - SqlParserPos pos; + final List<SqlNode> list = Lists.newArrayList(); + final SqlParserPos pos; } { - name = CompoundIdentifier() - { + name = CompoundIdentifier() { pos = getPos(); } - ( - LOOKAHEAD(2) - <LPAREN> - { pos = getPos(); } - <RPAREN> - { pos = pos.plus(getPos()); args = new SqlNodeList(pos); } - | args = ParenthesizedQueryOrCommaList(exprContext) - { pos = pos.plus(getPos()); } - ) + <LPAREN> + [ + Arg0(list, exprContext) + ( + <COMMA> { + // a comma-list can't appear where only a query is expected + checkNonQueryExpression(exprContext); + } + Arg(list, exprContext) + )* + ] + <RPAREN> { SqlNode function = createCall( - name, pos, routineType, null, SqlParserUtil.toNodeArray(args)); + name, pos.plus(getPos()), routineType, null, SqlParserUtil.toNodeArray(list)); return function; } } http://git-wip-us.apache.org/repos/asf/calcite/blob/1594ed57/core/src/main/java/org/apache/calcite/prepare/CalciteCatalogReader.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/prepare/CalciteCatalogReader.java b/core/src/main/java/org/apache/calcite/prepare/CalciteCatalogReader.java index 9e49d17..e3211ea 100644 --- a/core/src/main/java/org/apache/calcite/prepare/CalciteCatalogReader.java +++ b/core/src/main/java/org/apache/calcite/prepare/CalciteCatalogReader.java @@ -35,6 +35,7 @@ import org.apache.calcite.sql.SqlFunctionCategory; import org.apache.calcite.sql.SqlIdentifier; import org.apache.calcite.sql.SqlOperator; import org.apache.calcite.sql.SqlSyntax; +import org.apache.calcite.sql.type.FamilyOperandTypeChecker; import org.apache.calcite.sql.type.InferTypes; import org.apache.calcite.sql.type.OperandTypes; import org.apache.calcite.sql.type.ReturnTypes; @@ -252,34 +253,33 @@ public class CalciteCatalogReader implements Prepare.CatalogReader { typeFamilies.add( Util.first(type.getSqlTypeName().getFamily(), SqlTypeFamily.ANY)); } - final RelDataType returnType; + final Predicate<Integer> optional = + new Predicate<Integer>() { + public boolean apply(Integer input) { + return function.getParameters().get(input).isOptional(); + } + }; + final FamilyOperandTypeChecker typeChecker = + OperandTypes.family(typeFamilies, optional); + final List<RelDataType> paramTypes = toSql(argTypes); if (function instanceof ScalarFunction) { - Predicate<Integer> optional = - new Predicate<Integer>() { - public boolean apply(Integer input) { - return function.getParameters().get(input).isOptional(); - } - }; return new SqlUserDefinedFunction(name, ReturnTypes.explicit(Schemas.proto((ScalarFunction) function)), - InferTypes.explicit(argTypes), - OperandTypes.family(typeFamilies, optional), - toSql(argTypes), function); + InferTypes.explicit(argTypes), typeChecker, paramTypes, function); } else if (function instanceof AggregateFunction) { - returnType = ((AggregateFunction) function).getReturnType(typeFactory); + final RelDataType returnType = + ((AggregateFunction) function).getReturnType(typeFactory); return new SqlUserDefinedAggFunction(name, ReturnTypes.explicit(returnType), InferTypes.explicit(argTypes), - OperandTypes.family(typeFamilies), (AggregateFunction) function); + typeChecker, (AggregateFunction) function); } else if (function instanceof TableMacro) { - return new SqlUserDefinedTableMacro(name, - ReturnTypes.CURSOR, - InferTypes.explicit(argTypes), OperandTypes.family(typeFamilies), + return new SqlUserDefinedTableMacro(name, ReturnTypes.CURSOR, + InferTypes.explicit(argTypes), typeChecker, paramTypes, (TableMacro) function); } else if (function instanceof TableFunction) { - return new SqlUserDefinedTableFunction(name, - ReturnTypes.CURSOR, - InferTypes.explicit(argTypes), OperandTypes.family(typeFamilies), - toSql(argTypes), (TableFunction) function); + return new SqlUserDefinedTableFunction(name, ReturnTypes.CURSOR, + InferTypes.explicit(argTypes), typeChecker, paramTypes, + (TableFunction) function); } else { throw new AssertionError("unknown function type " + function); } http://git-wip-us.apache.org/repos/asf/calcite/blob/1594ed57/core/src/main/java/org/apache/calcite/sql/fun/SqlDefaultOperator.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlDefaultOperator.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlDefaultOperator.java index 5e71a41..a0ab702 100644 --- a/core/src/main/java/org/apache/calcite/sql/fun/SqlDefaultOperator.java +++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlDefaultOperator.java @@ -41,7 +41,7 @@ class SqlDefaultOperator extends SqlSpecialOperator { @Override public void unparse(SqlWriter writer, SqlCall call, int leftPrec, int rightPrec) { - writer.sep(getName()); + writer.keyword(getName()); } } http://git-wip-us.apache.org/repos/asf/calcite/blob/1594ed57/core/src/main/java/org/apache/calcite/sql/validate/ProcedureNamespace.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/validate/ProcedureNamespace.java b/core/src/main/java/org/apache/calcite/sql/validate/ProcedureNamespace.java index 9755877..e436527 100644 --- a/core/src/main/java/org/apache/calcite/sql/validate/ProcedureNamespace.java +++ b/core/src/main/java/org/apache/calcite/sql/validate/ProcedureNamespace.java @@ -18,6 +18,7 @@ package org.apache.calcite.sql.validate; import org.apache.calcite.rel.type.RelDataType; import org.apache.calcite.sql.SqlCall; +import org.apache.calcite.sql.SqlCallBinding; import org.apache.calcite.sql.SqlNode; import org.apache.calcite.sql.SqlOperator; import org.apache.calcite.sql.type.SqlTypeName; @@ -51,18 +52,20 @@ public class ProcedureNamespace extends AbstractNamespace { validator.inferUnknownTypes(validator.unknownType, scope, call); final RelDataType type = validator.deriveTypeImpl(scope, call); final SqlOperator operator = call.getOperator(); + final SqlCallBinding callBinding = + new SqlCallBinding(validator, scope, call); if (operator instanceof SqlUserDefinedTableFunction) { assert type.getSqlTypeName() == SqlTypeName.CURSOR : "User-defined table function should have CURSOR type, not " + type; final SqlUserDefinedTableFunction udf = (SqlUserDefinedTableFunction) operator; - return udf.getRowType(validator.typeFactory, call.getOperandList()); + return udf.getRowType(validator.typeFactory, callBinding.operands()); } else if (operator instanceof SqlUserDefinedTableMacro) { assert type.getSqlTypeName() == SqlTypeName.CURSOR : "User-defined table macro should have CURSOR type, not " + type; final SqlUserDefinedTableMacro udf = (SqlUserDefinedTableMacro) operator; - return udf.getTable(validator.typeFactory, call.getOperandList()) + return udf.getTable(validator.typeFactory, callBinding.operands()) .getRowType(validator.typeFactory); } return type; http://git-wip-us.apache.org/repos/asf/calcite/blob/1594ed57/core/src/main/java/org/apache/calcite/sql/validate/SqlUserDefinedTableMacro.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/validate/SqlUserDefinedTableMacro.java b/core/src/main/java/org/apache/calcite/sql/validate/SqlUserDefinedTableMacro.java index 3e670aa..e20eed0 100644 --- a/core/src/main/java/org/apache/calcite/sql/validate/SqlUserDefinedTableMacro.java +++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlUserDefinedTableMacro.java @@ -45,6 +45,7 @@ import org.apache.calcite.util.NlsString; import org.apache.calcite.util.Pair; import org.apache.calcite.util.Util; +import com.google.common.base.Preconditions; import com.google.common.collect.ImmutableMap; import com.google.common.collect.Lists; @@ -64,14 +65,20 @@ public class SqlUserDefinedTableMacro extends SqlFunction { public SqlUserDefinedTableMacro(SqlIdentifier opName, SqlReturnTypeInference returnTypeInference, SqlOperandTypeInference operandTypeInference, - SqlOperandTypeChecker operandTypeChecker, + SqlOperandTypeChecker operandTypeChecker, List<RelDataType> paramTypes, TableMacro tableMacro) { super(Util.last(opName.names), opName, SqlKind.OTHER_FUNCTION, - returnTypeInference, operandTypeInference, operandTypeChecker, null, + returnTypeInference, operandTypeInference, operandTypeChecker, + Preconditions.checkNotNull(paramTypes), SqlFunctionCategory.USER_DEFINED_FUNCTION); this.tableMacro = tableMacro; } + @Override public List<String> getParamNames() { + return Lists.transform(tableMacro.getParameters(), + FunctionParameter.NAME_FN); + } + /** Returns the table in this UDF, or null if there is no table. */ public TranslatableTable getTable(RelDataTypeFactory typeFactory, List<SqlNode> operandList) { @@ -95,7 +102,7 @@ public class SqlUserDefinedTableMacro extends SqlFunction { List<SqlNode> operandList, Function function, SqlIdentifier opName, boolean failOnNonLiteral) { - List<Object> arguments = new ArrayList<Object>(operandList.size()); + List<Object> arguments = new ArrayList<>(operandList.size()); // Construct a list of arguments, if they are all constants. for (Pair<FunctionParameter, SqlNode> pair : Pair.zip(function.getParameters(), operandList)) { @@ -141,6 +148,9 @@ public class SqlUserDefinedTableMacro extends SqlFunction { if (SqlUtil.isLiteral(right)) { return ((SqlLiteral) right).getValue(); } + if (right.getKind() == SqlKind.DEFAULT) { + return null; // currently NULL is the only default value + } throw new NonLiteralException(); } } http://git-wip-us.apache.org/repos/asf/calcite/blob/1594ed57/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java index b826cea..780b45b 100644 --- a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java +++ b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java @@ -2069,11 +2069,13 @@ public class SqlToRelConverter { // Expand table macro if possible. It's more efficient than // LogicalTableFunctionScan. + final SqlCallBinding callBinding = + new SqlCallBinding(bb.scope.getValidator(), bb.scope, call); if (operator instanceof SqlUserDefinedTableMacro) { final SqlUserDefinedTableMacro udf = (SqlUserDefinedTableMacro) operator; - final TranslatableTable table = udf.getTable(typeFactory, - call.getOperandList()); + final TranslatableTable table = + udf.getTable(typeFactory, callBinding.operands()); final RelDataType rowType = table.getRowType(typeFactory); RelOptTable relOptTable = RelOptTableImpl.create(null, rowType, table); RelNode converted = toRel(relOptTable); @@ -2084,7 +2086,7 @@ public class SqlToRelConverter { Type elementType; if (operator instanceof SqlUserDefinedTableFunction) { SqlUserDefinedTableFunction udtf = (SqlUserDefinedTableFunction) operator; - elementType = udtf.getElementType(typeFactory, call.getOperandList()); + elementType = udtf.getElementType(typeFactory, callBinding.operands()); } else { elementType = null; } http://git-wip-us.apache.org/repos/asf/calcite/blob/1594ed57/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 96b97a1..3c0c77c 100644 --- a/core/src/test/java/org/apache/calcite/test/JdbcTest.java +++ b/core/src/test/java/org/apache/calcite/test/JdbcTest.java @@ -179,6 +179,10 @@ public class JdbcTest { Types.lookupMethod(MazeTable.class, "generate", int.class, int.class, int.class); + public static final Method MAZE2_METHOD = + Types.lookupMethod(MazeTable.class, "generate2", int.class, int.class, + Integer.class); + public static final Method MULTIPLICATION_TABLE_METHOD = Types.lookupMethod(JdbcTest.class, "multiplicationTable", int.class, int.class, Integer.class); @@ -482,6 +486,34 @@ public class JdbcTest { assertThat(CalciteAssert.toString(resultSet), equalTo(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(MAZE2_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"; + assertThat(CalciteAssert.toString(resultSet), equalTo(result)); + + final String sql2 = "select *\n" + + "from table(\"s\".\"Maze\"(WIDTH => 5, HEIGHT => 3, SEED => 1))"; + resultSet = connection.createStatement().executeQuery(sql2); + assertThat(CalciteAssert.toString(resultSet), equalTo(result)); + + final String sql3 = "select *\n" + + "from table(\"s\".\"Maze\"(HEIGHT => 3, WIDTH => 5))"; + resultSet = connection.createStatement().executeQuery(sql3); + assertThat(CalciteAssert.toString(resultSet), equalTo(result)); + } + /** * Tests a table function that returns different row type based on * actual call arguments. @@ -724,6 +756,38 @@ public class JdbcTest { connection.close(); } + /** Tests a table macro with named and optional parameters. */ + @Test public void testTableMacroWithNamedParameters() throws Exception { + // View(String r optional, String s, int t optional) + final CalciteAssert.AssertThat with = + assertWithMacro(TableMacroFunctionWithNamedParameters.class); + with.query("select * from table(\"adhoc\".\"View\"('(5)'))") + .throws_("No match found for function signature View(<CHARACTER>)"); + final String expected1 = "c=1\n" + + "c=3\n" + + "c=5\n" + + "c=6\n"; + with.query("select * from table(\"adhoc\".\"View\"('5', '6'))") + .returns(expected1); + final String expected2 = "c=1\n" + + "c=3\n" + + "c=5\n" + + "c=6\n"; + with.query("select * from table(\"adhoc\".\"View\"(r=>'5', s=>'6'))") + .returns(expected2); + with.query("select * from table(\"adhoc\".\"View\"(t=>'5', t=>'6'))") + .throws_("Duplicate argument name 'T'"); + with.query("select * from table(\"adhoc\".\"View\"(t=>'5', s=>'6'))") + .throws_( + "No match found for function signature View(T => <CHARACTER>, S => <CHARACTER>)"); + final String expected3 = "c=1\n" + + "c=3\n" + + "c=6\n" + + "c=5\n"; + with.query("select * from table(\"adhoc\".\"View\"(t=>5, s=>'6'))") + .returns(expected3); + } + /** Tests a JDBC connection that provides a model that contains a table * macro. */ @Test public void testTableMacroInModel() throws Exception { @@ -739,7 +803,7 @@ public class JdbcTest { /** Tests a JDBC connection that provides a model that contains a table * function. */ @Test public void testTableFunctionInModel() throws Exception { - checkTableFunctionInModel(TestTableFunction.class); + checkTableFunctionInModel(MyTableFunction.class); } /** Tests a JDBC connection that provides a model that contains a table @@ -7229,6 +7293,29 @@ public class JdbcTest { } } + /** User-defined table-macro function with named and optional parameters. */ + public static class TableMacroFunctionWithNamedParameters { + public TranslatableTable eval( + @Parameter(name = "R", optional = true) String r, + @Parameter(name = "S") String s, + @Parameter(name = "T", optional = true) Integer t) { + final StringBuilder sb = new StringBuilder(); + abc(sb, r); + abc(sb, s); + abc(sb, t); + return view(sb.toString()); + } + + private void abc(StringBuilder sb, Object s) { + if (s != null) { + if (sb.length() > 0) { + sb.append(", "); + } + sb.append('(').append(s).append(')'); + } + } + } + private static QueryableTable oneThreePlus(String s) { List<Integer> items; // Argument is null in case SQL contains function call with expression. @@ -7254,7 +7341,7 @@ public class JdbcTest { } /** A table function that returns a {@link QueryableTable}. */ - public static class TestTableFunction { + public static class MyTableFunction { public QueryableTable eval(String s) { return oneThreePlus(s); } @@ -7277,6 +7364,13 @@ public class JdbcTest { return new MazeTable(); } + public static ScannableTable generate2( + @Parameter(name = "WIDTH") int width, + @Parameter(name = "HEIGHT") int height, + @Parameter(name = "SEED", optional = true) Integer seed) { + return new MazeTable(); + } + public RelDataType getRowType(RelDataTypeFactory typeFactory) { return typeFactory.builder() .add("S", SqlTypeName.VARCHAR, 12) http://git-wip-us.apache.org/repos/asf/calcite/blob/1594ed57/site/_docs/reference.md ---------------------------------------------------------------------- diff --git a/site/_docs/reference.md b/site/_docs/reference.md index bb594ac..2165410 100644 --- a/site/_docs/reference.md +++ b/site/_docs/reference.md @@ -129,7 +129,7 @@ tablePrimary: | '(' query ')' | values | UNNEST '(' expression ')' - | '(' TABLE expression ')' + | TABLE '(' [ SPECIFIC ] functionName '(' expression [, expression ]* ')' ')' values: VALUES expression [, expression ]* @@ -395,7 +395,7 @@ Not implemented: | CASE value<br/>WHEN value1 [, value11 ]* THEN result1<br/>[ WHEN valueN [, valueN1 ]* THEN resultN ]*<br/>[ ELSE resultZ ]<br/> END | Simple case | CASE<br/>WHEN condition1 THEN result1<br/>[ WHEN conditionN THEN resultN ]*<br/>[ ELSE resultZ ]<br/>END | Searched case | NULLIF(value, value) | Returns NULL if the values are the same.<br/><br/>For example, <code>NULLIF(5, 5)</code> returns NULL; <code>NULLIF(5, 0)</code> returns 5. -| COALESCE(value, value [, value]* ) | Provides a value if the first value is null.<br/><br/>For example, <code>COALESCE(NULL, 5)</code> returns 5. +| COALESCE(value, value [, value ]* ) | Provides a value if the first value is null.<br/><br/>For example, <code>COALESCE(NULL, 5)</code> returns 5. ### Type conversion @@ -409,10 +409,10 @@ Not implemented: |:--------------- |:----------- | ROW (value [, value]* ) | Creates a row from a list of values. | (value [, value]* ) | Creates a row from a list of values. -| map [ key ] | Returns the element of a map with a particular key. -| array [ index ] | Returns the element at a particular location in an array. -| ARRAY [ value [, value ]* ] | Creates an array from a list of values. -| MAP [ key, value [, key, value ]* ] | Creates a map from a list of key-value pairs. +| map '[' key ']' | Returns the element of a map with a particular key. +| array '[' index ']' | Returns the element at a particular location in an array. +| ARRAY '[' value [, value ]* ']' | Creates an array from a list of values. +| MAP '[' key, value [, key, value ]* ']' | Creates a map from a list of key-value pairs. ### Collection functions @@ -603,18 +603,21 @@ varying from convenient to efficient. To implement a *scalar function*, there are 3 options: -* Create a class with a public static `eval` method. and register the class; -* Create a class with a public non-static `eval` method. and a public - constructor with no arguments, and register the class; -* Create a class with one or more public static methods, and register - each class and method. +* Create a class with a public static `eval` method, + and register the class; +* Create a class with a public non-static `eval` method, + and a public constructor with no arguments, + and register the class; +* Create a class with one or more public static methods, + and register each class/method combination. -To implement an *aggregate function*: +To implement an *aggregate function*, there are 2 options: -* Create a class with public static `init`, `add` and `result` methods, and - register the class; -* Create a class with public non-static `init`, `add` and `result` methods, and - a public constructor with no arguments, and register the class. +* Create a class with public static `init`, `add` and `result` methods, + and register the class; +* Create a class with public non-static `init`, `add` and `result` methods, + and a public constructor with no arguments, + and register the class. Optionally, add a public `merge` method to the class; this allows Calcite to generate code that merges sub-totals. @@ -624,18 +627,35 @@ Optionally, make your class implement the interface; this allows Calcite to decompose the function across several stages of aggregation, roll up from summary tables, and push it through joins. -To implement a *table function*: +To implement a *table function*, there are 3 options: * Create a class with a static `eval` method that returns - [TranslatableTable]({{ site.apiRoot }}/org/apache/calcite/schema/TranslatableTable.html) + [ScannableTable]({{ site.apiRoot }}/org/apache/calcite/schema/ScannableTable.html) or [QueryableTable]({{ site.apiRoot }}/org/apache/calcite/schema/QueryableTable.html), and register the class; * Create a class with a non-static `eval` method that returns - [TranslatableTable]({{ site.apiRoot }}/org/apache/calcite/schema/TranslatableTable.html) + [ScannableTable]({{ site.apiRoot }}/org/apache/calcite/schema/ScannableTable.html) or [QueryableTable]({{ site.apiRoot }}/org/apache/calcite/schema/QueryableTable.html), - and register the class. + and register the class; +* Create a class with one or more public static methods that return + [ScannableTable]({{ site.apiRoot }}/org/apache/calcite/schema/ScannableTable.html) + or + [QueryableTable]({{ site.apiRoot }}/org/apache/calcite/schema/QueryableTable.html), + and register each class/method combination. + +To implement a *table macro*, there are 3 options: + +* Create a class with a static `eval` method that returns + [TranslatableTable]({{ site.apiRoot }}/org/apache/calcite/schema/TranslatableTable.html), + and register the class; +* Create a class with a non-static `eval` method that returns + [TranslatableTable]({{ site.apiRoot }}/org/apache/calcite/schema/TranslatableTable.html), + and register the class; +* Create a class with one or more public static methods that return + [TranslatableTable]({{ site.apiRoot }}/org/apache/calcite/schema/TranslatableTable.html), + and register each class/method combination. Calcite deduces the parameter types and result type of a function from the parameter and return types of the Java method that implements it. Further, you @@ -646,8 +666,12 @@ annotation. ### Calling functions with named and optional parameters Usually when you call a function, you need to specify all of its parameters, -in order. But if the function has been defined with named and optional -parameters +in order. But that can be a problem if a function has a lot of parameters, +and especially if you want to add more parameters over time. + +To solve this problem, the SQL standard allows you to pass parameters by name, +and to define parameters which are optional (that is, have a default value +that is used if they are not specified). Suppose you have a function `f`, declared as in the following pseudo syntax: @@ -658,13 +682,14 @@ Suppose you have a function `f`, declared as in the following pseudo syntax: INTEGER d DEFAULT NULL, INTEGER e DEFAULT NULL) RETURNS INTEGER``` -Note that all parameters have names, and parameters `b`, `d` and `e` +All of the function's parameters have names, and parameters `b`, `d` and `e` have a default value of `NULL` and are therefore optional. (In Calcite, `NULL` is the only allowable default value for optional parameters; this may change [in future](https://issues.apache.org/jira/browse/CALCITE-947).) -You can omit optional arguments at the end of the list, or use the `DEFAULT` +When calling a function with optional parameters, +you can omit optional arguments at the end of the list, or use the `DEFAULT` keyword for any optional arguments. Here are some examples:
