Starburst json type is erroneously mapped to :type/Text

[atekin]

In the following code
https://github.com/starburstdata/metabase-driver/blob/f4fe016552eed2567addb5c5f9af6edaa5322a59/drivers/starburst/src/metabase/driver/implementation/sync.clj#L45C40-L45C50
Starburst json type is being mapped to :type/Text. This make Metabase thinks some columns errernously as varchar and send queries like
... SUBSTRING("some_json_column", 1, 1234) "substring2494"...
which throws an exception like:
io.trino.spi.TrinoException: line 1:3439: Unexpected parameters (json, integer, integer) for function substring. Expected: substring(char(x), bigint), substring(char(x), bigint, bigint), substring(varchar(x), bigint), substring(varchar(x), bigint, bigint)

If one looks at postgres driver line 605 and 606 can see that https://github.com/metabase/metabase/blob/6fe41dd4379f4476b3eb78215b600393dd34474d/src/metabase/driver/postgres.clj#L605
and https://github.com/metabase/metabase/blob/6fe41dd4379f4476b3eb78215b600393dd34474d/src/metabase/driver/postgres.clj#L606

:json :type/JSON
:jsonb :type/JSON

Could you please correct json fields as :type/JSON

[lpoulain]

@atekin I was not able to reproduce this error despite looking at a table with a JSON type. Could you provide the steps to get the error you mentioned?

[atekin]

I believe you need to have JSON data type, with column value length greater than 1234 characters as seen in the exception: SUBSTRING("some_json_column", 1, 1234), since it tries to get first 1234 characters. When the json column string exceeds this 1234 number it tries to get substring part of it, then trino creates the exception, since json type does not support substring function.

If you still cannot produce I can supply an example postgresql data type and values

[lpoulain]

I tried a large column with more than 1234 characters but couldn't reproduce the problem. And when I look at the SQL being generated by Metabase, I never see the SUBSTRING() being called. What steps in Metabase itself do you follow to generate the error? I went to "Browse data" and looked at my table.

[jeffreybrooks-vecna]

I experienced a similar problem with Metabase assigning type/Text to a Trino array column. I'm guessing Metabase generates the problematic query to profile the table, so you might try re-syncing the database schema or re-scanning the field values (both in Settings > Admin Settings > Databases). You could also look at Metabase's take on the table schema (Settings > Admin Settings > Table Metadata > (your_db) > (your_table) > Original Schema). Columns that Trino identifies as arrays (or JSON, in atekin's case) are identified by Metabase as type/Text.

[jeffreybrooks-vecna]

Here are steps to reproduce:

1. Set up a Trino instance with the memory database.
2. Create a table ("table_with_array") with an array column ("array_field") in the memory database and confirm that Trino identifies its type is array:
   
3. Add the Trino memory database to a Metabase instance and sync the database schema.
4. Check what type Metabase identifies for the "array_field" column.
   

Expected behavior: Metabase identifies the data type as array. ("type/array"?)
Actual behavior: Metabase identifies the data type as "type/text".

Side notes:

• As far as I can tell, the Metabase documentation does not provide a list of data types that it supports. It should. I wrote '("type/array"?)' because I don't know whether Metabase even has an array type.
• Similarly, the documentation for each connector should specify the mapping between the connector's native data types and the Metabase data types. The current documentation doesn't shed light on whether the mapping from Trino array to Metabase type/text is a bug or intended behavior (because, perhaps, Metabase doesn't have an array type).