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:rahul....@option3consulting.com] Sent: Friday, July 14, 2017 7:56 AM To: user@drill.apache.org 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.****
