[
https://issues.apache.org/jira/browse/IGNITE-7822?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16881426#comment-16881426
]
Ilya Kasnacheev commented on IGNITE-7822:
-----------------------------------------
Minimal reproducer as follows:
create table a (id int primary key, name varchar);
create table aa (id int primary key, a_id int);
create table ab (id int primary key, a_id int);
select ab.id from (select id, name from a where id = 1 union select id, name
from a where id = 2) a left join aa on a.id = aa.a_id left join ab on a.id =
ab.a_id;
> SQL Query with union and left join produces "Column not found" error
> --------------------------------------------------------------------
>
> Key: IGNITE-7822
> URL: https://issues.apache.org/jira/browse/IGNITE-7822
> Project: Ignite
> Issue Type: Bug
> Components: sql
> Affects Versions: 2.1, 2.3
> Reporter: Pavel Vinokurov
> Assignee: Ilya Kasnacheev
> Priority: Major
> Time Spent: 10m
> Remaining Estimate: 0h
>
> Initial script:
>
> CREATE TABLE Person (id INTEGER PRIMARY KEY, company_id INTEGER, salary
> DECIMAL);
> CREATE TABLE Company (id INTEGER PRIMARY KEY, name VARCHAR);
> CREATE TABLE Company_Value (id INTEGER PRIMARY KEY, company_id INTEGER,
> market_value DECIMAL);
> INSERT INTO Person (id, company_id, salary) VALUES (1, 1, 100), (2, 2, 200),
> (3, 3, 300);
> INSERT INTO Company (id, name) VALUES (1, 'n1'), (2, 'n2'), (3, 'n3');
> INSERT INTO Company_Value (id, company_id, market_value) VALUES (1, 1,
> 10000), (2, 2, 20000), (3, 3, 30000);
> CREATE TABLE Address (id INTEGER PRIMARY KEY, person_id INTEGER, city
> VARCHAR);
>
> Query:
> SELECT a.id FROM (SELECT p1.id as pid, p1.salary, p1.company_id
> FROM Person p1 WHERE p1.id = 1 UNION SELECT p2.id as pid,
> p2.salary, p2.company_id FROM Person p2 WHERE p2.id = 2) p LEFT
> JOIN Company c ON p.company_id = c.id LEFT JOIN Company_Value cv ON c.id =
> cv.company_id LEFT JOIN Address a ON a.person_id = p.pid;
>
> Result:
> Exception:Caused by: org.h2.jdbc.JdbcSQLException: Column "P__Z2.ID" not
> found; SQL statement:SELECTC__Z3.ID __C2_0FROM PUBLIC.COMPANY C__Z3 LEFT
> OUTER JOIN PUBLIC.COMPANY_VALUE CV__Z4 ON C__Z3.ID = CV__Z4.COMPANY_ID LEFT
> OUTER JOIN PUBLIC.ADDRESS A__Z5 ON A__Z5.PERSON_ID = P__Z2.IDORDER BY 1
> [42122-195]
>
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
