View Details: "is connected to:" uses wrong PK for query with join

[babakbh]

Describe the bug

In some cases, after clicking on the "View Details", instead of showing the connected entities, the phrase "Unknown" is displayed.

By monitoring the messages passed between Metabase and the Postgres Docker Image using Wireshark, one can notice that the problem comes from a wrong sql query, that Metabase generates and sends to the Postgres Database:

Message generated by metabase and sent to the Postgres DB:
SELECT count(*) AS "count" FROM "source1"."rooms" WHERE "source1"."rooms"."h_id" = 9

The Reply message from Postgres DB to metabase:
operator does not exist: text = integer No operator matches the given name and argument types. You might need to add explicit type casts

Similar errors can be seen by checking the Wireshark log of discussions between metabase and the postgres DB after clicking on different columns data, every now and then.

To Reproduce

In order to reproduce this error,
1- start from the Destination Postgres Database from the following link:
https://github.com/ontopic-vkg/destination-tutorial

2- Add the destination database, as a source database to metabase,

3- join the following tables: Source1.Hospitality, Source1.Municipality.

4- after visualizing the data, and clicking on the "View Details" as follow,

one will get the following error message:

No response

Logs

By monitoring the messages passed between Metabase and the Postgres Docker Image using Wireshark, one can notice that the problem comes from a wrong sql query, that Metabase generates and sends to the Postgres Database:

Message generated by metabase and sent to the Postgres DB:
SELECT count(*) AS "count" FROM "source1"."rooms" WHERE "source1"."rooms"."h_id" = 9

The Reply message from Postgres DB to metabase:
operator does not exist: text = integer No operator matches the given name and argument types. You might need to add explicit type casts

Information about your Metabase installation

{
  "browser-info": {
    "language": "en-US",
    "platform": "Win32",
    "userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) 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.18+10",
    "java.vendor": "Eclipse Adoptium",
    "java.vendor.url": "https://adoptium.net/",
    "java.version": "11.0.18",
    "java.vm.name": "OpenJDK 64-Bit Server VM",
    "java.vm.version": "11.0.18+10",
    "os.name": "Linux",
    "os.version": "5.10.102.1-microsoft-standard-WSL2",
    "user.language": "en",
    "user.timezone": "GMT"
  },
  "metabase-info": {
    "databases": [
      "h2",
      "postgres"
    ],
    "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": "2023-01-27",
      "tag": "v0.45.2.1",
      "branch": "release-x.45.2.x",
      "hash": "1a59de7"
    },
    "settings": {
      "report-timezone": null
    }
  }
}

Severity

Blocking the usage of metabase for us.

No response

Tasks

Beta Give feedback

No tasks being tracked yet.

[calherries]

Repro'd using the Sample database on v0.45.2.1, v0.46.6 and master.

The problem above with "View Details' happens more generally when one table is joined with another by a foreign key in a GUI question.

The POST /api/dataset request that is used to render the "... is connected to" section (the ObjectRelationships component) uses the row index in the table (starting from 0) to query the object relationships data. It should use the row's PK instead.

Steps to reproduce:

1. Create a new question joining Accounts to Invoices.

2. Visualize the results, open the browser network tab and click "View Details" on the third row of the results (the ID should be 1).
3. In the payload of the latest POST /api/dataset request, observe that the filter in the query has the value 2, even though the ID of the Account is 1.

4. Further, click one of the buttons under "Macy is connected to..."

5. Observe that the filter applied to the Analytics table is "Account ID is 2", when it should be 1.
6. You can try steps 2-5 again with the 4th row, and the same result will happen but instead the ID used in the filter is 3.
7. On versions from 46 onwards, 'View Details' doesn't show any data at all when the dataset query doesn't find any results. You can reproduce this by going to the last result in the table, and clicking "View Details". You will see a blank modal with a loading spinner:
   
   This is because the query includes a filter for an Account with ID=1999, which doesn't exist.

[kamilmielnik]

I can reproduce it in master at afc28b9.