MySQL Bugs: #84735: Beautify function cut off HEX constants in WHERE clause

Bug #84735	Beautify function cut off HEX constants in WHERE clause
Submitted:	31 Jan 2017 11:00	Modified:	31 Jan 2017 12:01
Reporter:	Alexander Rumyantsev	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S3 (Non-critical)
Version:	6.3.6	OS:	Linux (Ubuntu 14.04.5 LTS)
Assigned to:		CPU Architecture:	Any
Tags:	beautify reformat views DDL

Description:
I've got a table and a view for data selection.
When I try to beautify view sql code in MySQL Workbench I receive truncated HEX constants in WHERE clause. 

How to repeat:
1. Create these table and view.

CREATE TABLE `test_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `some_flags` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `root`@`localhost` 
    SQL SECURITY DEFINER
VIEW `test`.`new_view` AS
    SELECT 
        `t`.`id` AS `id`, `t`.`some_flags` AS `some_flags`
    FROM
        `test`.`test_table` `t`
    WHERE
        ((`t`.`some_flags` & 0x0002) <> 0);

2. Right click on the View in your DB schema and select "Send to SQL Editor" -> "Create Statement". Then you will have this SQL string:

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `test`.`new_view` AS select `t`.`id` AS `id`,`t`.`some_flags` AS `some_flags` from `test`.`test_table` `t` where ((`t`.`some_flags` & 0x0002) <> 0);

3. Select all this SQL string in Editor and click Beautify button on the upper panel. Then you will get this code with invalid HEX value:

CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `root`@`localhost` 
    SQL SECURITY DEFINER
VIEW `test`.`new_view` AS
    SELECT 
        `t`.`id` AS `id`, `t`.`some_flags` AS `some_flags`
    FROM
        `test`.`test_table` `t`
    WHERE
        ((`t`.`some_flags` & 0X00) <> 0);

Suggested fix:
Beautify function must work correctly with HEX constants and don't truncate them.

Hello Alexander,

Thank you for the report.
I'm not seeing this issue with latest WB GA 6.3.8. Could you please check with 6.3.8 and confirm?

Thanks,
Umesh

Screenshot..

Attachment: 84735_6.3.6_6.3.8.png (image/png, text), 50.74 KiB.

Can't try 6.3.8 version because of dependency errors during installation. There is the only version for Ubuntu 16 at dev.mysql.com.

Thank you for the feedback, unfortunately we don't fix bug in the older versions and this reported issue is not repeatable on latest GA. 
I haven't tried but have you tried installing WB using MySQL APT Repository - https://dev.mysql.com/downloads/repo/apt/ ? 

Also, https://dev.mysql.com/doc/workbench/en/wb-installing-linux.html

If you can provide more information, feel free to add it to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Thanks,
Umesh