I’ve said it before and I’ll say it again, Oracle products don’t run properly on Tru64 anymore!
The latest issue came from an observation by a developer and involves problems with using NVL inside an EXISTS subquery. The following code is the test case I sent in my TAR to Oracle support.
CREATE TABLE tab1 (
code VARCHAR2(10),
description VARCHAR2(50)
);
ALTER TABLE tab1 ADD (
CONSTRAINT tab1_pk PRIMARY KEY (code)
);
INSERT INTO tab1 (code, description) VALUES ('1', 'ONE');
INSERT INTO tab1 (code, description) VALUES ('2', 'TWO');
INSERT INTO tab1 (code, description) VALUES ('3', 'THREE');
CREATE TABLE tab2 (
code VARCHAR2(10),
tab1_code VARCHAR2(10),
description VARCHAR2(50)
);
ALTER TABLE tab2 ADD (
CONSTRAINT tab2_pk PRIMARY KEY (code)
);
ALTER TABLE tab2 ADD (
CONSTRAINT tab2_tab1_fk FOREIGN KEY (tab1_code)
REFERENCES tab1(code)
);
CREATE INDEX tab2_tab1_fk_i ON tab2(tab1_code);
INSERT INTO tab2 (code, tab1_code, description) VALUES ('1', '1', 'ONE');
INSERT INTO tab2 (code, tab1_code, description) VALUES ('2', '1', 'ONE');
INSERT INTO tab2 (code, tab1_code, description) VALUES ('3', '1', 'ONE');
INSERT INTO tab2 (code, tab1_code, description) VALUES ('4', '2', 'TWO');
INSERT INTO tab2 (code, tab1_code, description) VALUES ('5', '2', 'TWO');
INSERT INTO tab2 (code, tab1_code, description) VALUES ('6', '2', 'TWO');
INSERT INTO tab2 (code, tab1_code, description) VALUES ('7', '3', 'THREE');
INSERT INTO tab2 (code, tab1_code, description) VALUES ('8', '3', 'THREE');
INSERT INTO tab2 (code, tab1_code, description) VALUES ('9', '3', 'THREE');
COMMIT;
VARIABLE p1 VARCHAR2(1)
BEGIN
:p1 := '%';
END;
/
SELECT t1.code
FROM tab1 t1
WHERE EXISTS(SELECT rowid
FROM tab2 t2
WHERE t2.tab1_code = t1.code
AND t2.code like :p1)
ORDER BY t1.code;
CODE
----------
1
2
3
3 rows selected.
SELECT t1.code
FROM tab1 t1
WHERE EXISTS(SELECT rowid
FROM tab2 t2
WHERE t2.tab1_code = t1.code
AND t2.code like NVL(:p1,'%'))
ORDER BY t1.code;
CODE
----------
1
1
1
2
2
2
3
3
3
9 rows selected.
SELECT t1.code
FROM tab1 t1
WHERE EXISTS(SELECT rowid
FROM tab2 t2
WHERE t2.tab1_code = t1.code
AND t2.code like COALESCE(:p1,'%'))
ORDER BY t1.code;
CODE
----------
1
2
3
3 rows selected.
From this you can see that then the bind variable is set to ‘%’ the query without the NVL works as expected, but the query using the NVL displays a row in the master table for each row found in the subquery. In comparison, the query using the COALESCE function works fine.
Now I realise that this is not a great query anyway, but you would still expect it to work!
Happy days in Tru64 land again 🙂
Once again I’m drawn to the inevitable conclusion that running Oracle products on anything but a tier-one platform is a bad idea. What’s more, judging by the recent arrival of Oracle 10g Release 2, there is now only a single tier-one platform. I don’t see a version for Solaris, HP-UX or Windows yet.
As for me, I’m still waiting for 10.1.0.4.0 to be released on Tru64. Release 2 is a long way off 🙂
Cheers
Tim…