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

Fix SQL using join and "select alias.fieldname"

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Invalid
    • Resolution: Done
    • Fix Version/s: None
    • Component/s: Qserv
    • Labels:

      Description

      Next query fails:

      mysql --host=127.0.0.1 --port=4040 --user=qsmaster qservTest_case04_qserv -e "SELECT sce.filterName, sce.field, sce.camcol, sce.run, s.deepForcedSourceId, s.ra, s.decl, s.x, s.y, s.psfFlux, s.psfFluxSigma, s.apFlux, s.apFluxSigma, s.modelFlux, s.modelFluxSigma, s.instFlux, s.instFluxSigma, s.shapeIxx, s.shapeIyy, s.shapeIxy, s.flagPixInterpCen, s.flagNegative, s.flagPixEdge, s.flagBadCentroid, s.flagPixSaturCen, s.extendedness FROM DeepForcedSource AS s, Science_Ccd_Exposure AS sce WHERE (s.scienceCcdExposureId = sce.scienceCcdExposureId) LIMIT 100"

      Here's qserv-czar.log:

      0207 02:44:09.082 [0x7f7cd3fff700] DEBUG root (build/qdisp/MessageStore.cc:49) - Msg: 7140 2000 Complete (success) 0 1423273449
      0207 02:44:09.082 [0x7f7cd3fff700] DEBUG root (build/qdisp/MessageStore.cc:49) - Msg: 7308 2000 Complete (success) 0 1423273449
      0207 02:44:09.082 [0x7f7cd3fff700] DEBUG root (build/qdisp/MessageStore.cc:49) - Msg: 7310 2000 Complete (success) 0 1423273449
      0207 02:44:09.088 [0x7f7cd3fff700] INFO  root (build/rproc/InfileMerger.cc:307) - Merging w/CREATE TABLE qservResult.result_4153079819 SELECT sce.filterName,sce.field,sce.camcol,sce.run,s.deepForcedSourceId,s.ra,s.decl,s.x,s.y,s.psfFlux,s.psfFluxSigma,s.apFlux,s.apFluxSigma,s.modelFlux,s.modelFluxSigma,s.instFlux,s.instFluxSigma,s.shapeIxx,s.shapeIyy,s.shapeIxy,s.flagPixInterpCen,s.flagNegative,s.flagPixEdge,s.flagBadCentroid,s.flagPixSaturCen,s.extendedness FROM qservResult.result_4153079819_m LIMIT 100
      0207 02:44:09.088 [0x7f7cd3fff700] ERROR root (build/rproc/InfileMerger.cc:359) - InfileMerger sql error: Error applying sql. Error 1054: Unknown column 'sce.filterName' in 'field list' Unable to execute query: CREATE TABLE qservResult.result_4153079819 SELECT sce.filterName,sce.field,sce.camcol,sce.run,s.deepForcedSourceId,s.ra,s.decl,s.x,s.y,s.psfFlux,s.psfFluxSigma,s.apFlux,s.apFluxSigma,s.modelFlux,s.modelFluxSigma,s.instFlux,s.instFluxSigma,s.shapeIxx,s.shapeIyy,s.shapeIxy,s.flagPixInterpCen,s.flagNegative,s.flagPixEdge,s.flagBadCentroid,s.flagPixSaturCen,s.extendedness FROM qservResult.result_4153079819_m LIMIT 100
       
      0207 02:44:09.088 [0x7f7cd3fff700] INFO  root (build/rproc/InfileMerger.cc:313) - Cleaning up qservResult.result_4153079819_m
      0207 02:44:09.089 [0x7f7cd3fff700] INFO  root (build/rproc/InfileMerger.cc:325) - Merged qservResult.result_4153079819_m into qservResult.result_4153079819
      0207 02:44:09.089 [0x7f7cd3fff700] INFO  root (build/ccontrol/UserQuery.cc:218) - Joined everything (success)
      0207 02:44:09.091 [0x7f7cd3fff700] INFO  root (app.py:569) - Query exec (5) took 0.092488 seconds
      0207 02:44:09.092 [0x7f7cd3fff700] INFO  root (app.py:574) - Final state of all queries success
      Runner running job
      0207 02:44:09.096 [0x7f7cd35fe700] INFO  root (build/ccontrol/UserQuery.cc:251) - Discarded UserQuery(5)

        Attachments

          Issue Links

            Activity

            Hide
            fritzm Fritz Mueller added a comment - - edited

            Apparently fixed in meantime with parser/column rework. The following query executes correctly on -int cluster at NCSA:

            SELECT s.id, s.coord_decl, s.coord_ra, sce.filterID, sce.filterName
            FROM sdss_stripe82_01.RunDeepForcedSource AS s, sdss_stripe82_01.Science_Ccd_Exposure AS sce 
            WHERE (s.exposure_id = sce.scienceCcdExposureId)
            LIMIT 100
            

            Show
            fritzm Fritz Mueller added a comment - - edited Apparently fixed in meantime with parser/column rework. The following query executes correctly on -int cluster at NCSA: SELECT s.id, s.coord_decl, s.coord_ra, sce.filterID, sce.filterName FROM sdss_stripe82_01.RunDeepForcedSource AS s, sdss_stripe82_01.Science_Ccd_Exposure AS sce WHERE (s.exposure_id = sce.scienceCcdExposureId) LIMIT 100

              People

              Assignee:
              Unassigned Unassigned
              Reporter:
              jammes Fabrice Jammes
              Watchers:
              Fritz Mueller
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Jenkins

                  No builds found.