[
https://issues.apache.org/jira/browse/IGNITE-12201?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
shivakumar updated IGNITE-12201:
--------------------------------
Attachment: distributed_sql_error.txt
> 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
> Priority: Major
> 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)
