Re: [HACKERS] Open 6.5 items

> 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(at)debis(dot)com (Jan Wieck) #