Subject: Set-Returning functions in a select list
To: pgsql-general@postgresql.org
References: <159567011970.625.15282665367468168691@wrigleys.postgresql.org>
 <673790.1595689577@sss.pgh.pa.us>
From: J Lumby <johnlumby@hotmail.com>
Message-ID: 
 <DM5PR06MB3082AEC288F546F7AF5393E3A3E69@DM5PR06MB3082.namprd06.prod.outlook.com>
Date: Sat, 24 Jul 2021 11:52:23 -0400
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101
 Thunderbird/78.4.3
In-Reply-To: <673790.1595689577@sss.pgh.pa.us>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Content-Language: en-US
MIME-Version: 1.0
X-MS-Exchange-MessageSentRepresentingType: 1
Received: from macrch.localdomain (69.165.169.141) by
 YT1PR01CA0133.CANPRD01.PROD.OUTLOOK.COM (2603:10b6:b01:2f::12) with Microsoft
 SMTP Server (version=TLS1_2,
 cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.4352.25 via Frontend
 Transport; Sat, 24 Jul 2021 15:52:14 +0000
X-MS-PublicTrafficType: Email
X-IncomingHeaderCount: 47
X-EOPAttributedMessage: 0
X-MS-Office365-Filtering-Correlation-Id: 7d018b27-89a8-41e3-ce03-08d94ebb01e1
X-MS-TrafficTypeDiagnostic: BN7NAM10HT169:
X-Microsoft-Antispam: BCL:0;
X-Microsoft-Antispam-Message-Info: 
	IE1522ul1rTELItEMjCsomGN66dYguxmGtEr2VtHqeoW+Y5CDNfT+tpJvadfRunj7tM/QKxcPhNpm5J8lsqKNg33JteQQ8eKI7GRzWvhqnoHsJC7yitgvLjH/EfT9zM3I095QCEnsnGZTCJP1+if1SIU1o+VAUPNWNSOqbaFXBkPhtrlRPcgltNRlfGYmIWvch7uSYuj5pD7Ud8rzOVt0Xz1AdWvH0tUCjVWO8PiIFZ/FcI8bSFPig/YKgfyc3ga0mz1srcSU+QyTUsu+J+CijZxD+b+CHzbviT1HRbcmA1jENg0qmkXYTOd5WYdwVcKIXyzqPdV0SS9651hdd1yp6G0YzfQwQSeNfaunsVTNa/3soE7pFrHWWGuagdgcyy2T4fC6ybdyk8T4Q+c5j+XftWuiZfwLRVJ9hxx5+6wRb12ffBHXvqxaBmRb3h3PrUU
X-MS-Exchange-AntiSpam-MessageData-ChunkCount: 1
X-MS-Exchange-AntiSpam-MessageData-0: 
	ZzBr3hHNM/dQpEG6G37doBJ10v9Uvdde/a1nIWWnq9GVu3zX/2ENCzRSAlPFywJt21Xdcrs5AnekQbUAc3fQoNIwPuQPO8LNV4/53mNCeVue95FUcZ5yDUa3/9Dr4DhGjTebvmU9OVm9qKgwFs2wkQ==
X-OriginatorOrg: hotmail.com
X-MS-Exchange-CrossTenant-Network-Message-Id: 
 7d018b27-89a8-41e3-ce03-08d94ebb01e1
X-MS-Exchange-CrossTenant-OriginalArrivalTime: 24 Jul 2021 15:52:15.4947
 (UTC)
X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted
X-MS-Exchange-CrossTenant-Id: 84df9e7f-e9f6-40af-b435-aaaaaaaaaaaa
X-MS-Exchange-CrossTenant-AuthSource: 
	BN7NAM10FT053.eop-nam10.prod.protection.outlook.com
X-MS-Exchange-CrossTenant-AuthAs: Anonymous
X-MS-Exchange-CrossTenant-FromEntityHeader: Internet
X-MS-Exchange-CrossTenant-RMS-PersistedConsumerOrg: 
	00000000-0000-0000-0000-000000000000
X-MS-Exchange-Transport-CrossTenantHeadersStamped: BN7NAM10HT169
List-Id: <pgsql-general.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-general@lists.postgresql.org>
List-Owner: <mailto:pgsql-general-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-general>
Archived-At: 
 <https://www.postgresql.org/message-id/DM5PR06MB3082AEC288F546F7AF5393E3A3E69%40DM5PR06MB3082.namprd06.prod.outlook.com>
Precedence: bulk

I am unclear about the rules which postgresql uses in building a result 
set when the select list ( see note ) contains several different 
Set-Returning functions,  possibly with sets of different sizes.
At a very high level,   what I see happening is that each Set-Returning 
expression causes the result set to be expanded with additional rows,  
one per member of the set.
When there is only one such expression,     this is clear  -   the size 
of the final result set is N times the size of the output of the final 
operator where N is the size of the set   (i.e. N times the size if that 
expression had been a scalar).
But it becomes unclear where there is more than one such expression.

I can't find much on this point in the doc  (I am looking in PostgreSQL 
13.2 Documentation).    There is one small example in
"9.25. Set Returning Functions"
but in this example there is only one Set-Returning expression in the 
select list.

Also the description of the SELECT command mentions something called a 
"row group" :
"The actual output rows are computed using the SELECT output expressions 
for each selected row or row group. (See SELECT List below.)"
but no mention of Set-Returning expressions in the referenced section.

I tried an experiment :

<code>
echo -e "DROP SCHEMA testing cascade;
CREATE SCHEMA testing;
CREATE TABLE testing.set_returning_in_select_list (id int4 , bitmask_a 
int , bitmask_b int );
insert into testing.set_returning_in_select_list values ( 1 , 
x'1010'::int , x'0101'::int ) , ( 2 , x'2020'::int , x'0202'::int ) , ( 
3 , x'3030'::int , x'0303'::int );
select id , to_hex(bitmask_a) as hex_a , unnest( array [ ( bitmask_a & 
16 ) ,  ( bitmask_a & 32 ) , ( bitmask_a & 48 ) ] ) as bits_of_a ,
                to_hex(bitmask_b) as hex_b from 
testing.set_returning_in_select_list order by bits_of_a;
select id , to_hex(bitmask_a) as hex_a , unnest( array [ ( bitmask_a & 
16 ) ,  ( bitmask_a & 32 ) , ( bitmask_a & 48 ) ] ) as bits_of_a ,
                to_hex(bitmask_b) as hex_b , unnest( array [ ( bitmask_b 
& 1 ) ,  ( bitmask_b & 2 ) , ( bitmask_b & 3 ) ] ) as bits_of_b
  from testing.set_returning_in_select_list order by bits_of_a;
select id , to_hex(bitmask_a) as hex_a , unnest( array [ ( bitmask_a & 
16 ) ,  ( bitmask_a & 32 ) , ( bitmask_a & 48 ) ] ) as bits_of_a ,
                to_hex(bitmask_b) as hex_b , unnest( array [ ( bitmask_b 
& 3 ) ,  ( bitmask_b & 2 ) , ( bitmask_b & 1 ) ] ) as bits_of_b ,
                unnest( array [ ( bitmask_b & 3 ) ,  ( bitmask_b & 2 ) , 
( bitmask_b & 1 ) , ( bitmask_b & 17 ) ] ) as extraneous_bits_of_b
  from testing.set_returning_in_select_list order by bits_of_a;" | psql -e
</code>

The output of the last query is,  firstly,  different on postgresql-9.6 
than on 13.3,   and secondly,     hard to explain in either case.
my outputs appended below.

I *think* :

  . postgresql-9.6 applies a kind of triple cartesian join on the 
elements of each set, joining one set with the other set and then again 
with itself,   ending up with many duplicates
  . postgresql-13.3 applies a kind of null-padding extension of the 
elements of the smaller set and then an arbitrary linear pairing of the 
two sets,   ending up with one duplicate.

Which one,  if any,  is correct?    And why?

(note : I am not asking about the case of Set-Returning functions in the 
FROM clause,  where things seem clear(er).)

Cheers,   John Lumby

My outputs of the final query :


  . postgresql-9.6

<code>

  id | hex_a | bits_of_a | hex_b | bits_of_b | extraneous_bits_of_b
----+-------+-----------+-------+-----------+----------------------
   1 | 1010  |         0 | 101   |         0 |                    0
   1 | 1010  |         0 | 101   |         0 |                    1
   1 | 1010  |         0 | 101   |         0 |                    1
   1 | 1010  |         0 | 101   |         0 |                    1
   2 | 2020  |         0 | 202   |         2 |                    2
   2 | 2020  |         0 | 202   |         2 |                    2
   2 | 2020  |         0 | 202   |         2 |                    0
   2 | 2020  |         0 | 202   |         2 |                    0
   1 | 1010  |        16 | 101   |         1 |                    1
   1 | 1010  |        16 | 101   |         1 |                    1
   1 | 1010  |        16 | 101   |         1 |                    1
   1 | 1010  |        16 | 101   |         1 |                    0
   1 | 1010  |        16 | 101   |         1 |                    1
   1 | 1010  |        16 | 101   |         1 |                    1
   1 | 1010  |        16 | 101   |         1 |                    0
   1 | 1010  |        16 | 101   |         1 |                    1
   3 | 3030  |        16 | 303   |         3 |                    3
   3 | 3030  |        16 | 303   |         3 |                    1
   3 | 3030  |        16 | 303   |         3 |                    1
   3 | 3030  |        16 | 303   |         3 |                    2
   2 | 2020  |        32 | 202   |         2 |                    0
   2 | 2020  |        32 | 202   |         2 |                    2
   2 | 2020  |        32 | 202   |         2 |                    0
   2 | 2020  |        32 | 202   |         0 |                    0
   2 | 2020  |        32 | 202   |         2 |                    2
   2 | 2020  |        32 | 202   |         0 |                    2
   2 | 2020  |        32 | 202   |         0 |                    0
   2 | 2020  |        32 | 202   |         0 |                    2
   3 | 3030  |        32 | 303   |         2 |                    2
   3 | 3030  |        32 | 303   |         2 |                    3
   3 | 3030  |        32 | 303   |         2 |                    1
   3 | 3030  |        32 | 303   |         2 |                    1
   3 | 3030  |        48 | 303   |         1 |                    3
   3 | 3030  |        48 | 303   |         1 |                    2
   3 | 3030  |        48 | 303   |         1 |                    1
   3 | 3030  |        48 | 303   |         1 |                    1
(36 rows)

</code>


  . postgresql-13.3

<code>

  id | hex_a | bits_of_a | hex_b | bits_of_b | extraneous_bits_of_b
----+-------+-----------+-------+-----------+----------------------
   1 | 1010  |         0 | 101   |         0 |                    0
   2 | 2020  |         0 | 202   |         2 |                    2
   1 | 1010  |        16 | 101   |         1 |                    1
   1 | 1010  |        16 | 101   |         1 |                    1
   3 | 3030  |        16 | 303   |         3 |                    3
   2 | 2020  |        32 | 202   |         0 |                    0
   2 | 2020  |        32 | 202   |         2 |                    2
   3 | 3030  |        32 | 303   |         2 |                    2
   3 | 3030  |        48 | 303   |         1 |                    1
   1 | 1010  |           | 101   |           |                    1
   3 | 3030  |           | 303   |           |                    1
   2 | 2020  |           | 202   |           |                    0
(12 rows)
</code>