Hey, just started playing with Trino as I have a need to do subqueries / joins on Pinot tables. I'm having trouble with array fields (singleValueField: false) in Pinot when querying through Trino. From reading through the connector documentation, it seems to support arrays. Here is my Pinot query that works (service is String array field):

select service, count(*) from immutable_unified_events group by service limit 10

I've tried this query in Trino using basic and passthrough syntax, but get the same error either way.

class java.lang.String cannot be cast to class java.util.List

Trino standard query:

select service, count(*) from pinot.default.immutable_unified_events group by service limit 10;

Trino passthrough query:

select * from pinot.default."select service, count(*) from immutable_unified_events group by service limit 10";

Hi @Aaron Weiss - we have a fix for this, I will mention the pr for it here shortly.

thanks @Elon, so once that PR gets merged, it would be live, or that would be a version update?

You can rebase from master once it's landed - we usually wait until releases to rebase, but you can try it. I'll update this thread so you can see the fix.

thanks a lot, appreciate it!

@Aaron Weiss - just to get some more context, what are the datatypes for the columns in the query? Also, which ones are arrays?

@Elon I don't follow the issue here. What unexpected behavior do you find in pinot?

ex. let's say you have an array column, string for example:

string_array_col

with the following values

['hi', 'hello', 'how are you'],
[null],
['good day'],
['bye', 'good bye', 'take care']

This is expected MV column behavior, where the grouping is applied on each individual value of the MV column

aha! Thanks:)

To get the array grouping semantic, we need to pre-join the values into a single value and do the grouping

Yep

To push down the query, we need to add a transform function to join MV values into a single value

nice, is that something that would be a welcome contribution? Worth filing a github issue?

Sure, thanks!

Hey @Elon, sorry I didn't respond earlier, but I think yall got it straight. An MV field does get broken up when you group by, which is actually the preferred behavior in my case, but I could also see where not breaking it up could be useful in some cases. So I like @Jackie’s idea of having the option of grouping by the entire array through a function. As for the Trino errors, I tried grouping by an MV string column only and then an MV int column only and got the same error for both on the Trino side.

Yep, agreed. Sounds good!

looks good to me

Yes, it's being worked on - I will add your query as a unit test 🙂 The fix is to disable pushdown when the grouping column is an array. I'll update it shortly and share the link.

thanks, just so I understand, you would still end up with the split array resultset? What implication does disabling pushdown have on the performance?

It would end up with the entire array as the grouping column - this matches the behavior of trino and other query engines, where

select col, ... from table group by col

will not modify or explode the column value.

hmmm, I'd actually prefer the split array resultset, but we're saying that's just not possible through trino?

It is absolutely:) You just need to do it in a passthrough query. But if you did the above query it would return the result that trino expects.

ah ok, yeah that seems like best of both worlds, nice

will return the result with unmodified mv column, which matches the behavior if the table was in any other type of connector (postgres, hive, iceberg, etc.)

got it, yeah my initial example where service is a string MV column would work now

select * from pinot.default."select service, count(*) from immutable_unified_events group by service limit 10";

sure! Great that you're using pinot and trino 🙂

yeah it's really been fun to work with so far, and the responsiveness of the community has been great

@Aaron Weiss - just added support both the best of both worlds:) Regular queries return the full array, passthrough queries explode the array so you still get the benefit of that feature in pinot

Awesome! Thanks sir

Here is the pr with the fixes for array support: https://github.com/trinodb/trino/pull/9781

hey @Elon, we just noticed that filters on MV columns also fail in trino. Does this PR fix that as well? browser is an MV string column

pinot> SELECT COUNT(1) FROM pinot.default.immutable_unified_events WHERE browser = 'Chrome'
[2022-03-16 10:10:43] [58] Query failed (#20220315_220247_00048_mxd5n): line 1:75: Cannot apply operator: array(varchar) = varchar(6)
[2022-03-16 10:10:43] io.trino.spi.TrinoException: Unexpected parameters (array(varchar), varchar(6)) for function $operator$equal. Expected: $operator$equal(T, T) T:comparable

Tried the same with flexible syntax and while it doesn't error, it just returns NULL if filtering on MV col. If I use SV col, everything works as expected using either syntax.

SELECT * FROM pinot.default."SELECT COUNT(1) FROM immutable_unified_events WHERE browser = 'Chrome'"

Is

browser

an MV column and

immutable_unified_events

an SV column?

immutable_unified_events is the table

What is the corresponding pinot query which works?

the same query works in Pinot

SELECT COUNT(1) FROM immutable_unified_events WHERE browser = 'Chrome'

Ok, I'll test it and update the pr, thanks for catching this!

thank you!

@alon I found this thread while looking for a solution to the same error Aaron shared above (from a year ago) where I'm trying to filter on a Pinot MV column using Trino (414).

trino error: line 1:50: Cannot apply operator: array(bigint) = integer

Is query passthrough still the only option? @Mayank, @Xiang Fu. Looks like MV column support was added to prestodb? https://github.com/prestodb/presto/pull/15093

Can you paste the query?

select * from events where ids = 29536865 limit 10

ids

column is MV in the

events

table in Pinot.

when you describe the table, what do you see

It lists out the columns, type, extra, comment.

For the data type, it gives you an array of type or just the data type?

What is the type? Iirc we now have array support. Which version of Pinot and Trino do you run?

for the column that I'm using in the query above, the type =

array(bigint)

. I tried similar query on

array(varchar)

type column as well and got a failure. I'm running Trino 414 in K8s and Pinot is latest Startree version.

What version for startree? I definitely see support for array types including unit/integration tests which pass (also used it in prod) - support is for Pinot 11.0 and newer. The error message should not be what it was above iirc, can you paste the error you get and I’ll try to repro?

So when I try to query Pinot via Trino in Superset I get the error below:

Trino Error
trino error: line 1:64: Cannot apply operator: array(bigint) = integer


This may be triggered by:
Issue 1002 - The database returned an unexpected error. 

See less

From Redash:

Error running query: TrinoUserError(type=USER_ERROR, name=TYPE_MISMATCH, message="line 1:65: Cannot apply operator: array(bigint) = integer", query_id=20230501_185514_00374_xk5ym)

DBeaver:

SQL Error [58]: Query failed (#20230501_171816_00370_xk5ym): line 2:65: Cannot apply operator: array(bigint) = integer

This doesn’t look like it comes from the Pinot connector - either that or for some reason it thinks the column is a SV integer. Can you paste the output of describe table? Feel free to redact any column names, just need to see the types

Untitled spreadsheet - Sheet1.csv

And which field corresponds with the one that you had an issue with?

Ah! facepalm , I was under the impression that Trino supported the same semantics via the pinot connector. Thank you for looking into this and your time! 🙏

Sure, any time!

Thanks @Elon !