Hi Charuta, This is a known issue that other people have been bumping into as well. As of now there is no work around other than changing the column names in your tables. I've filed these tickets regarding the issue but haven't gotten around to working on them yet:
https://issues.apache.org/jira/browse/DRILL-5713 https://issues.apache.org/jira/browse/DRILL-4211 Maybe someone else has some bandwidth to pick them up? Thanks, Tim ________________________________ From: Charuta Rajopadhye <[email protected]> Sent: Thursday, October 12, 2017 2:16:10 AM To: [email protected] Subject: Newbie: Join queries in MySQL. Hi Team, I have created a configuration for JDBC storage plugin in Drill, for a MySQL database. I am trying to fire join query on 2 tables. Both my tables have a column called 'id'/ Table defination: CREATE TABLE `simpleone` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` text NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 CREATE TABLE `simpletwo` ( `id` int(11) NOT NULL AUTO_INCREMENT, `city` text NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 because of the redundant ‘id’ column, i get values from one ‘id’ column as NULL. I tried using a few permutations to fire my queries: 0: jdbc:drill:zk=local>* select * from **pluginjdbc**.drillTest.simpleone, * *pluginjdbc**.drillTest.simpletwo where simpleone.id <http://simpleone.id> = simpletwo.id <http://simpletwo.id>;* +-----+-------------------+-------+----------------+ | id | name | *id0* | city | +-----+-------------------+-------+----------------+ | 1 | J. D Salinger | *null* | New York City | | 2 | Charlotte Bronte | *null* | Thornton | +-----+-------------------+-------+----------------+ 2 rows selected (0.224 seconds) 0: jdbc:drill:zk=local> *select tb1.id <http://tb1.id>, tb2.id <http://tb2.id> from pluginjdbc.drillTest.simpleone as tb1, pluginjdbc.drillTest.simpletwo as tb2 where tb1.id <http://tb1.id> = tb2.id <http://tb2.id>;* +-----+-------+ | id | id0 | +-----+-------+ | 1 | null | | 2 | null | +-----+-------+ 2 rows selected (0.368 seconds) 0: jdbc:drill:zk=local> *select tb1.id <http://tb1.id> as col1, tb2.id <http://tb2.id> as col2 from pluginjdbc.drillTest.simpleone as tb1, pluginjdbc.drillTest.simpletwo as tb2 where tb1.id <http://tb1.id> = tb2.id <http://tb2.id>;* +-------+-------+ | col1 | col2 | +-------+-------+ | 1 | null | | 2 | null | +-------+-------+ 2 rows selected (0.236 seconds) Could somebody please guide me through this? Please let me know if i am missing something. Thanks, Charuta
