You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@hive.apache.org by "Robert Surówka (Created JIRA)" <ji...@apache.org> on 2011/11/08 01:58:52 UTC

[jira] [Created] (HIVE-2558) Timestamp comparisons don't work

Timestamp comparisons don't work
--------------------------------

                 Key: HIVE-2558
                 URL: https://issues.apache.org/jira/browse/HIVE-2558
             Project: Hive
          Issue Type: Bug
            Reporter: Robert Surówka


I may be missing something, but:

After performing:

create table rrt (r timestamp);
insert into table rrt select '1970-01-01 00:00:01' from src limit 1;

Following queries give undesirable results:

select * from rrt where r in ('1970-01-01 00:00:01');
select * from rrt where r in (0); 
select * from rrt where r = 0; 
select * from rrt where r = '1970-01-01 00:00:01';

At least for the first two, the reason may be the lack of timestamp in numericTypes Map from FunctionRegistry.java (591) . Yet whether we really want to have a linear hierarchy of primitive types in the end, is another question.

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira

       

[jira] [Commented] (HIVE-2558) Timestamp comparisons don't work

Posted by "Michael Ubell (Commented) (JIRA)" <ji...@apache.org>.
    [ https://issues.apache.org/jira/browse/HIVE-2558?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13236800#comment-13236800 ] 

Michael Ubell commented on HIVE-2558:
-------------------------------------

Actually I misunderstood what hive is doing.  It is converting the string to a number and then comparing it to the timestamp:

select * from rrt where r = '28801';
OK
1970-01-01 00:00:01

While this does pass the reasonability test, I don't think its the right thing to do.  It would be much better to implicitly cast the string constant to a timestamp.
                
> Timestamp comparisons don't work
> --------------------------------
>
>                 Key: HIVE-2558
>                 URL: https://issues.apache.org/jira/browse/HIVE-2558
>             Project: Hive
>          Issue Type: Bug
>            Reporter: Robert Surówka
>
> I may be missing something, but:
> After performing:
> create table rrt (r timestamp);
> insert into table rrt select '1970-01-01 00:00:01' from src limit 1;
> Following queries give undesirable results:
> select * from rrt where r in ('1970-01-01 00:00:01');
> select * from rrt where r in (0); 
> select * from rrt where r = 0; 
> select * from rrt where r = '1970-01-01 00:00:01';
> At least for the first two, the reason may be the lack of timestamp in numericTypes Map from FunctionRegistry.java (591) . Yet whether we really want to have a linear hierarchy of primitive types in the end, is another question.

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira

       

[jira] [Commented] (HIVE-2558) Timestamp comparisons don't work

Posted by "Michael Ubell (Commented) (JIRA)" <ji...@apache.org>.
    [ https://issues.apache.org/jira/browse/HIVE-2558?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13236736#comment-13236736 ] 

Michael Ubell commented on HIVE-2558:
-------------------------------------

In the query:

select * from rrt where r = '1970-01-01 00:00:01';

you say that the problem is that the field is treated as a string not as a time stamp but if it is treated as a string then either there should be a proper conversion of the string to timestamp (or vice versa) to do the compare or an error should be raised, you cannot just compare the bytes (or what ever hive is doing) and return an invalid answer.

The first query:

select * from rrt where r in ('1970-01-01 00:00:01');

does the right thing:
FAILED: Error in semantic analysis: Line 1:26 Wrong arguments ''1970-01-01 00:00:01'': The arguments for IN should be the same type! Types are: {timestamp IN (string)}

                
> Timestamp comparisons don't work
> --------------------------------
>
>                 Key: HIVE-2558
>                 URL: https://issues.apache.org/jira/browse/HIVE-2558
>             Project: Hive
>          Issue Type: Bug
>            Reporter: Robert Surówka
>
> I may be missing something, but:
> After performing:
> create table rrt (r timestamp);
> insert into table rrt select '1970-01-01 00:00:01' from src limit 1;
> Following queries give undesirable results:
> select * from rrt where r in ('1970-01-01 00:00:01');
> select * from rrt where r in (0); 
> select * from rrt where r = 0; 
> select * from rrt where r = '1970-01-01 00:00:01';
> At least for the first two, the reason may be the lack of timestamp in numericTypes Map from FunctionRegistry.java (591) . Yet whether we really want to have a linear hierarchy of primitive types in the end, is another question.

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira

       

[jira] [Commented] (HIVE-2558) Timestamp comparisons don't work

Posted by "Priyadarshini (Commented) (JIRA)" <ji...@apache.org>.
    [ https://issues.apache.org/jira/browse/HIVE-2558?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13168407#comment-13168407 ] 

Priyadarshini commented on HIVE-2558:
-------------------------------------

Hi Robert,

In the above query, timestamp is used as a column datatype of the table rrt.
So when u want to use the timestamp in the where clause of select statement, we should be able to tell that the data we are searching is a timestamp value.
Giving the field value in single/double quote will be treated as a string value and not timestamp value.
So when u wanted to retreive the results that satisfy the timestamp value you are specifying in where clause, the query should be

select * from rrt where r = unix_timestamp('1970-01-01 00:00:01');

This would be interpreted as data u r searching is a timestamp. 
Hope it helps 


Thanks,
Priya
                
> Timestamp comparisons don't work
> --------------------------------
>
>                 Key: HIVE-2558
>                 URL: https://issues.apache.org/jira/browse/HIVE-2558
>             Project: Hive
>          Issue Type: Bug
>            Reporter: Robert Surówka
>
> I may be missing something, but:
> After performing:
> create table rrt (r timestamp);
> insert into table rrt select '1970-01-01 00:00:01' from src limit 1;
> Following queries give undesirable results:
> select * from rrt where r in ('1970-01-01 00:00:01');
> select * from rrt where r in (0); 
> select * from rrt where r = 0; 
> select * from rrt where r = '1970-01-01 00:00:01';
> At least for the first two, the reason may be the lack of timestamp in numericTypes Map from FunctionRegistry.java (591) . Yet whether we really want to have a linear hierarchy of primitive types in the end, is another question.

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira