Support TRUNCATE TABLE statement

[ebyhr]

This statement is DML (not DDL) in ANSI SQL. The keyword TRUNCATE is a part of reserved words.

<truncate table statement> ::=
  TRUNCATE TABLE <target table>  [ <identity column restart option>  ]

<identity column restart option> ::=
    CONTINUE IDENTITY
  | RESTART IDENTITY

Output of other databases:
MySQL

mysql> truncate table t1;
Query OK, 0 rows affected (0.01 sec)

PostgreSQL

postgres=# truncate table test.t1;
TRUNCATE TABLE

MS SQL Server

mssql> truncate table t1;
OK

Executed in 0 ms

Oracle

SQL> truncate table t1;
 
Table truncated.

Cassandra

cqlsh> truncate table test.t1;
cqlsh> 

Relates to https://trinodb.slack.com/archives/CFLB9AMBN/p1629328499266100

[martint]

Here are the syntax rules from the spec:

In particular, note that:

• T shall be a base table and shall not be a system-versioned table.
• T shall not be identified by the name of the referenced table in any referential constraint descriptor.

[martint]

Also, the spec says:

7. If no rows are deleted from T, then a completion condition is raised: no data.

[ebyhr]

@martint Thank you sharing the details. I will add some relevant tests. Could you upload the screenshot again? I can't see that.

[ebyhr]

7. If no rows are deleted from T, then a completion condition is raised: no data.

We may need to check if the table is empty or not beforehand to achieve this. MySQL, PostgreSQL and Oracle always return 0 in Statement.executeUpdate() method and doesn't throw any exception regardless of deleted rows.

[findepi]

does "completion condition is raised: no data" mean "query should fail"?

[martint]

does "completion condition is raised: no data" mean "query should fail"?

It looks like the answer is no, it shouldn't fail. Completion/exception conditions are parallel and somewhat orthogonal to query query success or failure. They are just a classification of the resulting SQLSTATE of the query.

If a condition is raised that causes a statement to have no effect other
than that associated with raising the condition (that is, not a completion
condition), then the condition is said to be an exception condition or exception.
If a condition is raised that permits a statement to have an effect other than
that associated with raising the condition (corresponding to an SQLSTATE
class code of successful completion, warning, or no data), then the condition
is said to be a completion condition.

The completion condition warning is broadly defined as completion in which the effects are correct, but there is reason to caution the user about those effects. It is raised for implementation-defined conditions as well as conditions specified in this part of ISO/IEC 9075. The completion condition no data has special significance and is used to indicate an empty result. The completion condition successful completion is defined to indicate a completion condition that does not correspond to warning or no data. This includes conditions in which the SQLSTATE subclass provides implementation-defined information of a non-cautionary nature.

For the purpose of choosing status parameter values to be returned, exception conditions for transaction rollback have precedence over exception conditions for statement failure. Similarly, the completion condition no data has precedence over the completion condition warning, which has precedence over the completion condition successful completion. All exception conditions have precedence over all completion conditions. The values assigned to SQLSTATE shall obey these precedence requirements.

and elsewhere:

8. Case:
   a) If S executed successfully, then either a completion condition is raised: successful completion, or a
   completion condition is raised: warning, or a completion condition is raised: no data.
   b) If S did not execute successfully, then all changes made to SQL-data or schemas by the execution of
   S are canceled and an exception condition is raised.