https://pinot.apache.org/ logo
d

Dileep Reddy

09/02/2020, 9:22 AM
doesn’t trim function work with sql?
Copy code
select * from guestslslitm3years where TRIM(regn_n) = 'R200 Regional Ofc' limit 10
this gives below exception
Copy code
requestId=273,table=guestslslitm3years_OFFLINE,timeMs=34,docs=0/0,entries=0/0,segments(queried/processed/matched/consuming/unavailable):10780/0/0/0/0,consumingFreshnessTimeMs=0,servers=10/10,groupLimitReached=false,exceptions=10,serverStats=(Server=SubmitDelayMs,ResponseDelayMs,ResponseSize,DeserializationTimeMs);10.59.100.32_O=0,26,1533,0;10.59.100.33_O=0,30,1533,0;10.59.100.30_O=1,28,1533,0;10.59.100.31_O=1,24,1533,0;10.59.100.36_O=1,23,1533,0;10.59.100.37_O=1,24,1533,0;10.59.100.34_O=1,22,1533,0;10.59.100.35_O=2,19,1533,0;10.59.100.38_O=2,24,1533,0;10.59.100.39_O=2,26,1533,0,query=select * from guestslslitm3years where trim(regn_n) = 'R200 Regional Ofc' limit 10

Caught BadQueryRequestException while processing requestId: 273, Unsupported function: trim with 3 parameters
Processed requestId=273,table=guestslslitm3years_OFFLINE,segments(queried/processed/matched/consuming)=1058/-1/-1/-1,schedulerWaitMs=0,reqDeserMs=10,totalExecMs=17,resSerMs=0,totalTimeMs=27,minConsumingFreshnessMs=-1,broker=Broker_10.59.100.48_8099,numDocsScanned=-1,scanInFilter=-1,scanPostFilter=-1,sched=fcfs
the same works with pql
k

Kishore G

09/02/2020, 2:47 PM
@Dileep Reddy checking
found the issue, there is a conflict between calcite and pinot udf for trim
👍 1
calcite is rewriting the function to
trim("BOTH", "' ", columnName)
ltrim and rtrim works as a work around for now
d

Dileep Reddy

09/02/2020, 3:18 PM
thanks