Re: mysterious behaviour where = (SELECT JAHR ...
From: Frank van Bortel <fbortel_at_home.nl>
Date: Sun, 26 Sep 2010 17:34:17 +0200
Message-ID: <75294$4c9f67fa$524ba3af$26196_at_cache3.tilbu1.nb.home.nl>
On 09/24/2010 10:33 AM, Norbert Winkler wrote:
> 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);
> ________________________________________________
>
>
>
Date: Sun, 26 Sep 2010 17:34:17 +0200
Message-ID: <75294$4c9f67fa$524ba3af$26196_at_cache3.tilbu1.nb.home.nl>
On 09/24/2010 10:33 AM, Norbert Winkler wrote:
> 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);
> ________________________________________________
>
>
>
Apart from the semantics, the technical design sucks.
Do NOT name columns in your design after something that is not stored. Naming a column YEAR (or JAHR in german) makes people expect something of a date is being stored.
You actually store character strings, not (part of) dates, hence YEAR_INDICATOR would be more appropriate. Calling the column YEAR will bite you. In performance (treating it as date, using implicit conversions, indexes useless), or while sorting.
-- Regards, Frank van BortelReceived on Sun Sep 26 2010 - 10:34:17 CDT