phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Binu Mathew <bmat...@groupon.com>
Subject to_date not working as expected
Date Fri, 29 Jan 2016 01:57:56 GMT
Phoenix version 4.4.0

Issues with Phoenix when used with HBase 0.96.0.2.0

2 Issues:

*ISSUE:* to_date Function is not converting string data types in valid date
formats to a DATE data type when used in the WHERE clause for date
comparison.

Below is a query I ran against a Phoenix view in which I use the ‘to_date’
function to convert 2 VARCHAR columns to date.
1. column ‘created_at_ts’ stored as VARCHAR in format such as 2009-05-05
15:40:10.000
2. column ‘created_at_date’ stored as VARCHAR in format such as 2009-05-05

Observe that the ‘to_date’ function coverts the 2 VARCHAR columns to dates:


select to_date("created_at_ts"), to_date("created_at_date") from
"gp_subscriptions" limit 5;

+-------------------------------------------------+---------------------------------------------------+
| TO_DATE(subscriber."created_at_ts", null, null) |
TO_DATE(subscriber."created_at_date", null, null) |
+-------------------------------------------------+---------------------------------------------------+
| 2009-05-05 15:40:10.000                         | 2009-05-05
00:00:00.000                           |
| 2012-11-22 07:37:34.000                         | 2012-11-22
00:00:00.000                           |
| 2010-07-24 14:12:33.000                         | 2010-07-24
00:00:00.000                           |
| 2012-11-22 07:38:04.000                         | 2012-11-22
00:00:00.000                           |
| 2012-11-22 07:38:10.000                         | 2012-11-22
00:00:00.000                           |
+-------------------------------------------------+---------------------------------------------------+


Here is another query in which I’m using the ‘to_date’ function on string
literals in the WHERE clause for date comparison .

Observer that the ‘to_date’ function coverts the string literals to dates
and the the date comparison correctly evaluates:

select '1' from "gp_subscriptions" where to_date('2009-05-05 15:40:10.000')
= to_date('2009-05-05 15:40:10.000') limit 2;
2 rows selected (0.035 seconds)


Now when I try the date comparison using the columns from my view, it fails:

select '1' from "gp_subscriptions" where to_date("created_at_ts") =
to_date('2009-05-05 15:40:10.000') limit 2;

Caused by: org.apache.hadoop.hbase.DoNotRetryIOException:
BooleanExpressionFilter failed during reading: Could not initialize class
org.apache.phoenix.util.DateUtil$ISODateFormatParser
Caused by: java.lang.NoClassDefFoundError: Could not initialize class
org.apache.phoenix.util.DateUtil$ISODateFormatParser

Also fails with same error when I try: select '1' from "gp_subscriptions"
where to_date("created_at_ts") = to_date('2009-05-05') limit 2;

Caused by: org.apache.hadoop.hbase.DoNotRetryIOException:
BooleanExpressionFilter failed during reading: Could not initialize class
org.apache.phoenix.util.DateUtil$ISODateFormatParser
Caused by: java.lang.NoClassDefFoundError: Could not initialize class
org.apache.phoenix.util.DateUtil$ISODateFormatParser


*ISSUE: *Date comparisons on string literals are not evaluating correctly
such that dates in the future get interpreted as being less than dates in
the past.

Test case 1:
2009-05-05 15:40:10.000 is greater than (in the future) 2005-05-05
15:40:10.000

The following query should return 2 rows, however, it does not return any
rows:

select '1' from "gp_subscriptions" where to_date('2009-05-05 15:40:10.000')
> to_date('2005-05-05 15:40:10.000') limit 2;
No rows selected (0.024 seconds)


The following query should return no rows, however, it returns 2 rows:

select '1' from "gp_subscriptions" where to_date('2009-05-05 15:40:10.000')
< to_date('2005-05-05 15:40:10.000') limit 2;
2 rows selected (0.033 seconds)

Test case 2:
2009-05-05 is greater than (in the future) than 1970-05-05

The following query should return 2 rows, however, it does not return any
rows:

select '1' from "gp_subscriptions" where to_date('2009-05-05') >
to_date('1970-05-05') limit 2;
No rows selected (0.024 seconds)


The following query should return no rows, however, it returns 2 rows:

select '1' from "gp_subscriptions" where to_date('2009-05-05') <
to_date('1970-05-05') limit 2;
2 rows selected (0.033 seconds)


-- 
*Binu Mathew*
Data Engineering

3101 Park Blvd., Palo Alto, CA 94306
Mobile: 630.267.5938
Groupon
<http://www.google.com/url?q=http%3A%2F%2Fwww.groupon.com%2F&sa=D&sntz=1&usg=AFrqEzcC80FkwsjyolWTKAH1sZ9yU2t0xg>

Mime
View raw message