Apache Pinot

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

```select
currency_code,
from orders 
where SUBSTR(currency_code, 0, 2) &lt;&gt; 'US'
limit 10```

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)```

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

It works if `substr` is in the select statement

```select
SUBSTR(currency_code, 0, 2)
from order
--where SUBSTR(currency_code, 0, 2) &lt;&gt; 'US'
limit 10```

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

Can you try adding a filter `where currency_code = ''`?

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.```

hmm… but it seems I can’t exclude empty strings…
```where currency_code&lt;&gt;''
and SUBSTR(currency_code, 0, 2) &lt;&gt; 'US'```

`SUBSTR(RPAD(currency_code, 5, ' '), 0, 2) &lt;&gt; 'US'` would work :sweat_smile:

You can probably try `substr(currency_code, 0, min(2, length(currency_code)))`

Single copy of immutable string that kind of intern (<https://en.wikipedia.org/wiki/String_interning|wiki>)?

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

Could be. We don't use `intern` explicitly

You can try both and see which one runs faster