Steffan, One thing that may be different is that equal can cast operands to make equals work but that may not be true for IN. FWIW, this is me just speculating, I haven't looked at the code just yet.
Perhaps, you could explicit casting to get around this? On Fri, Apr 5, 2013 at 7:36 AM, LUTTER, Steffen <steffen.lut...@sap.com>wrote: > Equal, not equal, less than, less or equal, greater than, greater or > equal all work. Also the function execution in the IN clause seems to work, > as the error message states that the result type is bigint. Following the > error message, it expects the input as timestamp, but I couldn’t find a > syntax to express timestamps in HiveQL. **** > > ** ** > > Two questions remain:**** > > ** ** > > **1) **How to express timestamps in HiveQL?**** > > **2) **Why doesn’t the IN clause support comparisons between > timestamp and bigint, if “equal” and so on does?**** > > ** ** > > Thanks for any thought in this,**** > > ** ** > > Steffen**** > > ** ** > > *From:* Nitin Pawar [mailto:nitinpawar...@gmail.com] > *Sent:* 05 April 2013 16:11 > *To:* user@hive.apache.org > *Subject:* Re: Syntax for filters on timstamp data type**** > > ** ** > > I am not sure IN clause supports executing functions in the query **** > > ** ** > > did it fail when you tried less than greater than type **** > > ** ** > > On Fri, Apr 5, 2013 at 7:36 PM, LUTTER, Steffen <steffen.lut...@sap.com> > wrote:**** > > Hi,**** > > **** > > I have a question regarding filters on timestamps. The syntax seems to be > UNIX_TIMESTAMP('yyyy-MM-dd hh:mm:ss'), is there another way to express a > datetime type? The problem is that I get an exception when using the IN > <list> syntax, while the equal comparison works without problems.**** > > **** > > Example: SELECT * FROM table1 WHERE datecol IN ( > UNIX_TIMESTAMP('2009-01-05 00:00:00'),UNIX_TIMESTAMP('2009-01-10 > 00:00:00') )**** > > **** > > Throws exception: **** > > **** > > Caused by: java.sql.SQLException: Query returned non-zero code: 10014, > cause: FAILED: SemanticException [Error 10014]: Line 5:21 Wrong arguments > ''2009-01-10 00:00:00'': The arguments for IN should be the same type! > Types are: {timestamp IN (bigint, bigint)}**** > > at > org.apache.hadoop.hive.jdbc.HiveStatement.executeQuery(HiveStatement.java:189) > **** > > at > org.apache.hadoop.hive.jdbc.HiveStatement.execute(HiveStatement.java:127)* > *** > > at > com.sap.connectivity.cs.java.drivers.jdbc.JDBCAPI$Statement.execute(JDBCAPI.java:1648) > **** > > at > com.sap.connectivity.cs.java.drivers.jdbc.JDBCDriver.prepare(JDBCDriver.java:1760) > **** > > ... 15 more**** > > **** > > Following query works:**** > > **** > > SELECT * FROM table1 WHERE datecol = UNIX_TIMESTAMP('2009-01-17 > 00:00:00')**** > > **** > > Is there another syntax for datetime types? Could it be a bug in the > filter “IN <list>” operation?**** > > **** > > Thanks in advance,**** > > **** > > Steffen**** > > **** > > > > **** > > ** ** > > -- > Nitin Pawar**** >
