[jira] [Created] (IGNITE-10110) SQL query with DISTINCT and JOIN in suquery produces "Column not found"

[Pavel Vinokurov (JIRA)]

Pavel Vinokurov created IGNITE-10110:
----------------------------------------

             Summary: SQL query with DISTINCT and JOIN in suquery produces 
"Column  not found" 
                 Key: IGNITE-10110
                 URL: https://issues.apache.org/jira/browse/IGNITE-10110
             Project: Ignite
          Issue Type: Bug
          Components: sql
    Affects Versions: 2.4
            Reporter: Pavel Vinokurov


Initial script:
CREATE TABLE Person(
  person_id INTEGER PRIMARY KEY,
  company_id INTEGER,
  last_name VARCHAR(100)
);

CREATE TABLE Company(
  company_id INTEGER PRIMARY KEY,
  location_id INTEGER
);

CREATE TABLE Department(
  department_id INTEGER PRIMARY KEY,
  person_id INTEGER
);

CREATE TABLE Organization(
  organization_id INTEGER PRIMARY KEY,
  company_id INTEGER
);

Query:
SELECT
    last_name
FROM
    (  SELECT
        last_name,
        person_id,
        company_id
    FROM
        ( SELECT
            last_name,
            person_id,
            p.company_id as company_id
        FROM
            Person p
        INNER JOIN
            (
                SELECT
                    DISTINCT location_id,
                    company_id
                FROM
                    Company
                WHERE
                    location_id = 1
            ) cpy
                ON (
                    p.company_id = cpy.company_id
                )
            ) a
  ) src
INNER JOIN
    department dep
        ON src.person_id = dep.person_id
LEFT JOIN
    organization og
        ON src.company_id = og.company_id

Result:
Caused by: org.h2.jdbc.JdbcSQLException: Column "SRC__Z4.COMPANY_ID" not found; 
SQL statement:
SELECT
DEP__Z5.PERSON_ID __C2_0
FROM PUBLIC.DEPARTMENT DEP__Z5 
 LEFT OUTER JOIN PUBLIC.ORGANIZATION OG__Z6 
 ON SRC__Z4.COMPANY_ID = OG__Z6.COMPANY_ID



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)