Thread: wired behaviour

wired behaviour

From
Lutz Steinborn
Date:
Hello,

I have an problem understanding a simple query:

For example I have the following:
select count(*) from foo;
gives me 1000

select count(*) from foo where bar = 'Mr Spock';
gives me 5

select count(*) from foo where NOT bar = 'Mr Spock';
gives me 857

Why I'm not getting 955 for the last query ?


Any idea ?

Kindly regards


Lutz


Re: wired behaviour

From
"Paul Dam"
Date:
NULL values?

Met vriendelijke groet,
Paul Dam
Informatieanalist


Amyyon
Bijsterhuizen 11.58
6546 AS Nijmegen
050 - 311 5686
www.amyyon.nl
-----Oorspronkelijk bericht-----
Van: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org] Namens Lutz Steinborn
Verzonden: vrijdag 28 november 2008 14:52
Aan: pgsql-sql@postgresql.org
Onderwerp: [SQL] wired behaviour

Hello,

I have an problem understanding a simple query:

For example I have the following:
select count(*) from foo;
gives me 1000

select count(*) from foo where bar = 'Mr Spock';
gives me 5

select count(*) from foo where NOT bar = 'Mr Spock';
gives me 857

Why I'm not getting 955 for the last query ?


Any idea ?

Kindly regards


Lutz

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql


Re: wired behaviour

From
ries van Twisk
Date:
On Nov 28, 2008, at 8:51 AM, Lutz Steinborn wrote:

> Hello,
>
> I have an problem understanding a simple query:
>
> For example I have the following:
> select count(*) from foo;
> gives me 1000
>
> select count(*) from foo where bar = 'Mr Spock';
> gives me 5
>
> select count(*) from foo where NOT bar = 'Mr Spock';
> gives me 857
>
> Why I'm not getting 955 for the last query ?
>
>
> Any idea ?



Do you happen to have any NULL values on the field bar??

Ries





Re: wired behaviour

From
Lutz Steinborn
Date:
Hello Paul,

thanks for the quick answer.

> NULL values?
Jepp, thats it.
I've supposed this but can't believe it. So NULL is something out of this
dimension :-)

Kindly regards
Lutz

> 
> Met vriendelijke groet,
>  
> Paul Dam
> Informatieanalist
>  
>  
> 
> 
> Amyyon
> Bijsterhuizen 11.58
> 6546 AS Nijmegen
> 050 - 311 5686
> www.amyyon.nl
> -----Oorspronkelijk bericht-----
> Van: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org] Namens Lutz Steinborn
> Verzonden: vrijdag 28 november 2008 14:52
> Aan: pgsql-sql@postgresql.org
> Onderwerp: [SQL] wired behaviour
> 
> Hello,
> 
> I have an problem understanding a simple query:
> 
> For example I have the following:
> select count(*) from foo;
> gives me 1000
> 
> select count(*) from foo where bar = 'Mr Spock';
> gives me 5
> 
> select count(*) from foo where NOT bar = 'Mr Spock';
> gives me 857
> 
> Why I'm not getting 955 for the last query ?
> 
> 
> Any idea ?
> 
> Kindly regards
> 
> 
> Lutz
> 
> -- 
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
> 
> -- 
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql


-- 
Lutz Steinborn
4c Business Service GmbH
Putzbrunner Str. 71
81739 M_nchen

Fon: +49 89 99341 30, Fax +49 89 99341 399
l.steinborn@4c-ag.de, http://www.4c-shopping.de

---------------------------------------------------------
Sitz der Gesellschaft: Putzbrunner Str. 71, 81739 Muenchen
Vertretungsberechtigter Geschaeftsfuehrer: Frank W. Lutze
Registergericht: Amtsgericht Muenchen
Registernummer: HR 130 207
Ustnr. gemaess _ 27 a Umsatzsteuergesetz: DE 206 864 106


Re: wired behaviour

From
Ragnar Hafstað
Date:
On fös, 2008-11-28 at 15:22 +0100, Lutz Steinborn wrote:
> Hello Paul,
> 
> thanks for the quick answer.
> 
> > NULL values?
> Jepp, thats it.
> I've supposed this but can't believe it. So NULL is something out of this
> dimension :-)

Yes, that is one way of putting it.

A more useful way to look at it is to say that NULL represents an
UNKNOWN value.

rows with NULL bar value will not be counted by: 
> > select count(*) from foo where bar = 'Mr Spock';

because you cannot know whether the UNKNOWN value is 'Mr Spock'.

these rows will neither be counted by:
> > select count(*) from foo where NOT bar = 'Mr Spock';

because you also cannot know that the UNKNOWN value is NOT 'Mr Spock'.

Does that make it any clearer?

gnari




Re: wired behaviour

From
Shane Ambler
Date:
Lutz Steinborn wrote:
> Hello Paul,
> 
> thanks for the quick answer.
> 
>> NULL values?
> Jepp, thats it.
> I've supposed this but can't believe it. So NULL is something out of this
> dimension :-)
> 
> Kindly regards
> Lutz
> 

NULL refers to an unknown value - it cannot be said to equal or not 
equal anything other than NULL

It is a concept that catches those new to databases (and sometimes not 
so new).



-- 

Shane Ambler
pgSQL (at) Sheeky (dot) Biz

Get Sheeky @ http://Sheeky.Biz


Re: wired behaviour

From
Lennin Caro
Date:


--- On Fri, 11/28/08, Lutz Steinborn <l.steinborn@4c-ag.de> wrote:

> From: Lutz Steinborn <l.steinborn@4c-ag.de>
> Subject: [SQL] wired behaviour
> To: pgsql-sql@postgresql.org
> Date: Friday, November 28, 2008, 1:51 PM
> Hello,
> 
> I have an problem understanding a simple query:
> 
> For example I have the following:
> select count(*) from foo;
> gives me 1000
> 
> select count(*) from foo where bar = 'Mr Spock';
> gives me 5
> 
> select count(*) from foo where NOT bar = 'Mr
> Spock';
> gives me 857
> 
> Why I'm not getting 955 for the last query ?
> 
> 
> Any idea ?
> 
> Kindly regards
> 
> 
> Lutz
> 
> -- 

Probably you have null values in bar column 


     



Re: wired behaviour

From
Steve Crawford
Date:
Shane Ambler wrote:
> Lutz Steinborn wrote:
>> Jepp, thats it.
>> I've supposed this but can't believe it. So NULL is something out of 
>> this
>> dimension :-)
>>
>>
>
> NULL refers to an unknown value - it cannot be said to equal or not 
> equal anything other than NULL ...
>
Not exactly. Null does not equal null. The "translation" being does 
some-unknown-value equal some-unknown-value? Answer: unknown.
If you want to determine if something is null you must use "is null".

select null = null;
null

select null is null;
true

If you want to treat nulls as a defined known value, use the coalesce 
function:
select coalesce(my_column, 'a null value')....;
will return the string 'a null value' whenever my_column is null.

Cheers,
Steve