Mailing-List: contact issues-help@hive.apache.org; run by ezmlm
Precedence: bulk
Reply-To: dev@hive.apache.org
Date: Thu, 1 Sep 2016 23:22:21 +0000 (UTC)
From: "Zoltan Haindrich (JIRA)" <jira@apache.org>
To: issues@hive.apache.org
Message-ID: <JIRA.12960237.1461133029000.474437.1472772141917@Atlassian.JIRA>
In-Reply-To: <JIRA.12960237.1461133029000@Atlassian.JIRA>
References: <JIRA.12960237.1461133029000@Atlassian.JIRA> <JIRA.12960237.1461133029714@arcas>
Subject: [jira] [Updated] (HIVE-13555) Add nullif udf
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit
archived-at: Thu, 01 Sep 2016 23:22:29 -0000


     [ https://issues.apache.org/jira/browse/HIVE-13555?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Zoltan Haindrich updated HIVE-13555:
------------------------------------
    Attachment: HIVE-13555.2.patch

[~ashutoshc] i've made it more strict...I think it will be more safer this way too 

patch#2 will refuse to use different primitive type groups at the same time.

so: {{nullif('x',1)}} is invalid...for postgres this was invalid too, but psql interpreted {{nullif('1',1)}} as a valid...I think refusing to cast string all the way it's possible may cover user problems.

however...ibm things the 2 types should be casted in any way it's possible (patch#1)
https://www.ibm.com/support/knowledgecenter/SSGU8G_11.50.0/com.ibm.sqls.doc/ids_sqs_1446.htm

oracle does only note to cast numeric datatypes (#1 or #2)
https://docs.oracle.com/database/121/SQLRF/functions128.htm
however...this will do between all the other primitive types; I think that's fair - and i've adopted to only compare unknown types if they are the same.

i'm rejecting the VOID_GROUP as first argument...
because {{NULLIF(NULL,x)}} has no real use - and oracle have noted this explicitly...putting a null literal there must be a mistake.

I don't have a working set of sql servers to peek around now ;)


> Add nullif udf
> --------------
>
>                 Key: HIVE-13555
>                 URL: https://issues.apache.org/jira/browse/HIVE-13555
>             Project: Hive
>          Issue Type: Sub-task
>          Components: UDF
>            Reporter: Ashutosh Chauhan
>            Assignee: Zoltan Haindrich
>         Attachments: HIVE-13555.1.patch, HIVE-13555.2.patch
>
>
> {{nullif(exp1, exp2)}} is shorthand for: {{case when exp1 = exp2 then null else exp1}}


--
This message was sent by Atlassian JIRA
(v6.3.4#6332)