[IGNITE-12201] distributed sql join not working as mentioned in documentation

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 2.7
• Component/s: sql

Environment

Kubernetes on RHEL 7.6

• Ignite Flags: Docs Required, Release Notes Required
• Assignee: Unassigned
• Reporter: shivakumar
• Created: 19/Sep/19 13:26
• Updated: 30/Sep/19 08:33

Description

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

 CREATE TABLE City (

 {{ id LONG PRIMARY KEY, name VARCHAR)}}

 {{ WITH "backup=1";}}

 {{}}

 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); {{
}}

Query to be run:

select * from City c, Person p;

or 
SELECT  FROM City AS c CROSS join Person AS* p;