CREATE TABLE t(a INTEGER, ts timestamp time index);

Affected Rows: 0

INSERT INTO t VALUES (1, 1), (null, 2), (3, 3);

Affected Rows: 3

SELECT ISNULL(a) from t;

+-------------+
| isnull(t.a) |
+-------------+
| false       |
| true        |
| false       |
+-------------+

SELECT ISNULL(null);

+--------------+
| isnull(NULL) |
+--------------+
| true         |
+--------------+

SELECT ISNULL(1);

+------------------+
| isnull(Int64(1)) |
+------------------+
| false            |
+------------------+

SELECT ISNULL(-1);

+-------------------+
| isnull(Int64(-1)) |
+-------------------+
| false             |
+-------------------+

SELECT ISNULL(1.0);

+--------------------+
| isnull(Float64(1)) |
+--------------------+
| false              |
+--------------------+

SELECT ISNULL(true);

+-----------------------+
| isnull(Boolean(true)) |
+-----------------------+
| false                 |
+-----------------------+

SELECT ISNULL('string');

+------------------------+
| isnull(Utf8("string")) |
+------------------------+
| false                  |
+------------------------+

SELECT FIRST_VALUE(1);

+-----------------------+
| first_value(Int64(1)) |
+-----------------------+
| 1                     |
+-----------------------+

SELECT FIRST_VALUE('a');

+------------------------+
| first_value(Utf8("a")) |
+------------------------+
| a                      |
+------------------------+

SELECT LAST_VALUE(1);

+----------------------+
| last_value(Int64(1)) |
+----------------------+
| 1                    |
+----------------------+

SELECT LAST_VALUE('a');

+-----------------------+
| last_value(Utf8("a")) |
+-----------------------+
| a                     |
+-----------------------+

-- MySQL-compatible IF function tests
SELECT IF(true, 'yes', 'no');

+------------------------------------------+
| if(Boolean(true),Utf8("yes"),Utf8("no")) |
+------------------------------------------+
| yes                                      |
+------------------------------------------+

SELECT IF(false, 'yes', 'no');

+-------------------------------------------+
| if(Boolean(false),Utf8("yes"),Utf8("no")) |
+-------------------------------------------+
| no                                        |
+-------------------------------------------+

SELECT IF(NULL, 'yes', 'no');

+---------------------------------+
| if(NULL,Utf8("yes"),Utf8("no")) |
+---------------------------------+
| no                              |
+---------------------------------+

SELECT IF(1, 'yes', 'no');

+-------------------------------------+
| if(Int64(1),Utf8("yes"),Utf8("no")) |
+-------------------------------------+
| yes                                 |
+-------------------------------------+

SELECT IF(0, 'yes', 'no');

+-------------------------------------+
| if(Int64(0),Utf8("yes"),Utf8("no")) |
+-------------------------------------+
| no                                  |
+-------------------------------------+

SELECT IF(-1, 'yes', 'no');

+--------------------------------------+
| if(Int64(-1),Utf8("yes"),Utf8("no")) |
+--------------------------------------+
| yes                                  |
+--------------------------------------+

SELECT IF(1.5, 'yes', 'no');

+-----------------------------------------+
| if(Float64(1.5),Utf8("yes"),Utf8("no")) |
+-----------------------------------------+
| yes                                     |
+-----------------------------------------+

SELECT IF(0.0, 'yes', 'no');

+---------------------------------------+
| if(Float64(0),Utf8("yes"),Utf8("no")) |
+---------------------------------------+
| no                                    |
+---------------------------------------+

-- Test with table column
SELECT IF(a > 1, 'greater', 'not greater') FROM t;

+--------------------------------------------------------+
| if(t.a > Int64(1),Utf8("greater"),Utf8("not greater")) |
+--------------------------------------------------------+
| not greater                                            |
| not greater                                            |
| greater                                                |
+--------------------------------------------------------+

-- Test numeric return types
SELECT IF(true, 100, 200);

+-----------------------------------------+
| if(Boolean(true),Int64(100),Int64(200)) |
+-----------------------------------------+
| 100                                     |
+-----------------------------------------+

SELECT IF(false, 100, 200);

+------------------------------------------+
| if(Boolean(false),Int64(100),Int64(200)) |
+------------------------------------------+
| 200                                      |
+------------------------------------------+

-- Test with IFNULL (should already work via DataFusion)
SELECT IFNULL(NULL, 'default');

+------------------------------+
| ifnull(NULL,Utf8("default")) |
+------------------------------+
| default                      |
+------------------------------+

SELECT IFNULL('value', 'default');

+---------------------------------------+
| ifnull(Utf8("value"),Utf8("default")) |
+---------------------------------------+
| value                                 |
+---------------------------------------+

-- Test COALESCE (should already work via DataFusion)
SELECT COALESCE(NULL, NULL, 'third');

+-----------------------------------+
| coalesce(NULL,NULL,Utf8("third")) |
+-----------------------------------+
| third                             |
+-----------------------------------+

SELECT COALESCE('first', 'second');

+----------------------------------------+
| coalesce(Utf8("first"),Utf8("second")) |
+----------------------------------------+
| first                                  |
+----------------------------------------+

DROP TABLE t;

Affected Rows: 0