CIO: Counter-intuitive Oracle

Valid or not?

2010-12-18T10:45:00.000-08:00

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

I solemnly DECLARE: NOT NULL

2010-10-02T10:05:00.000-07:00

An interesting fact about NOT NULL (declarative) constraints, see Not NULL « Oracle Scratchpad and Ask Tom "Constraints - Table and Column Level"

SQL Data Modeler is now free!

2010-10-02T09:39:00.000-07:00

See Data Modeler Pricing FAQ (updated September 2010).

Tasty? PL/SQL Developer in Wine!

2010-09-05T01:25:00.000-07:00

Running Oracle XE on your Linux? Missing your favorite PL/SQL GUI tool?? Check out this article PL/SQL DEVELOPER UNDER WINE!!!

Give me a FUNCTION that...

2010-09-03T07:58:00.000-07:00

DECLARE
  i PLS_INTEGER:=0;
  maxlen PLS_INTEGER:=0;
BEGIN
  FOR j IN 1..str.COUNT LOOP
    IF LENGTH(str(j))>maxlen THEN
      maxlen:=LENGTH(str(j));
      i:=j;
    END IF;
  END LOOP;
  RETURN i;
END;

Doesn't compile (it's sort of PL/SQL if you wonder). Lacks some details (what's that str anyway). But you get the picture (it's job interview pseudocode ;o)

Indexing Nothing... CONSTANTly

2010-08-08T12:17:00.000-07:00

Indexing NULLs (Empty Spaces) « Richard Foote’s Oracle Blog

Twitter, not Not NOT

2009-10-12T12:29:00.001-07:00

SO SIMPLE and yet SO LOUSY!

Except NullPointerException?

2009-10-12T11:22:00.000-07:00

The Cafes » Imagine There's No Null

Don't always AskTom

2009-05-09T01:30:00.000-07:00

When trying to reach AskTom from my work PC - it errors out with a PL/SQL error:

... maybe there's a WHEN OTHERS too many in there? Maybe my browser string - HTTP_USER_AGENT - has gotten too long after having installed the latest version of the .NET-framework!?

HTTP_USER_AGENT=Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.1; Trident/4.0; .NET CLR 2.0.50727; .NET CLR 3.0.04506.30; .NET CLR 3.0.04506.648; .NET CLR 1.1.4322; InfoPath.2; .NET CLR 3.5.21022; OfficeLiveConnector.1.3; OfficeLivePatch.0.0; .NET CLR 3.0.4506.2152; .NET CLR 3.5.30729)

Internet Explorer (8) will do that to you sometimes :o(

Foreign NULLs!?

2009-05-09T01:26:00.001-07:00

Jonathan Lewis has an interesting entry about how foreign keys handle nulls - see Foreign Keys!

NULL is not %null%

2008-01-17T06:18:00.000-08:00

APEX Null Value Handling in LOVs (SageLogix Tech Tip).

Everything is NOT IN nothing

2008-01-17T06:07:00.000-08:00

Everything is NOT IN nothing (a.k.a. NULL). See the Oracle Documentation on the IN Condition - it says NOT IN [is] Equivalent to !=ALL. Evaluates to FALSE if any member of the set is NULL.


SQL> SELECT 'x' FROM DUAL
  2   WHERE 3 NOT IN (1,2,NULL);

no rows selected

Counter-intuitive or what?

Not so overlapping OVERLAPS!

2008-01-04T12:36:00.000-08:00

The OVERLAPS operator is counter-intuitive when it comes to the same date:

SQL> select * from v$version where rownum<2;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod

SQL> select 'x' from dual 
  2      where (trunc(sysdate),trunc(sysdate)+24)
  3                  overlaps (trunc(sysdate)+24,trunc(sysdate)+42);

Ingen rækker er valgt

But apparently this is according to the standard (see sql1992.txt) where it says (under "8.11 <overlaps predicate>"):

( S1 > S2 AND NOT ( S1 >= T2 AND T1 >= T2 ) )
OR
( S2 > S1 AND NOT ( S2 >= T1 AND T2 >= T1 ) )
OR
( S1 = S2 AND ( T1 <> T2 OR T1 = T2 ) )

Notice the equal in the S2 >= T1 comparison that is negated (i.e. NOT). So be careful out there - know thou standards!

Undocumented OVERLAPS

2006-12-13T14:42:00.000-08:00

SQL*Plus: Release 10.2.0.1.0 - Production on On Dec 13 23:36:15 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> select 'yes' from dual where (sysdate-5,sysdate) overlaps (sysdate-2,sysdate-1);

'YE
---
yes

SQL> select 'yes' from dual where (-5,0) overlaps (-2,-1);
select 'yes' from dual where (-5,0) overlaps (-2,-1)
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected DATE got NUMBER

See ANSI_SQL - Overlaps

Paying is (not) an option!

2006-10-12T01:01:00.000-07:00

(How would you feel about something like the following?)

ANNOUNCEMENT: The new "Capital of France" version of this Letter is here and it's free

This is the all new version of the Letter I wrote some time ago. This new version, rev. 11hR3 (codenamed "Capital of France") has been completely rewritten. The previous version was sold bundled together with some other letters under the name "Letter Suite". This new version of the Letter is FREE. However, please note that some of the words used are specially licensed (as part of one or more speciel add-on options to the core) and must not be read without paying a license fee (in fact some of the words in the sentence you are currently reading may or may not be read without paying) – so please read the license documentation carefully (in fact you should read it before reading this, because some of these words could in fact be in the group of words that you must not read without paying).

If by now you have read all of the above, please send payment to me (Larry(I'll make you pay for something)-wannabe) for the words that were read that were not free (as outlined in the Licensing Information)…

(Inspired by OWB options, see Database Licensing Information 10g Release 2 (10.2))

The empty string IS NULL

2006-06-22T11:32:00.000-07:00

LENGTH('MGS''X')=LENGTH('MGS')+LENGTH('X')=3+1=4

but

LENGTH('MGS''')=LENGTH('MGS')+0=3+0=3 (which IS NOT NULL)
vs.
LENGTH('MGS')+LENGTH('')=3+NULL=NULL (all = are IS NULL)

Counter-intuitive or what?

No more FOREIGN KEYS!? (BUG)

2006-04-25T13:35:00.000-07:00

I do not (want to) believe it:

SQL*Plus: Release 10.2.0.1.0 - Production on Ti Apr 25 22:31:37 2006

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> create table t1(c1 number,constraint t1_pk primary key (c1) using
index novalidate);

Table created.

SQL> create table t2 (c1 number ,c2 number );

Table created.

SQL> alter table t2 add  constraint t2_pk primary key (c1, c2);

Table altered.

SQL> alter table t2 add constraint eir_related_event_fk foreign key (c2)
references t1 (c1) ;

Table altered.

SQL> select eir.c2 from t2 eir join t1 e on eir.c2 = e.c1;
select eir.c2 from t2 eir join t1 e on eir.c2 = e.c1
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel

Straight from MetaLink - Bug No. 5126551.

Maybe we should just stop using FOREIGN KEY constraints!?!?!

Equal or NULL (that IS the question)

2006-04-20T13:10:00.000-07:00

Yesterday I recalled these lyrics:

...
and everything under the Sun is in tune
but the Sun is eclipsed by the Moon
[Pink Floyd: Eclipse (Dark Side of the Moon)]

In association with amazon.co.uk

And it made me think:

all other things being equal
but nothing IS equal to NULL
[Dark Side of (ANSI) SQL]

How many rows are there?

2006-04-20T11:57:00.000-07:00

How many rows should the following query return?

select level as l
 from  dual
 connect by level<=742

SQL*Plus may tell you 1 (one row):

TOAD may tell you 500 (more or exactly):

SQL Developer may tell you 10:

Maybe they should all start counting:

select count(*) from (
select level as l
 from  dual
 connect by level<=742)

... and they all answer 742!

More of nothing...

2006-01-25T12:52:00.000-08:00

Nothing is and has always been a very hot topic - see previous posting, see The max value of Null and see Something about nothing....

NULLs and 3VL!

2006-01-18T12:23:00.000-08:00

Missing values (i.e. NULLs) impact on SQL is maybe one of the biggest gotchas in the field of relational databases and Oracle is no exception: Three-valued logic comes from Nothing (and gets you nowhere) and it creeps in everywhere - just to name one sighting see = null in WB_TRUNCATE_TABLE.

WHENEVER OSERROR EXIT what?

2006-01-17T13:58:00.000-08:00

The SQL*Plus User's Guide and Reference, Release 9.2 on WHENEVER OSERROR command does not mention OSCODE as a possible (system) variable to be used as exit code (just as SQL.SQLCODE is for WHENEVER SQLERROR EXIT) - however this is suggested by SQL*Plus when issuing just WHENEVER OSERROR;.

So which is right (the manual or SQL*Plus) - I cannot tell (Oracle is funny (that way)).

Counter-intuitive Oracle...

2005-12-21T02:09:00.000-08:00

Three links to get this BLOG started:

Welcome...