Changeset 43014 for branches/eam_branches/ipp-pstamp-20260421/ippTools/share/difftool_definewarpstack_old.sql

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

File:

• branches/eam_branches/ipp-pstamp-20260421/ippTools/share/difftool_definewarpstack_old.sql (modified) (2 diffs)

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

@@ -1 +1 @@
 -- 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 +44 @@
 -- %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