Tuesday, April 25, 2006

No more FOREIGN KEYS!? (BUG)

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!?!?!

Thursday, April 20, 2006

Equal or NULL (that IS the question)

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)]
Pink Floyd: Dark Side of the Moon - Buy it at amazon.co.uk
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?

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!