Bug #14208 SHOW COLUMNS FROM returns incorrect default val for TIMESTAMP
Submitted: 21 Oct 2005 12:58 Modified: 21 Oct 2005 15:53
Reporter: John Lucas Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Information schema Severity:S4 (Feature request)
Version:5.0.13 OS:Microsoft Windows (Win 2k)
Assigned to: CPU Architecture:Any
Triage: Triaged: D5 (Feature request)

[21 Oct 2005 12:58] John Lucas
Description:
SHOW COLUMNS FROM tablename does not correctly report default values when the column type is timestamp.

How to repeat:
CREATE TABLE Table1
(
         ID1 Bigint NOT NULL AUTO_INCREMENT, PRIMARY KEY (ID1),
         Column1 Timestamp DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP 
 )
TYPE=InnoDB

Then:

SHOW COLUMNS FROM Table1

returns default as "CURRENT_TIMESTAMP" only.
[21 Oct 2005 13:28] Valeriy Kravchuk
Thank you for a problem report.

"on update CURRENT_TIMESTAMP" is a specific clause to be used with CURRENT_TIMASTAMP default value only. It is not shown neither in  DESC, nor in SHOW COLUMNS or SHOW FIELDS results:

mysql> CREATE TABLE Table1
    -> (
    ->          ID1 Bigint NOT NULL AUTO_INCREMENT, PRIMARY KEY (ID1),
    ->          Column1 Timestamp DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
    ->  )
    -> TYPE=InnoDB;
Query OK, 0 rows affected, 1 warning (0,08 sec)
mysql> show columns from Table1
    -> ;
+---------+------------+------+-----+-------------------+----------------+
| Field   | Type       | Null | Key | Default           | Extra          |
+---------+------------+------+-----+-------------------+----------------+
| ID1     | bigint(20) | NO   | PRI | NULL              | auto_increment |
| Column1 | timestamp  | YES  |     | CURRENT_TIMESTAMP |                |
+---------+------------+------+-----+-------------------+----------------+
2 rows in set (0,03 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.15    |
+-----------+
1 row in set (0,00 sec)

It may be somehow noted in the Extra column, surely. So, I change this report to a feature request.
[21 Oct 2005 15:53] John Lucas
This was changed to S4 because "It may be somehow noted in the Extra column.."  Well, I don't see it in the Extra column and if someone can show me where I will be happy.  Otherwise, could we please cange this back to S3

Thanks