Index: /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/addtool_checkminidvodbaddrun.sql =================================================================== --- /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/addtool_checkminidvodbaddrun.sql (revision 43013) +++ /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/addtool_checkminidvodbaddrun.sql (revision 43014) @@ -1,20 +1,40 @@ -- this selects the minidvodbs that have complete addRuns -SELECT minidvodb_id, minidvodb_name, minidvodb_path, state, minidbg -AS minidvodb_group, (cnt - cnt2) as not_full, cnt as addRun_count +SELECT + minidvodb_id, + minidvodb_name, + minidvodb_path, + state, + minidbg AS minidvodb_group, + (cnt - cnt2) as not_full, + cnt as addRun_count FROM minidvodbRun -JOIN - (SELECT minidvodb_name, count(state) as cnt2, cnt, minidbg, minidbn - FROM addRun LEFT JOIN - -- this select grabs (and counts) all the addRuns with any state - -- and groups them by the name - (SELECT minidvodb_group as minidbg, minidvodb_name as minidbn, count(state) - AS cnt - FROM addRun where minidvodb_group = '%s' - GROUP BY minidvodb_name ) - AS foo1 ON minidvodb_name = minidbn - -- the second select grabs and counts all the addRuns with state - -- of 'full' - WHERE addRun.state = 'full' and minidvodb_group = '%s' - GROUP BY minidvodb_name ) +JOIN ( + SELECT + minidvodb_name, + count(state) AS cnt2, + MIN(cnt) AS cnt, + MIN(minidbg) AS minidbg, + minidbn + FROM addRun + LEFT JOIN ( + -- this select grabs (and counts) all the addRuns with any state + -- and groups them by the name + SELECT + MIN(minidvodb_group) AS minidbg, + minidvodb_name AS minidbn, + count(state) AS cnt + FROM addRun + WHERE minidvodb_group = '%s' + GROUP BY minidvodb_name + ) + AS foo1 + ON minidvodb_name = minidbn + -- the outer select grabs and counts all the addRuns with state + -- of 'full' + WHERE addRun.state = 'full' + AND minidvodb_group = '%s' + GROUP BY + minidvodb_name +) AS foo2 USING(minidvodb_name) Index: /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/addtool_find_minidvodbrun2.sql =================================================================== --- /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/addtool_find_minidvodbrun2.sql (revision 43013) +++ /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/addtool_find_minidvodbrun2.sql (revision 43014) @@ -1,23 +1,37 @@ -- this selects the minidvodbs that have complete addRuns -SELECT minidvodb_name, minidvodb_path, state, minidbg -AS minidvodb_group +SELECT + minidvodb_name, + minidvodb_path, + state, + minidbg AS minidvodb_group FROM minidvodbRun -JOIN - (SELECT minidvodb_name, count(state) as cnt2, cnt, minidbg, minidbn - FROM addRun LEFT JOIN - -- this select grabs (and counts) all the addRuns with any state - -- and groups them by the name - (SELECT minidvodb_group as minidbg, minidvodb_name as minidbn, count(state) - AS cnt - FROM addRun - GROUP BY minidvodb_name ) - AS foo1 ON minidvodb_name = minidbn - -- the second select grabs and counts all the addRuns with state - -- of 'full' - WHERE addRun.state = 'full' - GROUP BY minidvodb_name ) +JOIN ( + SELECT + minidvodb_name, + count(state) AS cnt2, + MIN(cnt) AS cnt, + MIN(minidbg) as minidbg, + minidbn + FROM addRun + LEFT JOIN ( + -- this select grabs (and counts) all the addRuns with any state + -- and groups them by the name + SELECT + MIN(minidvodb_group) AS minidbg, + minidvodb_name AS minidbn, + count(state) AS cnt + FROM addRun + GROUP BY minidvodb_name + ) + AS foo1 + ON minidvodb_name = minidbn + -- the second select grabs and counts all the addRuns with state + -- of 'full' + WHERE addRun.state = 'full' + GROUP BY minidvodb_name +) AS foo2 USING(minidvodb_name) WHERE cnt2 = cnt - -- the 3rd select only grabs the columns where the counts are the same - -- (same number of 'full' and any state) +-- the 3rd select only grabs the columns where the counts are the same +-- (same number of 'full' and any state) Index: /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/bgtool_advancechip.sql =================================================================== --- /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/bgtool_advancechip.sql (revision 43013) +++ /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/bgtool_advancechip.sql (revision 43014) @@ -1,5 +1,5 @@ SELECT DISTINCT chipBackgroundRun.chip_bg_id, - chipBackgroundImfile.magicked + MIN(chipBackgroundImfile.magicked) AS magicked FROM chipBackgroundRun JOIN camRun using(cam_id) Index: /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/bgtool_advancewarp.sql =================================================================== --- /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/bgtool_advancewarp.sql (revision 43013) +++ /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/bgtool_advancewarp.sql (revision 43014) @@ -1,5 +1,5 @@ SELECT DISTINCT warpBackgroundRun.warp_bg_id, - warpBackgroundSkyfile.magicked + MIN(warpBackgroundSkyfile.magicked) AS magicked FROM warpBackgroundRun JOIN warpSkyfile USING(warp_id) Index: /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/bgtool_towarp.sql =================================================================== --- /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/bgtool_towarp.sql (revision 43013) +++ /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/bgtool_towarp.sql (revision 43014) @@ -4,5 +4,7 @@ skycell_id, rawExp.exp_tag, - rawExp.camera + rawExp.camera, + -- EAM: this was missing, I think this was a legitimate error + priority FROM warpBackgroundRun JOIN warpRun USING(warp_id) Index: /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/chiptool_coalesce_run.sql =================================================================== --- /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/chiptool_coalesce_run.sql (revision 43013) +++ /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/chiptool_coalesce_run.sql (revision 43014) @@ -1,1 +1,2 @@ -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 +-- 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 +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 = 1 Index: /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/chiptool_completely_processed_exp.sql =================================================================== --- /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/chiptool_completely_processed_exp.sql (revision 43013) +++ /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/chiptool_completely_processed_exp.sql (revision 43014) @@ -20,6 +20,11 @@ (SELECT chipRun.*, - rawImfile.class_id as rawimfile_class_id, - chipProcessedImfile.class_id, +-- rawImfile.class_id and chipProcessedImfile.class_id are not aggregated +-- and cause problems given the GROUP BY +-- clause below. A solution is to explicitly select a single value, the MIN. +-- But this is not needed because the field is not used by the rest of the query +-- rawImfile.class_id as rawimfile_class_id, +-- chipProcessedImfile.class_id, +-- MIN(rawImfile.class_id) as rawimfile_class_id, -- XXX using chipProcessedImfile assumes that all imfile's have -- the same magicked value if that isn't right then more than one @@ -28,5 +33,5 @@ -- We could use rawExp.magicked but that would make it possible for -- the chipRun to have a different magicked value than the imfiles - chipProcessedImfile.magicked AS imfile_magicked + MIN(chipProcessedImfile.magicked) AS imfile_magicked FROM chipRun JOIN rawImfile Index: /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/detselect_search.sql =================================================================== --- /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/detselect_search.sql (revision 43013) +++ /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/detselect_search.sql (revision 43014) @@ -7,5 +7,6 @@ det_id, good_iteration as iteration, - filelevel + filelevel, + registered FROM (SELECT DISTINCT Index: /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/dettool_find_completed_runs.sql =================================================================== --- /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/dettool_find_completed_runs.sql (revision 43013) +++ /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/dettool_find_completed_runs.sql (revision 43014) @@ -6,5 +6,5 @@ detRun.det_id, detRun.iteration, - detInputExp.exp_id + MIN(detInputExp.exp_id) AS exp_id FROM detRun LEFT JOIN detInputExp Index: /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/dettool_todetrunsummary.sql =================================================================== --- /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/dettool_todetrunsummary.sql (revision 43013) +++ /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/dettool_todetrunsummary.sql (revision 43014) @@ -22,6 +22,9 @@ detRun.mode, detRun.workdir, - detInputExp.exp_id, - rawExp.camera +-- detInputExp.exp_id, +-- this chooses a specific value +-- for camera. unclear if multiple +-- camera were used + MIN(rawExp.camera) as camera FROM detRun JOIN detInputExp Index: /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/dettool_tonormalizedexp.sql =================================================================== --- /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/dettool_tonormalizedexp.sql (revision 43013) +++ /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/dettool_tonormalizedexp.sql (revision 43014) @@ -1,9 +1,20 @@ -- this is used by both 'tonormalizedexp' and 'addnormalizedexp' + +-- EAM : I believe the outer SELECT * FROM () as Bar can be removed. SELECT * FROM (SELECT - * - FROM ( + det_id, + iteration, + MIN(det_type) as det_type, + MIN(workdir) as workdir, + MIN(camera) as camera, + MIN(telescope) as telescope, + MIN(exp_type) as exp_type, + MIN(norm_class_id) as norm_class_id, + MIN(fault) as fault, + MIN(stack_class_id) as stack_class_id + FROM ( SELECT DISTINCT detRun.det_id, Index: /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/dettool_tonormalizedstat.sql =================================================================== --- /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/dettool_tonormalizedstat.sql (revision 43013) +++ /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/dettool_tonormalizedstat.sql (revision 43014) @@ -6,5 +6,5 @@ detRun.camera, detRun.workdir, - detProcessedImfile.class_id, + MIN(detProcessedImfile.class_id) AS class_id, COUNT(detProcessedImfile.class_id), COUNT(detResidImfile.class_id) Index: /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/dettool_toprocessedexp.sql =================================================================== --- /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/dettool_toprocessedexp.sql (revision 43013) +++ /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/dettool_toprocessedexp.sql (revision 43014) @@ -27,7 +27,8 @@ detRun.det_type, detRun.workdir, - detProcessedImfile.exp_id, - detProcessedImfile.class_id, - rawImfile.class_id as rawimfile_class_id, + MIN(detProcessedImfile.exp_id) AS exp_id, + -- not actually used and cause aggregation errors + -- detProcessedImfile.class_id, + -- rawImfile.class_id as rawimfile_class_id, rawExp.camera, rawExp.exp_tag Index: /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/dettool_tostacked.sql =================================================================== --- /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/dettool_tostacked.sql (revision 43013) +++ /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/dettool_tostacked.sql (revision 43014) @@ -14,12 +14,17 @@ SELECT - detProcessedImfile.det_id, + -- why is this using detProcessedImfile.det_id and not detRun.det_id + -- MIN(detProcessedImfile.det_id) as det_id, + detRun.det_id, detRun.iteration, detRun.det_type, detRun.workdir, detRun.reduction, - detProcessedImfile.class_id, - rawExp.camera, - rawExp.exp_tag + -- detProcessedImfile.class_id could have NULLs given LEFT JOIN, + -- detProcessedImfile.class_id, + rawImfile.class_id, + MIN(rawExp.camera) AS camera + -- not needed in output? + -- rawExp.exp_tag FROM detRun JOIN detInputExp Index: /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/diffphottool_advance.sql =================================================================== --- /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/diffphottool_advance.sql (revision 43013) +++ /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/diffphottool_advance.sql (revision 43014) @@ -1,5 +1,5 @@ SELECT diff_phot_id, - diffPhotSkyfile.magicked + MIN(diffPhotSkyfile.magicked) AS magicked FROM diffPhotRun JOIN diffSkyfile USING(diff_id) Index: /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/difftool_completed_runs.sql =================================================================== --- /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/difftool_completed_runs.sql (revision 43013) +++ /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/difftool_completed_runs.sql (revision 43014) @@ -2,5 +2,6 @@ COUNT(diffInputSkyfile.skycell_id), COUNT(diffSkyfile.skycell_id), - diffSkyfile.*, + diffInputSkyfile.diff_id, + MIN(diffSkyfile.magicked) AS magicked, SUM(!diffSkyfile.magicked) = 0 as all_magicked FROM diffRun Index: /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/difftool_definewarpstack.sql =================================================================== --- /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/difftool_definewarpstack.sql (revision 43013) +++ /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/difftool_definewarpstack.sql (revision 43014) @@ -1,30 +1,35 @@ -- Get list of warp-stack pairs that can be diffed -- and check results against existing diffs +-- EAM: the output of this query should be limited to +-- only the fields need otherwise we have aggregation errors SELECT exp_id, warp_id, - filter, - warpLabel, +-- filter, +-- warpLabel, warpDataGroup, Inputs.tess_id, Inputs.skycell_id, - stack_id, - stackLabel, - stackDataGroup, - diff_id, - diffLabel + stack_id +-- these are NOT needed as an output +-- stackLabel, -- only used in the whereClauseString +-- stackDataGroup, +-- diff_id, +-- diffLabel FROM ( SELECT - exp_id, - warp_id, - rawExp.filter, - warpRun.label AS warpLabel, - warpRun.data_group AS warpDataGroup, - warpRun.tess_id, - warpSkyfile.skycell_id, - MAX(stack_id) AS stack_id, - stackRun.label AS stackLabel, - stackRun.data_group AS stackDataGroup + exp_id, -- KEEP + warp_id, -- KEEP + rawExp.filter, -- KEEP +-- warpRun.label AS warpLabel, -- DROP? + warpRun.data_group AS warpDataGroup, -- KEEP + warpRun.tess_id, -- KEEP + warpSkyfile.skycell_id, -- KEEP + MAX(stack_id) AS stack_id -- KEEP +-- these two fields are only used by the whereClauseString below +-- and do NOT need to be SELECTed +-- stackRun.label AS stackLabel, +-- stackRun.data_group AS stackDataGroup FROM warpRun JOIN fakeRun USING(fake_id) @@ -46,4 +51,5 @@ AND stackSumSkyfile.quality = 0 AND exp_id IS NOT NULL +-- replaced by whereClauseString -- %s GROUP BY exp_id,warp_id,skycell_id @@ -62,4 +68,5 @@ WHERE diff_mode = 2 +-- replaced by diffWhereClause -- %s ) AS Diffs ON @@ -70,4 +77,5 @@ WHERE 1 --- %s +-- replaced by joinWhereClause +-- %s Index: /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/difftool_definewarpstack_old.sql =================================================================== --- /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/difftool_definewarpstack_old.sql (revision 43013) +++ /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/difftool_definewarpstack_old.sql (revision 43014) @@ -1,17 +1,20 @@ -- Get list of warp-stack pairs that can be diffed -- and check results against existing diffs +-- EAM: the output of this query should be limited to +-- only the fields need otherwise we have aggregation errors SELECT - exp_id, - warp_id, - rawExp.filter, - warpRun.label AS warpLabel, - warpRun.data_group AS warpDataGroup, - warpRun.tess_id, - warpSkyfile.skycell_id, - MAX(stack_id) AS stack_id, - stackRun.label AS stackLabel, - stackRun.data_group AS stackDataGroup, - diff_id, - diffRun.label AS diffLabel + exp_id, + warp_id, +-- rawExp.filter, +-- warpRun.label AS warpLabel, + warpRun.data_group AS warpDataGroup, + warpRun.tess_id, + warpSkyfile.skycell_id, + MAX(stack_id) AS stack_id +-- these are NOT needed as an output +-- MIN(stackRun.label) AS stackLabel, +-- MIN(stackRun.data_group) AS stackDataGroup, +-- diffRun.diff_id, +-- diffRun.label AS diffLabel FROM warpRun JOIN fakeRun USING(fake_id) @@ -41,2 +44,5 @@ -- %s GROUP BY exp_id,warp_id,skycell_id + +-- the GROUP BY above, combined with the MAX(stack_id) selects the most recent stack_id +-- for a given exposure->warp->stack Index: /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/faketool_completely_processed_exp.sql =================================================================== --- /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/faketool_completely_processed_exp.sql (revision 43013) +++ /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/faketool_completely_processed_exp.sql (revision 43014) @@ -17,7 +17,9 @@ FROM (SELECT - fakeRun.*, - rawImfile.class_id as rawimfile_class_id, - fakeProcessedImfile.class_id + fakeRun.* +-- These two fields below are not required and cause +-- trouble with the GROUP BY below +-- rawImfile.class_id as rawimfile_class_id, +-- fakeProcessedImfile.class_id FROM fakeRun JOIN camRun Index: /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/fftool_toadvance.sql =================================================================== --- /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/fftool_toadvance.sql (revision 43013) +++ /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/fftool_toadvance.sql (revision 43014) @@ -4,5 +4,5 @@ stackRun.skycell_id, stackRun.filter, - rawExp.camera + MIN(rawExp.camera) AS camera FROM fullForceRun JOIN fullForceInput USING(ff_id) Index: /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/magictool_definebyquery_select_multidiff.sql =================================================================== --- /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/magictool_definebyquery_select_multidiff.sql (revision 43013) +++ /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/magictool_definebyquery_select_multidiff.sql (revision 43014) @@ -52,2 +52,4 @@ -- WHERE hook %s GROUP BY chipRun.exp_id + +-- EAM : magic is deprecated, I should not port this Index: /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/magictool_toprocess_runs.sql =================================================================== --- /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/magictool_toprocess_runs.sql (revision 43013) +++ /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/magictool_toprocess_runs.sql (revision 43014) @@ -1,4 +1,4 @@ SELECT DISTINCT - magic_id, + magicRun.magic_id, IFNULL(priority, 10000) AS priority FROM magicTree Index: /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/pstamptool_addaccesslevel.sql =================================================================== --- /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/pstamptool_addaccesslevel.sql (revision 43013) +++ /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/pstamptool_addaccesslevel.sql (revision 43014) @@ -1,4 +1,3 @@ -INSERT - INTO pstampAccessLevel +INSERT INTO pstampAccessLevel SELECT proj_id, %d, %f, %f FROM pstampProject WHERE name = '%s' Index: /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/pstamptool_pendingdependent.sql =================================================================== --- /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/pstamptool_pendingdependent.sql (revision 43013) +++ /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/pstamptool_pendingdependent.sql (revision 43014) @@ -1,8 +1,8 @@ SELECT DISTINCT pstampDependent.*, - pstampRequest.label, + MIN(pstampRequest.label) as label, MIN(req_id) as first_req_id, concat( pstampDependent.outdir, '/checkdep.', dep_id, '.log') as logfile, - IFNULL(Label.priority, 10000) AS priority + MIN(IFNULL(Label.priority, 10000)) AS priority FROM pstampDependent JOIN pstampJob USING(dep_id) @@ -13,2 +13,6 @@ AND pstampRequest.state = 'run' AND (Label.active OR Label.active IS NULL) +-- EAM TEST: +-- this is added in pstamptool, but is needed to test the SQL validity +-- GROUP BY dep_id ORDER BY priority DESC, MIN(req_id), dep_id + Index: /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/pztool_find_completed_exp.sql =================================================================== --- /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/pztool_find_completed_exp.sql (revision 43013) +++ /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/pztool_find_completed_exp.sql (revision 43014) @@ -8,5 +8,8 @@ FROM ( SELECT - pzDownloadImfile.*, + pzDownloadImfile.summit_id as summit_id, + MIN(pzDownloadImfile.exp_name) as exp_name, + MIN(pzDownloadImfile.camera) as camera, + MIN(pzDownloadImfile.telescope) as telescope, pzDownloadExp.state, summitExp.imfiles, Index: /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/regtool_pendingexp.sql =================================================================== --- /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/regtool_pendingexp.sql (revision 43013) +++ /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/regtool_pendingexp.sql (revision 43014) @@ -33,10 +33,11 @@ JOIN ( - select DISTINCT newExp.exp_id, - rawImfile.camera, - rawImfile.filter, - rawImfile.dateobs, - rawImfile.obs_mode, - rawImfile.obs_group, + select DISTINCT + MIN(newExp.exp_id) as exp_id, + MIN(rawImfile.camera) as camera, + MIN(rawImfile.filter) as filter, + MIN(rawImfile.dateobs) as dateobs, + MIN(rawImfile.obs_mode) as obs_mode, + MIN(rawImfile.obs_group) as obs_group, count(rawImfile.tmp_class_id) AS raw_count from newExp Index: /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/releasetool_definerelgroup_select_data_group.sql =================================================================== --- /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/releasetool_definerelgroup_select_data_group.sql (revision 43013) +++ /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/releasetool_definerelgroup_select_data_group.sql (revision 43014) @@ -1,4 +1,4 @@ SELECT - ippRelease.rel_id, + MIN(ippRelease.rel_id) AS rel_id, count(exp_id) AS num_exposures FROM ippRelease Index: /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/stacktool_tobkg.sql =================================================================== --- /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/stacktool_tobkg.sql (revision 43013) +++ /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/stacktool_tobkg.sql (revision 43014) @@ -8,5 +8,5 @@ stackRun.state, stackSumSkyfile.path_base, - rawExp.camera, + MIN(rawExp.camera) as camera, IFNULL(Label.priority, 10000) AS priority FROM stackRun Index: /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/stacktool_tosummary.sql =================================================================== --- /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/stacktool_tosummary.sql (revision 43013) +++ /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/stacktool_tosummary.sql (revision 43014) @@ -1,5 +1,16 @@ -SELECT sass_id,camera,workdir,tess_id,state FROM -(SELECT sass_id,rawExp.camera,stackRun.workdir,stackRun.tess_id,stackRun.state, - sum((stackRun.state = 'full')) AS is_done, sum(1) AS is_defined +SELECT + sass_id, + camera, + workdir, + tess_id, + state +FROM +(SELECT sass_id, + MIN(rawExp.camera) AS camera, + MIN(stackRun.workdir) AS workdir, + MIN(stackRun.tess_id) AS tess_id, + MIN(stackRun.state) AS state, + SUM((stackRun.state = 'full')) AS is_done, + SUM(1) AS is_defined FROM stackRun JOIN stackInputSkyfile ON stackRun.stack_id = stackInputSkyfile.stack_id Index: /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/staticskytool_definebyquery_select.sql =================================================================== --- /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/staticskytool_definebyquery_select.sql (revision 43013) +++ /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/staticskytool_definebyquery_select.sql (revision 43014) @@ -22,7 +22,7 @@ LEFT JOIN ( SELECT DISTINCT sky_id, - stackRun.skycell_id, - stackRun.tess_id, - stackRun.data_group, + MIN(stackRun.skycell_id) as skycell_id, + MIN(stackRun.tess_id) as tess_id, + MIN(stackRun.data_group) as data_group, COUNT(stack_id) AS num_filter FROM staticskyRun Index: /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/staticskytool_definebyquery_select_by_dg.sql =================================================================== --- /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/staticskytool_definebyquery_select_by_dg.sql (revision 43013) +++ /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/staticskytool_definebyquery_select_by_dg.sql (revision 43014) @@ -8,5 +8,5 @@ tess_id, skycell_id, - label, + MIN(label) as label, data_group, COUNT(DISTINCT filter) AS num_filter @@ -25,7 +25,7 @@ LEFT JOIN ( SELECT DISTINCT sky_id, - stackRun.skycell_id, - stackRun.tess_id, - stackRun.data_group, + MIN(stackRun.skycell_id) as skycell_id, + MIN(stackRun.tess_id) as tess_id, + MIN(stackRun.data_group) as data_group, COUNT(stack_id) AS num_filter FROM staticskyRun Index: /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/staticskytool_result.sql =================================================================== --- /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/staticskytool_result.sql (revision 43013) +++ /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/staticskytool_result.sql (revision 43014) @@ -1,15 +1,18 @@ SELECT - staticskyResult.*, - staticskyRun.state, - staticskyRun.workdir, - staticskyRun.label, - stackRun.tess_id, - stackRun.skycell_id, + -- EAM only return values needed by supporting code + -- this query is used in dist_advancerun.pl & permcheck.pl + -- needed fields: workdir, skycell_id, path_base + MIN(staticskyResult.path_base) AS path_base, + MIN(staticskyRun.state) AS state, + MIN(staticskyRun.workdir) AS workdir, + MIN(staticskyRun.label) AS label, + MIN(stackRun.tess_id) AS tess_id, + MIN(stackRun.skycell_id) AS skycell_id, count(stackRun.filter) AS num_filters, - TRUNCATE(skycell.radeg/15., 4) AS rahours, - skycell.radeg, - skycell.decdeg, - skycell.glong, - skycell.glat + TRUNCATE(MIN(skycell.radeg)/15., 4) AS rahours, + MIN(skycell.radeg) AS radeg, + MIN(skycell.decdeg) AS decdeg, + MIN(skycell.glong) AS glong, + MIN(skycell.glat) AS glat FROM staticskyRun JOIN staticskyResult USING(sky_id) Index: /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/staticskytool_todo.sql =================================================================== --- /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/staticskytool_todo.sql (revision 43013) +++ /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/staticskytool_todo.sql (revision 43014) @@ -5,11 +5,11 @@ staticskyRun.label, staticskyRun.state, - stackRun.tess_id, - stackRun.skycell_id, - TRUNCATE(skycell.radeg/15., 4) as rahours, - skycell.radeg, - skycell.decdeg, - skycell.glong, - skycell.glat, + MIN(stackRun.tess_id) as tess_id, + MIN(stackRun.skycell_id) as skycell_id, + TRUNCATE(MIN(skycell.radeg)/15., 4) as rahours, + MIN(skycell.radeg) AS radeg, + MIN(skycell.decdeg) AS decdeg, + MIN(skycell.glong) AS glong, + MIN(skycell.glat) AS glat, staticskyResult.path_base, IFNULL(Label.priority, 10000) AS priority Index: /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/warptool_finished_run_select.sql =================================================================== --- /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/warptool_finished_run_select.sql (revision 43013) +++ /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/warptool_finished_run_select.sql (revision 43014) @@ -6,7 +6,11 @@ warpRun.warp_id, warpRun.label as label, - warpSkyCellMap.warp_id as foo, - warpSkyfile.warp_id as bar, - warpSkyfile.magicked as magicked +-- these two fields are not used so we should not select for them +-- warpSkyCellMap.warp_id as foo, +-- warpSkyfile.warp_id as bar, +-- this field is not guaranteed to be unique in the set for a single warp_id +-- but the previous query version selected an arbitrary entry. select +-- the MIN to avoid trouble with the GROUP BY + MIN(warpSkyfile.magicked) as magicked FROM warpRun JOIN warpSkyCellMap Index: /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/warptool_towarped.sql =================================================================== --- /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/warptool_towarped.sql (revision 43013) +++ /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/warptool_towarped.sql (revision 43014) @@ -74,5 +74,5 @@ warpImfile.warp_skyfile_id, warpSkyCellMap.skycell_id, - warpSkyCellMap.tess_id, + MIN(warpSkyCellMap.tess_id) as tess_id, warpRun.fake_id, warpRun.state, @@ -84,5 +84,5 @@ warpRun.workdir, MIN(chipProcessedImfile.magicked) AS magicked, - warpSkyfile.path_base, + MIN(warpSkyfile.path_base) as past_base, IFNULL(Label.priority, 10000) AS priority FROM warpRun Index: /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/warptool_towarped_labels.sql =================================================================== --- /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/warptool_towarped_labels.sql (revision 43013) +++ /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/warptool_towarped_labels.sql (revision 43014) @@ -3,5 +3,5 @@ Label.label AS label_label, IFNULL(Label.priority, 10000) AS priority, - count(warpRun.warp_id) + count(warpRun.warp_id) AS N_warp FROM warpRun LEFT JOIN Label ON warpRun.label = Label.label @@ -11,3 +11,5 @@ -- where hook %s group by warpRun.label - ORDER BY priority DESC, warp_id + ORDER BY priority DESC, N_warp DESC +-- The old query had warp_id below which is nonsensical +-- ORDER BY priority DESC, warp_id Index: /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/xcskytool_definebyquery_select_by_dg.sql =================================================================== --- /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/xcskytool_definebyquery_select_by_dg.sql (revision 43013) +++ /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/xcskytool_definebyquery_select_by_dg.sql (revision 43014) @@ -8,5 +8,5 @@ tess_id, skycell_id, - label, + MIN(label) as label, data_group, COUNT(DISTINCT filter) AS num_filter @@ -25,7 +25,7 @@ LEFT JOIN ( SELECT DISTINCT xcsky_id, - xcstackRun.skycell_id, - xcstackRun.tess_id, - xcstackRun.data_group, + MIN(xcstackRun.skycell_id) as skycell_id, + MIN(xcstackRun.tess_id) as tess_id, + MIN(xcstackRun.data_group) as data_group, COUNT(xcstack_id) AS num_filter FROM xcskyRun Index: /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/xcskytool_result.sql =================================================================== --- /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/xcskytool_result.sql (revision 43013) +++ /branches/eam_branches/ipp-pstamp-20260421/ippTools/share/xcskytool_result.sql (revision 43014) @@ -1,7 +1,10 @@ SELECT - xcskyResult.*, - xcskyRun.state, - xcskyRun.workdir, - xcskyRun.label, + -- EAM only return values needed by supporting code + -- this query is used in dist_advancerun.pl & permcheck.pl + -- needed fields: workdir, skycell_id, path_base + MIN(xcskyResult.path_base) AS path_base, + MIN(xcskyRun.state) AS state, + MIN(xcskyRun.workdir) as workdir, + MIN(xcskyRun.label) as label, MIN(xcstackRun.tess_id) as tess_id, -- we implicitly assume that all xcstackRuns used for a xcskyRun have the same tess_id MIN(xcstackRun.skycell_id) as skycell_id, -- and skycell_id