NOT_NULL Flag incorrectly set

[zhangysh1995]

Bug Report

1. Minimal reproduce step (Required)

CREATE TABLE `test` (
            `iD` bigint(20) NOT NULL,
            `INT_TEST` int(11) DEFAULT NULL
          ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
INSERT INTO test VALUES (2, 10), (3, NULL);

 SELECT DISTINCT count(*), id + int_test as res FROM test  GROUP BY res ORDER BY res;

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

mysql>  SELECT DISTINCT count(*), id + int_test as res FROM test  GROUP BY res ORDER BY res;
+----------+-----+
| count(*) | res |
+----------+-----+
|        1 | NULL|
|        1 |  12 |
+----------+-----+
2 rows in set (0.01 sec)

3. What did you see instead (Required)

mysql>  SELECT DISTINCT count(*), id + int_test as res FROM test  GROUP BY res ORDER BY res;
+----------+-----+
| count(*) | res |
+----------+-----+
|        1 | NULL |
|        1 |  12 |
+----------+-----+
2 rows in set (0.01 sec)

4. Affected version (Required)

commit a9177fe846bf2638261f64514ab4ebd0155cc60c (HEAD -> master, origin/master, origin/HEAD)
Author: Boqin Qin <bobbqqin@bupt.edu.cn>
Date:   Mon Jul 6 19:28:12 2020 +0800

5. Root Cause Analysis

An extra space is printed.

[ghost]

Thank you for the bug report. I can reproduce:

mysql>  SELECT DISTINCT count(*), id + int_test as res FROM test  GROUP BY res ORDER BY res;
+----------+-----+
| count(*) | res |
+----------+-----+
|        1 | NULL |
|        1 |  12 |
+----------+-----+
2 rows in set (0.01 sec)

For context, here is the output with column-type-info enabled.

In MySQL 8.0:

mysql [localhost:8020] {msandbox} (test) > SELECT DISTINCT count(*), id + int_test as res FROM test  GROUP BY res ORDER BY res;
Field   1:  `count(*)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONGLONG
Collation:  binary (63)
Length:     21
Max_length: 1
Decimals:   0
Flags:      NOT_NULL NUM 

Field   2:  `res`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONGLONG
Collation:  binary (63)
Length:     21
Max_length: 2
Decimals:   0
Flags:      NUM 


+----------+------+
| count(*) | res  |
+----------+------+
|        1 | NULL |
|        1 |   12 |
+----------+------+
2 rows in set (0.00 sec)

In TiDB:

mysql> SELECT DISTINCT count(*), id + int_test as res FROM test  GROUP BY res ORDER BY res;
Field   1:  `count(*)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONGLONG
Collation:  binary (63)
Length:     21
Max_length: 1
Decimals:   0
Flags:      NOT_NULL BINARY NUM 

Field   2:  `res`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONGLONG
Collation:  binary (63)
Length:     20
Max_length: 2
Decimals:   0
Flags:      NOT_NULL BINARY NUM 


+----------+-----+
| count(*) | res |
+----------+-----+
|        1 | NULL |
|        1 |  12 |
+----------+-----+
2 rows in set (0.00 sec)

The bug is because TiDB sets the NOT_NULL flag incorrectly.

[fzhedu]

/label component/executor

[ghost]

This is a regression between TiDB 3.1 and 4.0. I can reproduce on 4.0.1, but not on 3.1.2.

[github-actions]

Please check whether the issue should be labeled with 'affects-x.y' or 'backport-x.y.z',
and then remove 'needs-more-info' label.