Re: Understanding behavior of SELECT with multiple unnested columns

2013/3/27 Ken Tanzer <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