[HIVE-179] SUBSTR function should work like other databases

• Type: Bug
• Status: Closed
• Priority: Critical
• Resolution: Fixed
• Fix Version/s: 0.3.0
• Component/s: Query Processor
• Hadoop Flags: Incompatible change, Reviewed
• Release Note: SUBSTR position now starts at 1, not 0. A negative position counts backwards from the end of the string, starting at -1. Position 0 is treated as position 1.
• Assignee: David Phillips
• Reporter: David Phillips
• Created: 16/Dec/08 00:08
• Updated: 17/Dec/11 00:08
• Resolved: 28/Jan/09 00:14

Description

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?

 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>

Issue Links

• is blocked by: HIVE-215 Map-side aggregate with DISTINCT generates bad intermediate table
• is blocked by: HIVE-222 Group by on a combination of disitinct and non distinct aggregates can return serialization errors with map side aggregations.