Whilst chasing down a few more aggregate-related bug reports,
I realized that the planner is doing the Wrong Thing when
a query's FROM clause mentions tables that are not used
elswhere in the query. For example, I make a table with
three rows:
play=> select x.f1 from x;
f1
--123
(3 rows)
Now:
play=> select x.f1 from x, x as x2;
f1
--123
(3 rows)
It seems to me that the latter query must yield 9 rows (three
occurrences of each value) to satisfy the SQL spec. The spec defines
the result of a two-query FROM clause to be the Cartesian product of the
two tables, period. It doesn't say anything about "only if one or more
columns of each table are actually used somewhere".
The particular case that led me into this was for an aggregate:
play=> select count(f1) from x;
count
----- 3
(1 row)
play=> select count(1) from x;
count
----- 1
(1 row)
Now IMHO count(1) should yield the same count as for any other non-null
expression, ie, the number of rows in the source table, because the spec
effectively says "evaluate the expression for each row and count the
number of non-null results". The reason you get 1 here is that the
planner is dropping the "unreferenced" x, deciding that the query looks
like "select 2+2;", and generating a single-row Result plan.
Before I look into ways of fixing this, is there anyone who wants
to argue that the current behavior is correct? It looks all wrong
to me, but...
regards, tom lane