drill-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Milind Utsav <milind.ut...@yfret.com>
Subject Erroneous results in Drill with JOIN and CAST
Date Tue, 09 Feb 2016 12:54:43 GMT
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