The error would encounter when IN clause is rewritten into Join, and planner chooses to use distributed join (involves a Hash Exchange). For distributed join, when two join keys do not have same types, only certain type of implicit cast will be allowed. In Khurram's case, I think the query is not using hash-distributed join method, and hence did not hit this error.
On Fri, Jul 14, 2017 at 12:17 PM, Khurram Faraaz <[email protected]> wrote: > I tried the below query using data from your query from the IN clause (as > is), I didn't see an error/Exception on sqlline, I am on Drill 1.11.0 > > > 0: jdbc:drill:schema=dfs.tmp> select * from (values(1376764200000)) WHERE > EXPR$0 IN (1376677800000, 1376764200000, 1376850600000, 1377023400000, > 1377282600000, 1377369000000, 1377455400000, 1377887400000, 1377973800000, > 1379097000000, 1379183400000, 1379269800000, 1379701800000, 1379788200000, > 1380306600000, 1380393000000, 1380479400000, 1380911400000, 1380997800000); > +----------------+ > | EXPR$0 | > +----------------+ > | 1376764200000 | > +----------------+ > 1 row selected (0.509 seconds) > > Thanks, > Khurram > > > ________________________________ > From: Kunal Khatua <[email protected]> > Sent: Friday, July 14, 2017 11:00:03 PM > To: [email protected] > Subject: RE: Error when timestamp IN clause contains more elements > > This works for 19 elements because the IN clause will do a filter pushdown > to the source for up to 19 elements. 20 or more... it resorts to using a > JOIN, in which case it finds data type mismatch. > > It is a good practice (for readability purposes) to do casts in such cases. > > Alternatively (not recommended in IMO) is to raise the threshold for the > property "in_subquery_threshold" in sys.options from 20 to a higher value. > > -----Original Message----- > From: Rahul Raj [mailto:[email protected]] > Sent: Friday, July 14, 2017 7:56 AM > To: [email protected] > Subject: Error when timestamp IN clause contains more elements > > I am getting the error below when there are more than 19 elements in the IN > clause: > > DrillRuntimeException: Join only supports implicit casts between 1. > Numeric data 2. Varchar, Varbinary data 3. Date, Timestamp data Left type: > TIMESTAMP, Right type: BIGINT. Add explicit casts to avoid this error. > > However, query succeeds when IN clause contains one element less. Query is > > SELECT > `Date` > , `COUNT`(`Date`) `COUNT_Date` > , `COUNT`(`a`) `COUNT_a` > FROM > dfs.test.`latest` > WHERE (`Date` IN (1376677800000, 1376764200000, 1376850600000, > 1377023400000, 1377282600000, 1377369000000, 1377455400000, 1377887400000, > 1377973800000, 1379097000000, 1379183400000, 1379269800000, 1379701800000, > 1379788200000, 1380306600000, 1380393000000, 1380479400000, 1380911400000, > 1380997800000)) > GROUP BY `Date` > ORDER BY `Date` ASC, `COUNT_Date` ASC; > > > Regards, > Rahul > > -- > **** This email and any files transmitted with it are confidential and > intended solely for the use of the individual or entity to whom it is > addressed. If you are not the named addressee then you should not > disseminate, distribute or copy this e-mail. Please notify the sender > immediately and delete this e-mail from your system.**** >
