Thread

RE: Can't use NULL in IN conditional?

Piers Scannell <piers.scannell@globecastne.com> — 2000-12-11T16:18:26Z

Since it's probably a "special case" I'd feel it good software engineering
practice to make that fact explicit anyway.

For example:
  
  SELECT type_id, code FROM product_types WHERE code IN ('0A', 'F3', '99')
OR code IS NULL;

I don't know how that would affect the speed but it might be the clearest
expression of intent. 


Piers Scannell
Systems Engineer, GlobeCast France Telecom
Tel: +44 1707 667 228   Fax: +44 1707 667 206
 


> -----Original Message-----
> From: pgsql-bugs@postgresql.org [mailto:pgsql-bugs@postgresql.org]
> Sent: 11 December 2000 07:49
> To: pgsql-bugs@postgresql.org
> Subject: [BUGS] Can't use NULL in IN conditional?
> 
> 
> Charles Tassell (ctassell@isn.net) reports a bug with a severity of 3
> The lower the number the more severe it is.
> 
> Short Description
> Can't use NULL in IN conditional?
> 
> Long Description
> I don't know if this is a "feature" that comes alogn with 
> NULL values, or somethign I'm doing wrong, but I can't seem 
> to search for the NULL value via a WHERE xx IN clause.  I've 
> re-written the query to use OR and it works fine then, but 
> not with the IN clause.  This is with Postgres 7.02 on a 
> Linux system (PostgreSQL 7.0.2 on i686-pc-linux-gnu, compiled 
> by gcc egcs-2.91.66)
> 
> 
> Sample Code
> CREATE TABLE product_types (
>   type_id serial,
>   code  text
> )
> INSERT INTO product_types (code) VALUES ('0A');
> INSERT INTO product_types (code) VALUES (NULL);
> -- This works
> SELECT type_id, code FROM product_types WHERE code = '0A' OR 
> code = NULL;
> -- This doesn't
> SELECT type_id, code FROM product_types WHERE code IN ('OA', NULL);
> 
> 
> 
> No file was uploaded with this report
>