[tarantool-patches] [PATCH v1 1/1] sql: introduce TRUNCATE TABLE operation

[tarantool-patches] [PATCH v1 1/1] sql: introduce TRUNCATE TABLE operation

[Kirill Shcherbatov]

As sql_table_delete_from already support OP_Clear fast cleanup
for non-complex requests we only need to introduce new words
to parser that call DELETE with NULL conditions.

Closes #2201.
---
Branch: http://github.com/tarantool/tarantool/tree/kshch/gh-2201-truncate-table
Issue: https://github.com/tarantool/tarantool/issues/2201

 extra/mkkeywordhash.c    |  1 +
 src/box/sql/parse.y      |  9 +++++++++
 test/sql/delete.result   | 10 ++++++++++
 test/sql/delete.test.lua |  6 ++++++
 4 files changed, 26 insertions(+)

diff --git a/extra/mkkeywordhash.c b/extra/mkkeywordhash.c
index 1ec1538..705caf1 100644
--- a/extra/mkkeywordhash.c
+++ b/extra/mkkeywordhash.c
@@ -281,6 +281,7 @@ static Keyword aKeywordTable[] = {
   { "VARCHAR",                "TK_ID",          RESERVED,         true  },
   { "WHENEVER",               "TK_STANDARD",    RESERVED,         true  },
   { "WHILE",                  "TK_STANDARD",    RESERVED,         true  },
+  { "TRUNCATE",               "TK_TRUNCATE",    ALWAYS,           true  },
 };
 
 /* Number of keywords */
diff --git a/src/box/sql/parse.y b/src/box/sql/parse.y
index 0c510f5..2091ce8 100644
--- a/src/box/sql/parse.y
+++ b/src/box/sql/parse.y
@@ -725,6 +725,15 @@ cmd ::= with(C) DELETE FROM fullname(X) indexed_opt(I) where_opt(W). {
   sql_table_delete_from(pParse,X,W);
 }
 
+/////////////////////////// The TRUNCATE statement /////////////////////////////
+//
+cmd ::= TRUNCATE TABLE fullname(X). {
+  sqlSubProgramsRemaining = SQL_MAX_COMPILING_TRIGGERS;
+  /* Instruct SQL to initate Tarantool's transaction.  */
+  pParse->initiateTTrans = true;
+  sql_table_delete_from(pParse, X, NULL);
+}
+
 %type where_opt {Expr*}
 %destructor where_opt {sql_expr_delete(pParse->db, $$, false);}
 
diff --git a/test/sql/delete.result b/test/sql/delete.result
index c33079c..6026406 100644
--- a/test/sql/delete.result
+++ b/test/sql/delete.result
@@ -33,6 +33,16 @@ box.sql.execute("SELECT * FROM t1;");
 ---
 - - [2, 4]
 ...
+--
+-- gh-2201: TRUNCATE TABLE operation
+--
+box.sql.execute("TRUNCATE TABLE t1;")
+---
+...
+box.sql.execute("SELECT * FROM t1;")
+---
+- []
+...
 -- Cleanup
 box.sql.execute("DROP TABLE t1;");
 ---
diff --git a/test/sql/delete.test.lua b/test/sql/delete.test.lua
index 1721989..7132302 100644
--- a/test/sql/delete.test.lua
+++ b/test/sql/delete.test.lua
@@ -21,6 +21,12 @@ box.sql.execute("DELETE FROM t1 WHERE a=1;");
 -- Verify
 box.sql.execute("SELECT * FROM t1;");
 
+--
+-- gh-2201: TRUNCATE TABLE operation
+--
+box.sql.execute("TRUNCATE TABLE t1;")
+box.sql.execute("SELECT * FROM t1;")
+
 -- Cleanup
 box.sql.execute("DROP TABLE t1;");
 
-- 
2.7.4

[tarantool-patches] Re: [PATCH v1 1/1] sql: introduce TRUNCATE TABLE operation

[Vladislav Shpilevoy]

Hello. I do not see, where do you call box_truncate, and where is a
test that it is called.

On 18/07/2018 11:22, Kirill Shcherbatov wrote:
> As sql_table_delete_from already support OP_Clear fast cleanup
> for non-complex requests we only need to introduce new words
> to parser that call DELETE with NULL conditions.
> 
> Closes #2201.
> ---
> Branch: http://github.com/tarantool/tarantool/tree/kshch/gh-2201-truncate-table
> Issue: https://github.com/tarantool/tarantool/issues/2201
> 
>   extra/mkkeywordhash.c    |  1 +
>   src/box/sql/parse.y      |  9 +++++++++
>   test/sql/delete.result   | 10 ++++++++++
>   test/sql/delete.test.lua |  6 ++++++
>   4 files changed, 26 insertions(+)
> 

[tarantool-patches] Re: [PATCH v1 1/1] sql: introduce TRUNCATE TABLE operation

[Kirill Shcherbatov]

> Hello. I do not see, where do you call box_truncate, and where is a
> test that it is called.
I've discussed this problem with Nikita and we have decided to make separate OP_Truncate code.

=====================================

diff --git a/extra/mkkeywordhash.c b/extra/mkkeywordhash.c
index 1ec1538..705caf1 100644
--- a/extra/mkkeywordhash.c
+++ b/extra/mkkeywordhash.c
@@ -281,6 +281,7 @@ static Keyword aKeywordTable[] = {
   { "VARCHAR",                "TK_ID",          RESERVED,         true  },
   { "WHENEVER",               "TK_STANDARD",    RESERVED,         true  },
   { "WHILE",                  "TK_STANDARD",    RESERVED,         true  },
+  { "TRUNCATE",               "TK_TRUNCATE",    ALWAYS,           true  },
 };
 
 /* Number of keywords */
diff --git a/src/box/sql/build.c b/src/box/sql/build.c
index a64d723..be4c860 100644
--- a/src/box/sql/build.c
+++ b/src/box/sql/build.c
@@ -1953,7 +1953,7 @@ vdbe_emit_stat_space_clear(struct Parse *parse, const char *stat_table_name,
 	 * On memory allocation error sql_table delete_from
 	 * releases memory for its own.
 	 */
-	sql_table_delete_from(parse, src_list, where);
+	sql_table_delete_from(parse, src_list, where, false);
 }
 
 /**
diff --git a/src/box/sql/delete.c b/src/box/sql/delete.c
index f9d3498..ab6771d 100644
--- a/src/box/sql/delete.c
+++ b/src/box/sql/delete.c
@@ -73,7 +73,7 @@ sql_materialize_view(struct Parse *parse, const char *name, struct Expr *where,
 
 void
 sql_table_delete_from(struct Parse *parse, struct SrcList *tab_list,
-		      struct Expr *where)
+		      struct Expr *where, bool allow_truncate)
 {
 	struct sqlite3 *db = parse->db;
 	if (parse->nErr || db->mallocFailed)
@@ -185,9 +185,10 @@ sql_table_delete_from(struct Parse *parse, struct SrcList *tab_list,
 	 */
 	if (where == NULL && !is_complex) {
 		assert(!is_view);
-
-		sqlite3VdbeAddOp1(v, OP_Clear, space_id);
-
+		if (!allow_truncate || space_is_system(space) || in_txn() != NULL)
+			sqlite3VdbeAddOp1(v, OP_Clear, space_id);
+		else
+			sqlite3VdbeAddOp1(v, OP_Truncate, space_id);
 		/* Do not start Tarantool's transaction in case of
 		 * truncate optimization. This is workaround until
 		 * system tables cannot be changes inside a
diff --git a/src/box/sql/fkey.c b/src/box/sql/fkey.c
index 6a91890..e67866c 100644
--- a/src/box/sql/fkey.c
+++ b/src/box/sql/fkey.c
@@ -784,7 +784,7 @@ sqlite3FkDropTable(Parse *parser, SrcList *name, Table *table)
 	/* Staring new transaction before DELETE FROM <tbl> */
 	sqlite3VdbeAddOp0(v, OP_TTransaction);
 	sql_table_delete_from(parser, sqlite3SrcListDup(parser->db, name, 0),
-			      NULL);
+			      NULL, false);
 	parser->disableTriggers = 0;
 	/*
 	 * If the DELETE has generated immediate foreign key
diff --git a/src/box/sql/parse.y b/src/box/sql/parse.y
index 0c510f5..18ca39b 100644
--- a/src/box/sql/parse.y
+++ b/src/box/sql/parse.y
@@ -722,7 +722,16 @@ cmd ::= with(C) DELETE FROM fullname(X) indexed_opt(I) where_opt(W). {
   sqlSubProgramsRemaining = SQL_MAX_COMPILING_TRIGGERS;
   /* Instruct SQL to initate Tarantool's transaction.  */
   pParse->initiateTTrans = true;
-  sql_table_delete_from(pParse,X,W);
+  sql_table_delete_from(pParse,X,W,true);
+}
+
+/////////////////////////// The TRUNCATE statement /////////////////////////////
+//
+cmd ::= TRUNCATE TABLE fullname(X). {
+  sqlSubProgramsRemaining = SQL_MAX_COMPILING_TRIGGERS;
+  /* Instruct SQL to initate Tarantool's transaction.  */
+  pParse->initiateTTrans = true;
+  sql_table_delete_from(pParse, X, NULL,true);
 }
 
 %type where_opt {Expr*}
diff --git a/src/box/sql/sqliteInt.h b/src/box/sql/sqliteInt.h
index 54661cb..5c293bc 100644
--- a/src/box/sql/sqliteInt.h
+++ b/src/box/sql/sqliteInt.h
@@ -3700,10 +3700,11 @@ void sqlite3OpenTable(Parse *, int iCur, Table *, int);
  * @param tab_list List of single element which table from which
  * deletetion if performed.
  * @param where The WHERE clause.  May be NULL.
+ * @param allow_truncate use truncate opcode if possible.
  */
 void
 sql_table_delete_from(struct Parse *parse, struct SrcList *tab_list,
-		      struct Expr *where);
+		      struct Expr *where, bool allow_truncate);
 
 void sqlite3Update(Parse *, SrcList *, ExprList *, Expr *,
 		   enum on_conflict_action);
diff --git a/src/box/sql/trigger.c b/src/box/sql/trigger.c
index ec0bc98..1949fdb 100644
--- a/src/box/sql/trigger.c
+++ b/src/box/sql/trigger.c
@@ -698,8 +698,8 @@ codeTriggerProgram(Parse * pParse,	/* The parser context */
 						      targetSrcList(pParse, pStep),
 						      sqlite3ExprDup(db,
 								     pStep->pWhere,
-								     0)
-				    );
+								     0),
+						      false);
 				break;
 			}
 		default:
diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c
index f50e389..66a567b 100644
--- a/src/box/sql/vdbe.c
+++ b/src/box/sql/vdbe.c
@@ -39,6 +39,7 @@
  * in this file for details.  If in doubt, do not deviate from existing
  * commenting and indentation practices when changing or adding code.
  */
+#include "box/box.h"
 #include "box/txn.h"
 #include "box/session.h"
 #include "sqliteInt.h"
@@ -4574,12 +4575,27 @@ case OP_IdxGE:  {       /* jump */
  * doesn't involve truncating, since it features completely
  * different mechanism under hood.
  */
-case OP_Clear: {
+/* Opcode: Truncate P1 * * * *
+ * Synopsis: space id = P1
+ *
+ * Same as OP_Clear except this routine uses box_truncate that
+ * works faster, but have some restictions: the space_id is not an
+ * ID of system space, this routine mustn't be called by active
+ * transaction.
+ */
+case OP_Clear:
+case OP_Truncate: {
 	assert(pOp->p1 > 0);
 	uint32_t space_id = pOp->p1;
-	struct space *space = space_by_id(space_id);
-	assert(space != NULL);
-	rc = tarantoolSqlite3ClearTable(space);
+	if (pOp->opcode == OP_Clear) {
+		struct space *space = space_by_id(space_id);
+		assert(space != NULL);
+		rc = tarantoolSqlite3ClearTable(space);
+	} else {
+		rc = box_truncate(space_id);
+		if (rc != 0)
+			rc = SQL_TARANTOOL_ERROR;
+	}
 	if (rc) goto abort_due_to_error;
 	break;
 }
diff --git a/test/sql/delete.result b/test/sql/delete.result
index c33079c..6026406 100644
--- a/test/sql/delete.result
+++ b/test/sql/delete.result
@@ -33,6 +33,16 @@ box.sql.execute("SELECT * FROM t1;");
 ---
 - - [2, 4]
 ...
+--
+-- gh-2201: TRUNCATE TABLE operation
+--
+box.sql.execute("TRUNCATE TABLE t1;")
+---
+...
+box.sql.execute("SELECT * FROM t1;")
+---
+- []
+...
 -- Cleanup
 box.sql.execute("DROP TABLE t1;");
 ---
diff --git a/test/sql/delete.test.lua b/test/sql/delete.test.lua
index 1721989..7132302 100644
--- a/test/sql/delete.test.lua
+++ b/test/sql/delete.test.lua
@@ -21,6 +21,12 @@ box.sql.execute("DELETE FROM t1 WHERE a=1;");
 -- Verify
 box.sql.execute("SELECT * FROM t1;");
 
+--
+-- gh-2201: TRUNCATE TABLE operation
+--
+box.sql.execute("TRUNCATE TABLE t1;")
+box.sql.execute("SELECT * FROM t1;")
+
 -- Cleanup
 box.sql.execute("DROP TABLE t1;");
 

[tarantool-patches] Re: [PATCH v1 1/1] sql: introduce TRUNCATE TABLE operation

[n.pettik]

> On 18 Jul 2018, at 16:01, Kirill Shcherbatov <kshcherbatov@tarantool.org> wrote:
> 
>> Hello. I do not see, where do you call box_truncate, and where is a
>> test that it is called.
> I've discussed this problem with Nikita and we have decided to make separate OP_Truncate code.

Should we document somehow this feature (new truncate statement)?
I have heard about doc-bot (but still have never used it tho).

Well, personally I would implement this feature in other way.
I stick to the point that TRUNCATE should be executed only if
user asks for TRUNCATE (using TRUNCATE statement) and vice versa:
DELETE mustn’t result in TRUNCATE.
Not so long ago we deliberately removed truncate from clearing routine.
In fact, you return back previous behaviour which was disabled by this commit:
https://github.com/tarantool/tarantool/commit/290a5c9a08d469be9954df9e7d6d33f5743826c9

Lets ask smb (Vlad or Kirill) for advice, but that is my point.

Actually, TRUNCATE is quite sophisticated (or at least dubious) feature in its behaviour.
I would also ask Peter for his expert opinion, since TRUNCATE is ANSI extension and
is not described there. For instance, TRUNCATE in Postgres and MySQL
doesn’t fire ON DELETE triggers (but fires ON TRUNCATE ones). In your implementation it
might fire, if TRUNCATE launched inside transaction. 
Either, it can’t be executed on referenced table:

create table t1(id int primary key, a int) \\
create table t2(id int primary key references t1) \\
insert into t1 values (1, 2) \\
insert into t2 values (1) \\
truncate t1 \\

0A000: cannot truncate a table referenced in a foreign key constraint

How does TRUNCATE work with IPROTO requests? In MySQL TRUNCATE
doesn’t return number of deleted rows, for example. At least add tests on this case.

> /* Number of keywords */
> diff --git a/src/box/sql/build.c b/src/box/sql/build.c
> index a64d723..be4c860 100644
> --- a/src/box/sql/build.c
> +++ b/src/box/sql/build.c
> @@ -1953,7 +1953,7 @@ vdbe_emit_stat_space_clear(struct Parse *parse, const char *stat_table_name,
> 	 * On memory allocation error sql_table delete_from
> 	 * releases memory for its own.
> 	 */
> -	sql_table_delete_from(parse, src_list, where);
> +	sql_table_delete_from(parse, src_list, where, false);
> }
> 
> /**
> diff --git a/src/box/sql/delete.c b/src/box/sql/delete.c
> index f9d3498..ab6771d 100644
> --- a/src/box/sql/delete.c
> +++ b/src/box/sql/delete.c
> @@ -73,7 +73,7 @@ sql_materialize_view(struct Parse *parse, const char *name, struct Expr *where,
> 
> void
> sql_table_delete_from(struct Parse *parse, struct SrcList *tab_list,
> -		      struct Expr *where)
> +		      struct Expr *where, bool allow_truncate)

For bool flags we usually use ‘is_’ or ‘if_’ prefixes.

> {
> 	struct sqlite3 *db = parse->db;
> 	if (parse->nErr || db->mallocFailed)
> @@ -185,9 +185,10 @@ sql_table_delete_from(struct Parse *parse, struct SrcList *tab_list,
> 	 */
> 	if (where == NULL && !is_complex) {
> 		assert(!is_view);
> -
> -		sqlite3VdbeAddOp1(v, OP_Clear, space_id);
> -
> +		if (!allow_truncate || space_is_system(space) || in_txn() != NULL)

It unlikely to be OK checking active transaction in parser.
When we will have cache for prepared statements, these stmts
might be compiled firstly and then executed together (without re-compilcation):

BEGIN; // Only generate OP_StartTransaction, but not execute it.
DELETE FROM t1; // It would generate OP_Truncate which in turn would fail during execution.

Also, fit code into 80 chars (this <if> is 83 chars long).

> +			sqlite3VdbeAddOp1(v, OP_Clear, space_id);
> +		else
> +			sqlite3VdbeAddOp1(v, OP_Truncate, space_id);

Furthermore, I see no reason to introduce separate opcode.
When I suggested to do so, I thought it wouldn’t mess with OP_Clear.
In your approach it is enough to pass flag to OP_Clear.

> diff --git a/test/sql/delete.test.lua b/test/sql/delete.test.lua
> index 1721989..7132302 100644
> --- a/test/sql/delete.test.lua
> +++ b/test/sql/delete.test.lua
> @@ -21,6 +21,12 @@ box.sql.execute("DELETE FROM t1 WHERE a=1;");
> -- Verify
> box.sql.execute("SELECT * FROM t1;");
> 
> +--
> +-- gh-2201: TRUNCATE TABLE operation
> +--
> +box.sql.execute("TRUNCATE TABLE t1;")
> +box.sql.execute("SELECT * FROM t1;”)

I would add wider range of tests checking TRUNCATE work.
At least truncate inside transaction, truncate on system space,
truncate on view, truncate on space with FK constraints, triggers etc.
Moreover, this test doesn’t check that OP_Truncate opcode is really executed.

[tarantool-patches] Re: [PATCH v1 1/1] sql: introduce TRUNCATE TABLE operation

[Kirill Shcherbatov]

Hi! Thank you for your ideas.
I've reworked the code:

diff --git a/extra/mkkeywordhash.c b/extra/mkkeywordhash.c
index 1ec1538..705caf1 100644
--- a/extra/mkkeywordhash.c
+++ b/extra/mkkeywordhash.c
@@ -281,6 +281,7 @@ static Keyword aKeywordTable[] = {
   { "VARCHAR",                "TK_ID",          RESERVED,         true  },
   { "WHENEVER",               "TK_STANDARD",    RESERVED,         true  },
   { "WHILE",                  "TK_STANDARD",    RESERVED,         true  },
+  { "TRUNCATE",               "TK_TRUNCATE",    ALWAYS,           true  },
 };
 
 /* Number of keywords */
diff --git a/src/box/sql/build.c b/src/box/sql/build.c
index a64d723..be4c860 100644
--- a/src/box/sql/build.c
+++ b/src/box/sql/build.c
@@ -1953,7 +1953,7 @@ vdbe_emit_stat_space_clear(struct Parse *parse, const char *stat_table_name,
 	 * On memory allocation error sql_table delete_from
 	 * releases memory for its own.
 	 */
-	sql_table_delete_from(parse, src_list, where);
+	sql_table_delete_from(parse, src_list, where, false);
 }
 
 /**
diff --git a/src/box/sql/delete.c b/src/box/sql/delete.c
index f9d3498..56dbbe5 100644
--- a/src/box/sql/delete.c
+++ b/src/box/sql/delete.c
@@ -73,7 +73,7 @@ sql_materialize_view(struct Parse *parse, const char *name, struct Expr *where,
 
 void
 sql_table_delete_from(struct Parse *parse, struct SrcList *tab_list,
-		      struct Expr *where)
+		      struct Expr *where, bool is_truncate)
 {
 	struct sqlite3 *db = parse->db;
 	if (parse->nErr || db->mallocFailed)
@@ -185,8 +185,11 @@ sql_table_delete_from(struct Parse *parse, struct SrcList *tab_list,
 	 */
 	if (where == NULL && !is_complex) {
 		assert(!is_view);
-
-		sqlite3VdbeAddOp1(v, OP_Clear, space_id);
+		if (!is_truncate) {
+			sqlite3VdbeAddOp1(v, OP_Clear, space_id);
+		} else {
+			sqlite3VdbeAddOp2(v, OP_Clear, space_id, true);
+		}
 
 		/* Do not start Tarantool's transaction in case of
 		 * truncate optimization. This is workaround until
@@ -195,6 +198,13 @@ sql_table_delete_from(struct Parse *parse, struct SrcList *tab_list,
 		 */
 		parse->initiateTTrans = false;
 	} else {
+		if (is_truncate) {
+			diag_set(ClientError, ER_SQL,
+				 "Specified space could not be truncated.");
+			parse->nErr++;
+			parse->rc = SQL_TARANTOOL_ERROR;
+			goto delete_from_cleanup;
+		}
 		/* Resolve the column names in the WHERE clause. */
 		struct NameContext nc;
 		memset(&nc, 0, sizeof(nc));
diff --git a/src/box/sql/fkey.c b/src/box/sql/fkey.c
index 6a91890..e67866c 100644
--- a/src/box/sql/fkey.c
+++ b/src/box/sql/fkey.c
@@ -784,7 +784,7 @@ sqlite3FkDropTable(Parse *parser, SrcList *name, Table *table)
 	/* Staring new transaction before DELETE FROM <tbl> */
 	sqlite3VdbeAddOp0(v, OP_TTransaction);
 	sql_table_delete_from(parser, sqlite3SrcListDup(parser->db, name, 0),
-			      NULL);
+			      NULL, false);
 	parser->disableTriggers = 0;
 	/*
 	 * If the DELETE has generated immediate foreign key
diff --git a/src/box/sql/parse.y b/src/box/sql/parse.y
index 0c510f5..700f40d 100644
--- a/src/box/sql/parse.y
+++ b/src/box/sql/parse.y
@@ -722,7 +722,16 @@ cmd ::= with(C) DELETE FROM fullname(X) indexed_opt(I) where_opt(W). {
   sqlSubProgramsRemaining = SQL_MAX_COMPILING_TRIGGERS;
   /* Instruct SQL to initate Tarantool's transaction.  */
   pParse->initiateTTrans = true;
-  sql_table_delete_from(pParse,X,W);
+  sql_table_delete_from(pParse,X,W,false);
+}
+
+/////////////////////////// The TRUNCATE statement /////////////////////////////
+//
+cmd ::= TRUNCATE TABLE fullname(X). {
+  sqlSubProgramsRemaining = SQL_MAX_COMPILING_TRIGGERS;
+  /* Instruct SQL to initate Tarantool's transaction.  */
+  pParse->initiateTTrans = true;
+  sql_table_delete_from(pParse, X, NULL,true);
 }
 
 %type where_opt {Expr*}
diff --git a/src/box/sql/sqliteInt.h b/src/box/sql/sqliteInt.h
index 54661cb..5eb4825 100644
--- a/src/box/sql/sqliteInt.h
+++ b/src/box/sql/sqliteInt.h
@@ -3703,7 +3703,7 @@ void sqlite3OpenTable(Parse *, int iCur, Table *, int);
  */
 void
 sql_table_delete_from(struct Parse *parse, struct SrcList *tab_list,
-		      struct Expr *where);
+		      struct Expr *where, bool is_truncate);
 
 void sqlite3Update(Parse *, SrcList *, ExprList *, Expr *,
 		   enum on_conflict_action);
diff --git a/src/box/sql/trigger.c b/src/box/sql/trigger.c
index ec0bc98..1949fdb 100644
--- a/src/box/sql/trigger.c
+++ b/src/box/sql/trigger.c
@@ -698,8 +698,8 @@ codeTriggerProgram(Parse * pParse,	/* The parser context */
 						      targetSrcList(pParse, pStep),
 						      sqlite3ExprDup(db,
 								     pStep->pWhere,
-								     0)
-				    );
+								     0),
+						      false);
 				break;
 			}
 		default:
diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c
index f50e389..357beaa 100644
--- a/src/box/sql/vdbe.c
+++ b/src/box/sql/vdbe.c
@@ -39,6 +39,7 @@
  * in this file for details.  If in doubt, do not deviate from existing
  * commenting and indentation practices when changing or adding code.
  */
+#include <box/box.h>
 #include "box/txn.h"
 #include "box/session.h"
 #include "sqliteInt.h"
@@ -4566,8 +4567,9 @@ case OP_IdxGE:  {       /* jump */
 	break;
 }
 
-/* Opcode: Clear P1 * * * *
+/* Opcode: Clear P1 P2 * * *
  * Synopsis: space id = P1
+ * If P2 is not 0, use Truncate semantics.
  *
  * Delete all contents of the space, which space id is given
  * in P1 argument. It is worth mentioning, that clearing routine
@@ -4579,7 +4581,13 @@ case OP_Clear: {
 	uint32_t space_id = pOp->p1;
 	struct space *space = space_by_id(space_id);
 	assert(space != NULL);
-	rc = tarantoolSqlite3ClearTable(space);
+	rc = 0;
+	if (pOp->p2 > 0) {
+		if (box_truncate(space_id) != 0)
+			rc = SQL_TARANTOOL_ERROR;
+	} else {
+		rc = tarantoolSqlite3ClearTable(space);
+	}
 	if (rc) goto abort_due_to_error;
 	break;
 }
diff --git a/test/sql/delete.result b/test/sql/delete.result
index c33079c..0ab8c6a 100644
--- a/test/sql/delete.result
+++ b/test/sql/delete.result
@@ -33,6 +33,34 @@ box.sql.execute("SELECT * FROM t1;");
 ---
 - - [2, 4]
 ...
+--
+-- gh-2201: TRUNCATE TABLE operation
+--
+box.sql.execute("TRUNCATE TABLE \"_sql_stat1\";")
+---
+- error: Can't truncate a system space, space '_sql_stat1'
+...
+box.sql.execute("START TRANSACTION")
+---
+...
+box.sql.execute("TRUNCATE TABLE t1;")
+---
+- error: DDL does not support multi-statement transactions
+...
+box.sql.execute("ROLLBACK")
+---
+...
+box.sql.execute("SELECT * FROM t1;")
+---
+- - [2, 4]
+...
+box.sql.execute("TRUNCATE TABLE t1;")
+---
+...
+box.sql.execute("SELECT * FROM t1;")
+---
+- []
+...
 -- Cleanup
 box.sql.execute("DROP TABLE t1;");
 ---
diff --git a/test/sql/delete.test.lua b/test/sql/delete.test.lua
index 1721989..afe843b 100644
--- a/test/sql/delete.test.lua
+++ b/test/sql/delete.test.lua
@@ -21,6 +21,19 @@ box.sql.execute("DELETE FROM t1 WHERE a=1;");
 -- Verify
 box.sql.execute("SELECT * FROM t1;");
 
+--
+-- gh-2201: TRUNCATE TABLE operation
+--
+box.sql.execute("TRUNCATE TABLE \"_sql_stat1\";")
+
+box.sql.execute("START TRANSACTION")
+box.sql.execute("TRUNCATE TABLE t1;")
+box.sql.execute("ROLLBACK")
+box.sql.execute("SELECT * FROM t1;")
+
+box.sql.execute("TRUNCATE TABLE t1;")
+box.sql.execute("SELECT * FROM t1;")
+
 -- Cleanup
 box.sql.execute("DROP TABLE t1;");
 

[tarantool-patches] Re: [PATCH v1 1/1] sql: introduce TRUNCATE TABLE operation

[Kirill Shcherbatov]

Hi! Thank you for you suggestions. I've reworked code.

===================================

We have introduced a new P2 argument for OP_Clear opcode
that calles box_truncate instead of tarantoolSqlite3ClearTable.
Thus, sql_table_delete_from called with is_truncate = true
cause such behavior.

Closes #2201.

@TarantoolBot document
Title: New TRUNCATE operation
Removes all rows from a table or specified partitions of a table,
without logging the individual row deletions.
TRUNCATE TABLE is similar to the DELETE statement with no WHERE
clause; however, TRUNCATE TABLE is faster and uses fewer system
resources.
It couldn't be used with system tables. It couldn't be called in
transaction.
The triggers on target table would not be called.
Example:
TRUNCATE TABLE t1;
---
 extra/mkkeywordhash.c    |  1 +
 src/box/sql/build.c      |  2 +-
 src/box/sql/delete.c     | 12 ++++++++----
 src/box/sql/fkey.c       |  2 +-
 src/box/sql/parse.y      | 11 ++++++++++-
 src/box/sql/sqliteInt.h  |  2 +-
 src/box/sql/trigger.c    |  4 ++--
 src/box/sql/vdbe.c       | 12 ++++++++++--
 test/sql/delete.result   | 28 ++++++++++++++++++++++++++++
 test/sql/delete.test.lua | 17 +++++++++++++++++
 10 files changed, 79 insertions(+), 12 deletions(-)

diff --git a/extra/mkkeywordhash.c b/extra/mkkeywordhash.c
index 1ec1538..705caf1 100644
--- a/extra/mkkeywordhash.c
+++ b/extra/mkkeywordhash.c
@@ -281,6 +281,7 @@ static Keyword aKeywordTable[] = {
   { "VARCHAR",                "TK_ID",          RESERVED,         true  },
   { "WHENEVER",               "TK_STANDARD",    RESERVED,         true  },
   { "WHILE",                  "TK_STANDARD",    RESERVED,         true  },
+  { "TRUNCATE",               "TK_TRUNCATE",    ALWAYS,           true  },
 };
 
 /* Number of keywords */
diff --git a/src/box/sql/build.c b/src/box/sql/build.c
index a64d723..be4c860 100644
--- a/src/box/sql/build.c
+++ b/src/box/sql/build.c
@@ -1953,7 +1953,7 @@ vdbe_emit_stat_space_clear(struct Parse *parse, const char *stat_table_name,
 	 * On memory allocation error sql_table delete_from
 	 * releases memory for its own.
 	 */
-	sql_table_delete_from(parse, src_list, where);
+	sql_table_delete_from(parse, src_list, where, false);
 }
 
 /**
diff --git a/src/box/sql/delete.c b/src/box/sql/delete.c
index f9d3498..f5b010e 100644
--- a/src/box/sql/delete.c
+++ b/src/box/sql/delete.c
@@ -73,7 +73,7 @@ sql_materialize_view(struct Parse *parse, const char *name, struct Expr *where,
 
 void
 sql_table_delete_from(struct Parse *parse, struct SrcList *tab_list,
-		      struct Expr *where)
+		      struct Expr *where, bool is_truncate)
 {
 	struct sqlite3 *db = parse->db;
 	if (parse->nErr || db->mallocFailed)
@@ -183,10 +183,13 @@ sql_table_delete_from(struct Parse *parse, struct SrcList *tab_list,
 	/* Special case: A DELETE without a WHERE clause deletes
 	 * everything. It is easier just to erase the whole table.
 	 */
-	if (where == NULL && !is_complex) {
+	if ((where == NULL && !is_complex) || (is_truncate && !is_view)) {
 		assert(!is_view);
-
-		sqlite3VdbeAddOp1(v, OP_Clear, space_id);
+		if (!is_truncate) {
+			sqlite3VdbeAddOp1(v, OP_Clear, space_id);
+		} else {
+			sqlite3VdbeAddOp2(v, OP_Clear, space_id, true);
+		}
 
 		/* Do not start Tarantool's transaction in case of
 		 * truncate optimization. This is workaround until
@@ -195,6 +198,7 @@ sql_table_delete_from(struct Parse *parse, struct SrcList *tab_list,
 		 */
 		parse->initiateTTrans = false;
 	} else {
+		assert(!is_truncate || (is_truncate && !is_view));
 		/* Resolve the column names in the WHERE clause. */
 		struct NameContext nc;
 		memset(&nc, 0, sizeof(nc));
diff --git a/src/box/sql/fkey.c b/src/box/sql/fkey.c
index 6a91890..e67866c 100644
--- a/src/box/sql/fkey.c
+++ b/src/box/sql/fkey.c
@@ -784,7 +784,7 @@ sqlite3FkDropTable(Parse *parser, SrcList *name, Table *table)
 	/* Staring new transaction before DELETE FROM <tbl> */
 	sqlite3VdbeAddOp0(v, OP_TTransaction);
 	sql_table_delete_from(parser, sqlite3SrcListDup(parser->db, name, 0),
-			      NULL);
+			      NULL, false);
 	parser->disableTriggers = 0;
 	/*
 	 * If the DELETE has generated immediate foreign key
diff --git a/src/box/sql/parse.y b/src/box/sql/parse.y
index 0c510f5..700f40d 100644
--- a/src/box/sql/parse.y
+++ b/src/box/sql/parse.y
@@ -722,7 +722,16 @@ cmd ::= with(C) DELETE FROM fullname(X) indexed_opt(I) where_opt(W). {
   sqlSubProgramsRemaining = SQL_MAX_COMPILING_TRIGGERS;
   /* Instruct SQL to initate Tarantool's transaction.  */
   pParse->initiateTTrans = true;
-  sql_table_delete_from(pParse,X,W);
+  sql_table_delete_from(pParse,X,W,false);
+}
+
+/////////////////////////// The TRUNCATE statement /////////////////////////////
+//
+cmd ::= TRUNCATE TABLE fullname(X). {
+  sqlSubProgramsRemaining = SQL_MAX_COMPILING_TRIGGERS;
+  /* Instruct SQL to initate Tarantool's transaction.  */
+  pParse->initiateTTrans = true;
+  sql_table_delete_from(pParse, X, NULL,true);
 }
 
 %type where_opt {Expr*}
diff --git a/src/box/sql/sqliteInt.h b/src/box/sql/sqliteInt.h
index 54661cb..5eb4825 100644
--- a/src/box/sql/sqliteInt.h
+++ b/src/box/sql/sqliteInt.h
@@ -3703,7 +3703,7 @@ void sqlite3OpenTable(Parse *, int iCur, Table *, int);
  */
 void
 sql_table_delete_from(struct Parse *parse, struct SrcList *tab_list,
-		      struct Expr *where);
+		      struct Expr *where, bool is_truncate);
 
 void sqlite3Update(Parse *, SrcList *, ExprList *, Expr *,
 		   enum on_conflict_action);
diff --git a/src/box/sql/trigger.c b/src/box/sql/trigger.c
index ec0bc98..1949fdb 100644
--- a/src/box/sql/trigger.c
+++ b/src/box/sql/trigger.c
@@ -698,8 +698,8 @@ codeTriggerProgram(Parse * pParse,	/* The parser context */
 						      targetSrcList(pParse, pStep),
 						      sqlite3ExprDup(db,
 								     pStep->pWhere,
-								     0)
-				    );
+								     0),
+						      false);
 				break;
 			}
 		default:
diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c
index f50e389..357beaa 100644
--- a/src/box/sql/vdbe.c
+++ b/src/box/sql/vdbe.c
@@ -39,6 +39,7 @@
  * in this file for details.  If in doubt, do not deviate from existing
  * commenting and indentation practices when changing or adding code.
  */
+#include "box/box.h"
 #include "box/txn.h"
 #include "box/session.h"
 #include "sqliteInt.h"
@@ -4566,8 +4567,9 @@ case OP_IdxGE:  {       /* jump */
 	break;
 }
 
-/* Opcode: Clear P1 * * * *
+/* Opcode: Clear P1 P2 * * *
  * Synopsis: space id = P1
+ * If P2 is not 0, use Truncate semantics.
  *
  * Delete all contents of the space, which space id is given
  * in P1 argument. It is worth mentioning, that clearing routine
@@ -4579,7 +4581,13 @@ case OP_Clear: {
 	uint32_t space_id = pOp->p1;
 	struct space *space = space_by_id(space_id);
 	assert(space != NULL);
-	rc = tarantoolSqlite3ClearTable(space);
+	rc = 0;
+	if (pOp->p2 > 0) {
+		if (box_truncate(space_id) != 0)
+			rc = SQL_TARANTOOL_ERROR;
+	} else {
+		rc = tarantoolSqlite3ClearTable(space);
+	}
 	if (rc) goto abort_due_to_error;
 	break;
 }
diff --git a/test/sql/delete.result b/test/sql/delete.result
index c33079c..0ab8c6a 100644
--- a/test/sql/delete.result
+++ b/test/sql/delete.result
@@ -33,6 +33,34 @@ box.sql.execute("SELECT * FROM t1;");
 ---
 - - [2, 4]
 ...
+--
+-- gh-2201: TRUNCATE TABLE operation
+--
+box.sql.execute("TRUNCATE TABLE \"_sql_stat1\";")
+---
+- error: Can't truncate a system space, space '_sql_stat1'
+...
+box.sql.execute("START TRANSACTION")
+---
+...
+box.sql.execute("TRUNCATE TABLE t1;")
+---
+- error: DDL does not support multi-statement transactions
+...
+box.sql.execute("ROLLBACK")
+---
+...
+box.sql.execute("SELECT * FROM t1;")
+---
+- - [2, 4]
+...
+box.sql.execute("TRUNCATE TABLE t1;")
+---
+...
+box.sql.execute("SELECT * FROM t1;")
+---
+- []
+...
 -- Cleanup
 box.sql.execute("DROP TABLE t1;");
 ---
diff --git a/test/sql/delete.test.lua b/test/sql/delete.test.lua
index 1721989..2a6427f 100644
--- a/test/sql/delete.test.lua
+++ b/test/sql/delete.test.lua
@@ -21,7 +21,24 @@ box.sql.execute("DELETE FROM t1 WHERE a=1;");
 -- Verify
 box.sql.execute("SELECT * FROM t1;");
 
+--
+-- gh-2201: TRUNCATE TABLE operation
+--
+box.sql.execute("TRUNCATE TABLE \"_sql_stat1\";")
+
+box.sql.execute("START TRANSACTION")
+box.sql.execute("TRUNCATE TABLE t1;")
+box.sql.execute("ROLLBACK")
+box.sql.execute("SELECT * FROM t1;")
+
+box.sql.execute("CREATE VIEW v1 AS SELECT * FROM t1;")
+box.sql.execute("TRUNCATE TABLE v1;")
+
+box.sql.execute("TRUNCATE TABLE t1;")
+box.sql.execute("SELECT * FROM t1;")
+
 -- Cleanup
+box.sql.execute("DROP VIEW v1");
 box.sql.execute("DROP TABLE t1;");
 
 -- Debug
-- 
2.7.4

[tarantool-patches] Re: [PATCH v1 1/1] sql: introduce TRUNCATE TABLE operation

[n.pettik]

Firstly, you have partially ignored my comment concerning test:
I still see no tests on table with triggers (including INSTEAD OF on VIEW),
FK constraints. Also I would add few tests checking syntax correctness.

Secondly, AFAIR we discussed that TRUNCATE (until it is DDL) must not have
nothing in common with DELETE. So, implement separate function handling
TRUNCATE from parser. It would provide all necessary checks and emit one opcode:
OP_Truncate (or OP_Clear working on truncate mode AYW).

> --- a/src/box/sql/parse.y
> +++ b/src/box/sql/parse.y
> @@ -722,7 +722,16 @@ cmd ::= with(C) DELETE FROM fullname(X) indexed_opt(I) where_opt(W). {
>   sqlSubProgramsRemaining = SQL_MAX_COMPILING_TRIGGERS;
>   /* Instruct SQL to initate Tarantool's transaction.  */
>   pParse->initiateTTrans = true;
> -  sql_table_delete_from(pParse,X,W);
> +  sql_table_delete_from(pParse,X,W,false);
> +}
> +
> +/////////////////////////// The TRUNCATE statement /////////////////////////////
> +//
> +cmd ::= TRUNCATE TABLE fullname(X). {
> +  sqlSubProgramsRemaining = SQL_MAX_COMPILING_TRIGGERS;

Why is this related to truncate? Truncate and triggers are not compatible.

> +  /* Instruct SQL to initate Tarantool's transaction.  */
> +  pParse->initiateTTrans = true;

Why do you need to start transaction for truncate?

[tarantool-patches] Re: [PATCH v1 1/1] sql: introduce TRUNCATE TABLE operation

[Kirill Shcherbatov]

On 20.07.2018 05:16, n.pettik wrote:
> Firstly, you have partially ignored my comment concerning test:
> I still see no tests on table with triggers (including INSTEAD OF on VIEW),
> FK constraints. Also I would add few tests checking syntax correctness.I've implemented few new tests.

> Secondly, AFAIR we discussed that TRUNCATE (until it is DDL) must not have
> nothing in common with DELETE. So, implement separate function handling
> TRUNCATE from parser. It would provide all necessary checks and emit one opcode:
> OP_Truncate (or OP_Clear working on truncate mode AYW).
I've implemented sql_table_truncate,
> Why is this related to truncate? Truncate and triggers are not compatible.
> Why do you need to start transaction for truncate?
This reworked in new version.


=============================================

We have introduced a new P2 argument for OP_Clear opcode
that calles box_truncate instead of tarantoolSqlite3ClearTable.
Thus, sql_table_delete_from called with is_truncate = true
cause such behavior.

Closes #2201.

@TarantoolBot document
Title: New TRUNCATE operation
Removes all rows from a table or specified partitions of a table,
without logging the individual row deletions.
TRUNCATE TABLE is similar to the DELETE statement with no WHERE
clause; however, TRUNCATE TABLE is faster and uses fewer system
resources.
It couldn't be used with system tables. It couldn't be called in
transaction.
The triggers on target table would not be called.
Example:
TRUNCATE TABLE t1;
---
 extra/mkkeywordhash.c    |  1 +
 src/box/sql/delete.c     | 38 ++++++++++++++++++++++++++
 src/box/sql/parse.y      |  6 +++++
 src/box/sql/sqliteInt.h  | 11 ++++++++
 src/box/sql/vdbe.c       | 12 +++++++--
 test/sql/delete.result   | 70 ++++++++++++++++++++++++++++++++++++++++++++++++
 test/sql/delete.test.lua | 37 +++++++++++++++++++++++++
 7 files changed, 173 insertions(+), 2 deletions(-)

diff --git a/extra/mkkeywordhash.c b/extra/mkkeywordhash.c
index 1ec1538..705caf1 100644
--- a/extra/mkkeywordhash.c
+++ b/extra/mkkeywordhash.c
@@ -281,6 +281,7 @@ static Keyword aKeywordTable[] = {
   { "VARCHAR",                "TK_ID",          RESERVED,         true  },
   { "WHENEVER",               "TK_STANDARD",    RESERVED,         true  },
   { "WHILE",                  "TK_STANDARD",    RESERVED,         true  },
+  { "TRUNCATE",               "TK_TRUNCATE",    ALWAYS,           true  },
 };
 
 /* Number of keywords */
diff --git a/src/box/sql/delete.c b/src/box/sql/delete.c
index f9d3498..e2d321c 100644
--- a/src/box/sql/delete.c
+++ b/src/box/sql/delete.c
@@ -72,6 +72,44 @@ sql_materialize_view(struct Parse *parse, const char *name, struct Expr *where,
 }
 
 void
+sql_table_truncate(struct Parse *parse, struct SrcList *tab_list)
+{
+	struct sqlite3 *db = parse->db;
+	if (parse->nErr || db->mallocFailed)
+		goto cleanup;
+
+	assert(tab_list->nSrc == 1);
+
+	struct Vdbe *v = sqlite3GetVdbe(parse);
+	if (v == NULL)
+		goto cleanup;
+
+	const char *tab_name = tab_list->a->zName;
+	uint32_t space_id = box_space_id_by_name(tab_name, strlen(tab_name));
+	if (space_id == BOX_ID_NIL) {
+		diag_set(ClientError, ER_NO_SUCH_SPACE, tab_name);
+		parse->rc = SQL_TARANTOOL_ERROR;
+		parse->nErr++;
+		goto cleanup;
+	}
+	struct space *space = space_cache_find(space_id);
+	assert(space != NULL);
+	if (space->def->opts.is_view) {
+		const char *err_msg =
+			tt_sprintf("cannot truncate %s because it is a view",
+				   space->def->name);
+		diag_set(ClientError, ER_SQL, err_msg);
+		parse->rc = SQL_TARANTOOL_ERROR;
+		parse->nErr++;
+		goto cleanup;
+	}
+	sqlite3VdbeAddOp2(v, OP_Clear, space_id, true);
+
+cleanup:
+	sqlite3SrcListDelete(parse->db, tab_list);
+}
+
+void
 sql_table_delete_from(struct Parse *parse, struct SrcList *tab_list,
 		      struct Expr *where)
 {
diff --git a/src/box/sql/parse.y b/src/box/sql/parse.y
index 0c510f5..90fe6d4 100644
--- a/src/box/sql/parse.y
+++ b/src/box/sql/parse.y
@@ -725,6 +725,12 @@ cmd ::= with(C) DELETE FROM fullname(X) indexed_opt(I) where_opt(W). {
   sql_table_delete_from(pParse,X,W);
 }
 
+/////////////////////////// The TRUNCATE statement /////////////////////////////
+//
+cmd ::= TRUNCATE TABLE fullname(X). {
+  sql_table_truncate(pParse, X);
+}
+
 %type where_opt {Expr*}
 %destructor where_opt {sql_expr_delete(pParse->db, $$, false);}
 
diff --git a/src/box/sql/sqliteInt.h b/src/box/sql/sqliteInt.h
index 54661cb..1ea3266 100644
--- a/src/box/sql/sqliteInt.h
+++ b/src/box/sql/sqliteInt.h
@@ -3700,11 +3700,22 @@ void sqlite3OpenTable(Parse *, int iCur, Table *, int);
  * @param tab_list List of single element which table from which
  * deletetion if performed.
  * @param where The WHERE clause.  May be NULL.
+ * @param is_truncate use OP_Clear to truncate table.
  */
 void
 sql_table_delete_from(struct Parse *parse, struct SrcList *tab_list,
 		      struct Expr *where);
 
+/**
+ * Generate a code for TRUNCATE TABLE statement.
+ *
+ * @param parse Parsing context.
+ * @param tab_list List of single element which table from which
+ *        truncation is performed.
+ */
+void
+sql_table_truncate(struct Parse *parse, struct SrcList *tab_list);
+
 void sqlite3Update(Parse *, SrcList *, ExprList *, Expr *,
 		   enum on_conflict_action);
 WhereInfo *sqlite3WhereBegin(Parse *, SrcList *, Expr *, ExprList *, ExprList *,
diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c
index f50e389..48238aa 100644
--- a/src/box/sql/vdbe.c
+++ b/src/box/sql/vdbe.c
@@ -39,6 +39,7 @@
  * in this file for details.  If in doubt, do not deviate from existing
  * commenting and indentation practices when changing or adding code.
  */
+#include "box/box.h"
 #include "box/txn.h"
 #include "box/session.h"
 #include "sqliteInt.h"
@@ -4566,8 +4567,9 @@ case OP_IdxGE:  {       /* jump */
 	break;
 }
 
-/* Opcode: Clear P1 * * * *
+/* Opcode: Clear P1 P2 * * *
  * Synopsis: space id = P1
+ * If P2 is not 0, use Truncate semantics.
  *
  * Delete all contents of the space, which space id is given
  * in P1 argument. It is worth mentioning, that clearing routine
@@ -4579,7 +4581,13 @@ case OP_Clear: {
 	uint32_t space_id = pOp->p1;
 	struct space *space = space_by_id(space_id);
 	assert(space != NULL);
-	rc = tarantoolSqlite3ClearTable(space);
+	rc = 0;
+	if (pOp->p2 > 0) {
+		if (box_truncate(space_id) != 0)
+			rc = SQL_TARANTOOL_ERROR;
+	} else {
+		rc = tarantoolSqlite3ClearTable(space);
+	}
 	if (rc) goto abort_due_to_error;
 	break;
 }
diff --git a/test/sql/delete.result b/test/sql/delete.result
index c33079c..38b8d7b 100644
--- a/test/sql/delete.result
+++ b/test/sql/delete.result
@@ -59,3 +59,73 @@ box.sql.execute("INSERT INTO t2 VALUES (0);")
 box.sql.execute("DROP TABLE t2;")
 ---
 ...
+--
+-- gh-2201: TRUNCATE TABLE operation
+--
+-- can't truncate system table
+box.sql.execute("TRUNCATE TABLE \"_sql_stat1\";")
+---
+- error: Can't truncate a system space, space '_sql_stat1'
+...
+box.sql.execute("CREATE TABLE t1(id INTEGER PRIMARY KEY, a, b);")
+---
+...
+box.sql.execute("INSERT INTO t1 VALUES(1, 1, 'one');")
+---
+...
+box.sql.execute("INSERT INTO t1 VALUES(2, 2, 'two');")
+---
+...
+box.sql.execute("CREATE VIEW v1 AS SELECT * FROM t1;")
+---
+...
+box.sql.execute("DROP TRIGGER IF EXISTS trig1;")
+---
+...
+box.sql.execute("CREATE TABLE t2(x PRIMARY KEY);")
+---
+...
+box.sql.execute("CREATE TRIGGER trig1 INSTEAD OF UPDATE ON v1 BEGIN INSERT INTO t2 VALUES(old.a); END;")
+---
+...
+-- can't truncate in transaction
+box.sql.execute("START TRANSACTION")
+---
+...
+box.sql.execute("TRUNCATE TABLE t1;")
+---
+- error: DDL does not support multi-statement transactions
+...
+box.sql.execute("ROLLBACK")
+---
+...
+-- can't truncate view
+box.sql.execute("TRUNCATE TABLE v1;")
+---
+- error: 'SQL error: cannot modify V1 because it is a view'
+...
+-- table triggers should be ignered
+box.sql.execute("CREATE TRIGGER trig2 BEFORE UPDATE ON t1 BEGIN INSERT INTO t2 VALUES(old.x); END;")
+---
+...
+box.sql.execute("TRUNCATE TABLE t1;")
+---
+...
+box.sql.execute("SELECT * FROM t1;")
+---
+- []
+...
+box.sql.execute("SELECT * FROM t2;")
+---
+- []
+...
+-- Cleanup
+box.sql.execute("DROP VIEW v1");
+---
+...
+box.sql.execute("DROP TABLE t1;")
+---
+...
+box.sql.execute("DROP TABLE t2;")
+---
+...
diff --git a/test/sql/delete.test.lua b/test/sql/delete.test.lua
index 1721989..541bd82 100644
--- a/test/sql/delete.test.lua
+++ b/test/sql/delete.test.lua
@@ -37,3 +37,40 @@ box.sql.execute("CREATE TRIGGER t2 BEFORE INSERT ON t2 BEGIN DELETE FROM t1; END
 box.sql.execute("INSERT INTO t2 VALUES (0);")
 
 box.sql.execute("DROP TABLE t2;")
+
+
+--
+-- gh-2201: TRUNCATE TABLE operation
+--
+
+-- can't truncate system table
+box.sql.execute("TRUNCATE TABLE \"_sql_stat1\";")
+
+box.sql.execute("CREATE TABLE t1(id INTEGER PRIMARY KEY, a, b);")
+box.sql.execute("INSERT INTO t1 VALUES(1, 1, 'one');")
+box.sql.execute("INSERT INTO t1 VALUES(2, 2, 'two');")
+
+box.sql.execute("CREATE VIEW v1 AS SELECT * FROM t1;")
+box.sql.execute("DROP TRIGGER IF EXISTS trig1;")
+
+box.sql.execute("CREATE TABLE t2(x PRIMARY KEY);")
+box.sql.execute("CREATE TRIGGER trig1 INSTEAD OF UPDATE ON v1 BEGIN INSERT INTO t2 VALUES(old.a); END;")
+
+-- can't truncate in transaction
+box.sql.execute("START TRANSACTION")
+box.sql.execute("TRUNCATE TABLE t1;")
+box.sql.execute("ROLLBACK")
+
+-- can't truncate view
+box.sql.execute("TRUNCATE TABLE v1;")
+
+-- table triggers should be ignered
+box.sql.execute("CREATE TRIGGER trig2 BEFORE UPDATE ON t1 BEGIN INSERT INTO t2 VALUES(old.x); END;")
+box.sql.execute("TRUNCATE TABLE t1;")
+box.sql.execute("SELECT * FROM t1;")
+box.sql.execute("SELECT * FROM t2;")
+
+-- Cleanup
+box.sql.execute("DROP VIEW v1");
+box.sql.execute("DROP TABLE t1;")
+box.sql.execute("DROP TABLE t2;")
-- 
2.7.4

[tarantool-patches] Re: [PATCH v1 1/1] sql: introduce TRUNCATE TABLE operation

[n.pettik]

Hello.

I still don’t see how truncate interacts with FK constraints
and appropriate tests on it. Also, you should test not UPDATE
trigger, but DELETE.

[tarantool-patches] Re: [PATCH v1 1/1] sql: introduce TRUNCATE TABLE operation

[Kirill Shcherbatov]

> I still don’t see how truncate interacts with FK constraints
> and appropriate tests on it. Also, you should test not UPDATE
> trigger, but DELETE.Hi! Thank you for review.
I've supported FK checks and rework tests a bit.

====================================

diff --git a/extra/mkkeywordhash.c b/extra/mkkeywordhash.c
index 1ec1538..705caf1 100644
--- a/extra/mkkeywordhash.c
+++ b/extra/mkkeywordhash.c
@@ -281,6 +281,7 @@ static Keyword aKeywordTable[] = {
   { "VARCHAR",                "TK_ID",          RESERVED,         true  },
   { "WHENEVER",               "TK_STANDARD",    RESERVED,         true  },
   { "WHILE",                  "TK_STANDARD",    RESERVED,         true  },
+  { "TRUNCATE",               "TK_TRUNCATE",    ALWAYS,           true  },
 };
 
 /* Number of keywords */
diff --git a/src/box/sql/delete.c b/src/box/sql/delete.c
index 0681177..b016084 100644
--- a/src/box/sql/delete.c
+++ b/src/box/sql/delete.c
@@ -72,6 +72,62 @@ sql_materialize_view(struct Parse *parse, const char *name, struct Expr *where,
 }
 
 void
+sql_table_truncate(struct Parse *parse, struct SrcList *tab_list)
+{
+	struct sqlite3 *db = parse->db;
+	if (parse->nErr || db->mallocFailed)
+		goto cleanup;
+	assert(tab_list->nSrc == 1);
+
+	struct Vdbe *v = sqlite3GetVdbe(parse);
+	if (v == NULL)
+		goto cleanup;
+
+	const char *tab_name = tab_list->a->zName;
+	struct Table *table = sqlite3LocateTable(parse, LOCATE_NOERR, tab_name);
+	struct space_def *space_def = NULL;
+	if (table == NULL) {
+		/* Space created with LUA. */
+		uint32_t space_id =
+			box_space_id_by_name(tab_name, strlen(tab_name));
+		if (space_id == BOX_ID_NIL) {
+			diag_set(ClientError, ER_NO_SUCH_SPACE, tab_name);
+			parse->rc = SQL_TARANTOOL_ERROR;
+			parse->nErr++;
+			goto cleanup;
+		}
+		struct space *space = space_cache_find(space_id);
+		assert(space != NULL);
+		space_def = space->def;
+	} else {
+		space_def = table->def;
+		if (sqlite3FkRequired(table, NULL) != 0) {
+			const char *err_msg =
+				tt_sprintf("cannot truncate %s because it has "
+					   "foreign keys");
+			diag_set(ClientError, ER_SQL, err_msg);
+			parse->rc = SQL_TARANTOOL_ERROR;
+			parse->nErr++;
+			goto cleanup;
+		}
+	}
+	assert(space_def != NULL);
+	if (space_def->opts.is_view) {
+		const char *err_msg =
+			tt_sprintf("cannot truncate %s because it is a view",
+				   space_def->name);
+		diag_set(ClientError, ER_SQL, err_msg);
+		parse->rc = SQL_TARANTOOL_ERROR;
+		parse->nErr++;
+		goto cleanup;
+	}
+	sqlite3VdbeAddOp2(v, OP_Clear, space_def->id, true);
+
+cleanup:
+	sqlite3SrcListDelete(parse->db, tab_list);
+}
+
+void
 sql_table_delete_from(struct Parse *parse, struct SrcList *tab_list,
 		      struct Expr *where)
 {
diff --git a/src/box/sql/parse.y b/src/box/sql/parse.y
index 0c510f5..90fe6d4 100644
--- a/src/box/sql/parse.y
+++ b/src/box/sql/parse.y
@@ -725,6 +725,12 @@ cmd ::= with(C) DELETE FROM fullname(X) indexed_opt(I) where_opt(W). {
   sql_table_delete_from(pParse,X,W);
 }
 
+/////////////////////////// The TRUNCATE statement /////////////////////////////
+//
+cmd ::= TRUNCATE TABLE fullname(X). {
+  sql_table_truncate(pParse, X);
+}
+
 %type where_opt {Expr*}
 %destructor where_opt {sql_expr_delete(pParse->db, $$, false);}
 
diff --git a/src/box/sql/sqliteInt.h b/src/box/sql/sqliteInt.h
index 07e7829..a90b10e 100644
--- a/src/box/sql/sqliteInt.h
+++ b/src/box/sql/sqliteInt.h
@@ -3710,11 +3710,22 @@ void sqlite3OpenTable(Parse *, int iCur, Table *, int);
  * @param tab_list List of single element which table from which
  * deletetion if performed.
  * @param where The WHERE clause.  May be NULL.
+ * @param is_truncate use OP_Clear to truncate table.
  */
 void
 sql_table_delete_from(struct Parse *parse, struct SrcList *tab_list,
 		      struct Expr *where);
 
+/**
+ * Generate a code for TRUNCATE TABLE statement.
+ *
+ * @param parse Parsing context.
+ * @param tab_list List of single element which table from which
+ *        truncation is performed.
+ */
+void
+sql_table_truncate(struct Parse *parse, struct SrcList *tab_list);
+
 void sqlite3Update(Parse *, SrcList *, ExprList *, Expr *,
 		   enum on_conflict_action);
 WhereInfo *sqlite3WhereBegin(Parse *, SrcList *, Expr *, ExprList *, ExprList *,
diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c
index 1a75f77..dc22c26 100644
--- a/src/box/sql/vdbe.c
+++ b/src/box/sql/vdbe.c
@@ -39,6 +39,7 @@
  * in this file for details.  If in doubt, do not deviate from existing
  * commenting and indentation practices when changing or adding code.
  */
+#include "box/box.h"
 #include "box/txn.h"
 #include "box/session.h"
 #include "sqliteInt.h"
@@ -4562,8 +4563,9 @@ case OP_IdxGE:  {       /* jump */
 	break;
 }
 
-/* Opcode: Clear P1 * * * *
+/* Opcode: Clear P1 P2 * * *
  * Synopsis: space id = P1
+ * If P2 is not 0, use Truncate semantics.
  *
  * Delete all contents of the space, which space id is given
  * in P1 argument. It is worth mentioning, that clearing routine
@@ -4575,7 +4577,13 @@ case OP_Clear: {
 	uint32_t space_id = pOp->p1;
 	struct space *space = space_by_id(space_id);
 	assert(space != NULL);
-	rc = tarantoolSqlite3ClearTable(space);
+	rc = 0;
+	if (pOp->p2 > 0) {
+		if (box_truncate(space_id) != 0)
+			rc = SQL_TARANTOOL_ERROR;
+	} else {
+		rc = tarantoolSqlite3ClearTable(space);
+	}
 	if (rc) goto abort_due_to_error;
 	break;
 }
diff --git a/test/sql/delete.result b/test/sql/delete.result
index c33079c..39f7345 100644
--- a/test/sql/delete.result
+++ b/test/sql/delete.result
@@ -59,3 +59,82 @@ box.sql.execute("INSERT INTO t2 VALUES (0);")
 box.sql.execute("DROP TABLE t2;")
 ---
 ...
+--
+-- gh-2201: TRUNCATE TABLE operation
+--
+-- can't truncate system table
+box.sql.execute("TRUNCATE TABLE \"_sql_stat1\";")
+---
+- error: Can't truncate a system space, space '_sql_stat1'
+...
+box.sql.execute("CREATE TABLE t1(id INTEGER PRIMARY KEY, a, b);")
+---
+...
+box.sql.execute("INSERT INTO t1 VALUES(1, 1, 'one');")
+---
+...
+box.sql.execute("INSERT INTO t1 VALUES(2, 2, 'two');")
+---
+...
+-- can't truncate in transaction
+box.sql.execute("START TRANSACTION")
+---
+...
+box.sql.execute("TRUNCATE TABLE t1;")
+---
+- error: DDL does not support multi-statement transactions
+...
+box.sql.execute("ROLLBACK")
+---
+...
+-- can't truncate view
+box.sql.execute("CREATE VIEW v1 AS SELECT * FROM t1;")
+---
+...
+box.sql.execute("TRUNCATE TABLE v1;")
+---
+- error: 'SQL error: cannot truncate V1 because it is a view'
+...
+-- can't truncate table with FK
+box.sql.execute("CREATE TABLE t2(x PRIMARY KEY REFERENCES t1(id));")
+---
+...
+box.sql.execute("TRUNCATE TABLE t2;")
+---
+- error: 'SQL error: cannot truncate T2 because it has foreign keys'
+...
+box.sql.execute("TRUNCATE TABLE t1;")
+---
+- error: 'SQL error: cannot truncate T1 because it has foreign keys'
+...
+-- table triggers should be ignored
+box.sql.execute("DROP TABLE t2;")
+---
+...
+box.sql.execute("CREATE TABLE t2(x PRIMARY KEY);")
+---
+...
+box.sql.execute("CREATE TRIGGER trig2 BEFORE DELETE ON t1 BEGIN INSERT INTO t2 VALUES(old.x); END;")
+---
+...
+box.sql.execute("TRUNCATE TABLE t1;")
+---
+...
+box.sql.execute("SELECT * FROM t1;")
+---
+- []
+...
+box.sql.execute("SELECT * FROM t2;")
+---
+- []
+...
+-- Cleanup
+box.sql.execute("DROP VIEW v1");
+---
+...
+box.sql.execute("DROP TABLE t1;")
+---
+...
+box.sql.execute("DROP TABLE t2;")
+---
+...
diff --git a/test/sql/delete.test.lua b/test/sql/delete.test.lua
index 1721989..5e24689 100644
--- a/test/sql/delete.test.lua
+++ b/test/sql/delete.test.lua
@@ -37,3 +37,42 @@ box.sql.execute("CREATE TRIGGER t2 BEFORE INSERT ON t2 BEGIN DELETE FROM t1; END
 box.sql.execute("INSERT INTO t2 VALUES (0);")
 
 box.sql.execute("DROP TABLE t2;")
+
+
+--
+-- gh-2201: TRUNCATE TABLE operation
+--
+
+-- can't truncate system table
+box.sql.execute("TRUNCATE TABLE \"_sql_stat1\";")
+
+box.sql.execute("CREATE TABLE t1(id INTEGER PRIMARY KEY, a, b);")
+box.sql.execute("INSERT INTO t1 VALUES(1, 1, 'one');")
+box.sql.execute("INSERT INTO t1 VALUES(2, 2, 'two');")
+
+-- can't truncate in transaction
+box.sql.execute("START TRANSACTION")
+box.sql.execute("TRUNCATE TABLE t1;")
+box.sql.execute("ROLLBACK")
+
+-- can't truncate view
+box.sql.execute("CREATE VIEW v1 AS SELECT * FROM t1;")
+box.sql.execute("TRUNCATE TABLE v1;")
+
+-- can't truncate table with FK
+box.sql.execute("CREATE TABLE t2(x PRIMARY KEY REFERENCES t1(id));")
+box.sql.execute("TRUNCATE TABLE t2;")
+box.sql.execute("TRUNCATE TABLE t1;")
+
+-- table triggers should be ignored
+box.sql.execute("DROP TABLE t2;")
+box.sql.execute("CREATE TABLE t2(x PRIMARY KEY);")
+box.sql.execute("CREATE TRIGGER trig2 BEFORE DELETE ON t1 BEGIN INSERT INTO t2 VALUES(old.x); END;")
+box.sql.execute("TRUNCATE TABLE t1;")
+box.sql.execute("SELECT * FROM t1;")
+box.sql.execute("SELECT * FROM t2;")
+
+-- Cleanup
+box.sql.execute("DROP VIEW v1");
+box.sql.execute("DROP TABLE t1;")
+box.sql.execute("DROP TABLE t2;")

[tarantool-patches] Re: [PATCH v1 1/1] sql: introduce TRUNCATE TABLE operation

[n.pettik]

Except for comments below, also pls add to tarantool-doc bot request
notice concerning the fact that in our implementation TRUNCATE is DDL operation.


> diff --git a/extra/mkkeywordhash.c b/extra/mkkeywordhash.c
> index 1ec1538..705caf1 100644
> --- a/extra/mkkeywordhash.c
> +++ b/extra/mkkeywordhash.c
> @@ -281,6 +281,7 @@ static Keyword aKeywordTable[] = {
>   { "VARCHAR",                "TK_ID",          RESERVED,         true  },
>   { "WHENEVER",               "TK_STANDARD",    RESERVED,         true  },
>   { "WHILE",                  "TK_STANDARD",    RESERVED,         true  },
> +  { "TRUNCATE",               "TK_TRUNCATE",    ALWAYS,           true  },
> };
> 
> /* Number of keywords */
> diff --git a/src/box/sql/delete.c b/src/box/sql/delete.c
> index 0681177..b016084 100644
> --- a/src/box/sql/delete.c
> +++ b/src/box/sql/delete.c
> @@ -72,6 +72,62 @@ sql_materialize_view(struct Parse *parse, const char *name, struct Expr *where,
> }
> 
> void
> +sql_table_truncate(struct Parse *parse, struct SrcList *tab_list)
> +{
> +	struct sqlite3 *db = parse->db;
> +	if (parse->nErr || db->mallocFailed)
> +		goto cleanup;

Is this check reasonable?

> +	assert(tab_list->nSrc == 1);
> +
> +	struct Vdbe *v = sqlite3GetVdbe(parse);
> +	if (v == NULL)
> +		goto cleanup;
> +
> +	const char *tab_name = tab_list->a->zName;
> +	struct Table *table = sqlite3LocateTable(parse, LOCATE_NOERR, tab_name);

I guess here you can explicitly use space_by_id and box_space_id_by_name
to bypass table hash. If you do so, you will be able to get rid off 
if (table == NULL) branch.

> +	struct space_def *space_def = NULL;
> +	if (table == NULL) {
> +		/* Space created with LUA. */
> +		uint32_t space_id =
> +			box_space_id_by_name(tab_name, strlen(tab_name));
> +		if (space_id == BOX_ID_NIL) {
> +			diag_set(ClientError, ER_NO_SUCH_SPACE, tab_name);
> +			parse->rc = SQL_TARANTOOL_ERROR;
> +			parse->nErr++;

During exception handling you three times process
parse->rc = SQL_TARANTOOL_ERROR;
parse->nErr++;

Lets use separate label for this purpose.

> +			goto cleanup;
> +		}
> +		struct space *space = space_cache_find(space_id);
> +		assert(space != NULL);
> +		space_def = space->def;
> +	} else {
> +		space_def = table->def;
> +		if (sqlite3FkRequired(table, NULL) != 0) {

sqlite3FkRequired is too strict condition. I guess you are still
capable of truncating child (or referencing) table, since no
FK violations may take place in this case.

So, what you need is simple check:

sqlite3References() != NULL;

> +			const char *err_msg =
> +				tt_sprintf("cannot truncate %s because it has "
> +					   "foreign keys”);

Lets add ’space’ or ’table' word to message to avoid confusing:

“can no truncate table %s becase other objects depend on it”
OR
“can no truncate table %s becase other objects reference it”

> +			diag_set(ClientError, ER_SQL, err_msg);
> +			parse->rc = SQL_TARANTOOL_ERROR;
> +			parse->nErr++;
> +			goto cleanup;
> +		}
> +	}
> +	assert(space_def != NULL);
> +	if (space_def->opts.is_view) {
> +		const char *err_msg =
> +			tt_sprintf("cannot truncate %s because it is a view",
> +				   space_def->name);
> +		diag_set(ClientError, ER_SQL, err_msg);
> +		parse->rc = SQL_TARANTOOL_ERROR;
> +		parse->nErr++;
> +		goto cleanup;
> +	}
> +	sqlite3VdbeAddOp2(v, OP_Clear, space_def->id, true);
> +
> +cleanup:
> +	sqlite3SrcListDelete(parse->db, tab_list);
> +}
> +
> +void
> sql_table_delete_from(struct Parse *parse, struct SrcList *tab_list,
> 		      struct Expr *where)
> {
> 
> diff --git a/src/box/sql/sqliteInt.h b/src/box/sql/sqliteInt.h
> index 07e7829..a90b10e 100644
> --- a/src/box/sql/sqliteInt.h
> +++ b/src/box/sql/sqliteInt.h
> @@ -3710,11 +3710,22 @@ void sqlite3OpenTable(Parse *, int iCur, Table *, int);
>  * @param tab_list List of single element which table from which
>  * deletetion if performed.
>  * @param where The WHERE clause.  May be NULL.
> + * @param is_truncate use OP_Clear to truncate table.

This comment is remained from previous patch version, I guess.

> --- a/test/sql/delete.test.lua
> +++ b/test/sql/delete.test.lua
> @@ -37,3 +37,42 @@ box.sql.execute("CREATE TRIGGER t2 BEFORE INSERT ON t2 BEGIN DELETE FROM t1; END
> box.sql.execute("INSERT INTO t2 VALUES (0);")
> 
> box.sql.execute("DROP TABLE t2;")
> +
> +
> +--
> +-- gh-2201: TRUNCATE TABLE operation
> +--
> +
> +-- can't truncate system table

Pls start comments (even in tests) from capital letter and end with dot.

> +box.sql.execute("TRUNCATE TABLE \"_sql_stat1\";")
> +
> +box.sql.execute("CREATE TABLE t1(id INTEGER PRIMARY KEY, a, b);”)

Specify types for all columns. Static typing is on the way.

[tarantool-patches] Re: [PATCH v1 1/1] sql: introduce TRUNCATE TABLE operation

[Kirill Shcherbatov]

> Except for comments below, also pls add to tarantool-doc bot request
> notice concerning the fact that in our implementation TRUNCATE is DDL operation.
Ok, done.

> Is this check reasonable?
Not really. Dropped.
> I guess here you can explicitly use space_by_id and box_space_id_by_name
> to bypass table hash. If you do so, you will be able to get rid off 
> if (table == NULL) branch.
done. made this place a little easy to refactor.

> Lets use separate label for this purpose.
Ok, done. It's looking a bit ugly for me..;  


> sqlite3FkRequired is too strict condition. I guess you are still
> capable of truncating child (or referencing) table, since no
> FK violations may take place in this case.
> So, what you need is simple check:
> sqlite3References() != NULL;
Ok, done.

> Lets add ’space’ or ’table' word to message to avoid confusing:
Done.

> This comment is remained from previous patch version, I guess.
You are right.

> Pls start comments (even in tests) from capital letter and end with dot.
> Specify types for all columns. Static typing is on the way.
Fixed.

=============================================


To implement new TRUNCATE operation, we have introduced a
new P2 argument for OP_Clear opcode that calles box_truncate
instead of tarantoolSqlite3ClearTable.
This operation should work faster than DELETE FROM; but have
a few restricts.

Closes #2201.

@TarantoolBot document
Title: New TRUNCATE operation
TRUNCATE is DDL operation.
Removes all rows from a table or specified partitions of a table,
without logging the individual row deletions.
TRUNCATE TABLE is similar to the DELETE statement with no WHERE
clause; however, TRUNCATE TABLE is faster and uses fewer system
resources.
It couldn't be used with system tables or with tables having FKs.
It also couldn't be called in transaction.
The triggers on table will have ignored.
Example:
TRUNCATE TABLE t1;
---
 extra/mkkeywordhash.c    |  1 +
 src/box/sql/delete.c     | 48 +++++++++++++++++++++++++++++++
 src/box/sql/parse.y      |  6 ++++
 src/box/sql/sqliteInt.h  | 10 +++++++
 src/box/sql/vdbe.c       | 12 ++++++--
 test/sql/delete.result   | 75 ++++++++++++++++++++++++++++++++++++++++++++++++
 test/sql/delete.test.lua | 38 ++++++++++++++++++++++++
 7 files changed, 188 insertions(+), 2 deletions(-)

diff --git a/extra/mkkeywordhash.c b/extra/mkkeywordhash.c
index 1ec1538..705caf1 100644
--- a/extra/mkkeywordhash.c
+++ b/extra/mkkeywordhash.c
@@ -281,6 +281,7 @@ static Keyword aKeywordTable[] = {
   { "VARCHAR",                "TK_ID",          RESERVED,         true  },
   { "WHENEVER",               "TK_STANDARD",    RESERVED,         true  },
   { "WHILE",                  "TK_STANDARD",    RESERVED,         true  },
+  { "TRUNCATE",               "TK_TRUNCATE",    ALWAYS,           true  },
 };
 
 /* Number of keywords */
diff --git a/src/box/sql/delete.c b/src/box/sql/delete.c
index 0681177..0809b35 100644
--- a/src/box/sql/delete.c
+++ b/src/box/sql/delete.c
@@ -72,6 +72,54 @@ sql_materialize_view(struct Parse *parse, const char *name, struct Expr *where,
 }
 
 void
+sql_table_truncate(struct Parse *parse, struct SrcList *tab_list)
+{
+	assert(tab_list->nSrc == 1);
+
+	struct Vdbe *v = sqlite3GetVdbe(parse);
+	if (v == NULL)
+		goto cleanup;
+
+	const char *tab_name = tab_list->a->zName;
+	struct space_def *space_def = NULL;
+	uint32_t space_id =
+		box_space_id_by_name(tab_name, strlen(tab_name));
+	if (space_id == BOX_ID_NIL) {
+		diag_set(ClientError, ER_NO_SUCH_SPACE, tab_name);
+		goto tarantool_error;
+	}
+	struct space *space = space_cache_find(space_id);
+	assert(space != NULL);
+	space_def = space->def;
+
+	struct Table *table = sqlite3LocateTable(parse, LOCATE_NOERR, tab_name);
+	if (table != NULL && sqlite3FkReferences(table) != NULL) {
+		const char *err_msg =
+			tt_sprintf("can no truncate space %s because other "
+				   "objects depend on it", space_def->name);
+		diag_set(ClientError, ER_SQL, err_msg);
+		goto tarantool_error;
+	}
+	if (space_def->opts.is_view) {
+		const char *err_msg =
+			tt_sprintf("can no truncate space %s because it is a "
+				   "view", space_def->name);
+		diag_set(ClientError, ER_SQL, err_msg);
+		goto tarantool_error;
+	}
+	sqlite3VdbeAddOp2(v, OP_Clear, space_def->id, true);
+
+cleanup:
+	sqlite3SrcListDelete(parse->db, tab_list);
+	return;
+
+tarantool_error:
+	sqlite3SrcListDelete(parse->db, tab_list);
+	parse->rc = SQL_TARANTOOL_ERROR;
+	parse->nErr++;
+}
+
+void
 sql_table_delete_from(struct Parse *parse, struct SrcList *tab_list,
 		      struct Expr *where)
 {
diff --git a/src/box/sql/parse.y b/src/box/sql/parse.y
index 71518e0..0e50d21 100644
--- a/src/box/sql/parse.y
+++ b/src/box/sql/parse.y
@@ -733,6 +733,12 @@ cmd ::= with(C) DELETE FROM fullname(X) indexed_opt(I) where_opt(W). {
   sql_table_delete_from(pParse,X,W);
 }
 
+/////////////////////////// The TRUNCATE statement /////////////////////////////
+//
+cmd ::= TRUNCATE TABLE fullname(X). {
+  sql_table_truncate(pParse, X);
+}
+
 %type where_opt {Expr*}
 %destructor where_opt {sql_expr_delete(pParse->db, $$, false);}
 
diff --git a/src/box/sql/sqliteInt.h b/src/box/sql/sqliteInt.h
index 8cc2288..56c8899 100644
--- a/src/box/sql/sqliteInt.h
+++ b/src/box/sql/sqliteInt.h
@@ -3719,6 +3719,16 @@ void
 sql_table_delete_from(struct Parse *parse, struct SrcList *tab_list,
 		      struct Expr *where);
 
+/**
+ * Generate a code for TRUNCATE TABLE statement.
+ *
+ * @param parse Parsing context.
+ * @param tab_list List of single element which table from which
+ *        truncation is performed.
+ */
+void
+sql_table_truncate(struct Parse *parse, struct SrcList *tab_list);
+
 void sqlite3Update(Parse *, SrcList *, ExprList *, Expr *,
 		   enum on_conflict_action);
 WhereInfo *sqlite3WhereBegin(Parse *, SrcList *, Expr *, ExprList *, ExprList *,
diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c
index 1a75f77..dc22c26 100644
--- a/src/box/sql/vdbe.c
+++ b/src/box/sql/vdbe.c
@@ -39,6 +39,7 @@
  * in this file for details.  If in doubt, do not deviate from existing
  * commenting and indentation practices when changing or adding code.
  */
+#include "box/box.h"
 #include "box/txn.h"
 #include "box/session.h"
 #include "sqliteInt.h"
@@ -4562,8 +4563,9 @@ case OP_IdxGE:  {       /* jump */
 	break;
 }
 
-/* Opcode: Clear P1 * * * *
+/* Opcode: Clear P1 P2 * * *
  * Synopsis: space id = P1
+ * If P2 is not 0, use Truncate semantics.
  *
  * Delete all contents of the space, which space id is given
  * in P1 argument. It is worth mentioning, that clearing routine
@@ -4575,7 +4577,13 @@ case OP_Clear: {
 	uint32_t space_id = pOp->p1;
 	struct space *space = space_by_id(space_id);
 	assert(space != NULL);
-	rc = tarantoolSqlite3ClearTable(space);
+	rc = 0;
+	if (pOp->p2 > 0) {
+		if (box_truncate(space_id) != 0)
+			rc = SQL_TARANTOOL_ERROR;
+	} else {
+		rc = tarantoolSqlite3ClearTable(space);
+	}
 	if (rc) goto abort_due_to_error;
 	break;
 }
diff --git a/test/sql/delete.result b/test/sql/delete.result
index c33079c..d06907c 100644
--- a/test/sql/delete.result
+++ b/test/sql/delete.result
@@ -59,3 +59,78 @@ box.sql.execute("INSERT INTO t2 VALUES (0);")
 box.sql.execute("DROP TABLE t2;")
 ---
 ...
+--
+-- gh-2201: TRUNCATE TABLE operation.
+--
+-- can't truncate system table.
+box.sql.execute("TRUNCATE TABLE \"_sql_stat1\";")
+---
+- error: Can't truncate a system space, space '_sql_stat1'
+...
+box.sql.execute("CREATE TABLE t1(id INT PRIMARY KEY, a INT, b STR);")
+---
+...
+box.sql.execute("INSERT INTO t1 VALUES(1, 1, 'one');")
+---
+...
+box.sql.execute("INSERT INTO t1 VALUES(2, 2, 'two');")
+---
+...
+-- Can't truncate in transaction.
+box.sql.execute("START TRANSACTION")
+---
+...
+box.sql.execute("TRUNCATE TABLE t1;")
+---
+- error: DDL does not support multi-statement transactions
+...
+box.sql.execute("ROLLBACK")
+---
+...
+-- Can't truncate view.
+box.sql.execute("CREATE VIEW v1 AS SELECT * FROM t1;")
+---
+...
+box.sql.execute("TRUNCATE TABLE v1;")
+---
+- error: 'SQL error: can no truncate space V1 because it is a view'
+...
+-- Can't truncate table with FK.
+box.sql.execute("CREATE TABLE t2(x INT PRIMARY KEY REFERENCES t1(id));")
+---
+...
+box.sql.execute("TRUNCATE TABLE t1;")
+---
+- error: 'SQL error: can no truncate space T1 because other objects depend on it'
+...
+-- Table triggers should be ignored.
+box.sql.execute("DROP TABLE t2;")
+---
+...
+box.sql.execute("CREATE TABLE t2(x INT PRIMARY KEY);")
+---
+...
+box.sql.execute("CREATE TRIGGER trig2 BEFORE DELETE ON t1 BEGIN INSERT INTO t2 VALUES(old.x); END;")
+---
+...
+box.sql.execute("TRUNCATE TABLE t1;")
+---
+...
+box.sql.execute("SELECT * FROM t1;")
+---
+- []
+...
+box.sql.execute("SELECT * FROM t2;")
+---
+- []
+...
+-- Cleanup.
+box.sql.execute("DROP VIEW v1");
+---
+...
+box.sql.execute("DROP TABLE t1;")
+---
+...
+box.sql.execute("DROP TABLE t2;")
+---
+...
diff --git a/test/sql/delete.test.lua b/test/sql/delete.test.lua
index 1721989..0477d22 100644
--- a/test/sql/delete.test.lua
+++ b/test/sql/delete.test.lua
@@ -37,3 +37,41 @@ box.sql.execute("CREATE TRIGGER t2 BEFORE INSERT ON t2 BEGIN DELETE FROM t1; END
 box.sql.execute("INSERT INTO t2 VALUES (0);")
 
 box.sql.execute("DROP TABLE t2;")
+
+
+--
+-- gh-2201: TRUNCATE TABLE operation.
+--
+
+-- can't truncate system table.
+box.sql.execute("TRUNCATE TABLE \"_sql_stat1\";")
+
+box.sql.execute("CREATE TABLE t1(id INT PRIMARY KEY, a INT, b STR);")
+box.sql.execute("INSERT INTO t1 VALUES(1, 1, 'one');")
+box.sql.execute("INSERT INTO t1 VALUES(2, 2, 'two');")
+
+-- Can't truncate in transaction.
+box.sql.execute("START TRANSACTION")
+box.sql.execute("TRUNCATE TABLE t1;")
+box.sql.execute("ROLLBACK")
+
+-- Can't truncate view.
+box.sql.execute("CREATE VIEW v1 AS SELECT * FROM t1;")
+box.sql.execute("TRUNCATE TABLE v1;")
+
+-- Can't truncate table with FK.
+box.sql.execute("CREATE TABLE t2(x INT PRIMARY KEY REFERENCES t1(id));")
+box.sql.execute("TRUNCATE TABLE t1;")
+
+-- Table triggers should be ignored.
+box.sql.execute("DROP TABLE t2;")
+box.sql.execute("CREATE TABLE t2(x INT PRIMARY KEY);")
+box.sql.execute("CREATE TRIGGER trig2 BEFORE DELETE ON t1 BEGIN INSERT INTO t2 VALUES(old.x); END;")
+box.sql.execute("TRUNCATE TABLE t1;")
+box.sql.execute("SELECT * FROM t1;")
+box.sql.execute("SELECT * FROM t2;")
+
+-- Cleanup.
+box.sql.execute("DROP VIEW v1");
+box.sql.execute("DROP TABLE t1;")
+box.sql.execute("DROP TABLE t2;")
-- 
2.7.4

[tarantool-patches] Re: [PATCH v1 1/1] sql: introduce TRUNCATE TABLE operation

[n.pettik]

Thx, now patch looks OK to me.

[tarantool-patches] Re: [PATCH v1 1/1] sql: introduce TRUNCATE TABLE operation

[Vladislav Shpilevoy]

Hi! Thanks for the patch! I have pushed my review fixes on
the branch. Please, squash.

On 18/07/2018 11:22, Kirill Shcherbatov wrote:
> As sql_table_delete_from already support OP_Clear fast cleanup
> for non-complex requests we only need to introduce new words
> to parser that call DELETE with NULL conditions.
> 
> Closes #2201.
> ---
> Branch: http://github.com/tarantool/tarantool/tree/kshch/gh-2201-truncate-table
> Issue: https://github.com/tarantool/tarantool/issues/2201
> 

[tarantool-patches] Re: [PATCH v1 1/1] sql: introduce TRUNCATE TABLE operation

[Kirill Shcherbatov]

> Hi! Thanks for the patch! I have pushed my review fixes on
> the branch. Please, squash.
Hi! Thank you for review. Your fixes are looking good for me, squashed.

[tarantool-patches] Re: [PATCH v1 1/1] sql: introduce TRUNCATE TABLE operation

[Kirill Yukhin]

Hello,
On 18 июл 11:22, Kirill Shcherbatov wrote:
> As sql_table_delete_from already support OP_Clear fast cleanup
> for non-complex requests we only need to introduce new words
> to parser that call DELETE with NULL conditions.
> 
> Closes #2201.
> ---
> Branch: http://github.com/tarantool/tarantool/tree/kshch/gh-2201-truncate-table
> Issue: https://github.com/tarantool/tarantool/issues/2201
I've pushed your patch into 2.0 branch.

--
Regards, Kirill Yukhin