Hello, I'm evaluating metabase and seeing some join syntax being generated from the UI that seems a bit random and is causing incorrect results.
This is the query that metabase is generating when I join several tables together and filter with "IsNot"
Here is the question setup in metabase:
And here is the SQL that it produces:
SELECT count(*) AS "count"
FROM "dbo"."JobContract"
INNER JOIN "dbo"."JobApplication" "JobApplication - JobApplicationId"
ON "dbo"."JobContract"."JobApplicationId" = "JobApplication - JobApplicationId"."Id"
INNER JOIN "dbo"."Job" "Job - JobId"
ON "JobApplication - JobApplicationId"."JobId" = "Job - JobId"."Id"
INNER JOIN "dbo"."Company" "Company - CompanyId"
ON ((floor((("Job - JobId"."CompanyId" - 0.0) / 25.0)) * 25.0) + 0.0) = "Company - CompanyId"."Id"
WHERE ("dbo"."JobContract"."IsContractEnded" = 0
AND "dbo"."JobContract"."IsAcceptedByCompany" = 1
AND "dbo"."JobContract"."IsAcceptedByCandidate" = 1
AND ("Company - CompanyId"."Id" <> '44' OR "Company - CompanyId"."Id" IS NULL)
AND ("Company - CompanyId"."Id" <> '10' OR "Company - CompanyId"."Id" IS NULL))
A couple of things:
- Database is MS SSQL Server on AWS RDS
- Metabase is 41.0
- The query produces incorrect results
- The database is missing an actual FK but I don't think that should cause this and they are added in the admin section of metabase
- All the ID columns and FK columns referencing them in the question are bigint so no conversion is needed
- If I remove the floor and the math from the generated join language above the answers are correct
- I've tried this several times from scratch
- I have seen another example of this and two of the joins in a muli-table join like this had the weird math but with different numbers throw in - I think one used 25 and the other used 300.
Questions:
- What causes or is the reasoning for the floor function and apparently randomly selected numbers with a math equation in the join clause?
- Why is excluding two companies causing that strange OR clause with IS NULL on a PK with a NOT NULL clause? (correctly declared in the database and in metabase)
Any insight and/or advice is much appreciated!
Full system info:
{
"browser-info": {
"language": "en-US",
"platform": "MacIntel",
"userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/94.0.4606.81 Safari/537.36",
"vendor": "Google Inc."
},
"system-info": {
"file.encoding": "UTF-8",
"java.runtime.name": "OpenJDK Runtime Environment",
"java.runtime.version": "11.0.12+7",
"java.vendor": "Eclipse Foundation",
"java.vendor.url": "https://adoptium.net/",
"java.version": "11.0.12",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "11.0.12+7",
"os.name": "Linux",
"os.version": "5.10.47-linuxkit",
"user.language": "en",
"user.timezone": "GMT"
},
"metabase-info": {
"databases": [
"h2",
"sqlserver"
],
"hosting-env": "unknown",
"application-database": "h2",
"application-database-details": {
"database": {
"name": "H2",
"version": "1.4.197 (2018-03-18)"
},
"jdbc-driver": {
"name": "H2 JDBC Driver",
"version": "1.4.197 (2018-03-18)"
}
},
"run-mode": "prod",
"version": {
"date": "2021-10-06",
"tag": "v0.41.0",
"branch": "release-x.41.x",
"hash": "c529fe2"
},
"settings": {
"report-timezone": null
}
}
}
on the first post