Nested query with custom column return incorrect results

Bug reports
1

Original nested query without custom column

SELECT
source.event_year AS event_year,
source.count AS count,
Question 193.event_year AS Question 193__event_year,
Question 193.count AS count_2

FROM (SELECT
all_results_events_view.event_year AS event_year,
count() AS count FROM all_results_events_view
WHERE ((lower(all_results_events_view.event_name) like '%3m%')
AND (NOT (lower(all_results_events_view.chip_time) like '%dn%')
OR all_results_events_view.chip_time IS NULL))
GROUP BY all_results_events_view.event_year
ORDER BY all_results_events_view.event_year ASC) source
INNER JOIN
(SELECT all_registrations_events_view.event_year AS event_year,
count() AS count FROM all_registrations_events_view
WHERE ((lower(all_registrations_events_view.event_name) like '%3m%')
AND (NOT (lower(all_registrations_events_view.activity_name) like '%captain%')
OR all_registrations_events_view.activity_name IS NULL))
GROUP BY all_registrations_events_view.event_year) Question 193

ON source.event_year = Question 193.event_year
LIMIT 1048575

Original nested query with a custom column that returns the incorrect results

SELECT
source.event_year AS event_year,
source.count AS count,
source.finish rate AS finish rate,
source.Question 193__event_year AS Question 193__event_year,
source.count AS count_2
FROM (SELECT
source.event_year AS event_year,
source.count AS count,
((source.count / CASE WHEN Question 193.count = 0 THEN NULL ELSE Question 193.count END) 100) AS finish rate,
Question 193.event_year AS Question 193__event_year,
Question 193.count AS count_2
FROM (SELECT all_results_events_view.event_year AS event_year,
count() AS count FROM all_results_events_view
WHERE ((lower(all_results_events_view.event_name) like '%3m%')
AND (NOT (lower(all_results_events_view.chip_time) like '%dn%')
OR all_results_events_view.chip_time IS NULL))
GROUP BY all_results_events_view.event_year
ORDER BY all_results_events_view.event_year ASC) source
INNER JOIN
(SELECT
all_registrations_events_view.event_year AS event_year,
count() AS count
FROM all_registrations_events_view
WHERE ((lower(all_registrations_events_view.event_name) like '%3m%')
AND (NOT (lower(all_registrations_events_view.activity_name) like '%captain%')
OR all_registrations_events_view.activity_name IS NULL))
GROUP BY all_registrations_events_view.event_year) Question 193

ON source.event_year = Question 193.event_year) source
LIMIT 1048575

"Fixed"(in bold) nested query with the custom column

SELECT
source.event_year AS event_year,
source.count AS count,
source.finish rate AS finish rate,
source.Question 193__event_year AS Question 193__event_year,
source.count_2 AS count_2
FROM (SELECT
source.event_year AS event_year,
source.count AS count,
((source.count / CASE WHEN Question 193.count = 0 THEN NULL ELSE Question 193.count END) 100) AS finish rate,
Question 193.event_year AS Question 193__event_year,
Question 193.count AS count_2
FROM (SELECT all_results_events_view.event_year AS event_year,
count() AS count FROM all_results_events_view
WHERE ((lower(all_results_events_view.event_name) like '%3m%')
AND (NOT (lower(all_results_events_view.chip_time) like '%dn%')
OR all_results_events_view.chip_time IS NULL))
GROUP BY all_results_events_view.event_year
ORDER BY all_results_events_view.event_year ASC) source
INNER JOIN
(SELECT
all_registrations_events_view.event_year AS event_year,
count() AS count
FROM all_registrations_events_view
WHERE ((lower(all_registrations_events_view.event_name) like '%3m%')
AND (NOT (lower(all_registrations_events_view.activity_name) like '%captain%')
OR all_registrations_events_view.activity_name IS NULL))
GROUP BY all_registrations_events_view.event_year) Question 193

ON source.event_year = Question 193.event_year) source
LIMIT 1048575

2

Hi @johnc
Post "Diagnostic Info" from Admin > Troubleshooting.
I'm guessing you're seeing this issue:
https://github.com/metabase/metabase/issues/18086 - upvote by clicking :+1: on the first post

3

That does seem like the issue.

Here is the diag info

{
"browser-info": {
"language": "en-US",
"platform": "Win32",
"userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/96.0.4664.93 Safari/537.36",
"vendor": "Google Inc."
},
"system-info": {
"file.encoding": "UTF-8",
"java.runtime.name": "OpenJDK Runtime Environment",
"java.runtime.version": "11.0.11+9-Ubuntu-0ubuntu2.20.04",
"java.vendor": "Ubuntu",
"java.vendor.url": "https://ubuntu.com/",
"java.version": "11.0.11",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "11.0.11+9-Ubuntu-0ubuntu2.20.04",
"os.name": "Linux",
"os.version": "5.4.0-65-generic",
"user.language": "en",
"user.timezone": "Etc/UTC"
},
"metabase-info": {
"databases": [
"mysql"
],
"hosting-env": "unknown",
"application-database": "mysql",
"application-database-details": {
"database": {
"name": "MySQL",
"version": "8.0.26"
},
"jdbc-driver": {
"name": "MariaDB Connector/J",
"version": "2.6.2"
}
},
"run-mode": "prod",
"version": {
"date": "2021-12-01",
"tag": "v0.41.3",
"branch": "release-x.41.x",
"hash": "b0ac6f2"
},
"settings": {
"report-timezone": "US/Central"
}
}
}