expression "ifnull(concat(NULL),'~')" show hex

[knull-cn]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

select ifnull(concat(NULL),'~');

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

mysql> select ifnull(concat(NULL),'~');
+--------------------------+
| ifnull(concat(NULL),'~') |
+--------------------------+
| ~                        |
+--------------------------+
1 row in set (0.04 sec)

3. What did you see instead (Required)

mysql> select ifnull(concat(NULL),'~');
+----------------------------------------------------+
| ifnull(concat(NULL),'~')                           |
+----------------------------------------------------+
| 0x7E                                               |
+----------------------------------------------------+

4. What is your TiDB version? (Required)

I test v5.2.0 is ok ;
Starting from v5.3.0, is has been displayed as hex, including the latest v7.6.0

[knull-cn]

/found gs

[jebter]

Unable to reproduce, are there any parameters that need to be configured?
TiDB root@127.0.0.1:test> select ifnull(concat(NULL),'');
+--------------------------+
| ifnull(concat(NULL),'') |
+--------------------------+
| ~ |
+--------------------------+

1 row in set
Time: 0.003s
TiDB root@127.0.0.1:test> select tidb_version() \G
[ 1. row ]
tidb_version() | Release Version: v7.6.0
Edition: Community
Git Commit Hash: 42960c3
Git Branch: heads/refs/tags/v7.6.0
UTC Build Time: 2024-01-15 11:27:07
GoVersion: go1.21.5

[dveeden]

TiDB with mysql --column-type-info ...:

mysql> select ifnull(concat(NULL),'~');
Field   1:  `ifnull(concat(NULL),'~')`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  binary (63)
Length:     1
Max_length: 1
Decimals:   31
Flags:      NOT_NULL 


+----------------------------------------------------+
| ifnull(concat(NULL),'~')                           |
+----------------------------------------------------+
| 0x7E                                               |
+----------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT TIDB_VERSION()\G
*************************** 1. row ***************************
TIDB_VERSION(): Release Version: v7.6.0
Edition: Community
Git Commit Hash: 52794d985ba6325d75a714d4eaa0838d59425eb6
Git Branch: heads/refs/tags/v7.6.0
UTC Build Time: 2024-01-22 14:20:42
GoVersion: go1.21.5
Race Enabled: false
Check Table Before Drop: false
Store: tikv
1 row in set (0.01 sec)

TiDB with mysql --skip-binary-as-hex --column-type-info ...:

mysql> select ifnull(concat(NULL),'~');
Field   1:  `ifnull(concat(NULL),'~')`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  binary (63)
Length:     1
Max_length: 1
Decimals:   31
Flags:      NOT_NULL 


+--------------------------+
| ifnull(concat(NULL),'~') |
+--------------------------+
| ~                        |
+--------------------------+
1 row in set (0.01 sec)

[dveeden]

MySQL 8.3.0 with mysql --column-type-info ...:

mysql> select ifnull(concat(NULL),'~');
Field   1:  `ifnull(concat(NULL),'~')`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  utf8mb4_0900_ai_ci (255)
Length:     4
Max_length: 1
Decimals:   31
Flags:      NOT_NULL 


+--------------------------+
| ifnull(concat(NULL),'~') |
+--------------------------+
| ~                        |
+--------------------------+
1 row in set (0.00 sec)

mysql> SELECT VERSION();
Field   1:  `VERSION()`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  utf8mb4_0900_ai_ci (255)
Length:     20
Max_length: 5
Decimals:   31
Flags:      NOT_NULL 


+-----------+
| VERSION() |
+-----------+
| 8.3.0     |
+-----------+
1 row in set (0.01 sec)

[dveeden]

Difference in output:

7,8c7,8
< Collation:  utf8mb4_0900_ai_ci (255)
< Length:     4
---
> Collation:  binary (63)
> Length:     1

[YangKeao]

The behavior is really a chaos. It's even not consistent internally in MySQL. For example, if you create a VAR_BINARY column, and give it a NULL, the result will be different:

create table t (id varbinary(16));
insert into t values ();

Then

mysql> select concat(NULL);
Field   1:  `concat(NULL)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  binary (63)
Length:     0
Max_length: 0
Decimals:   31
Flags:      BINARY 


+----------------------------+
| concat(NULL)               |
+----------------------------+
| NULL                       |
+----------------------------+
1 row in set (0.00 sec)

mysql> select ifnull(concat(NULL),'~');
Field   1:  `ifnull(concat(NULL),'~')`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  utf8mb4_0900_ai_ci (255)
Length:     4
Max_length: 1
Decimals:   31
Flags:      NOT_NULL 


+--------------------------+
| ifnull(concat(NULL),'~') |
+--------------------------+
| ~                        |
+--------------------------+
1 row in set (0.00 sec)

mysql> select concat(id) from t;
Field   1:  `concat(id)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  binary (63)
Length:     16
Max_length: 0
Decimals:   31
Flags:      BINARY 


+------------------------+
| concat(id)             |
+------------------------+
| NULL                   |
+------------------------+
1 row in set (0.00 sec)

mysql> select ifnull(concat(id),'~') from t;
Field   1:  `ifnull(concat(id),'~')`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  binary (63)
Length:     16
Max_length: 1
Decimals:   31
Flags:      NOT_NULL BINARY 


+------------------------------------------------+
| ifnull(concat(id),'~')                         |
+------------------------------------------------+
| 0x7E                                           |
+------------------------------------------------+
1 row in set (0.00 sec)

You can find that the collation of select concat(NULL) and select concat(id) are both binary. However, the result of ifnull is different: the first is utf8mb4_0900_ai_ci (according to the collation of ~ literal), and the second one is binary.

This behavior shows us that it's not easy to make it compatible with MySQL in TiDB, because TiDB expects to have an consistent behavior without considering whether its literal or not 🤦, and I think it sounds better/cleaner.

[YangKeao]

Oops. I can fix this issue in constant folding specially 🤔 , though it sounds weird.