Uploaded image for project: 'Data Management'
  1. Data Management
  2. DM-8426

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: dbserv, Qserv
    • Labels:

      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

          Hide
          salnikov 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
          salnikov 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
          salnikov 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
          salnikov 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
          jgates 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
          jgates 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
          gpdf 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
          gpdf 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
          jgates John Gates added a comment -

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

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

            People

            Assignee:
            jgates John Gates
            Reporter:
            gpdf Gregory Dubois-Felsmann
            Reviewers:
            Andy Salnikov, Fabrice Jammes
            Watchers:
            Andy Salnikov, Fabrice Jammes, Gregory Dubois-Felsmann, John Gates, Kian-Tat Lim
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: