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

SUBSTRING function fails if start position is less than 1

VotersWatch issueWatchersLinkCloneUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Fixed
    • None
    • 1.18.0
    • core
    • 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

          This comment will be Viewable by All Users Viewable by All Users
          Cancel

          People

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

            Dates

              Created:
              Updated:
              Resolved:

              Slack

                Issue deployment