[
https://issues.apache.org/jira/browse/IGNITE-10110?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16671457#comment-16671457
]
Pavel Vinokurov commented on IGNITE-10110:
------------------------------------------
Simplified query:
SELECT
last_name
FROM
( SELECT
DISTINCT last_name,
person_id,
company_id
FROM Person
) src
INNER JOIN
department dep
ON src.person_id = dep.person_id
LEFT JOIN
organization og
ON src.company_id = og.company_id
> 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
> Priority: Major
> Labels: sql
>
> 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)
