[
https://issues.apache.org/jira/browse/DRILL-4021?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14988604#comment-14988604
]
Victoria Markman commented on DRILL-4021:
-----------------------------------------
Julian,
We need to remove addition from the title, it is not allowed, period :)
However, subtraction according to SQL 2011 ISO/IEC 9075-2:2011(E) is allowed
between two timestamps ( see excerpt attached and hopefully I'm understanding
it correctly )
It is supported in Postgres 9.3
{code}
postgres=# select c_timestamp, now() - c_timestamp as interval from test;
c_timestamp | interval
---------------------+-------------------------
2015-03-01 00:11:15 | 247 days 16:11:45.11652
2015-03-01 00:20:46 | 247 days 16:02:14.11652
2015-03-01 00:38:12 | 247 days 15:44:48.11652
2015-03-01 00:53:00 | 247 days 15:30:00.11652
2015-03-01 01:07:15 | 247 days 15:15:45.11652
2015-03-01 01:14:14 | 247 days 15:08:46.11652
2015-03-01 01:30:41 | 247 days 14:52:19.11652
2015-03-01 01:42:47 | 247 days 14:40:13.11652
2015-03-01 01:49:46 | 247 days 14:33:14.11652
|
(10 rows)
{code}
It is also supported in drill:
{code}
0: jdbc:drill:schema=dfs> select c_timestamp, now() - c_timestamp from j2;
+------------------------+-------------------+
| c_timestamp | EXPR$1 |
+------------------------+-------------------+
| 2015-03-01 00:11:15.0 | P247DT86100.498S |
| 2015-03-01 00:20:46.0 | P247DT85529.498S |
| 2015-03-01 00:38:12.0 | P247DT84483.498S |
| 2015-03-01 00:53:00.0 | P247DT83595.498S |
| 2015-03-01 01:07:15.0 | P247DT82740.498S |
| 2015-03-01 01:14:14.0 | P247DT82321.498S |
| 2015-03-01 01:30:41.0 | P247DT81334.498S |
| 2015-03-01 01:42:47.0 | P247DT80608.498S |
| 2015-03-01 01:49:46.0 | P247DT80189.498S |
| null | null |
+------------------------+-------------------+
10 rows selected (0.283 seconds)
{code}
j2 is a parquet file:
{code}
[Tue Nov 03 16:10:29 ] # ~/parquet-tools/parquet-schema 0_0_0.parquet
message root {
optional binary c_varchar (UTF8);
optional int32 c_integer;
optional int64 c_bigint;
optional float c_float;
optional double c_double;
optional int32 c_date (DATE);
optional int32 c_time (TIME_MILLIS);
optional int64 c_timestamp (TIMESTAMP_MILLIS);
optional boolean c_boolean;
optional double d9;
optional double d18;
optional double d28;
optional double d38;
}
{code}
The reason this case failed for Krystal, I believe, is because hive table is a
"strongly typed data source", and this behavior is inconsistent with the rest
of our product.
In fact, if you create a drill view with cast to timestamp, you are going to
get the same error:
{code}
0: jdbc:drill:schema=dfs> create or replace view test_timestamp(c1) as select
CAST(c_timestamp as TIMESTAMP) from j2;
+-------+------------------------------------------------------------------------+
| ok | summary
|
+-------+------------------------------------------------------------------------+
| true | View 'test_timestamp' created successfully in 'dfs.subqueries' schema
|
+-------+------------------------------------------------------------------------+
1 row selected (0.337 seconds)
0: jdbc:drill:schema=dfs> select c1, now() - c1 from test_timestamp;
Error: VALIDATION ERROR: From line 1, column 12 to line 1, column 21: Cannot
apply '-' to arguments of type '<ANY> - <TIMESTAMP(0)>'. Supported form(s):
'<NUMERIC> - <NUMERIC>'
'<DATETIME_INTERVAL> - <DATETIME_INTERVAL>'
'<DATETIME> - <DATETIME_INTERVAL>'
[Error Id: 5928b2fc-0650-4096-a0a3-8be8337a1b8e on atsqa4-133.qa.lab:31010]
(state=,code=0)
{code}
We do have a problem: drill behaves inconsistently. [~jnadeau] mentioned during
our last hangout that he was trying to fix parquet types to be used during
planning and he got tons of errors. Jacques, you probably got hundreds of those
?
> Cannot subract or add between two timestamps
> --------------------------------------------
>
> Key: DRILL-4021
> URL: https://issues.apache.org/jira/browse/DRILL-4021
> Project: Apache Drill
> Issue Type: Bug
> Components: Execution - Data Types
> Reporter: Krystal
>
> The following subtraction between 2 now() function works:
> select now() - now()from voter_hive limit 1;
> +---------+
> | EXPR$0 |
> +---------+
> | PT0S |
> +---------+
>
> However, the following queries fail:
> select now() - create_time from voter_hive where voter_id=1;
> Error: VALIDATION ERROR: From line 1, column 8 to line 1, column 26: Cannot
> apply '-' to arguments of type '<ANY> - <TIMESTAMP(0)>'. Supported form(s):
> '<NUMERIC> - <NUMERIC>'
> '<DATETIME_INTERVAL> - <DATETIME_INTERVAL>'
> '<DATETIME> - <DATETIME_INTERVAL>'
> select create_time - cast('1997-02-12 15:18:31.072' as timestamp) from
> voter_hive where voter_id=1;
> Error: VALIDATION ERROR: From line 1, column 8 to line 1, column 65: Cannot
> apply '-' to arguments of type '<TIMESTAMP(0)> - <TIMESTAMP(0)>'. Supported
> form(s): '<NUMERIC> - <NUMERIC>'
> '<DATETIME_INTERVAL> - <DATETIME_INTERVAL>'
> '<DATETIME> - <DATETIME_INTERVAL>'
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
