Обсуждение: experimenting with coalesce, strange EXPLAIN results

I was experimenting with moving some of my case logic out of the
application and into sql and found  a strange EXPLAIN result with one of
my COALESCE statements.  Why is each subselect listed twice?  This is in
7.3.4 on freebsd 4.8.

cms3=# explain analyze select coalesce ((sELECT thumb FROM content_group_descriptor WHERE content_group_id = 140 AND
language_id= 1),(SELECT tc.file FROM thumbnail_content tc, ccl WHERE tc.parent_content_id = cid AND ccgid = 140 limit
1));
                                                                       QUERY PLAN
                                

--------------------------------------------------------------------------------------------------------------------------------------------------------
 Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.98..0.98 rows=1 loops=1)
   InitPlan
     ->  Seq Scan on content_group_descriptor  (cost=0.00..2.78 rows=1 width=4) (actual time=0.10..0.10 rows=0 loops=1)
           Filter: ((content_group_id = 140) AND (language_id = 1))
     ->  Seq Scan on content_group_descriptor  (cost=0.00..2.78 rows=1 width=4) (never executed)
           Filter: ((content_group_id = 140) AND (language_id = 1))
     ->  Limit  (cost=0.00..8.43 rows=1 width=12) (actual time=0.35..0.42 rows=1 loops=1)
           ->  Nested Loop  (cost=0.00..14.94 rows=2 width=12) (actual time=0.34..0.40 rows=2 loops=1)
                 ->  Seq Scan on content_collection  (cost=0.00..5.46 rows=1 width=4) (actual time=0.24..0.26 rows=3
loops=1)
                       Filter: (content_collection_group_id = 140)
                 ->  Index Scan using parent_file_key on thumbnail_content tc  (cost=0.00..6.83 rows=1 width=8) (actual
time=0.02..0.02rows=1 loops=3) 
                       Index Cond: (tc.parent_content_id = "outer".content_id)
     ->  Limit  (cost=0.00..8.43 rows=1 width=12) (actual time=0.32..0.39 rows=1 loops=1)
           ->  Nested Loop  (cost=0.00..14.94 rows=2 width=12) (actual time=0.31..0.37 rows=2 loops=1)
                 ->  Seq Scan on content_collection  (cost=0.00..5.46 rows=1 width=4) (actual time=0.24..0.26 rows=3
loops=1)
                       Filter: (content_collection_group_id = 140)
                 ->  Index Scan using parent_file_key on thumbnail_content tc  (cost=0.00..6.83 rows=1 width=8) (actual
time=0.01..0.02rows=1 loops=3) 
                       Index Cond: (tc.parent_content_id = "outer".content_id)
 Total runtime: 1.14 msec
(19 rows)

Josh Goldberg <josh@4dmatrix.com> writes:
> I was experimenting with moving some of my case logic out of the
> application and into sql and found  a strange EXPLAIN result with one of
> my COALESCE statements.  Why is each subselect listed twice?  This is in
> 7.3.4 on freebsd 4.8.

Prior to 7.4, COALESCE is just a parse-time macro for a CASE construct,
and so the items do appear twice in the CASE.

            regards, tom lane