drill-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Charuta Rajopadhye <charuta...@gmail.com>
Subject Re: Newbie: Join queries in MySQL.
Date Fri, 13 Oct 2017 06:28:03 GMT
Hi Timothy,

Thank you very much for your reply.

Regards,
Charuta

On Fri, Oct 13, 2017 at 9:53 AM, Timothy Farkas <tfarkas@mapr.com> 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 <charutar90@gmail.com>
> Sent: Thursday, October 12, 2017 2:16:10 AM
> To: dev@drill.apache.org
> 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
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message