Re: Understanding behavior of SELECT with multiple unnested columns

On 27/03/13 20:36, Ian Lawrence Barwick wrote:
>
> 2013/3/27 Ken Tanzer <ken(dot)tanzer(at)gmail(dot)com <mailto:ken(dot)tanzer(at)gmail(dot)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
>

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