Thread: Understanding behavior of SELECT with multiple unnested columns

2013/3/27 Ken Tanzer <ken.tanzer@gmail.com>
>
> I've been working on some queries involving multiple unnested columns.  At first, I expected the number of rows
returnedwould be the product of the array lengths, so that this query would return 4 rows: 
>
> SELECT unnest2(array['a','b']),unnest2(array['1','2']);
>
> when in fact it returns 2:
>
>  unnest2 | unnest2
> ---------+---------
>  a       | 1
>  b       | 2
>
> Which is all well and good.  (Better, in fact, for my purposes.)  But then this query returns 6 rows:
>
> SELECT unnest2(array['a','b','c']),unnest2(array['1','2']);
>  unnest2 | unnest2
> ---------+---------
>  a       | 1
>  b       | 2
>  c       | 1
>  a       | 2
>  b       | 1
>  c       | 2
>
> Throw an unnested null column in and you get zero rows, which I also didn't expect:
>
> SELECT unnest2(array['a','b','c']),unnest2(array['1','2']),unnest(NULL::varchar[]);
>  unnest2 | unnest2 | unnest
> ---------+---------+--------
> (0 rows)
>
>
> After some head scratching, I think I understand what to expect from these unnests, but I'm unclear of the logic
behindwhat is going on.  I'm hoping someone can explain it a bit. 

Basically you are getting Cartesian joins on the row output of
unnest() (and presumably
unnest2() - I guess this is a function you defined yourself?)

Effectively you are doing this:

CREATE TABLE t1 (val INT);
INSERT INTO t1 VALUES (1),(2);

CREATE TABLE t2 (val CHAR(1));
INSERT INTO t2 VALUES ('a'),('b'),('c');

CREATE TABLE t3 (val INT);

testdb=# SELECT * from t1, t2;
 val | val
-----+-----
   1 | a
   1 | b
   1 | c
   2 | a
   2 | b
   2 | c
(6 rows)


testdb=# DELETE FROM t2 where val='c';
DELETE 1
testdb=# SELECT * from t1, t2;
 val | val
-----+-----
   1 | a
   1 | b
   2 | a
   2 | b
(4 rows)

testdb=# SELECT * from t1, t2, t3;
 val | val | val
-----+-----+-----
(0 rows)


HTH

Ian Barwick

Basically you are getting Cartesian joins on the row output of
unnest()

(and presumably
unnest2() - I guess this is a function you defined yourself?)

2013/3/27 Ken Tanzer <ken.tanzer@gmail.com>

Basically you are getting Cartesian joins on the row output of
unnest()

Well that's what I expected too.  Except look at this example, after you delete c:

testdb=# DELETE FROM t2 where val='c';

DELETE 1

testdb=# SELECT * from t1, t2;

 val | val

-----+-----

   1 | a

   1 | b

   2 | a

   2 | b

(4 rows)

 

And compare to:

SELECT unnest(array[1,2]),unnest(array['a','b']);

 unnest | unnest 

--------+--------

      1 | a

      2 | b

(2 rows)

You can see they are not the same! 

Ah yes, what I suggested is actually the equivalent to 
SELECT * FROM unnest(array[1,2]) u1,unnest(array['a','b']) u2;

I seem to recall seeing the explanation for this before, although I'll be
darned if I can remember what it is.

FWIW this happens with other functions returning SETOF:

testdb=# SELECT
testdb-# generate_series(1,2) x,
testdb-# generate_series(1,2) y;
 x | y
---+---
 1 | 1
 2 | 2
(2 rows)

testdb=# SELECT
testdb-# generate_series(1,2) x,
testdb-# generate_series(1,3) y;
 x | y
---+---
 1 | 1
 2 | 2
 1 | 3
 2 | 1
 1 | 2
 2 | 3
(6 rows)

Regards


Ian Barwick

Gavin Flower <GavinFlower@archidevsys.co.nz> writes:
> The rule appears to be,
> where N_x & N_y are the number of entries returned for x & y:
> N_result = is the smallest positive integer that has N_x & N_y as factors.

Right: if there are multiple set-returning functions in a SELECT list,
the number of rows you get is the least common multiple of their
periods.  (See the logic in ExecTargetList that cycles the SRFs until
they all report "done" at the same time.)  I guess there's some value
in this for the case where they all have the same period, but otherwise
it's kind of bizarre.  It's been like that since Berkeley days though,
so I doubt we'll consider changing it now.  Rather, it'll just be
quietly deprecated in favor of putting SRFs into FROM (with LATERAL
where needed).

            regards, tom lane

2013/3/27 Tom Lane <tgl@sss.pgh.pa.us>:
> Gavin Flower <GavinFlower@archidevsys.co.nz> writes:
>> The rule appears to be,
>> where N_x & N_y are the number of entries returned for x & y:
>> N_result = is the smallest positive integer that has N_x & N_y as factors.
>
> Right: if there are multiple set-returning functions in a SELECT list,
> the number of rows you get is the least common multiple of their
> periods.  (See the logic in ExecTargetList that cycles the SRFs until
> they all report "done" at the same time.)  I guess there's some value
> in this for the case where they all have the same period, but otherwise
> it's kind of bizarre.  It's been like that since Berkeley days though,
> so I doubt we'll consider changing it now.  Rather, it'll just be
> quietly deprecated in favor of putting SRFs into FROM (with LATERAL
> where needed).

Thanks for the clarification, I was half-worried there was some fundamental
set theory or something which had passed me by.

Regards

Ian Barwick

Gavin Flower <GavinFlower@archidevsys.co.nz> writes:

The rule appears to be,
where N_x & N_y are the number of entries returned for x & y:
N_result = is the smallest positive integer that has N_x & N_y as factors.

Right: if there are multiple set-returning functions in a SELECT list,
the number of rows you get is the least common multiple of their
periods.  (See the logic in ExecTargetList that cycles the SRFs until
they all report "done" at the same time.)  I guess there's some value
in this for the case where they all have the same period, but otherwise
it's kind of bizarre.  It's been like that since Berkeley days though,
so I doubt we'll consider changing it now.  Rather, it'll just be
quietly deprecated in favor of putting SRFs into FROM (with LATERAL
where needed).
		regards, tom lane

On 2013-03-27, Ken Tanzer <ken.tanzer@gmail.com> wrote:
> --047d7b5da657ecd54004d8e23a90
> Content-Type: text/plain; charset=ISO-8859-1
>
> I've been working on some queries involving multiple unnested columns.  At
> first, I expected the number of rows returned would be the product of the
> array lengths, so that this query would return 4 rows:
>
> SELECT unnest2(array['a','b']),unnest2(array['1','2']);
>
> when in fact it returns 2:
>
>  unnest2 | unnest2
> ---------+---------
>  a       | 1
>  b       | 2
>
> Which is all well and good.  (Better, in fact, for my purposes.)  But then
> this query returns 6 rows:
>
> SELECT unnest2(array['a','b','c']),unnest2(array['1','2']);
>  unnest2 | unnest2
> ---------+---------
>  a       | 1
>  b       | 2
>  c       | 1
>  a       | 2
>  b       | 1
>  c       | 2
>

check out the documentation for generate_series, it behaves similarly.

I think basically the problem is caused by using SRFs between SELECT
and FROM

to see that it's not the cartesion product try it with sets of length
4 and 6

SELECT unnest(array['a','b','c','d']),unnest(array['1','2','3','4','5','6']);


what you want is possible, but it's not pretty - you have to number
the results and join explicitly.

with
  a as ( select u,row_number() over ()
           from (select unnest(array['a','b']) as u ) as x),
  b as ( select u,row_number() over ()
           from (select unnest(array['1','2','3']) as u ) as x)
select a.u as a, b.u as b
  from a full outer join b on a.row_number=b.row_number;


⚂⚃ 100% natural

On Wed, Mar 27, 2013 at 9:03 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Gavin Flower <GavinFlower@archidevsys.co.nz> writes:
>> The rule appears to be,
>> where N_x & N_y are the number of entries returned for x & y:
>> N_result = is the smallest positive integer that has N_x & N_y as factors.
>
> Right: if there are multiple set-returning functions in a SELECT list,
> the number of rows you get is the least common multiple of their
> periods.  (See the logic in ExecTargetList that cycles the SRFs until
> they all report "done" at the same time.)  I guess there's some value
> in this for the case where they all have the same period, but otherwise
> it's kind of bizarre.  It's been like that since Berkeley days though,
> so I doubt we'll consider changing it now.  Rather, it'll just be
> quietly deprecated in favor of putting SRFs into FROM (with LATERAL
> where needed).

It's a neat way to make a query that doesn't terminate (which AFAIK is
impossible in vanilla SQL):

create sequence s;
select generate_series(1,nextval('s')), generate_series(1,nextval('s'));

merlin