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

2018-11-01 Thread Pavel Vinokurov (JIRA) 


 [ 
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)

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

2018-11-01 Thread Pavel Vinokurov (JIRA) 


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

{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


> 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)