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] [Comment Edited] (IGNITE-10110) SQL query with DISTINCT and JOIN in suquery produces "Column not found"
Date Thu, 01 Nov 2018 10:49:00 GMT

    [ https://issues.apache.org/jira/browse/IGNITE-10110?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16671457#comment-16671457
] 

Pavel Vinokurov edited comment on IGNITE-10110 at 11/1/18 10:48 AM:
--------------------------------------------------------------------

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


was (Author: pvinokurov):
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)

Mime
View raw message