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…
I’d settle for Oracle working properly on ANY platform…
Right now I’m VERY unhappy about the level and quantity of bug fixing coming out of Larry-land, let me tell you! If it continues like this, I’m going to start looking very seriously at other db alternatives…
Yes. It gets you down when you spend more time on Meatlink that doing your real job π
Oracle will fix it eventually.