Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-2572

SUBSTRING function fails if start position is less than 1

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 1.18.0
    • Component/s: core
    • Labels:
      None

      Description

      Calcite substring throws a IndexOutOfBoundsException with a position less than 1. Per the SQL standard, the position should be treated as the larger of the provided value and 1, however many implementations treat negative values as starting from the end of the string.

      Extended standard:
      https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions162.htm
      https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_substring
      https://spark.apache.org/docs/2.3.0/api/sql/index.html#substring
      https://www.sqlite.org/lang_corefunc.html#substr

      Follow the standard:
      https://docs.microsoft.com/en-us/sql/t-sql/functions/substring-transact-sql?view=sql-server-2017
      https://www.postgresql.org/docs/9.1/static/functions-string.html

        Attachments

          Activity

            People

            • Assignee:
              julianhyde Julian Hyde
              Reporter:
              apilloud Andrew Pilloud
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: