SUBSTRING() function is 0-indexed

[jordanw-bq]

Using Docker image amazon/opendistro-for-elasticsearch:1.1.0
Dataset: Flights sample data (loaded through Kibana)
SQL Plugin version: 1.1.0.1

Example Query:
SELECT substring(OriginWeather, 1, 4) FROM kibana_sample_data_flights

Results:

| substring_1 | 
| unn | 
| lea | 
| ain | 
| hun | 
| ama | 
...

Expectation:
The JDBC standard defines the SUBSTRING() function as being 1-indexed (ie. the first character in the string is index 0), and the upper bound as inclusive, so the results should be as shown below:

| substring_1 | 
| Sunn | 
| Clea | 
| Rain | 
| Thun | 
| Dama | 
...

[dai-chen]

Just did a quick look into x-pack. Interestingly, it supports both 0 and 1-indexed. Will check SQL and JDBC standard later.

[chloe-zh]

Besides, the function format should be substring(originalString, startIndex, substringLength), but our substring was implemented to be substring(originalString, startIndex, endIndex).

Will check the index rule to be 0-index or 1-index, and fix the problem mentioned above in the latest PR I'm going to submit as well.

[chloe-zh]

I've check this case in es x-pack, it should be 1-index. When either startIndex or endIndex is out of bound, they are corrected to startIndex = 1 or endIndex = stringLength, which is translated to start_index = 0 and end_index = string_legnth - 1 in painless language script.

[chloe-zh]

I've check this case in es x-pack, it should be 1-index. When either startIndex or endIndex is out of bound, they are corrected to startIndex = 1 or endIndex = stringLength, which is translated to start_index = 0 and end_index = string_legnth - 1 in painless language script.

One more thing to mention, our semantic analyzer throws exception when the params like index, length etc. are set to negative.