--disable_abort_on_error

--disable_warnings
DROP TABLE IF EXISTS t1, t2, t3;
DROP VIEW  IF EXISTS v1, v2, v3, v4, v5, v6, v7;
--enable_warnings

CREATE TABLE t1 (EMPNUM INT, GRP INT);
INSERT INTO t1 VALUES (0, 10);
INSERT INTO t1 VALUES (2, 30);

CREATE TABLE t2 (EMPNUM INT, NAME CHAR(5));
INSERT INTO t2 VALUES (0, 'KERI');
INSERT INTO t2 VALUES (9, 'BARRY');


CREATE VIEW v1 AS
SELECT COALESCE(t2.EMPNUM,t1.EMPNUM) AS EMPNUM,
NAME, GRP FROM t2 LEFT OUTER JOIN t1 USING (EMPNUM);

SELECT * FROM v1;
SELECT COUNT(*) FROM v1 WHERE EMPNUM = 0 AND NAME = 'KERI' AND GRP = 10;

# here comes a wrong result
SELECT COUNT(*) FROM v1 WHERE EMPNUM = 9 AND NAME = 'BARRY' AND GRP IS NULL;
# here comes a wrong result
SELECT * FROM v1 WHERE EMPNUM = 9 ;
# here comes a correct result
SELECT * FROM v1 WHERE NAME = 'BARRY';
#---------------------------------

#### Some variations for comparison

# Subquery instead of VIEW  --> no Bug
SELECT COUNT(*) FROM (SELECT COALESCE(t2.EMPNUM,t1.EMPNUM) AS EMPNUM,
                             NAME, GRP 
                      FROM t2 LEFT OUTER JOIN t1 USING (EMPNUM)) AS SUBTAB
WHERE EMPNUM = 9 AND NAME = 'BARRY' AND GRP IS NULL;
#---------------------------------

# Left outer join + switched order of columns within COALESCE --> Bug
CREATE VIEW v2 AS
SELECT COALESCE(t1.EMPNUM,t2.EMPNUM) AS EMPNUM,
NAME, GRP FROM t2 LEFT OUTER JOIN t1 USING (EMPNUM);

SELECT * FROM v2;
SELECT COUNT(*) FROM v2 WHERE EMPNUM = 0 AND NAME = 'KERI' AND GRP = 10;
# here comes a wrong result
SELECT COUNT(*) FROM v2 WHERE EMPNUM = 9 AND NAME = 'BARRY' AND GRP IS NULL;
#---------------------------------

# Right outer join + initial order of columns within COALESCE --> Bug
CREATE VIEW v3 AS
SELECT COALESCE(t2.EMPNUM,t1.EMPNUM) AS EMPNUM,
NAME, GRP FROM t2 RIGHT OUTER JOIN t1 USING (EMPNUM);

SELECT * FROM v3;
SELECT COUNT(*) FROM v3 WHERE EMPNUM = 0 AND NAME = 'KERI' AND GRP = 10;
# here comes a wrong result
SELECT COUNT(*) FROM v3 WHERE EMPNUM = 2 AND NAME IS NULL  AND GRP = 30;
#---------------------------------

# Right outer join + switched order of columns within COALESCE --> Bug
CREATE VIEW v4 AS
SELECT COALESCE(t1.EMPNUM,t2.EMPNUM) AS EMPNUM,
NAME, GRP FROM t2 RIGHT OUTER JOIN t1 USING (EMPNUM);

SELECT * FROM v4;
SELECT COUNT(*) FROM v4 WHERE EMPNUM = 0 AND NAME = 'KERI' AND GRP = 10;
# here comes a wrong result
SELECT COUNT(*) FROM v4 WHERE EMPNUM = 2 AND NAME IS NULL  AND GRP = 30;
#---------------------------------

# No COALESCE --> no Bug
CREATE VIEW v5 AS
SELECT t2.EMPNUM AS EMPNUM,
NAME, GRP FROM t2 LEFT OUTER JOIN t1 USING (EMPNUM);

SELECT * FROM v5;
SELECT COUNT(*) FROM v5 WHERE EMPNUM = 0 AND NAME = 'KERI' AND GRP = 10;
SELECT COUNT(*) FROM v5 WHERE EMPNUM = 9 AND NAME = 'BARRY' AND GRP IS NULL;
#---------------------------------

# COALESCE NULL constant with Not NULL column value --> no Bug
CREATE VIEW v6 AS
SELECT COALESCE(NULL,t2.EMPNUM) AS EMPNUM, NAME FROM t2;

SELECT * FROM v6;
SELECT COUNT(*) FROM v6 WHERE EMPNUM = 0 AND NAME = 'KERI';
SELECT COUNT(*) FROM v6 WHERE EMPNUM = 9 AND NAME = 'BARRY';
#---------------------------------

# COALESCE NULL column value with Not NULL column value --> no Bug
CREATE TABLE t3 (EMPNUM INT, GRP INT);
INSERT INTO t3 VALUES (NULL, 10);

CREATE VIEW v7 AS
SELECT COALESCE(t1.EMPNUM,t3.EMPNUM) AS EMPNUM, 
       t1.EMPNUM EMPNUM1, t3.EMPNUM EMPNUM3, t1.GRP 
FROM t1, t3 WHERE t1.GRP = t3.GRP;

SELECT * FROM v7;
SELECT * FROM v7 WHERE EMPNUM = 0 AND GRP = 10;
#---------------------------------

DROP VIEW v1, v2, v3, v4, v5, v6, v7;

# Conclusions:
# - only outer joins suffer from this bug
# - The outer join direction (left or right) does not play any role.
# - All records suffer, where a not NULL column value is coalesced with
#   a "special" NULL column value.
#   "special" means a NULL produced by outer join completion of rows
# - The order of columns within coalesce does not play any role.