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.****
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB