Re: Understanding behavior of SELECT with multiple unnested columns

>
> 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(at)gmail(dot)com>wrote:

> 2013/3/27 Ken Tanzer <ken(dot)tanzer(at)gmail(dot)com>
> >
> > 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(dot)tanzer(at)agency-software(dot)org
(253) 245-3801

Subscribe to the mailing
list<agency-general-request(at)lists(dot)sourceforge(dot)net?body=subscribe>
to
learn more about AGENCY or
follow the discussion.