how to enable spatial when sql validate

[tonytao <to...@outlook.com>]

hi ,

I'm trying to parse sql with org.apache.calcite.tools.Planner, but when 
validate,it throw exception:

Exception in thread "main" org.apache.calcite.tools.ValidationException: 
org.apache.calcite.runtime.CalciteContextException: From line 1, column 
8 to line 1, column 30: No match found for function signature 
ST_MAKEPOINT(<NUMERIC>, <NUMERIC>)
     at 
org.apache.calcite.prepare.PlannerImpl.validate(PlannerImpl.java:217)
     at mtest.App8.main(App8.java:58)


I set "fun=spatial" in calcite connection and conformance to "LENIENT"(I 
noticed it allowed geometry),but they didn't work.Could you please let 
me know how to enable spatial when sql validated.

Here is my code:

         Class.forName("org.apache.calcite.jdbc.Driver");
         Properties info = new Properties();
         info.setProperty("caseSensitive", "false");
         info.setProperty("conformance", "LENIENT");
         Connection connection = 
DriverManager.getConnection("jdbc:calcite:fun=spatial", info);
         CalciteConnection calciteConnection = 
connection.unwrap(CalciteConnection.class);

         SchemaPlus rootSchema = calciteConnection.getRootSchema();

         String url = "jdbc:postgresql://127.0.0.1:5432/hdb";
DriverManager.registerDriver(DriverManager.getDriver(url));
         Class.forName("org.postgresql.Driver");
         BasicDataSource dataSource = new BasicDataSource();
         dataSource.setUrl(url);
         dataSource.setUsername("dev");
         dataSource.setPassword("123456");
         Schema schema = JdbcSchema.create(rootSchema, "test", 
dataSource, null, "public");
         rootSchema.add("test", schema);
         SqlParser.Config insensitiveParser = 
SqlParser.config().withCaseSensitive(false)
                 .withConformance(SqlConformanceEnum.LENIENT);
         FrameworkConfig config = 
Frameworks.newConfigBuilder().parserConfig(insensitiveParser).defaultSchema(rootSchema)
                 .build();
         Planner planner = Frameworks.getPlanner(config);
         String sql = "select ST_MakePoint(10.1,11.2)  from test.cities 
t  ";
         SqlNode sqlNode = planner.parse(sql);
         SqlNode sqlNodeValidated = planner.validate(sqlNode);

Re: how to enable spatial when sql validate

[Julian Hyde <jh...@apache.org>]

You have created a connection, and used the root schema from it, but
then you create a parser and planner outside of the connection, and
therefore the connection's settings are not used during
validation/planning.

You're on the right track with "fun=spatial" but you need to get that
config into the planner somehow.

Julian

On Fri, Apr 2, 2021 at 8:39 AM tonytao <to...@outlook.com> wrote:
>
> hi ,
>
> I'm trying to parse sql with org.apache.calcite.tools.Planner, but when
> validate,it throw exception:
>
> Exception in thread "main" org.apache.calcite.tools.ValidationException:
> org.apache.calcite.runtime.CalciteContextException: From line 1, column
> 8 to line 1, column 30: No match found for function signature
> ST_MAKEPOINT(<NUMERIC>, <NUMERIC>)
>      at
> org.apache.calcite.prepare.PlannerImpl.validate(PlannerImpl.java:217)
>      at mtest.App8.main(App8.java:58)
>
>
> I set "fun=spatial" in calcite connection and conformance to "LENIENT"(I
> noticed it allowed geometry),but they didn't work.Could you please let
> me know how to enable spatial when sql validated.
>
> Here is my code:
>
>          Class.forName("org.apache.calcite.jdbc.Driver");
>          Properties info = new Properties();
>          info.setProperty("caseSensitive", "false");
>          info.setProperty("conformance", "LENIENT");
>          Connection connection =
> DriverManager.getConnection("jdbc:calcite:fun=spatial", info);
>          CalciteConnection calciteConnection =
> connection.unwrap(CalciteConnection.class);
>
>          SchemaPlus rootSchema = calciteConnection.getRootSchema();
>
>          String url = "jdbc:postgresql://127.0.0.1:5432/hdb";
> DriverManager.registerDriver(DriverManager.getDriver(url));
>          Class.forName("org.postgresql.Driver");
>          BasicDataSource dataSource = new BasicDataSource();
>          dataSource.setUrl(url);
>          dataSource.setUsername("dev");
>          dataSource.setPassword("123456");
>          Schema schema = JdbcSchema.create(rootSchema, "test",
> dataSource, null, "public");
>          rootSchema.add("test", schema);
>          SqlParser.Config insensitiveParser =
> SqlParser.config().withCaseSensitive(false)
>                  .withConformance(SqlConformanceEnum.LENIENT);
>          FrameworkConfig config =
> Frameworks.newConfigBuilder().parserConfig(insensitiveParser).defaultSchema(rootSchema)
>                  .build();
>          Planner planner = Frameworks.getPlanner(config);
>          String sql = "select ST_MakePoint(10.1,11.2)  from test.cities
> t  ";
>          SqlNode sqlNode = planner.parse(sql);
>          SqlNode sqlNodeValidated = planner.validate(sqlNode);
>
>