DROP DATABASE IF EXISTS mcs171_db;
CREATE DATABASE mcs171_db;
USE mcs171_db;
SET default_storage_engine=Columnstore;
CREATE TABLE t1 (a CHAR(1), b INT, c DATETIME, d DOUBLE);
INSERT INTO t1 VALUES (NULL, NULL, '0-0-0', NULL),('a', 12, '1212-12-12', 1.19691E+100),('b', 13, '1313-3-13 13:13:13', 2.1961E+18),('c', 14, '1414-4-14', 0.16191),('d', 15, '2015-5-15 15:15:15', 1.971917);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` char(1) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `c` datetime DEFAULT NULL,
  `d` double DEFAULT NULL
) ENGINE=Columnstore DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci
SELECT IFNULL(NULL, 'abc');
IFNULL(NULL, 'abc')
abc
SELECT IFNULL(NULL, 123);
IFNULL(NULL, 123)
123
SELECT IFNULL('abc', 123);
IFNULL('abc', 123)
abc
SELECT ISNULL(NULL);
ISNULL(NULL)
1
SELECT ISNULL(1);
ISNULL(1)
0
SELECT ISNULL('a');
ISNULL('a')
0
SELECT NULLIF(1, 1);
NULLIF(1, 1)
NULL
SELECT NULLIF('abc', 'a');
NULLIF('abc', 'a')
abc
SELECT NULLIF(1, 'abcd');
NULLIF(1, 'abcd')
1
SELECT a, ISNULL(a) FROM t1;
a	ISNULL(a)
NULL	1
a	0
b	0
c	0
d	0
SELECT b, ISNULL(b) FROM t1;
b	ISNULL(b)
NULL	1
12	0
13	0
14	0
15	0
SELECT c, ISNULL(c) FROM t1;
c	ISNULL(c)
0000-00-00 00:00:00	0
1212-12-12 00:00:00	0
1313-03-13 13:13:13	0
1414-04-14 00:00:00	0
2015-05-15 15:15:15	0
SELECT d, ISNULL(d) FROM t1;
d	ISNULL(d)
NULL	1
1.19691e100	0
2.1961e18	0
0.16191	0
1.971917	0
SELECT a, IFNULL(a, 'Null Char') FROM t1;
a	IFNULL(a, 'Null Char')
NULL	Null Char
a	a
b	b
c	c
d	d
SELECT b, IFNULL(b, 'Null Integer') FROM t1;
b	IFNULL(b, 'Null Integer')
NULL	Null Integer
12	12
13	13
14	14
15	15
SELECT c, IFNULL(d, 'Null Decimal') FROM t1;
c	IFNULL(d, 'Null Decimal')
0000-00-00 00:00:00	Null Decimal
1212-12-12 00:00:00	1.19691e100
1313-03-13 13:13:13	2.1961e18
1414-04-14 00:00:00	0.16191
2015-05-15 15:15:15	1.971917
SELECT a, NULLIF(a, 'c') FROM t1;
a	NULLIF(a, 'c')
NULL	NULL
a	a
b	b
c	NULL
d	d
SELECT b, NULLIF(b, 15) FROM t1;
b	NULLIF(b, 15)
NULL	-2147483648
12	12
13	13
14	14
15	NULL
SELECT c, NULLIF(c, '2015-5-15') FROM t1;
c	NULLIF(c, '2015-5-15')
0000-00-00 00:00:00	0000-00-00 00:00:00
1212-12-12 00:00:00	1212-12-12 00:00:00
1313-03-13 13:13:13	1313-03-13 13:13:13
1414-04-14 00:00:00	1414-04-14 00:00:00
2015-05-15 15:15:15	2015-05-15 15:15:15
DROP DATABASE mcs171_db;