Details
-
Type:
Bug
-
Status: Done
-
Resolution: Done
-
Fix Version/s: None
-
Labels:
-
Story Points:10
-
Epic Link:
-
Sprint:DB_S17_12, DB_S17_01, DB_S17_2
-
Team:Data Access and Database
Description
The following dbserv query works:
curl -d 'query=SELECT objectId, id, fsrc.exposure_id, fsrc.exposure_time_mid, exp.run, scisql_dnToAbMag(fsrc.flux_psf,exp.fluxMag0) AS g, scisql_dnToAbMagSigma(fsrc.flux_psf, fsrc.flux_psf_err, exp.fluxMag0, exp.fluxMag0Sigma) AS gErr FROM RunDeepForcedSource AS fsrc, Science_Ccd_Exposure AS exp WHERE exp.scienceCcdExposureId = fsrc.exposure_id AND fsrc.exposure_filter_id=1 AND objectId=3448068867358968 ORDER BY exposure_time_mid' http://lsst-qserv-dax01.ncsa.illinois.edu:5000/db/v0/tap/sync
while this version, identical in all but one respect, fails:
curl -d 'query=SELECT objectId, id, fsrc.exposure_id, fsrc.exposure_time_mid, exp.run, scisql_dnToAbMag(fsrc.flux_psf,exp.fluxMag0) AS g, scisql_dnToAbMagSigma(fsrc.flux_psf, fsrc.flux_psf_err, exp.fluxMag0, exp.fluxMag0Sigma) AS gErr FROM RunDeepForcedSource AS fsrc, Science_Ccd_Exposure AS exp WHERE exp.scienceCcdExposureId = fsrc.exposure_id AND fsrc.exposure_filter_id=1 AND objectId=3448068867358968 ORDER BY fsrc.exposure_time_mid' http://lsst-qserv-dax01.ncsa.illinois.edu:5000/db/v0/tap/sync
The change is from ORDER BY exposure_time_mid to ORDER BY fsrc.exposure_time_mid .
The error in the second instance is:
{"message": "(_mysql_exceptions.OperationalError) (1054, \"Unknown column 'fsrc.exposure_time_mid' in 'order clause'\")", "error": "OperationalError"}
Severity is minor, for now, because the first form works for PDAC and in this case there is no ambiguity between the joined tables. However, it should be investigated because it may be the sign of a more complex issue.
Attachments
Activity
Reviewed, see comments on PR. I'm still not quite happy and I believe we should do better otherwise there will be more surprised clients. Still this ticket is an improvement and we should probably open new ticket now to try and make it even better.
There might be some confusion here. It's only the queries that add qualifiers to the ORDER BY clauses that will not work. Hopefully, any query that has a problematic clause in the ORDER BY will now return an error immediately. The first query above should be fine. SELECT fsrc.exposure_time_mid ... ORDER BY exposure_time_mid should work without issue, as there's only one instance of exposure_time_mid.
All of the following should cause an immediate error and can easily be fixed with an alias.
SELECT fsrc.exposure_time_mid, obj.exposure_time_mid ... ORDER BY exposure_time_mid; -- duplicate column exposure_time_mid
|
SELECT fsrc.exposure_time_mid ... ORDER BY fsrc.exposure_time_mid; -- fsrc. qualifier not allowed in ORDER BY
|
SELECT ABS(exposure_time_mid) ... ORDER BY ABS(exposure_time_mid); -- order by clause is too complicated, column header will almost certainly not match order by clause.
|
SELECT exposure_time_mid * 2 ... ORDER BY exposure_time_mid * 2; -- order by clause is again too complicated.
|
SELECT exposure_time_mid AS expTimeMid ... ORDER BY exposure_time_mid; -- needs to use the alias.
|
The following should work without issue. Using an alias is very reliable.
SELECT fsrc.exposure_time_mid AS fExpTimeMid, obj.exposure_time_mid ... ORDER BY fExpTimeMid;
|
SELECT fsrc.exposure_time_mid ... ORDER BY exposure_time_mid;
|
SELECT fsrc.exposure_time_mid AS fExpTimeMid ... ORDER BY fExpTimeMid;
|
SELECT ABS(exposure_time_mid) AS absExpExpTime ... ORDER BY absExpExpTime;
|
SELECT exposure_time_mid * 2 AS fExpTimeMidx2 ... ORDER BY fExpTimeMidx2;
|
John Gates
added a comment - - edited There might be some confusion here. It's only the queries that add qualifiers to the ORDER BY clauses that will not work. Hopefully, any query that has a problematic clause in the ORDER BY will now return an error immediately. The first query above should be fine. SELECT fsrc.exposure_time_mid ... ORDER BY exposure_time_mid should work without issue, as there's only one instance of exposure_time_mid .
All of the following should cause an immediate error and can easily be fixed with an alias.
SELECT fsrc.exposure_time_mid, obj.exposure_time_mid ... ORDER BY exposure_time_mid; -- duplicate column exposure_time_mid
SELECT fsrc.exposure_time_mid ... ORDER BY fsrc.exposure_time_mid; -- fsrc. qualifier not allowed in ORDER BY
SELECT ABS(exposure_time_mid) ... ORDER BY ABS(exposure_time_mid); -- order by clause is too complicated, column header will almost certainly not match order by clause.
SELECT exposure_time_mid * 2 ... ORDER BY exposure_time_mid * 2; -- order by clause is again too complicated.
SELECT exposure_time_mid AS expTimeMid ... ORDER BY exposure_time_mid; -- needs to use the alias.
The following should work without issue. Using an alias is very reliable.
SELECT fsrc.exposure_time_mid AS fExpTimeMid, obj.exposure_time_mid ... ORDER BY fExpTimeMid;
SELECT fsrc.exposure_time_mid ... ORDER BY exposure_time_mid;
SELECT fsrc.exposure_time_mid AS fExpTimeMid ... ORDER BY fExpTimeMid;
SELECT ABS(exposure_time_mid) AS absExpExpTime ... ORDER BY absExpExpTime;
SELECT exposure_time_mid * 2 AS fExpTimeMidx2 ... ORDER BY fExpTimeMidx2;
So I think this is a non-breaking change for PDAC, if I'm reading this correctly. Still, I'd like to be made aware of when this change, once merged, is actually deployed, so that we can promptly check our behavior.
Added a generic toString function in globals/stringUtil.h.
John Gates
added a comment - Added a generic toString function in globals/stringUtil.h. 
I think I disagree with John, if I'm reading it correctly the first query should still be acceptable because there is just one exposure_time_mid AND MySQL in this case should name result column exposure_time_mid too. Second query with ORDER BY fsrc.exposure_time_mid will fail (immediately and not after 1 hour later) but with more explicit error message ("ORDER BY argument should not include database or table").