You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by iceguo <gu...@ffcs.cn> on 2015/10/27 10:00:26 UTC
Does Ignite support nested SQL Queries?
I want to know whether Ignite can do nested SQL Queries such as following:
"select a.orgid, b.name from (select orgid from person group by orgid) a
LEFT JOIN organization b on a.orgid = b.id order by b.name".
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Does-Ignite-support-nested-SQL-Queries-tp1714.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: Does Ignite support nested SQL Queries?
Posted by iceguo <gu...@ffcs.cn>.
I have tried according to your advises, it works!
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Does-Ignite-support-nested-SQL-Queries-tp1714p1796.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: Does Ignite support nested SQL Queries?
Posted by ght230 <gh...@163.com>.
Thank you very much!
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Does-Ignite-support-nested-SQL-Queries-tp1714p2011.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: Does Ignite support nested SQL Queries?
Posted by Sergi Vladykin <se...@gmail.com>.
This issue is already fixed in the latest H2 database engine, I hope we
will be able to upgrade in 1.5
Sergi
2015-11-11 0:48 GMT+03:00 vkulichenko <va...@gmail.com>:
> Hi,
>
> I reproduced the issue and created a ticket [1]. Someone in the community
> will take a look shortly and provide feedback.
>
> [1] https://issues.apache.org/jira/browse/IGNITE-1886
>
> -Val
>
>
>
> --
> View this message in context:
> http://apache-ignite-users.70518.x6.nabble.com/Does-Ignite-support-nested-SQL-Queries-tp1714p1919.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>
Re: Does Ignite support nested SQL Queries?
Posted by vkulichenko <va...@gmail.com>.
Hi,
I reproduced the issue and created a ticket [1]. Someone in the community
will take a look shortly and provide feedback.
[1] https://issues.apache.org/jira/browse/IGNITE-1886
-Val
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Does-Ignite-support-nested-SQL-Queries-tp1714p1919.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Fwd: Does Ignite support nested SQL Queries?
Posted by Valentin Kulichenko <va...@gmail.com>.
Sergi,
I tried to execute this query and got an NPE in H2:
https://issues.apache.org/jira/browse/IGNITE-1886. Can you please take a
look?
-Val
---------- Forwarded message ----------
From: ght230 <gh...@163.com>
Date: Tue, Nov 10, 2015 at 8:46 AM
Subject: Re: Does Ignite support nested SQL Queries?
To: user@ignite.apache.org
Hi
I tried the following sub-queries, it seems ignite can not work well.
"SELECT
a.*
FROM
(SELECT
CASE WHEN u.id < 100 THEN u.id ELSE ug.id END id
FROM
"UserCache".user u,
userorder ug
WHERE u.id = ug.usrid) a,
(SELECT
CASE
WHEN og.goodid < 5
THEN 100
ELSE og.goodid
END id
FROM
userorder ug,
"OrderGoodCache".ordergood og
WHERE ug.id = og.orderid) b
WHERE a.id = b.id"
information about error as following：
Exception in thread "main" javax.cache.CacheException: class
org.apache.ignite.IgniteException: Failed to parse query: SELECT
a.*
FROM
(SELECT
CASE WHEN u.id < 100 THEN u.id ELSE ug.id END id
FROM
"UserCache".user u,
userorder ug
WHERE u.id = ug.usrid) a,
(SELECT
CASE
WHEN og.goodid < 5
THEN 100
ELSE og.goodid
END id
FROM
userorder ug,
"OrderGoodCache".ordergood og
WHERE ug.id = og.orderid) b
WHERE a.id = b.id
at
org.apache.ignite.internal.processors.cache.IgniteCacheProxy.query(IgniteCacheProxy.java:636)
at cn.ffcs.main2.main(main2.java:54)
Caused by: class org.apache.ignite.IgniteException: Failed to parse query:
SELECT
a.*
FROM
(SELECT
CASE WHEN u.id < 100 THEN u.id ELSE ug.id END id
FROM
"UserCache".user u,
userorder ug
WHERE u.id = ug.usrid) a,
(SELECT
CASE
WHEN og.goodid < 5
THEN 100
ELSE og.goodid
END id
FROM
userorder ug,
"OrderGoodCache".ordergood og
WHERE ug.id = og.orderid) b
WHERE a.id = b.id
at
org.apache.ignite.internal.processors.query.GridQueryProcessor.queryTwoStep(GridQueryProcessor.java:641)
at
org.apache.ignite.internal.processors.cache.IgniteCacheProxy.query(IgniteCacheProxy.java:627)
... 1 more
Caused by: class org.apache.ignite.IgniteCheckedException: Failed to parse
query: SELECT
a.*
FROM
(SELECT
CASE WHEN u.id < 100 THEN u.id ELSE ug.id END id
FROM
"UserCache".user u,
userorder ug
WHERE u.id = ug.usrid) a,
(SELECT
CASE
WHEN og.goodid < 5
THEN 100
ELSE og.goodid
END id
FROM
userorder ug,
"OrderGoodCache".ordergood og
WHERE ug.id = og.orderid) b
WHERE a.id = b.id
at
org.apache.ignite.internal.processors.query.GridQueryProcessor.executeQuery(GridQueryProcessor.java:1510)
at
org.apache.ignite.internal.processors.query.GridQueryProcessor.queryTwoStep(GridQueryProcessor.java:634)
... 2 more
--
View this message in context:
http://apache-ignite-users.70518.x6.nabble.com/Does-Ignite-support-nested-SQL-Queries-tp1714p1910.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: Does Ignite support nested SQL Queries?
Posted by ght230 <gh...@163.com>.
Hi
I tried the following sub-queries, it seems ignite can not work well.
"SELECT
a.*
FROM
(SELECT
CASE WHEN u.id < 100 THEN u.id ELSE ug.id END id
FROM
"UserCache".user u,
userorder ug
WHERE u.id = ug.usrid) a,
(SELECT
CASE
WHEN og.goodid < 5
THEN 100
ELSE og.goodid
END id
FROM
userorder ug,
"OrderGoodCache".ordergood og
WHERE ug.id = og.orderid) b
WHERE a.id = b.id"
information about error as following：
Exception in thread "main" javax.cache.CacheException: class
org.apache.ignite.IgniteException: Failed to parse query: SELECT
a.*
FROM
(SELECT
CASE WHEN u.id < 100 THEN u.id ELSE ug.id END id
FROM
"UserCache".user u,
userorder ug
WHERE u.id = ug.usrid) a,
(SELECT
CASE
WHEN og.goodid < 5
THEN 100
ELSE og.goodid
END id
FROM
userorder ug,
"OrderGoodCache".ordergood og
WHERE ug.id = og.orderid) b
WHERE a.id = b.id
at
org.apache.ignite.internal.processors.cache.IgniteCacheProxy.query(IgniteCacheProxy.java:636)
at cn.ffcs.main2.main(main2.java:54)
Caused by: class org.apache.ignite.IgniteException: Failed to parse query:
SELECT
a.*
FROM
(SELECT
CASE WHEN u.id < 100 THEN u.id ELSE ug.id END id
FROM
"UserCache".user u,
userorder ug
WHERE u.id = ug.usrid) a,
(SELECT
CASE
WHEN og.goodid < 5
THEN 100
ELSE og.goodid
END id
FROM
userorder ug,
"OrderGoodCache".ordergood og
WHERE ug.id = og.orderid) b
WHERE a.id = b.id
at
org.apache.ignite.internal.processors.query.GridQueryProcessor.queryTwoStep(GridQueryProcessor.java:641)
at
org.apache.ignite.internal.processors.cache.IgniteCacheProxy.query(IgniteCacheProxy.java:627)
... 1 more
Caused by: class org.apache.ignite.IgniteCheckedException: Failed to parse
query: SELECT
a.*
FROM
(SELECT
CASE WHEN u.id < 100 THEN u.id ELSE ug.id END id
FROM
"UserCache".user u,
userorder ug
WHERE u.id = ug.usrid) a,
(SELECT
CASE
WHEN og.goodid < 5
THEN 100
ELSE og.goodid
END id
FROM
userorder ug,
"OrderGoodCache".ordergood og
WHERE ug.id = og.orderid) b
WHERE a.id = b.id
at
org.apache.ignite.internal.processors.query.GridQueryProcessor.executeQuery(GridQueryProcessor.java:1510)
at
org.apache.ignite.internal.processors.query.GridQueryProcessor.queryTwoStep(GridQueryProcessor.java:634)
... 2 more
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Does-Ignite-support-nested-SQL-Queries-tp1714p1910.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: Does Ignite support nested SQL Queries?
Posted by Dmitriy Setrakyan <ds...@apache.org>.
In short, the answer is Yes, sub-queries are supported. The only thing you
need to know is that in case of Partitioned caches the join keys need to be
colocated. So, in your example, a.orgId and b.id have to be collocated.
More information on collocation can be found here:
http://apacheignite.gridgain.org/docs/affinity-collocation
D.
On Tue, Oct 27, 2015 at 2:00 AM, iceguo <gu...@ffcs.cn> wrote:
> I want to know whether Ignite can do nested SQL Queries such as following:
>
> "select a.orgid, b.name from (select orgid from person group by orgid) a
> LEFT JOIN organization b on a.orgid = b.id order by b.name".
>
>
>
> --
> View this message in context:
> http://apache-ignite-users.70518.x6.nabble.com/Does-Ignite-support-nested-SQL-Queries-tp1714.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>
Re: Does Ignite support nested SQL Queries?
Posted by Sergi Vladykin <se...@gmail.com>.
The first think you have to know about SQL in Ignite is that it works
differently
when you run it over replicated cache and when over partitioned cache [1].
Having said that in case of REPLICATED cache Ignite supports everything
that H2 database engine does, in case of PARTITIONED cache Ingite has
to split the original query into map-reduce parts to be executed across all
the partitions and currently this ability to rewrite queries is quite
limited:
it analyzes only top level queries but not subqueries. It means that all
the subqueries must be fully collocated.
Lets take your example.
Suppose Person is in a partitioned cache and and it is collocated with
Organization
which is in partitioned cache as well by affinity key `orgId`. Then we know
that
the top level query can be rewritten, but the subquery can not. But in this
query it is
not needed, because join condition is collocated and this join of the
subquery to
Organization can be correctly done on each node and the results can be
merged and sorted in reduce step.
I know it looks a bit complicated but we continuously work on improving
this functionality. Currently I'd suggest to run EXPLAIN and see if map and
reduce queries look correctly for you (map query will be executed on each
data node, reduce query is on local node will merge results from map step).
Sergi
[1] https://apacheignite.readme.io/docs/sql-queries#how-sql-queries-work
2015-10-27 12:00 GMT+03:00 iceguo <gu...@ffcs.cn>:
> I want to know whether Ignite can do nested SQL Queries such as following:
>
> "select a.orgid, b.name from (select orgid from person group by orgid) a
> LEFT JOIN organization b on a.orgid = b.id order by b.name".
>
>
>
> --
> View this message in context:
> http://apache-ignite-users.70518.x6.nabble.com/Does-Ignite-support-nested-SQL-Queries-tp1714.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>