From: Tom Lane <tgl@sss.pgh.pa.us>
To: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] Oddities with NULL and GROUP BY
Date: Sat, 10 Jul 1999 16:03:17 -0400
Message-ID: <5126.931636997@sss.pgh.pa.us> (raw)
In-Reply-To: <3742C0CC.FA412323@kearneydev.com>
I believe I have finally resolved this old bug from May:
secret <secret@kearneydev.com> writes:
> GROUPing in PostgreSQL w/NULLs works just fine when there is only 1
> column, however when one throws 2 in, the 2nd one having NULLs it starts
> failing. Your example demonstrates the right answer for 1 group by
> column, try it with 2 and I expect 6.5beta1 will fail as 6.4.2 does.
Actually, I believe that the problem was seen when you sort/group by
multiple columns and there are nulls in the *earlier* columns. The bug
I just fixed in the sort logic was that it would stop comparing as soon
as it hit a null column. Thus (NULL,1) would sort as equal to (NULL,2)
whereas you'd obviously like it to sort as smaller.
The reason it affected GROUP BY is that the sort could produce results
like
(NULL,1)
(NULL,1)
(NULL,2)
(NULL,1)
Because of the comparison bug, the sorter thought these tuples were
all equal-keyed and so it didn't worry about what order they'd come
out in. But then the adjacent-duplicate-merging step would produce
(NULL,1) --- 2 tuples represented by this group
(NULL,2)
(NULL,1)
which is the wrong answer.
The fix is to continue comparing columns when both tuples have a null
in one column, rather than stopping and declaring them equal. This
is in current CVS sources and will be in 6.5.1.
The bug cannot be observed if you use test cases that only sort/group
on one column...
regards, tom lane
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us
Subject: Re: [SQL] Oddities with NULL and GROUP BY
In-Reply-To: <5126.931636997@sss.pgh.pa.us>
References: <3742C0CC.FA412323@kearneydev.com> <5126.931636997@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox