| Bug #99831 | UNEXPECTED RETURNED VALUE by SELECT FROM COLUMN(BINARY) | ||
|---|---|---|---|
| Submitted: | 10 Jun 2020 5:42 | Modified: | 10 Jun 2020 13:59 |
| Reporter: | ick R | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Data Types | Severity: | S3 (Non-critical) |
| Version: | 5.7.30 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[10 Jun 2020 8:35]
ick R
mysql> select * from test where col=binary ''; Empty set (0.00 sec) Still can't get the value.
[10 Jun 2020 9:06]
Yushan ZHANG
Interestingly, it saves the '' as 0 into the table. But it cannot display the value with 0. And it cannot be selected with `binary ''`. mysql> select col = 0 from test; +---------+ | col = 0 | +---------+ | 1 | +---------+ 1 row in set, 1 warning (0.00 sec) mysql> select col != 0 from test; +----------+ | col != 0 | +----------+ | 0 | +----------+ 1 row in set, 1 warning (0.00 sec) mysql> show warnings; +---------+------+----------------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------------+ | Warning | 1292 | Truncated incorrect DOUBLE value: '\x00\x00' | +---------+------+----------------------------------------------+ 1 row in set (0.00 sec) mysql> select binary col = 0 from test; +----------------+ | binary col = 0 | +----------------+ | 1 | +----------------+ 1 row in set (0.00 sec) mysql> select binary col != 0 from test; +-----------------+ | binary col != 0 | +-----------------+ | 0 | +-----------------+ 1 row in set (0.00 sec) mysql> select hex(col) from test; +----------+ | hex(col) | +----------+ | 0000 | +----------+ 1 row in set (0.00 sec) mysql> select binary col = binary '' from test; +------------------------+ | binary col = binary '' | +------------------------+ | 0 | +------------------------+ 1 row in set (0.00 sec) mysql> select binary col != binary '' from test; +-------------------------+ | binary col != binary '' | +-------------------------+ | 1 | +-------------------------+ 1 row in set (0.00 sec)
[10 Jun 2020 12:53]
MySQL Verification Team
Hi Mr. R, Thank you for your bug report. Actually, with your last comment, you have answered your own questions. Since the column is BINARY and not CHAR, this is exactly the expected behaviour. Hence, for empty string, you only have a zero byte. This is all explained in our Reference Manual. Not a bug.
[10 Jun 2020 13:00]
Yushan ZHANG
I have another consideration, why the `zero` byte is not displayed when using `select * from test;`? If empty string means `inserting a zero`, why it is not displayed?
[10 Jun 2020 13:01]
MySQL Verification Team
The answer is simple. It denotes the empty string.
Description: There is a column (col BINARY(2)) and a value('') in the column. When we execute select * from test where col='',it returns empty set. But ''='' is expected to be true. Same situation in value(' '). How to repeat: mysql> drop table if exits test; mysql> create table test (col BINARY(2)); Query OK, 0 rows affected (0.02 sec) mysql> INSERT INTO test values(''); Query OK, 1 row affected (0.00 sec) mysql> select * from test ; +------+ | col | +------+ | | +------+ 1 row in set (0.00 sec) mysql> select * from test where col =''; Empty set (0.01 sec) mysql> select binary 'a'='A'; +----------------+ | binary 'a'='A' | +----------------+ | 0 | +----------------+ 1 row in set (0.00 sec) mysql> select BINARY ''=''; +--------------+ | BINARY ''='' | +--------------+ | 1 | +--------------+ 1 row in set (0.00 sec)