Bug #78507 Type for "@a=now()" and "@b=NULL" is MEDIUM_BLOB

• Submitted: 22 Sep 2015 2:48
• Modified: 22 Sep 2015 7:30
• Reporter: Su Dylan
• Status: Verified
• Category: MySQL Server: Data Types
• Severity: S3 (Non-critical)
• Version: 5.6.22, 5.7.10, 5.6.28, 5.5.47, 5.1.77
• OS: Any
• CPU Architecture: Any

[22 Sep 2015 2:48] Su Dylan

Description:
Output:
=======
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 45568
Server version: 5.6.22-log Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
mysql> set @a=now();
Query OK, 0 rows affected (0.00 sec)

mysql> set @b=NULL;
Query OK, 0 rows affected (0.00 sec)

mysql> select @a,@b;
Field   1:  `@a`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       MEDIUM_BLOB
Collation:  ? (45)
Length:     67108860
Max_length: 19
Decimals:   31
Flags:

Field   2:  `@b`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       MEDIUM_BLOB
Collation:  binary (63)
Length:     16777215
Max_length: 0
Decimals:   31
Flags:      BINARY

+---------------------+------+
| @a                  | @b   |
+---------------------+------+
| 2015-09-22 10:34:51 | NULL |
+---------------------+------+
1 row in set (0.00 sec)

Problem:
========
1. "Type" for @a=now() is expected to be "datetime".
2. "Type" for NULL is expected to be "NULL".

How to repeat:
set @a=now();
set @b=NULL;
select @a,@b;

Suggested fix:
1. "Type" for @a=now() is expected to be "datetime".
2. "Type" for NULL is expected to be "NULL".

[22 Sep 2015 7:30] MySQL Verification Team

Hello Su Dylan,

Thank you for the report.

Thanks,
Umesh

[22 Sep 2015 7:31] MySQL Verification Team

// 5.1.77

mysql> select @a,@b;
Field   1:  `@a`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       MEDIUM_BLOB
Collation:  binary (63)
Length:     16777215
Max_length: 19
Decimals:   31
Flags:      BINARY

Field   2:  `@b`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       MEDIUM_BLOB
Collation:  binary (63)
Length:     16777215
Max_length: 0
Decimals:   31
Flags:      BINARY

+---------------------+------+
| @a                  | @b   |
+---------------------+------+
| 2015-09-22 06:32:44 | NULL |
+---------------------+------+
1 row in set (0.00 sec)

// 5.5.47

mysql> select @a,@b;
Field   1:  `@a`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       MEDIUM_BLOB
Collation:  utf8_general_ci (33)
Length:     50331645
Max_length: 19
Decimals:   31
Flags:

Field   2:  `@b`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       MEDIUM_BLOB
Collation:  binary (63)
Length:     16777215
Max_length: 0
Decimals:   31
Flags:      BINARY

+---------------------+------+
| @a                  | @b   |
+---------------------+------+
| 2015-09-22 08:26:20 | NULL |
+---------------------+------+
1 row in set (0.00 sec)

mysql>

// 5.6.28

mysql> select @a,@b;
Field   1:  `@a`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       MEDIUM_BLOB
Collation:  utf8_general_ci (33)
Length:     50331645
Max_length: 19
Decimals:   31
Flags:

Field   2:  `@b`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       MEDIUM_BLOB
Collation:  binary (63)
Length:     16777215
Max_length: 0
Decimals:   31
Flags:      BINARY

+---------------------+------+
| @a                  | @b   |
+---------------------+------+
| 2015-09-22 08:37:17 | NULL |
+---------------------+------+
1 row in set (0.00 sec)

mysql>

// 5.7.10

mysql> select @a,@b;
Field   1:  `@a`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       MEDIUM_BLOB
Collation:  utf8_general_ci (33)
Length:     50331645
Max_length: 19
Decimals:   31
Flags:

Field   2:  `@b`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       MEDIUM_BLOB
Collation:  binary (63)
Length:     16777215
Max_length: 0
Decimals:   31
Flags:      BINARY

+---------------------+------+
| @a                  | @b   |
+---------------------+------+
| 2015-09-22 08:44:49 | NULL |
+---------------------+------+
1 row in set (0.00 sec)

mysql>