[CALCITE-4662] geometry type support problem

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 1.27.0
• Component/s: jdbc-adapter
• Assignee: Unassigned
• Reporter: TaoJIn
• Created: 23/Jun/21 02:27
• Updated: 23/Jun/21 02:27

Description

calcite jdbc schema cann't recognize postgis GEOMETRY type id(1111) and convert it to ANY type.It made the planner add cast to GEOMETRY type field.

table in postgresql with postgis:

\d cities
                       Table "public.cities"
  Column  |         Type          | Collation | Nullable | Default
----------+-----------------------+-----------+----------+---------
 id       | integer               |           |          |
 name     | character varying(50) |           |          |
 the_geom | geometry(Point,4326)  |           |          |
 x        | double precision      |           |          |
 y        | double precision      |           |          |

query:

select ST_POINT(ST_x(the_geom),ST_Y(the_geom)),ST_MAKELINE(the_geom,the_geom),ST_AsWKT(the_geom),ST_AsText(\"the_geom\"),ST_x(the_geom),ST_y(the_geom) from pg.cities t

logical plan in 1.26.0:

LogicalProject(EXPR$0=[ST_POINT(ST_X($2), ST_Y($2))], EXPR$1=[ST_MAKELINE($2, $2)], EXPR$2=[ST_ASWKT($2)], EXPR$3=[ST_ASTEXT($2)], EXPR$4=[ST_X($2)], EXPR$5=[ST_Y($2)])
  JdbcTableScan(table=[[pg, cities]])

logical plan in 1.27.0

LogicalProject(EXPR$0=[ST_POINT(ST_X(CAST($2):JavaType(interface org.apache.calcite.runtime.Geometries$Geom)), ST_Y(CAST($2):JavaType(interface org.apache.calcite.runtime.Geometries$Geom)))], EXPR$1=[ST_MAKELINE(CAST($2):JavaType(interface org.apache.calcite.runtime.Geometries$Geom), CAST($2):JavaType(interface org.apache.calcite.runtime.Geometries$Geom))], EXPR$2=[ST_ASWKT(CAST($2):JavaType(interface org.apache.calcite.runtime.Geometries$Geom))], EXPR$3=[ST_ASTEXT(CAST($2):JavaType(interface org.apache.calcite.runtime.Geometries$Geom))], EXPR$4=[ST_X(CAST($2):JavaType(interface org.apache.calcite.runtime.Geometries$Geom))], EXPR$5=[ST_Y(CAST($2):JavaType(interface org.apache.calcite.runtime.Geometries$Geom))])
  JdbcTableScan(table=[[pg, cities]])

rel2sql couldn't convert logical plan in 1.27 to sql for:

java.lang.UnsupportedOperationException: Unsupported type when convertTypeToSpec: GEOMETRY
    at org.apache.calcite.sql.type.SqlTypeUtil.convertTypeToSpec(SqlTypeUtil.java:1065)
    at org.apache.calcite.sql.type.SqlTypeUtil.convertTypeToSpec(SqlTypeUtil.java:1087)
    at org.apache.calcite.sql.SqlDialect.getCastSpec(SqlDialect.java:800)
    at org.apache.calcite.sql.dialect.PostgresqlSqlDialect.getCastSpec(PostgresqlSqlDialect.java:92)
    at org.apache.calcite.rel.rel2sql.SqlImplementor$Context.callToSql(SqlImplementor.java:772)

From postgresql jdbc driver,the datatype id of GEOMETRY is 1111, the id didn't match any type in SqlTypeName.
I think this problem is not only a bug.For the spatial type ,different database has different implements and definitions.It's difficult to design a general converter to match all type ids.

In my opinion calcite maybe add a hook there ,then users can custom their data type converter.