mysterious behaviour where = (SELECT JAHR ...

From: Norbert Winkler <norbert.winkler1_at_gmx.de>
Date: Fri, 24 Sep 2010 10:33:47 +0200
Message-ID: <18zm0amjxzqek.1iinhm3b7czd4$.dlg_at_40tude.net>

Hi,

the following script creates two tables. The problem causes due to an error using a not existing YEAR column.
My error, in the real tables, dues to a column named "JAHR" the german word for year. For testing in a non-german-environment I've replaced JAHR with YEAR. In Statement <1> all records are shown. The machine should show an error like it does with statement <2> or show only the record with "2010" like in the correct statment <3>.

---

CREATE TABLE TST_ONE_YEAR (
  TST_YEAR VARCHAR2(4) NOT NULL
)
/

CREATE TABLE TST_TAB (
  YEAR VARCHAR2(4) NOT NULL
)
/

INSERT INTO TST_ONE_YEAR (TST_YEAR) VALUES ('2010');

INSERT INTO TST_TAB (YEAR) VALUES ('2008');
INSERT INTO TST_TAB (YEAR) VALUES ('2009');
INSERT INTO TST_TAB (YEAR) VALUES ('2010');
INSERT INTO TST_TAB (YEAR) VALUES ('XXXX');

commit;

select * from TST_ONE_YEAR;
select * from TST_TAB;

/* <1> */

select * from TST_TAB WHERE YEAR = (SELECT YEAR FROM TST_ONE_YEAR);


/* <2> */

select * from TST_TAB WHERE YEAR = (SELECT YEAR1 FROM TST_ONE_YEAR);

/* <3> */

select * from TST_TAB WHERE YEAR = (SELECT TST_YEAR FROM TST_ONE_YEAR);

---

-- 
Norbert
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit
Production

Received on Fri Sep 24 2010 - 03:33:47 CDT