MySQL Bugs: #61599: INSERTS table tab doesn't support NOW() column value for TIMESTAMPS

Bug #61599	INSERTS table tab doesn't support NOW() column value for TIMESTAMPS
Submitted:	22 Jun 2011 19:42	Modified:	23 Jun 2011 8:32
Reporter:	Alex Z	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: Modeling	Severity:	S3 (Non-critical)
Version:	5.2.34	OS:	Windows (XP SP3)
Assigned to:		CPU Architecture:	Any
Tags:	insert now timestamp

Description:
On the INSERTS tab if you put NOW() into a TIMESTAMP column it will try to insert it as 'NOW()' string and fail.

How to repeat:
* Create a table with TIMESTAMP column
* Go to INSERTS tab for this table
* Put NOW() into column cell
* Try to Forward Engineer schema into the database

Suggested fix:
For TIMESTAMPS and DATE/TIME columns user NOW() without quotes.

and 'current_timestamp' and 'localtimestamp' and 'UTC_TIMESTAMP' should be handled as well (ie. such string constants should not be quoted).

Peter
(not a MySQL person)

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

ADDITIONAL INFO:

7.7.1.3.9. The Inserts Tab

....

Note
It is possible to enter a function, or other expression, into a field. If doing so, the prefix \func should be used, to prevent MySQL Workbench from escaping quotation marks. For example, if entering the expression md5('fred') MySQL Workbench would generate the code md5(\'fred\'). To prevent this enter the expression as \func md5('fred'). This will ensure that the quoting is not escaped.

@Miguel .. 

IMHO you miss the point here. Alex is using a function with an *empty* argument ("now()").  What you quote from docs is relevant only for functions that uses strings as arguments. Inside the inner paranthesis of "now()" there is nothing to escape at all.

Same consideration would apply to special keywords as 'localtimestamp', 'current_user' etc. They should not be quoted (but I did not check if WB does quote those or not).

No, Peter, he solved the problem. I can write "\func NOW()" and it will work.

It's not intuitive, but the problem here is that a string is perfectly legal for datetime fields, so the program can't figure out what to quote and what not.

Maybe one improvement would be to add a context-menu item for datetime column fields with typical functions, like NOW(), which will insert "\func NOW()", so the user will see how it is done. Similar to how default CURRENT_TIMESTAMP is set via context menu for datetime columns in a table.

OK!

It is actually a problem with all GUI clients using a kind of DATA GRID for data manipulation.  For instance in a string column both "now()" and "'now()'" are valid inputs.  First is current time as the server will resolve it, second is just a string constant. So some kind of client implementation is required for user to specify if it should be first or second.

In SQLyog we did like this:
http://webyog.com/faq/8_99_en.html
http://webyog.com/faq/8_116_en.html

The problem is that if developers are to require quotes for strings, then all string columns (which used more often than datetime) will require quotes in each cell, like ['text']. It would solve the problem with [NOW()] vs ['2011-06-23'], but it's not worth it.

The program could auto-detect some keywords, like NOW(), indeed, but it is not universal, because there can be many functions and their combinations as a valid column value, for example [ADDTIME(NOW(), '00:01:00')].

Yes .. In a GUI client DATA GRID you would want to enter strings without 'quoting'. From here the problems origine!

We have no problem with nested functions. We just identify "func(" in the first position and once we see that we just dont quote.  Our functions list identifying valid values for "func(" is stored in a small SQLite database that ships with the program. Same small database is used for syntax highligting in the editor and for autocompletion.

Every decent GUI client has some way to do it. We have ours, WB has theirs and so on. But different ways in different clients. So also here user has a choice :-)

I don't mind using "\func" or any other prefix, the problem is - how to tell user about it. Help for Workbench is far from perfect and nobody has time to read it anyway :)

That's why I suggest context-menu items, like "NOW()", that will insert "\func NOW()" and show the user how it is done.