[jira] [Commented] (CALCITE-5281) Implement geometry set returning functions (SRF)

["Bertil Chapuis (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-5281?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17643580#comment-17643580 ] 

Bertil Chapuis commented on CALCITE-5281:
-----------------------------------------

After digging (a lot ;)), I wonder if the cursor is the only means to achieve the desired result. Here is a clearer description of the problem.

I would like to execute a query similar to the example provided by [~julianhyde] earlier:{{{}{}}}
{code:java}
SELECT * FROM ST_Explode(SELECT * FROM test_point WHERE ST_Dimension(THE_GEOM)=0);{code}
 

The following query, present in the {{spatial.iq}} tests, produces something very close to the desired result:

 
{code:java}
SELECT t1.id, t2.geom, t2.index
FROM (SELECT 1 as id, 'MULTIPOINT((1 1), (2 2))' as points UNION ALL SELECT 2 as id, 'MULTIPOINT((3 3), (4 4))' as points) as t1,
LATERAL table(ST_Explode(t1.points)) as t2(geom, index);{code}
However, I would prefer to write something closer to the original example, such as the query described in the following test case (not committed):

 

 
{code:java}
SELECT *
FROM table(ST_Explode(
    SELECT ST_GeomFromText('MULTIPOINT((1 1), (2 2))') as points
    UNION ALL
    SELECT ST_GeomFromText('MULTIPOINT((3 3), (4 4))') as points
));
GEOM, INDEX
POINT (1 1), 0
POINT (2 2), 1
POINT (3 3), 0
POINT (4 4), 1
!ok{code}
 

Unfortunately, this test case produces the following error:
{{}}
{code:java}
> java.sql.SQLException: Error while executing SQL "SELECT *
> FROM table(ST_Explode(
>     SELECT ST_GeomFromText('MULTIPOINT((1 1), (2 2))') as points
>     UNION ALL
>     SELECT ST_GeomFromText('MULTIPOINT((3 3), (4 4))') as points
> ))": Unable to implement EnumerableTableFunctionScan(invocation=[ST_EXPLODE($SCALAR_QUERY({
> LogicalUnion(all=[true])
>   LogicalProject(POINTS=[ST_GEOMFROMTEXT('MULTIPOINT((1 1), (2 2))')])
>     LogicalValues(tuples=[[{ 0 }]])
>   LogicalProject(POINTS=[ST_GEOMFROMTEXT('MULTIPOINT((3 3), (4 4))')])
>     LogicalValues(tuples=[[{ 0 }]])
> }))], rowType=[RecordType(GEOMETRY GEOM, INTEGER INDEX)], elementType=[class [Ljava.lang.Object;]): rowcount = 1.0, cumulative cost = {1.0 rows, 1.0 cpu, 0.0 io}, id = 2628{code}
In my understanding of the exception, the query is successfully parsed but it fails during the execution. To address this issue, the ST_Explode function should accept a scalar query as an input. As the cursor feature is currently disabled by calcite, are there other means to achieve this?

> Implement geometry set returning functions (SRF)
> ------------------------------------------------
>
>                 Key: CALCITE-5281
>                 URL: https://issues.apache.org/jira/browse/CALCITE-5281
>             Project: Calcite
>          Issue Type: Improvement
>          Components: spatial
>            Reporter: Bertil Chapuis
>            Assignee: Bertil Chapuis
>            Priority: Minor
>              Labels: pull-request-available
>          Time Spent: 0.5h
>  Remaining Estimate: 0h
>
> The idea is to implement set returning functions, such as ST_Explode in H2GIS and ST_Dump in PostGIS. Is the Uncollect class a good starting point?



--
This message was sent by Atlassian Jira
(v8.20.10#820010)