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" <[email protected]> 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": "[email protected]"} > {"_id": "36479361", "email": "[email protected]"} > {"_id": "36479362", "email": "[email protected]"} > {"_id": "36479363", "email": "[email protected]"} > {"_id": "36479364", "email": "[email protected]"} > > *actions* > > {"_id": "374629", "email": "[email protected]", "action_type": ""} > {"_id": "374630", "email": "[email protected]", "action_type": "view"} > {"_id": "374631", "email": "[email protected]", "action_type": "abandoned"} > {"_id": "374632", "email": "[email protected]", "action_type": "view"} > {"_id": "374633", "email": "[email protected]", "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 | > +-----------------------+ > | [email protected] | > | [email protected] | > | [email protected] | > | [email protected] | > | [email protected] | > +-----------------------+ > > 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 >
