#2 /result query optimized to remove merge join, will break oth

[Allen Day]

Query in:
/var/www/das2-chado-Hsa-17/lib/Bio/Das2/Source/Assay/Celsius.pm
line 95, and subsequent mangling of platform allows for direct join to the correct table in the netaffx schema, e.g. netaffx.hg_u133a_annot.

The problem with this is that previously a LEFT JOIN was being done against annot1. While this is fine for platforms that inherit from annot1 (i.e. LEFT JOIN will return no rows), this introduces an error when attempting to retrieve data from tables that do not inherit from annot1 (e.g. netaffx.mapping10k_xba131_annot).

This is going to be a bitch to fix. One route to explore is looking at the Pg catalog to see which parent table (i.e. annot1, annot2, or annot3), then formulating the query appropriately.

[boxman]

Logged In: YES
user_id=1588688
Originator: NO

on radius

[Allen Day]

Logged In: YES
user_id=288536
Originator: YES

this should succeed, http 200 OK
wget -O - -S 'http://das.biopackages.net/das/assay/celsius/1/result/SN:1005595?format=egr;protocol=rma'
this shold fail, http 500 internal server error
wget -O - -S 'http://das.biopackages.net/das/assay/celsius/1/result/SN:1021600?format=egr;protocol=brlmm'

when they are both 200 OK you have fixed it.
do this in an interactive psql session so you don't have to worry about webserver caching.

SELECT DISTINCT
'SN:1005595' AS result, x.accession AS name, p.name AS platform, 'SN:1005595' AS quantification_id, o.alignments, (part_elementresult.pva( r.signal, (SELECT nodes FROM part_elementresult.pvamodel WHERE arraydesign_id = y.arraydesign_id AND analysis_id = q.analysis_id AND element_id = r.element_id) ))[0] AS signal
FROM
dbxref AS x LEFT JOIN netaffx.hg_u133a_annot AS o ON ( x.dbxref_id = o.dbxref_id ),
element AS e,
part_elementresult.rma_byq AS r,
quantification AS q,
acquisition AS u,
assay AS y,
arraydesign AS p
WHERE e.element_id = r.element_id
AND e.dbxref_id = x.dbxref_id
AND r.quantification_id = q.quantification_id
AND q.acquisition_id = u.acquisition_id
AND u.assay_id = y.assay_id
AND y.arraydesign_id = p.arraydesign_id
AND r.quantification_id = 7260
ORDER BY x.accession, o.alignments DESC