Re: EXCEPT/INTERSECT for v6.4

I just received this via e-mail. I am boucing the whole posting to the
patches list. I am going to need help understanding the issues here.

> Hi!
>
> INTERSECT and EXCEPT is available for postgresql-v6.4!
>
> The patch against v6.4 is included at the end of the current text
> (in uuencoded form!)
>
> I also included the text of my Master's Thesis. (a postscript
> version). I hope that you find something of it useful and would be
> happy if parts of it find their way into the PostgreSQL documentation
> project (If so, tell me, then I send the sources of the document!)
>
> The contents of the document are:
> -) The first chapter might be of less interest as it gives only an
> overview on SQL.
>
> -) The second chapter gives a description on much of PostgreSQL's
> features (like user defined types etc. and how to use these features)
>
> -) The third chapter starts with an overview of PostgreSQL's internal
> structure with focus on the stages a query has to pass (i.e. parser,
> planner/optimizer, executor). Then a detailed description of the
> implementation of the Having clause and the Intersect/Except logic is
> given.
>
> Originally I worked on v6.3.2 but never found time enough to prepare
> and post a patch. Now I applied the changes to v6.4 to get Intersect
> and Except working with the new version. Chapter 3 of my documentation
> deals with the changes against v6.3.2, so keep that in mind when
> comparing the parts of the code printed there with the patched sources
> of v6.4.
>
> Here are some remarks on the patch. There are some things that have
> still to be done but at the moment I don't have time to do them
> myself. (I'm doing my military service at the moment) Sorry for that
> :-(
>
> -) I used a rewrite technique for the implementation of the Except/Intersect
> logic which rewrites the query to a semantically equivalent query before
> it is handed to the rewrite system (for views, rules etc.), planner,
> executor etc.
>
> -) In v6.3.2 the types of the attributes of two select statements
> connected by the UNION keyword had to match 100%. In v6.4 the types
> only need to be familiar (i.e. int and float can be mixed). Since this
> feature did not exist when I worked on Intersect/Except it
> does not work correctly for Except/Intersect queries WHEN USED IN
> COMBINATION WITH UNIONS! (i.e. sometimes the wrong type is used for the
> resulting table. This is because until now the types of the attributes of
> the first select statement have been used for the resulting table.
> When Intersects and/or Excepts are used in combination with Unions it
> might happen, that the first select statement of the original query
> appears at another position in the query which will be executed. The reason
> for this is the technique used for the implementation of
> Except/Intersect which does a query rewrite!)
> NOTE: It is NOT broken for pure UNION queries and pure INTERSECT/EXCEPT
> queries!!!
>
> -) I had to add the field intersect_clause to some data structures
> but did not find time to implement printfuncs for the new field.
> This does NOT break the debug modes but when an Except/Intersect
> is used the query debug output will be the already rewritten query.
>
> -) Massive changes to the grammar rules for SELECT and INSERT statements
> have been necessary (see comments in gram.y and documentation for
> deatails) in order to be able to use mixed queries like
> (SELECT ... UNION (SELECT ... EXCEPT SELECT)) INTERSECT SELECT...;
>
> -) When using UNION/EXCEPT/INTERSECT you will get:
> NOTICE: equal: "Don't know if nodes of type xxx are equal".
> I did not have time to add comparsion support for all the needed nodes,
> but the default behaviour of the function equal met my requirements.
> I did not dare to supress this message!
>
> That's the reason why the regression test for union will fail: These
> messages are also included in the union.out file!
>
> -) Somebody of you changed the union_planner() function for v6.4
> (I copied the targetlist to new_tlist and that was removed and
> replaced by a cleanup of the original targetlist). These chnages
> violated some having queries executed against views so I changed
> it back again. I did not have time to examine the differences between the
> two versions but now it works :-)
> If you want to find out, try the file queries/view_having.sql on
> both versions and compare the results . Two queries won't produce a
> correct result with your version.
>
> -) I included some queries in the patch (intersect.sql shows which
> Intersect/Except/Union queries are possible now!)
>
> Create a new database dummy and populate it:
>
> $ createdb dummy
> $ psql dummy
> dummy=> \i queries/create_insert.sql
> .
> .
> .
> dummy=> \i queries/intersect.sql
>
>
> So, that's it, hope my work will be useful!
>
> regards
>
> Stefan

--
Bruce Momjian | http://www.op.net/~candle
maillist(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026