[jira] [Commented] (IGNITE-7785) SQL query with COUNT and UNION in sub-query produces JdbcSQLException
[ https://issues.apache.org/jira/browse/IGNITE-7785?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16385631#comment-16385631 ] Pavel Vinokurov commented on IGNITE-7785: - Workaround: SELECT count(1) FROM (select * from person) p LEFT JOIN (select id from company union select id from company) as c on c.id=p.company_id > 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)
[jira] [Commented] (IGNITE-7785) SQL query with COUNT and UNION in sub-query produces JdbcSQLException
[
https://issues.apache.org/jira/browse/IGNITE-7785?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16378534#comment-16378534
]
Alexander Paschenko commented on IGNITE-7785:
-
[~pvinokurov],
Initial query is a bit weird as long as it has union of a table with itself.
In absence of {{UNION ALL}} clause, that subquery
{{(select id from company union all select id from company)}}
should give same result as single {{SELECT id FROM Company}}, and thus in
initial case that UNION is redundant - if you remove UNION, query works.
However, if you add ALL to UNION, query works - however, in this case UNION has
duplicates just like it should.
I will also have a look at second query.
> 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)
[jira] [Commented] (IGNITE-7785) SQL query with COUNT and UNION in sub-query produces JdbcSQLException
[ https://issues.apache.org/jira/browse/IGNITE-7785?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=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, 1), (2, 2, 2), (3, 3, 3); 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, 1), (2, 2, 2), (3, 3, 3); 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)
