Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mYuus-0004kI-8K for pgsql-bugs@arkaria.postgresql.org; Fri, 08 Oct 2021 18:48:22 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1mYuur-0002ea-8N for pgsql-bugs@arkaria.postgresql.org; Fri, 08 Oct 2021 18:48:21 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mYtoK-0007FB-VR for pgsql-bugs@lists.postgresql.org; Fri, 08 Oct 2021 17:37:33 +0000 Received: from sender4-op-o11.zoho.com ([136.143.188.11]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mYtoI-0005XE-H3 for pgsql-bugs@lists.postgresql.org; Fri, 08 Oct 2021 17:37:31 +0000 ARC-Seal: i=1; a=rsa-sha256; t=1633714643; cv=none; d=zohomail.com; s=zohoarc; b=DMcuNqQt5Wixsf4vRV9lrnNROd6exAs4va+rlv9w4YhcRnrYbyD0MHZutztw3IBSUobeFaE2gK3ubSRWrfQMqyJF7O0yznPJps86izDSy+6lD6cyISKUrvBEKG3GDcNg8nrsceRgTFEQj+/JfTQrEh1m6RcPJV3iAqPxnnJ5dRo= ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=zohomail.com; s=zohoarc; t=1633714643; h=Content-Type:Cc:Date:From:In-Reply-To:MIME-Version:Message-ID:References:Subject:To; bh=M45xhII1UdvefmUqi8ILsvPuctXXus8we5CLpdADs1U=; b=NfjWS6esq8QjBZZJjr8iT3aY6OmevrZmqwdKVDaqZkJ8d8CuvmrFjSDw9kW5Nxrs5rI7rSid7Se3dBDJIhRiguIRsH7CTVoz4gYIaQ5JBj+YCkTfJeG5npWw4AdBPXOIUGlM3t87NNVOQOwlPJU6Z3A3O5Q4D2Z5esE8Ilht6rU= ARC-Authentication-Results: i=1; mx.zohomail.com; dkim=pass header.i=yottadb.com; spf=pass smtp.mailfrom=nars@yottadb.com; dmarc=pass header.from= DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; t=1633714643; s=bam5eec4; d=yottadb.com; i=nars@yottadb.com; h=From:To:Cc:References:In-Reply-To:Subject:Date:Message-ID:MIME-Version:Content-Type; bh=M45xhII1UdvefmUqi8ILsvPuctXXus8we5CLpdADs1U=; b=VSYSGbuLY1rVkuRGOPPI25yIJt0AxAQRz2JcwTMsFCnc1MfHOw3freUYR4mrab3O jqRuw0T8oZ4o7HW6LdM5F6PP45IWNmb83gJboZJsTQjLtPsXaIgZ8pCAs7n6lnSPDPL BFYweobZu3hIPbx4q3v03N/quZph4CSWv0b4kS28= Received: from NARSWIN10 (static-71-162-243-192.phlapa.fios.verizon.net [71.162.243.192]) by mx.zohomail.com with SMTPS id 163371464249774.83773213831614; Fri, 8 Oct 2021 10:37:22 -0700 (PDT) From: "Narayanan Iyer" To: "'David G. Johnston'" , "'Tom Lane'" Cc: , References: <0e0301d7bc59$a1e416b0$e5ac4410$@yottadb.com> <236280.1633710901@sss.pgh.pa.us> In-Reply-To: Subject: RE: GROUP BY using tablename.* does not work if tablename has 1 column with NULL values Date: Fri, 8 Oct 2021 13:37:19 -0400 Message-ID: <0f6b01d7bc6b$25561500$70023f00$@yottadb.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0F6C_01D7BC49.9E4908E0" X-Mailer: Microsoft Outlook 16.0 Thread-Index: AQFDuJQIdzhfXF3+VBqkdhB2CmBVAgGZWxM2ATO13XCs2vvzYA== Content-Language: en-us X-Antivirus: Avast (VPS 211008-0, 10/7/2021), Outbound message X-Antivirus-Status: Clean X-ZohoMailClient: External List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multipart message in MIME format. ------=_NextPart_000_0F6C_01D7BC49.9E4908E0 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable David, =20 This query was the simplest I could come up with to illustrate what I = thought was the issue. It is not a real world query. =20 Narayanan. =20 From: David G. Johnston [mailto:david.g.johnston@gmail.com]=20 Sent: Friday, October 8, 2021 12:51 PM To: Tom Lane Cc: Narayanan Iyer ; pgsql-bugs@lists.postgresql.org; = support@yottadb.com Subject: Re: GROUP BY using tablename.* does not work if tablename has 1 = column with NULL values =20 On Friday, October 8, 2021, Tom Lane > wrote: "Narayanan Iyer" > writes: > In the below example, t1 points to a table with just 1 column = (lastName) and so I expect the 2 SELECT queries (pasted below) using = t1.lastName or t1.* syntax to produce the exact same results. But the = latter produces one extra row of output (3 rows vs 2 rows). SELECT *, t1.*::record FROM tmp t2 LEFT JOIN (SELECT lastName FROM tmp) = AS t1 ON (t2.firstName <=3D 'Acid'); The third row comes from failing to join t2 Joey,NULL to anything. =20 This is the part that seems the most unusual (from a =E2=80=9Cwhy did = you write the query that way=E2=80=9D perspective, not the result). A = left join=E2=80=99s on clause does not act as a filter for the left side = table, so a record with t1.firstName=3DJoey can still be output. So, on = the whole, this is just a poorly written query that takes too much = effort for someone to understand due to the non-traditional use of left = join and an on clause that doesn=E2=80=99t actually join and oddly = decides to restrict the left side. =20 The fact that nulls are not counted and are also not equal to each other = in group by does indeed explain the rest. =20 David J. ------=_NextPart_000_0F6C_01D7BC49.9E4908E0 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

David,

 

This = query was the simplest I could come up with to illustrate what I thought = was the issue. It is not a real world = query.

 

Narayanan.

 

From: David G. Johnston = [mailto:david.g.johnston@gmail.com]
Sent: Friday, October 8, 2021 = 12:51 PM
To: Tom Lane = <tgl@sss.pgh.pa.us>
Cc: Narayanan Iyer = <nars@yottadb.com>; pgsql-bugs@lists.postgresql.org; = support@yottadb.com
Subject: Re: GROUP BY using = tablename.* does not work if tablename has 1 column with NULL = values

 

On Friday, October 8, 2021, Tom Lane <tgl@sss.pgh.pa.us> = wrote:

"Narayanan Iyer" <nars@yottadb.com> = writes:
> In the below example, t1 points to a table with just 1 = column (lastName) and so I expect the 2 SELECT queries (pasted below) = using t1.lastName or t1.* syntax to produce the exact same results. But = the latter produces one extra row of output (3 rows vs 2 = rows).

SELECT *, t1.*::record FROM tmp t2 LEFT JOIN (SELECT = lastName FROM tmp) AS t1 ON (t2.firstName <=3D 'Acid');

The = third row comes from failing to join t2 Joey,NULL to = anything.

 

=

This is the part that seems the most unusual = (from a =E2=80=9Cwhy did you write the query that way=E2=80=9D = perspective, not the result).  A left join=E2=80=99s on clause does = not act as a filter for the left side table, so a record with = t1.firstName=3DJoey can still be output.  So, on the whole, this is = just a poorly written query that takes too much effort for someone to = understand due to the non-traditional use of left join and an on clause = that doesn=E2=80=99t actually join and oddly decides to restrict the = left side.

 

=

The fact that nulls are not counted and are = also not equal to each other in group by does indeed explain the = rest.

 

=

David = J.

------=_NextPart_000_0F6C_01D7BC49.9E4908E0--