Bug #5507 TRUNCATE wth views needs better error message
Submitted: 10 Sep 2004 2:35 Modified: 5 Dec 2007 18:56
Reporter: Peter Gulutzan Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Views Severity:S4 (Feature request)
Version:5.0.2-alpha OS:Linux (SuSE 8.2)
Assigned to: CPU Architecture:Any

[10 Sep 2004 2:35] Peter Gulutzan
Description:
TRUNCATE does not work with views. 
It should have the same effect as TRUNCATE on the underlying base table, 
or it should be converted to a DELETE statement. 
 

How to repeat:
mysql> create table t (s1 int); 
Query OK, 0 rows affected (0.28 sec) 
 
mysql> create view v as select * from t; 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> truncate table t; 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> truncate table v; 
ERROR 1146 (42S02): Table 'db5.v' doesn't exist
[19 Aug 2005 10:34] MySQL Verification Team
verified with 5.0.12

TRUNCATE should be implemented or error message should be more informative.
[11 Apr 2007 20:09] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/24323

ChangeSet@1.2442, 2007-04-12 01:10:10+05:00, gshchepa@uchum.(none) +4 -0
  Bug#5507: TRUNCATE does not work with views.
  Support of views wasn't implemented for the TRUNCATE statement.
  Now TRUNCATE on views has the same semantics as DELETE FROM view:
  mysql_truncate() checks whether the table is a view and falls back to delete
  if so.
  In order to initialize properly the LEX::updatable for a view
  st_lex::can_use_merged() now allows usage of merged views for th TRUNCATE
  statement.
[12 Apr 2007 9:08] Sergei Golubchik
I am not sure TRUNCATE should work with views.

TRUNCATE is a DDL statement that is defined as DROP+CREATE without removing a table (that is, it's DROP+CREATE without dropping all dependent objects, like triggers or routines). And it required DROP privilege, not DELETE. Of course it could be implemented as DROP+CREATE for a view, but it's useless, and I doubt that  you meant that.

TRUNCATE is an Oracle compatibility feature. And in Oracle one cannot TRUNCATE a view ("ORA-01702: a view is not appropriate here")
[12 Apr 2007 18:20] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/24405

ChangeSet@1.2448, 2007-04-12 23:21:37+05:00, gshchepa@gshchepa.localdomain +6 -0
  Bug#5507: TRUNCATE does not work with views.
  Support of views wasn't implemented for the TRUNCATE statement.
  Now TRUNCATE on views has the same semantics as DELETE FROM view:
  mysql_truncate() checks whether the table is a view and falls back
  to delete if so.
  In order to initialize properly the LEX::updatable for a view
  st_lex::can_use_merged() now allows usage of merged views for the
  TRUNCATE statement.
[15 Apr 2007 16:48] Bugs System
Pushed into 5.1.18-beta
[15 Apr 2007 16:53] Bugs System
Pushed into 5.0.40
[17 Apr 2007 10:35] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/24634

ChangeSet@1.2457, 2007-04-17 12:32:01+02:00, serg@janus.mylan +4 -0
  reverted the fix for Bug#5507
[27 Apr 2007 9:21] Bugs System
Pushed into 5.1.18-beta
[27 Apr 2007 9:23] Bugs System
Pushed into 5.0.42
[17 May 2007 22:01] Trudy Pelzer
I'm changing the title of this bug to reflect the real
problem: the error message returned when one attempts
to use the invalid syntax TRUNCATE TABLE <view name>
is misleading. A better error message -- e.g. something
like "<view name> is not a base table" -- should be 
returned.