MySQL Bugs: #100251: SELECT on an undefined variable returns 0x not NULL

Bug #100251	SELECT on an undefined variable returns 0x not NULL
Submitted:	17 Jul 2020 18:37	Modified:	13 Oct 2020 16:57
Reporter:	Justin Swanhart	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S2 (Serious)
Version:	8.0.21	OS:	Linux (Fedora 32)
Assigned to:		CPU Architecture:	x86

Description:
mysql> select @novar;
+----------------+
| @novar         |
+----------------+
| 0x             |
+----------------+
1 row in set (0.00 sec)

mysql> select @novar is null;
+----------------+
| @novar is null |
+----------------+
|              1 |
+----------------+
1 row in set (0.00 sec)

Comparisons of @novar work properly:
select * from tbl where c1 <=> @novar

So it is basically just a display issue, but tools that parse raw output of MySQL clients may not behave properly.

How to repeat:
select a variable that has not been defined:
mysql> select @novar;
+----------------+
| @novar         |
+----------------+
| 0x             |
+----------------+
1 row in set (0.00 sec)

Suggested fix:
Unsure.

update synopsis

You can't insert 0x into a table, it says it is an invalid value.  I had confused 0x with 0x00 (ascii NULL) but anyway, when a @var is set to NULL it says NULL but when it is unset it should be NULL and says 0x.  I don't think NULL values in binary should say 0x they should say NULL, but maybe that is another issue, I don't know.  I guess unset vars are considered to be NULL with binary collation so it says 0x.

It's the new --binary-as-hex command line client option.

[anon@fc30 mysql-commercial-8.0.21-linux-glibc2.12-x86_64]$ ./bin/mysql -uroot --binary-as-hex=0 --column-type-info
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.21-commercial MySQL Enterprise Server - Commercial

Copyright (c) 2000, 2020, 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> select @novar;
Field   1:  `@novar`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONG_BLOB
Collation:  binary (63)
Length:     16777216
Max_length: 0
Decimals:   31
Flags:      BINARY 

+--------+
| @novar |
+--------+
| NULL   |
+--------+
1 row in set (0.01 sec)

mysql> \q
Bye
[anon@fc30 mysql-commercial-8.0.21-linux-glibc2.12-x86_64]$ ./bin/mysql -uroot --binary-as-hex=1 --column-type-info
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.21-commercial MySQL Enterprise Server - Commercial

Copyright (c) 2000, 2020, 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> select @novar;
Field   1:  `@novar`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONG_BLOB
Collation:  binary (63)
Length:     16777216
Max_length: 0
Decimals:   31
Flags:      BINARY 

+----------------+
| @novar         |
+----------------+
| 0x             |
+----------------+
1 row in set (0.00 sec)

But I agree that NULL is NULL not matter what the collation or column type is, so printing "0x" is wrong.

Posted by developer:
 
Fixed in 8.0.23.

Selecting an undefined variable returned the empty binary string (0x)
rather than NULL.