# Finicky behavior from dbserv/Qserv on ORDER BY using table_alias.column syntax

XMLWordPrintable

#### Details

• Type: Bug
• Status: Done
• Resolution: Done
• Fix Version/s: None
• Component/s:
• Labels:
• Story Points:
10
• 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.

#### Activity

Hide
Andy Salnikov added a comment -

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").

Show
Andy Salnikov added a comment - 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").
Hide
Andy Salnikov added a comment -

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.

Show
Andy Salnikov added a comment - 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.
Hide
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; 

Show
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;
Hide
Gregory Dubois-Felsmann added a comment -

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.

Show
Gregory Dubois-Felsmann added a comment - 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.
Hide
John Gates added a comment -

Added a generic toString function in globals/stringUtil.h.

Show
John Gates added a comment - Added a generic toString function in globals/stringUtil.h.

#### People

Assignee:
John Gates
Reporter:
Gregory Dubois-Felsmann
Reviewers:
Andy Salnikov, Fabrice Jammes
Watchers:
Andy Salnikov, Fabrice Jammes, Gregory Dubois-Felsmann, John Gates, Kian-Tat Lim