ignite-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Evgenii Zhuravlev <e.zhuravlev...@gmail.com>
Subject Re: distributed sql join not working as mentioned in documentation
Date Mon, 23 Sep 2019 10:27:35 GMT
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.eng@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;
>
>
>
>

Mime
View raw message