Metabase 0.47 - Error with models

[matenek]

I updated metabase 0.47 and got bugs as a gift. Something to do with calculations when using models.

Diagnostic Info:

{
  "browser-info": {
    "language": "en-GB",
    "platform": "MacIntel",
    "userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/116.0.0.0 Safari/537.36",
    "vendor": "Google Inc."
  },
  "system-info": {
    "file.encoding": "UTF-8",
    "java.runtime.name": "OpenJDK Runtime Environment",
    "java.runtime.version": "11.0.20+8-post-Ubuntu-1ubuntu122.04",
    "java.vendor": "Ubuntu",
    "java.vendor.url": "https://ubuntu.com/",
    "java.version": "11.0.20",
    "java.vm.name": "OpenJDK 64-Bit Server VM",
    "java.vm.version": "11.0.20+8-post-Ubuntu-1ubuntu122.04",
    "os.name": "Linux",
    "os.version": "5.15.0-79-generic",
    "user.language": "en",
    "user.timezone": "Asia/Dili"
  },
  "metabase-info": {
    "databases": [
      "postgres"
    ],
    "hosting-env": "unknown",
    "application-database": "postgres",
    "application-database-details": {
      "database": {
        "name": "PostgreSQL",
        "version": "14.9 (Ubuntu 14.9-0ubuntu0.22.04.1)"
      },
      "jdbc-driver": {
        "name": "PostgreSQL JDBC Driver",
        "version": "42.5.4"
      }
    },
    "run-mode": "prod",
    "version": {
      "date": "2023-08-16",
      "tag": "v0.47.0",
      "branch": "release-x.47.x",
      "hash": "682ec2d"
    },
    "settings": {
      "report-timezone": null
    }
  }
}

[Luiggi]

can you give us some details about what are those fields and where they come from?

also, are those tables or models?

[matenek]

hi @Luiggi
I merged two different tables via sql(UNION) and turned them into a model. I also tested it as a question.
error occurs:

• if custom column - aggregation show me errors
• if I don't use aggregation + I use column, it works.

I can't use both in the same question.
The same problem in my local instance.

WITH ida_data AS (
    SELECT
        'ida' AS source,
        submission_id::TEXT,
        z3.district_id,
        z3.district_name,
        z2.subdistrict_id,
        z2.subdistrict_name,
        z.suco_id,
        z.suco_name,
        attendance_male,
        attendance_female,
        SUM(kpa_male) AS total_kpa_male,
        SUM(kpa_female) AS total_kpa_female,
        SUM(Disable_Male) AS total_disable_male,
        SUM(Disable_Female) AS total_disable_female,
        SUM(Community_Member_Male) AS total_community_member_male,
        SUM(Community_Member_Female) AS total_community_member_female,
        MAX(activity_type) AS program_activity_type_id,
        MAX(activity_subtype) AS program_activity_id,
        0 AS Suco_cycleid,
        0 AS cycle_id,
        0 AS phase_id,
        0 AS suco_phase
    FROM
        ida_forms_sf_1_1 ifs
        LEFT JOIN zsuco z ON z.suco_id = ifs.suco_id
        LEFT JOIN zsubdistrict z2 ON z2.subdistrict_id = z.subdistrict_id
        LEFT JOIN zdistrict z3 ON z2.district_id = z3.district_id
        LEFT JOIN zaldeia z4 ON ifs.Suco_ID = z4.suco_id
    GROUP BY
        submission_id::TEXT,
        z3.district_id,
        z3.district_name,
        z2.subdistrict_id,
        z2.subdistrict_name,
        z.suco_id,
        z.suco_name,
        attendance_male,
        attendance_female
),

legacy_data AS (
    SELECT
        'legacy' AS source,
        Sa.id::TEXT,
        zd.district_id,
        zd.district_name,
        zs.subdistrict_id,
        zs.subdistrict_name,
        zsuco.suco_id,
        zsuco.suco_name,
        Sa.attendance_male AS male,
        Sa.attendance_female AS female,
        SUM(Sa.kpa_male) AS total_kpa_male,
        SUM(Sa.kpa_female) AS total_kpa_female,
        SUM(Sa.disable_male) AS total_disable_male,
        SUM(Sa.disable_female) AS total_disable_female,
        SUM(Sa.community_member_male) AS total_community_member_male,
        SUM(Sa.community_member_female) AS total_community_member_female,
        MAX(zpat.program_activtiy_type_id) AS program_activity_type_id,
        MAX(zpa.program_activity_id) AS program_activity_id,
        Suco_Cycles.Suco_CycleID AS suco_cycleid,
        zcycles.cycle_id AS cycle_id,
        zsuco_phase.id AS phase_id,
        zsuco.suco_phase
    FROM
    zsuco
    JOIN zaldeia z ON z.Suco_ID = zsuco.Suco_ID
    JOIN Suco_Cycles ON zsuco.Suco_ID = Suco_Cycles.Suco_ID
    JOIN Zcycles ON Suco_Cycles.cycleid = Zcycles.cycle_id
    JOIN Suco_Actvities Sa ON Suco_Cycles.Suco_CycleID = Sa.Suco_CycleID
    JOIN zSubdistrict zs ON zsuco.SubDistrict_ID = zs.Subdistrict_ID
    JOIN zDistrict zd ON zd.District_ID = zs.District_ID
    JOIN zprogram_activity zpa ON zpa.program_activity_id = Sa.zproject_activity_id
    JOIN zprogram_activty_type zpat ON zpat.program_activtiy_type_id = zpa.zprogram_activity_type_id
    JOIN zsuco_phase ON zsuco.suco_phase = zsuco_phase.id
    GROUP BY
        Sa.id::TEXT,
        zsuco.suco_id,
        zd.district_id,
        zd.district_name,
        zs.subdistrict_id,
        zs.subdistrict_name,
        zpat.program_activtiy_type_id,
        zpa.program_activity_id,
        Suco_Cycles.Suco_CycleID,
        Sa.attendance_male,
        Sa.attendance_female,
        zcycles.cycle_id,
        zsuco_phase.id
        
)

SELECT * FROM ida_data
UNION ALL
SELECT * FROM legacy_data;