hi folks,
I'm using calcite with postgis, and found the support of geometry type
is not very good.
Firstly, geometry was not added to enum "SqlTypeName" as a jdbc type,
geometry type read from postgresql jdbc will convert to ANY type.
Since 1.27.0, the hepPlanner will add a cast-to-geometry function to the
ANY type as below:
table:
\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 of 1.26:
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 of 1.27:
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 of 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)
I think this problem is not only a bug.For the spatial type ,different
database has different implements and definitions.It's hard to write a
general solution.In my opinion it could add a hook to support a specific
spatial database type convert?
Many thanks for your work,looking forward for your reply.
Thanks again!
Tony Tao
