[rt-devel] SearchBuilder DISTINCTs

[Ian Grant]

> > 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.
> ...
> 	The reason you cannot get rid of the DISTINCTs is because of
> 	the joins.  They will lead to multiple duplicate rows.

Ah yes, I didn't think of that. The problem is when there are joins and the 
unique id column in the joined table is not selected.

> 	If you can come up with a solution to this problem that works
> 	on complicated queries, (of which I can provide you many),
> 	then we can get rid of DISTINCT.  

There might be a way to detect these queries and log them. Quite a bit of RT 
does work without the DISTINCT clauses. My interest is in getting RT usable 
with our database. If that can be done without major changes SearchBuilder 
then I would rather do that. I have a limited amount of time to spend on this.

> 	By subclassing the query building stuff, you can eliminate the
> 	DISTINCTs for SQL Server, and take a performance hit by doing
> 	dupe checking on the perl side.  But every other database
> 	doesn't have to deal with it.  

> 	If you have 2GB attachments in the database things are going
> 	to be slow anyway-

It's not my attachments that are the problem - I have had to limit them to 8K 
for other reasons (the Sybase DBD driver allocates MAX_LENGTH buffers for 
every returned column.) I was simply imagining M$'s justification for the 
restriction, albeit a weak one. They don't offer any explanation:

http://support.microsoft.com/default.aspx?scid=kb;en-us;162032

>       Maybe SQL Server has another data type
> 	that can be used?

Only VARCHAR which is limited to 256 bytes.
-- 
Ian Grant, Computer Lab., William Gates Building, JJ Thomson Ave., Cambridge
Phone: +44 1223 334420