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

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

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