[rt-devel] Behaviour correction for SB-0.88 (improves Oracle support)

[Brook Schofield]

On Thursday, July 17, 2003, at 07:35  PM, Paul Wagland wrote:

> Figured I would chime in... I can't say whether the change is correct 
> or
> not, will look into testing this next Monday... off to Frankfurt for 
> the REM
> concert in the meantime ;-)

The issue isn't whether the change is correct or not - its whether the 
SQL that is generated gives the same result set as previous 
implementations (or a result set the same as the MySQL or PostgreSQL 
implementations - which wasn't the case).

This change to the distinct code does not change the result set 
returned - but does improve the SQL generated.

This also made it easier for me to make the next change to SB-0.88 - in 
that changing the order of the building of the SQL removed the syntax 
problems with the SQL generated for Oracle.

The only issue I could see creeping up is the ($key IS NULL or $key = 
'') change - as that may not be portable between other databases. This 
is due to the differences between CLOB and VARCHAR2 in Oracle (this has 
been detailed in a previous post of mine).

> But with postgres, this statement appears to be true:

This SQL isn't generated for PostgreSQL. Only Oracle uses the nested 
SELECT DISTINCT to avoid the problem of being unable to perform a 
DISTINCT on a table with a CLOB column type.

I believe that the previous code would have generated an excessive 
amount of SELECT DISTINCT queries when they are unnecessary.

> Here is the explain texts for these. (We have 18 queues, 1 of which is
> disabled). Mind you, this probably won't make a huge difference in the
> interface... but every little bit helps I guess...

Since this improvement is present in all queries - there would be an 
improvement by not needing a DISTINCT. Remember that this change would 
only modify the SQL for PostgreSQL/MySQL from:

SELECT DISTINCT main.* FROM Queues main WHERE (main.Disabled = '0')

to:

SELECT main.* FROM Queues main WHERE (main.Disabled = '0')

which should result in very little cost saving - but a much bigger 
improvement for Oracle users. Not that there are any Oracle users (or 
at least not many - because support is not complete).

BTW:	There are many other SQL queries that were generated using an 
unnecessary DISTINCT - so there will be savings by removing this when 
it isn't necessary - especially on large datasets most useful for 
installations which have large numbers of users.

Additional Oracle support is still on the way!

-Brook