Incorrect return type about if function when argument type contains bit

[yuqi1129]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

Run master and do the following

CREATE TABLE `t4` (
  `b` bit(10) DEFAULT NULL,
  KEY `idx_b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

insert into t4 values(0),(1), (1000);
select if (b, b,1) from t4;

2. What did you expect to see? (Required)

select if (b, b,1) from t4;
+--------------------------+
| if (b, b,1)              |
+--------------------------+
| 0x31                     |
| 0x0001                   |
| 0x03C3A8                 |
+--------------------------+
3 rows in set (0.03 sec)

3. What did you see instead (Required)

select if (b, b,1) from t4;
+-------------+
| if (b, b,1) |
+-------------+
|           1 |
|           1 |
|        1000 |
+-------------+
3 rows in set (1.73 sec)

4. What is your TiDB version? (Required)

Master

[yuqi1129]

Seems that the return type in MySQL is var_string and TiDB is LongLong

[ichn-hu]

@yuqi1129 are you interested in fixing it?

[yuqi1129]

@ichn-hu , Yes, I am working on this

[ichn-hu]

/assign @yuqi1129 let me know if you have any problems

[XuHuaiyu]

It seems that tidb returns type newdecimal

tidb> select if (b, b,1) from t4;
Field   1:  `if (b, b,1)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       NEWDECIMAL
Collation:  binary (63)
Length:     12
Max_length: 4
Decimals:   0
Flags:      NOT_NULL MULTIPLE_KEY BINARY NUM


+-------------+
| if (b, b,1) |
+-------------+
|           1 |
|           1 |
|        1000 |
+-------------+
3 rows in set (0.00 sec)

[yuqi1129]

@XuHuaiyu , Yes, it's NEWDECIMAL , thank you for point out it.

[yuqi1129]

MySQL is var_string and TiDB is Lon

Correct: MySQL is var_string and TiDB is NEWDECIMAL

[gengliqi]

I test with MySQL 5.7 and 8.0.
Both of them output the messy code as below.

mysql> select if(b, b,1) from t4;
+------------+
| if(b, b,1) |
+------------+
|          1 |
|           |
|         è |
+------------+
3 rows in set (0.00 sec)

And in MySQL 8.0, the return type is newdecimal which is the same with TiDB.
But it returns messy code as well which is very strange.

mysql> select if(b, b,1) from t4;
Field   1:  `if(b, b,1)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       NEWDECIMAL
Collation:  binary (63)
Length:     11
Max_length: 3
Decimals:   0
Flags:      BINARY NUM


+------------+
| if(b, b,1) |
+------------+
|          1 |
|           |
|         è |
+------------+
3 rows in set (0.01 sec)

I highly doubt the output given by @yuqi1129 is wrong because 0x31 is 49 and 0x03C3A8 is 246696.
Do you use some unusual flags in the MySQL client?

select if (b, b,1) from t4;
+--------------------------+
| if (b, b,1)              |
+--------------------------+
| 0x31                     |
| 0x0001                   |
| 0x03C3A8                 |
+--------------------------+
3 rows in set (0.03 sec)

For now, my conclusion is the output of TiDB is more suitable than MySQL.

There is other evidence that can prove it's probably a bug of MySQL.
MySQL 8.0

mysql> select hex(if(b, b,1)) from t4;
+-----------------+
| hex(if(b, b,1)) |
+-----------------+
| 1               |
| 1               |
| 3E8             |
+-----------------+
3 rows in set (0.00 sec)

mysql> select bin(if(b, b,1)) from t4;
+-----------------+
| bin(if(b, b,1)) |
+-----------------+
| 1               |
| 0               |
| 0               |
+-----------------+
3 rows in set, 2 warnings (0.00 sec)

mysql> show warnings;
+---------+------+------------------------------------------+
| Level   | Code | Message                                  |
+---------+------+------------------------------------------+
| Warning | 1292 | Truncated incorrect DECIMAL value: ''    |
| Warning | 1292 | Truncated incorrect DECIMAL value: 'è'  |
+---------+------+------------------------------------------+
2 rows in set (0.00 sec)

mysql> select bin(b) from t4;
+------------+
| bin(b)     |
+------------+
| 0          |
| 1          |
| 1111101000 |
+------------+
3 rows in set (0.08 sec)

TiDB

mysql> select hex(if(b, b,1)) from t4;
+-----------------+
| hex(if(b, b,1)) |
+-----------------+
| 1               |
| 1               |
| 3E8             |
+-----------------+
3 rows in set (0.00 sec)

mysql> select bin(if(b, b,1)) from t4;
+-----------------+
| bin(if(b, b,1)) |
+-----------------+
| 1               |
| 1               |
| 1111101000      |
+-----------------+
3 rows in set (0.00 sec)

mysql> select bin(b) from t4;
+------------+
| bin(b)     |
+------------+
| 0          |
| 1          |
| 1111101000 |
+------------+
3 rows in set (0.00 sec)

[gengliqi]

@zanmato1984 Do you mind taking a look? I think we can lower the severity or just close this issue.

[zanmato1984]

According to @gengliqi 's description, I think:

1. The difference between hex(if(b, b, 1)) and bin(if(b, b, 1)) in MySQL doesn't make sense;
2. TiDB behaves absolutely self-consistent between hex(if(b, b, 1))andbin(if(b, b, 1))`.

I would consider this a MySQL bug and TiDB is (surprisingly) better, i.e., more self-consistent, than MySQL.

So I'm closing this issue as a non-bug, as well as the associated PR (which we don't see a respond to the review comment for a long time).

Feel free to reopen this issue if you have any question. Thanks.