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 1qLhfV-000T7b-Ih for pgsql-bugs@arkaria.postgresql.org; Tue, 18 Jul 2023 10:10:57 +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 1qLhfT-0079NL-Eg for pgsql-bugs@arkaria.postgresql.org; Tue, 18 Jul 2023 10:10:55 +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 1qLhfT-0079ND-3X for pgsql-bugs@lists.postgresql.org; Tue, 18 Jul 2023 10:10:55 +0000 Received: from mail-ej1-x632.google.com ([2a00:1450:4864:20::632]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qLhfP-0007Ot-Cy for pgsql-bugs@lists.postgresql.org; Tue, 18 Jul 2023 10:10:54 +0000 Received: by mail-ej1-x632.google.com with SMTP id a640c23a62f3a-991fe70f21bso743015266b.3 for ; Tue, 18 Jul 2023 03:10:52 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=cybertec-at.20221208.gappssmtp.com; s=20221208; t=1689675051; x=1692267051; h=mime-version:user-agent:content-transfer-encoding:references :in-reply-to:date:to:from:subject:message-id:from:to:cc:subject:date :message-id:reply-to; bh=6PavPCCuxrAXXA0i5bDpJDd2szptadbiL9EasvfUEtk=; b=CcLsdYGUfLG55FNFy6D5VsCxJS9JF+85+pxlUGQ7QACBcuX+cHGnEgAK6XACX2xl1/ SABFeaVrtxJOMJzws0qsrDCSTLxjT4L2U4Xb3n8ZrYHj10YJBJ98zXoSuJmxrTjeDVpO V4nqN23RiiCGpzQmMy5u6Qe0ZUd27KEtEE31YMgGdt2KiblDI/DJRycR7eHsz1PXk5T5 cqS0SJG/eMOYNvLGFXVL8oil8+SiLoVU/xGQ8NZhCNjxLPQa28zh90DwtD0PSyBuBqzG lW6ASQzh0kqi4D+l/vBMrJ9DHI53Oy4Ua60rOICTC7/2rvlxhZluxPX65uQdP1CBgaPF urLQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1689675051; x=1692267051; h=mime-version:user-agent:content-transfer-encoding:references :in-reply-to:date:to:from:subject:message-id:x-gm-message-state:from :to:cc:subject:date:message-id:reply-to; bh=6PavPCCuxrAXXA0i5bDpJDd2szptadbiL9EasvfUEtk=; b=UjXYS3kP9fK1PGKqeaP9/oi4O7i1u2mQqQhw0qt94Aw6ZzhplyCbpTt4hCJZKcNMEt XiWgPiYoR2V1cvas1GIOt1FUdKqJqvIUqp8PvqnB/oRyiPm4kqOei2UkrFabv/Say/zR xyX0oYdpKAwd8s/tnZkzFzsSOWYrGCBS0CN3coeycyL/oWov87XEl6MTsZdiQmAvkgm2 eIyxw2kuLeZSgAmiZB3RS+h1Nqn/VNk57w4lzc0mwfevfpQfvVCgLd1WMNuWd247fD03 jZMYI39KY4RzsR7XR5N68sh/7EJPe01NLBXBDnkHofN6UpILo7LC6wyGrE033adDEISL cgQg== X-Gm-Message-State: ABy/qLYmVXT62BZ+1U+eX0NtLTxzDkQ43BMWbewdB3rqtibRzox7Z/Gz ktPlH8sOtJZm4/9eHDUdXSX3ckES+ccim/lko7M= X-Google-Smtp-Source: APBJJlGfHN6w9jdQxfpIhwnKF1QPo2VgSh7xoQkJh7gON9qHmA3IzMyR/cfXchMCSnnJXjX3T6Z2bA== X-Received: by 2002:a17:906:51da:b0:994:4f08:2bad with SMTP id v26-20020a17090651da00b009944f082badmr9356591ejk.69.1689675051199; Tue, 18 Jul 2023 03:10:51 -0700 (PDT) Received: from localhost.localdomain ([2001:871:5e:b5fa:a3b0:803a:e1a1:bf99]) by smtp.gmail.com with ESMTPSA id y14-20020a1709063a8e00b00984822540c9sm829093ejd.96.2023.07.18.03.10.50 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Tue, 18 Jul 2023 03:10:50 -0700 (PDT) Message-ID: <870b19cb98c433e7052fb7b2d2baf83fdd285c0c.camel@cybertec.at> Subject: Re: Bug with aggregate Window Functions when using Order By. Elements dropped if order by provided From: Laurenz Albe To: Walker Philips , "pgsql-bugs@lists.postgresql.org" Date: Tue, 18 Jul 2023 12:10:49 +0200 In-Reply-To: References: <7eb5a6a9-fd0f-463e-acca-b7622b6820b0.3d353b87-a551-460b-bc24-1d08800ce948.daff3c6d-a26a-4345-b8c7-6e15d34ecd9c@emailsignatures365.codetwo.com> <7eb5a6a9-fd0f-463e-acca-b7622b6820b0.3bd2cb9b-57e9-4cfe-882f-740b5c103819.14d1b346-d657-42e6-b43b-1b0454fa0c4a@emailsignatures365.codetwo.com> Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable User-Agent: Evolution 3.46.4 (3.46.4-1.fc37) MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Mon, 2023-07-17 at 21:41 +0000, Walker Philips wrote: > Applying an order by clause changes the contents of an array_agg (and str= ing_agg) when performing a window function. >=20 > select=C2=A0right(a.telephone1,3),a.accountid,array_agg(accountid)over=C2= =A0(partition=C2=A0by=C2=A0lower(a.telephone1)order=C2=A0by=C2=A0a.accounti= d desc)as=C2=A0hashed_household > from=C2=A0air_spotter.account=C2=A0a; >=20 > generates the results: > 889=E2=80=82=E2=80=82=E2=80=82=C2=A0 =C2=A01C14927F-C28A-E311-A35E-6C3BE5= A84FE4=E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82{1C14927F-C28A-= E311-A35E-6C3BE5A84FE4} > 889=E2=80=82=E2=80=82=E2=80=82=C2=A0 =C2=A08552B5D9-95ED-E311-96E3-6C3BE5= A86DF8=E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82{1C14927F-C28A-= E311-A35E-6C3BE5A84FE4,8552B5D9-95ED-E311-96E3-6C3BE5A86DF8} > [NULL]=E2=80=82 B25520B1-A08B-E011-8DC7-1CC1DEE8AA5F=E2=80=82=E2=80=82=E2= =80=82=E2=80=82 =E2=80=82{B25520B1-A08B-E011-8DC7-1CC1DEE8AA5F} >=20 > Expected results would be the same total content, but the content being s= orted as specified by the order by. Specifically > =E2=80=82{8552B5D9-95ED-E311-96E3-6C3BE5A86DF8,1C14927F-C28A-E311-A35E-6C= 3BE5A84FE4} for both telephones ending in 889. I don't get it: '8552B5D9-95ED-E311-96E3-6C3BE5A86DF8' is greater than '1C1= 4927F-C28A-E311-A35E-6C3BE5A84FE4'. Yours, Laurenz Albe