Randgen: wrong results from left join + group by

[fzhedu]

reproduce env:
mysql -h 172.16.5.85 -P 53324 -u root -D no_subquery100000

tiup cluster nightly of 2021-2-18

mysql> SELECT    table1 . `col_varchar_binary_not_null_key` AS field1 , table1 . `col_varchar_binary_key` AS field2 , table2 . `pk` AS field3 FROM ( A AS table1 LEFT  JOIN C AS table2 ON (table2 . `col_int_key` = table1 . `col_int_key` ) )   GROUP BY field1, field2, field3  ;
+--------+--------+--------+
| field1 | field2 | field3 |
+--------+--------+--------+
| c      | w      |      1 |
| c      | w      |      3 |
| r      | w      |     31 |
| r      | w      |      8 |
| c      | w      |     11 |
| t      | o      |   NULL |
| h      | l      |   NULL |
| y      | i      |      7 |
| g      | w      |   NULL |
| t      | h      |   NULL |
| m      | g      |   NULL |
| u      | e      |   NULL |
| a      | s      |     52 |
| b      | y      |   NULL |
| w      | h      |   NULL |
| c      | w      |     10 |
| s      | i      |   NULL |
| n      | h      |   NULL |
| z      | t      |   NULL |
| a      | s      |     28 |
| r      | w      |     35 |
| y      | i      |     44 |
| y      | i      |     43 |
| h      | m      |   NULL |
| a      | d      |     24 |
| r      | w      |     46 |
| a      | s      |     23 |
| r      | w      |     39 |
| t      | I      |   NULL |
| r      | o      |   NULL |
| y      | z      |   NULL |
+--------+--------+--------+
31 rows in set (0.01 sec)

mysql> set @@tidb_isolation_read_engines='tiflash,tidb'; set @@tidb_allow_mpp=1;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT    table1 . `col_varchar_binary_not_null_key` AS field1 , table1 . `col_varchar_binary_key` AS field2 , table2 . `pk` AS field3 FROM ( A AS table1 LEFT  JOIN C AS table2 ON (table2 . `col_int_key` = table1 . `col_int_key` )
)   GROUP BY field1, field2, field3  ;
+--------+--------+-------------+
| field1 | field2 | field3      |
+--------+--------+-------------+
| w      | h      |   157798496 |
| y      | z      | -1504189896 |
| a      | d      |       32553 |
| r      | o      | -1504189936 |
| c      | w      |       32553 |
| r      | w      |       32553 |
| s      | i      |       32553 |
| a      | s      |           0 |
| t      | h      |           1 |
| r      | w      | -1504189840 |
| r      | w      |           0 |
| y      | i      |           0 |
| b      | y      |       32509 |
| y      | i      |           1 |
| n      | h      |       32509 |
| z      | t      |   770083600 |
| g      | w      |       32509 |
| t      | o      |   770083696 |
| r      | w      |       32509 |
| a      | s      |   157798496 |
| h      | m      |   770083644 |
| m      | g      |       32581 |
| c      | w      |       32581 |
| r      | w      |  -220155336 |
| a      | s      |   157798496 |
| t      | I      |           1 |
| h      | l      |       32581 |
| y      | i      |           0 |
| c      | w      |       32581 |
| u      | e      |  -220155376 |
| c      | w      |  -220155280 |
+--------+--------+-------------+
31 rows in set (0.03 sec)

mysql> set @@tidb_isolation_read_engines='tiflash,tidb'; set @@tidb_allow_mpp=0;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT    table1 . `col_varchar_binary_not_null_key` AS field1 , table1 . `col_varchar_binary_key` AS field2 , table2 . `pk` AS field3 FROM ( A AS table1 LEFT  JOIN C AS table2 ON (table2 . `col_int_key` = table1 . `col_int_key` )
)   GROUP BY field1, field2, field3  ;
+--------+--------+--------+
| field1 | field2 | field3 |
+--------+--------+--------+
| h      | l      |   NULL |
| c      | w      |      3 |
| r      | w      |      8 |
| c      | w      |     11 |
| c      | w      |      1 |
| r      | w      |     31 |
| t      | o      |   NULL |
| a      | s      |     28 |
| r      | w      |     35 |
| y      | i      |     44 |
| m      | g      |   NULL |
| u      | e      |   NULL |
| y      | i      |      7 |
| a      | s      |     52 |
| g      | w      |   NULL |
| t      | h      |   NULL |
| y      | i      |     43 |
| r      | w      |     46 |
| a      | d      |     24 |
| h      | m      |   NULL |
| y      | z      |   NULL |
| r      | o      |   NULL |
| r      | w      |     39 |
| a      | s      |     23 |
| t      | I      |   NULL |
| w      | h      |   NULL |
| b      | y      |   NULL |
| s      | i      |   NULL |
| z      | t      |   NULL |
| n      | h      |   NULL |
| c      | w      |     10 |
+--------+--------+--------+
31 rows in set (0.00 sec)

mysql>

[tisonkun]

@hanfei1991 there is another exchange schema issue tracked as pingcap/tidb#23031

the minimal reproduce is

mysql> DESC test.t2;
+-------+---------+------+------+---------+-------+
| Field | Type    | Null | Key  | Default | Extra |
+-------+---------+------+------+---------+-------+
| c1    | int(11) | NO   |      | NULL    |       |
+-------+---------+------+------+---------+-------+
1 row in set (0.00 sec)

mysql> SELECT * FROM test.t2;
+----+
| c1 |
+----+
|  1 |
|  2 |
|  1 |
+----+
3 rows in set (0.00 sec)

mysql> set @@tidb_allow_mpp=1; EXPLAIN SELECT DISTINCT 87 FROM test.t2 WHERE NOT NULL IS NOT NULL;
Query OK, 0 rows affected (0.00 sec)

+------------------------------------+---------+--------------+---------------+-------------------------------------------------------+
| id                                 | estRows | task         | access object | operator info                                         |
+------------------------------------+---------+--------------+---------------+-------------------------------------------------------+
| TableReader_18                     | 1.00    | root         |               | data:ExchangeSender_17                                |
| └─ExchangeSender_17                | 1.00    | cop[tiflash] |               | ExchangeType: PassThrough                             |
|   └─Projection_16                  | 1.00    | cop[tiflash] |               | Column#3                                              |
|     └─HashAgg_7                    | 1.00    | cop[tiflash] |               | group by:Column#3, funcs:firstrow(Column#3)->Column#3 |
|       └─ExchangeReceiver_15        | 3.00    | cop[tiflash] |               |                                                       |
|         └─ExchangeSender_14        | 3.00    | cop[tiflash] |               | ExchangeType: HashPartition, Hash Cols: Column#3      |
|           └─Projection_12          | 3.00    | cop[tiflash] |               | 87->Column#3                                          |
|             └─TableFullScan_13     | 3.00    | cop[tiflash] | table:t2      | keep order:false, stats:pseudo                        |
+------------------------------------+---------+--------------+---------------+-------------------------------------------------------+
8 rows in set (0.00 sec)

mysql> set @@tidb_allow_mpp=1; SELECT DISTINCT 87 FROM test.t2 WHERE NOT NULL IS NOT NULL;
Query OK, 0 rows affected (0.00 sec)

ERROR 1105 (HY000): rpc error: code = Unavailable desc = transport is closing