ignite-issues mailing list archives

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

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

Pavel Vinokurov updated IGNITE-10110:
-------------------------------------
    Description: 
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:

{code:java}
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
{code}


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

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


> 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:
> {code:java}
> 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
> {code}
> 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