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 1mYsCm-0006Vk-80 for pgsql-bugs@arkaria.postgresql.org; Fri, 08 Oct 2021 15:54:40 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1mYsCk-0005fM-0H for pgsql-bugs@arkaria.postgresql.org; Fri, 08 Oct 2021 15:54:38 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mYrr5-0005l7-8q for pgsql-bugs@lists.postgresql.org; Fri, 08 Oct 2021 15:32:15 +0000 Received: from sender4-op-o11.zoho.com ([136.143.188.11]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mYrr1-00088S-Jy for pgsql-bugs@lists.postgresql.org; Fri, 08 Oct 2021 15:32:14 +0000 ARC-Seal: i=1; a=rsa-sha256; t=1633707121; cv=none; d=zohomail.com; s=zohoarc; b=nSAPt23o81jwcm+0Ze4laPX77y2ZCjcsT+UQT1WO8mFkgJA3TL11FYXEr3QLibYBI60HWzOdBzjcbWzi0GgZ/54l+4avF8IblmZCoq/xVtqW5i1Z9hTCYoN9r28lTVORDXR1x3ViR/CIbm+sKaBNsXG9mzxxUywVDyNEJ08XFck= ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=zohomail.com; s=zohoarc; t=1633707121; h=Content-Type:Cc:Date:From:MIME-Version:Message-ID:Subject:To; bh=fb5X75cye7ytLZIa67hLbSsRU5Z3lQ4MQlRDiIaqs2U=; b=GNO4sP2YHC4KsVjkSmQo6NFMPt3uAI6/du7xqWkIdHVce3nuTd+mRqTGYeWXfLLO53L56pVXPsg5gYsIBr19JV5Bc2/NKkWRkwojscNSG5dSIpVXDUhzwMxJCnYYelCkR/SdET89hCVuJO6jM0Lg5uYNQRvC9gwWqOi2WFfms+s= 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=1633707121; s=bam5eec4; d=yottadb.com; i=nars@yottadb.com; h=From:To:Cc:Subject:Date:Message-ID:MIME-Version:Content-Type; bh=fb5X75cye7ytLZIa67hLbSsRU5Z3lQ4MQlRDiIaqs2U=; b=a9MSV1JI9Ivr2yYCsb1ZY8XKonf6mEyTPyWLSf4XDnJb3otZGM27b3SXaJKDghIo JCT/SyZntgN6xyY8oNaT0AjMrBxq/dtGeFaQIt/tRGHpxi8wlFdve42269LbnoH+QiL acsrXMEPEG/FwSx95EAEdOitsoDfOLtFR2qkdEdo= Received: from NARSWIN10 (static-71-162-243-192.phlapa.fios.verizon.net [71.162.243.192]) by mx.zohomail.com with SMTPS id 1633707120420322.4704840711006; Fri, 8 Oct 2021 08:32:00 -0700 (PDT) From: "Narayanan Iyer" To: Cc: Subject: GROUP BY using tablename.* does not work if tablename has 1 column with NULL values Date: Fri, 8 Oct 2021 11:31:57 -0400 Message-ID: <0e0301d7bc59$a1e416b0$e5ac4410$@yottadb.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0E04_01D7BC38.1AD758B0" X-Mailer: Microsoft Outlook 16.0 Thread-Index: Ade8WZJ6ND2/imN8QsS+Dp8xQBQOCw== 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_0E04_01D7BC38.1AD758B0 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi, =20 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). =20 $ psql --version psql (PostgreSQL) 13.4 (Ubuntu 13.4-0ubuntu0.21.04.1) =20 $ psql db db=3D> DROP TABLE IF EXISTS tmp; DROP TABLE db=3D> CREATE TABLE tmp (id INTEGER PRIMARY KEY, firstName VARCHAR(30), = lastName VARCHAR(30)); CREATE TABLE db=3D> INSERT INTO tmp VALUES (1,'Acid','Burn'); INSERT 0 1 db=3D> INSERT INTO tmp VALUES (2,'Joey',NULL); INSERT 0 1 db=3D> SELECT COUNT(t1.lastName) FROM tmp t2 LEFT JOIN (SELECT lastName = FROM tmp) AS t1 ON (t2.firstName <=3D 'Acid') GROUP BY t1.lastName; count 0 1 (2 rows) db=3D> SELECT COUNT(t1.*) FROM tmp t2 LEFT JOIN (SELECT lastName FROM = tmp) AS t1 ON (t2.firstName <=3D 'Acid') GROUP BY t1.*; count 1 1 0 (3 rows) =20 It seems like a Postgres bug to me. Not sure if there is a Postgres = setting that I need to enable in order to avoid this discrepancy. =20 Let me know if you need any more information. =20 Thanks, Narayanan. ------=_NextPart_000_0E04_01D7BC38.1AD758B0 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

Hi,

 

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).

 

$ psql --version

psql (PostgreSQL) 13.4 (Ubuntu = 13.4-0ubuntu0.21.04.1)

 

$ psql db

db=3D> DROP TABLE = IF EXISTS tmp;

DROP TABLE

db=3D> CREATE TABLE = tmp (id INTEGER PRIMARY KEY, firstName VARCHAR(30), lastName = VARCHAR(30));

CREATE TABLE

db=3D> INSERT INTO = tmp VALUES = (1,'Acid','Burn');

INSERT 0 1

db=3D> INSERT INTO = tmp VALUES (2,'Joey',NULL);

INSERT 0 1

db=3D> SELECT = COUNT(t1.lastName) FROM tmp t2 LEFT JOIN (SELECT lastName FROM tmp) AS = t1 ON (t2.firstName <=3D 'Acid') GROUP BY = t1.lastName;

count

0

1

(2 rows)

db=3D> SELECT = COUNT(t1.*) FROM tmp t2 LEFT JOIN (SELECT lastName FROM tmp) AS t1 ON = (t2.firstName <=3D 'Acid') GROUP BY = t1.*;

count

1

1

0

(3 rows)

 

It seems like a Postgres bug to me. Not sure if = there is a Postgres setting that I need to enable in order to avoid this = discrepancy.

 

Let me know if you need any more = information.

 

Thanks,

Narayanan.

------=_NextPart_000_0E04_01D7BC38.1AD758B0--