[jira] [Commented] (IGNITE-12201) distributed sql join not working as mentioned in documentation

["Ivan Pavlukhin (Jira)" <ji...@apache.org>]

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

Ivan Pavlukhin commented on IGNITE-12201:
-----------------------------------------

Hi [~shm],

What kind of fix do you expect for this issue? Will it be enough to update documentation?

As my understanding goes Ignite requires that index exists on a table column which is used to be "joined" remotely. As for replicated caches, each node has all table data so there is no need to do remote lookups for replicated tables. I agree here that limitations here are not trivial and should be documented well.

> distributed sql join not working as mentioned in documentation
> --------------------------------------------------------------
>
>                 Key: IGNITE-12201
>                 URL: https://issues.apache.org/jira/browse/IGNITE-12201
>             Project: Ignite
>          Issue Type: Bug
>          Components: sql
>    Affects Versions: 2.7
>         Environment: Kubernetes on RHEL 7.6
>            Reporter: shivakumar
>            Priority: Major
>         Attachments: distributed_sql_error.txt
>
>
> I am trying to do a simple cross join on two tables with non-collocated data (without affinity key), 
> This non-collocated distributed join always fails with the error message:
>  
> *"java.sql.SQLException: javax.cache.CacheException: Failed to prepare distributed join query: join condition does not use index "*
>  
> If I create one of the tables in replicated mode and another one in partitioned mode this Join operation works but documentation mentions that Ignite supports non-collocated joins without any condition.
> And we tried with 3 tables and 1 in replicated and other 2 in partitioned then we observed that it failed.
> we are running the Join operations with *distributedJoins=true.*
> *We observed that if there are N tables in Join operation then (N-1) should be in replicated mode, is our understanding right?*
> *If our understanding is correct then to do Join operation the dimensioning of cluster increases by many folds which can't be used in a production environment.*
> *To reproduce:*
> *Ignite with 4 node cluster with native persistence enabled.*
> *create the following tables*
> {quote} {{CREATE TABLE City (}}{quote}
> {quote} {{  id LONG PRIMARY KEY, name VARCHAR)}}{quote}
> {quote} {{  WITH "backup=1";}}{quote}
> {quote} {{}}{quote}
> {quote} {{CREATE TABLE Person (}}
>  {{  id LONG, name VARCHAR, city_id LONG, PRIMARY KEY (id, city_id))}}
>  {{  WITH "backups=1";}}
>  {{}}
>  {{CREATE INDEX idx_city_name ON City (name);}}
>  {{}}
>  {{CREATE INDEX idx_person_name ON Person (name);}}
>  
>  {{INSERT INTO City (id, name) VALUES (1, 'Forest Hill');}}
>  {{INSERT INTO City (id, name) VALUES (2, 'Denver');}}
>  {{INSERT INTO City (id, name) VALUES (3, 'St. Petersburg');}}
>  {{}}
>  {{INSERT INTO Person (id, name, city_id) VALUES (1, 'John Doe', 3);}}
>  {{INSERT INTO Person (id, name, city_id) VALUES (2, 'Jane Roe', 2);}}
>  {{INSERT INTO Person (id, name, city_id) VALUES (3, 'Mary Major', 1);}}
>  {{INSERT INTO Person (id, name, city_id) VALUES (4, 'Richard Miles', 2);}} {{
> }}{quote}
> Query to be run:
> {quote}select * from City c, Person p;{color:#666666}
> {color}{quote}
> {quote}or 
> {color:#800000}*SELECT*{color} * *{color:#800000}FROM{color}* City *{color:#800000}AS{color}* c *{color:#800000}CROSS{color}* *{color:#800000}join{color}* Person *{color:#800000}AS{color}* p;{quote}



--
This message was sent by Atlassian Jira
(v8.3.4#803005)