Mailing-List: contact issues-help@drill.apache.org; run by ezmlm
Precedence: bulk
Reply-To: dev@drill.apache.org
Date: Wed, 18 May 2016 09:10:12 +0000 (UTC)
From: "Egbert van der Veen (JIRA)" <jira@apache.org>
To: issues@drill.apache.org
Message-ID: <JIRA.12970685.1463562423000.221799.1463562612856@Atlassian.JIRA>
In-Reply-To: <JIRA.12970685.1463562423000@Atlassian.JIRA>
References: <JIRA.12970685.1463562423000@Atlassian.JIRA> <JIRA.12970685.1463562423780@arcas>
Subject: [jira] [Updated] (DRILL-4684) Incorrect behavior when joining RDBMS
 table on itself
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit
archived-at: Wed, 18 May 2016 09:10:14 -0000


     [ https://issues.apache.org/jira/browse/DRILL-4684?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Egbert van der Veen updated DRILL-4684:
---------------------------------------
    Attachment: actual result.png
                expected result.png

> Incorrect behavior when joining RDBMS table on itself
> -----------------------------------------------------
>
>                 Key: DRILL-4684
>                 URL: https://issues.apache.org/jira/browse/DRILL-4684
>             Project: Apache Drill
>          Issue Type: Bug
>            Reporter: Egbert van der Veen
>         Attachments: actual result.png, expected result.png
>
>
> When joining a RDBMS table on itself (for instance; with a table that contains both subitems and main items), Drill fails to correctly retrieve the result columns. This has been tested using both MySQL and PostgreSQL databases as storage providers.
> Code to reproduce:
> ```
> CREATE TABLE `join_test` (
>   `id` int(11) NOT NULL,
>   `type` varchar(50) DEFAULT NULL,
>   `mainItem` int(11) DEFAULT NULL,
>   `description` varchar(255) DEFAULT NULL,
>   PRIMARY KEY (`id`),
>   UNIQUE KEY `id` (`id`)
> ) ;
> INSERT INTO join_test (id, type, mainItem, description) VALUES (1, 'mainItem', null, 'First main item');
> INSERT INTO join_test (id, type, mainItem, description) VALUES (2, 'subItem', 1, 'First subitem of first main item');
> INSERT INTO join_test (id, type, mainItem, description) VALUES (3, 'subItem', 1, 'Second subitem of first main item');
> SELECT main.type firsttype, main.description firstdescription, sub.type secondtype, sub.description seconddescription 
> FROM join_test sub
> JOIN join_test main ON sub.mainItem = main.id
> ``` 


--
This message was sent by Atlassian JIRA
(v6.3.4#6332)