hadoop-hive-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "David Phillips (JIRA)" <j...@apache.org>
Subject [jira] Created: (HIVE-179) SUBSTR function should work like other databases
Date Tue, 16 Dec 2008 00:09:44 GMT
SUBSTR function should work like other databases
------------------------------------------------

                 Key: HIVE-179
                 URL: https://issues.apache.org/jira/browse/HIVE-179
             Project: Hadoop Hive
          Issue Type: Bug
          Components: Query Processor
            Reporter: David Phillips
            Assignee: David Phillips


Positions start at 1, not 0.  Negative positions start at the end of the string and count
backwards.

Oracle returns null for lengths less than 1 or non-existent substrings (any empty strings
are null).  MySQL and PostgreSQL return empty strings, never null.  PostgreSQL errors for
negative lengths.  I suggest we follow the MySQL behavior.

Oracle treats position 0 the same as 1.  Perhaps we should too?

{noformat}
 SUBSTR('ABCDEFG',3,4): CDEF
SUBSTR('ABCDEFG',-5,4): CDEF
   SUBSTR('ABCDEFG',3): CDEFG
  SUBSTR('ABCDEFG',-5): CDEFG
     SUBSTR('ABC',1,1): A

MySQL:
     SUBSTR('ABC',0,1): <empty>
     SUBSTR('ABC',0,2): <empty>
     SUBSTR('ABC',1,0): <empty>
    SUBSTR('ABC',1,-1): <empty>

Oracle:
     SUBSTR('ABC',0,1): A
     SUBSTR('ABC',0,2): AB
     SUBSTR('ABC',1,0): <null>
    SUBSTR('ABC',1,-1): <null>
{noformat}


-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message