ignite-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Ilya Kasnacheev (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (IGNITE-7822) SQL Query with union and left join produces "Column not found" error
Date Tue, 09 Jul 2019 17:08:00 GMT

     [ https://issues.apache.org/jira/browse/IGNITE-7822?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Ilya Kasnacheev updated IGNITE-7822:
------------------------------------
    Description: 
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]
 

  was:
 
 
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]
 


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

Mime
View raw message