[jira] [Comment Edited] (IGNITE-8072) SQL query with multiple JOIN and subquery with UNION produces "Column not found" exception
[ https://issues.apache.org/jira/browse/IGNITE-8072?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16418417#comment-16418417 ] Pavel Vinokurov edited comment on IGNITE-8072 at 3/29/18 4:16 AM: -- There are two workarounds 1) select distinct(p.id),* from ( select p.id as id, p.company_id as company_id from person p union ALL select p.id as id, p.company_id as company_id from person p ) p left join company c on p.company_id=c.id left join department d on p.company_id=d.id 2) select distinct(p.id),* from ( select p.id as id, p.company_id as company_id from person p union ALL select p.id as id, p.company_id as company_id from person p ) p left join company c left join department d where p.company_id=c.id and p.company_id=d.id was (Author: pvinokurov): There are two workarounds 1) select distinct(p.id),* from ( select p.id as id, p.company_id as company_id from person p union ALL select p.id as id, p.company_id as company_id from person p ) p left join company c on p.company_id=c.id left join department d on p.company_id=d.id 2) select distinct(p.id),* from ( select p.id as id, p.company_id as company_id from person p union ALL select p.id as id, p.company_id as company_id from person p ) p left join company c left join department d where p.company_id=c.id and p.company_id=d.id > SQL query with multiple JOIN and subquery with UNION produces "Column not > found" exception > -- > > Key: IGNITE-8072 > URL: https://issues.apache.org/jira/browse/IGNITE-8072 > Project: Ignite > Issue Type: Bug > Components: sql >Affects Versions: 2.1, 2.3, 2.4 >Reporter: Pavel Vinokurov >Priority: Major > > Initial script: > CREATE TABLE Person(id INTEGER PRIMARY KEY, company_id INTEGER); > CREATE TABLE Company(id INTEGER PRIMARY KEY, name VARCHAR); > CREATE TABLE Department(id INTEGER PRIMARY KEY, company_id INTEGER); > Query: > select * from > ( > select >p.id as id, >p.company_id as company_id >from person p >union >select >p.id as id, >p.company_id as company_id >from person p > ) p > left join company c on p.company_id=c.id > left join department d on p.company_id=d.id > Result: > SEVERE: Failed to run map query on local node. > class org.apache.ignite.IgniteCheckedException: Failed to parse SQL query: > SELECT > C__Z3.NAME __C2_0, > D__Z4.ID __C2_1, > D__Z4.COMPANY_ID __C2_2, > C__Z3.ID __C2_3 > FROM PUBLIC.COMPANY C__Z3 > LEFT OUTER JOIN PUBLIC.DEPARTMENT D__Z4 > ON P__Z2.COMPANY_ID = D__Z4.ID > ORDER BY 4 -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Comment Edited] (IGNITE-8072) SQL query with multiple JOIN and subquery with UNION produces "Column not found" exception
[ https://issues.apache.org/jira/browse/IGNITE-8072?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16418417#comment-16418417 ] Pavel Vinokurov edited comment on IGNITE-8072 at 4/2/18 8:35 AM: - There are two workarounds 1) select distinct(p.id),* from ( select p.id as id, p.company_id as company_id from person p union ALL select p.id as id, p.company_id as company_id from person p ) p left join company c on p.company_id=c.id left join department d on p.company_id=d.id 2) select * from ( select p.id as id, p.company_id as company_id from person p union select p.id as id, p.company_id as company_id from person p ) p left join company c left join department d where p.company_id=c.id and p.company_id=d.id was (Author: pvinokurov): There are two workarounds 1) select distinct(p.id),* from ( select p.id as id, p.company_id as company_id from person p union ALL select p.id as id, p.company_id as company_id from person p ) p left join company c on p.company_id=c.id left join department d on p.company_id=d.id 2) select distinct(p.id),* from ( select p.id as id, p.company_id as company_id from person p union ALL select p.id as id, p.company_id as company_id from person p ) p left join company c left join department d where p.company_id=c.id and p.company_id=d.id > SQL query with multiple JOIN and subquery with UNION produces "Column not > found" exception > -- > > Key: IGNITE-8072 > URL: https://issues.apache.org/jira/browse/IGNITE-8072 > Project: Ignite > Issue Type: Bug > Components: sql >Affects Versions: 2.1, 2.3, 2.4 >Reporter: Pavel Vinokurov >Priority: Major > > Initial script: > CREATE TABLE Person(id INTEGER PRIMARY KEY, company_id INTEGER); > CREATE TABLE Company(id INTEGER PRIMARY KEY, name VARCHAR); > CREATE TABLE Department(id INTEGER PRIMARY KEY, company_id INTEGER); > Query: > select * from > ( > select >p.id as id, >p.company_id as company_id >from person p >union > select >p.id as id, >p.company_id as company_id >from person p > ) p > left join company c on p.company_id=c.id > left join department d on p.company_id=d.id > Result: > SEVERE: Failed to run map query on local node. > class org.apache.ignite.IgniteCheckedException: Failed to parse SQL query: > SELECT > C__Z3.NAME __C2_0, > D__Z4.ID __C2_1, > D__Z4.COMPANY_ID __C2_2, > C__Z3.ID __C2_3 > FROM PUBLIC.COMPANY C__Z3 > LEFT OUTER JOIN PUBLIC.DEPARTMENT D__Z4 > ON P__Z2.COMPANY_ID = D__Z4.ID > ORDER BY 4 -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Comment Edited] (IGNITE-8072) SQL query with multiple JOIN and subquery with UNION produces "Column not found" exception
[ https://issues.apache.org/jira/browse/IGNITE-8072?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16418661#comment-16418661 ] Pavel Vinokurov edited comment on IGNITE-8072 at 4/2/18 8:59 AM: - 3) SqlFieldsQuery#setLocal(true) and the query works fine. was (Author: pvinokurov): 3) SqlFieldsQuery#setLocal(true) an the query works fine. > SQL query with multiple JOIN and subquery with UNION produces "Column not > found" exception > -- > > Key: IGNITE-8072 > URL: https://issues.apache.org/jira/browse/IGNITE-8072 > Project: Ignite > Issue Type: Bug > Components: sql >Affects Versions: 2.1, 2.3, 2.4 >Reporter: Pavel Vinokurov >Priority: Major > > Initial script: > CREATE TABLE Person(id INTEGER PRIMARY KEY, company_id INTEGER); > CREATE TABLE Company(id INTEGER PRIMARY KEY, name VARCHAR); > CREATE TABLE Department(id INTEGER PRIMARY KEY, company_id INTEGER); > Query: > select * from > ( > select >p.id as id, >p.company_id as company_id >from person p >union > select >p.id as id, >p.company_id as company_id >from person p > ) p > left join company c on p.company_id=c.id > left join department d on p.company_id=d.id > Result: > SEVERE: Failed to run map query on local node. > class org.apache.ignite.IgniteCheckedException: Failed to parse SQL query: > SELECT > C__Z3.NAME __C2_0, > D__Z4.ID __C2_1, > D__Z4.COMPANY_ID __C2_2, > C__Z3.ID __C2_3 > FROM PUBLIC.COMPANY C__Z3 > LEFT OUTER JOIN PUBLIC.DEPARTMENT D__Z4 > ON P__Z2.COMPANY_ID = D__Z4.ID > ORDER BY 4 -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Comment Edited] (IGNITE-8072) SQL query with multiple JOIN and subquery with UNION produces "Column not found" exception
[ https://issues.apache.org/jira/browse/IGNITE-8072?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16423393#comment-16423393 ] Pavel Vinokurov edited comment on IGNITE-8072 at 4/3/18 3:43 AM: - If the one of caches is replicated, the query work on the 1.9.1 version. was (Author: pvinokurov): If the cache is replicated, the query work on the 1.9.1 version. > SQL query with multiple JOIN and subquery with UNION produces "Column not > found" exception > -- > > Key: IGNITE-8072 > URL: https://issues.apache.org/jira/browse/IGNITE-8072 > Project: Ignite > Issue Type: Bug > Components: sql >Affects Versions: 2.1, 2.3, 2.4 >Reporter: Pavel Vinokurov >Priority: Major > > Initial script: > CREATE TABLE Person(id INTEGER PRIMARY KEY, company_id INTEGER); > CREATE TABLE Company(id INTEGER PRIMARY KEY, name VARCHAR); > CREATE TABLE Department(id INTEGER PRIMARY KEY, company_id INTEGER); > Query: > select * from > ( > select >p.id as id, >p.company_id as company_id >from person p >union > select >p.id as id, >p.company_id as company_id >from person p > ) p > left join company c on p.company_id=c.id > left join department d on p.company_id=d.id > Result: > SEVERE: Failed to run map query on local node. > class org.apache.ignite.IgniteCheckedException: Failed to parse SQL query: > SELECT > C__Z3.NAME __C2_0, > D__Z4.ID __C2_1, > D__Z4.COMPANY_ID __C2_2, > C__Z3.ID __C2_3 > FROM PUBLIC.COMPANY C__Z3 > LEFT OUTER JOIN PUBLIC.DEPARTMENT D__Z4 > ON P__Z2.COMPANY_ID = D__Z4.ID > ORDER BY 4 -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Comment Edited] (IGNITE-8072) SQL query with multiple JOIN and subquery with UNION produces "Column not found" exception
[ https://issues.apache.org/jira/browse/IGNITE-8072?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16423393#comment-16423393 ] Pavel Vinokurov edited comment on IGNITE-8072 at 4/3/18 4:43 AM: - If the one of caches is replicated, the query work on the 1.9 version. was (Author: pvinokurov): If the one of caches is replicated, the query work on the 1.9.1 version. > SQL query with multiple JOIN and subquery with UNION produces "Column not > found" exception > -- > > Key: IGNITE-8072 > URL: https://issues.apache.org/jira/browse/IGNITE-8072 > Project: Ignite > Issue Type: Bug > Components: sql >Affects Versions: 2.1, 2.3, 2.4 >Reporter: Pavel Vinokurov >Priority: Major > > Initial script: > CREATE TABLE Person(id INTEGER PRIMARY KEY, company_id INTEGER); > CREATE TABLE Company(id INTEGER PRIMARY KEY, name VARCHAR); > CREATE TABLE Department(id INTEGER PRIMARY KEY, company_id INTEGER); > Query: > select * from > ( > select >p.id as id, >p.company_id as company_id >from person p >union > select >p.id as id, >p.company_id as company_id >from person p > ) p > left join company c on p.company_id=c.id > left join department d on p.company_id=d.id > Result: > SEVERE: Failed to run map query on local node. > class org.apache.ignite.IgniteCheckedException: Failed to parse SQL query: > SELECT > C__Z3.NAME __C2_0, > D__Z4.ID __C2_1, > D__Z4.COMPANY_ID __C2_2, > C__Z3.ID __C2_3 > FROM PUBLIC.COMPANY C__Z3 > LEFT OUTER JOIN PUBLIC.DEPARTMENT D__Z4 > ON P__Z2.COMPANY_ID = D__Z4.ID > ORDER BY 4 -- This message was sent by Atlassian JIRA (v7.6.3#76005)