Details

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

      Description

      Oracle and MSSQL have a REPLACE function (see http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions134.htm). It is not in the SQL standard, and in fact the standard has no equivalent function.

      Syntax:

      REPLACE(char, search_string [, replace_string])
      

      REPLACE is not a reserved word. Therefore the implementation will need to add REPLACE to CommonNonReservedKeyWord() in the parser.

      Note that Drill and potentially other users of Optiq use REPLACE as part of CREATE OR REPLACE syntax. Careful not to break that.

        Issue Links

          Activity

          Hide
          julianhyde Julian Hyde added a comment -

          As part of this task, update REFERENCE.md.

          Show
          julianhyde Julian Hyde added a comment - As part of this task, update REFERENCE.md.
          Hide
          julianhyde Julian Hyde added a comment -

          Riccardo Tommasini, The PR looks good as far as it goes. Can you please also update REFERENCE.md and add tests; see CALCITE-1557's additions to SqlOperatorBaseTest.

          Show
          julianhyde Julian Hyde added a comment - Riccardo Tommasini , The PR looks good as far as it goes. Can you please also update REFERENCE.md and add tests; see CALCITE-1557 's additions to SqlOperatorBaseTest.
          Hide
          rictomm Riccardo Tommasini added a comment -

          Julian Hyde, added test and reference.md edits, from the PR it looks the same commit for simplicity.

          Show
          rictomm Riccardo Tommasini added a comment - Julian Hyde , added test and reference.md edits, from the PR it looks the same commit for simplicity.
          Hide
          julianhyde Julian Hyde added a comment -

          The javadoc looks fishy to me:

          Definition of the "REPLACE" built-in SQL function that takes 3 arguments.

          Based on Postgres's REPLACE function, that takes 2
          arguments and has an entirely different purpose.

          Show
          julianhyde Julian Hyde added a comment - The javadoc looks fishy to me: Definition of the "REPLACE" built-in SQL function that takes 3 arguments. Based on Postgres's REPLACE function, that takes 2 arguments and has an entirely different purpose.
          Hide
          julianhyde Julian Hyde added a comment -

          It should return the empty string, not null (except when we fix CALCITE-815 have SqlConformance#emptyStringIsNull enabled). I will change the test case and implementation.

          I checked Postgres:

          foodmart=> select replace('ciao','ciao','') is null;
           ?column? 
          ----------
           f
          (1 row)
          
          Show
          julianhyde Julian Hyde added a comment - It should return the empty string, not null (except when we fix CALCITE-815 have SqlConformance#emptyStringIsNull enabled). I will change the test case and implementation. I checked Postgres: foodmart=> select replace('ciao','ciao','') is null; ?column? ---------- f (1 row)
          Hide
          julianhyde Julian Hyde added a comment -

          My remarks about javadoc no longer apply: I realized that we did not need an explicit class for the function, so removed it.

          Fixed in http://git-wip-us.apache.org/repos/asf/calcite/commit/5184aa7b. Thanks for the PR, Riccardo Tommasini!

          Show
          julianhyde Julian Hyde added a comment - My remarks about javadoc no longer apply: I realized that we did not need an explicit class for the function, so removed it. Fixed in http://git-wip-us.apache.org/repos/asf/calcite/commit/5184aa7b . Thanks for the PR, Riccardo Tommasini !
          Hide
          julianhyde Julian Hyde added a comment -

          Resolved in release 1.12.0 (2017-03-24).

          Show
          julianhyde Julian Hyde added a comment - Resolved in release 1.12.0 (2017-03-24).

            People

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

              Dates

              • Created:
                Updated:
                Resolved:

                Development