Details

    • Type: Sub-task
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 1.0.0-incubating
    • Component/s: None
    • Labels:

      Description

      Support an UPSERT VALUES and UPSERT SELECT statement in Calcite. These have the same syntax as INSERT VALUES and INSERT SELECT, but they insert the row if it doesn't already exist and update it otherwise. This is a pretty common pattern for big data applications that want to have an option to write without reading first. See http://phoenix.apache.org/language/index.html#upsert_values and http://phoenix.apache.org/language/index.html#upsert_select for syntax.

        Activity

        Hide
        julianhyde Julian Hyde added a comment -

        I was initially thinking we would have create a Phoenix-specific extension to the parser. But since UPSERT is identical to INSERT except for the first word, that would seem to be overkill. I think it would work to add a new reserved word, parse UPSERT as INSERT, and add a boolean flag to SqlInsert AST node.

        That flag would be carried into the TableModify relational expression, perhaps an extra value in its enum Operation

        { INSERT, UPDATE, DELETE, MERGE }

        . It's TBD whether UPSERT behaves more like INSERT or MERGE during optimization. It's clear that it's more like INSERT at parse time.

        Show
        julianhyde Julian Hyde added a comment - I was initially thinking we would have create a Phoenix-specific extension to the parser. But since UPSERT is identical to INSERT except for the first word, that would seem to be overkill. I think it would work to add a new reserved word, parse UPSERT as INSERT, and add a boolean flag to SqlInsert AST node. That flag would be carried into the TableModify relational expression, perhaps an extra value in its enum Operation { INSERT, UPDATE, DELETE, MERGE } . It's TBD whether UPSERT behaves more like INSERT or MERGE during optimization. It's clear that it's more like INSERT at parse time.
        Show
        julianhyde Julian Hyde added a comment - Fixed in http://git-wip-us.apache.org/repos/asf/incubator-calcite/commit/2727c5b3 .
        Hide
        julianhyde Julian Hyde added a comment -

        Closing now that 1.0.0-incubating has been released.

        Show
        julianhyde Julian Hyde added a comment - Closing now that 1.0.0-incubating has been released.

          People

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

            Dates

            • Created:
              Updated:
              Resolved:

              Development