[CALCITE-2072] Enable spatial functions by adding 'fun=spatial' to JDBC connect string
Project: http://git-wip-us.apache.org/repos/asf/calcite/repo Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/c1749ade Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/c1749ade Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/c1749ade Branch: refs/heads/master Commit: c1749ade6638cd2e724f33a09f12f45d8d2a5503 Parents: 26b4712 Author: Julian Hyde <[email protected]> Authored: Fri Dec 8 16:10:55 2017 -0800 Committer: Julian Hyde <[email protected]> Committed: Mon Dec 11 14:24:53 2017 -0800 ---------------------------------------------------------------------- .../config/CalciteConnectionConfigImpl.java | 25 +++++--- .../config/CalciteConnectionProperty.java | 3 +- .../calcite/prepare/CalciteCatalogReader.java | 63 +++++++++++++++++--- .../sql/validate/SqlUserDefinedAggFunction.java | 5 ++ .../java/org/apache/calcite/test/JdbcTest.java | 18 ++++++ site/_docs/adapter.md | 2 +- site/_docs/howto.md | 15 +++++ site/_docs/spatial.md | 18 ++++++ 8 files changed, 131 insertions(+), 18 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/calcite/blob/c1749ade/core/src/main/java/org/apache/calcite/config/CalciteConnectionConfigImpl.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/config/CalciteConnectionConfigImpl.java b/core/src/main/java/org/apache/calcite/config/CalciteConnectionConfigImpl.java index c2a3239..794d1c8 100644 --- a/core/src/main/java/org/apache/calcite/config/CalciteConnectionConfigImpl.java +++ b/core/src/main/java/org/apache/calcite/config/CalciteConnectionConfigImpl.java @@ -20,6 +20,8 @@ import org.apache.calcite.avatica.ConnectionConfigImpl; import org.apache.calcite.avatica.util.Casing; import org.apache.calcite.avatica.util.Quoting; import org.apache.calcite.model.JsonSchema; +import org.apache.calcite.prepare.CalciteCatalogReader; +import org.apache.calcite.runtime.GeoFunctions; import org.apache.calcite.sql.SqlOperatorTable; import org.apache.calcite.sql.fun.OracleSqlOperatorTable; import org.apache.calcite.sql.fun.SqlStdOperatorTable; @@ -27,8 +29,8 @@ import org.apache.calcite.sql.util.ChainedSqlOperatorTable; import org.apache.calcite.sql.validate.SqlConformance; import org.apache.calcite.sql.validate.SqlConformanceEnum; -import java.util.ArrayList; -import java.util.List; +import java.util.Collection; +import java.util.LinkedHashSet; import java.util.Properties; /** Implementation of {@link CalciteConnectionConfig}. */ @@ -86,22 +88,29 @@ public class CalciteConnectionConfigImpl extends ConnectionConfigImpl if (fun == null || fun.equals("") || fun.equals("standard")) { return defaultOperatorTable; } - final List<SqlOperatorTable> tables = new ArrayList<>(); + final Collection<SqlOperatorTable> tables = new LinkedHashSet<>(); for (String s : fun.split(",")) { - tables.add(operatorTable(s)); + operatorTable(s, tables); } + tables.add(SqlStdOperatorTable.instance()); return operatorTableClass.cast( ChainedSqlOperatorTable.of( tables.toArray(new SqlOperatorTable[tables.size()]))); } - private static SqlOperatorTable operatorTable(String s) { + private static void operatorTable(String s, + Collection<SqlOperatorTable> tables) { switch (s) { case "standard": - return SqlStdOperatorTable.instance(); + tables.add(SqlStdOperatorTable.instance()); + return; case "oracle": - return ChainedSqlOperatorTable.of(OracleSqlOperatorTable.instance(), - SqlStdOperatorTable.instance()); + tables.add(OracleSqlOperatorTable.instance()); + return; + case "spatial": + tables.add( + CalciteCatalogReader.operatorTable(GeoFunctions.class.getName())); + return; default: throw new IllegalArgumentException("Unknown operator table: " + s); } http://git-wip-us.apache.org/repos/asf/calcite/blob/c1749ade/core/src/main/java/org/apache/calcite/config/CalciteConnectionProperty.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/config/CalciteConnectionProperty.java b/core/src/main/java/org/apache/calcite/config/CalciteConnectionProperty.java index b576aac..67909da 100644 --- a/core/src/main/java/org/apache/calcite/config/CalciteConnectionProperty.java +++ b/core/src/main/java/org/apache/calcite/config/CalciteConnectionProperty.java @@ -74,7 +74,8 @@ public enum CalciteConnectionProperty implements ConnectionProperty { LEX("lex", Type.ENUM, Lex.ORACLE, false), /** Collection of built-in functions and operators. Valid values include - * "standard" and "oracle". */ + * "standard", "oracle" and "spatial", and also comma-separated lists, for + * example "oracle,spatial". */ FUN("fun", Type.STRING, "standard", true), /** How identifiers are quoted. http://git-wip-us.apache.org/repos/asf/calcite/blob/c1749ade/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 af0d3a6..4dc8be7 100644 --- a/core/src/main/java/org/apache/calcite/prepare/CalciteCatalogReader.java +++ b/core/src/main/java/org/apache/calcite/prepare/CalciteCatalogReader.java @@ -19,11 +19,13 @@ package org.apache.calcite.prepare; import org.apache.calcite.config.CalciteConnectionConfig; import org.apache.calcite.jdbc.CalciteSchema; import org.apache.calcite.jdbc.JavaTypeFactoryImpl; +import org.apache.calcite.model.ModelHandler; import org.apache.calcite.plan.RelOptPlanner; import org.apache.calcite.rel.type.RelDataType; import org.apache.calcite.rel.type.RelDataTypeFactory; import org.apache.calcite.rel.type.RelDataTypeFactoryImpl; import org.apache.calcite.rel.type.RelDataTypeField; +import org.apache.calcite.rel.type.RelDataTypeSystem; import org.apache.calcite.runtime.PredicateImpl; import org.apache.calcite.schema.AggregateFunction; import org.apache.calcite.schema.Function; @@ -38,14 +40,18 @@ import org.apache.calcite.sql.SqlFunctionCategory; import org.apache.calcite.sql.SqlIdentifier; import org.apache.calcite.sql.SqlOperator; import org.apache.calcite.sql.SqlOperatorBinding; +import org.apache.calcite.sql.SqlOperatorTable; import org.apache.calcite.sql.SqlSyntax; +import org.apache.calcite.sql.parser.SqlParserPos; 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; import org.apache.calcite.sql.type.SqlReturnTypeInference; +import org.apache.calcite.sql.type.SqlTypeFactoryImpl; import org.apache.calcite.sql.type.SqlTypeFamily; import org.apache.calcite.sql.type.SqlTypeName; +import org.apache.calcite.sql.util.ListSqlOperatorTable; import org.apache.calcite.sql.validate.SqlMoniker; import org.apache.calcite.sql.validate.SqlMonikerImpl; import org.apache.calcite.sql.validate.SqlMonikerType; @@ -275,7 +281,43 @@ public class CalciteCatalogReader implements Prepare.CatalogReader { })); } + /** Creates an operator table that contains functions in the given class. + * + * @see ModelHandler#addFunctions */ + public static SqlOperatorTable operatorTable(String className) { + // Dummy schema to collect the functions + final CalciteSchema schema = + CalciteSchema.createRootSchema(false, false); + ModelHandler.addFunctions(schema.plus(), null, ImmutableList.<String>of(), + className, "*", true); + + // The following is technical debt; see [CALCITE-2082] Remove + // RelDataTypeFactory argument from SqlUserDefinedAggFunction constructor + final SqlTypeFactoryImpl typeFactory = + new SqlTypeFactoryImpl(RelDataTypeSystem.DEFAULT); + + final ListSqlOperatorTable table = new ListSqlOperatorTable(); + for (String name : schema.getFunctionNames()) { + for (Function function : schema.getFunctions(name, true)) { + final SqlIdentifier id = new SqlIdentifier(name, SqlParserPos.ZERO); + table.add( + toOp(typeFactory, id, function)); + } + } + return table; + } + private SqlOperator toOp(SqlIdentifier name, final Function function) { + return toOp(typeFactory, name, function); + } + + /** Converts a function to a {@link org.apache.calcite.sql.SqlOperator}. + * + * <p>The {@code typeFactory} argument is technical debt; see [CALCITE-2082] + * Remove RelDataTypeFactory argument from SqlUserDefinedAggFunction + * constructor. */ + private static SqlOperator toOp(RelDataTypeFactory typeFactory, + SqlIdentifier name, final Function function) { List<RelDataType> argTypes = new ArrayList<>(); List<SqlTypeFamily> typeFamilies = new ArrayList<>(); for (FunctionParameter o : function.getParameters()) { @@ -292,7 +334,7 @@ public class CalciteCatalogReader implements Prepare.CatalogReader { }; final FamilyOperandTypeChecker typeChecker = OperandTypes.family(typeFamilies, optional); - final List<RelDataType> paramTypes = toSql(argTypes); + final List<RelDataType> paramTypes = toSql(typeFactory, argTypes); if (function instanceof ScalarFunction) { return new SqlUserDefinedFunction(name, infer((ScalarFunction) function), InferTypes.explicit(argTypes), typeChecker, paramTypes, function); @@ -313,9 +355,10 @@ public class CalciteCatalogReader implements Prepare.CatalogReader { } } - private SqlReturnTypeInference infer(final ScalarFunction function) { + private static SqlReturnTypeInference infer(final ScalarFunction function) { return new SqlReturnTypeInference() { public RelDataType inferReturnType(SqlOperatorBinding opBinding) { + final RelDataTypeFactory typeFactory = opBinding.getTypeFactory(); final RelDataType type; if (function instanceof ScalarFunctionImpl) { type = ((ScalarFunctionImpl) function).getReturnType(typeFactory, @@ -323,30 +366,34 @@ public class CalciteCatalogReader implements Prepare.CatalogReader { } else { type = function.getReturnType(typeFactory); } - return toSql(type); + return toSql(typeFactory, type); } }; } - private SqlReturnTypeInference infer(final AggregateFunction function) { + private static SqlReturnTypeInference infer( + final AggregateFunction function) { return new SqlReturnTypeInference() { public RelDataType inferReturnType(SqlOperatorBinding opBinding) { + final RelDataTypeFactory typeFactory = opBinding.getTypeFactory(); final RelDataType type = function.getReturnType(typeFactory); - return toSql(type); + return toSql(typeFactory, type); } }; } - private List<RelDataType> toSql(List<RelDataType> types) { + private static List<RelDataType> toSql( + final RelDataTypeFactory typeFactory, List<RelDataType> types) { return Lists.transform(types, new com.google.common.base.Function<RelDataType, RelDataType>() { public RelDataType apply(RelDataType type) { - return toSql(type); + return toSql(typeFactory, type); } }); } - private RelDataType toSql(RelDataType type) { + private static RelDataType toSql(RelDataTypeFactory typeFactory, + RelDataType type) { if (type instanceof RelDataTypeFactoryImpl.JavaType && ((RelDataTypeFactoryImpl.JavaType) type).getJavaClass() == Object.class) { http://git-wip-us.apache.org/repos/asf/calcite/blob/c1749ade/core/src/main/java/org/apache/calcite/sql/validate/SqlUserDefinedAggFunction.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/validate/SqlUserDefinedAggFunction.java b/core/src/main/java/org/apache/calcite/sql/validate/SqlUserDefinedAggFunction.java index a8be50b..68c954f 100644 --- a/core/src/main/java/org/apache/calcite/sql/validate/SqlUserDefinedAggFunction.java +++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlUserDefinedAggFunction.java @@ -17,6 +17,7 @@ package org.apache.calcite.sql.validate; import org.apache.calcite.jdbc.JavaTypeFactoryImpl; +import org.apache.calcite.linq4j.function.Experimental; import org.apache.calcite.rel.type.RelDataType; import org.apache.calcite.rel.type.RelDataTypeFactory; import org.apache.calcite.rel.type.RelDataTypeFactoryImpl; @@ -46,6 +47,10 @@ import java.util.List; */ public class SqlUserDefinedAggFunction extends SqlAggFunction { public final AggregateFunction function; + + /** This field is is technical debt; see [CALCITE-2082] Remove + * RelDataTypeFactory argument from SqlUserDefinedAggFunction constructor. */ + @Experimental public final RelDataTypeFactory typeFactory; /** Creates a SqlUserDefinedAggFunction. */ http://git-wip-us.apache.org/repos/asf/calcite/blob/c1749ade/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 cdebd47..2fd5d0a 100644 --- a/core/src/test/java/org/apache/calcite/test/JdbcTest.java +++ b/core/src/test/java/org/apache/calcite/test/JdbcTest.java @@ -6157,6 +6157,24 @@ public class JdbcTest { .throws_("No match found for function signature NVL(<NUMERIC>, <NUMERIC>)"); } + /** Test case for + * <a href="https://issues.apache.org/jira/browse/CALCITE-2072";>[CALCITE-2072] + * Enable spatial operator table by adding 'fun=spatial'to JDBC URL</a>. */ + @Test public void testFunSpatial() { + final String sql = "select distinct\n" + + " ST_PointFromText('POINT(-71.0642.28)') as c\n" + + "from \"hr\".\"emps\""; + CalciteAssert.that(CalciteAssert.Config.REGULAR) + .with("fun", "spatial") + .query(sql) + .returnsUnordered("C={\"x\":-71.0642,\"y\":0.28}"); + + // NVL is present in the Oracle operator table, but not spatial or core + CalciteAssert.that(CalciteAssert.Config.REGULAR) + .query("select nvl(\"commission\", -99) as c from \"hr\".\"emps\"") + .throws_("No match found for function signature NVL(<NUMERIC>, <NUMERIC>)"); + } + /** Tests that {@link Hook#PARSE_TREE} works. */ @Test public void testHook() { final int[] callCount = {0}; http://git-wip-us.apache.org/repos/asf/calcite/blob/c1749ade/site/_docs/adapter.md ---------------------------------------------------------------------- diff --git a/site/_docs/adapter.md b/site/_docs/adapter.md index 5562c4c..35001ed 100644 --- a/site/_docs/adapter.md +++ b/site/_docs/adapter.md @@ -88,7 +88,7 @@ as implemented by Avatica's | <a href="{{ site.apiRoot }}/org/apache/calcite/config/CalciteConnectionProperty.html#DEFAULT_NULL_COLLATION">defaultNullCollation</a> | How NULL values should be sorted if neither NULLS FIRST nor NULLS LAST are specified in a query. The default, HIGH, sorts NULL values the same as Oracle. | <a href="{{ site.apiRoot }}/org/apache/calcite/config/CalciteConnectionProperty.html#DRUID_FETCH">druidFetch</a> | How many rows the Druid adapter should fetch at a time when executing SELECT queries. | <a href="{{ site.apiRoot }}/org/apache/calcite/config/CalciteConnectionProperty.html#FORCE_DECORRELATE">forceDecorrelate</a> | Whether the planner should try de-correlating as much as possible. Default true. -| <a href="{{ site.apiRoot }}/org/apache/calcite/config/CalciteConnectionProperty.html#FUN">fun</a> | Collection of built-in functions and operators. Valid values: "standard" (the default), "oracle". +| <a href="{{ site.apiRoot }}/org/apache/calcite/config/CalciteConnectionProperty.html#FUN">fun</a> | Collection of built-in functions and operators. Valid values are "standard" (the default), "oracle", "spatial", and may be combined using commas, for example "oracle,spatial". | <a href="{{ site.apiRoot }}/org/apache/calcite/config/CalciteConnectionProperty.html#LEX">lex</a> | Lexical policy. Values are ORACLE (default), MYSQL, MYSQL_ANSI, SQL_SERVER, JAVA. | <a href="{{ site.apiRoot }}/org/apache/calcite/config/CalciteConnectionProperty.html#MATERIALIZATIONS_ENABLED">materializationsEnabled</a> | Whether Calcite should use materializations. Default false. | <a href="{{ site.apiRoot }}/org/apache/calcite/config/CalciteConnectionProperty.html#MODEL">model</a> | URI of the JSON model file. http://git-wip-us.apache.org/repos/asf/calcite/blob/c1749ade/site/_docs/howto.md ---------------------------------------------------------------------- diff --git a/site/_docs/howto.md b/site/_docs/howto.md index 742a83f..437f64d 100644 --- a/site/_docs/howto.md +++ b/site/_docs/howto.md @@ -464,6 +464,21 @@ Before you start: a fix version assigned (most likely the version we are just about to release) +Smoke-test `sqlline` with Spatial and Oracle function tables: + +{% highlight sql %} +$ ./sqlline +> !connect jdbc:calcite:fun=spatial,oracle "sa" "" +SELECT NVL(ST_Is3D(ST_PointFromText('POINT(-71.064544 42.28787)')), TRUE); ++--------+ +| EXPR$0 | ++--------+ +| false | ++--------+ +1 row selected (0.039 seconds) +> !quit +{% endhighlight %} + Create a release branch named after the release, e.g. `branch-1.1`, and push it to Apache. {% highlight bash %} http://git-wip-us.apache.org/repos/asf/calcite/blob/c1749ade/site/_docs/spatial.md ---------------------------------------------------------------------- diff --git a/site/_docs/spatial.md b/site/_docs/spatial.md index 3b98464..1521ce1 100644 --- a/site/_docs/spatial.md +++ b/site/_docs/spatial.md @@ -51,6 +51,24 @@ Calcite's support for spatial data includes: and will at some point also include query rewrites to use spatial indexes. +## Enabling spatial support + +Though the `GEOMETRY` data type is built-in, the functions are not enabled by +default. You need to add `fun=spatial` to the JDBC connect string to enable +the functions. For example, `sqlline`: + +{% highlight sql %} +$ ./sqlline +> !connect jdbc:calcite:fun=spatial "sa" "" +SELECT ST_PointFromText('POINT(-71.064544 42.28787)'); ++-------------------------------+ +| EXPR$0 | ++-------------------------------+ +| {"x":-71.064544,"y":42.28787} | ++-------------------------------+ +1 row selected (0.323 seconds) +{% endhighlight %} + ## Acknowledgements Calcite's OpenGIS implementation uses the
