Chundong Wang
02/26/2021, 11:16 PMIllegalStateException
when using string functions in where clause. 😢select
currency_code,
from orders
where SUBSTR(currency_code, 0, 2) <> 'US'
limit 10
"message": "QueryExecutionError:\njava.lang.IllegalStateException: Caught exception while invoking method: public static java.lang.String org.apache.pinot.common.function.scalar.StringFunctions.substr(java.lang.String,int,int) with arguments: [, 0, 2]\n\tat org.apache.pinot.common.function.FunctionInvoker.invoke(FunctionInvoker.java:148)\n\tat org.apache.pinot.core.operator.transform.function.ScalarTransformFunctionWrapper.transformToStringValuesSV(ScalarTransformFunctionWrapper.java:209)\n\tat org.apache.pinot.core.operator.dociditerators.ExpressionScanDocIdIterator.processProjectionBlock(ExpressionScanDocIdIterator.java:167)\n\tat org.apache.pinot.core.operator.dociditerators.ExpressionScanDocIdIterator.next(ExpressionScanDocIdIterator.java:81)
Jackie
02/26/2021, 11:19 PMsubstring
on an empty stringChundong Wang
02/26/2021, 11:20 PMsubstr
is in the select statementselect
SUBSTR(currency_code, 0, 2)
from order
--where SUBSTR(currency_code, 0, 2) <> 'US'
limit 10
Jackie
02/26/2021, 11:21 PMwhere currency_code = ''
?Chundong Wang
02/26/2021, 11:21 PMJackie
02/26/2021, 11:22 PMString.substring()
Returns a string that is a substring of this string. The substring begins at the specified beginIndex and extends to the character at index endIndex - 1. Thus the length of the substring is endIndex-beginIndex.
Examples:
"hamburger".substring(4, 8) returns "urge"
"smiles".substring(1, 5) returns "mile"
Params:
beginIndex – the beginning index, inclusive.
endIndex – the ending index, exclusive.
Returns:
the specified substring.
Throws:
IndexOutOfBoundsException – if the beginIndex is negative, or endIndex is larger than the length of this String object, or beginIndex is larger than endIndex.
Chundong Wang
02/26/2021, 11:22 PMwhere currency_code<>''
and SUBSTR(currency_code, 0, 2) <> 'US'
SUBSTR(RPAD(currency_code, 5, ' '), 0, 2) <> 'US'
would work 😅Jackie
02/26/2021, 11:24 PMsubstr(currency_code, 0, min(2, length(currency_code)))
Chundong Wang
02/26/2021, 11:24 PMJackie
02/26/2021, 11:26 PMintern'ed
mean here?Chundong Wang
02/26/2021, 11:26 PMRPAD
would create more string in memory than arithmetic operations like min/strlenJackie
02/26/2021, 11:28 PMintern
explicitlyChundong Wang
02/26/2021, 11:29 PM