MySQL Workbench ALTER VIEW bug? when using hexadecimal values
Date: Thu, 21 Apr 2016 07:50:01 -0700 (PDT)
Message-ID: <3c1faab0-4a70-4c99-8ece-e6fdfd692111_at_googlegroups.com>
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
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? Received on Thu Apr 21 2016 - 16:50:01 CEST