[
https://issues.apache.org/jira/browse/DRILL-2313?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14746017#comment-14746017
]
Victoria Markman commented on DRILL-2313:
-----------------------------------------
[~seanhychu],
(1) Is this expected behavior ?
{code}
0: jdbc:drill:schema=dfs> select * from t1 where c1 between cast('2015-01-01'
as date) and '2015-01-03 xxxxxxxxxxxxxxx';
+-----+--------+-------------+
| a1 | b1 | c1 |
+-----+--------+-------------+
| 1 | aaaaa | 2015-01-01 |
| 2 | bbbbb | 2015-01-02 |
| 3 | ccccc | 2015-01-03 |
+-----+--------+-------------+
3 rows selected (0.222 seconds)
{code}
Postgres, for example, only tolerates trailing spaces:
{code}
postgres=# select * from t1 where c1 between cast('2015-01-01' as date) and
'2015-01-03 xxxxxx';
'ERROR: invalid input syntax for type date: "2015-01-03 xxxxxx"
LINE 1: ...1 where c1 between cast('2015-01-01' as date) and '2015-01-0...
^
postgres=# select * from t1 where c1 between cast('2015-01-01 ' as date) and
'2015-01-03 ';
a1 | b1 | c1
----+-------+------------
1 | aaaaa | 2015-01-01
2 | bbbbb | 2015-01-02
3 | ccccc | 2015-01-03
(3 rows)
{code}
(2) In this fix, did we only implement implicit cast from string to date or
string to numeric should work as well ?
(3) It feels that we maybe need to document this behavior as well. What do you
think ?
> Query fails when one of the operands is a DATE literal without an explicit
> cast
> -------------------------------------------------------------------------------
>
> Key: DRILL-2313
> URL: https://issues.apache.org/jira/browse/DRILL-2313
> Project: Apache Drill
> Issue Type: Bug
> Components: Query Planning & Optimization
> Affects Versions: 0.8.0
> Reporter: Abhishek Girish
> Assignee: Sean Hsuan-Yi Chu
> Fix For: 1.2.0
>
>
> For operations involving the date datatype, when one of the operands is a
> DATE literal without a cast, query fails.
> *The following query fails to validate:*
> {code:sql}
> SELECT
> *
> FROM
> date_dim
>
> WHERE d_date BETWEEN '2002-3-01' AND cast('2002-3-01' AS DATE)
> LIMIT 1;
> {code}
> Query failed: SqlValidatorException: Cannot apply 'BETWEEN' to arguments of
> type '<ANY> BETWEEN <CHAR(9)> AND <DATE>'. Supported form(s):
> '<COMPARABLE_TYPE> BETWEEN <COMPARABLE_TYPE> AND <COMPARABLE_TYPE>'
> *The following query executes fine:*
> {code:sql}
> SELECT
> *
> FROM
> date_dim
>
> WHERE d_date BETWEEN '2002-3-01' AND
> '2002-3-01'
> LIMIT 1;
> {code}
> Both the queries execute fine on postgres
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
