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@debis.com (Jan Wieck) #