Add support for scalar function IFNULL(expr1, expr2)

[monetdb-team]

Date: 2020-07-22 15:07:18 +0200
From: Martin van Dinther <<martin.van.dinther>>
To: SQL devs <>
Version: 11.37.7 (Jun2020)

Last updated: 2020-07-22 15:07:18 +0200

Comment 27930

Date: 2020-07-22 15:07:18 +0200
From: Martin van Dinther <<martin.van.dinther>>

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, 23) as "ifnull(i,23)", 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, 23) as "ifnull(i,23)", 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, 23) as "ifnull(i,23)", 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]);

[njnes]

mapped into coalesce