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 <[email protected]>: > 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; > > > >
