[rt-devel] SearchBuilder DISTINCTs

[Ian Grant]

Sorry about the spurious Subject: header and the duplicate signatures on my 
previous message.

> I suspect that SearchBuilder's query building probably needs to be
> refactored to call out to methods in Handle, to allow easier
> customization

Is this for reasons other than the particular problem I know about?

> ....It's a fairly serious refactoring job, if you're up for
> it.

I'm not the best person to do this sort of thing - I am not good at 
object-oriented programming.

> > 
> > The first is that SearchBuilder does SELECTs always using the DISTINCT 
> > keyword. This is a problem because SQL Server will not allow DISTINCT with 
> > large object (type TEXT) fields. (They can be up to 2GB and I suppose the 
> > comparisons could get expensive.)
> > 
> > My question is: Does anyone know, or, failing that, can anyone suggest a way 
> > to find out where the DISTINCT clauses are really necessary?
> 
> Er. Just about everywhere. DBIx::SearchBuilder desperately needs the set
> of rows returned to be unique.

Having stared at this a little harder I am now of the opinion that the 
DISTINCT argument is redundant in SELECT DISTINCT main.* FROM table main [, 
....]  because every table has a column 'id' which contains an integer unique 
to that row in that table. This is sufficient to make every row distinct. As 
far as I can see this the only place in SearchBuilder.pm where it's a problem 
for SQL Server.


 
-- 
Ian Grant, Computer Lab., William Gates Building, JJ Thomson Ave., Cambridge
Phone: +44 1223 334420