Changeset 43014 for branches/eam_branches/ipp-pstamp-20260421/ippTools/share/difftool_definewarpstack.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.sql (modified) (4 diffs)

branches/eam_branches/ipp-pstamp-20260421/ippTools/share/difftool_definewarpstack.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,
-   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 +51 @@
      AND stackSumSkyfile.quality = 0
      AND exp_id IS NOT NULL
+-- replaced by whereClauseString
 -- %s
 GROUP BY exp_id,warp_id,skycell_id
@@ -62 +68 @@
 WHERE
 diff_mode = 2
+-- replaced by diffWhereClause
 -- %s 
 ) AS Diffs ON
@@ -70 +77 @@
 WHERE
 1
--- %s
+-- replaced by joinWhereClause
+-- %s