Re: SQL:2011 application time

Thank you again for such thorough reviews!

On Thu, Nov 16, 2023 at 11:12 PM jian he <jian(dot)universality(at)gmail(dot)com> wrote:
> UPDATE FOR PORTION OF, may need insert privilege. We also need to document this.
> Similarly, we also need to apply the above logic to DELETE FOR PORTION OF.

I don't think UPDATE/DELETE FOR PORTION OF is supposed to require INSERT permission.

Notionally the INSERTs are just to preserve what was there already, not to add new data.
The idea is that a temporal table is equivalent to a table with one row for every "instant",
i.e. one row per microsecond/second/day/whatever-time-resolution. Of course that would be too slow,
so we use PERIODs/ranges instead, but the behavior should be the same. Date's book has a good
discussion of this idea.

I also checked the SQL:2011 draft standard, and there is a section called Access Rules in Part 2:
SQL/Foundation for UPDATE and DELETE statements. Those sections say you need UPDATE/DELETE
privileges, but say nothing about needing INSERT privileges. That is on page 949 and 972 of the PDFs
from the "SQL:20nn Working Draft Documents" link at [1]. If someone has a copy of SQL:2016 maybe
something was changed, but I would be surprised.

I also checked MariaDB and IBM DB2, the only two RDBMSes that implement FOR PORTION OF to my
knowledge. (It is not in Oracle or MSSQL.) I created a table with one row, then gave another user
privileges to SELECT & UPDATE, but not INSERT. In both cases, that user could execute an UPDATE FOR
PORTION OF that resulted in new rows, but could not INSERT genuinely new rows. [2,3]

So instead of changing this I've updated the documentation to make it explicit that you do not need
INSERT privilege to use FOR PORTION OF. I also documented which triggers will fire and in which order.

> + <para>
> + If the table has a <link
> linkend="ddl-periods-application-periods">range column
> + or <literal>PERIOD</literal></link>, you may supply a
>
> should be
>
> + <para>
> + If the table has a range column or <link
> linkend="ddl-periods-application-periods">
> + <literal>PERIOD</literal></link>, you may supply a
>
> similarly the doc/src/sgml/ref/delete.sgml the link reference also broken.

Okay, changed.

> "given interval", "cut off" these words, imho, feel not so clear.
> We also need a document that:
> "UPDATE FOR PORTION OF" is UPDATE and INSERT (if overlaps).
> If the "UPDATE FOR PORTION OF" range overlaps then
> It will invoke triggers in the following order: before row update,
> before row insert, after row insert. after row update.

Okay, reworked the docs for this.

> src/test/regress/sql/for_portion_of.sql
> You only need to create two triggers?
> since for_portion_of_trigger only raises notice to output the triggers
> meta info.

Changed.

v19 patch series attached, rebased to a11c9c42ea.

[1] https://web.archive.org/web/20230923221106/https://www.wiscorp.com/SQLStandards.html

[2] MariaDB test:

First create a table as the root user:

```
create table t (id int, ds date, de date, name text, period for valid_at (ds, de));
insert into t values (1, '2000-01-01', '2001-01-01', 'foo');
```

and give another user select & update privlege (but not insert):

```
create database paul;
use paul;
create user 'update_only'@'localhost' identified by 'test';
grant select, update on paul.t to 'update_only'@'localhost';
flush privileges;
```

Now as that user:

```
mysql -uupdate_only -p
use paul;
-- We can update the whole record:
update t for portion of valid_at from '2000-01-01' to '2001-01-01' set name = 'bar';
-- We can update a part of the record:
update t for portion of valid_at from '2000-01-01' to '2000-07-01' set name = 'baz';
select * from t;
+------+------------+------------+------+
| id | ds | de | name |
+------+------------+------------+------+
| 1 | 2000-01-01 | 2000-07-01 | baz |
| 1 | 2000-07-01 | 2001-01-01 | bar |
+------+------------+------------+------+
-- We cannot insert:
insert into t values (2, '2000-01-01', '2001-01-01' 'another');
ERROR 1142 (42000): INSERT command denied to user 'update_only'@'localhost' for table `paul`.`t`
```

[3] IBM DB2 test:

```
mkdir ~/local/db2
cd ~/local/db2
tar xzvf ~/Downloads/v11.5.9_linuxx64_server_dec.tar.gz
cd server_dev
./db2_install # should put something at ~/sqllib
source ~/sqllib/db2profile
db2start # but I got "The database manager is already active."
db2
create database paul -- first time only, note no semicolon
connect to paul
create table t (id integer, ds date not null, de date not null, name varchar(4000), period
business_time (ds, de));
insert into t values (1, '2000-01-01', '2001-01-01', 'foo');
grant connect on database to user james;
grant select, update on t to user james;
```

Now as james:

```
source ~paul/sqllib/db2profile
db2
connect to paul
select * from paul.t;
update paul.t for portion of business_time from '2000-01-01' to '2000-06-01' set name = 'bar';
DB20000I The SQL command completed successfully.
select * from paul.t;
insert into paul.t values (2, '2000-01-01', '2001-01-01', 'bar');
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "JAMES". Operation: "INSERT". Object: "PAUL.T". SQLSTATE=42501
```

Yours,

--
Paul ~{:-)
pj(at)illuminatedcomputing(dot)com