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)