[rt-devel] DBIx::SearchBuilder DistinctQuery for Oracle

[Brook Schofield]

Jesse, Pavel,
         attached is a patch against DBIx::SearchBuilder to allow for 
DISTINCT queries in Oracle.

a) DISTINCT queries aren't performed on a query against a single table. I 
can't see a reason why a select on a single table wouldn't return a 
distinct set of data - since that table on its own is distinct. If someone 
can give an example that disproves this please tell me.

b) DISTINCT queries are off loaded to the individual database specific 
Handles - just incase PostgreSQL or SQLServer prefer a different format.

Since Oracle doesn't like DISINCT in a select clause containing CLOB/BLOB 
types I've moved it into a subselect to ease the pain.

Original Query (MySQL compatible):
SELECT DISTINCT main.* FROM Tickets main, Users users WHERE
(main.Owner = users.id) ORDER BY main.id ASC

Oracle Query:
SELECT main.* FROM (
         SELECT DISTINCT main.id FROM Tickets main, Users users WHERE 
(main.Owner = users.id)
                              ^^
) distinctquery, Tickets main WHERE (main.id = distinctquery.id)

This format query also allows for the wrappering by the ApplyLimits patch.

Although I believe that a more efficient version of the DISTINCT and LIMIT 
subqueries would be to perform the LIMIT before the DISTINCT join, as in:

SELECT main.* FROM (
         SELECT * FROM (
                 SELECT limitquery.*,rownum rn FROM (
                         SELECT DISTINCT main.id FROM Tickets main, Users 
users WHERE
                         (main.Owner = users.id) ORDER BY main.id ASC
                 ) limitquery WHERE rownum <= 50
         ) WHERE rn >= 1
) distinctquery, Tickets main WHERE (main.id = distinctquery.id)

as this would limit the result set before the join with the original table. 
Until I find
(or someone else finds) a better way of structuring the LIMIT and DISTINCT
manipulations this format will have to do:

SELECT * FROM (
         SELECT limitquery.*,rownum rn FROM (
                 SELECT main.* FROM (
                         SELECT DISTINCT main.id FROM Tickets main, Users 
users WHERE
                         (main.Owner = users.id) ORDER BY main.id ASC
                 ) distinctquery, Tickets main WHERE (main.id = 
distinctquery.id)
         ) limitquery WHERE rownum <= 50
) WHERE rn >= 1

Those running Oracle will just have to use beefy database servers ;-)

NB:- The patch also changes a section in CleanSlate() from 'tables' to 'table'
which I believe is a typo!

-Brook

=========================================================================
=     _/_/_/ _/_/_/ _/_/_/ _/_/_/ _/  _/ Brook Schofield                =
=    _/  _/ _/  _/ _/  _/ _/  _/ _/ _/   B.Schofield at griffith.edu.au    =
=   _/_/   _/_/_/ _/  _/ _/  _/ _/_/     Ph: +61 7 387 53779 - WCN 0.28 =
=  _/  _/ _/ _/  _/  _/ _/  _/ _/ _/     Directory Services Integration =
= _/_/_/ _/  _/ _/_/_/ _/_/_/ _/  _/     Griffith University QLD 4111   =
=========================================================================
-------------- next part --------------
A non-text attachment was scrubbed...
Name: oracle_distinct.patch
Type: application/octet-stream
Size: 3867 bytes
Desc: not available
Url : http://pallas.eruditorum.org/pipermail/rt-devel/attachments/20030317/a2c7be42/oracle_distinct.obj