Joining with saved question results in conversion of an integer field and TYPE_MISMATCH error

Get Help
1

Hi, I've looked around the forums for similar issues to this and haven't found anything. When joining with a saved question, metabase seems to try to convert an integer to a float resulting in a TYPE_MISMATCH error. Sometimes recreating the questions resolves the issue.

The table is from clickhouse which we connect to with the metabase-clickhouse driver GitHub - ClickHouse/metabase-clickhouse-driver: ClickHouse database driver for the Metabase business intelligence front-end (version 1.1.7). We're using the latest version of metabase ( v0.46.6) and Java 11 on Ubuntu 22.

The table in question has field 'idvisit' of type UInt64. The question with the issue references a previous question so I'll refer to the main question as question A and the referenced question as question B.

Question B is simply filtering the table with a single WHERE clause. In question A, we join a table with Question B on the idvisit column. Both columns are type UInt64. The resulting error is

Code: 53. DB::Exception: Can't infer common type for joined columns: --table1.idvisit: UInt64 at left, plus(multiply(floor(divide(minus(idvisit, 10000.), 2000)), 2000), 10000.): Float64 at right. There is no supertype for types Float64, UInt64 because some of them are integers and some are floating point, but there is no floating point type, that can exactly represent all required integers. (TYPE_MISMATCH) (version 23.3.1.2823 (official build)) , server ClickHouseNode [uri=http://REDACTED, options={use_server_time_zone_for_dates=true,use_no_proxy=false,product_name=metabase/1.1.7}]@-1348457042

It appears to be trying to convert the integer (idvisit field) with plus(multiply(floor(divide(minus(idvisit, 10000.), 2000)), 2000), 10000.). This conversion also shows up if I try to join on a separate UInt64 column.

We can sometimes get this to work by removing and recreating the joins or recreating the question entirely. When it works, the SQL makes sense and is simply table1.idvisit = table2.idvisit as opposed to having the conversion shown above where it's table1.idvisit = plus(multiply(floor...table2.idvisit).

Note: This error doesn't occur when joining with the original table, only when joining with the saved question (which is simply a filtered subset of the table). We could rewrite the question differently but filtering and then joining with the saved question seems to have a huge performance benefit.

Has anybody seen a similar issue or have a reliable way to get it to work consistently without having to convert the question to sql or recreate the question?

Thanks.

2

I would suggest you try reaching the clickhouse driver devs (they're very responsive): GitHub - ClickHouse/metabase-clickhouse-driver: ClickHouse database driver for the Metabase business intelligence front-end.

What if you try to define the id's as keys in the table?

3

Thanks Luiggi, I'll reach out to the clickhouse driver devs. I did find a similar question which contained a similar join/conversion but they aren't using clickhouse. https://discourse.metabase.com/t/why-is-floor-and-generated-math-used-as-part-of-a-join/17325/2

The referenced issue doesn't appear related to the issue I'm seeing though (unless I'm misunderstanding it).

Do you mean in metabase Admin or the original clickhouse table? I did try setting it to "Entity Key" type in metabase but that didn't change anything. I haven't tried "Foreign Key" but will try that. In the clickhouse DB, the field being joined is a primary key. I've tried using a different field (not primary key) and seen the same issue so I don't feel like it's specific to this field.