Trino lowercases the query when passing to pinot. As a result query with predicates like

field = 'Some Value'

returns no result because it gets translated to

field = 'some value'

. did anyone figure out a way to resolve this issue?

@Elon ^ might know

Hi, yes you can either move the filter outside the `"`'s and it will get pushed down properly with the case

yeah i have mixed case filter, and although i am sharing predicate here, what i am really using is

valuein

where i don't have the option of sending that outside of double quotes as i am querying from looker to pinot via trino.

Ah I see - does

IN ('value1', 'value2', ...)

work? Or that is not pushed down? I can check also

SELECT VALUEIN(myfield, 'Value One', 'vAluE TwO') AS bucket_id,
       DISTINCTCOUNTHLL(account_id) AS distinct_allocations
FROM   allocation_events
WHERE  myfield IN ('Value One', 'vAluE TwO')
GROUP  BY bucket_id

and

valuein

is not registered in looker, so i have to include the entire query in double quotes. what that means is that trino receives this query from looker

SELECT * FROM default."SELECT VALUEIN(myfield, 'Value One', 'vAluE TwO') AS bucket_id,
                              DISTINCTCOUNTHLL(account_id) AS distinct_allocations
                       FROM   allocation_events
                       WHERE  myfield IN ('Value One', 'vAluE TwO')
                       GROUP  BY bucket_id"

Note that

myfield

is an array of strings.

Ah, there is an ugly way you can do it:

select ... from "select valuein(concat(upper('v'), 'alue', '')...

technically yes, but what goes into

valuein

comes from user input lol. for now i am just lower casing data as it goes into pinot, but it might not work for some other use cases where we need to be case sensitive.

iirc there is an effort to make trino identifiers mixed case, not sure what the state of the project is though.

i think it was, not sure if it is anymore after it got acquired by google.

🙂

do you know if there is a github issue for this? i was only able to find where lower case of table name was an issue.

I saw it a while back - can see if I can find it

thank you, really appreciate it.

sure, if you have a license where you can modify looker lmk also:)

haha i am trying to push away from looker to save $ and move to free superset 😂

That's a different issue - this one focuses on a pretty large modification to trino to enable mixed case identifiers.

I don't see an issue on github specific to what i am facing. Do you want me to file one?

sure!

Actually this one does specify the lower casing of predicates

Case 1a
The following pass-through query returns NULL value as the result:

SELECT * FROM pinot.default."
  SELECT COUNT(*)
  FROM tab
  WHERE col_str1 = 'SOME_STRING_VALUE'
";
When reading the logical query plan in Trino, the string value SOME_STRING_VALUE in the WHERE clause gets lowercased, hence the Pinot connector in Trino receives the value some_string_value.

In Pinot, this query works as expected.

I am using Trino version 378.

awesome! Thanks so much @Priyank Bagrecha!