Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1shqHq-009vDW-6s for pgsql-bugs@arkaria.postgresql.org; Sat, 24 Aug 2024 12:54:35 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1shqHo-005ImK-1F for pgsql-bugs@arkaria.postgresql.org; Sat, 24 Aug 2024 12:54:32 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1shqHn-005ImC-JS for pgsql-bugs@lists.postgresql.org; Sat, 24 Aug 2024 12:54:32 +0000 Received: from rex-mailout.bayern.de ([193.34.207.154]) by magus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1shqHj-001Gtw-MJ for pgsql-bugs@lists.postgresql.org; Sat, 24 Aug 2024 12:54:31 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=bayern.de; i=@bayern.de; s=cc-yang; t=1724504068; x=1740056068; h=from:to:subject:date:message-id:content-type: mime-version; bh=UvB1r1LtFhCjun2zgQdOulCd2Nk14yhgMSrfzT+oD6o=; b=KDI+n7OQBdsofucmDn59gr0P2mUXFSgKeTKGl2H6/4SfkpjX/0bAl6wx MTzmWZ8HqxPxmxGaS0rFcDKj/irIcoM7lClHUaurEsXeVPpZprj9QrW+I OImRcp4m3wsgesAdlzUg+V59FzOj4XggEDjsmWOhKUoZc2Clo5yUHaep9 zjCufj0MAu2k+01p0mrVXwQlA5HeLVMt+S2l9V3CTopTEMlfBGUZMMMSH gpBPAgT0h3fF+ht/laVMk2nobW20uuVmMKw0QOXDnamWkPtZLmeO10nfl xuBWHKXbdyi/iFMsLnXv/wOd2KyJUVbHPDMkTb84ddG5YOuRyifGvWrNx Q==; X-CSE-ConnectionGUID: RdMh0KHWSeGaEedtWYv+bw== X-CSE-MsgGUID: 6sPItWiOSR6xx1wPgr0otA== Received: from unknown (HELO rin132.bayern.de) ([10.197.20.132]) by rex145.bayern.de with ESMTP; 24 Aug 2024 14:54:26 +0200 X-CSE-ConnectionGUID: gWTP+HuPQcqhzbe8qP8gYg== X-CSE-MsgGUID: oU9+DsxvS1qS216cbqMw0Q== X-Talos-CUID: 9a23:vMhXFGwC7YFQDEe2ZPtcBgUbJfwdMV3A9E7bOk+qA2s5UYXFRm6frfY= X-Talos-MUID: 9a23:Qv1VRQbLSFU6buBTsAK9xx15JNlTur2VUhlTmp8fntbcDHkl X-BYBN-routing: post Received: from zmx-eh1-p24388.max.bayern.de ([10.173.241.114]) by rin132.bayern.de with ESMTP/TLS/ECDHE-RSA-AES256-GCM-SHA384; 24 Aug 2024 14:54:26 +0200 Received: from ZMX-EH1-P24388.max.bayern.de (10.173.241.114) by ZMX-EH1-P24388.max.bayern.de (10.173.241.114) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.2.1544.11; Sat, 24 Aug 2024 14:54:26 +0200 Received: from ZMX-EH1-P24388.max.bayern.de ([10.171.65.32]) by ZMX-EH1-P24388.max.bayern.de ([10.171.65.32]) with mapi id 15.02.1544.011; Sat, 24 Aug 2024 14:54:26 +0200 From: "Plettenbacher, Tobias (LWF)" To: "pgsql-bugs@lists.postgresql.org" Subject: Bug or strange result of Max() on arrays containing NULL values Thread-Topic: Bug or strange result of Max() on arrays containing NULL values Thread-Index: Adr14AZ6Vp3s5879RNKruhNQvuaPNw== Date: Sat, 24 Aug 2024 12:54:26 +0000 Message-ID: <325d78c062e945ebb286091f0eaa8b72@lwf.bayern.de> Accept-Language: de-DE, en-US Content-Language: de-DE X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [10.171.65.1] Content-Type: multipart/alternative; boundary="_000_325d78c062e945ebb286091f0eaa8b72lwfbayernde_" MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_325d78c062e945ebb286091f0eaa8b72lwfbayernde_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Hi, I'm getting strange results when using the Max() aggregate function on arra= ys containing NULL values. With Max(Value) I get the expected result (in this case 2): SELECT Max(Val) FROM (VALUES (1, 5), (2, 6), (NULL, 7)) AS T(Val, ID); With Max(ARRAY[]) I get a strange result (in this case {NULL,7}): SELECT Max(ARRAY[Val, ID]) FROM (VALUES (1, 5), (2, 6), (NULL, 7)) AS T(Val= , ID); But with Min(ARRAY[]) I get the expected result (in this case {-2,6}): SELECT Min(ARRAY[-Val, ID]) FROM (VALUES (1, 5), (2, 6), (NULL, 7)) AS T(Va= l, ID); Is this a bug or the correct result of Max(ARRAY[]), i.e. should Max() retu= rn NULL as the maximum value? I often use Max(ARRAY[]) to get the ID of the maximum value. As a workaroun= d I must use (Min(ARRAY[-Val, ID]))[2] or (Max(ARRAY[Coalesce(Val, 0), ID]))[= 2]. I'm using PostgreSQL 13.16 on x86_64-pc-linux-gnu, compiled by gcc (SUSE Li= nux) 7.5.0, 64-bit With kind regards Tobias Plettenbacher SB Abt.3 LWF Hans-Carl-von-Carlowitz-Platz 1 85354 Freising Telefon +49 8161 4591-317 Tobias.Plettenbacher@lwf.bayern.de --_000_325d78c062e945ebb286091f0eaa8b72lwfbayernde_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hi,

 

I’m getting strange results when using the Max= () aggregate function on arrays containing NULL values.

 

With Max(Value) I get the expected result (in this c= ase 2):

SELECT Max(V= al) FROM (VALUES (1, 5), (= 2, 6), (= NULL, 7)) AS = T(Val, ID);

 

With Max(ARRAY[]) I get a strange result (in this ca= se {NULL,7}):

SELECT Max(<= /span>ARRAY[Val, ID]) FROM (VALUES (1, 5), (= 2, 6), (= NULL, 7)) AS = T(Val, ID);

 

But with Min(ARRAY[]) I get the expected result (in = this case {-2,6}):

SELECT Min(<= /span>ARRAY[-Val, ID]) FROM (VALUES (1, 5), (= 2, 6), (NULL, 7)) AS = T(Val, ID);

 

Is this a bug or the correct result of Max(ARRAY[]),= i.e. should Max() return NULL as the maximum value?

I often use Max(ARRAY[]) to get the ID of the maximu= m value. As a workaround

I must use (Min(ARRAY[-Val, ID]))[2] or (Max(ARRAY[C= oalesce(Val, 0), ID]))[2].

 

I’m using PostgreSQL 13.16 on x86_64-pc-linux-= gnu, compiled by gcc (SUSE Linux) 7.5.0, 64-bit

 

With kind regards

<= span style=3D"font-family:"Arial",sans-serif;color:dimgray;mso-fa= reast-language:DE">Tobias Plettenbacher=

SB
Abt.3
LWF
Hans-Carl-von-Carlowitz-Platz 1
85354 Freising
Telefon +49 8161 4591-317

Tobias.Plettenbacher@lwf.b= ayern.de


 

--_000_325d78c062e945ebb286091f0eaa8b72lwfbayernde_--