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:
None 
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
Description:
Output:
=====
mysql> 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)

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.7.8-rc  |
+-----------+
1 row in set (0.00 sec)

Problem:
=====
Since 429496729 < 4294967295, to be consistent, 'a' is expected for both expressions.

How to repeat:
SELECT CAST( 'a' AS BINARY(4294967295) ), CAST( 'a' AS BINARY(429496729) );

Suggested fix:
'a' is returned for both exprs.
[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.