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

[31 Jan 2017 11:00] Alexander Rumyantsev
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.
[31 Jan 2017 11:21] MySQL Verification Team
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
[31 Jan 2017 11:23] MySQL Verification Team
Screenshot..

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

[31 Jan 2017 11:51] Alexander Rumyantsev
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.
[31 Jan 2017 12:01] MySQL Verification Team
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