[ https://issues.apache.org/jira/browse/HIVE-17009?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Timothy Miron updated HIVE-17009: --------------------------------- Description: Error "Invalid function 'nullif'" thrown if nullif() used in conjunction with certain other commands, but equivalently behaving CASE WHEN ... END block behaves fine. Example: (note the use of `dual` table) This throws an {color:#ff0000}"Invalid function 'nullif'" error{color} seemingly due to the presence of operators (date_sub()) in the WHERE clause: {code:sql} select coalesce(nullif('a','a'),'b') result_value from workspace_t886880.dual where current_date > date_sub(current_date,1); {code} As well as simply attaching 'EXPLAIN' like this (throws the same error): {code:sql} EXPLAIN select coalesce(nullif('a','a'),'b') result_value from workspace_t886880.dual {code} However, this similarly behaving CASE..WHEN block does {color:#14892c}not{color} throw an error, even with the same where clause as above: {code:sql} select coalesce(case when 'a' = 'a' then null else 'a' end,'b') result_value from workspace_t886880.dual where current_date > date_sub(current_date,1); {code} Similarly, omitting any where clause returns functionality to normal, as does removing any _operations _from the where clause also {color:#14892c}allows {color}the query to execute: {code:sql} /* this works */ select coalesce(nullif('a','a'),'b') result_value from workspace_t886880.dual; /* no where clause! */ /* and this works too */ select coalesce(nullif('a','a'),'b') result_value from workspace_t886880.dual where DATE '2016-01-02' > DATE '2016-01-01' {code} was: Error "Invalid function 'nullif'" thrown if nullif() used in conjunction with certain other commands, but equivalently behaving CASE WHEN ... END block behaves fine. Example: (note the use of `dual` table) # This throws an {color:#ff0000}"Invalid function 'nullif'" error{color}: {code:sql} select coalesce(nullif('a','a'),'b') result_value from workspace_t886880.dual where current_date > date_sub(current_date,1); {code} As well as simply attaching 'EXPLAIN' like this: {code:sql} EXPLAIN select coalesce(nullif('a','a'),'b') result_value from workspace_t886880.dual {code} # This similarily behaving CASE..WHEN block does {color:#14892c}not{color} throw an error: {code:sql} select coalesce(case when 'a' = 'a' then null else 'a' end,'b') result_value from workspace_t886880.dual where current_date > date_sub(current_date,1); {code} Similarly, omitting any where clause returns functionality to normal, as does removing any _operations _from the where clause also {color:#14892c}allows {color}the query to execute: {code:sql} /* this works */ select coalesce(nullif('a','a'),'b') result_value from workspace_t886880.dual; /* no where clause! */ /* and this works too */ select coalesce(nullif('a','a'),'b') result_value from workspace_t886880.dual where DATE '2016-01-02' > DATE '2016-01-01' {code} > ifnull() compatibility with explain or use of constants > ------------------------------------------------------- > > Key: HIVE-17009 > URL: https://issues.apache.org/jira/browse/HIVE-17009 > Project: Hive > Issue Type: Bug > Components: Hive, SQL > Affects Versions: 1.2.1 > Environment: Running hive queries from Toad Data Point 4.2 via ODBC > connection. > Reporter: Timothy Miron > > Error "Invalid function 'nullif'" thrown if nullif() used in conjunction with > certain other commands, but equivalently behaving CASE WHEN ... END block > behaves fine. > Example: > (note the use of `dual` table) > This throws an {color:#ff0000}"Invalid function 'nullif'" error{color} > seemingly due to the presence of operators (date_sub()) in the WHERE clause: > {code:sql} > select > coalesce(nullif('a','a'),'b') result_value > from > workspace_t886880.dual > where current_date > date_sub(current_date,1); > {code} > As well as simply attaching 'EXPLAIN' like this (throws the same error): > {code:sql} > EXPLAIN select > coalesce(nullif('a','a'),'b') result_value > from > workspace_t886880.dual > {code} > However, this similarly behaving CASE..WHEN block does > {color:#14892c}not{color} throw an error, even with the same where clause as > above: > {code:sql} > select > coalesce(case when 'a' = 'a' then null else 'a' end,'b') result_value > from > workspace_t886880.dual > where current_date > date_sub(current_date,1); > {code} > Similarly, omitting any where clause returns functionality to normal, as does > removing any _operations _from the where clause also {color:#14892c}allows > {color}the query to execute: > {code:sql} > /* this works */ > select > coalesce(nullif('a','a'),'b') result_value > from > workspace_t886880.dual; > /* no where clause! */ > /* and this works too */ > select > coalesce(nullif('a','a'),'b') result_value > from > workspace_t886880.dual > where DATE '2016-01-02' > DATE '2016-01-01' > {code} -- This message was sent by Atlassian JIRA (v6.4.14#64029)