-- TESTS FOR CASE AND SIMILAR OPERATIONS -- drop table testcase if exists; create table testcase(id integer, data varchar(10), filler varchar(10)); insert into testcase values(100,'xxxx',null); insert into testcase values(200,'yyyy',null); /*u1*/insert into testcase values(300,null,null); /*c2*/select id from testcase where data is not null; /*r300*/select id from testcase where data is null; /*rNULLVALUE*/select ifnull(data,'NULLVALUE') from testcase where data is null; /*rNULLVALUE*/select case data when 'xxxx' then 'X' else 'NULLVALUE' end from testcase where data is null; SELECT CASE data WHEN 'xxxx' THEN 'X' ELSE (CASE data WHEN 'yyyy' THEN 'Y' ELSE (CASE data WHEN 'zzzz' THEN 'Z' ELSE 'NOTFOUND' END) END) END FROM testcase; SELECT CASE data WHEN 'xxxx' THEN 'X' WHEN 'yyyy' THEN 'Y' WHEN 'zzzz' THEN 'Z' ELSE 'NOTFOUND' END FROM testcase; /*rALLNULL*/SELECT COALESCE (filler, data, 'ALLNULL') FROM testcase WHERE id = 300; /*r600.0*/select cast (sum(id) as double) from testcase; /*r600*/select coalesce(sum(id), 0) from testcase; /*r600.0*/select abs(coalesce(sum(id), 0)) from testcase; drop table testcase2 if exists; create table testcase2(id integer, data varchar(10), filler varchar(10), datecol date); /*rNULL*/select cast (sum(id) as double) from testcase2; /*r0*/select coalesce(sum(id), 0) from testcase2; /*r0.0*/select abs(coalesce(sum(id), 0)) from testcase2; /*R2005-10-25*/select coalesce(datecol, '2005-10-25') from testcase2; drop table test if exists; create table test (sel int, name1 varchar(3), name2 varchar(3)); insert into test (sel, name1, name2) values (0, 'foo', 'bar') insert into test (sel, name1, name2) values (1, 'baz', 'foo') insert into test (sel, name1, name2) values (1, 'foo', 'qux') select coalesce(a.name1, a.name2) as name,count(a.sel) as counter from test a group by coalesce(a.name1, a.name2) select case when a.sel=1 then a.name2 else a.name1 end as name, count(a.name1) as counter from test a group by case when a.sel=1 then a.name2 else a.name1 end -- nested expressions create table single (c char); select case c when 'X' then 1 else 2 end from single; insert into single values('X'); select case c when 'X' then 1 else 2 end from single; insert into single values(null); /*r X Y */select ifnull(c,'Y') from single /*r X Y */select coalesce(c,'Y') from single /*e*/select coalesce() from single /*e*/select coalesce(c) from single /*e*/select ifnull() from single /*e*/select ifnull(c) from single