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.