I am executing a query and getting following error `select l

Question

I am executing a query and getting following error `select length mydata from mytable where length mydata gt 500 LIMIT 10` Other queries are working fine Wondering whether this a query issue or cluster setup index issue ``` ERROR SplitRunner 0 38 com facebook presto execution executor TaskExecutor Error processing Split 20201121 222456 00039 rmrgi 1 0 0 4 PinotSplit connectorId=pinot splitType=SEGMENT columnHandle= PinotColumnHandle columnName=mydata dataType=varchar type=REGULAR segmentPinotQuery=Optional SELECT mydata FROM mytable REALTIME LIMIT 2147483647 brokerPinotQuery=Optional empty segments= mytable 0 7 20201121T2220Z segmentHost=Optional Server test pinot server 1 test pinot server headless 8098 start = 2824864 565796 wall = 3 ms cpu = 0 ms wait = 0 ms calls = 1 PINOT UNCLASSIFIED ERROR Error when hitting host Server test pinot server 1 sb pinot server headless 8098 with pinot query SELECT mydata FROM mytable REALTIME LIMIT 2147483647 ```

Kishore G · Answer

is this with Presto Pinot connector

Matt · Answer

yes that is correct < Kishore G>

Kishore G · Answer

Does it work if you run it directly on Pinot

Kishore G · Answer

prestodb or prestosql

Matt · Answer

good thinking just tried and it works in Pinot

Kishore G · Answer

looks like the connector is not pushing the udf down to Pinot

Kishore G · Answer

```SELECT mydata FROM mytable REALTIME LIMIT 2147483647```

Kishore G · Answer

its trying to pull everything out of Pinot

Matt · Answer

that query worked once earlier So do you think I have to check Pinot presto connector settings

Kishore G · Answer

which presto are you using

Kishore G · Answer

prestodb or prestosql

Matt · Answer

prestodb the one from incubator pinot github chart

Matt · Answer

The query always works without where clause like `select length mydata from mytable limit 500`

Matt · Answer

< Kishore G> After clearing the caches for presto the query started to work as normal Thanks for pointing the issue

Kishore G · Answer

it might be working but its not efficient

Matt · Answer

Thats correct there is a latency comparing with query directly hitting pinot and executing via presto

Matt · Answer

I was expecting the same performance though

Kishore G · Answer

its basically pulling all the data from Pinot into Presto and doing the computation in Presto you want most of the computation to run in Pinot

Kishore G · Answer

yeah its a matter of telling Presto to push this function down to Pinot

Kishore G · Answer

< Xiang Fu> is there a way to configure this or it requires a code change in the connector

Xiang Fu · Answer

is mydata a multi value column

Xiang Fu · Answer

I think presto has a function called array length

Matt · Answer

< Xiang Fu> mydata is a STRING with maxsize 10000

Matt · Answer

mapped as VARCHAR in presto

Xiang Fu · Answer

i see I think string length method is not pushed down through presto

Xiang Fu · Answer

it requires code change to do that

Xiang Fu · Answer

current approach is that presto will read all the strings back and compute the string length

Xiang Fu · Answer

it s definitely inefficient

Matt · Answer

Ohh ok most of the other methods are available right so far I face issue with length only Trying others as well

Matt · Answer

yes it is extremely slow

Matt · Answer

I now installed pinot driver in superset and trying to split the diff types of queries

Xiang Fu · Answer

for aggregations we pushed down them but those string functions we don t pushdown yet

Xiang Fu · Answer

split the diff types

Xiang Fu · Answer

you mean register both presto and pinot tables

Matt · Answer

The ones require join from other datasources I can use presto driver and the queries that can be served just by pinot I can use pinot

Matt · Answer

yes correct

Xiang Fu · Answer

got it

Matt · Answer

direct Pinot queries are performing well

Xiang Fu · Answer

yes that s why we need function pushdown slightly smiling face

Matt · Answer

Hope that will get implemented at some point soon Also came across SQL Passthrough <https prestosql io blog 2020 05 25 pinot connector html>

Xiang Fu · Answer

yes prestosql allows to directly pushdown whatever your wrote to pinot That is good for users know how pinot query works and syntax

Matt · Answer

Thanks will try and see