Pavel Vinokurov created IGNITE-7822:
---------------------------------------
Summary: SQL Query with 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.3, 2.1
Reporter: Pavel Vinokurov
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);INSERT INTO Person (id, company_id, salary)
VALUES (1, 1, 100), (2, 2, 200), (3, 3, 300);INSERT INTO Address (id,
person_id, city) VALUES (1, 1, 'san francisco'), (2, 2, 'paris'), (3, 3, 'new
york');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);
Query:
SELECT a.idFROM (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)
