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

Pavel Vinokurov commented on IGNITE-7785:
-----------------------------------------

Another one reproducer with GROUP BY:

CREATE TABLE Person (id INTEGER PRIMARY KEY, company_id INTEGER, salary 
DECIMAL);
CREATE TABLE Company (id INTEGER PRIMARY KEY, name VARCHAR);
CREATE TABLE Company_Value (id INTEGER PRIMARY KEY, company_id INTEGER, 
market_value DECIMAL);

INSERT INTO Person (id, company_id, salary) VALUES (1, 1, 100), (2, 2, 200), 
(3, 3, 300);
INSERT INTO Company (id, name) VALUES (1, 'n1'), (2, 'n2'), (3, 'n3');
INSERT INTO Company_Value (id, company_id, market_value) VALUES (1, 1, 10000), 
(2, 2, 20000), (3, 3, 30000); CREATE TABLE Address (id INTEGER PRIMARY KEY, 
person_id INTEGER, city VARCHAR);INSERT INTO Person (id, company_id, salary) 
VALUES (1, 1, 100), (2, 2, 200), (3, 3, 300);INSERT INTO Address (id, 
person_id, city) VALUES (1, 1, 'san francisco'), (2, 2, 'paris'), (3, 3, 'new 
york');INSERT INTO Company (id, name) VALUES (1, 'n1'), (2, 'n2'), (3, 
'n3');INSERT INTO Company_Value (id, company_id, market_value) VALUES (1, 1, 
10000), (2, 2, 20000), (3, 3, 30000);

 

SELECT a.idFROM  (SELECT     p1.id as pid,     p1.salary,     p1.company_id   
FROM Person p1   WHERE p1.id = 1   UNION   SELECT     p2.id as pid,     
p2.salary,     p2.company_id   FROM Person p2   WHERE p2.id = 2)  p  LEFT JOIN 
Company c ON p.company_id = c.id  LEFT JOIN Company_Value cv ON c.id = 
cv.company_id  LEFT JOIN Address a ON a.person_id = p.pid;

 

Caused by: org.h2.jdbc.JdbcSQLException: Column "P__Z2.ID" not found; SQL 
statement:SELECTC__Z3.ID __C2_0FROM PUBLIC.COMPANY C__Z3  LEFT OUTER JOIN 
PUBLIC.COMPANY_VALUE CV__Z4  ON C__Z3.ID = CV__Z4.COMPANY_ID  LEFT OUTER JOIN 
PUBLIC.ADDRESS A__Z5  ON A__Z5.PERSON_ID = P__Z2.IDORDER BY 1 [42122-195]

 

 

 

 

> SQL query with COUNT and UNION in sub-query produces JdbcSQLException
> ---------------------------------------------------------------------
>
>                 Key: IGNITE-7785
>                 URL: https://issues.apache.org/jira/browse/IGNITE-7785
>             Project: Ignite
>          Issue Type: Bug
>          Components: sql
>    Affects Versions: 2.1, 2.3
>            Reporter: Pavel Vinokurov
>            Priority: Major
>
> SQL initial script:
> CREATE TABLE Person(id INTEGER PRIMARY KEY, company_id INTEGER);
>  CREATE TABLE Company(id INTEGER PRIMARY KEY, name VARCHAR);
>  INSERT INTO Person(id,company_id) VALUES (1, 1), (2, 2), (3, 3);
>  INSERT INTO Company(id,name) VALUES (1,'n1'), (2,'n2'), (3,'n3');
> SQL Query:
> SELECT count(1) FROM person p
>  LEFT JOIN (select id from company union select id from company) as c on 
> c.id=p.company_id
> JDBC Exception:
> Caused by: org.h2.jdbc.JdbcSQLException: Column "P__Z0.COMPANY_ID" must be in 
> the GROUP BY list; SQL statement:
> SELECT
> P__Z0.COMPANY_ID __C0_0,
> COUNT(1) __C0_1
> FROM PUBLIC.PERSON P__Z0 [90016-195]
>  



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

Reply via email to