TaoJIn created CALCITE-4662:
-------------------------------
Summary: geometry type support problem
Key: CALCITE-4662
URL: https://issues.apache.org/jira/browse/CALCITE-4662
Project: Calcite
Issue Type: Bug
Components: jdbc-adapter
Affects Versions: 1.27.0
Reporter: TaoJIn
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:
{code:sql}
\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 | | |
{code}
query:
{code:sql}
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
{code}
logical plan in 1.26.0:
{code}
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]])
{code}
logical plan in 1.27.0
{code}
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]])
{code}
rel2sql couldn't convert logical plan in 1.27 to sql for:
{code:java}
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)
{code}
>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.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
