Bug #44006 Quotes for DEFAULT 0 in TIMESTAMP columns
Submitted: 1 Apr 2009 7:23 Modified: 22 Jun 2009 10:09
Reporter: Michael Skulsky Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Workbench Severity:S2 (Serious)
Version:5.0.30 OSS OS:Any
Assigned to: Mike Lischke CPU Architecture:Any
Tags: CHECKED

[1 Apr 2009 7:23] Michael Skulsky 
Description:
When specifying a DEFAULT 0 for a TIMESTAMP column, the 0 is added as '0', which makes the SQL script fail.

How to repeat:
- Add a table in Workbench
- Add a TIMESTAMP column
- Add a 0 default value. Note the quotes that appear automatically around it
- Generate an SQL script and note that the quotes are also present

Suggested fix:
Do not quote numeric values for TIMESTAMP defaults
[1 Apr 2009 7:47] Valeriy Kravchuk 
Thank you for the bug report. Verified just as described.
[27 May 2009 14:54] Petr Burian 
Not only numeric values but also others are wrongly quoted. E.g. 'CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP', '0 ON UPDATE CURRENT_TIMESTAMP'.

5.1.12 and 5.2.1 also affected.
[4 Jun 2009 11:30] Mike Lischke 
Fixed in 5.1 + 5.2
[10 Jun 2009 9:29] Mike Lischke 
Added support for CHAR (VARCHAR was already in), nothing else should be auto quoted. The user is responsible for quoting default values if needed.
[12 Jun 2009 12:12] Johannes Taxacher 
fix confirmed. will be included in 5.1.13

Note for Documentation: WB will autoquote values entered as default only for columns of type CHAR and VARCHAR. And only if the user doesn't start his entry with a quote. If user enters a value starting with a quote char, WB doesn't touch the entered string anymore.
For default values entered for columns of any type other than CHAR and VARCHAR the user has to take care of quoting the values every time.
[22 Jun 2009 10:09] Tony Bedford 
Added entry to 5.1.13 changelog:

In the table editor, when a column was added with a data type of TIMESTAMP, and the default value of 0 entered, MySQL Workbench erroneously added quotes around the 0. Any generated SQL script therefore contained errors, and would subsequently fail. 

Main documentation will also be updated.
[2 Sep 2009 19:11] Thomas Healy 
I am still seeing this same behavior where entering a the default of 

CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP

for a timestamp, the value is surrounded by single quotes.  I am using Workbench 5.1.17 Rev 4302
[2 Sep 2009 20:11] Mike Lischke 
That was a regression, which has been fixed by now. Next release will not quote timestamp anymore.