On 27/03/13 20:36, Ian Lawrence Barwick wrote:
2013/3/27 Ken Tanzer <ken.tan...@gmail.com <mailto:ken.tan...@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
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
