Hi Evgenii, Even with *where condition*, I am getting the same error. I have some use case where I can't collocate tables data, as Ignite doc says non-collocated distributed join or cross join is supported in Ignite I am trying to use that but getting this exception when I create tables in replicated mode. I have filed a bug https://issues.apache.org/jira/browse/IGNITE-12201
regards, shiva On Mon, Sep 23, 2019 at 3:57 PM Evgenii Zhuravlev <e.zhuravlev...@gmail.com> wrote: > Hi, > > To make work this query, you can add one where clause or join condition in > the query, for example: where c.id = city_id;. I don't really understand > why do you want to run a fully distributed cross join on these tables - it > doesn't make sense, moreover, it will lead to the a lot of data movement > between nodes. > > What are you trying to achieve? > > Best Regards, > Evgenii > > чт, 19 сент. 2019 г. в 16:18, Shiva Kumar <shivakumar....@gmail.com>: > >> Hi all, >> 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; >> >> >> >>
