MySQL Bugs: #6824: Lack of column's default value type info confuses clients

Bug #6824	Lack of column's default value type info confuses clients
Submitted:	25 Nov 2004 12:27	Modified:	12 Feb 2006 12:34
Reporter:	Serdar S. Kacar	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	next	OS:	Any (all)
Assigned to:		CPU Architecture:	Any

Description:
Functions in COLUMN's Default confuse clients.

Many clients use default value metadata to insert default values directly.
I think it is NOT a good approach BUT they have their reasonings like could not retrieving just inserted default values in case of that they do not have a row accessor.

How to repeat:
Say we have the following table:

CREATE TABLE `t` (
  `x` int(11) NOT NULL default '0',
  `y` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
) ENGINE=MyISAM

So client that use default metadata would most like issues following query:
SHOW COLUMNS FROM t;
and get the following resultset (aligned if fixed width font used):
Field Type      Null Key Default            Extra
x     int(11)            0
y     timestamp YES      CURRENT_TIMESTAMP 

The problem is Default column of y is not a date value, it is a function - CURRENT_TIMESTAMP.
But clients think that default value of timestamp columns should be a date value.
Consequently, in queries they add string's enclosing characters - "'".

INSERT INTO t (x,y) VALUES (0, 'CURRENT_TIMESTAMP'); is non-sense. And interpreted as '0000-00-00 00:00:00' by MySQL.
It should be like,
INSERT INTO t (x,y) VALUES (0, CURRENT_TIMESTAMP);

MySQL's out-of-date Control Center has exactly doing this. Hence we can expect many other clients do so.

I don't think that simple validity checks (like if it is a valid date do so otherwise ..) are good for two reasons:
- Clients may misinterpret valid values, and
- MySQL has Type of Default Value information already.

Suggested fix:
MySQL AB Part:
SHOW COLUMNS resultset should have a "DefaultIsFunction" column.

Client Applications Part (MySQL AB may recommend this):
Had they encounter functions in default values by SHOW COLUMNS query, Clients should first issue a query to get return values of default value of functions like
SELECT CURRENT_UPDATETIME;
Then use returned value in default value place like,
INSERT INTO t (x,y) VALUES (0, '2004-11-25 14:22:33');

Note that default value functions operating on rows (kind of calculated columns)are whole another story. 
Clients should be warned about those ones and they should not interfere with this default value mechanism unless they know what they are doing.

May be the new SHOW COLUMNS' column could be enumurated one like:
DefaultKind:
1 - Ordinary Value
2 - General Function
3 - Row Function

Thank you for the feature request. Sorry, but there is no need to fix anything, at least, until MySQL will start to support arbitrary expressions as default value for column. CURRENT_TIMESTAMP is the only special case, and it simply shooud be taken into account by client applications. Read http://dev.mysql.com/doc/refman/5.0/en/create-table.html for the details.