Re: MySQL Workbench ALTER VIEW bug? when using hexadecimal values

On 4/21/2016 10:50 AM, djburbridge_at_gmail.com wrote:
> I can't find this reported anywhere, though I'm sure I can't be the first to find it if it's a real bug!
> My applications uses 96-bit hexadecimal values, stored as varchar(24) in a column "hexcode". I then break the value down using combinations of substr, conv and bitwise operations.
> For convenience, I've been looking at storing these calculations as a separate view (generated values would also work).
> I've found strange behaviour, which can be reproduced as follows (I'm using MySQL 5.7.10 and Workbench 6.3.6.511).
>
> (1) create the table:
> CREATE TABLE test_table (hexcode varchar(24));
>
> (2) add some values:
> INSERT INTO test.test_table VALUES ('e5e52c9cc7aa3adb14dc223b');
>
> (3) create the view:
> CREATE VIEW test_view AS
> SELECT
> hexcode,
> ((conv(substr(hexcode,3,2),16,10) & 0x1c) >> 2) AS value1,
> ((conv(substr(hexcode,3,12),16,10) & 0x03fffffc0000) >> 18) AS value2
> FROM test_table;
>
> (4) test it:
> SELECT * FROM test.test_view;
>
> e5e52c9cc7aa3adb14dc223b 1 4925233
>
> (5) try to alter the view by right-clicking the view in Workbench and selecting Alter View...:
> CREATE
> ALGORITHM = UNDEFINED
> DEFINER = `root`_at_`localhost`
> SQL SECURITY DEFINER
> VIEW `test`.`test_view` AS
> SELECT
> `test`.`test_table`.`hexcode` AS `hexcode`,
> ((CONV(SUBSTR(`test`.`test_table`.`hexcode`,
> 3,
> 2),
> 16,
> 10) & 0X) >> 2) AS `value1`,
> ((CONV(SUBSTR(`test`.`test_table`.`hexcode`,
> 3,
> 12),
> 16,
> 10) & 0X03FFFFFC00) >> 18) AS `value2`
> FROM
> `test`.`test_table`
>
> Note, firstly, how the "0x"s have all been changed to "0X"s, and, secondly, how they've been truncated (0x1c->0X, 0x03fffffc0000->0X03FFFFFC00).
>
> The underlying view still works if I don't actually change the view code, but if I do update the code (say, add another column), I get "There was an error applying the SQL script to the database", since it rejects both hex values because they use "X" not "x", and anyway the first one (0x) is still invalid anyway!
>
> Anyone else come across anything similar?
>

I suspect few, if any, have found such a bug because they properly normalize their databases, and yours is not (multiple values in a single column violates 1NF).

It sounds like a bug, and you can report it to MySQL.

-- 
==================
Remove the "x" from my email address
Jerry Stuckle
jstucklex_at_attglobal.net
==================