WL#9015: Deprecation warnings for bit-wise operation with [VAR]BINARY args
Affects: Server-8.0 — Status: Complete — Priority: Medium
Existing bit-wise operations: & | ^ ~ << >> BIT_COUNT BIT_AND BIT_XOR BIT_OR currently take BIGINT (64-bit integer) arguments and return BIGINT. If both arguments are binary strings they get converted to BIGINT and truncation might happen. This behavior will change in 5.8 and this task will add warnings to 5.7 for the behavior changes described in WL#8699. User Documentation ================== http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-11.html http://dev.mysql.com/doc/refman/5.7/en/bit-functions.html http://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html
F-1: When the arguments are binary strings(and at least one is not a hex/bit/NULL literal), & | ^ ~ << >> BIT_COUNT BIT_AND BIT_XOR BIT_OR must produce BIGINT output accompanied by a warning to inform users of the changing-in-5.8 behavior, thus giving them an opportunity to rewrite their query F-2: in other cases & | ^ ~ << >> BIT_COUNT BIT_AND BIT_XOR BIT_OR must work as before. F-3: the warning should be emitted for every offending query, but only for the first evaluation of the offending expression e.g.: if a query has a 'WHERE <bitwise op expression>...' the bitwise op is evaluated multiple times but there will be only one warning. If the query is executed 10 times: 10 warnings F-4 The warning should be sent to the client, not to the error log
The relevant bit-wise operations can theoretically exist in: - an application's SQL code - a stored routine - a view's definition ('CREATE VIEW ... SELECT bit-op') - a statement-based binary log. Warning's text: "warning: Bitwise operations on BINARY will change behavior in a future version, check the 'Bit functions' section in the manual.' For example, SELECT bin_col1 & bin_col2; will emit this warning. This warning will also be emitted when using a view, or stored routine. In the 'Bit functions' section of the manual, we will explain that, to not experience a behavior change when upgrading to 5.8, the user should change his query by casting the binary argument to BIGINT with CAST ... AS UNSIGNED (*). This will make the existing implicit binary->int cast become explicit, so 5.8 will respect the intention to treat the binary as an integer. The page should also mention that, for those who cannot change their query, their unchanged query will not work reliably on 5.8, neither will statement-based or mixed replication from 5.7 to 5.8, so if they plan to do 5.7->5.8 replication they should at least switch their 5.7 master to row-based replication. Same if they plan to replay their 5.7 binlog into a 5.8 master (like with mysqlbinlog). It is important that this documentation be added at the same time we add warnings to 5.7, so users can find information. To not flood users, the warning will be emitted for every offending query, but only for the first evaluation of the offending expression: if a query has a 'WHERE <bitwise op expression>...' the bitwise op is evaluated multiple times but there will be only one warning. If the query is executed 10 times: 10 warnings. The warning will be sent to the client, not to the error log. (*) CAST is always correct. '+0' is correct when applied to an hex/bit literal: it triggers direct conversion of the literal to BIGINT: x'0100' + 0 is 256. But, when applied to a non-hex string like _binary '1234', conversion is first done to DOUBLE (1234e0), 0 is added, then the result is converted to BIGINT for the bit op. The conversion to DOUBLE can lose precision, for example: select ('9007199254740993'+0)^('9007199254740992'+0) returns 0, showing that both numbers are treated as equal! So we should not recommend '+0', only CAST. Btw, https://dev.mysql.com/doc/refman/5.7/en/hexadecimal-literals.html recommends CAST even though +0 would work for hex literals.
Copyright (c) 2000, 2019, Oracle Corporation and/or its affiliates. All rights reserved.