IPP Software Navigation Tools IPP Links Communication Pan-STARRS Links

Changeset 43014


Ignore:
Timestamp:
May 11, 2026, 3:09:36 PM (2 months ago)
Author:
eugene
Message:

fix various SQL errors caught by more strict mysql versions, especially: force SQL to select a specific entry from non-aggregated fields when other fields are aggregated using e.g., GROUP BY

Location:
branches/eam_branches/ipp-pstamp-20260421/ippTools/share
Files:
38 edited

Legend:

Unmodified
Added
Removed

  • branches/eam_branches/ipp-pstamp-20260421/ippTools/share/addtool_checkminidvodbaddrun.sql

    r39528 r43014  
    11-- this selects the minidvodbs that have complete addRuns
    2 SELECT minidvodb_id, minidvodb_name, minidvodb_path, state, minidbg
    3 AS minidvodb_group, (cnt - cnt2) as not_full, cnt as addRun_count
     2SELECT
     3  minidvodb_id,
     4  minidvodb_name,
     5  minidvodb_path,
     6  state,
     7  minidbg AS minidvodb_group,
     8  (cnt - cnt2) as not_full,
     9  cnt as addRun_count
    410FROM minidvodbRun
    5 JOIN
    6    (SELECT minidvodb_name, count(state) as cnt2, cnt, minidbg, minidbn
    7    FROM addRun LEFT JOIN
    8        -- this select grabs (and counts) all the addRuns with any state
    9        -- and groups them by the name
    10        (SELECT minidvodb_group as minidbg, minidvodb_name as minidbn, count(state)
    11         AS cnt
    12         FROM addRun where minidvodb_group = '%s'
    13         GROUP BY minidvodb_name )
    14    AS foo1 ON minidvodb_name = minidbn
    15         -- the second select grabs and counts all the addRuns with state
    16         -- of 'full'         
    17    WHERE addRun.state = 'full' and minidvodb_group = '%s'
    18    GROUP BY minidvodb_name )
     11JOIN (
     12  SELECT
     13    minidvodb_name,
     14    count(state) AS cnt2,
     15    MIN(cnt)     AS cnt,
     16    MIN(minidbg) AS minidbg,
     17    minidbn
     18  FROM addRun
     19  LEFT JOIN (
     20    -- this select grabs (and counts) all the addRuns with any state
     21    -- and groups them by the name
     22    SELECT
     23      MIN(minidvodb_group) AS minidbg,
     24      minidvodb_name AS minidbn,
     25      count(state) AS cnt
     26    FROM addRun
     27    WHERE minidvodb_group = '%s'
     28    GROUP BY minidvodb_name
     29  )
     30  AS foo1
     31  ON minidvodb_name = minidbn
     32  -- the outer select grabs and counts all the addRuns with state
     33  -- of 'full'         
     34  WHERE addRun.state = 'full'
     35  AND minidvodb_group = '%s'
     36  GROUP BY
     37  minidvodb_name
     38)
    1939AS foo2
    2040USING(minidvodb_name)

  • branches/eam_branches/ipp-pstamp-20260421/ippTools/share/addtool_find_minidvodbrun2.sql

    r33030 r43014  
    11-- this selects the minidvodbs that have complete addRuns
    2 SELECT minidvodb_name, minidvodb_path, state, minidbg
    3 AS minidvodb_group
     2SELECT
     3  minidvodb_name,
     4  minidvodb_path,
     5  state,
     6  minidbg AS minidvodb_group
    47FROM minidvodbRun
    5 JOIN
    6    (SELECT minidvodb_name, count(state) as cnt2, cnt, minidbg, minidbn
    7    FROM addRun LEFT JOIN
    8        -- this select grabs (and counts) all the addRuns with any state
    9        -- and groups them by the name
    10        (SELECT minidvodb_group as minidbg, minidvodb_name as minidbn, count(state)
    11         AS cnt
    12         FROM addRun
    13         GROUP BY minidvodb_name )
    14    AS foo1 ON minidvodb_name = minidbn
    15         -- the second select grabs and counts all the addRuns with state
    16         -- of 'full'         
    17    WHERE addRun.state = 'full'
    18    GROUP BY minidvodb_name )
     8JOIN (
     9  SELECT
     10    minidvodb_name,
     11    count(state) AS cnt2,
     12    MIN(cnt)     AS cnt,
     13    MIN(minidbg) as minidbg,
     14    minidbn
     15  FROM addRun
     16  LEFT JOIN (
     17    -- this select grabs (and counts) all the addRuns with any state
     18    -- and groups them by the name
     19    SELECT
     20      MIN(minidvodb_group) AS minidbg,
     21      minidvodb_name AS minidbn,
     22      count(state) AS cnt
     23    FROM addRun
     24    GROUP BY minidvodb_name
     25  )
     26  AS foo1
     27  ON minidvodb_name = minidbn
     28  -- the second select grabs and counts all the addRuns with state
     29  -- of 'full'       
     30  WHERE addRun.state = 'full'
     31  GROUP BY minidvodb_name
     32)
    1933AS foo2
    2034USING(minidvodb_name)
    2135WHERE cnt2 = cnt
    22        -- the 3rd select only grabs the columns where the counts are the same
    23        -- (same number of 'full' and any state)
     36-- the 3rd select only grabs the columns where the counts are the same
     37-- (same number of 'full' and any state)

  • branches/eam_branches/ipp-pstamp-20260421/ippTools/share/bgtool_advancechip.sql

    r35672 r43014  
    11SELECT DISTINCT
    22    chipBackgroundRun.chip_bg_id,
    3     chipBackgroundImfile.magicked
     3    MIN(chipBackgroundImfile.magicked) AS magicked
    44FROM chipBackgroundRun
    55JOIN camRun using(cam_id)

  • branches/eam_branches/ipp-pstamp-20260421/ippTools/share/bgtool_advancewarp.sql

    r28486 r43014  
    11SELECT DISTINCT
    22    warpBackgroundRun.warp_bg_id,
    3     warpBackgroundSkyfile.magicked
     3    MIN(warpBackgroundSkyfile.magicked) AS magicked
    44FROM warpBackgroundRun
    55JOIN warpSkyfile USING(warp_id)

  • branches/eam_branches/ipp-pstamp-20260421/ippTools/share/bgtool_towarp.sql

    r35134 r43014  
    44    skycell_id,
    55    rawExp.exp_tag,
    6     rawExp.camera
     6    rawExp.camera,
     7    -- EAM: this was missing, I think this was a legitimate error
     8    priority
    79FROM warpBackgroundRun
    810JOIN warpRun USING(warp_id)

  • branches/eam_branches/ipp-pstamp-20260421/ippTools/share/chiptool_coalesce_run.sql

    r28043 r43014  
    1 select software_ver,maskfrac_npix,maskfrac_static,maskfrac_dynamic,maskfrac_magic,maskfrac_advisory from chipProcessedImfile where data_state = 'full' AND fault = 0 AND chip_id = %lld
     1-- select software_ver,maskfrac_npix,maskfrac_static,maskfrac_dynamic,maskfrac_magic,maskfrac_advisory from chipProcessedImfile where data_state = 'full' AND fault = 0 AND chip_id = %lld
     2select software_ver,maskfrac_npix,maskfrac_static,maskfrac_dynamic,maskfrac_magic,maskfrac_advisory from chipProcessedImfile where data_state = 'full' AND fault = 0 AND chip_id = 1

  • branches/eam_branches/ipp-pstamp-20260421/ippTools/share/chiptool_completely_processed_exp.sql

    r35789 r43014  
    2020    (SELECT
    2121        chipRun.*,
    22         rawImfile.class_id as rawimfile_class_id,
    23         chipProcessedImfile.class_id,
     22--      rawImfile.class_id and chipProcessedImfile.class_id are not aggregated
     23--      and cause problems given the GROUP BY
     24--      clause below.  A solution is to explicitly select a single value, the MIN.
     25--      But this is not needed because the field is not used by the rest of the query
     26--      rawImfile.class_id as rawimfile_class_id,
     27--      chipProcessedImfile.class_id,
     28--      MIN(rawImfile.class_id) as rawimfile_class_id,
    2429        -- XXX using chipProcessedImfile assumes that all imfile's have
    2530        -- the same magicked value if that isn't right then more than one
    … …  
    2833        -- We could use rawExp.magicked but that would make it possible for
    2934        -- the chipRun to have a different magicked value than the imfiles
    30         chipProcessedImfile.magicked AS imfile_magicked
     35        MIN(chipProcessedImfile.magicked) AS imfile_magicked
    3136    FROM chipRun
    3237    JOIN rawImfile

  • branches/eam_branches/ipp-pstamp-20260421/ippTools/share/detselect_search.sql

    r27797 r43014  
    77    det_id,
    88    good_iteration as iteration,
    9     filelevel
     9    filelevel,
     10    registered
    1011FROM
    1112    (SELECT DISTINCT

  • branches/eam_branches/ipp-pstamp-20260421/ippTools/share/dettool_find_completed_runs.sql

    r14023 r43014  
    66        detRun.det_id,
    77        detRun.iteration,
    8         detInputExp.exp_id
     8        MIN(detInputExp.exp_id) AS exp_id
    99    FROM detRun
    1010        LEFT JOIN detInputExp

  • branches/eam_branches/ipp-pstamp-20260421/ippTools/share/dettool_todetrunsummary.sql

    r23122 r43014  
    2222        detRun.mode,
    2323        detRun.workdir,
    24         detInputExp.exp_id,
    25         rawExp.camera
     24--      detInputExp.exp_id,
     25--      this chooses a specific value
     26--      for camera.  unclear if multiple
     27--      camera were used
     28        MIN(rawExp.camera) as camera
    2629    FROM detRun
    2730    JOIN detInputExp

  • branches/eam_branches/ipp-pstamp-20260421/ippTools/share/dettool_tonormalizedexp.sql

    r18561 r43014  
    11-- this is used by both 'tonormalizedexp' and 'addnormalizedexp'
     2
     3-- EAM : I believe the outer SELECT * FROM () as Bar can be removed.
    24
    35SELECT
    46    *
    57FROM (SELECT
    6       *
    7     FROM (
     8   det_id,
     9   iteration,
     10   MIN(det_type) as det_type,
     11   MIN(workdir) as workdir,
     12   MIN(camera) as camera,
     13   MIN(telescope) as telescope,
     14   MIN(exp_type) as exp_type,
     15   MIN(norm_class_id) as norm_class_id,
     16   MIN(fault) as fault,
     17   MIN(stack_class_id) as stack_class_id
     18  FROM (
    819        SELECT DISTINCT
    920            detRun.det_id,

  • branches/eam_branches/ipp-pstamp-20260421/ippTools/share/dettool_tonormalizedstat.sql

    r40535 r43014  
    66  detRun.camera,
    77  detRun.workdir,
    8   detProcessedImfile.class_id,
     8  MIN(detProcessedImfile.class_id) AS class_id,
    99  COUNT(detProcessedImfile.class_id),
    1010  COUNT(detResidImfile.class_id)

  • branches/eam_branches/ipp-pstamp-20260421/ippTools/share/dettool_toprocessedexp.sql

    r19621 r43014  
    2727        detRun.det_type,
    2828        detRun.workdir,
    29         detProcessedImfile.exp_id,
    30         detProcessedImfile.class_id,
    31         rawImfile.class_id as rawimfile_class_id,
     29        MIN(detProcessedImfile.exp_id) AS exp_id,
     30        -- not actually used and cause aggregation errors
     31        -- detProcessedImfile.class_id,
     32        -- rawImfile.class_id as rawimfile_class_id,
    3233        rawExp.camera,
    3334        rawExp.exp_tag

  • branches/eam_branches/ipp-pstamp-20260421/ippTools/share/dettool_tostacked.sql

    r15769 r43014  
    1414
    1515SELECT
    16     detProcessedImfile.det_id,
     16    -- why is this using detProcessedImfile.det_id and not detRun.det_id
     17    -- MIN(detProcessedImfile.det_id) as det_id,
     18    detRun.det_id,
    1719    detRun.iteration,
    1820    detRun.det_type,
    1921    detRun.workdir,
    2022    detRun.reduction,
    21     detProcessedImfile.class_id,
    22     rawExp.camera,
    23     rawExp.exp_tag
     23    -- detProcessedImfile.class_id could have NULLs given LEFT JOIN,
     24    -- detProcessedImfile.class_id,
     25    rawImfile.class_id,
     26    MIN(rawExp.camera) AS camera
     27    -- not needed in output?
     28    -- rawExp.exp_tag
    2429FROM detRun
    2530JOIN detInputExp

  • branches/eam_branches/ipp-pstamp-20260421/ippTools/share/diffphottool_advance.sql

    r28488 r43014  
    11SELECT
    22    diff_phot_id,
    3     diffPhotSkyfile.magicked
     3    MIN(diffPhotSkyfile.magicked) AS magicked
    44FROM diffPhotRun
    55JOIN diffSkyfile USING(diff_id)

  • branches/eam_branches/ipp-pstamp-20260421/ippTools/share/difftool_completed_runs.sql

    r25515 r43014  
    22    COUNT(diffInputSkyfile.skycell_id),
    33    COUNT(diffSkyfile.skycell_id),
    4     diffSkyfile.*,
     4    diffInputSkyfile.diff_id,
     5    MIN(diffSkyfile.magicked) AS magicked,
    56    SUM(!diffSkyfile.magicked) = 0 as all_magicked
    67FROM diffRun

  • branches/eam_branches/ipp-pstamp-20260421/ippTools/share/difftool_definewarpstack.sql

    r37974 r43014  
    11-- Get list of warp-stack pairs that can be diffed
    22-- and check results against existing diffs
     3-- EAM: the output of this query should be limited to
     4-- only the fields need otherwise we have aggregation errors
    35SELECT
    46   exp_id,
    57   warp_id,
    6    filter,
    7    warpLabel,
     8--   filter,
     9--   warpLabel,
    810   warpDataGroup,
    911   Inputs.tess_id,
    1012   Inputs.skycell_id,
    11    stack_id,
    12    stackLabel,
    13    stackDataGroup,
    14    diff_id,
    15    diffLabel
     13   stack_id
     14-- these are NOT needed as an output
     15--   stackLabel, -- only used in the whereClauseString
     16--   stackDataGroup,
     17--   diff_id,
     18--   diffLabel
    1619FROM
    1720(
    1821SELECT
    19    exp_id,
    20    warp_id,
    21    rawExp.filter,
    22    warpRun.label AS warpLabel,
    23    warpRun.data_group AS warpDataGroup,
    24    warpRun.tess_id,
    25    warpSkyfile.skycell_id,
    26    MAX(stack_id) AS stack_id,
    27    stackRun.label AS stackLabel,
    28    stackRun.data_group AS stackDataGroup
     22   exp_id, -- KEEP
     23   warp_id, -- KEEP
     24   rawExp.filter, -- KEEP
     25--   warpRun.label AS warpLabel,  -- DROP?
     26   warpRun.data_group AS warpDataGroup, -- KEEP
     27   warpRun.tess_id, -- KEEP
     28   warpSkyfile.skycell_id, -- KEEP
     29   MAX(stack_id) AS stack_id -- KEEP
     30--   these two fields are only used by the whereClauseString below
     31--   and do NOT need to be SELECTed
     32--   stackRun.label AS stackLabel,
     33--   stackRun.data_group AS stackDataGroup
    2934FROM warpRun
    3035     JOIN fakeRun USING(fake_id)
    … …  
    4651     AND stackSumSkyfile.quality = 0
    4752     AND exp_id IS NOT NULL
     53-- replaced by whereClauseString
    4854-- %s
    4955GROUP BY exp_id,warp_id,skycell_id
    … …  
    6268WHERE
    6369diff_mode = 2
     70-- replaced by diffWhereClause
    6471-- %s
    6572) AS Diffs ON
    … …  
    7077WHERE
    71781
    72 -- %s
     79-- replaced by joinWhereClause
     80-- %s
    7381

  • branches/eam_branches/ipp-pstamp-20260421/ippTools/share/difftool_definewarpstack_old.sql

    r40109 r43014  
    11-- Get list of warp-stack pairs that can be diffed
    22-- and check results against existing diffs
     3-- EAM: the output of this query should be limited to
     4-- only the fields need otherwise we have aggregation errors
    35SELECT
    4    exp_id,
    5    warp_id,
    6    rawExp.filter,
    7    warpRun.label AS warpLabel,
    8    warpRun.data_group AS warpDataGroup,
    9    warpRun.tess_id,
    10    warpSkyfile.skycell_id,
    11    MAX(stack_id) AS stack_id,
    12    stackRun.label AS stackLabel,
    13    stackRun.data_group AS stackDataGroup,
    14    diff_id,
    15    diffRun.label AS diffLabel
     6  exp_id,
     7  warp_id,
     8--   rawExp.filter,
     9--   warpRun.label AS warpLabel,
     10  warpRun.data_group AS warpDataGroup,
     11  warpRun.tess_id,
     12  warpSkyfile.skycell_id,
     13  MAX(stack_id) AS stack_id
     14-- these are NOT needed as an output
     15--   MIN(stackRun.label) AS stackLabel,
     16--   MIN(stackRun.data_group) AS stackDataGroup,
     17--   diffRun.diff_id,
     18--   diffRun.label AS diffLabel
    1619FROM warpRun
    1720     JOIN fakeRun USING(fake_id)
    … …  
    4144-- %s
    4245GROUP BY exp_id,warp_id,skycell_id
     46
     47-- the GROUP BY above, combined with the MAX(stack_id) selects the most recent stack_id
     48-- for a given exposure->warp->stack

  • branches/eam_branches/ipp-pstamp-20260421/ippTools/share/faketool_completely_processed_exp.sql

    r25835 r43014  
    1717FROM
    1818    (SELECT
    19         fakeRun.*,
    20         rawImfile.class_id as rawimfile_class_id,
    21         fakeProcessedImfile.class_id
     19        fakeRun.*
     20--      These two fields below are not required and cause
     21--      trouble with the GROUP BY below
     22--      rawImfile.class_id as rawimfile_class_id,
     23--      fakeProcessedImfile.class_id
    2224    FROM fakeRun
    2325    JOIN camRun

  • branches/eam_branches/ipp-pstamp-20260421/ippTools/share/fftool_toadvance.sql

    r36633 r43014  
    44    stackRun.skycell_id,
    55    stackRun.filter,
    6     rawExp.camera
     6    MIN(rawExp.camera) AS camera
    77FROM fullForceRun
    88    JOIN fullForceInput USING(ff_id)

  • branches/eam_branches/ipp-pstamp-20260421/ippTools/share/magictool_definebyquery_select_multidiff.sql

    r32283 r43014  
    5252-- WHERE hook %s
    5353GROUP BY chipRun.exp_id
     54
     55-- EAM : magic is deprecated, I should not port this

  • branches/eam_branches/ipp-pstamp-20260421/ippTools/share/magictool_toprocess_runs.sql

    r28768 r43014  
    11SELECT DISTINCT
    2     magic_id,
     2    magicRun.magic_id,
    33    IFNULL(priority, 10000) AS priority
    44FROM magicTree

  • branches/eam_branches/ipp-pstamp-20260421/ippTools/share/pstamptool_addaccesslevel.sql

    r38157 r43014  
    1 INSERT
    2     INTO pstampAccessLevel
     1INSERT INTO pstampAccessLevel
    32        SELECT proj_id, %d, %f, %f FROM pstampProject
    43        WHERE name = '%s'

  • branches/eam_branches/ipp-pstamp-20260421/ippTools/share/pstamptool_pendingdependent.sql

    r38586 r43014  
    11SELECT DISTINCT
    22    pstampDependent.*,
    3     pstampRequest.label,
     3    MIN(pstampRequest.label) as label,
    44    MIN(req_id) as first_req_id,
    55    concat( pstampDependent.outdir, '/checkdep.', dep_id, '.log') as logfile,
    6     IFNULL(Label.priority, 10000) AS priority
     6    MIN(IFNULL(Label.priority, 10000)) AS priority
    77FROM pstampDependent
    88JOIN pstampJob USING(dep_id)
    … …  
    1313    AND pstampRequest.state = 'run'
    1414    AND (Label.active OR Label.active IS NULL)
     15-- EAM TEST:
     16-- this is added in pstamptool, but is needed to test the SQL validity
     17-- GROUP BY dep_id ORDER BY priority DESC, MIN(req_id), dep_id
     18

  • branches/eam_branches/ipp-pstamp-20260421/ippTools/share/pztool_find_completed_exp.sql

    r31047 r43014  
    88FROM (
    99    SELECT
    10         pzDownloadImfile.*,
     10        pzDownloadImfile.summit_id as summit_id,
     11        MIN(pzDownloadImfile.exp_name) as exp_name,
     12        MIN(pzDownloadImfile.camera) as camera,
     13        MIN(pzDownloadImfile.telescope) as telescope,
    1114        pzDownloadExp.state,
    1215        summitExp.imfiles,

  • branches/eam_branches/ipp-pstamp-20260421/ippTools/share/regtool_pendingexp.sql

    r31043 r43014  
    3333         JOIN
    3434         (
    35           select DISTINCT newExp.exp_id,
    36           rawImfile.camera,
    37           rawImfile.filter,
    38           rawImfile.dateobs,
    39           rawImfile.obs_mode,
    40           rawImfile.obs_group,
     35          select DISTINCT
     36          MIN(newExp.exp_id) as exp_id,
     37          MIN(rawImfile.camera) as camera,
     38          MIN(rawImfile.filter) as filter,
     39          MIN(rawImfile.dateobs) as dateobs,
     40          MIN(rawImfile.obs_mode) as obs_mode,
     41          MIN(rawImfile.obs_group) as obs_group,
    4142          count(rawImfile.tmp_class_id) AS raw_count
    4243          from newExp

  • branches/eam_branches/ipp-pstamp-20260421/ippTools/share/releasetool_definerelgroup_select_data_group.sql

    r35213 r43014  
    11SELECT
    2     ippRelease.rel_id,
     2    MIN(ippRelease.rel_id) AS rel_id,
    33    count(exp_id) AS num_exposures
    44FROM ippRelease

  • branches/eam_branches/ipp-pstamp-20260421/ippTools/share/stacktool_tobkg.sql

    r35081 r43014  
    88    stackRun.state,
    99    stackSumSkyfile.path_base,
    10     rawExp.camera,
     10    MIN(rawExp.camera) as camera,
    1111    IFNULL(Label.priority, 10000) AS priority
    1212FROM stackRun

  • branches/eam_branches/ipp-pstamp-20260421/ippTools/share/stacktool_tosummary.sql

    r35856 r43014  
    1 SELECT sass_id,camera,workdir,tess_id,state FROM
    2 (SELECT sass_id,rawExp.camera,stackRun.workdir,stackRun.tess_id,stackRun.state,
    3        sum((stackRun.state = 'full')) AS is_done, sum(1) AS is_defined
     1SELECT
     2  sass_id,
     3  camera,
     4  workdir,
     5  tess_id,
     6  state
     7FROM
     8(SELECT sass_id,
     9        MIN(rawExp.camera)    AS camera,
     10        MIN(stackRun.workdir) AS workdir,
     11        MIN(stackRun.tess_id) AS tess_id,
     12        MIN(stackRun.state)   AS state,
     13        SUM((stackRun.state = 'full')) AS is_done,
     14        SUM(1) AS is_defined
    415       FROM stackRun
    516       JOIN stackInputSkyfile ON stackRun.stack_id = stackInputSkyfile.stack_id

  • branches/eam_branches/ipp-pstamp-20260421/ippTools/share/staticskytool_definebyquery_select.sql

    r33919 r43014  
    2222LEFT JOIN (
    2323  SELECT DISTINCT sky_id,
    24     stackRun.skycell_id,
    25     stackRun.tess_id,
    26     stackRun.data_group,
     24    MIN(stackRun.skycell_id) as skycell_id,
     25    MIN(stackRun.tess_id) as tess_id,
     26    MIN(stackRun.data_group) as data_group,
    2727    COUNT(stack_id) AS num_filter
    2828  FROM staticskyRun

  • branches/eam_branches/ipp-pstamp-20260421/ippTools/share/staticskytool_definebyquery_select_by_dg.sql

    r31687 r43014  
    88      tess_id,
    99      skycell_id,
    10       label,
     10      MIN(label) as label,
    1111      data_group,
    1212      COUNT(DISTINCT filter) AS num_filter
    … …  
    2525LEFT JOIN (
    2626  SELECT DISTINCT sky_id,
    27     stackRun.skycell_id,
    28     stackRun.tess_id,
    29     stackRun.data_group,
     27    MIN(stackRun.skycell_id) as skycell_id,
     28    MIN(stackRun.tess_id) as tess_id,
     29    MIN(stackRun.data_group) as data_group,
    3030    COUNT(stack_id) AS num_filter
    3131  FROM staticskyRun

  • branches/eam_branches/ipp-pstamp-20260421/ippTools/share/staticskytool_result.sql

    r34719 r43014  
    11SELECT
    2     staticskyResult.*,
    3     staticskyRun.state,
    4     staticskyRun.workdir,
    5     staticskyRun.label,
    6     stackRun.tess_id,
    7     stackRun.skycell_id,
     2    -- EAM only return values needed by supporting code
     3    -- this query is used in dist_advancerun.pl & permcheck.pl
     4    -- needed fields: workdir, skycell_id, path_base
     5    MIN(staticskyResult.path_base) AS path_base,
     6    MIN(staticskyRun.state) AS state,
     7    MIN(staticskyRun.workdir) AS workdir,
     8    MIN(staticskyRun.label) AS label,
     9    MIN(stackRun.tess_id) AS tess_id,
     10    MIN(stackRun.skycell_id) AS skycell_id,
    811    count(stackRun.filter) AS num_filters,
    9     TRUNCATE(skycell.radeg/15., 4) AS rahours,
    10     skycell.radeg,
    11     skycell.decdeg,
    12     skycell.glong,
    13     skycell.glat
     12    TRUNCATE(MIN(skycell.radeg)/15., 4) AS rahours,
     13    MIN(skycell.radeg) AS radeg,
     14    MIN(skycell.decdeg) AS decdeg,
     15    MIN(skycell.glong) AS glong,
     16    MIN(skycell.glat) AS glat
    1417FROM staticskyRun
    1518JOIN staticskyResult USING(sky_id)

  • branches/eam_branches/ipp-pstamp-20260421/ippTools/share/staticskytool_todo.sql

    r38296 r43014  
    55    staticskyRun.label,
    66    staticskyRun.state,
    7     stackRun.tess_id,
    8     stackRun.skycell_id,
    9     TRUNCATE(skycell.radeg/15., 4) as rahours,
    10     skycell.radeg,
    11     skycell.decdeg,
    12     skycell.glong,
    13     skycell.glat,
     7    MIN(stackRun.tess_id) as tess_id,
     8    MIN(stackRun.skycell_id) as skycell_id,
     9    TRUNCATE(MIN(skycell.radeg)/15., 4) as rahours,
     10    MIN(skycell.radeg) AS radeg,
     11    MIN(skycell.decdeg) AS decdeg,
     12    MIN(skycell.glong) AS glong,
     13    MIN(skycell.glat) AS glat, 
    1414    staticskyResult.path_base,
    1515    IFNULL(Label.priority, 10000) AS priority

  • branches/eam_branches/ipp-pstamp-20260421/ippTools/share/warptool_finished_run_select.sql

    r26567 r43014  
    66       warpRun.warp_id,
    77       warpRun.label as label,
    8        warpSkyCellMap.warp_id as foo,
    9        warpSkyfile.warp_id as bar,
    10        warpSkyfile.magicked as magicked
     8--     these two fields are not used so we should not select for them
     9--     warpSkyCellMap.warp_id as foo,
     10--     warpSkyfile.warp_id as bar,
     11--     this field is not guaranteed to be unique in the set for a single warp_id
     12--     but the previous query version selected an arbitrary entry.  select
     13--     the MIN to avoid trouble with the GROUP BY
     14       MIN(warpSkyfile.magicked) as magicked
    1115   FROM warpRun
    1216   JOIN warpSkyCellMap

  • branches/eam_branches/ipp-pstamp-20260421/ippTools/share/warptool_towarped.sql

    r36529 r43014  
    7474        warpImfile.warp_skyfile_id,
    7575        warpSkyCellMap.skycell_id,
    76         warpSkyCellMap.tess_id,
     76        MIN(warpSkyCellMap.tess_id) as tess_id,
    7777        warpRun.fake_id,
    7878        warpRun.state,
    … …  
    8484        warpRun.workdir,
    8585        MIN(chipProcessedImfile.magicked) AS magicked,
    86         warpSkyfile.path_base,
     86        MIN(warpSkyfile.path_base) as past_base,
    8787        IFNULL(Label.priority, 10000) AS priority
    8888    FROM warpRun

  • branches/eam_branches/ipp-pstamp-20260421/ippTools/share/warptool_towarped_labels.sql

    r29888 r43014  
    33        Label.label AS label_label,
    44        IFNULL(Label.priority, 10000) AS priority,
    5         count(warpRun.warp_id)
     5        count(warpRun.warp_id) AS N_warp
    66    FROM warpRun
    77    LEFT JOIN Label ON warpRun.label = Label.label
    … …  
    1111        -- where hook %s
    1212        group by warpRun.label
    13         ORDER BY priority DESC, warp_id
     13        ORDER BY priority DESC, N_warp DESC
     14--      The old query had warp_id below which is nonsensical
     15--      ORDER BY priority DESC, warp_id

  • branches/eam_branches/ipp-pstamp-20260421/ippTools/share/xcskytool_definebyquery_select_by_dg.sql

    r42837 r43014  
    88      tess_id,
    99      skycell_id,
    10       label,
     10      MIN(label) as label,
    1111      data_group,
    1212      COUNT(DISTINCT filter) AS num_filter
    … …  
    2525LEFT JOIN (
    2626  SELECT DISTINCT xcsky_id,
    27     xcstackRun.skycell_id,
    28     xcstackRun.tess_id,
    29     xcstackRun.data_group,
     27    MIN(xcstackRun.skycell_id) as skycell_id,
     28    MIN(xcstackRun.tess_id) as tess_id,
     29    MIN(xcstackRun.data_group) as data_group,
    3030    COUNT(xcstack_id) AS num_filter
    3131  FROM xcskyRun

  • branches/eam_branches/ipp-pstamp-20260421/ippTools/share/xcskytool_result.sql

    r42837 r43014  
    11SELECT
    2     xcskyResult.*,
    3     xcskyRun.state,
    4     xcskyRun.workdir,
    5     xcskyRun.label,
     2    -- EAM only return values needed by supporting code
     3    -- this query is used in dist_advancerun.pl & permcheck.pl
     4    -- needed fields: workdir, skycell_id, path_base
     5    MIN(xcskyResult.path_base) AS path_base,
     6    MIN(xcskyRun.state) AS state,
     7    MIN(xcskyRun.workdir) as workdir,
     8    MIN(xcskyRun.label) as label,
    69    MIN(xcstackRun.tess_id) as tess_id, -- we implicitly assume that all xcstackRuns used for a xcskyRun have the same tess_id
    710    MIN(xcstackRun.skycell_id) as skycell_id, -- and skycell_id
Note: See TracChangeset for help on using the changeset viewer.