WL#935: ON COMMIT clause for temporary tables

F531 Temporary tables 
        "CREATE TEMPORARY TABLE F531 (S1 INT) 
         /* or this alternate syntax: */ 
         CREATE GLOBAL TEMPORARY TABLE F531 (S1 INT)" 
        DEFICIENT 
 
     MySQL automatically drops tables at the end of a 
     session. This is not enough, an "on commit delete 
     rows" option would put temporary tables closer to 
     the ANSI/ISO specification. 
 
M> Should we just delete rows or also drop the temporary table ? 
 
P> ON COMMIT DELETE ROWS is the default (the non-default is ON 
P> COMMIT PRESERVE ROWS). The table is not dropped. There are 
P> other details, which are less important. 
When creating a temporary table, allow this syntax: 
 
[ON COMMIT {DELETE|PRESERVE} ROWS] 
 
The default is ON COMMIT PRESERVE ROWS, which means: 
after the commit, the rows are still there. 
 
ON COMMIT DELETE ROWS would mean: when encountering 
an explicit COMMIT statement, delete all rows in the 
temporary table. 
 
In fact the deletion should take place for auto-commit 
as well, but we should relax that requirement. 
 
The above syntax is SQL standard, and compliant with 
the non-core feature F531 "Temporary tables". 
 
There is one additional clause, not standard but 
Oracle-like: 
 
ON COMMIT DROP 
 
This would have the effect that the temporary table 
is dropped automatically when an explicit COMMIT 
occurs. 

See also WL#934 Temporary Tables In The Standard Way