Postgres Pro
Downloads
Home   >   mailing lists

Thread: Wrong query execution.

Wrong query execution.

From
pginfo
Date:
Hi,

It is possible that I am wrong, but I can nof find my mistake.

I have this 2 querie:

 delete from a_grad  where ids NOT in ( select KL.IDS_GRAD from
a_klienti kl  ) ;

 It returns 0 rows are deleted !

And the second one:

 delete from a_grad  where ids IN (select G.IDS FROM A_GRAD G WHERE NOT
EXISTS ( select * from a_klienti kl where KL.IDS_GRAD = G.IDS) ) ;

 It returns 356 rows are deleted !

I expected that the first will delete also 356 rows.

I am using pg 7.3.1 ( I got the same result on pg 7.3) running linux rh
7.3.

All IDS and IDS_GRAD are declared as name and do not exists any null's.
The encoding is unicode.

Pls. help me to resolve the situation.

Regards,
ivan.

Re: Wrong query execution.

From
dev@archonet.com
Date:
> Hi,
>
> It is possible that I am wrong, but I can nof find my mistake.
>
> I have this 2 querie:
>
>  delete from a_grad  where ids NOT in ( select KL.IDS_GRAD from
> a_klienti kl  ) ;
>
>  It returns 0 rows are deleted !
>
> And the second one:
>
>  delete from a_grad  where ids IN (select G.IDS FROM A_GRAD G WHERE NOT
> EXISTS ( select * from a_klienti kl where KL.IDS_GRAD = G.IDS) ) ;
>
>  It returns 356 rows are deleted !
>
> I expected that the first will delete also 356 rows.

I'm guessing this is the NULL issue hitting you. What does

SELECT ids_grad FROM a_klienti WHERE ids_grad IS NULL

show you?

For those interested, an example of the NULL vs IN issue can be seen in
the sample SQL below - just uncomment the 4th insert to tb.

DROP TABLE ta;
CREATE TABLE ta (id_a int4, a text);
DROP TABLE tb;
CREATE TABLE tb (id_b int4, id_a_ref int4);

INSERT INTO ta VALUES (1,'aaa');
INSERT INTO ta VALUES (2,'bbb');
INSERT INTO ta VALUES (3,'ccc');

INSERT INTO tb VALUES (1,1);
INSERT INTO tb VALUES (2,3);
INSERT INTO tb VALUES (3,1);
-- INSERT INTO tb VALUES (4,Null);

SELECT count(id_a) AS num_to_delete FROM ta WHERE id_a NOT IN (SELECT
id_a_ref FROM tb);
DELETE FROM ta WHERE id_a NOT IN (SELECT id_a_ref FROM tb);

HTH

- Richard Huxton

Re: Wrong query execution.

From
pginfo
Date:
Hi Richard,
It is the case.
I have found records with NULL's !
Exist any docs where I can read more about interpreting of NOT IN ?

regards,
ivan.

dev@archonet.com wrote:

> > Hi,
> >
> > It is possible that I am wrong, but I can nof find my mistake.
> >
> > I have this 2 querie:
> >
> >  delete from a_grad  where ids NOT in ( select KL.IDS_GRAD from
> > a_klienti kl  ) ;
> >
> >  It returns 0 rows are deleted !
> >
> > And the second one:
> >
> >  delete from a_grad  where ids IN (select G.IDS FROM A_GRAD G WHERE NOT
> > EXISTS ( select * from a_klienti kl where KL.IDS_GRAD = G.IDS) ) ;
> >
> >  It returns 356 rows are deleted !
> >
> > I expected that the first will delete also 356 rows.
>
> I'm guessing this is the NULL issue hitting you. What does
>
> SELECT ids_grad FROM a_klienti WHERE ids_grad IS NULL
>
> show you?
>
> For those interested, an example of the NULL vs IN issue can be seen in
> the sample SQL below - just uncomment the 4th insert to tb.
>
> DROP TABLE ta;
> CREATE TABLE ta (id_a int4, a text);
> DROP TABLE tb;
> CREATE TABLE tb (id_b int4, id_a_ref int4);
>
> INSERT INTO ta VALUES (1,'aaa');
> INSERT INTO ta VALUES (2,'bbb');
> INSERT INTO ta VALUES (3,'ccc');
>
> INSERT INTO tb VALUES (1,1);
> INSERT INTO tb VALUES (2,3);
> INSERT INTO tb VALUES (3,1);
> -- INSERT INTO tb VALUES (4,Null);
>
> SELECT count(id_a) AS num_to_delete FROM ta WHERE id_a NOT IN (SELECT
> id_a_ref FROM tb);
> DELETE FROM ta WHERE id_a NOT IN (SELECT id_a_ref FROM tb);
>
> HTH
>
> - Richard Huxton

Re: Wrong query execution.

From
dev@archonet.com
Date:
> Hi Richard,
> It is the case.
> I have found records with NULL's !
> Exist any docs where I can read more about interpreting of NOT IN ?

There's an item on it in my Postgresql Notes (subqueries and NULLs),
linked to from

http://techdocs.postgresql.org

You can also read more about it in Bruce's book (also available online
from the above URL).

Basically, any good relational database book should have a section of the
meaning of nulls.

- Richard Huxton

Re: Wrong query execution.

From
"Mike Mascari"
Date:
----- Original Message -----
> > Hi Richard,
> > It is the case.
> > I have found records with NULL's !
> > Exist any docs where I can read more about interpreting of NOT IN ?
>
> There's an item on it in my Postgresql Notes (subqueries and NULLs),
> linked to from
>
> http://techdocs.postgresql.org
>
> You can also read more about it in Bruce's book (also available online
> from the above URL).
>
> Basically, any good relational database book should have a section of the
> meaning of nulls.

And why they shouldn't be allowed in a database. ;-)

Mike Mascari
mascarm@mascari.com

>
> - Richard Huxton
>