[DRILL-594] cast interval as time returns wrong value

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Fix Version/s: Future
• Component/s: Functions - Drill
• Labels: interval
• Assignee: Unassigned
• Reporter: Chun Chang
• Created: 29/Apr/14 23:41
• Updated: 10/Nov/15 18:39

Description

with postgres, the following interval values can all be casted as time:

foodmart=# select c_row, c_interval, cast(c_interval as time) from data;
c_row | c_interval | c_interval
-------------------------------------------------------
1 | 00:01:00 | 00:01:00
2 | 05:00:00 | 05:00:00
3 | 10 days | 00:00:00
4 | 34 years | 00:00:00
5 | 3 mons | 00:00:00
6 | -00:00:14 | 23:59:46
7 | 1 day 02:03:04 | 02:03:04
8 | 6 years | 00:00:00
9 | 5 mons | 00:00:00
10 | 5 mons 12:00:00 | 12:00:00
11 | 01:00:00 | 01:00:00
12 | 02:00:00 | 02:00:00
13 | -08:00:00 | 16:00:00
14 | -1 days +02:03:00 | 02:03:00
15 | -1 days +02:03:00 | 02:03:00
16 | 10 days 12:00:00 | 12:00:00
17 | 1 mon 15 days | 00:00:00
18 | 9 years 1 mon -12 days +13:14:00 | 13:14:00
19 | 3 years 5 mons 12 days 360:00:00 | 00:00:00
20 | -3 years -5 mons -12 days +360:00:00 | 00:00:00
21 | -12 days | 00:00:00
22 | 9 mons -27 days +12:34:56 | 12:34:56
23 | -3 years +482 days 76:54:32.189 | 04:54:32.189
(23 rows)

foodmart=# select c_row, cast(c_interval as varchar(20)), cast(c_interval as time) from data where c_row < 3;
c_row | c_interval | c_interval
---------------------------
1 | 00:01:00 | 00:01:00
2 | 05:00:00 | 05:00:00
(2 rows)

with Drill, we either return a wrong value or fail all together depends on the interval value:

0: jdbc:drill:schema=dfs> select c_row, cast(c_interval as varchar(20)), cast(c_interval as time) from data where c_row < 3;
--------------------------------

c_row

EXPR$1

EXPR$2

--------------------------------

1	00:01:00	00:00:00
2	05:00:00	00:00:00

--------------------------------
2 rows selected (0.185 seconds)

0: jdbc:drill:schema=dfs> select c_row, c_interval, cast(c_interval as time) from data;
Query failed: org.apache.drill.exec.rpc.RpcException: Remote failure while running query.[error_id: "be935b5c-e1b8-4b5d-9ffa-c1ecd3f4fe7f"
endpoint {
address: "qa-node119.qa.lab"
user_port: 31010
control_port: 31011
data_port: 31012
}
error_type: 0
message: "Failure while running fragment. < IllegalArgumentException:[ Invalid format: \"10 days\" is malformed at \" days\" ]"
]
Error: exception while executing query (state=,code=0)

Issue Links

• Is contained by: DRILL-3460 Implement function validation in Drill