Bug #80630 | CAST( .. AS BINARY(N)) gets unexpected NULL | ||
---|---|---|---|
Submitted: | 7 Mar 2016 7:47 | Modified: | 20 Apr 2016 15:02 |
Reporter: | Su Dylan | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
Version: | 5.5+ | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[7 Mar 2016 7:47]
Su Dylan
[7 Mar 2016 12:55]
MySQL Verification Team
Thank you for the bug report. C:\dbs>c:\dbs\5.5\bin\mysql -uroot --port=3550 --prompt="mysql 5.5 > " Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.5.49 Source distribution PULL: 2016-FEB-19 Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql 5.5 > SELECT CAST( 'a' AS BINARY(4294967295) ), CAST( 'a' AS BINARY(429496729) ); +-----------------------------------+----------------------------------+ | CAST( 'a' AS BINARY(4294967295) ) | CAST( 'a' AS BINARY(429496729) ) | +-----------------------------------+----------------------------------+ | a | NULL | +-----------------------------------+----------------------------------+ 1 row in set, 1 warning (0.00 sec) Your MySQL connection id is 3 Server version: 5.7.12 Source distribution PULL: 2016-FEB-19 Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql 5.7 > SELECT CAST( 'a' AS BINARY(4294967295) ), CAST( 'a' AS BINARY(429496729) ); ERROR 2020 (HY000): Got packet bigger than 'max_allowed_packet' bytes mysql 5.7 > SELECT CAST( 'a' AS BINARY(4294967295) ); ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 4 Current database: *** NONE *** +-----------------------------------+ | CAST( 'a' AS BINARY(4294967295) ) | +-----------------------------------+ | a | +-----------------------------------+ 1 row in set (0.03 sec) mysql 5.7 > SELECT CAST( 'a' AS BINARY(429496729) ); ERROR 2020 (HY000): Got packet bigger than 'max_allowed_packet' bytes mysql 5.7 >
[7 Mar 2016 14:08]
Tor Didriksen
http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_allowed_pac... SELECT CAST( 'a' AS BINARY(4294967295) ), CAST( 'a' AS BINARY(429496729) ); CAST( 'a' AS BINARY(4294967295) ) CAST( 'a' AS BINARY(429496729) ) a NULL Warnings: Warning 1301 Result of cast_as_binary() was larger than max_allowed_packet (4194304) - truncated SET GLOBAL max_allowed_packet=1024 * 1024 * 1024; <reconnect> SELECT CAST( 'a' AS BINARY(4294967295) ), CAST( 'a' AS BINARY(429496729) ); CAST( 'a' AS BINARY(4294967295) ) CAST( 'a' AS BINARY(429496729) ) a a
[8 Mar 2016 10:07]
Tor Didriksen
Both casts should return NULL: SELECT CAST( 'a' AS BINARY(4294967295) ), CAST( 'a' AS BINARY(429496729) ); CAST( 'a' AS BINARY(4294967295) ) CAST( 'a' AS BINARY(429496729) ) NULL NULL Warnings: Warning 1301 Result of cast_as_binary() was larger than max_allowed_packet (4194304) - truncated Warning 1301 Result of cast_as_binary() was larger than max_allowed_packet (4194304) - truncated
[20 Apr 2016 15:02]
Paul DuBois
Posted by developer: Noted in 5.8.0 changelog. CAST(expr AS BINARY(N) ) unexpectedly returned NULL for some valid values of N.