Re: [HACKERS] Open 6.5 items
От | jwieck@debis.com (Jan Wieck) |
---|---|
Тема | Re: [HACKERS] Open 6.5 items |
Дата | |
Msg-id | m10kAA0-000EBbC@orion.SAPserv.Hamburg.dsh.de обсуждение исходный текст |
Ответ на | Open 6.5 items (Bruce Momjian <maillist@candle.pha.pa.us>) |
Ответы |
Re: [HACKERS] Open 6.5 items
(Bruce Momjian <maillist@candle.pha.pa.us>)
|
Список | pgsql-hackers |
> resno's, sublevelsup corrupt when reaching rewrite system Don't remember exactly how I produced them. Haven't seen them again after the latest changes in the rule system. I think it was due to incorrect handling of unrewritten TLE's from group by clauses, which are now pulled out of the main targetlist. > 3 = sum(x) in rewrite system is a problem Is it? I guess what is meant by this item is the problem of the rewriter that it must create subqueries for view aggregate columns if they appear in the WHERE clause. That entire area is a very problematic one. And for sake it must wait for after v6.5. Aggregates and GROUP BY in views are unsafe and depend on the later usage of the view. Consider the following: CREATE TABLE t1 (a text, b text, c int4); CREATE VIEW v1 AS SELECT a, b, sum(c) as n FROM t1 GROUP BY a, b; CREATE TABLE t2 (a text, b text); SELECT t2.a, v1.n FROM t2, v1 WHERE t2.a = v1.a GROUP BY t2.a; Due to the new code in the rewriter, adding junk TLE's for the view's GROUP BY columns, this doesn't crash the backend anymore. The result (IMHO wrong) will return multiple rows with same t2.a because the rewritten query reads as: SELECT t2.a, sum(t1.c) FROM t2, t1 WHERE t2.a = t1.a GROUP BY t2.a, t1.a, t1.b; The correct result would be only one row per t2.a with one of the possible values of v1.n if a plain SELECT * FROM v1 is done. But there's currently no way to express that in a querytree. What's absolutely broken is: SELECT t2.a, sum(v1.n) FROM t2, v1 WHERE t2.a = v1.a GROUP BY t2.a; This gives totally unpredictable results because after rewriting you have cascaded aggregates. And I expected the rotten results I've seen from it :-) I really hope to find the time after v6.5 to implement my idea of subselecting RTE's where I can place all those views that have these beasty DISTINCT, UNION, GROUP BY and other f*ing stuff. The result of a subselecting RTE will be an on- the-fly-materialization of the entire view used in a nestloop or so (dunno exactly yet). It's expansive - yes - and I don't know yet how to pull out restrictions from the WHERE clause to make the views subset as small as possible - but AFAICS the only fail-safe way to meet the view definition in a complex join. > Future TODO items > ----------------- > CREATE VIEW ignores DISTINCT Covered above. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #======================================== jwieck@debis.com (Jan Wieck) #
В списке pgsql-hackers по дате отправления:
Следующее
От: ZEUGSWETTER Andreas IZ5Дата:
Сообщение: Re: [HACKERS] Some progress on INSERT/SELECT/GROUP BY bugs