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

          relates to

          Bug - A problem which impairs or prevents the functions of the product. DM-1974 Fix enclose, escape, and line termination characters in qserv-data-loader

          • Done
          mentioned in

          Page Loading...

            Activity

            Hide
            Permalink
            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.