Re: Weird Query

From: Maxim Demenko <mdemenko_at_arcor.de>
Date: Mon, 01 Aug 2005 15:19:44 +0200
Message-ID: <42ee2284$0$6972$9b4e6d93@newsread2.arcor-online.net>

Marian Aldenhövel schrieb:
> Hi,
>

>> Well, it's simply an agreement, i could say that en empty string 
>> doesn't compare with any "real" value too, but it's not the point, the 
>> point is , that you should write your sql *according* the rules which 
>> are adopted in the rdbms engine.

>
>
> I am absolutely willing to do so. I can't which is why I posted my
> question.
>
> How would a WHERE-Constraint look that returned only the rows in which
> a given field is not empty? "Not empty" here being defined as
> "something to see" or "text to read".
It is very simply: WHERE column IS NOT NULL Nothing more.
I am not (at this time, anyway)
> interested in the very fine distinction between NULL and an empty
> string

There is *NO* distiction in Oracle
and how it is handled by Oracle. I just want those records
> that hold some text.
>

>> it is very irrational to expect that Oracle behave equally those 

>
> > SQL Server's.
>
> I don't.
>
> I fully expect differences and am prepared for them. In fact the
> program whose malfunction I traced to the problem being discussed
> here can as of now use Paradox, MS Access, MS-SQL and
> Interbase/Firebird as backend. In between them this makes for a
> very "interesting" database access layer :-).
>

>> do you expect the very *same* row to be returned for both where 
>> clauses - WHERE PP_LOCK IS NULL and WHERE PP_LOCK IS NOT NULL?

>
>
> No.
>
> I expect "WHERE PP_LOCK=''" to return one set of rows A, "WHERE
> PP_LOCK<>''"
> to return another set of rows B

It is wrong expectation. You can blindly substitute '' by NULL, in that case you get 2 WHERE conditions
WHERE "PP_LOCK"=NULL
and
WHERE "PP_LOCK"<>NULL
both are wrong ( that's mean you should *NOT* use them ), but nethertheless, Oracle raises no exceptions and silly evaluates *both* expressions to NULL, which means *NO ROWS* comes in question.

and the intersection of A and B to be
> empty. The results of "equal" and "does not equal" should be distinct.
Only if you compare properly, in case of empty string means: WHERE column IS NULL
and
WHERE column IS NOT NULL

What you are doing, is ( very simplified of course ) comparable with: (4-4)=(5-5) => 4(1-1)=5(1-1) => 4=5 => 2*2 = 5
>
> I further expect "WHERE PP_LOCK IS NULL" to return another set C and
> I do not care whether C is included in A or not. The _Union_ of A, B
> and C should however in my very limited understanding of relational
> logic consist of _every_ record in the table.
>
> If it's NULL it's in C. If it's not NULL it either has some text or
> an empty string.

The very same again - if it's empty string, then it is in C. The empty string is NULL.
So it's in A or B.
>
> This is not the case here, though. My test record is in none of A, B or
> C. And very clearly it does _not_ have NULL in PP_LOCK, I count 47
> characters of text.
>
> Ciao, MM

Your test record contains 47 characters, also it is definitely NOT NULL, to query this record you should

select pp_lock from cbr_385120060b39d42d8 where pp_lock IS NOT NULL; ( not the WHERE pp_lock <> NULL ,
not the WHERE pp_lock <>'',
please remember, every comparison with NULL ( the '' is another form to express NULL ) results in NULL. The only proper method to check the value for NULLness is: "IS NULL" or "IS NOT NULL" , both results in TRUE   or FALSE resp.)

Best regards

Maxim Received on Mon Aug 01 2005 - 08:19:44 CDT