ignite-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Pavel Vinokurov (JIRA)" <j...@apache.org>
Subject [jira] [Comment Edited] (IGNITE-8072) SQL query with multiple JOIN and subquery with UNION produces "Column not found" exception
Date Thu, 29 Mar 2018 04:17:00 GMT

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

Mime
View raw message