Bug 6933 - Add support for scalar function IFNULL(expr1, expr2)
Summary: Add support for scalar function IFNULL(expr1, expr2)
Status: NEW
Alias: None
Product: SQL
Classification: Unclassified
Component: all (show other bugs)
Version: 11.37.7 (Jun2020)
Hardware: All All
: Normal enhancement
Assignee: SQL devs
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2020-07-22 15:07 CEST by Martin van Dinther
Modified: 2020-07-22 15:07 CEST (History)
0 users



Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Martin van Dinther cwiconfidential 2020-07-22 15:07:18 CEST
User-Agent:       Mozilla/5.0 (Windows NT 6.1; rv:78.0) Gecko/20100101 Firefox/78.0
Build Identifier: 

We currently do not support the scalar function IFNULL(expr1, expr2). This function tests whether expr1 IS NULL and if so returns expr2 else it returns expr1. When both expr1 and expr2 are NULL, NULLL is returned.

Request to add a native C implementation for ifnull() (including bulk version) which is more efficient than current alternatives coalesce() and ifthenelse().

Reproducible: Always

Steps to Reproduce:
create table t6933 (i int, c varchar(8));
insert into t6933 values (1, 'aa1'), (2, null), (null, 'bb2'), (null, null);
select i, c, ifnull(i, 2*3) as "ifnull(i,2*3)", ifnull(c, 'has null') as "ifnull(c,'x')", ifnull(i, c) as "ifnull(i,c)" from t6933;
select c, i, ifnull(c, i) as "ifnull(c,i)" from t6933;
explain select i, c, ifnull(i, c) as "ifnull(i,c)" from t6933;
drop table t6933;

Actual Results:  
sql>create table t6933 (i int, c varchar(8));
operation successful
sql>insert into t6933 values (1, 'aa1'), (2, null), (null, 'bb2'), (null, null);
4 affected rows
sql>select i, c, ifnull(i, 2*3) as "ifnull(i,2*3)", ifnull(c, 'has null') as "ifnull(i,'x')", ifnull(i, c) as "ifnull(i,c)" from t6933;
SELECT: no such binary operator 'ifnull(int,tinyint)'
sql>select c, i, ifnull(c, i) as "ifnull(c,i)" from t6933;
SELECT: no such binary operator 'ifnull(varchar,int)'
sql>explain select i, c, ifnull(i, c) as "ifnull(i,c)" from t6933;
SELECT: no such binary operator 'ifnull(int,varchar)'
sql>drop table t6933;
operation successful
sql>


Expected Results:  
sql>create table t6933 (i int, c varchar(8));
operation successful
sql>insert into t6933 values (1, 'aa1'), (2, null), (null, 'bb2'), (null, null);
4 affected rows
sql>select i, c, ifnull(i, 2*3) as "ifnull(i,2*3)", ifnull(c, 'has null') as "ifnull(c,'x')", ifnull(i, c) as "ifnull(i,c)" from t6933;
+------+------+---------------+---------------+-------------+
| i    | c    | ifnull(i,2*3) | ifnull(c,'x') | ifnull(i,c) |
+======+======+===============+===============+=============+
|    1 | aa1  |             1 | aa1           | 1           |
|    2 | null |             2 | has null      | 2           |
| null | bb2  |             6 | bb2           | bb2         |
| null | null |             6 | has null      | null        |
+------+------+---------------+---------------+-------------+
4 tuples
sql>select c, i, ifnull(c, i) as "ifnull(c,i)" from t6933;
+------+------+-------------+
| c    | i    | ifnull(c,i) |
+======+======+=============+
| aa1  |    1 | aa1         |
| null |    2 | 2           |
| bb2  | null | bb2         |
| null | null | null        |
+------+------+-------------+
4 tuples
sql>explain select i, c, coalesce(i, c) as "ifnull(i,c)" from t6933;
+----------------------------------------------------------------------------------------------------------------------------------------------------+
| mal                                                                                                                                                |
+====================================================================================================================================================+
| function user.s26_0():void;                                                                                                                        |
|     X_1:void := querylog.define("explain select i, c, ifnull(i, c) as \"ifnull(i,c)\" from t6933;":str, "default_pipe":str, 32:int);               |
|     X_50:bat[:str] := bat.pack("sys.t6933":str, "sys.t6933":str, "sys.":str);                                                                      |
|     X_51:bat[:str] := bat.pack("i":str, "c":str, "ifnull(i,c)":str);                                                                               |
|     X_52:bat[:str] := bat.pack("int":str, "varchar":str, "varchar":str);                                                                           |
|     X_53:bat[:int] := bat.pack(32:int, 8:int, 10:int);                                                                                             |
|     X_54:bat[:int] := bat.pack(0:int, 0:int, 0:int);                                                                                               |
|     X_4:int := sql.mvc();                                                                                                                          |
|     C_5:bat[:oid] := sql.tid(X_4:int, "sys":str, "t6933":str);                                                                                     |
|     X_17:bat[:int] := sql.bind(X_4:int, "sys":str, "t6933":str, "i":str, 0:int);                                                                   |
|     X_28:bat[:int] := algebra.projection(C_5:bat[:oid], X_17:bat[:int]);                                                                           |
|     X_22:bat[:str] := sql.bind(X_4:int, "sys":str, "t6933":str, "c":str, 0:int);                                                                   |
|     X_29:bat[:str] := algebra.projection(C_5:bat[:oid], X_22:bat[:str]);                                                                           |
|     X_47:bat[:str] := batcalc.ifnull(X_28:bat[:int], X_29:bat[:str]);                                                                              |
|     sql.resultSet(X_50:bat[:str], X_51:bat[:str], X_52:bat[:str], X_53:bat[:int], X_54:bat[:int], X_28:bat[:int], X_29:bat[:str], X_47:bat[:str]); |
| end user.s26_0;                                                                                                                                    |
+----------------------------------------------------------------------------------------------------------------------------------------------------+
44 tuples
sql>drop table t6933;
operation successful
sql>

The alternatives coalesce(expr1, expr2) and ifthenelse((expr1 IS NULL), expr2, expr1) produces more intermediate bats and are less efficient:

    X_17:bat[:bit] := sql.bind(X_4:int, "profiler":str, "b":str, "a":str, 0:int);
    X_27:bat[:bit] := algebra.projection(C_5:bat[:oid], X_17:bat[:bit]);
    X_22:bat[:bit] := sql.bind(X_4:int, "profiler":str, "b":str, "b":str, 0:int);
    X_28:bat[:bit] := algebra.projection(C_5:bat[:oid], X_22:bat[:bit]);
    X_29:bat[:bit] := batcalc.isnil(X_27:bat[:bit]);
    X_32:bat[:bit] := batcalc.not(X_29:bat[:bit]);
    X_36:bat[:bit] := batcalc.ifthenelse(X_32:bat[:bit], X_27:bat[:bit], X_28:bat[:bit]);

By having a native ifnull() we could replace:
    X_29:bat[:bit] := batcalc.isnil(X_27:bat[:bit]);
    X_32:bat[:bit] := batcalc.not(X_29:bat[:bit]);
    X_36:bat[:bit] := batcalc.ifthenelse(X_32:bat[:bit], X_27:bat[:bit], X_28:bat[:bit]);
with one:
    X_36:bat[:bit] := batcalc.ifnull(X_27:bat[:bit], X_28:bat[:bit]);