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

    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

            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:

                Summary Panel