From: | Ian Lawrence Barwick <barwick(at)gmail(dot)com> |
---|---|
To: | Ken Tanzer <ken(dot)tanzer(at)gmail(dot)com> |
Cc: | PG-General Mailing List <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Understanding behavior of SELECT with multiple unnested columns |
Date: | 2013-03-27 06:55:48 |
Message-ID: | CAB8KJ=h_g7zWim5ih2XtYYpxkC44Xcop-BuCODboa1RizAFs6g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Ken Tanzer | 2013-03-27 07:06:38 | Re: Understanding behavior of SELECT with multiple unnested columns |
Previous Message | Ken Tanzer | 2013-03-27 06:33:49 | Understanding behavior of SELECT with multiple unnested columns |