From issues-return-97179-archive-asf-public=cust-asf.ponee.io@ignite.apache.org Fri Jul 12 13:15:02 2019 Return-Path: X-Original-To: archive-asf-public@cust-asf.ponee.io Delivered-To: archive-asf-public@cust-asf.ponee.io Received: from mail.apache.org (hermes.apache.org [207.244.88.153]) by mx-eu-01.ponee.io (Postfix) with SMTP id E1C631802C7 for ; Fri, 12 Jul 2019 15:15:01 +0200 (CEST) Received: (qmail 4976 invoked by uid 500); 12 Jul 2019 13:15:01 -0000 Mailing-List: contact issues-help@ignite.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: dev@ignite.apache.org Delivered-To: mailing list issues@ignite.apache.org Received: (qmail 4963 invoked by uid 99); 12 Jul 2019 13:15:01 -0000 Received: from mailrelay1-us-west.apache.org (HELO mailrelay1-us-west.apache.org) (209.188.14.139) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 12 Jul 2019 13:15:01 +0000 Received: from jira-lw-us.apache.org (unknown [207.244.88.139]) by mailrelay1-us-west.apache.org (ASF Mail Server at mailrelay1-us-west.apache.org) with ESMTP id 9D95EE2A06 for ; Fri, 12 Jul 2019 13:15:00 +0000 (UTC) Received: from jira-lw-us.apache.org (localhost [127.0.0.1]) by jira-lw-us.apache.org (ASF Mail Server at jira-lw-us.apache.org) with ESMTP id 573D326583 for ; Fri, 12 Jul 2019 13:15:00 +0000 (UTC) Date: Fri, 12 Jul 2019 13:15:00 +0000 (UTC) From: "Ilya Kasnacheev (JIRA)" To: issues@ignite.apache.org Message-ID: In-Reply-To: References: Subject: [jira] [Commented] (IGNITE-7822) SQL Query with union and left join produces "Column not found" error MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394 [ https://issues.apache.org/jira/browse/IGNITE-7822?page=3Dcom.atlassia= n.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=3D168= 83798#comment-16883798 ]=20 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-j= oin-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: > =C2=A0 > CREATE TABLE Person (id INTEGER PRIMARY KEY, company_id INTEGER, salary D= ECIMAL); > CREATE TABLE Company (id INTEGER PRIMARY KEY, name VARCHAR); > CREATE TABLE Company_Value (id INTEGER PRIMARY KEY, company_id INTEGER, m= arket_value DECIMAL); > INSERT INTO Person (id, company_id, salary) VALUES (1, 1, 100), (2, 2, 20= 0), (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, 10= 000), (2, 2, 20000), (3, 3, 30000); > CREATE TABLE Address (id INTEGER PRIMARY KEY, person_id INTEGER, city VAR= CHAR); > =C2=A0 > Query: > SELECT a.id FROM =C2=A0(SELECT =C2=A0 =C2=A0 p1.id as pid, =C2=A0 =C2=A0 = p1.salary, =C2=A0 =C2=A0 p1.company_id =C2=A0 FROM Person p1 =C2=A0 WHERE p= 1.id =3D 1 =C2=A0 UNION =C2=A0 SELECT =C2=A0 =C2=A0 p2.id as pid, =C2=A0 = =C2=A0 p2.salary, =C2=A0 =C2=A0 p2.company_id =C2=A0 FROM Person p2 =C2=A0 = WHERE p2.id =3D 2) =C2=A0p =C2=A0LEFT JOIN Company c ON p.company_id =3D c.= id =C2=A0LEFT JOIN Company_Value cv ON c.id =3D cv.company_id =C2=A0LEFT JO= IN Address a ON a.person_id =3D p.pid; > =C2=A0 > 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 =C2=A0L= EFT OUTER JOIN PUBLIC.COMPANY_VALUE CV__Z4 =C2=A0ON C__Z3.ID =3D CV__Z4.COM= PANY_ID =C2=A0LEFT OUTER JOIN PUBLIC.ADDRESS A__Z5 =C2=A0ON A__Z5.PERSON_ID= =3D P__Z2.IDORDER BY 1 [42122-195] > =C2=A0 -- This message was sent by Atlassian JIRA (v7.6.14#76016)