Search Postgresql Archives

Re: Understanding behavior of SELECT with multiple unnested columns

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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!  Or this, which does not return the 12 rows we might both expect:

SELECT unnest(array[1,2]),unnest(array['a','b']),unnest(array[4,5,6]);
 unnest | unnest | unnest 
--------+--------+--------
      1 | a      |      4
      2 | b      |      5
      1 | a      |      6
      2 | b      |      4
      1 | a      |      5
      2 | b      |      6
(6 rows)

Add another element onto the third array, so they "match up" better, and you get only 4 rows:

SELECT unnest(array[1,2]),unnest(array['a','b']),unnest(array[4,5,6,7]);
 unnest | unnest | unnest 
--------+--------+--------
      1 | a      |      4
      2 | b      |      5
      1 | a      |      6
      2 | b      |      7
(4 rows)

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

Sorry for causing confusion--I meant to remove the unnest2.  There was source code for the unnest function for earlier versions, which I defined as unnest2 to try to understand what was going on.  It should yield the same behavior as unnest itself.

Cheers,
Ken

On Tue, Mar 26, 2013 at 11:55 PM, Ian Lawrence Barwick <barwick@xxxxxxxxx> wrote:
2013/3/27 Ken Tanzer <ken.tanzer@xxxxxxxxx>
>
> 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
>
> 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 behind what 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



--
AGENCY Software  
A data system that puts you in control
100% Free Software
http://agency-software.org/
ken.tanzer@xxxxxxxxxxxxxxxxxxx
(253) 245-3801

Subscribe to the mailing list to
learn more about AGENCY or
follow the discussion.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux