Upsert and Replace

-- Same example is applicable to `REPLACE`
UPSERT INTO Films VALUES ('UA502', 'Bananas', 105, '1971-07-13', 'Comedy', '82 minutes');

-- Same example is applicable to `REPLACE`
UPSERT INTO Films (code, title, did, date_prod, kind)
VALUES ('UA502', 'Bananas', 105, '1971-07-13', 'Comedy', '82 minutes');

-- Same example is applicable to `REPLACE`
UPSERT INTO Films
VALUES ('UA503', 'Bananas', 105, DEFAULT, 'Comedy', DEFAULT);

-- Same example is applicable to `REPLACE`
UPSERT INTO Films (code, title, did, date_prod, kind)
VALUES ('T_603', 'Yojimbo', 106, DEFAULT, 'Drama');

-- Same example is applicable to `REPLACE`
UPSERT INTO films (title, code, did, date_prod, len)
VALUES ('MyTitle', 'MyCode', 108, '1961-06-16', '180 minutes');

SELECT * FROM Films;<<
{
   'code': 'UA503',
   'title': 'Bananas',
   'did': 105,
   'date_prod': 2022-08-10T,
   'kind': 'Comedy',
   'len': NULL
},
{
   'code': 'T_603',
   'title': 'Yojimbo',
   'did': 106,
   'date_prod': 2022-08-10T,
   'kind': 'Drama',
   'len': NULL
},
{
   'code': 'MyCode',
   'title': 'MyTitle',
   'did': 108,
   'date_prod': 1961-06-16T,
   'kind': 'Comedy',
   'len': '180 minutes'
}>>

-- Same example is applicable to `REPLACE`
UPSERT INTO Films DEFAULT VALUES;

-- Same example is applicable to `REPLACE`
UPSERT INTO Films (code, title, did, date_prod, kind)
VALUES ('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),
       ('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy');

-- The following `CREATE TABLE` syntax is arbitrary since the PartiQL DDL is yet to be defined:
CREATE TABLE Music /* SCHEMA CLOSED */
(
   Artist     VARCHAR(20) NOT NULL,
   SongTitle  VARCHAR(30) NOT NULL,
   PRIMARY KEY (Artist, SongTitle)
);

-- Same example is applicable to `REPLACE`
UPSERT INTO Music
<<
{'Artist' : 'Acme Band', 'SongTitle' : 'PartiQL Rocks'},
{'Artist' : 'Emca Band', 'SongTitle' : 'PartiQL Rocks'}
>>;

-- The following `CREATE TABLE` syntax is arbitrary since the PartiQL DDL is yet to be defined:CREATE TABLE Person /* SCHEMA OPEN */
(
   LastName    VARCHAR(50) NOT NULL,
   FirstName   VARCHAR(20),
   DOB        DATE NOT NULL,
   PRIMARY KEY (LastName)
);

-- Same example is applicable to `REPLACE`
UPSERT INTO Person<<
{'FirstName' : 'Raul','LastName' : 'Lewis','DOB' : 1963-08-19T,'GovId' : 'LEWISR261LL','GovIdType' : 'Driver License',
},{'LastName' : 'Logan','DOB' : 1967-07-03T,'Address' : '43 Stockert Hollow Road, Everett, WA, 98203'
},{'LastName' : 'Pena','DOB' : 1974-02-10T,'GovId' : '744 849 301','GovIdType' : 'SSN','Address' : '4058 Melrose Street, Spokane Valley, WA, 99206'
}>>;

SELECT * FROM Person;<<
{'LastName' : 'Lewis','FirstName' : 'Raul','DOB' : 1963-08-19T,'GovId' : 'LEWISR261LL','GovIdType' : 'Driver License',
},
{'LastName' : 'Logan','FirstName' : NULL,'DOB' : 1967-07-03T,'Address' : '43 Stockert Hollow Road, Everett, WA, 98203'
},
{'LastName' : 'Pena','FirstName' : NULL,'DOB' : 1974-02-10T,'GovId' : '744 849 301','GovIdType' : 'SSN','Address' : '4058 Melrose Street, Spokane Valley, WA, 99206'
}>>;

-- The following `CREATE TABLE` syntax is arbitrary since the PartiQL DDL is yet to be defined:
CREATE TABLE Foo /* SCHEMA OPEN */
(
   id         INT     NOT NULL PRIMARY KEY,
   is_deleted BOOLEAN NOT NULL DEFAULT FALSE,
   title      VARCHAR(50),
   bar        VARCHAR(10)      DEFAULT 'baz',
);

-- In the following, inserting `{ 'id': 1 }` goes through because the rest of the attributes either have default value or
-- are nullable. In addition, because `Foo` has open-schema we can add `value` as a new attribute to the table.

-- Same example is applicable to `REPLACE`
UPSERT INTO Foo
<<
{ 'id': 1 },
{ 'id': 2, 'title': 'some-name' },
{ 'id': 3, 'is_deleted': true, 'bar': '10'},
{ 'id': 4, 'title': 'some-other-name', 'value': '10'}
>>;

SELECT * FROM Foo;
<<
{ 'id': 1, 'is_deleted': false, 'title': NULL, 'bar': 'baz' },
{ 'id': 2, 'is_deleted': false, 'title': 'some-name', 'bar': 'baz' },
{ 'id': 3, 'is_deleted': true, 'title': NULL, 'bar': '10' },
{ 'id': 4, 'is_deleted': false, 'title': 'some-other-name', 'bar': 'baz', 'value': '10'},
>>;

-- The following `CREATE TABLE` is arbitrary since the PartiQL DDL is yet to be defined:
CREATE TABLE Music /* SCHEMA OPEN */
(
   Artist     VARCHAR(20) NOT NULL,
   SongTitle  VARCHAR(30) NOT NULL,
   AlbumTitle VARCHAR(25) NOT NULL,
   Year       INT,
   Price      FLOAT,
   Genre      VARCHAR(10),
   PRIMARY KEY (Artist, SongTitle)
);

-- The following `CREATE TABLE` syntax is arbitrary since the PartiQL DDL is yet to be defined:
CREATE TABLE RockAlbums /* SCHEMA CLOSED */
(
    Artist     VARCHAR(20) NOT NULL,
    SongTitle  VARCHAR(30) NOT NULL,
    AlbumTitle VARCHAR(25) NOT NULL,
    Year       INT,
    RockGenre  VARCHAR(10),
    PRIMARY KEY (Artist, SongTitle)
);

-- The following query goes through because `Price` attribute is nullable.
-- Same example is applicable to `REPLACE`
UPSERT INTO Music
SELECT *
FROM RockAlbums
WHERE RockGenre IN ('Alternative', 'SpaceRock');

-- The following leads to a `SemanticError` because `RockAlbums` has closed schema.
-- Same example is applicable to `REPLACE`
UPSERT INTO RockAlbums
SELECT *
FROM Music;

-- Same example is applicable to `REPLACE`
UPSERT INTO Music (Artist, SongTitle, AlbumTitle)
SELECT Artist, SongTitle, AlbumTitle
FROM RockAlbums
WHERE RockGenre IN ('Alternative', 'SpaceRock');

-- The following `CREATE TABLE` syntax is arbitrary since the PartiQL DDL is yet to be defined:
CREATE TABLE Music /* SCHEMA OPEN */
(
   Artist     VARCHAR(20) NOT NULL,
   SongTitle  VARCHAR(30) NOT NULL,
   AlbumTitle VARCHAR(25) NOT NULL,
   Year       INT,
   Price      FLOAT,
   Genre      VARCHAR(10),
   PRIMARY KEY (Artist, SongTitle)
);

-- The following `CREATE TABLE` syntax is arbitrary since the PartiQL DDL is yet to be defined:
CREATE TABLE RockAlbums /* SCHEMA CLOSED */
(
    Artist     VARCHAR(20) NOT NULL,
    SongTitle  VARCHAR(30) NOT NULL,
    AlbumTitle VARCHAR(25) NOT NULL,
    Year       DATE,
    RockGenre  VARCHAR(10),
    PRIMARY KEY (Artist, SongTitle)
);

-- Same example is applicable to `REPLACE`
UPSERT INTO Music
SELECT * FROM RockAlbums;

-- The following `CREATE TABLE` syntax is arbitrary since the PartiQL DDL is yet to be defined:
CREATE TABLE Foo /* SCHEMA CLOSED */
(
   id         INT     NOT NULL PRIMARY KEY,
   is_deleted BOOLEAN NOT NULL DEFAULT FALSE,
   title      VARCHAR(50),
   bar        VARCHAR(10) DEFAULT 'baz'
);

-- Same example is applicable to `REPLACE`
UPSERT INTO Foo (id, title)
<<
[2, 'some-name'],
>>;

SELECT * FROM Foo;
<<
{ 'id': 2, 'is_deleted': false, 'title': 'some-name', 'bar': 'baz' }
>>;

UPSERT INTO Foo
<<
[3, true],
[4, true],
>>;

SELECT * FROM Foo;
<<
{ 'id': 3, 'is_deleted': true, 'title': NULL, 'bar': 'baz' },
{ 'id': 4, 'is_deleted': true, 'title': NULL, 'bar': 'baz' }
>>;

-- The following `CREATE TABLE` syntax is arbitrary since the PartiQL DDL is yet to be defined:
CREATE TABLE Foo /* SCHEMA OPEN */
(
   id         int     NOT NULL PRIMARY KEY,
   is_deleted boolean NOT NULL DEFAULT FALSE,
   title      varchar(50),
   bar        varchar(10) DEFAULT 'baz'
);

-- `SemanticError` because of the presence of target attribute names with bag of tuples.
-- In this case, the behavior for adding or not adding attributes like `is_deleted` is undefined.
-- Same example is applicable to `REPLACE`
UPSERT INTO Foo (id, title)
<<
{ 'id': 1 },
{ 'id': 2, 'title': 'some-name' },
{ 'id': 3, 'is_deleted': true, 'title': NULL, 'bar': '10'}
>>;


-- `SemanticError` because not all <bag value> items (row values) can get mapped to the target attributes set.
-- Same example is applicable to `REPLACE`
UPSERT INTO Foo (id, title)
<<
[2, 'some-name'],1,'some-other-name'
>>;

-- `SemanticError` because UPSERT/REPLACE has more target attributes than row values specified by some of the `<bag value>` elements (E.g. `[1]`).
-- Same example is applicable to `REPLACE`
UPSERT INTO Foo (id, title)
<<
[1],
[1, 'some_name']
>>;

-- `SemanticError` because usage of `DEFAULT` outside `VALUES(...)` is unsupported.
-- Same example is applicable to `REPLACE`
UPSERT INTO Foo (id, title)
<<
[1, DEFAULT],
[2, 'some-name']
>>;

-- `SemanticError` because usage of `DEFAULT` outside `VALUES(...)` is unsupported.
-- Same example is applicable to `REPLACE`
UPSERT INTO Foo
<<
{'id': 1, 'is_deleted': DEFAULT},
{'id': 2, 'is_deleted': true}
>>;

-- The following `CREATE TABLE` syntax is arbitrary since the PartiQL DDL is yet to be defined:
CREATE TABLE Customers /* SCHEMA OPEN */
(
    HK      INT     NOT NULL PARTITION KEY,
    RK      INT     NOT NULL SORT KEY,
);

-- Existing Item with HK as primary key: {'HK': 1, 'RK': 1, 'otherAttr1': 5}
-- Item after the update:  {'HK': 1, 'RK': 1, 'otherAttr': 5, 'myAttr1': 1, 'myAttr2': 2}

UPSERT INTO Customers <<
{'HK': 1, 'RK': 1, 'myAttr1': 1, 'myAttr2': 2}
>>

-- Existing Item with HK as primary key:
--  {'HK': 1, 'RK': 1, 'myAttr': 10}
-- Item after the update:
--  {'HK': 1, 'RK': 1, 'myAttr': 12, 'anotherAttr': 'hello'}

UPSERT INTO Customers <<
{'HK': 1, 'RK': 1, 'myAttr1': 12, 'anotherAttr': 'hello'}
>>

-- Existing Item is:
--  {'HK': 1, 'RK': 1, 'myAttr': 12 }
-- Outcome is:
--  SemanticError

UPSERT INTO Customers
<<{'HK': 1, 'thirdAttr': 'world'}>>

UPSERT INTO Customers
<<{'RK': 1, 'thirdAttr': 'world'}>>

UPSERT INTO Customers
<<{'thirdAttr': 'world'}>>

-- The following `CREATE TABLE` syntax is arbitrary since the PartiQL DDL is yet to be defined:
CREATE TABLE Customers /* SCHEMA CLOSED */
(
    HK      INT     NOT NULL PARTITION KEY,
    RK      INT     NOT NULL SORT KEY,
    OtherAttr INT   NOT NULL
);

-- Existing Item with HK as primary key: {'HK': 1, 'RK': 1, 'otherAttr': 5}
-- Outcome is:
--  SemanticError

UPSERT INTO Customers <<
{'HK': 1, 'RK': 1, 'otherAttr': 4, 'myAttr1': 1, 'myAttr2': 2}
>>

-- Existing Item is:
--  {'HK': 1, 'RK': 1, 'myAttr': 12 }
-- Outcome is:
--  {'HK': 1, 'RK': 1, 'thirdAttr': 'world'}

REPLACE INTO Customers
<<{'HK': 1, 'RK': 1, 'thirdAttr': 'world'}>>

-- Existing Item is:
--  {'HK': 1, 'RK': 1, 'myAttr': 12 }
-- Outcome is:
--  SemanticError

REPLACE INTO Customers
<<{'HK': 1, 'thirdAttr': 'world'}>>

REPLACE INTO Customers
<<{'RK': 1, 'thirdAttr': 'world'}>>

REPLACE INTO Customers
<<{'thirdAttr': 'world'}>>

-- The following `CREATE TABLE` syntax is arbitrary since the PartiQL DDL is yet to be defined:
CREATE TABLE Customers /* SCHEMA CLOSED */
(
    HK      INT     NOT NULL PARTITION KEY,
    RK      INT     NOT NULL SORT KEY,
    OtherAttr INT   NOT NULL
);

-- Existing Item is:
--  {'HK': 1, 'RK': 1, 'OtherAttr': 12 }
-- Outcome is:
--  SemanticError

REPLACE INTO Customers
<<{'HK': 1, 'RK': 1, 'OtherAttr': 13, 'thirdAttr': 'world'}>>

REPLACE INTO Customers
<<{'HK': 1, 'RK': 1, 'thirdAttr': 'world'}>>