Why is floor and generated math used as part of a join?

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:

  1. Database is MS SSQL Server on AWS RDS
  2. Metabase is 41.0
  3. The query produces incorrect results
  4. 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
  5. All the ID columns and FK columns referencing them in the question are bigint so no conversion is needed
  6. If I remove the floor and the math from the generated join language above the answers are correct
  7. I've tried this several times from scratch
  8. 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.


  1. What causes or is the reasoning for the floor function and apparently randomly selected numbers with a math equation in the join clause?
  2. 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": [
    "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

Hi @tpak
You're seeing this issue:
https://github.com/metabase/metabase/issues/18589 - upvote by clicking :+1: on the first post

Thanks for the link.

Hi @flamber - thanks for that - I upvoted it. However; in your examples there is an option to choose the binning on the UI, in my case there is not, I wonder if that is because I am choosing PK's?

Also, does this also explain that strange is null SQL that I am seing in the where clause?

@tpak Are you sure that "Job - JobId"."CompanyId" is a Entity Key or Foreign Key? Otherwise you should see a binning option, but perhaps it's caused by a fingerprinting issue.

The strange where-clause is because some databases does not include null rows otherwise.
I don't understand what you mean by "Why is excluding two companies", when you have added the filters.