Hi Timothy, Thank you very much for your reply.
Regards, Charuta On Fri, Oct 13, 2017 at 9:53 AM, Timothy Farkas <[email protected]> wrote: > 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 >
