You could try creating simple views (with different column names) on those tables. That way, you don’t have to try changing the column names in the table itself.
-----Original Message----- From: Charuta Rajopadhye [mailto:[email protected]] Sent: Thursday, October 12, 2017 11:28 PM To: [email protected] Subject: Re: Newbie: Join queries in MySQL. 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 >
