PowerBI cannot show compound types

[mikethebeer]

We are investigating issues with compound types (object, array), that they cannot be displayed correctly within PowerBI (printing Error).

A closer look into this show that PowerBI cannot to read and convert its values to the CLR type System.Int32[] since this is not supported--> in case its an Integer-Array in CrateDB.

However, using Postgresql as backend DB this work and it seems that compound types like Arrays are not binary streamed. It looks like they are streamed as text (String support as pgodbc does).

From pgodbc FAQ:

4.2) What data types does the driver support?
...
There is partial support for all other data types. Examples of these: point, circle, box and arrays. String support only is provided for these non-standard types. In other words, they are returned as SQL_VARCHAR and can be displayed and updated like any other data type.

That's what I get with Npgsql --> Postgresql:

Is there something you know @roji in Npgsql where we need to hook-in from the Crate-Npgsql Plugin to override its Array Handling that we could achieve the same behavior as with Postgresql?

[roji]

I'd guess that the CrateDB DatabaseInfoFactory somehow doesn't set up array types correctly... Getting a full exception (by trying to use arrays in code) would help pinpoint it.

[mikethebeer]

@roji okay I got new findings. It seems that PowerBI --> Crate runs the query:

select "$Table"."arr" as "arr"
from "doc"."t" "$Table"
limit 1000

whereas PowerBI --> Postgres runs:

select cast("$Table"."arr" as varchar) as "arr"
from "public"."t" "$Table"
limit 1000

and this is because Postgres returns in its column metadata a different result

select COLUMN_NAME, ORDINAL_POSITION, IS_NULLABLE, case when (data_type like '%unsigned%') then DATA_TYPE || ' unsigned' else DATA_TYPE end as DATA_TYPE
from INFORMATION_SCHEMA.columns
where TABLE_SCHEMA = 'public' and TABLE_NAME = 't'
order by TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION;

 column_name | ordinal_position | is_nullable | data_type 
-------------+------------------+-------------+-----------
 arr         |                1 | YES         | ARRAY
(1 row)

whereas Crate returns:

select COLUMN_NAME, ORDINAL_POSITION, IS_NULLABLE, case when (data_type like '%unsigned%') then DATA_TYPE || ' unsigned' else DATA_TYPE end as DATA_TYPE
from INFORMATION_SCHEMA.columns
where TABLE_SCHEMA = 'doc' and TABLE_NAME = 't'
order by TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION

 column_name | ordinal_position | is_nullable |   data_type   
-------------+------------------+-------------+---------------
 arr         |                1 | t           | integer_array
(1 row)