Re: Understanding behavior of SELECT with multiple unnested columns

[Gavin Flower <GavinFlower@xxxxxxxxxxxxxxxxx>]

On 27/03/13 20:36, Ian Lawrence Barwick wrote:

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

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

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.


Cheers,
Gavin