KSQL Functions and Arrays should use 1-based indexing

[blueedgenick]

Almost all common SQL systems count character-positions within strings starting from one, not zero. (Hive is the only common exception, which kinda betrays it's origins as a tool of java programmers). KSQL should also follow this common pattern in order to ease adoption.
Additionally, those "newer" SQL-based systems which support more complex types like Arrays - e.g. Presto - will count element positions within those arrays also starting at position one, not zero.

Looking at what's currently implemented / requested / in-flight in KSQL, this seems to impact the following:

• String Functions

  • SUBSTRING (already exists, zero-based)
  • STRPOS / INSTR / POSITION (not yet implemented but requested)
  • SPLIT_PART (not yet implemented)

• Arrays

  • access-by-index e.g. my_array[0] (already implemented as zero-based)
  • ELEMENT function (in PR Add SQL functions for working with Maps and Arrays #1611 )
  • ARRAY_REMOVE function (not yet implemented but likely to be added to Add SQL functions for working with Maps and Arrays #1611 )
  • ARRAY_POSITION function (not yet implemented)

[big-andy-coates]

How about adding a single property ksql.functions.arrayget.legacy.base or something, (which defaults to false, but people wanting old functionality can change to true), and this setting should only effect array access-by-index. SUBSTRING will have a legacy mode that will switch the meaning of its args and its indexing base back to pre-5.1 style. All the pending ones just work in an SQLy way from the get go.

Personally, I prefer this to having some property to control if the index-base is one or zero. Which would require all new functions added to adhere to this too.

[big-andy-coates]

This is related to #1634, whose PR looks to fix SUBSTRING. The PR #1635 fixes SUBSTRING: arrays are now base-one indexed by default.

For consistency, the fix for this issue should also use the ksql.functions.<something> configuration pattern. Maybe something like a boolean ksql.functions.arrayget.legacy.base configuration or something like that. Then, in the future, if we move array access into a UDF called ArrayGet, it will all still work.

[almazik]

I would prefer the current implementation with 0-based indexes and notes in the documentation reminding that