agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: Adrian Klaver <adrian.klaver@aklaver.com>
To: Jonathan Moules <jonathan-lists@lightpear.com>
To: pgsql-sql@postgresql.org
Subject: Re: Not getting the expected results for a simple where not in
Date: Wed, 7 Jun 2017 06:28:43 -0700
Message-ID: <d2a16055-efdd-d502-d1df-1fd36804e066@aklaver.com> (raw)
In-Reply-To: <15c827e9305.ae02346553193.1322341002046903374@lightpear.com>
References: <15c827e9305.ae02346553193.1322341002046903374@lightpear.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>

On 06/07/2017 05:20 AM, Jonathan Moules wrote:
> Hi List,
> I'm a little confused by what seems like it should be a simple query and 
> was hoping someone could explain what's going on.
> Using PG 9.4.x

> 
> It seems to relate to the nulls. If I change the above and add "and str 
> is not null" into the subquery:
> 
> select
>      *
> from
>      aaa.testing_nulls
> where
>      str not in
>      (
>          select
>              str
>          from
>              aaa.testing_nulls
>              where
>                  status in ('aa')
>                  and str is not null
>      )
> 
> It now gives the expected results.

Or you could do:

select
     *
from
     testing_nulls
where
     str not in
     (
         select
             coalesce(str, '')
         from
             testing_nulls
             where
                 status in ('aa')
     )
;

   str   | status
--------+--------
  third  | NULL
  fourth | bb
(2 rows)

> Why is this?
> (I tested this in SQLite too, and get the same behaviour, so I guess 
> it's a generic SQL thing I've never encountered before.)
> Thanks,
> Jonathan


-- 
Adrian Klaver
adrian.klaver@aklaver.com


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

view thread (3+ messages)

Message-ID: <d2a16055-efdd-d502-d1df-1fd36804e066@aklaver.com>
Permalink:  ../d2a16055-efdd-d502-d1df-1fd36804e066@aklaver.com/
Also on:    postgresql.org/message-id/d2a16055-efdd-d502-d1df-1fd36804e066@aklaver.com

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: adrian.klaver@aklaver.com, jonathan-lists@lightpear.com
  Subject: Re: Not getting the expected results for a simple where not in
  In-Reply-To: <d2a16055-efdd-d502-d1df-1fd36804e066@aklaver.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox