Bug #79398 view using hexadecimal or bit literal gives wrong results
Submitted: 24 Nov 2015 16:37 Modified: 9 Dec 2015 15:03
Reporter: Guilhem Bichot Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.7.11,5.6.28,5.5.44 OS:Any
Assigned to: CPU Architecture:Any

[24 Nov 2015 16:37] Guilhem Bichot
Description:
A view's definition is written to the data dictionary using Item::print() functions.
Alas, Item_hex_string::print() prints only the last 8 bytes of the hex literal.
So, the literal gets truncated => wrong results.

mysql> create view v1 as select x'7f9d04ae61b34468ac798ffcc984ab68'=x'7f9d04ae61b34468ac798ffcc984ab68' as a,x'7f9d04ae61b34468ac798ffcc984ab68'=x'7f9d04ae61b34468ac798ffcc984ab60' as b, x'7f9d04ae61b34468ac798ffcc984ab68'=x'0f9d04ae61b34468ac798ffcc984ab68' as c;

Above, the two last equalities should be false as I changed a byte in the right-hand side (for the 2nd equality I changed the last byte; for the 3rd I changed the first):

mysql> select * from v1;
+---+---+---+
| a | b | c |
+---+---+---+
| 1 | 0 | 1 |
+---+---+---+

c=1 is WRONG.

mysql> show create view v1;
| v1   | CREATE ALGORITHM=UNDEFINED DEFINER=`skip-grants user`@`skip-grants host` SQL SECURITY DEFINER VIEW `v1` AS select (0xac798ffcc984ab68 = 0xac798ffcc984ab68) AS `a`,(0xac798ffcc984ab68 = 0xac798ffcc984ab60) AS `b`,(0xac798ffcc984ab68 = 0xac798ffcc984ab68) AS `c` 

We see only 8 bytes are retained. The original literals are more than 8 bytes...
Bit literals are affected too.

How to repeat:
see above.
[9 Dec 2015 15:03] Paul DuBois
Noted in 5.7.11, 5.8.0 changelogs.

Hexadecimal and bit literals written to saved view definitions could
be truncated. This could also affect extended EXPLAIN output.