From: Tom Lane <tgl@sss.pgh.pa.us>
To: Hans Buschmann <buschmann@nidsa.net>
cc: "pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>
Subject: Re: AW: BUG #18189: Value partitioned tables: Upd ERROR: more than
 one row returned by a subquery used as an expression
In-reply-to: <8c6d3b59e47943efbba984c532424163@nidsa.net>
References: 
 <18189-816907aeaa0114e4@postgresql.org>,<1377901.1699629535@sss.pgh.pa.us>
 <8c6d3b59e47943efbba984c532424163@nidsa.net>
Comments: In-reply-to Hans Buschmann <buschmann@nidsa.net>
	message dated "Sat, 11 Nov 2023 08:54:40 +0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1605684.1699746206.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Sat, 11 Nov 2023 18:43:26 -0500
Message-ID: <1605685.1699746206@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1605685.1699746206%40sss.pgh.pa.us>
Precedence: bulk

Hans Buschmann <buschmann@nidsa.net> writes:
> I sent a "reply to all" mail after I received the Bug-reporting mail (WH=
Y after a longer time of clearance??) yesterday at 15:50 CET.
> It contained all 5 attachments just under 10 MB and I received the the c=
opy sent to myself. So I thought all is OK.
> Today I noticed,  that this mail doesn't appear in buglist #18189 and to=
tally disappeared for the community !!!
> It seems there are mechanisms behind the scenes, that are not documented=
 nor obvious for gentle users trying to ameliorate the software...

I think those messages are just hung up in moderation.  If you're
not subscribed to pgsql-bugs, whatever you send will be held for
moderator approval (a sadly-necessary spam defense).

Anyway, the error is coming out of this bit:

,qoffs as (
select
 ...
,(((((ra -(select ra_base from qsum))*1000000000000)::bigint)>>15)&((1::bi=
gint<<31)-1))::int as ira
 ...

"(select ra_base from qsum)" certainly is a "sub-select used as an
expression", and I guess the difference between your smaller and
larger datasets is how many rows the qsum CTE produces.  If I'm
reading it right, that'll produce one row for each distinct value
of "(source_id >> 35+12)&((1<<16)-1)".

			regards, tom lane