ignite-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Pavel Vinokurov (JIRA)" <j...@apache.org>
Subject [jira] [Created] (IGNITE-10110) SQL query with DISTINCT and JOIN in suquery produces "Column not found"
Date Thu, 01 Nov 2018 09:51:00 GMT
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)

Mime
View raw message