Saturday, December 18, 2010

Valid or not?

I ran into an SQL-statement that was valid in test running Oracle 11.2.0.1 but not valid in production also running Oracle 11.2.0.1!? Since I didn't have access to v$parameter in production it took a while to figure out... Here it is (note how using the ANSI-join syntax indirectly allows a feature that is otherwise not allowed):
SQL> SELECT * FROM v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> REM Set default value optimizer features enable
SQL> ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE='11.2.0.1';
Session er ændret.

SQL> DROP TABLE t2 PURGE;
Tabel er droppet.

SQL> DROP TABLE t1 PURGE;
Tabel er droppet.

SQL> CREATE TABLE t1(
2 t1_id INTEGER NOT NULL
3 ,t1_txt VARCHAR2(20) NOT NULL
4 ,CONSTRAINT t1_pk
5 PRIMARY KEY(t1_id)
6 );
Tabel er oprettet.

SQL> CREATE TABLE t2(
2 t2_id INTEGER NOT NULL
3 ,t1_id INTEGER NOT NULL
4 ,t2_val NUMBER(2,0) NOT NULL
5 ,CONSTRAINT t2_pk
6 PRIMARY KEY(t2_id)
7 ,CONSTRAINT t2_fk_t1
8 FOREIGN KEY(t1_id)
9 REFERENCES t1(t1_id)
10 );
Tabel er oprettet.

SQL> INSERT INTO t1 VALUES(1,'Txt1');
1 række er oprettet.

SQL> INSERT INTO t1 VALUES(2,'Txt2');
1 række er oprettet.

SQL> INSERT INTO t1 VALUES(3,'Txt3');
1 række er oprettet.

SQL> INSERT INTO t2 VALUES(1,1,1);
1 række er oprettet.

SQL> INSERT INTO t2 VALUES(2,2,1);
1 række er oprettet.

SQL> INSERT INTO t2 VALUES(3,2,2);
1 række er oprettet.

SQL> INSERT INTO t2 VALUES(4,2,3);
1 række er oprettet.

SQL> SELECT t1.t1_id,t1.t1_txt,t2.t2_id,t2.t2_val
2 FROM t1
3 ,t2
4 WHERE t2.t1_id(+)=t1.t1_id
5 AND t2.t2_val(+) IN (1,2)
6 ;
T1_ID T1_TXT T2_ID T2_VAL
---------- -------------------- ---------- ----------
1 Txt1 1 1
2 Txt2 2 1
2 Txt2 3 2
3 Txt3

SQL> SELECT t1.t1_id,t1.t1_txt,t2.t2_id,t2.t2_val
2 FROM t1
3 LEFT JOIN
4 t2 ON (t2.t1_id=t1.t1_id AND t2_val IN (1,2))
5 ;
T1_ID T1_TXT T2_ID T2_VAL
---------- -------------------- ---------- ----------
1 Txt1 1 1
2 Txt2 2 1
2 Txt2 3 2
3 Txt3


SQL> ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE='9.2.0';
Session er ændret.

SQL> REM Same SELECTs as above, but now...
SQL> SELECT t1.t1_id,t1.t1_txt,t2.t2_id,t2.t2_val
2 FROM t1
3 ,t2
4 WHERE t2.t1_id(+)=t1.t1_id
5 AND t2.t2_val(+) IN (1,2)
6 ;
AND t2.t2_val(+) IN (1,2)
*
FEJL i linie 5:
ORA-01719: ydre sammenkædninger (+) er ikke tilladt i operanden for OR- eller
IN-klausuler


SQL> SELECT t1.t1_id,t1.t1_txt,t2.t2_id,t2.t2_val
2 FROM t1
3 LEFT JOIN
4 t2 ON (t2.t1_id=t1.t1_id AND t2_val IN (1,2))
5 ;
T1_ID T1_TXT T2_ID T2_VAL
---------- -------------------- ---------- ----------
1 Txt1 1 1
2 Txt2 2 1
2 Txt2 3 2
3 Txt3

0 Comments:

Post a Comment

<< Home