Re: Having Patch (against v6.3.2) - Mailing list pgsql-hackers

I have applied this fine patch from Stephan.

It fixes many problems with Having, and some other problems that exist.

Vadim, can you check on the psort_end() issue, and see where that should
go.  I am lost in that area.


[Charset ISO-8859-1 unsupported, filtering to ASCII...]
> Hi out there!
>
> I think I have good news for you: Having is working now!  The patch is
> included in this email (tarred, gnu-zipped and finally uuencoded).
>
> But before you go on unpacking the patch let me tell you about the
> following items:
>
> 1) Queries using the having clause on base tables should work well
>    now. Here some tested features, (examples included in the patch):
>
> 1.1) Subselects in the having clause
> 1.2) Double nested subselects
> 1.3) Subselects used in the where clause and in the having clause
>      simultaneously
> 1.4) Union Selects using having
> 1.5) Indexes on the base relations are used correctly
> 1.6) Unallowed Queries are prevented (e.g. qualifications in the
>      having clause that belong to the where clause)
> 1.7) Insert into as select
>
> 2) Queries using the having clause on view relations also work
>    but there are some restrictions:
>
> 2.1) Create View as Select ... Having ...; using base tables in the select
>      does work *BUT*: only simple queries are allowed to this new
>                       created view. This is *not* because of the having
>                       logic but on the technique used to implement
>                       views in postgreSQL, the query rewrite system.
>
> 2.1.1) The Query rewrite system:
>        As you know, postgreSQL uses a query rewrite system to
>        implement views. It does so by storing the query used to define
>        the view somewhere in the system catalogs. If a user makes a
>        query against the view the system "rewrites" the user query by
>        merging it with the stored query (used to define the view). The
>        new "rewritten" query is optimized, planned, etc and executed
>        against the base tables from the view definition query.
>
> 2.1.2) Why are only simple queries allowed against a view from 2.1) ?
>        The problem with the technique described in 2.1.1) is, that it
>        is unfortunately not possible to merge any two SQL queries in
>        in such a way that the result will behave as expected:
>        consider the following view definition:
>
>           create view testview as
>           select pid, sid
>           from part
>           where pid=5
>           group by pid;
>
>        and the following query:
>
>           select max(pid), sid
>           from testview
>           where sid = 100
>           group by sid;
>
>        The query rewrite system will produce something like:
>
>           select max(pid), sid
>       from part
>           where pid=5 AND sid = 100 /* no problem here */
>           group by ???   /* which attribute(s) to group by?? */
>
>        You see, if the view definition and the query both contain
>        a group clause, we will run into troubles.
>
>        The solution to this would be the implementation of subselects
>        in the from clause, then the rewrite system would produce:
>
>           select max(pid), sid
>           from (select pid, sid
>                 from part
>                 where pid=5
>                 group by pid)
>           where sid = 100
>           group by sid;
>
> 2.2) Select ... from testview1, testview2, ... having...;
>      does also work, as long as the views used are simple
>      row/column subsets of the baserelations used. (No group clauses
>      in the view definitons)
>
>
> 3) Bug in ExecMergeJoin ??
>    This is something that has *NOTHING* to do with the Having logic!
>    Proof: Try the following query (without having my patch applied):
>
>     select s.sid, s.sname, s.city
>     from supplier s
>     where s.sid+10 in (select se1.pid
>                       from supplier s1, sells se1, part p1
>                       where s1.sid=se1.sid and
>                             s1.sname=s.sname and
>                             se1.pid=p1.pid);
>
>     (The file 'queries/create_insert.sql' included in the patch contains the
>      data for this, the query is included in 'queries/having.sql' !)
>
>     As you can see, there is neither a having qual nor an aggregate
>     function used in the above query an you will see, it will fail!
>
>     I found out that the reason for this is in the function
>     'ExecMergeJoin()' in
>                switch (mergestate->mj_JoinState)
>         {
>                   ....
>             case EXEC_MJ_NEXTOUTER:
>                      ....
>                      CleanUpSort(node->join.lefttree->lefttree);
>                      CleanUpSort(node->join.righttree->lefttree);
>               ....
>                 }
>
>     In 'CleanUpSort()' the function 'psort_end()' gets called and
>     closes down the sort, which is correct as long as no subselects
>     are in use!
>
>     I found out, that the bug does not appear when I comment the call
>     to 'psort_end()' out in 'CleanUpSort()'.
>
>     I heavily tested the system after that and things worked well but
>     maybe this is just a lucky chance.
>
>     So please, if anybody who has good knowledge of that part of the
>     code could have a look at that it would be great!
>
>     I am sure the sort has to be ended at some time calling 'psort_end()'
>     but I did not have enough time to look for the right place. I was
>     just happy about the fact it produced some correct results and
>     stopped working on that.
>
>
> 4) Test Examples included:
>    in the patch there is a directory 'queries' containing the
>    following files:
>           create_insert.sql      to create the test relations and views
>           destroy.sql            to drop the test relations and views
>           having.sql             the test queries on base relations
>           view_having.sql        the test queries on / defining views
>
> 5) The patch is against the original v6.3.2 and can be applied
>    by:
>        cd ..../postgresql-6.3.2/
>        patch [-p2] < having_6.3.2.diff
>
>
>
>
> Regards Stefan


--
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)