Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2] (may be forged)) by hub.org (8.9.3/8.9.3) with ESMTP id QAA11703 for ; Sat, 10 Jul 1999 16:03:54 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id QAA05128 for ; Sat, 10 Jul 1999 16:03:17 -0400 (EDT) To: pgsql-sql@postgreSQL.org Subject: Re: [SQL] Oddities with NULL and GROUP BY In-reply-to: Your message of Wed, 19 May 1999 09:46:52 -0400 <3742C0CC.FA412323@kearneydev.com> Date: Sat, 10 Jul 1999 16:03:17 -0400 Message-ID: <5126.931636997@sss.pgh.pa.us> From: Tom Lane I believe I have finally resolved this old bug from May: secret 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