Does Ignite support nested SQL Queries?

classic Classic list List threaded Threaded
8 messages Options
iceguo iceguo
Reply | Threaded
Open this post in threaded view
|

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".
Sergi Vladykin Sergi Vladykin
Reply | Threaded
Open this post in threaded view
|

Re: Does Ignite support nested SQL Queries?

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


2015-10-27 12:00 GMT+03:00 iceguo <[hidden email]>:
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.

dsetrakyan dsetrakyan
Reply | Threaded
Open this post in threaded view
|

Re: Does Ignite support nested SQL Queries?

In reply to this post by iceguo
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:

D.


On Tue, Oct 27, 2015 at 2:00 AM, iceguo <[hidden email]> 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.

iceguo iceguo
Reply | Threaded
Open this post in threaded view
|

Re: Does Ignite support nested SQL Queries?

I have tried according to your advises, it works!
ght230 ght230
Reply | Threaded
Open this post in threaded view
|

Re: Does Ignite support nested SQL Queries?

In reply to this post by dsetrakyan
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
vkulichenko vkulichenko
Reply | Threaded
Open this post in threaded view
|

Re: Does Ignite support nested SQL Queries?

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
Sergi Vladykin Sergi Vladykin
Reply | Threaded
Open this post in threaded view
|

Re: Does Ignite support nested SQL Queries?

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 <[hidden email]>:
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.

ght230 ght230
Reply | Threaded
Open this post in threaded view
|

Re: Does Ignite support nested SQL Queries?

Thank you very much!