great
so can we assume that equals comparison can auto cast but not the in range
statement ?
On Mon, Apr 8, 2013 at 7:08 PM, LUTTER, Steffen <steffen.lut...@sap.com>wrote:
> Hi Mark,****
>
> ** **
>
> Correct, I just did some tests and the cast is the way to go. While for
> comparison operations (equal, diff, …) implicit casts work, this is not the
> case for the IN clause. I think it should, as eventually this just
> translates to a disjunction of comparisons so it should be the same. ****
>
> ** **
>
> Anyway, I have a working solution now. For the record I paste two working
> example queries below.****
>
> ** **
>
> Thanks a lot for your help !!!****
>
> ** **
>
> Steffen****
>
> ** **
>
> Example 1: SELECT * FROM table1 WHERE datecol = CAST('2009-01-17
> 00:00:00' AS timestamp)****
>
> Example 2: SELECT * FROM table1 WHERE datecol IN (CAST ('2009-01-11
> 00:00:00' AS timestamp), CAST ('2009-01-08 00:00:00' AS timestamp) )****
>
> ** **
>
> *From:* Mark Grover [mailto:grover.markgro...@gmail.com]
> *Sent:* 05 April 2013 18:43
>
> *To:* user@hive.apache.org
> *Subject:* Re: Syntax for filters on timstamp data type****
>
> ** **
>
> 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****
>
> ** **
>
--
Nitin Pawar
