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.

Reply via email to