[
https://issues.apache.org/jira/browse/HIVE-179?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12657619#action_12657619
]
David Phillips commented on HIVE-179:
-------------------------------------
I note this will break ~27 tests that use {{substr(src.value,4)}} or similar.
Is this going to be a problem for existing user code?
> 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.