MySQL Bugs: #58045: Enhancement Request: unquoted input of numeric or date fields

Bug #58045	Enhancement Request: unquoted input of numeric or date fields
Submitted:	8 Nov 2010 11:39	Modified:	6 Jan 2011 16:24
Reporter:	Pete Batard	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S4 (Feature request)
Version:		OS:	Any
Assigned to:	Alexander Musienko	CPU Architecture:	Any
Tags:	date, Hexadecimal, NOW()

Description:
Currently, MySQL Workbench will quote any data input by the user when editing column data, which leads to values being improperly evaluated as strings.
For instance, if you have a table with an INT column called "hex_value", and you input 0xABCD, this is the SQL statement that Workbench will execute 

UPDATE `db`.`table` SET `hex_value`='0xABCD' WHERE `id`='1';

This results in '0xABCD' being wrongly evaluated as a string to 0, and hex_value being improperly set. The correct statement should be:

UPDATE `db`.`table` SET `hex_value`=0xABCD WHERE `id`='1';

Likewise, if you have a date field, and you want to manually populate it with a function like NOW(), Workbench will fail to detect that you are calling an SQL function and use the string 'NOW()' instead, which also returns a zero date.

While is it of course possible to remove the quotes on each SQL statement to have the proper data being populated, this is obviously very tedious. 

Could we have an enhancement request to ensure that Workbench detects that if a numeric/date field is being updated with an hex value/function, it either doesn't quote it, or parses it into the expected value?

How to repeat:
1. Create a table with two colums: an INT and a DATE.
2. Try to input 0xABCD for the INT field and NOW() for the DATE field
3. Apply the changes. Both values will be set to 0.

Thank you for the feature request.

Closely related to bug #57399

To enter NOW() for the DATE field, you have to prefix it with \func

fix confirmed in repository

An entry has been added to the 5.2.31 changelog:

In the SQL Editor, when entering a hex value into an INTEGER column, the hex value was automatically quoted as a string, causing it to be evaluated to 0.