You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by PunxsutawneyPhil3 <rw...@niksun.com> on 2021/04/14 23:33:01 UTC
Ignite unable to understand recursive query.
I am having a problem with the following query which results in this error:
javax.cache.CacheException: Failed to parse query. Column "S.TAGID" not
found; SQL statement:
From reading it looks like the reason for this is explained in this
<https://apacheignite-sql.readme.io/docs/distributed-joins> documentation,
and is that Ignite cannot resolve the columns of reportIdList after the
Inner Join.
Is there any way to restructure this query so it can be understood by
Ignite?
WITH RECURSIVE reportIdList AS
(
SELECT reportId, tagId, owner
FROM "MyReportPojoCache".MyReportPojo
WHERE id = ANY (SELECT id
FROM "MyOtherReportPojoCache".MyOtherReportPojo
WHERE owner IS NOT NULL
AND isManage IS TRUE
AND type = 'tag-group')
UNION
SELECT m.reportId, m.tagId, m.owner
FROM "MyReportPojoCache".MyReportPojo m
INNER JOIN reportIdList s ON s.tagId = m.reportId
AND s.owner IS NOT NULL
AND s.owner != 'admin'
AND s.owner = m.owner
)
SELECT qpIntId
FROM "MyReportPojoCache".MyReportPojo
WHERE (report_id, owner) IN (SELECT report_id, owner FROM reportIdList)
link StackOverflow post
https://stackoverflow.com/questions/67098705/how-to-rewrite-recursive-sql-query-to-work-with-ignite-sql-queries
<https://stackoverflow.com/questions/67098705/how-to-rewrite-recursive-sql-query-to-work-with-ignite-sql-queries>
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: Ignite unable to understand recursive query.
Posted by Ilya Kasnacheev <il...@gmail.com>.
Hello!
As far as my understanding goes, Ignite does not support recursive CTE.
You may still try running it as a local query by setting local=true in your
connection/query settings.
Regards,
--
Ilya Kasnacheev
чт, 15 апр. 2021 г. в 02:33, PunxsutawneyPhil3 <rw...@niksun.com>:
> I am having a problem with the following query which results in this error:
>
> javax.cache.CacheException: Failed to parse query. Column "S.TAGID" not
> found; SQL statement:
>
> From reading it looks like the reason for this is explained in this
> <https://apacheignite-sql.readme.io/docs/distributed-joins>
> documentation,
> and is that Ignite cannot resolve the columns of reportIdList after the
> Inner Join.
>
> Is there any way to restructure this query so it can be understood by
> Ignite?
>
>
>
> WITH RECURSIVE reportIdList AS
> (
> SELECT reportId, tagId, owner
> FROM "MyReportPojoCache".MyReportPojo
> WHERE id = ANY (SELECT id
> FROM "MyOtherReportPojoCache".MyOtherReportPojo
> WHERE owner IS NOT NULL
> AND isManage IS TRUE
> AND type = 'tag-group')
> UNION
> SELECT m.reportId, m.tagId, m.owner
> FROM "MyReportPojoCache".MyReportPojo m
> INNER JOIN reportIdList s ON s.tagId = m.reportId
> AND s.owner IS NOT NULL
> AND s.owner != 'admin'
> AND s.owner = m.owner
> )
> SELECT qpIntId
> FROM "MyReportPojoCache".MyReportPojo
> WHERE (report_id, owner) IN (SELECT report_id, owner FROM reportIdList)
>
>
> link StackOverflow post
>
> https://stackoverflow.com/questions/67098705/how-to-rewrite-recursive-sql-query-to-work-with-ignite-sql-queries
> <
> https://stackoverflow.com/questions/67098705/how-to-rewrite-recursive-sql-query-to-work-with-ignite-sql-queries>
>
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>