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

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

@@ -20 +20 @@
     (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 +33 @@
         -- 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