ignite-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "shivakumar (Jira)" <j...@apache.org>
Subject [jira] [Updated] (IGNITE-12201) distributed sql join not working as mentioned in documentation
Date Thu, 19 Sep 2019 13:27:00 GMT

     [ 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)

Mime
View raw message