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

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

@@ -1 +1 @@
 -- 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)