drill-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jacques Nadeau <jacq...@dremio.com>
Subject Re: Erroneous results in Drill with JOIN and CAST
Date Tue, 09 Feb 2016 13:54:07 GMT
You need to provide a length for varchar, otherwise you are truncating your
values. Varchar without a length is equal to varchar(1).

Try varchar(255) in your casts.
On Feb 9, 2016 4:57 AM, "Milind Utsav" <milind.utsav@yfret.com> wrote:

> Hello,
>
> I am trying to deploy Drill on production systems, and am facing a problem
> with JOIN between Drill table and Mongo table.
> I'll explain the problem using some sample data.
>
> *users*
>
> {"_id": "36479360", "email": "t1@domain.com"}
> {"_id": "36479361", "email": "t2@domain.com"}
> {"_id": "36479362", "email": "t3@domain.com"}
> {"_id": "36479363", "email": "t4@domain.com"}
> {"_id": "36479364", "email": "t5@domain.com"}
>
> *actions*
>
> {"_id": "374629", "email": "t1@domain.com", "action_type": ""}
> {"_id": "374630", "email": "t2@domain.com", "action_type": "view"}
> {"_id": "374631", "email": "t3@domain.com", "action_type": "abandoned"}
> {"_id": "374632", "email": "t4@domain.com", "action_type": "view"}
> {"_id": "374633", "email": "t5@domain.com", "action_type": "abandoned"}
>
> Now, when I try to do a JOIN on these tables and query the data, the
> results are different from what is expected. See the query below :
>
> drill>  SELECT DISTINCT U.email FROM actions AS A JOIN users AS U ON
> CAST(A.email AS VARCHAR) = CAST(U.email AS VARCHAR) WHERE
> A.action_type='view';
>
> +-----------------------+
> |          email           |
> +-----------------------+
> | t1@domain.com  |
> | t2@domain.com  |
> | t3@domain.com  |
> | t4@domain.com  |
> | t5@domain.com  |
> +-----------------------+
>
> I expect only users T2 and T4 to appear, but all the users are returned by
> the query. What am I missing here?
>
>
>
> Regards,
> --
> Milind Utsav
> +91-9742565861
>

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