You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by 贺波 <he...@163.com> on 2017/09/22 09:36:47 UTC
An issue of Ignite In-Menory Sql Grid since version 2.0.0
Hi,I used Apache Ignite in my project for more than a year,from version 1.8.0 to 2.2.0.I use Ignite In-Menory Sql Grid in my project.I use “with as” sql function in my sql,it executes correctly in version 1.9.0,but executes error since version 2.0.0.My sql statement is:
with RECURSIVE children(typeId, pTypeId) AS (
SELECT typeId, pTypeId FROM ProcessDefTypePo WHERE pTypeId = '1'
UNION ALL
SELECT ProcessDefTypePo.typeId, ProcessDefTypePo.pTypeId FROM children INNER JOIN ProcessDefTypePo ON children.typeId =ProcessDefTypePo.pTypeId
)
select t1.typeId,t1.pTypeId,t1.typeName,t1.description, t2.typeName as pTypeName from ProcessDefTypePo t1 left join ProcessDefTypePo t2 on t1.pTypeId=t2.typeId where t1.typeId not in ( select typeId from children )
The execution error in version 2.2.0 is:
Caused by: class org.apache.ignite.IgniteCheckedException: Unknown query type: null
at org.apache.ignite.internal.processors.query.GridQueryProcessor.executeQuery(GridQueryProcessor.java:2316)
at org.apache.ignite.internal.processors.query.GridQueryProcessor.querySqlFields(GridQueryProcessor.java:1820)
... 165 more
Caused by: java.lang.UnsupportedOperationException: Unknown query type: null
at org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseQuery(GridSqlQueryParser.java:1225)
at org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseTable(GridSqlQueryParser.java:501)
at org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseTableFilter(GridSqlQueryParser.java:465)
at org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseSelect(GridSqlQueryParser.java:565)
at org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseQuery(GridSqlQueryParser.java:1220)
at org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseQueryExpression(GridSqlQueryParser.java:452)
at org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseExpression0(GridSqlQueryParser.java:1436)
at org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseExpression(GridSqlQueryParser.java:1267)
at org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseExpression0(GridSqlQueryParser.java:1378)
at org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseExpression(GridSqlQueryParser.java:1267)
at org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseSelect(GridSqlQueryParser.java:536)
at org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseQuery(GridSqlQueryParser.java:1220)
at org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parse(GridSqlQueryParser.java:1181)
at org.apache.ignite.internal.processors.query.h2.sql.GridSqlQuerySplitter.parse(GridSqlQuerySplitter.java:1604)
at org.apache.ignite.internal.processors.query.h2.sql.GridSqlQuerySplitter.split(GridSqlQuerySplitter.java:197)
at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.queryDistributedSqlFields(IgniteH2Indexing.java:1307)
at org.apache.ignite.internal.processors.query.GridQueryProcessor$5.applyx(GridQueryProcessor.java:1815)
at org.apache.ignite.internal.processors.query.GridQueryProcessor$5.applyx(GridQueryProcessor.java:1813)
at org.apache.ignite.internal.util.lang.IgniteOutClosureX.apply(IgniteOutClosureX.java:36)
at org.apache.ignite.internal.processors.query.GridQueryProcessor.executeQuery(GridQueryProcessor.java:2293)
Can you help me with this problem?Thanks.
Re: An issue of Ignite In-Menory Sql Grid since version 2.0.0
Posted by Denis Mekhanikov <dm...@gmail.com>.
Sorry for a vague answer, my bad.
As I understand, you are trying to collect a tree based on fields typeId
and pTypeId. This can be achieved by iteratively collecting objects from
cache by typeId field.
You can do this by making typeId a key of a cache or by building an SQL
index and selecting objects with simple SELECT statements. Multiple gets
from cache won't cause any performance impact, comparing to "WITH
RECURSIVE" construct, as SQL would do the same thing internally.
Also if you actually have a forest of little trees, all nodes that have the
same root may be placed on the same partition using an affinity key. Trees
may be thus collected using affinity run. More on this:
https://apacheignite.readme.io/docs/affinity-collocation. Alternatively,
cache with information about types may be configured as replicated, so all
nodes will have a local copy of this cache, but it will involve some
overhead for put/update operations.
These hints should help you optimize performance.
Denis
ср, 27 сент. 2017 г. в 0:51, Denis Magda <dm...@apache.org>:
> Denis Mek.,
>
> How do you suggest executing the same query without SQL? Please be
> specific if you advise discontinuing the SQL for the use case.
>
>
> Alex P., Vladimir,
>
> What’s the main reason of WITH RECURSIVE removal? Why it worked before and
> broken now?
> https://issues.apache.org/jira/browse/IGNITE-5289
>
> —
> Denis
>
> On Sep 25, 2017, at 12:31 AM, Denis Mekhanikov <dm...@gmail.com>
> wrote:
>
> Yes, you are right about that, but Ignite has no intention to support this
> syntax, it worked in 1.9 by chance.
> Here, I found a ticket to explicitly forbid it:
> https://issues.apache.org/jira/browse/IGNITE-5289
>
> So, it's better to use cache directly without SQL in your case.
>
> Denis
>
> пн, 25 сент. 2017 г. в 4:11, 贺波 <he...@163.com>:
>
>> Hi!
>> I have executed the recursive sql statement on H2 database,it
>> executes correctly.But it executes error since version 2.0.0.So
>> <http://2.0.0.so/> I think it's a bug of Ingite,not H2.My test demo is
>> in the attachment.
>>
>>
>>
>>
>>
>>
>> At 2017-09-23 00:20:12, "Denis Mekhanikov" <dm...@gmail.com> wrote:
>>
>> Hi!
>>
>> Internally Ignite uses H2 to process SQL queries. Recursive queries is an
>> experimental feature of H2, so I wouldn't recommend you to use it in
>> production for now.
>> Ignite 2.0 and 2.1 don't seem to support this kind of queries, so the
>> best option for you is to modify your query if possible to avoid recursive
>> constructs, or retrieve data from cache directly, without use of SQL.
>>
>> Denis
>>
>> пт, 22 сент. 2017 г. в 12:37, 贺波 <he...@163.com>:
>>
>>> Hi,I used Apache Ignite in my project for more than a year,from version
>>> 1.8.0 to 2.2.0.I use Ignite In-Menory Sql Grid in my project.I use “with
>>> as” sql function in my sql,it executes correctly in version 1.9.0,but
>>> executes error since version 2.0.0.My <http://2.0.0.my/> sql
>>> statement is:
>>> *with* RECURSIVE children(typeId, pTypeId)* AS* (
>>> SELECT typeId, pTypeId FROM ProcessDefTypePo WHERE pTypeId = '1'
>>> UNION ALL
>>> SELECT ProcessDefTypePo.typeId, ProcessDefTypePo.pTypeId FROM children
>>> INNER JOIN ProcessDefTypePo ON children.typeId =ProcessDefTypePo.pTypeId
>>> )
>>> select t1.typeId,t1.pTypeId,t1.typeName,t1.description,
>>> t2.typeName as pTypeName from ProcessDefTypePo t1 left join
>>> ProcessDefTypePo t2 on t1.pTypeId=t2.typeId where t1.typeId not in ( select
>>> typeId from children )
>>>
>>> The execution error in version 2.2.0 is:
>>> Caused by: class org.apache.ignite.IgniteCheckedException: Unknown query
>>> type: null
>>> at
>>> org.apache.ignite.internal.processors.query.GridQueryProcessor.executeQuery(GridQueryProcessor.java:2316)
>>> at
>>> org.apache.ignite.internal.processors.query.GridQueryProcessor.querySqlFields(GridQueryProcessor.java:1820)
>>> ... 165 more
>>> Caused by: java.lang.UnsupportedOperationException: Unknown query type:
>>> null
>>> at
>>> org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseQuery(GridSqlQueryParser.java:1225)
>>> at
>>> org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseTable(GridSqlQueryParser.java:501)
>>> at
>>> org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseTableFilter(GridSqlQueryParser.java:465)
>>> at
>>> org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseSelect(GridSqlQueryParser.java:565)
>>> at
>>> org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseQuery(GridSqlQueryParser.java:1220)
>>> at
>>> org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseQueryExpression(GridSqlQueryParser.java:452)
>>> at
>>> org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseExpression0(GridSqlQueryParser.java:1436)
>>> at
>>> org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseExpression(GridSqlQueryParser.java:1267)
>>> at
>>> org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseExpression0(GridSqlQueryParser.java:1378)
>>> at
>>> org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseExpression(GridSqlQueryParser.java:1267)
>>> at
>>> org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseSelect(GridSqlQueryParser.java:536)
>>> at
>>> org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseQuery(GridSqlQueryParser.java:1220)
>>> at
>>> org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parse(GridSqlQueryParser.java:1181)
>>> at
>>> org.apache.ignite.internal.processors.query.h2.sql.GridSqlQuerySplitter.parse(GridSqlQuerySplitter.java:1604)
>>> at
>>> org.apache.ignite.internal.processors.query.h2.sql.GridSqlQuerySplitter.split(GridSqlQuerySplitter.java:197)
>>> at
>>> org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.queryDistributedSqlFields(IgniteH2Indexing.java:1307)
>>> at
>>> org.apache.ignite.internal.processors.query.GridQueryProcessor$5.applyx(GridQueryProcessor.java:1815)
>>> at
>>> org.apache.ignite.internal.processors.query.GridQueryProcessor$5.applyx(GridQueryProcessor.java:1813)
>>> at
>>> org.apache.ignite.internal.util.lang.IgniteOutClosureX.apply(IgniteOutClosureX.java:36)
>>> at
>>> org.apache.ignite.internal.processors.query.GridQueryProcessor.executeQuery(GridQueryProcessor.java:2293)
>>>
>>> Can you help me with this problem?Thanks.
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>
>>
>> 【网易自营】好吃到爆！鲜香弹滑加热即食，经典13香/麻辣小龙虾仅75元3斤>>
>> <http://you.163.com/item/detail?id=1183001&from=web_gg_mail_jiaobiao_7>
>>
>>
>
>
Re: An issue of Ignite In-Menory Sql Grid since version 2.0.0
Posted by Denis Magda <dm...@apache.org>.
Denis Mek.,
How do you suggest executing the same query without SQL? Please be specific if you advise discontinuing the SQL for the use case.
Alex P., Vladimir,
What’s the main reason of WITH RECURSIVE removal? Why it worked before and broken now?
https://issues.apache.org/jira/browse/IGNITE-5289 <https://issues.apache.org/jira/browse/IGNITE-5289>
—
Denis
> On Sep 25, 2017, at 12:31 AM, Denis Mekhanikov <dm...@gmail.com> wrote:
>
> Yes, you are right about that, but Ignite has no intention to support this syntax, it worked in 1.9 by chance.
> Here, I found a ticket to explicitly forbid it: https://issues.apache.org/jira/browse/IGNITE-5289 <https://issues.apache.org/jira/browse/IGNITE-5289>
>
> So, it's better to use cache directly without SQL in your case.
>
> Denis
>
> пн, 25 сент. 2017 г. в 4:11, 贺波 <hebo824@163.com <ma...@163.com>>:
> Hi!
> I have executed the recursive sql statement on H2 database,it executes correctly.But it executes error since version 2.0.0.So <http://2.0.0.so/> I think it's a bug of Ingite,not H2.My test demo is in the attachment.
>
>
>
>
>
>
> At 2017-09-23 00:20:12, "Denis Mekhanikov" <dmekhanikov@gmail.com <ma...@gmail.com>> wrote:
> Hi!
>
> Internally Ignite uses H2 to process SQL queries. Recursive queries is an experimental feature of H2, so I wouldn't recommend you to use it in production for now.
> Ignite 2.0 and 2.1 don't seem to support this kind of queries, so the best option for you is to modify your query if possible to avoid recursive constructs, or retrieve data from cache directly, without use of SQL.
>
> Denis
>
> пт, 22 сент. 2017 г. в 12:37, 贺波 <hebo824@163.com <ma...@163.com>>:
> Hi,I used Apache Ignite in my project for more than a year,from version 1.8.0 to 2.2.0.I use Ignite In-Menory Sql Grid in my project.I use “with as” sql function in my sql,it executes correctly in version 1.9.0,but executes error since version 2.0.0.My <http://2.0.0.my/> sql statement is:
> with RECURSIVE children(typeId, pTypeId) AS (
> SELECT typeId, pTypeId FROM ProcessDefTypePo WHERE pTypeId = '1'
> UNION ALL
> SELECT ProcessDefTypePo.typeId, ProcessDefTypePo.pTypeId FROM children INNER JOIN ProcessDefTypePo ON children.typeId =ProcessDefTypePo.pTypeId
> )
> select t1.typeId,t1.pTypeId,t1.typeName,t1.description, t2.typeName as pTypeName from ProcessDefTypePo t1 left join ProcessDefTypePo t2 on t1.pTypeId=t2.typeId where t1.typeId not in ( select typeId from children )
>
> The execution error in version 2.2.0 is:
> Caused by: class org.apache.ignite.IgniteCheckedException: Unknown query type: null
> at org.apache.ignite.internal.processors.query.GridQueryProcessor.executeQuery(GridQueryProcessor.java:2316)
> at org.apache.ignite.internal.processors.query.GridQueryProcessor.querySqlFields(GridQueryProcessor.java:1820)
> ... 165 more
> Caused by: java.lang.UnsupportedOperationException: Unknown query type: null
> at org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseQuery(GridSqlQueryParser.java:1225)
> at org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseTable(GridSqlQueryParser.java:501)
> at org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseTableFilter(GridSqlQueryParser.java:465)
> at org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseSelect(GridSqlQueryParser.java:565)
> at org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseQuery(GridSqlQueryParser.java:1220)
> at org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseQueryExpression(GridSqlQueryParser.java:452)
> at org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseExpression0(GridSqlQueryParser.java:1436)
> at org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseExpression(GridSqlQueryParser.java:1267)
> at org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseExpression0(GridSqlQueryParser.java:1378)
> at org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseExpression(GridSqlQueryParser.java:1267)
> at org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseSelect(GridSqlQueryParser.java:536)
> at org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseQuery(GridSqlQueryParser.java:1220)
> at org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parse(GridSqlQueryParser.java:1181)
> at org.apache.ignite.internal.processors.query.h2.sql.GridSqlQuerySplitter.parse(GridSqlQuerySplitter.java:1604)
> at org.apache.ignite.internal.processors.query.h2.sql.GridSqlQuerySplitter.split(GridSqlQuerySplitter.java:197)
> at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.queryDistributedSqlFields(IgniteH2Indexing.java:1307)
> at org.apache.ignite.internal.processors.query.GridQueryProcessor$5.applyx(GridQueryProcessor.java:1815)
> at org.apache.ignite.internal.processors.query.GridQueryProcessor$5.applyx(GridQueryProcessor.java:1813)
> at org.apache.ignite.internal.util.lang.IgniteOutClosureX.apply(IgniteOutClosureX.java:36)
> at org.apache.ignite.internal.processors.query.GridQueryProcessor.executeQuery(GridQueryProcessor.java:2293)
>
> Can you help me with this problem?Thanks.
>
>
>
>
>
>
>
>
>
> 【网易自营】好吃到爆！鲜香弹滑加热即食，经典13香/麻辣小龙虾仅75元3斤>> <http://you.163.com/item/detail?id=1183001&from=web_gg_mail_jiaobiao_7>
Re: An issue of Ignite In-Menory Sql Grid since version 2.0.0
Posted by Denis Magda <dm...@apache.org>.
Denis Mek.,
How do you suggest executing the same query without SQL? Please be specific if you advise discontinuing the SQL for the use case.
Alex P., Vladimir,
What’s the main reason of WITH RECURSIVE removal? Why it worked before and broken now?
https://issues.apache.org/jira/browse/IGNITE-5289 <https://issues.apache.org/jira/browse/IGNITE-5289>
—
Denis
> On Sep 25, 2017, at 12:31 AM, Denis Mekhanikov <dm...@gmail.com> wrote:
>
> Yes, you are right about that, but Ignite has no intention to support this syntax, it worked in 1.9 by chance.
> Here, I found a ticket to explicitly forbid it: https://issues.apache.org/jira/browse/IGNITE-5289 <https://issues.apache.org/jira/browse/IGNITE-5289>
>
> So, it's better to use cache directly without SQL in your case.
>
> Denis
>
> пн, 25 сент. 2017 г. в 4:11, 贺波 <hebo824@163.com <ma...@163.com>>:
> Hi!
> I have executed the recursive sql statement on H2 database,it executes correctly.But it executes error since version 2.0.0.So <http://2.0.0.so/> I think it's a bug of Ingite,not H2.My test demo is in the attachment.
>
>
>
>
>
>
> At 2017-09-23 00:20:12, "Denis Mekhanikov" <dmekhanikov@gmail.com <ma...@gmail.com>> wrote:
> Hi!
>
> Internally Ignite uses H2 to process SQL queries. Recursive queries is an experimental feature of H2, so I wouldn't recommend you to use it in production for now.
> Ignite 2.0 and 2.1 don't seem to support this kind of queries, so the best option for you is to modify your query if possible to avoid recursive constructs, or retrieve data from cache directly, without use of SQL.
>
> Denis
>
> пт, 22 сент. 2017 г. в 12:37, 贺波 <hebo824@163.com <ma...@163.com>>:
> Hi,I used Apache Ignite in my project for more than a year,from version 1.8.0 to 2.2.0.I use Ignite In-Menory Sql Grid in my project.I use “with as” sql function in my sql,it executes correctly in version 1.9.0,but executes error since version 2.0.0.My <http://2.0.0.my/> sql statement is:
> with RECURSIVE children(typeId, pTypeId) AS (
> SELECT typeId, pTypeId FROM ProcessDefTypePo WHERE pTypeId = '1'
> UNION ALL
> SELECT ProcessDefTypePo.typeId, ProcessDefTypePo.pTypeId FROM children INNER JOIN ProcessDefTypePo ON children.typeId =ProcessDefTypePo.pTypeId
> )
> select t1.typeId,t1.pTypeId,t1.typeName,t1.description, t2.typeName as pTypeName from ProcessDefTypePo t1 left join ProcessDefTypePo t2 on t1.pTypeId=t2.typeId where t1.typeId not in ( select typeId from children )
>
> The execution error in version 2.2.0 is:
> Caused by: class org.apache.ignite.IgniteCheckedException: Unknown query type: null
> at org.apache.ignite.internal.processors.query.GridQueryProcessor.executeQuery(GridQueryProcessor.java:2316)
> at org.apache.ignite.internal.processors.query.GridQueryProcessor.querySqlFields(GridQueryProcessor.java:1820)
> ... 165 more
> Caused by: java.lang.UnsupportedOperationException: Unknown query type: null
> at org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseQuery(GridSqlQueryParser.java:1225)
> at org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseTable(GridSqlQueryParser.java:501)
> at org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseTableFilter(GridSqlQueryParser.java:465)
> at org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseSelect(GridSqlQueryParser.java:565)
> at org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseQuery(GridSqlQueryParser.java:1220)
> at org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseQueryExpression(GridSqlQueryParser.java:452)
> at org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseExpression0(GridSqlQueryParser.java:1436)
> at org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseExpression(GridSqlQueryParser.java:1267)
> at org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseExpression0(GridSqlQueryParser.java:1378)
> at org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseExpression(GridSqlQueryParser.java:1267)
> at org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseSelect(GridSqlQueryParser.java:536)
> at org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseQuery(GridSqlQueryParser.java:1220)
> at org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parse(GridSqlQueryParser.java:1181)
> at org.apache.ignite.internal.processors.query.h2.sql.GridSqlQuerySplitter.parse(GridSqlQuerySplitter.java:1604)
> at org.apache.ignite.internal.processors.query.h2.sql.GridSqlQuerySplitter.split(GridSqlQuerySplitter.java:197)
> at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.queryDistributedSqlFields(IgniteH2Indexing.java:1307)
> at org.apache.ignite.internal.processors.query.GridQueryProcessor$5.applyx(GridQueryProcessor.java:1815)
> at org.apache.ignite.internal.processors.query.GridQueryProcessor$5.applyx(GridQueryProcessor.java:1813)
> at org.apache.ignite.internal.util.lang.IgniteOutClosureX.apply(IgniteOutClosureX.java:36)
> at org.apache.ignite.internal.processors.query.GridQueryProcessor.executeQuery(GridQueryProcessor.java:2293)
>
> Can you help me with this problem?Thanks.
>
>
>
>
>
>
>
>
>
> 【网易自营】好吃到爆！鲜香弹滑加热即食，经典13香/麻辣小龙虾仅75元3斤>> <http://you.163.com/item/detail?id=1183001&from=web_gg_mail_jiaobiao_7>
Re: Re: An issue of Ignite In-Menory Sql Grid since version 2.0.0
Posted by Denis Mekhanikov <dm...@gmail.com>.
Yes, you are right about that, but Ignite has no intention to support this
syntax, it worked in 1.9 by chance.
Here, I found a ticket to explicitly forbid it:
https://issues.apache.org/jira/browse/IGNITE-5289
So, it's better to use cache directly without SQL in your case.
Denis
пн, 25 сент. 2017 г. в 4:11, 贺波 <he...@163.com>:
> Hi!
> I have executed the recursive sql statement on H2 database,it
> executes correctly.But it executes error since version 2.0.0.So I think
> it's a bug of Ingite,not H2.My test demo is in the attachment.
>
>
>
>
>
>
> At 2017-09-23 00:20:12, "Denis Mekhanikov" <dm...@gmail.com> wrote:
>
> Hi!
>
> Internally Ignite uses H2 to process SQL queries. Recursive queries is an
> experimental feature of H2, so I wouldn't recommend you to use it in
> production for now.
> Ignite 2.0 and 2.1 don't seem to support this kind of queries, so the best
> option for you is to modify your query if possible to avoid recursive
> constructs, or retrieve data from cache directly, without use of SQL.
>
> Denis
>
> пт, 22 сент. 2017 г. в 12:37, 贺波 <he...@163.com>:
>
>> Hi,I used Apache Ignite in my project for more than a year,from version
>> 1.8.0 to 2.2.0.I use Ignite In-Menory Sql Grid in my project.I use “with
>> as” sql function in my sql,it executes correctly in version 1.9.0,but
>> executes error since version 2.0.0.My sql statement is:
>> *with* RECURSIVE children(typeId, pTypeId)* AS* (
>> SELECT typeId, pTypeId FROM ProcessDefTypePo WHERE pTypeId = '1'
>> UNION ALL
>> SELECT ProcessDefTypePo.typeId, ProcessDefTypePo.pTypeId FROM children
>> INNER JOIN ProcessDefTypePo ON children.typeId =ProcessDefTypePo.pTypeId
>> )
>> select t1.typeId,t1.pTypeId,t1.typeName,t1.description,
>> t2.typeName as pTypeName from ProcessDefTypePo t1 left join
>> ProcessDefTypePo t2 on t1.pTypeId=t2.typeId where t1.typeId not in ( select
>> typeId from children )
>>
>> The execution error in version 2.2.0 is:
>> Caused by: class org.apache.ignite.IgniteCheckedException: Unknown query
>> type: null
>> at
>> org.apache.ignite.internal.processors.query.GridQueryProcessor.executeQuery(GridQueryProcessor.java:2316)
>> at
>> org.apache.ignite.internal.processors.query.GridQueryProcessor.querySqlFields(GridQueryProcessor.java:1820)
>> ... 165 more
>> Caused by: java.lang.UnsupportedOperationException: Unknown query type:
>> null
>> at
>> org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseQuery(GridSqlQueryParser.java:1225)
>> at
>> org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseTable(GridSqlQueryParser.java:501)
>> at
>> org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseTableFilter(GridSqlQueryParser.java:465)
>> at
>> org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseSelect(GridSqlQueryParser.java:565)
>> at
>> org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseQuery(GridSqlQueryParser.java:1220)
>> at
>> org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseQueryExpression(GridSqlQueryParser.java:452)
>> at
>> org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseExpression0(GridSqlQueryParser.java:1436)
>> at
>> org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseExpression(GridSqlQueryParser.java:1267)
>> at
>> org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseExpression0(GridSqlQueryParser.java:1378)
>> at
>> org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseExpression(GridSqlQueryParser.java:1267)
>> at
>> org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseSelect(GridSqlQueryParser.java:536)
>> at
>> org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseQuery(GridSqlQueryParser.java:1220)
>> at
>> org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parse(GridSqlQueryParser.java:1181)
>> at
>> org.apache.ignite.internal.processors.query.h2.sql.GridSqlQuerySplitter.parse(GridSqlQuerySplitter.java:1604)
>> at
>> org.apache.ignite.internal.processors.query.h2.sql.GridSqlQuerySplitter.split(GridSqlQuerySplitter.java:197)
>> at
>> org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.queryDistributedSqlFields(IgniteH2Indexing.java:1307)
>> at
>> org.apache.ignite.internal.processors.query.GridQueryProcessor$5.applyx(GridQueryProcessor.java:1815)
>> at
>> org.apache.ignite.internal.processors.query.GridQueryProcessor$5.applyx(GridQueryProcessor.java:1813)
>> at
>> org.apache.ignite.internal.util.lang.IgniteOutClosureX.apply(IgniteOutClosureX.java:36)
>> at
>> org.apache.ignite.internal.processors.query.GridQueryProcessor.executeQuery(GridQueryProcessor.java:2293)
>>
>> Can you help me with this problem?Thanks.
>>
>>
>>
>>
>>
>>
>>
>>
>
>
> 【网易自营】好吃到爆！鲜香弹滑加热即食，经典13香/麻辣小龙虾仅75元3斤>>
> <http://you.163.com/item/detail?id=1183001&from=web_gg_mail_jiaobiao_7>
>
>
Re:Re: An issue of Ignite In-Menory Sql Grid since version 2.0.0
Posted by 贺波 <he...@163.com>.
Hi!
I have executed the recursive sql statement on H2 database,it executes correctly.But it executes error since version 2.0.0.So I think it's a bug of Ingite,not H2.My test demo is in the attachment.
At 2017-09-23 00:20:12, "Denis Mekhanikov" <dm...@gmail.com> wrote:
Hi!
Internally Ignite uses H2 to process SQL queries. Recursive queries is an experimental feature of H2, so I wouldn't recommend you to use it in production for now.
Ignite 2.0 and 2.1 don't seem to support this kind of queries, so the best option for you is to modify your query if possible to avoid recursive constructs, or retrieve data from cache directly, without use of SQL.
Denis
пт, 22 сент. 2017 г. в 12:37, 贺波 <he...@163.com>:
Hi,I used Apache Ignite in my project for more than a year,from version 1.8.0 to 2.2.0.I use Ignite In-Menory Sql Grid in my project.I use “with as” sql function in my sql,it executes correctly in version 1.9.0,but executes error since version 2.0.0.My sql statement is:
with RECURSIVE children(typeId, pTypeId) AS (
SELECT typeId, pTypeId FROM ProcessDefTypePo WHERE pTypeId = '1'
UNION ALL
SELECT ProcessDefTypePo.typeId, ProcessDefTypePo.pTypeId FROM children INNER JOIN ProcessDefTypePo ON children.typeId =ProcessDefTypePo.pTypeId
)
select t1.typeId,t1.pTypeId,t1.typeName,t1.description, t2.typeName as pTypeName from ProcessDefTypePo t1 left join ProcessDefTypePo t2 on t1.pTypeId=t2.typeId where t1.typeId not in ( select typeId from children )
The execution error in version 2.2.0 is:
Caused by: class org.apache.ignite.IgniteCheckedException: Unknown query type: null
at org.apache.ignite.internal.processors.query.GridQueryProcessor.executeQuery(GridQueryProcessor.java:2316)
at org.apache.ignite.internal.processors.query.GridQueryProcessor.querySqlFields(GridQueryProcessor.java:1820)
... 165 more
Caused by: java.lang.UnsupportedOperationException: Unknown query type: null
at org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseQuery(GridSqlQueryParser.java:1225)
at org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseTable(GridSqlQueryParser.java:501)
at org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseTableFilter(GridSqlQueryParser.java:465)
at org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseSelect(GridSqlQueryParser.java:565)
at org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseQuery(GridSqlQueryParser.java:1220)
at org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseQueryExpression(GridSqlQueryParser.java:452)
at org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseExpression0(GridSqlQueryParser.java:1436)
at org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseExpression(GridSqlQueryParser.java:1267)
at org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseExpression0(GridSqlQueryParser.java:1378)
at org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseExpression(GridSqlQueryParser.java:1267)
at org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseSelect(GridSqlQueryParser.java:536)
at org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseQuery(GridSqlQueryParser.java:1220)
at org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parse(GridSqlQueryParser.java:1181)
at org.apache.ignite.internal.processors.query.h2.sql.GridSqlQuerySplitter.parse(GridSqlQuerySplitter.java:1604)
at org.apache.ignite.internal.processors.query.h2.sql.GridSqlQuerySplitter.split(GridSqlQuerySplitter.java:197)
at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.queryDistributedSqlFields(IgniteH2Indexing.java:1307)
at org.apache.ignite.internal.processors.query.GridQueryProcessor$5.applyx(GridQueryProcessor.java:1815)
at org.apache.ignite.internal.processors.query.GridQueryProcessor$5.applyx(GridQueryProcessor.java:1813)
at org.apache.ignite.internal.util.lang.IgniteOutClosureX.apply(IgniteOutClosureX.java:36)
at org.apache.ignite.internal.processors.query.GridQueryProcessor.executeQuery(GridQueryProcessor.java:2293)
Can you help me with this problem?Thanks.
Re: An issue of Ignite In-Menory Sql Grid since version 2.0.0
Posted by Denis Mekhanikov <dm...@gmail.com>.
Hi!
Internally Ignite uses H2 to process SQL queries. Recursive queries is an
experimental feature of H2, so I wouldn't recommend you to use it in
production for now.
Ignite 2.0 and 2.1 don't seem to support this kind of queries, so the best
option for you is to modify your query if possible to avoid recursive
constructs, or retrieve data from cache directly, without use of SQL.
Denis
пт, 22 сент. 2017 г. в 12:37, 贺波 <he...@163.com>:
> Hi,I used Apache Ignite in my project for more than a year,from version
> 1.8.0 to 2.2.0.I use Ignite In-Menory Sql Grid in my project.I use “with
> as” sql function in my sql,it executes correctly in version 1.9.0,but
> executes error since version 2.0.0.My sql statement is:
> *with* RECURSIVE children(typeId, pTypeId)* AS* (
> SELECT typeId, pTypeId FROM ProcessDefTypePo WHERE pTypeId = '1'
> UNION ALL
> SELECT ProcessDefTypePo.typeId, ProcessDefTypePo.pTypeId FROM children
> INNER JOIN ProcessDefTypePo ON children.typeId =ProcessDefTypePo.pTypeId
> )
> select t1.typeId,t1.pTypeId,t1.typeName,t1.description, t2.typeName
> as pTypeName from ProcessDefTypePo t1 left join ProcessDefTypePo t2 on
> t1.pTypeId=t2.typeId where t1.typeId not in ( select typeId from children )
>
> The execution error in version 2.2.0 is:
> Caused by: class org.apache.ignite.IgniteCheckedException: Unknown query
> type: null
> at
> org.apache.ignite.internal.processors.query.GridQueryProcessor.executeQuery(GridQueryProcessor.java:2316)
> at
> org.apache.ignite.internal.processors.query.GridQueryProcessor.querySqlFields(GridQueryProcessor.java:1820)
> ... 165 more
> Caused by: java.lang.UnsupportedOperationException: Unknown query type:
> null
> at
> org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseQuery(GridSqlQueryParser.java:1225)
> at
> org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseTable(GridSqlQueryParser.java:501)
> at
> org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseTableFilter(GridSqlQueryParser.java:465)
> at
> org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseSelect(GridSqlQueryParser.java:565)
> at
> org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseQuery(GridSqlQueryParser.java:1220)
> at
> org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseQueryExpression(GridSqlQueryParser.java:452)
> at
> org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseExpression0(GridSqlQueryParser.java:1436)
> at
> org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseExpression(GridSqlQueryParser.java:1267)
> at
> org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseExpression0(GridSqlQueryParser.java:1378)
> at
> org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseExpression(GridSqlQueryParser.java:1267)
> at
> org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseSelect(GridSqlQueryParser.java:536)
> at
> org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseQuery(GridSqlQueryParser.java:1220)
> at
> org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parse(GridSqlQueryParser.java:1181)
> at
> org.apache.ignite.internal.processors.query.h2.sql.GridSqlQuerySplitter.parse(GridSqlQuerySplitter.java:1604)
> at
> org.apache.ignite.internal.processors.query.h2.sql.GridSqlQuerySplitter.split(GridSqlQuerySplitter.java:197)
> at
> org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.queryDistributedSqlFields(IgniteH2Indexing.java:1307)
> at
> org.apache.ignite.internal.processors.query.GridQueryProcessor$5.applyx(GridQueryProcessor.java:1815)
> at
> org.apache.ignite.internal.processors.query.GridQueryProcessor$5.applyx(GridQueryProcessor.java:1813)
> at
> org.apache.ignite.internal.util.lang.IgniteOutClosureX.apply(IgniteOutClosureX.java:36)
> at
> org.apache.ignite.internal.processors.query.GridQueryProcessor.executeQuery(GridQueryProcessor.java:2293)
>
> Can you help me with this problem?Thanks.
>
>
>
>
>
>
>
>