[
https://issues.apache.org/jira/browse/HIVE-179?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Carl Steinbach updated HIVE-179:
--------------------------------
Fix Version/s: 0.3.0
(was: 0.6.0)
> 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
> Priority: Critical
> Fix For: 0.3.0
>
> Attachments: hive-substr.patch
>
>
> 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.
