wiki:dbPipeQAQueries
Last modified 6 years ago Last modified on 04/05/2013 04:33:25 PM

Db Queries Run by PipeQA

LSST Database

Future changes

The query set will change as we add new data sets (e.g. DiaSources). In the longer term new queries will be added that get full lightcurves of objects across visits.

2013/01/14 (original version)

These queries were provided by Andy B. on 2013/01/14

SHOW COLUMNS FROM Source;

SELECT distinct run, field 
FROM   Science_Ccd_Exposure
WHERE  (run = 5924) AND (field = 390);

SELECT sce.filterName, sce.field, sce.camcol, sce.run 
FROM   Science_Ccd_Exposure AS sce
WHERE  (sce.filterName like '%') 
   AND (sce.field = 390)
   AND (sce.camcol like '%')
   AND (sce.run = 5924);

###### note, filterName is selected twice, is that needed???
###### see #2758, it is confusing qserv
SELECT sce.scienceCcdExposureId, sce.filterName, sce.field, sce.camcol, sce.run,
       sce.filterId, sce.filterName, sce.ra, sce.decl, sce.crpix1, sce.crpix2, 
       sce.crval1, sce.crval2, sce.cd1_1, sce.cd1_2, sce.cd2_1, sce.cd2_2, 
       sce.fluxMag0, sce.fluxMag0Sigma, sce.fwhm  
FROM   Science_Ccd_Exposure AS sce
WHERE  (sce.filterName = 'g')
   AND (sce.field = 390) 
   AND (sce.camcol = 1)
   AND (sce.run = 5924);

SELECT sce.filterName, sce.field, sce.camcol, sce.run, s.sourceId, 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   Source AS s,
       Science_Ccd_Exposure AS sce
WHERE  (s.scienceCcdExposureId = sce.scienceCcdExposureId)
   AND (sce.filterName = 'g')
   AND (sce.field = 390)
   AND (sce.camcol = 1)
   AND (sce.run = 5924);

SHOW COLUMNS FROM RefObject;

SELECT sce.filterName, sce.field, sce.camcol, sce.run  
FROM   Science_Ccd_Exposure AS sce
WHERE  (sce.filterName = 'g')
   AND (sce.field = 390)
   AND (sce.camcol = 1)
   AND (sce.run = 5924);

SELECT scisql_s2CPolyToBin(sce.corner1Ra, sce.corner1Decl, sce.corner2Ra, sce.corner2Decl,
                           sce.corner3Ra, sce.corner3Decl, sce.corner4Ra, sce.corner4Decl) 
FROM   Science_Ccd_Exposure AS sce
WHERE  (sce.filterName = 'g')
   AND (sce.field = 390)
   AND (sce.camcol = 1)
   AND (sce.run = 5924) INTO @poly;

SELECT sro.refObjectId, sro.isStar, sro.ra, sro.decl, sro.uMag, sro.gMag, 
       sro.rMag, sro.iMag, sro.zMag 
FROM   RefObject AS sro 
WHERE  (scisql_s2PtInCPoly(sro.ra, sro.decl, @poly) = 1);

SELECT sce.filterId, sce.filterName 
FROM   Science_Ccd_Exposure AS sce 
WHERE  (sce.filterName = 'g')
   AND (sce.field = 390)
   AND (sce.camcol = 1)
   AND (sce.run = 5924);

SELECT sce.filterName, sce.field, sce.camcol, sce.run, sro.gMag, sro.ra, sro.decl,
       sro.isStar, sro.refObjectId, s.sourceId,  rom.nSrcMatches,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   Source AS s, Science_Ccd_Exposure AS sce use index(),
       RefSrcMatch AS rom,
       RefObject AS sro 
WHERE  (s.scienceCcdExposureId = sce.scienceCcdExposureId)
   AND (s.sourceId = rom.sourceId)
   AND (rom.refObjectId = sro.refObjectId)
   AND (sce.filterName = 'g')
   AND (sce.field = 390)
   AND (sce.camcol = 1) 
   AND (sce.run = 5924);

SHOW COLUMNS FROM RefObject;

##### Coadd photometry:
##### Command : $TESTING_PIPEQA_DIR/bin/pipeQa.py --camera coadd -b ccd -T "deep" 
#####           -v "3-g" -c "24,1" -S /nfs/lsst7/krughoff/debugCoadd_deep2 
#####           -z none krughoff_early_prod_test


SELECT distinct tract,patch,filterName
FROM DeepCoadd;

SHOW COLUMNS FROM DeepSource;

SELECT distinct tract, patch, filterName 
FROM   DeepCoadd
WHERE  (tract = 3) 
   AND (patch = '24,1')
   AND (filterName = 'g');

SELECT sce.filterName, sce.tract, sce.patch
FROM   DeepCoadd AS sce
WHERE  (sce.filterName = 'g')
   AND (sce.tract = 3)
   AND (sce.patch = '24,1');

SELECT sce.DeepCoaddId, sce.filterName, sce.tract, sce.patch, sce.filterId, 
       sce.filterName, sce.ra, sce.decl, sce.crpix1, sce.crpix2, sce.crval1, 
       sce.crval2, sce.cd1_1, sce.cd1_2, sce.cd2_1, sce.cd2_2, sce.fluxMag0, 
       sce.fluxMag0Sigma, sce.measuredFwhm  
FROM   DeepCoadd AS sce
WHERE  (sce.filterName = 'g')
   AND (sce.tract = 3)
   AND (sce.patch = '24,1');

SELECT sce.filterName, sce.tract, sce.patch, s.deepSourceId, 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   DeepSource AS s, 
       DeepCoadd AS sce
WHERE  (s.deepCoaddId = sce.deepCoaddId)
   AND (sce.filterName = 'g')
   AND (sce.tract = 3)
   AND (sce.patch = '24,1');

SHOW COLUMNS FROM RefObject;

SELECT sce.filterName, sce.tract, sce.patch
FROM   DeepCoadd AS sce
WHERE  (sce.filterName = 'g')
   AND (sce.tract = 3)
   AND (sce.patch = '24,1');

SELECT scisql_s2CPolyToBin(sce.corner1Ra, sce.corner1Decl, sce.corner2Ra, sce.corner2Decl,
                           sce.corner3Ra, sce.corner3Decl, sce.corner4Ra, sce.corner4Decl)
FROM   DeepCoadd AS sce
WHERE  (sce.filterName = 'g')
   AND (sce.tract = 3)
   AND (sce.patch = '24,1') INTO @poly;

SELECT sro.refObjectId, sro.isStar, sro.ra, sro.decl, sro.uMag, sro.gMag, 
       sro.rMag, sro.iMag, sro.zMag
FROM   RefObject AS sro
WHERE  (scisql_s2PtInCPoly(sro.ra, sro.decl, @poly) = 1);

SELECT sce.filterId, sce.filterName
FROM   DeepCoadd AS sce
WHERE  (sce.filterName = 'g') 
   AND (sce.tract = 3)
   AND (sce.patch = '24,1');

SELECT sce.filterName, sce.tract, sce.patch, sro.gMag, sro.ra, sro.decl, sro.isStar,
       sro.refObjectId, s.deepSourceId,  rom.nSrcMatches,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   DeepSource AS s, 
       DeepCoadd AS sce,
       RefDeepSrcMatch AS rom,
       RefObject AS sro
WHERE  (s.deepCoaddId = sce.deepCoaddId)
   AND (s.deepSourceId = rom.deepSourceId)
   AND (rom.refObjectId = sro.refObjectId)
   AND (sce.filterName = 'g')
   AND (sce.tract = 3) AND (sce.patch = '24,1');

SHOW COLUMNS FROM RefObject;

##### Forced photometry:
##### Command: $TESTING_PIPEQA_DIR/bin/pipeQa.py --camera sdss -b ccd -F -T "deep" 
#####          -v 5924-390 -z none krughoff_early_prod_test

SHOW COLUMNS FROM DeepForcedSource;

SELECT distinct run, field
FROM   Science_Ccd_Exposure
WHERE  (run = 5924)
   AND (field = 390);

SELECT sce.filterName, sce.field, sce.camcol, sce.run
FROM   Science_Ccd_Exposure AS sce 
WHERE  (sce.filterName like '%')
   AND (sce.field = 390)
   AND (sce.camcol like '%')
   AND (sce.run = 5924);

SELECT sce.scienceCcdExposureId, sce.filterName, sce.field, sce.camcol, sce.run,
       sce.filterId, sce.filterName, sce.ra, sce.decl, sce.crpix1, sce.crpix2, 
       sce.crval1, sce.crval2, sce.cd1_1, sce.cd1_2, sce.cd2_1, sce.cd2_2, 
       sce.fluxMag0, sce.fluxMag0Sigma, sce.fwhm
FROM   Science_Ccd_Exposure AS sce
WHERE  (sce.filterName = 'g')
   AND (sce.field = 390)
   AND (sce.camcol = 1)
   AND (sce.run = 5924);

SELECT sce.filterName, sce.field, sce.camcol, sce.run, s.deepSourceId, 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)
   AND (sce.filterName = 'g')
   AND (sce.field = 390)
   AND (sce.camcol = 1)
   AND (sce.run = 5924);

SHOW COLUMNS FROM RefObject;

SELECT sce.filterName, sce.field, sce.camcol, sce.run
FROM   Science_Ccd_Exposure AS sce
WHERE  (sce.filterName = 'g')
   AND (sce.field = 390)
   AND (sce.camcol = 1)
   AND (sce.run = 5924);

SELECT scisql_s2CPolyToBin(sce.corner1Ra, sce.corner1Decl, sce.corner2Ra, sce.corner2Decl,
                           sce.corner3Ra, sce.corner3Decl, sce.corner4Ra, sce.corner4Decl) 
FROM   Science_Ccd_Exposure AS sce
WHERE  (sce.filterName = 'g')
   AND (sce.field = 390)
   AND (sce.camcol = 1)
   AND (sce.run = 5924) INTO @poly;

SELECT sro.refObjectId, sro.isStar, sro.ra, sro.decl, sro.uMag, 
       sro.gMag, sro.rMag, sro.iMag, sro.zMag
FROM   RefObject AS sro
WHERE  (scisql_s2PtInCPoly(sro.ra, sro.decl, @poly) = 1)

SELECT sce.filterId, sce.filterName
FROM   Science_Ccd_Exposure AS sce
WHERE  (sce.filterName = 'g')
   AND (sce.field = 390)
   AND (sce.camcol = 1)
   AND (sce.run = 5924);

SELECT sce.filterName, sce.field, sce.camcol, sce.run, sro.gMag, sro.ra, sro.decl,
       sro.isStar, sro.refObjectId, s.deepSourceId,  rom.nSrcMatches,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 use index(),
       RefDeepSrcMatch AS rom,
       RefObject AS sro
WHERE  (s.scienceCcdExposureId = sce.scienceCcdExposureId)
   AND (s.deepSourceId = rom.deepSourceId)
   AND (rom.refObjectId = sro.refObjectId)
   AND (sce.filterName = 'g')
   AND (sce.field = 390)
   AND (sce.camcol = 1)
   AND (sce.run = 5924);

SHOW COLUMNS FROM RefObject;

2014/03/20 (modified for W13)

And here is how it looks like if we try to run them on Winter13 data set:

# not supported
SHOW COLUMNS FROM Source;

# OK
SELECT distinct run, field 
FROM   Science_Ccd_Exposure
WHERE  (run = 94) AND (field = 536);

# OK
SELECT sce.filterName, sce.field, sce.camcol, sce.run 
FROM   Science_Ccd_Exposure AS sce
WHERE  (sce.filterName like '%') 
   AND (sce.field = 536)
   AND (sce.camcol like '%')
   AND (sce.run = 94);

# Original query was selecting sce.filterName which confuses
# qserv. Removing second occurance helps. Related ticket 2758
SELECT sce.scienceCcdExposureId, sce.filterName, sce.field, sce.camcol, sce.run,
       sce.filterId, sce.ra, sce.decl, sce.crpix1, sce.crpix2, 
       sce.crval1, sce.crval2, sce.cd1_1, sce.cd1_2, sce.cd2_1, sce.cd2_2, 
       sce.fluxMag0, sce.fluxMag0Sigma, sce.fwhm  
FROM   Science_Ccd_Exposure AS sce
WHERE  (sce.filterName = 'g')
   AND (sce.field = 536) 
   AND (sce.camcol = 1)
   AND (sce.run = 94);

# OK, but Source table does not exist in W13, used DeepForcedSource 
# with Science_Ccd_Exposure table, tbd what to do
SELECT sce.filterName, sce.field, sce.camcol, sce.run, s.deepSourceId, 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)
   AND (sce.filterName = 'g')
   AND (sce.field = 536)
   AND (sce.camcol = 1)
   AND (sce.run = 94);

# not supported
SHOW COLUMNS FROM RefObject;

# OK
SELECT sce.filterName, sce.field, sce.camcol, sce.run  
FROM   Science_Ccd_Exposure AS sce
WHERE  (sce.filterName = 'g')
   AND (sce.field = 536)
   AND (sce.camcol = 1)
   AND (sce.run = 94);

# Qserv has problem with syntax @poly
# Related ticket 2056
SELECT scisql_s2CPolyToBin(sce.corner1Ra, sce.corner1Decl, sce.corner2Ra, sce.corner2Decl,
                           sce.corner3Ra, sce.corner3Decl, sce.corner4Ra, sce.corner4Decl) 
FROM   Science_Ccd_Exposure AS sce
WHERE  (sce.filterName = 'g')
   AND (sce.field = 536)
   AND (sce.camcol = 1)
   AND (sce.run = 94) INTO @poly;

SELECT sro.refObjectId, sro.isStar, sro.ra, sro.decl, sro.uMag, sro.gMag, 
       sro.rMag, sro.iMag, sro.zMag 
FROM   RefObject AS sro 
WHERE  (scisql_s2PtInCPoly(sro.ra, sro.decl, @poly) = 1);

### the above should be rewritten as:

    SELECT sce.corner1Ra, sce.corner1Decl,
           sce.corner2Ra, sce.corner2Decl,
           sce.corner3Ra, sce.corner3Decl,
           sce.corner4Ra, sce.corner4Decl)
    FROM   DeepCoadd AS sce
    WHERE  (sce.filterName = 'r')
       AND (sce.tract = 0)
       AND (sce.patch = '159,3');

    # Then use the 8 values in the following statement:

    SELECT sro.refObjectId, sro.isStar, sro.ra, sro.decl, 
           sro.uMag, sro.gMag, sro.rMag, sro.iMag, sro.zMag
    FROM   RefObject AS sro
    WHERE  qserv_areaspec_poly(sro.ra, sro.decl, <ra1>, <decl1>, <ra2>, <decl2>, ...);

# OK
SELECT sce.filterId, sce.filterName 
FROM   Science_Ccd_Exposure AS sce 
WHERE  (sce.filterName = 'g')
   AND (sce.field = 536)
   AND (sce.camcol = 1)
   AND (sce.run = 94);

# Source table does not exist in W13, used DeepForcedSource, and RefDeepSrcMatch
# Had to remove "use index()"
# Can not select ra or decl twice"
# the query below (issues describe above fixed) works OK
SELECT sce.filterName, sce.field, sce.camcol, sce.run, sro.gMag, sro.ra, sro.decl,
       sro.isStar, sro.refObjectId, s.deepSourceId,  rom.nSrcMatches,
       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,
       RefDeepSrcMatch AS rom,
       RefObject AS sro
WHERE  (s.scienceCcdExposureId = sce.scienceCcdExposureId)
   AND (s.deepSourceId = rom.deepSourceId)
   AND (rom.refObjectId = sro.refObjectId)
   AND (sce.filterName = 'g')
   AND (sce.field = 536)
   AND (sce.camcol = 1)
   AND (sce.run = 94);

# not supported
SHOW COLUMNS FROM RefObject;

##### Coadd photometry:
##### Command : $TESTING_PIPEQA_DIR/bin/pipeQa.py --camera coadd -b ccd -T "deep" 
#####           -v "3-g" -c "24,1" -S /nfs/lsst7/krughoff/debugCoadd_deep2 
#####           -z none krughoff_early_prod_test

# OK
SELECT distinct tract,patch,filterName
FROM DeepCoadd;

# Not supported
SHOW COLUMNS FROM DeepSource;

# OK
SELECT distinct tract, patch, filterName 
FROM   DeepCoadd
WHERE  (tract = 0) 
   AND (patch = '159,3')
   AND (filterName = 'r');

# OK
SELECT sce.filterName, sce.tract, sce.patch
FROM   DeepCoadd AS sce
WHERE  (sce.filterName = 'r')
   AND (sce.tract = 0)
   AND (sce.patch = '159,3');

# Original query was selecting sce.filterName which confuses
# qserv. Removing second occurance helps. Related ticket 2758
SELECT sce.DeepCoaddId, sce.filterName, sce.tract, sce.patch, sce.filterId, 
       sce.ra, sce.decl, sce.crpix1, sce.crpix2, sce.crval1, 
       sce.crval2, sce.cd1_1, sce.cd1_2, sce.cd2_1, sce.cd2_2, sce.fluxMag0, 
       sce.fluxMag0Sigma, sce.measuredFwhm  
FROM   DeepCoadd AS sce
WHERE  (sce.filterName = 'r')
   AND (sce.tract = 0)
   AND (sce.patch = '159,3');

# OK
SELECT sce.filterName, sce.tract, sce.patch, s.deepSourceId, 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   DeepSource AS s, 
       DeepCoadd AS sce
WHERE  (s.deepCoaddId = sce.deepCoaddId)
   AND (sce.filterName = 'r')
   AND (sce.tract = 0)
   AND (sce.patch = '159,3');

# Not supported
SHOW COLUMNS FROM RefObject;

# OK
SELECT sce.filterName, sce.tract, sce.patch
FROM   DeepCoadd AS sce
WHERE  (sce.filterName = 'r')
   AND (sce.tract = 0)
   AND (sce.patch = '159,3');

# Qserv has problem with syntax @poly
# Related ticket 2056
SELECT scisql_s2CPolyToBin(sce.corner1Ra, sce.corner1Decl, sce.corner2Ra, sce.corner2Decl,
                           sce.corner3Ra, sce.corner3Decl, sce.corner4Ra, sce.corner4Decl)
FROM   DeepCoadd AS sce
WHERE  (sce.filterName = 'r')
   AND (sce.tract = 0)
   AND (sce.patch = '159,3') INTO @poly;

# See above
SELECT sro.refObjectId, sro.isStar, sro.ra, sro.decl, sro.uMag, sro.gMag, 
       sro.rMag, sro.iMag, sro.zMag
FROM   RefObject AS sro
WHERE  (scisql_s2PtInCPoly(sro.ra, sro.decl, @poly) = 1);

# OK
SELECT sce.filterId, sce.filterName
FROM   DeepCoadd AS sce
WHERE  (sce.filterName = 'r') 
   AND (sce.tract = 0)
   AND (sce.patch = '159,3');

# We have an issue here, because of "select sro.ra, s.ra"
# Qserv gets confused if column with the same name appears
# in the SELECT. Related ticket 2758
SELECT sce.filterName, sce.tract, sce.patch, sro.gMag, sro.ra, sro.decl, sro.isStar,
       sro.refObjectId, s.deepSourceId,  rom.nSrcMatches,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   DeepSource AS s, 
       DeepCoadd AS sce,
       RefDeepSrcMatch AS rom,
       RefObject AS sro
WHERE  (s.deepCoaddId = sce.deepCoaddId)
   AND (s.deepSourceId = rom.deepSourceId)
   AND (rom.refObjectId = sro.refObjectId)
   AND (sce.filterName = 'r')
   AND (sce.tract = 0) AND (sce.patch = '159,3');

# Not supported
SHOW COLUMNS FROM RefObject;

##### Forced photometry:
##### Command: $TESTING_PIPEQA_DIR/bin/pipeQa.py --camera sdss -b ccd -F -T "deep" 
#####          -v 94-536 -z none krughoff_early_prod_test

# Not supported
SHOW COLUMNS FROM DeepForcedSource;

# OK
SELECT distinct run, field
FROM   Science_Ccd_Exposure
WHERE  (run = 94)
   AND (field = 536);

# OK
SELECT sce.filterName, sce.field, sce.camcol, sce.run
FROM   Science_Ccd_Exposure AS sce 
WHERE  (sce.filterName like '%')
   AND (sce.field = 536)
   AND (sce.camcol like '%')
   AND (sce.run = 94);

# Original query was selecting sce.filterName which confuses
# qserv. Removing second occurance helps. Related ticket 2758
SELECT sce.scienceCcdExposureId, sce.filterName, sce.field, sce.camcol, sce.run,
       sce.filterId, sce.ra, sce.decl, sce.crpix1, sce.crpix2, 
       sce.crval1, sce.crval2, sce.cd1_1, sce.cd1_2, sce.cd2_1, sce.cd2_2, 
       sce.fluxMag0, sce.fluxMag0Sigma, sce.fwhm
FROM   Science_Ccd_Exposure AS sce
WHERE  (sce.filterName = 'g')
   AND (sce.field = 536)
   AND (sce.camcol = 1)
   AND (sce.run = 94);

# OK
SELECT sce.filterName, sce.field, sce.camcol, sce.run, s.deepSourceId, 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)
   AND (sce.filterName = 'g')
   AND (sce.field = 536)
   AND (sce.camcol = 1)
   AND (sce.run = 94);

# Not supported
SHOW COLUMNS FROM RefObject;

# OK
SELECT sce.filterName, sce.field, sce.camcol, sce.run
FROM   Science_Ccd_Exposure AS sce
WHERE  (sce.filterName = 'g')
   AND (sce.field = 536)
   AND (sce.camcol = 1)
   AND (sce.run = 94);

# Qserv has problem with syntax @poly
# Related ticket 2056
SELECT scisql_s2CPolyToBin(sce.corner1Ra, sce.corner1Decl, sce.corner2Ra, sce.corner2Decl,
                           sce.corner3Ra, sce.corner3Decl, sce.corner4Ra, sce.corner4Decl) 
FROM   Science_Ccd_Exposure AS sce
WHERE  (sce.filterName = 'g')
   AND (sce.field = 536)
   AND (sce.camcol = 1)
   AND (sce.run = 94) INTO @poly;

# See above
SELECT sro.refObjectId, sro.isStar, sro.ra, sro.decl, sro.uMag, 
       sro.gMag, sro.rMag, sro.iMag, sro.zMag
FROM   RefObject AS sro
WHERE  (scisql_s2PtInCPoly(sro.ra, sro.decl, @poly) = 1)

# OK
SELECT sce.filterId, sce.filterName
FROM   Science_Ccd_Exposure AS sce
WHERE  (sce.filterName = 'g')
   AND (sce.field = 536)
   AND (sce.camcol = 1)
   AND (sce.run = 94);

# "use index()" is confusing qserv.
SELECT sce.filterName, sce.field, sce.camcol, sce.run, sro.gMag, sro.ra, sro.decl,
       sro.isStar, sro.refObjectId, s.deepSourceId,  rom.nSrcMatches,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 use index(),
       RefDeepSrcMatch AS rom,
       RefObject AS sro
WHERE  (s.scienceCcdExposureId = sce.scienceCcdExposureId)
   AND (s.deepSourceId = rom.deepSourceId)
   AND (rom.refObjectId = sro.refObjectId)
   AND (sce.filterName = 'g')
   AND (sce.field = 536)
   AND (sce.camcol = 1)
   AND (sce.run = 94);

# Not supported
SHOW COLUMNS FROM RefObject;

Summary of issues discovered on W13 data set

  • "SHOW" syntax not supported. Related ticket #2750
  • Qserv gets confused if column with the same name appears in the SELECT, even if it is from different tables, eg "SELECT sro.ra, s.ra". Same failure if the same colum is selected twice from the same table (not sure why this is needed though). Workaround: alias the column names. Related ticket #2758
  • Qserv has problem with syntax @poly. Related ticket #2056
  • "use index()" is confusing qserv. Workaround: don't use, it is an optimization that won't matter anyway if we use qserv.