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

          Issue Links

            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: