Обсуждение: Strange query problem...

Поиск
Список
Период
Сортировка

Strange query problem...

От
"Scott Whitney"
Дата:
Um. How is this possible? Am I doing something very, very stupid, here?


mydb=# select * from time_recs where id_time_rec not in (select id_time_rec
from punch_time_recs);
 id_time_rec | id_user | record_date | id_code_task | id_code_pay_type |
id_project | time_amount | comment | commit_state | id_domain |
id_code_bill_type | id_group
-------------+---------+-------------+--------------+------------------+----
--------+-------------+---------+--------------+-----------+----------------
---+----------
(0 rows)

mydb=# select count(*) from time_recs;
 count
-------
 73725
(1 row)

mydb=# select count(*) from punch_time_recs;
 count
-------
  5369
(1 row)

There are many occurences where this is true...Roughly 68,356, if my math is
right. :)


Table definitions:
mydb=# \d time_recs
                          Table "public.time_recs"
      Column       |          Type          |           Modifiers
-------------------+------------------------+-------------------------------
 id_time_rec       | character varying(38)  | not null
 id_user           | character varying(38)  | not null
 record_date       | character varying(38)  | not null
 id_code_task      | character varying(38)  | not null
 id_code_pay_type  | character varying(38)  | not null
 id_project        | character varying(38)  | not null
 time_amount       | double precision       | not null
 comment           | character varying(252) | default ''::character varying
 commit_state      | integer                | not null
 id_domain         | character varying(38)  | not null
 id_code_bill_type | character varying(38)  | not null
 id_group          | character varying(38)  | not null
Indexes:
    "cpk_123" PRIMARY KEY, btree (id_time_rec)
    "ix123_10" btree (id_code_bill_type)
    "ix123_2" btree (record_date)
    "ix123_3" btree (id_code_task)
    "ix123_4" btree (id_code_pay_type)
    "ix123_5" btree (id_project)
    "ixc123_1_2" btree (id_user, record_date)
Foreign-key constraints:
    "f123_10_112" FOREIGN KEY (id_code_bill_type) REFERENCES
codes_bill_types(id_code)
    "f123_1_104" FOREIGN KEY (id_user) REFERENCES users(id_user)
    "f123_3_109" FOREIGN KEY (id_code_task) REFERENCES codes_tasks(id_code)
    "f123_4_111" FOREIGN KEY (id_code_pay_type) REFERENCES
codes_pay_types(id_code)
    "f123_5_108" FOREIGN KEY (id_project) REFERENCES projects(id_project)
    "f123_9_103" FOREIGN KEY (id_domain) REFERENCES domains(id_domain)



mydb=# \d punch_time_recs
                       Table "public.punch_time_recs"
      Column       |          Type          |           Modifiers
-------------------+------------------------+-------------------------------
 id_punch          | character varying(38)  | not null
 id_user           | character varying(38)  | not null
 record_date       | character varying(38)  | not null
 id_code_task      | character varying(38)  | not null
 id_code_pay_type  | character varying(38)  | not null
 id_project        | character varying(38)  | not null
 punch_datetime    | double precision       | not null
 comment           | character varying(252) | default ''::character varying
 id_time_rec       | character varying(38)  |
 when_exported     | double precision       |
 id_code_bill_type | character varying(38)  | not null
 pre_or_post       | double precision       |
 id_group          | character varying(38)  | not null
Indexes:
    "cpk_173" PRIMARY KEY, btree (id_punch)
    "ix173_10" btree (id_code_bill_type)
    "ix173_3" btree (id_code_task)
    "ix173_4" btree (id_code_pay_type)
    "ix173_5" btree (id_project)
    "ix173_6" btree (punch_datetime)
    "ix173_8" btree (id_time_rec)
    "ixc173_1_2" btree (id_user, record_date)
Foreign-key constraints:
    "f173_10_112" FOREIGN KEY (id_code_bill_type) REFERENCES
codes_bill_types(id_code)
    "f173_1_104" FOREIGN KEY (id_user) REFERENCES users(id_user)
    "f173_3_109" FOREIGN KEY (id_code_task) REFERENCES codes_tasks(id_code)
    "f173_4_111" FOREIGN KEY (id_code_pay_type) REFERENCES
codes_pay_types(id_code)
    "f173_5_108" FOREIGN KEY (id_project) REFERENCES projects(id_project)
    "f173_8_123" FOREIGN KEY (id_time_rec) REFERENCES time_recs(id_time_rec)
ON DELETE CASCADE


Re: Strange query problem...

От
"Kevin Grittner"
Дата:
>>> "Scott Whitney" <swhitney@journyx.com> wrote:
> Um. How is this possible?

> mydb=# select * from time_recs where id_time_rec not in (select
> id_time_rec from punch_time_recs);

> (0 rows)

>                        Table "public.punch_time_recs"
>       Column       |          Type          |           Modifiers

>
-------------------+------------------------+-------------------------------

>  id_time_rec       | character varying(38)  |

The column in punch_time_recs is null capable.  Try using NOT EXISTS.

The SQL spec requires the NOT IN to be the equivalent of a "not
equals" test for all entries, and you can't say that any given value
is not equal to NULL, since NULL can mean that there is a value but
you don't know it.  The semantics of NOT EXISTS are subtly different
here -- it means there aren't any rows known to have the value.

-Kevin

Re: Strange query problem...

От
"Scott Whitney"
Дата:
So, you're sayin' I ain't crazy? :)

-----Original Message-----
From: Hoover, Jeffrey [mailto:jhoover@jcvi.org]
Sent: Wednesday, January 28, 2009 12:18 PM
To: Kevin Grittner; Scott Whitney; pgsql-admin@postgresql.org
Subject: RE: [ADMIN] Strange query problem...

Wow! I would never have expected that behavior, but heres the proof:

cameradb_dev=# select * from a1;
   i
-------
 one
 three
 five
 two
 four
(5 rows)

cameradb_dev=# select * from a2;
 j |  i
---+------
 0 |
 2 | two
 4 | four
 (3 rows)

cameradb_dev=# select * from a1 where i not in (select i from a2);
 i
---
(0 rows)

cameradb_dev=# select * from a1 where i not in (select coalesce(i,'')
from a2);
   i
-------
 one
 three
 five
(3 rows)

cameradb_dev=#

-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Kevin Grittner
Sent: Wednesday, January 28, 2009 1:05 PM
To: Scott Whitney; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Strange query problem...

>>> "Scott Whitney" <swhitney@journyx.com> wrote:
> Um. How is this possible?

> mydb=# select * from time_recs where id_time_rec not in (select
> id_time_rec from punch_time_recs);

> (0 rows)

>                        Table "public.punch_time_recs"
>       Column       |          Type          |           Modifiers

>
-------------------+------------------------+---------------------------
----

>  id_time_rec       | character varying(38)  |

The column in punch_time_recs is null capable.  Try using NOT EXISTS.

The SQL spec requires the NOT IN to be the equivalent of a "not
equals" test for all entries, and you can't say that any given value
is not equal to NULL, since NULL can mean that there is a value but
you don't know it.  The semantics of NOT EXISTS are subtly different
here -- it means there aren't any rows known to have the value.

-Kevin

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


Re: Strange query problem...

От
"Scott Whitney"
Дата:
Wow. This just boggles my mind, but there it is.  Here's Oracle:

SQL> select i from a1;

I
--------------------
one
two
three
four
five

SQL> select i from a2;

I
--------------------

two
four

SQL> select i from a1 where i not in (select i from a2);

no rows selected

Or, if you want the exact test:

SQL>  select * from a1 where i not in (select i from a2);

no rows selected

SQL> select * from a1 where i not in (select coalesce(i,'')
  2  from a2);

no rows selected



-----Original Message-----
From: Hoover, Jeffrey [mailto:jhoover@jcvi.org]
Sent: Wednesday, January 28, 2009 12:31 PM
To: Scott Whitney; Kevin Grittner; pgsql-admin@postgresql.org
Subject: RE: [ADMIN] Strange query problem...

How do other databases handle this?  I tried it in SQLite and I get
different behavior (see below).  Can someone try it in Oracle? In MySQL?
In Sybase? If postgres is alone in this interpretation would the
community consider revising the postgres interpretation?

sqlite> select * from a1;
one
three
five
two
four

sqlite> select * from a2;
2|two
4|four
0|

sqlite>  select * from a1 where i not in (select i from a2);
one
three
five
sqlite>

-----Original Message-----
From: Scott Whitney [mailto:swhitney@journyx.com]
Sent: Wednesday, January 28, 2009 1:22 PM
To: Hoover, Jeffrey; 'Kevin Grittner'; pgsql-admin@postgresql.org
Subject: RE: [ADMIN] Strange query problem...

So, you're sayin' I ain't crazy? :)

-----Original Message-----
From: Hoover, Jeffrey [mailto:jhoover@jcvi.org]
Sent: Wednesday, January 28, 2009 12:18 PM
To: Kevin Grittner; Scott Whitney; pgsql-admin@postgresql.org
Subject: RE: [ADMIN] Strange query problem...

Wow! I would never have expected that behavior, but heres the proof:

cameradb_dev=# select * from a1;
   i
-------
 one
 three
 five
 two
 four
(5 rows)

cameradb_dev=# select * from a2;
 j |  i
---+------
 0 |
 2 | two
 4 | four
 (3 rows)

cameradb_dev=# select * from a1 where i not in (select i from a2);
 i
---
(0 rows)

cameradb_dev=# select * from a1 where i not in (select coalesce(i,'')
from a2);
   i
-------
 one
 three
 five
(3 rows)

cameradb_dev=#

-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Kevin Grittner
Sent: Wednesday, January 28, 2009 1:05 PM
To: Scott Whitney; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Strange query problem...

>>> "Scott Whitney" <swhitney@journyx.com> wrote:
> Um. How is this possible?

> mydb=# select * from time_recs where id_time_rec not in (select
> id_time_rec from punch_time_recs);

> (0 rows)

>                        Table "public.punch_time_recs"
>       Column       |          Type          |           Modifiers

>
-------------------+------------------------+---------------------------
----

>  id_time_rec       | character varying(38)  |

The column in punch_time_recs is null capable.  Try using NOT EXISTS.

The SQL spec requires the NOT IN to be the equivalent of a "not
equals" test for all entries, and you can't say that any given value
is not equal to NULL, since NULL can mean that there is a value but
you don't know it.  The semantics of NOT EXISTS are subtly different
here -- it means there aren't any rows known to have the value.

-Kevin

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



Re: Strange query problem...

От
"Joshua D. Drake"
Дата:
On Wed, 2009-01-28 at 12:38 -0600, Scott Whitney wrote:
> Wow. This just boggles my mind, but there it is.  Here's Oracle:
>

This looks like a NULL vs '' issue. Am I wrong?

Joshua D. Drake


> SQL> select i from a1;
>
> I
> --------------------
> one
> two
> three
> four
> five
>
> SQL> select i from a2;
>
> I
> --------------------
>
> two
> four
>
> SQL> select i from a1 where i not in (select i from a2);
>
> no rows selected
>
> Or, if you want the exact test:
>
> SQL>  select * from a1 where i not in (select i from a2);
>
> no rows selected
>
> SQL> select * from a1 where i not in (select coalesce(i,'')
>   2  from a2);
>
> no rows selected
>
>
>
> -----Original Message-----
> From: Hoover, Jeffrey [mailto:jhoover@jcvi.org]
> Sent: Wednesday, January 28, 2009 12:31 PM
> To: Scott Whitney; Kevin Grittner; pgsql-admin@postgresql.org
> Subject: RE: [ADMIN] Strange query problem...
>
> How do other databases handle this?  I tried it in SQLite and I get
> different behavior (see below).  Can someone try it in Oracle? In MySQL?
> In Sybase? If postgres is alone in this interpretation would the
> community consider revising the postgres interpretation?
>
> sqlite> select * from a1;
> one
> three
> five
> two
> four
>
> sqlite> select * from a2;
> 2|two
> 4|four
> 0|
>
> sqlite>  select * from a1 where i not in (select i from a2);
> one
> three
> five
> sqlite>
>
> -----Original Message-----
> From: Scott Whitney [mailto:swhitney@journyx.com]
> Sent: Wednesday, January 28, 2009 1:22 PM
> To: Hoover, Jeffrey; 'Kevin Grittner'; pgsql-admin@postgresql.org
> Subject: RE: [ADMIN] Strange query problem...
>
> So, you're sayin' I ain't crazy? :)
>
> -----Original Message-----
> From: Hoover, Jeffrey [mailto:jhoover@jcvi.org]
> Sent: Wednesday, January 28, 2009 12:18 PM
> To: Kevin Grittner; Scott Whitney; pgsql-admin@postgresql.org
> Subject: RE: [ADMIN] Strange query problem...
>
> Wow! I would never have expected that behavior, but heres the proof:
>
> cameradb_dev=# select * from a1;
>    i
> -------
>  one
>  three
>  five
>  two
>  four
> (5 rows)
>
> cameradb_dev=# select * from a2;
>  j |  i
> ---+------
>  0 |
>  2 | two
>  4 | four
>  (3 rows)
>
> cameradb_dev=# select * from a1 where i not in (select i from a2);
>  i
> ---
> (0 rows)
>
> cameradb_dev=# select * from a1 where i not in (select coalesce(i,'')
> from a2);
>    i
> -------
>  one
>  three
>  five
> (3 rows)
>
> cameradb_dev=#
>
> -----Original Message-----
> From: pgsql-admin-owner@postgresql.org
> [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Kevin Grittner
> Sent: Wednesday, January 28, 2009 1:05 PM
> To: Scott Whitney; pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] Strange query problem...
>
> >>> "Scott Whitney" <swhitney@journyx.com> wrote:
> > Um. How is this possible?
>
> > mydb=# select * from time_recs where id_time_rec not in (select
> > id_time_rec from punch_time_recs);
>
> > (0 rows)
>
> >                        Table "public.punch_time_recs"
> >       Column       |          Type          |           Modifiers
>
> >
> -------------------+------------------------+---------------------------
> ----
>
> >  id_time_rec       | character varying(38)  |
>
> The column in punch_time_recs is null capable.  Try using NOT EXISTS.
>
> The SQL spec requires the NOT IN to be the equivalent of a "not
> equals" test for all entries, and you can't say that any given value
> is not equal to NULL, since NULL can mean that there is a value but
> you don't know it.  The semantics of NOT EXISTS are subtly different
> here -- it means there aren't any rows known to have the value.
>
> -Kevin
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
>
>
--
PostgreSQL - XMPP: jdrake@jabber.postgresql.org
   Consulting, Development, Support, Training
   503-667-4564 - http://www.commandprompt.com/
   The PostgreSQL Company, serving since 1997


Re: Strange query problem...

От
"Scott Whitney"
Дата:
Adding in "where id_time_rec is not null" does solve the problem. SQL server
appears to be the only one that natively says "yeah, he doesn't care about
those."

I'd argue, now that I'm understanding it, that the query is doing what I
asked. Just not what I wanted. :)

-----Original Message-----
From: Joshua D. Drake [mailto:jd@commandprompt.com]
Sent: Wednesday, January 28, 2009 12:47 PM
To: Scott Whitney
Cc: 'Hoover, Jeffrey'; 'Kevin Grittner'; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Strange query problem...

On Wed, 2009-01-28 at 12:38 -0600, Scott Whitney wrote:
> Wow. This just boggles my mind, but there it is.  Here's Oracle:
>

This looks like a NULL vs '' issue. Am I wrong?

Joshua D. Drake


> SQL> select i from a1;
>
> I
> --------------------
> one
> two
> three
> four
> five
>
> SQL> select i from a2;
>
> I
> --------------------
>
> two
> four
>
> SQL> select i from a1 where i not in (select i from a2);
>
> no rows selected
>
> Or, if you want the exact test:
>
> SQL>  select * from a1 where i not in (select i from a2);
>
> no rows selected
>
> SQL> select * from a1 where i not in (select coalesce(i,'')
>   2  from a2);
>
> no rows selected
>
>
>
> -----Original Message-----
> From: Hoover, Jeffrey [mailto:jhoover@jcvi.org]
> Sent: Wednesday, January 28, 2009 12:31 PM
> To: Scott Whitney; Kevin Grittner; pgsql-admin@postgresql.org
> Subject: RE: [ADMIN] Strange query problem...
>
> How do other databases handle this?  I tried it in SQLite and I get
> different behavior (see below).  Can someone try it in Oracle? In MySQL?
> In Sybase? If postgres is alone in this interpretation would the
> community consider revising the postgres interpretation?
>
> sqlite> select * from a1;
> one
> three
> five
> two
> four
>
> sqlite> select * from a2;
> 2|two
> 4|four
> 0|
>
> sqlite>  select * from a1 where i not in (select i from a2);
> one
> three
> five
> sqlite>
>
> -----Original Message-----
> From: Scott Whitney [mailto:swhitney@journyx.com]
> Sent: Wednesday, January 28, 2009 1:22 PM
> To: Hoover, Jeffrey; 'Kevin Grittner'; pgsql-admin@postgresql.org
> Subject: RE: [ADMIN] Strange query problem...
>
> So, you're sayin' I ain't crazy? :)
>
> -----Original Message-----
> From: Hoover, Jeffrey [mailto:jhoover@jcvi.org]
> Sent: Wednesday, January 28, 2009 12:18 PM
> To: Kevin Grittner; Scott Whitney; pgsql-admin@postgresql.org
> Subject: RE: [ADMIN] Strange query problem...
>
> Wow! I would never have expected that behavior, but heres the proof:
>
> cameradb_dev=# select * from a1;
>    i
> -------
>  one
>  three
>  five
>  two
>  four
> (5 rows)
>
> cameradb_dev=# select * from a2;
>  j |  i
> ---+------
>  0 |
>  2 | two
>  4 | four
>  (3 rows)
>
> cameradb_dev=# select * from a1 where i not in (select i from a2);
>  i
> ---
> (0 rows)
>
> cameradb_dev=# select * from a1 where i not in (select coalesce(i,'')
> from a2);
>    i
> -------
>  one
>  three
>  five
> (3 rows)
>
> cameradb_dev=#
>
> -----Original Message-----
> From: pgsql-admin-owner@postgresql.org
> [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Kevin Grittner
> Sent: Wednesday, January 28, 2009 1:05 PM
> To: Scott Whitney; pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] Strange query problem...
>
> >>> "Scott Whitney" <swhitney@journyx.com> wrote:
> > Um. How is this possible?
>
> > mydb=# select * from time_recs where id_time_rec not in (select
> > id_time_rec from punch_time_recs);
>
> > (0 rows)
>
> >                        Table "public.punch_time_recs"
> >       Column       |          Type          |           Modifiers
>
> >
> -------------------+------------------------+---------------------------
> ----
>
> >  id_time_rec       | character varying(38)  |
>
> The column in punch_time_recs is null capable.  Try using NOT EXISTS.
>
> The SQL spec requires the NOT IN to be the equivalent of a "not
> equals" test for all entries, and you can't say that any given value
> is not equal to NULL, since NULL can mean that there is a value but
> you don't know it.  The semantics of NOT EXISTS are subtly different
> here -- it means there aren't any rows known to have the value.
>
> -Kevin
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
>
>
--
PostgreSQL - XMPP: jdrake@jabber.postgresql.org
   Consulting, Development, Support, Training
   503-667-4564 - http://www.commandprompt.com/
   The PostgreSQL Company, serving since 1997



Re: Strange query problem...

От
"Kevin Grittner"
Дата:
>>> "Hoover, Jeffrey" <jhoover@jcvi.org> wrote:
> In Sybase?

Sybase ASE defaults to the same behavior as Microsoft SQL Server, but
they have a configuration option to yield standards compliant behavior
in this regard (SET ANSINULL ON).

-Kevin

Re: Strange query problem...

От
Tom Lane
Дата:
"Joshua D. Drake" <jd@commandprompt.com> writes:
> This looks like a NULL vs '' issue. Am I wrong?

No, it's a NULL vs NOT IN issue.  Specifically, if the subquery yields
any NULLs and the comparison operator is strict (which nearly all are)
then it's impossible to get a TRUE result from the NOT IN --- the only
possibilities are FALSE (if a match is found among the non-nulls)
or NULL (if not).  Standard gotcha for newbie SQL coders.

My recommendation is to use NOT EXISTS instead; it's got less surprising
semantics (and, as of 8.4, it'll get optimized significantly better than
NOT IN).

            regards, tom lane