coalesce.sql

EXPLAIN VALUES COALESCE(1);
-- EXPLAIN: VALUES (COL1 SMALLINT) = ROW(COALESCE(1))

EXPLAIN VALUES COALESCE(1, 2);
-- EXPLAIN: VALUES (COL1 SMALLINT) = ROW(COALESCE(1, 2))

EXPLAIN VALUES COALESCE(1, 2, 3);
-- EXPLAIN: VALUES (COL1 SMALLINT) = ROW(COALESCE(1, 2, 3))

/* types */
VALUES COALESCE(1);
-- COL1: 1 (SMALLINT)

/* types */
VALUES COALESCE(1, 2);
-- COL1: 1 (SMALLINT)

/* types */
VALUES COALESCE(1, 200000000);
-- error 42804: data type mismatch in argument 2 of COALESCE: expected SMALLINT but got INTEGER

CREATE TABLE foo (f1 BIGINT);
INSERT INTO foo (f1) VALUES (NULL);
INSERT INTO foo (f1) VALUES (3);
SELECT COALESCE(f1, CAST(2 AS BIGINT)) FROM foo;
SELECT COALESCE(f1) FROM foo;
-- msg: CREATE TABLE 1
-- msg: INSERT 1
-- msg: INSERT 1
-- COL1: 2
-- COL1: 3
-- COL1: NULL
-- COL1: 3

VALUES COALESCE(1, 'hello');
-- error 42804: data type mismatch in argument 2 of COALESCE: expected SMALLINT but got CHARACTER