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

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

shivakumar created IGNITE-12201:
-----------------------------------

             Summary: 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
         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)