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)