Re: SQL:2011 application time

Thank you again for such thorough reviews!

On Thu, Nov 16, 2023 at 11:12 PM jian he <jian.universality@gmail.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@illuminatedcomputing.com