Ran into `IllegalStateException` when using string functions

Question

Ran into `IllegalStateException` when using string functions in where clause cry

Chundong Wang · Answer

```select currency code from orders where SUBSTR currency code 0 2 lt gt US limit 10```

Chundong Wang · Answer

Exception ``` 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 · Answer

Seems the problem is caused by calling `substring` on an empty string

Chundong Wang · Answer

It works if `substr` is in the select statement

Chundong Wang · Answer

```select SUBSTR currency code 0 2 from order where SUBSTR currency code 0 2 lt gt US limit 10```

Chundong Wang · Answer

Is there way to tell if it s related to some of the segments

Jackie · Answer

Can you try adding a filter `where currency code = `

Chundong Wang · Answer

yep that d go wrong too

Jackie · Answer

Javadoc for `String 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 · Answer

hmm but it seems I can t exclude empty strings ```where currency code lt gt and SUBSTR currency code 0 2 lt gt US ```

Chundong Wang · Answer

ah i see

Chundong Wang · Answer

`SUBSTR RPAD currency code 5 0 2 lt gt US ` would work sweat smile

Jackie · Answer

You can probably try `substr currency code 0 min 2 length currency code `

Jackie · Answer

Yeah that works too

Chundong Wang · Answer

that works too

Chundong Wang · Answer

probably min length is better than RPAD

Chundong Wang · Answer

Would such string be intern ed

Jackie · Answer

What does `intern ed` mean here

Chundong Wang · Answer

Single copy of immutable string that kind of intern <https en wikipedia org wiki String interning|wiki>

Chundong Wang · Answer

I m wonder if `RPAD` would create more string in memory than arithmetic operations like min strlen

Jackie · Answer

Could be We don t use `intern` explicitly

Jackie · Answer

You can try both and see which one runs faster

Chundong Wang · Answer

Got it Thanks for the help