Re: SELECT CASE on NULL doesn't work (bug B165879)

Posted in 2004

Topics: SQL Development & Query Writing, Data Types & Schema Design, Platform-Specific Issues, Versions, Editions & End-of-Life

Jonathan — Wed, 04 Feb 2004 21:09:13 — source: IIUG Forums & Mailing Lists

--0__=08BBE4A3DFE146CE8f9e8a93df938690918c08BBE4A3DFE146CE Content-type: text/plain; charset=US-ASCII Content-transfer-encoding: quoted-printable Oops - red face time! :-( I managed to get two errors in my commentary on this issue (originally posted in just classics@iiug.org). 1. I got the logic in my test case inverted. 2. I managed to get the wrong version numbers out. I have entered B165879 in PTS for this. I've confirmed the problem in = IDS 7.31.UD5 on Solaris 8; I've confirmed the absence of the problem in IDS= 9.21.UC1, 9.30.UC3, 9.40.UC1. June Hunt said it was not a problem in 9.20.UC3. It appears, then, to be an issue only in the 7.31 versions o= f IDS. Gary Holeman - you reported the problem initially; please can you confi= rm that you were working with an IDS 7.31 (or, perish the thought, IDS 7.3= 0) server? My reproduction script is: CREATE TABLE id_rec (id INTEGER NOT NULL PRIMARY KEY, fullname VARCHAR(= 30) NOT NULL); CREATE TABLE pers_rec(id INTEGER NOT NULL REFERENCES id_rec(id) PRIMARY= KEY); INSERT INTO id_rec VALUES(1, "Mr Floozy"); -- Correct answer has status =3D "*IS NULL" -- Incorrect answer has status =3D "NOT NULL" -- Informix Outer join SELECT i.fullname, p.id, CASE p.id WHEN NULL THEN "*IS NULL" ELSE "NOTNULL" END AS STATUS FROM id_rec i, OUTER pers_rec p WHERE i.id =3D p.id; -- ISO Outer join SELECT i.fullname, p.id, CASE p.id WHEN NULL THEN "*IS NULL" ELSE "NOTNULL" END AS STATUS FROM id_rec AS i LEFT JOIN pers_rec AS p ON i.id =3D p.id; -- Jonathan Leffler (jleffler@us.ibm.com) STSM, Informix Database Engineering, IBM Data Management 4100 Bohannon Drive, Menlo Park, CA 94025 Tel: +1 650-926-6921 Tie-Line: 630-6921 "I don't suffer from insanity; I enjoy every minute of it!" = --0__=08BBE4A3DFE146CE8f9e8a93df938690918c08BBE4A3DFE146CE Content-type: text/html; charset=US-ASCII Content-Disposition: inline Content-transfer-encoding: quoted-printable <html><body> Oops - red face time! :-( I managed to get two errors in my commentary on this issue (originally = posted in just classics@iiug.org). 1. I got the logic in my test case inverted. 2. I managed to get the wrong version numbers out. I have entered B165879 in PTS for this. I've confirmed the problem in = IDS 7.31.UD5 on Solaris 8; I've confirmed the absence of the problem in= IDS 9.21.UC1, 9.30.UC3, 9.40.UC1. June Hunt said it was not a problem= in 9.20.UC3. It appears, then, to be an issue only in the 7.31 versio= ns of IDS. Gary Holeman - you reported the problem initially; please can you confi= rm that you were working with an IDS 7.31 (or, perish the thought, IDS = 7.30) server? My reproduction script is: CREATE TABLE id_rec (id INTEGER NOT NULL PRI= MARY KEY, fullname VARCHAR(30) NOT NULL); CREATE TABLE pers_rec(id INTEGER NOT NULL RE= FERENCES id_rec(id) PRIMARY KEY); INSERT INTO id_rec VALUES(1, "Mr Floozy= "); -- Correct answer has status =3D "*IS N= ULL" -- Incorrect answer has status =3D "NOT= NULL" -- Informix Outer join SELECT i.fullname, p.id, CASE p.id WHEN NULL= THEN "*IS NULL" ELSE "NOT NULL" END AS STATUS</fon= t> FROM id_rec i, OUTER pers_rec p WHERE i.id = =3D p.id; -- ISO Outer join SELECT i.fullname, p.id, CASE p.id WHEN NULL= THEN "*IS NULL" ELSE "NOT NULL" END AS STATUS</fon= t> FROM id_rec AS i LEFT JOIN pers_rec AS p ON= i.id =3D p.id; -- Jonathan Leffler (jleffler@us.ibm.com) STSM, Informix Database Engineering, IBM Data Management 4100 Bohannon Drive, Menlo Park, CA 94025 Tel: +1 650-926-6921 Tie-Line: 630-6921 "I don't suffer from insanity; I enjoy every minute of it!&q= uot; </body></html>= --0__=08BBE4A3DFE146CE8f9e8a93df938690918c08BBE4A3DFE146CE--

↪ replying to Jonathan

Scott — Wed, 04 Feb 2004 22:00:30 — source: IIUG Forums & Mailing Lists

Three times??? posting <HTML>junk<HTML> should count too. I do forgive the MicroSlop width wrap at 70 characters, though. But it is messy... Jonathan Le.... wrote: > --0__=08BBE4A3DFE146CE8f9e8a93df938690918c08BBE4A3DFE146CE > Content-type: text/plain; charset=US-ASCII > Content-transfer-encoding: quoted-printable > > > Oops - red face time! :-( > > I managed to get two errors in my commentary on this issue (originally > posted in just classics@iiug.org). > 1. I got the logic in my test case inverted. > 2. I managed to get the wrong version numbers out. > > I have entered B165879 in PTS for this. I've confirmed the problem in = > IDS > 7.31.UD5 on Solaris 8; I've confirmed the absence of the problem in IDS= > > 9.21.UC1, 9.30.UC3, 9.40.UC1. June Hunt said it was not a problem in > 9.20.UC3. It appears, then, to be an issue only in the 7.31 versions o= > f > IDS. > > Gary Holeman - you reported the problem initially; please can you confi= > rm > that you were working with an IDS 7.31 (or, perish the thought, IDS 7.3= > 0) > server? > > My reproduction script is: > > CREATE TABLE id_rec (id INTEGER NOT NULL PRIMARY KEY, fullname VARCHAR(= > 30) > NOT NULL); > CREATE TABLE pers_rec(id INTEGER NOT NULL REFERENCES id_rec(id) PRIMARY=> > KEY); > INSERT INTO id_rec VALUES(1, "Mr Floozy");> > -- Correct answer has status =3D "*IS NULL" > -- Incorrect answer has status =3D "NOT NULL" > > -- Informix Outer join > SELECT i.fullname, p.id, CASE p.id WHEN NULL THEN "*IS NULL" ELSE "NOT> NULL" END AS STATUS > FROM id_rec i, OUTER pers_rec p WHERE i.id =3D p.id; > > -- ISO Outer join > SELECT i.fullname, p.id, CASE p.id WHEN NULL THEN "*IS NULL" ELSE "NOT> NULL" END AS STATUS > FROM id_rec AS i LEFT JOIN pers_rec AS p ON i.id =3D p.id; > > -- > Jonathan Leffler (jleffler@us.ibm.com) > STSM, Informix Database Engineering, IBM Data Management > 4100 Bohannon Drive, Menlo Park, CA 94025 > Tel: +1 650-926-6921 Tie-Line: 630-6921 > "I don't suffer from insanity; I enjoy every minute of it!" > = > > --0__=08BBE4A3DFE146CE8f9e8a93df938690918c08BBE4A3DFE146CE > Content-type: text/html; charset=US-ASCII > Content-Disposition: inline > Content-transfer-encoding: quoted-printable > > <html><body> > Oops - red face time! :-( [.big clip.] > </body></html> > > --0__=08BBE4A3DFE146CE8f9e8a93df938690918c08BBE4A3DFE146CE-- > > -- ( ______ )) .-- Scott MacKenzie; Dine' College ISD --. >===<--. C|~~| (>--- Phone/Voice Mail: 928-724-6639 ---<) | ; o |-' | | \\\\--- Senior DBA/CARS Coordinator/Etc. --/ | _ | `--' `-- E: scottm at dinecollege dot edu -' `-----'

Glossary

IDS (Informix Dynamic Server)