Re: EXCEPT/INTERSECT for v6.4 - Mailing list pgsql-hackers

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@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026