I can try that, thanks a lot! On Fri, Oct 13, 2017 at 11:06 PM, Kunal Khatua <[email protected]> wrote:
> 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 > > >
