drill-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mehant Baid (JIRA)" <j...@apache.org>
Subject [jira] [Resolved] (DRILL-889) = or <> filter with date against timestamp column not working as expected
Date Wed, 11 Jun 2014 01:58:01 GMT

     [ https://issues.apache.org/jira/browse/DRILL-889?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Mehant Baid resolved DRILL-889.
-------------------------------

    Resolution: Invalid

Your filter predicate seems incorrect for what you intend to do. Drill is schema less so it
doesn't know the type of column 'create_time' so when you compare it with '2008-05-31' we
are doing string comparison. Filter predicate should be changed to indicate its a timestamp
literal --> timestamp '2008-5-31'

> = or <> filter with date against timestamp column not working as expected  
> ---------------------------------------------------------------------------
>
>                 Key: DRILL-889
>                 URL: https://issues.apache.org/jira/browse/DRILL-889
>             Project: Apache Drill
>          Issue Type: Bug
>          Components: Functions - Drill
>            Reporter: Krystal
>            Assignee: Mehant Baid
>             Fix For: 1.0.0-BETA1
>
>
> git.commit.id.abbrev=8490d74
> git.commit.time=02.06.2014 @ 09\:15\:29 PDT
> The student test table has a create_time column as a timestamp.
> 0: jdbc:drill:schema=dfs> select cast(name as varchar(30)), cast(create_time as timestamp)
create_time from student limit 5;
> +------------+-------------+
> |   EXPR$0   | create_time |
> +------------+-------------+
> | fred ovid  | 2014-05-27T00:26:07.000-07:00 |
> | bob brown  | 2014-12-04T21:43:14.000-08:00 |
> | bob hernandez | 2014-05-31T14:33:06.000-07:00 |
> | ulysses xylophone | 2014-06-12T11:27:33.000-07:00 |
> | rachel robinson | 2014-09-01T06:45:57.000-07:00 |
> +------------+-------------+
> Comparing the create_time field using only date portion does not return expected data.
> 0: jdbc:drill:schema=dfs> select cast(name as varchar(30)), cast(create_time as timestamp)
create_time from student where create_time = '2014-05-31';
> +------------+-------------+
> |   EXPR$0   | create_time |
> +------------+-------------+
> | bob hernandez | 2014-05-31T14:33:06.000-07:00 |
> | priscilla king | 2014-05-31T04:51:54.000-07:00 |
> | yuri nixon | 2014-05-31T00:51:49.000-07:00 |
> +------------+-------------+
> 3 rows selected (0.689 seconds)
> Looks like drill matches only the date portion of the column since the compare value
contains only date.  In oracle and postgres, the same query returned 0 rows.  Looks like these
2 db default time to 0 min/sec/ms before doing comparison.



--
This message was sent by Atlassian JIRA
(v6.2#6252)

Mime
View raw message