Planner drops unreferenced tables --- bug, no?
От | Tom Lane |
---|---|
Тема | Planner drops unreferenced tables --- bug, no? |
Дата | |
Msg-id | 3624.938615683@sss.pgh.pa.us обсуждение исходный текст |
Ответы |
Re: [HACKERS] Planner drops unreferenced tables --- bug, no?
Re: [HACKERS] Planner drops unreferenced tables --- bug, no? |
Список | pgsql-hackers |
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
В списке pgsql-hackers по дате отправления: