MySQL Bugs: #36206: INSERTs escape CURRENT

Bug #36206	INSERTs escape CURRENT_TIMESTAP, DEFAULT
Submitted:	18 Apr 2008 11:14	Modified:	21 May 2010 13:13
Reporter:	Takatoshi Iwanaga	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: Modeling	Severity:	S3 (Non-critical)
Version:	5.0.19	OS:	Windows (XP Professional)
Assigned to:	Sergei Tkachenko	CPU Architecture:	Any
Tags:	inserts

Description:
table definition =====================
`id` INT NOT NULL AUTO_INCREMENT
`rdate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
======================================

I want to define like this with Standard Inserts Window:
(Table definition -> Inserts -> Open Editor)
INSERT INTO `table` (`id`, `rdate`) VALUES (DEFAULT, CURRENT_TIMESTAMP);

Standard Inserts doesn't allow inputting CURRENT_TIMESTAMP and DEFAULT to values.
(I can input NULL to values. )

* sorry, my english is not good

How to repeat:
Create a table.

Table definition -> Inserts -> Open Editor

Input CURRENT_TIMESTMP or DEFAULT.

the inserts editor was changed so that automatic quotes are no longer automatically added

Added code to allow CURRENT_TIMESTAP, DEFAULT explicitly

entering DEFAULT and CURRENT_TIMESTAMP as values is possible now (note that Quotes aren't added automatically any longer).

As of what version is this fixed?  Trying similar now with the inserts tab to populate rows which include a datetime field.  I'm trying to use NOW() but it's always inserted as quoted.  I'm using version 5.0.28 SE (rev 3356) on XP Pro OS.

Sorry; I missed the 'explicitly' on Alfredo's 2nd May comment.  As it's only for those two values (DEFAULT and CURRENT_TIMESTAMP), could you perhaps make it more expansive to cover the other MySQL functions such as NOW()?  Perhaps a right-click contextual menu option to insert a language construct (cascading down to the various methods/reserved words)?  That way you could keep the standard typing as being auto-quoted.

I am using Workbench 5.1.17 SE (commercial), and I cannot add CURRENT_TIMESTAMP as a default value for a field of type TIMESTAMP, because it gets quoted like this: 'CURRENT_TIMESTAMP' - and MySQL does not accept this.

Either you need to implement all reserved words, or just somehow allow the user to choose whether quoting should be used or not.

Bug #36250 is marked as duplicate of this bug here.

Create a simple table in diagram.

Two columns:
i integer NN Default 5
t timestamp NN Default current_timestamp

Open INSERT editor ...

Now try to add the word DEFAULT in column i. Also try it in column t.

Both is not working in 5.2.8.

Fixed in 5.2.

Sorry, I misread this problem as one for the columns editor (where you can add default values). In the Inserts editor this works for the editor itself, but shows up quoted when forward engineering the schema with enabled INSERTs values.

So it's neither a problem of the columns editor nor the inserts editor, but the SQL generator. Hence passing this on to Alex.

In v5.2.11 we added option to use expression as field value.
Please see the bug #38600 for details.

fix confirmed in repository. 
to be able to enter special values (like DEFAULT or CURRENT_TIMESTAMP) you need to preceed them with: \func

i.e. for specifying CURRENT_TIMESTAMP for a cell-value you'd enter: \func CURRENT_TIMESTAMP

An entry has been added to the 5.2.22 changelog:

MySQL Workbench now supports entering of expressions such as DEFAULT or CURRENT_TIMESTAMP into the inserts editor. The expression needs to be preceded by \func.