[jira] [Created] (DRILL-4684) Incorrect behavior when joining RDBMS table on itself

["Egbert van der Veen (JIRA)" <ji...@apache.org>]

Egbert van der Veen created DRILL-4684:
------------------------------------------

             Summary: 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


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)