[jira] [Commented] (IGNITE-7822) SQL Query with union and left join produces "Column not found" error
[ https://issues.apache.org/jira/browse/IGNITE-7822?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16883798#comment-16883798 ] Ilya Kasnacheev commented on IGNITE-7822: - I have proposed a docs change about this issue: https://apacheignite-sql.readme.io/docs/how-ignite-sql-works#section-left-join-referring-to-other-tables-in-on-clause > SQL Query with union and left join produces "Column not found" error > > > Key: IGNITE-7822 > URL: https://issues.apache.org/jira/browse/IGNITE-7822 > Project: Ignite > Issue Type: Bug > Components: sql >Affects Versions: 2.1, 2.3 >Reporter: Pavel Vinokurov >Assignee: Ilya Kasnacheev >Priority: Major > Time Spent: 10m > Remaining Estimate: 0h > > Initial script: > > 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); > > Query: > SELECT a.id FROM (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; > > Result: > Exception: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] > -- This message was sent by Atlassian JIRA (v7.6.14#76016)
[jira] [Commented] (IGNITE-7822) SQL Query with union and left join produces "Column not found" error
[ https://issues.apache.org/jira/browse/IGNITE-7822?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16883101#comment-16883101 ] Ilya Kasnacheev commented on IGNITE-7822: - Workaround in this case: select ab.id from (select id, name from a where id = 1 union select id, name from a where id = 2) a left join aa on a.id = aa.a_id left join ab *where* a.id = ab.a_id; Please note that it will still produce inefficient splitted queries. > SQL Query with union and left join produces "Column not found" error > > > Key: IGNITE-7822 > URL: https://issues.apache.org/jira/browse/IGNITE-7822 > Project: Ignite > Issue Type: Bug > Components: sql >Affects Versions: 2.1, 2.3 >Reporter: Pavel Vinokurov >Assignee: Ilya Kasnacheev >Priority: Major > Time Spent: 10m > Remaining Estimate: 0h > > Initial script: > > 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); > > Query: > SELECT a.id FROM (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; > > Result: > Exception: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] > -- This message was sent by Atlassian JIRA (v7.6.14#76016)
[jira] [Commented] (IGNITE-7822) SQL Query with union and left join produces "Column not found" error
[ https://issues.apache.org/jira/browse/IGNITE-7822?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16881426#comment-16881426 ] Ilya Kasnacheev commented on IGNITE-7822: - Minimal reproducer as follows: create table a (id int primary key, name varchar); create table aa (id int primary key, a_id int); create table ab (id int primary key, a_id int); select ab.id from (select id, name from a where id = 1 union select id, name from a where id = 2) a left join aa on a.id = aa.a_id left join ab on a.id = ab.a_id; > SQL Query with union and left join produces "Column not found" error > > > Key: IGNITE-7822 > URL: https://issues.apache.org/jira/browse/IGNITE-7822 > Project: Ignite > Issue Type: Bug > Components: sql >Affects Versions: 2.1, 2.3 >Reporter: Pavel Vinokurov >Assignee: Ilya Kasnacheev >Priority: Major > Time Spent: 10m > Remaining Estimate: 0h > > Initial script: > > 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); > > Query: > SELECT a.id FROM (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; > > Result: > Exception: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] > -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Commented] (IGNITE-7822) SQL Query with union and left join produces "Column not found" error
[ https://issues.apache.org/jira/browse/IGNITE-7822?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16694574#comment-16694574 ] Taras Ledkov commented on IGNITE-7822: -- Hotfix doesn't work for huge complex query. A lot of merge table is created, H2 parser/optimizer cannot create a plan for reduce query. > SQL Query with union and left join produces "Column not found" error > > > Key: IGNITE-7822 > URL: https://issues.apache.org/jira/browse/IGNITE-7822 > Project: Ignite > Issue Type: Bug > Components: sql >Affects Versions: 2.1, 2.3 >Reporter: Pavel Vinokurov >Assignee: Taras Ledkov >Priority: Major > > > > Initial script: > > 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); > > Query: > 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; > > Result: > Exception: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] > -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Commented] (IGNITE-7822) SQL Query with union and left join produces "Column not found" error
[ https://issues.apache.org/jira/browse/IGNITE-7822?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16685054#comment-16685054 ] Taras Ledkov commented on IGNITE-7822: -- *Root cause:* the query is splitted incorrectly when the query contains more then one left joins and subquery that must be splitted (e.g. subquery contains DISTINCT, UNION or not-collocated GROUP BY). *Hotfix:* disable push down model range and push down each relation of join separately, > SQL Query with union and left join produces "Column not found" error > > > Key: IGNITE-7822 > URL: https://issues.apache.org/jira/browse/IGNITE-7822 > Project: Ignite > Issue Type: Bug > Components: sql >Affects Versions: 2.1, 2.3 >Reporter: Pavel Vinokurov >Assignee: Taras Ledkov >Priority: Major > > > > Initial script: > > 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); > > Query: > 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; > > Result: > Exception: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] > -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Commented] (IGNITE-7822) SQL Query with union and left join produces "Column not found" error
[ https://issues.apache.org/jira/browse/IGNITE-7822?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16684994#comment-16684994 ] ASF GitHub Bot commented on IGNITE-7822: GitHub user tledkov-gridgain opened a pull request: https://github.com/apache/ignite/pull/5375 IGNITE-7822: hotfix: push down query model range separately for each relation in case statement contains subquery that must be splitted You can merge this pull request into a Git repository by running: $ git pull https://github.com/gridgain/apache-ignite ignite-7822 Alternatively you can review and apply these changes as the patch at: https://github.com/apache/ignite/pull/5375.patch To close this pull request, make a commit to your master/trunk branch with (at least) the following in the commit message: This closes #5375 commit 19e2deb36e0e79dee23a92ca80f5bbb19fedbd81 Author: tledkov-gridgain Date: 2018-03-14T14:53:27Z IGNITE-7822: add test commit 99a0d5684bc8e01e11397a401e7a9af0ee7bf892 Author: tledkov-gridgain Date: 2018-03-15T09:00:06Z IGNITE-7822: save the progress commit 01cc5e6cb79bf796aeacbe8ed164ca98ee1e38f4 Author: tledkov-gridgain Date: 2018-03-15T10:15:59Z Merge branch '_master' into ignite-7822 commit 1040abe13ee5062a0f0f08cd3b85ac3f469971a2 Author: tledkov-gridgain Date: 2018-03-16T08:28:31Z IGNITE-7822: save the progress commit cb3f8d3d61e900cfa6c15c4a9727025ffedd4872 Author: tledkov-gridgain Date: 2018-03-16T09:39:16Z IGNITE-7822: save the progress commit feb16bfbdc188bf055edd8cb2b5b02c0cd0fada9 Author: tledkov-gridgain Date: 2018-03-16T09:54:29Z Merge branch '_master' into ignite-7822 commit e3e6097b6aff4901ed462d0de6afd696e1794efc Author: tledkov-gridgain Date: 2018-03-16T12:34:47Z IGNITE-7822: save the progress commit 14ed9376c10cc6d3431a5cbd953df13522cc7c87 Author: tledkov-gridgain Date: 2018-04-03T11:23:35Z Merge branch 'master' into ignite-7822 # Conflicts: # modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/SqlPushDownFunctionTest.java commit cbe65e919fe9fac9c212835704e21a6970777662 Author: tledkov-gridgain Date: 2018-04-03T11:35:17Z ignite-7822: save the progress commit e76148cab1c3d98f89590aeab5bc54324d828e8a Author: tledkov-gridgain Date: 2018-05-23T14:51:26Z Merge branch '_master' into ignite-7822 commit 8b0dbfcc20d0a04a5def87d86963763c6742ba98 Author: tledkov Date: 2018-11-09T10:16:19Z Merge branch '_master' into ignite-7822 commit 1b310e01c68a114c3f9dd1e5cb317a9a40dd7db2 Author: tledkov Date: 2018-11-09T15:32:40Z IGNITE-7822: extract test commit d9c754ddeffeb221a683a261d202aeee354a8e4d Author: tledkov Date: 2018-11-12T12:20:46Z Merge branch '_master' into ignite-7822 commit 1ff1f38849c5c9757c2879c73175d5eb04396952 Author: tledkov Date: 2018-11-13T10:30:14Z IGNITE-7822: hotfix: push down query model range separately for each relation in case statement contains subquery that must be splitted > SQL Query with union and left join produces "Column not found" error > > > Key: IGNITE-7822 > URL: https://issues.apache.org/jira/browse/IGNITE-7822 > Project: Ignite > Issue Type: Bug > Components: sql >Affects Versions: 2.1, 2.3 >Reporter: Pavel Vinokurov >Assignee: Taras Ledkov >Priority: Major > > > > Initial script: > > 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); > > Query: > 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
