Unexpected Slow Query and Result by INL_JOIN

[bajinsheng]

Bug Report

1. Minimal reproduce step (Required)

CREATE TABLE t0(c0 BOOL) PARTITION BY HASH(c0) PARTITIONS 75;
INSERT INTO t0(c0) VALUES (true);
CREATE VIEW v0(c0) AS SELECT t0.c0 FROM t0 GROUP BY (FALSE)NOT LIKE(1);
INSERT INTO t0 VALUES (false), (true);

SELECT v0.c0, t0.c0 FROM  v0 LEFT  OUTER JOIN t0 ON t0.c0 WHERE v0.c0; -- {1|1}, {1|1}
SELECT /*+ INL_JOIN(t0)*/v0.c0, t0.c0 FROM  v0 LEFT  OUTER JOIN t0 ON t0.c0 WHERE v0.c0; -- {}

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

3. What did you see instead (Required)

1. It takes around 1 minute to execute the above test case.
2. Both queries return different results.

4. What is your TiDB version? (Required)

| Release Version: v7.3.0
Edition: Community
Git Commit Hash: 40b72e7a9a4fc9670d4c5d974dd503a3c6097471
Git Branch: heads/refs/tags/v7.3.0
UTC Build Time: 2023-08-08 10:08:14
GoVersion: go1.20.7
Race Enabled: false
Check Table Before Drop: false
Store: tikv |

Startup command:

tiup playground 7.3.0

[bajinsheng]

/label fuzz/sqlancer

[qw4990]

The root cause is the view's definition breaks the only_full_group_by attribute.
You can see the plan of this view has a firstrow function so its returned results are not stable:

mysql> SELECT t0.c0 FROM t0 GROUP BY (FALSE)NOT LIKE(1);
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.t0.c0' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> explain SELECT t0.c0 FROM t0 GROUP BY (FALSE)NOT LIKE(1); 
+--------------------------------+---------+-----------+------------------------+-------------------------------------------------------------------------------+
| id                             | estRows | task      | access object          | operator info                                                                 |
+--------------------------------+---------+-----------+------------------------+-------------------------------------------------------------------------------+
| HashAgg_12                     | 2.00    | root      |                        | group by:Column#3, funcs:firstrow(Column#4)->test.t0.c0                       |
| └─PartitionUnion_13            | 2.00    | root      |                        |                                                                               |
|   ├─HashAgg_16                 | 1.00    | root      |                        | group by:1, funcs:firstrow(test.t0.c0)->Column#4, funcs:firstrow(1)->Column#3 |
|   │ └─TableReader_21           | 1.00    | root      |                        | data:TableFullScan_20                                                         |
|   │   └─TableFullScan_20       | 1.00    | cop[tikv] | table:t0, partition:p0 | keep order:false, stats:pseudo                                                |
|   └─HashAgg_24                 | 1.00    | root      |                        | group by:1, funcs:firstrow(test.t0.c0)->Column#4, funcs:firstrow(1)->Column#3 |
|     └─TableReader_29           | 2.00    | root      |                        | data:TableFullScan_28                                                         |
|       └─TableFullScan_28       | 2.00    | cop[tikv] | table:t0, partition:p1 | keep order:false, stats:pseudo                                                |
+--------------------------------+---------+-----------+------------------------+-------------------------------------------------------------------------------+
8 rows in set, 1 warning (0.00 sec)

mysql> select v0.c0 from v0;
+------+
| c0   |
+------+
|    1 |
+------+
1 row in set, 1 warning (0.00 sec)

mysql> select v0.c0 from v0;
+------+
| c0   |
+------+
|    0 |
+------+
1 row in set, 1 warning (0.01 sec)

mysql> SELECT t0.c0 FROM t0 GROUP BY (FALSE)NOT LIKE(1);
+------+
| c0   |
+------+
|    1 |
+------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT t0.c0 FROM t0 GROUP BY (FALSE)NOT LIKE(1);
+------+
| c0   |
+------+
|    0 |
+------+
1 row in set, 1 warning (0.00 sec)

[qw4990]

The root cause is the view's definition breaks the only_full_group_by attribute. You can see the plan of this view has a firstrow function so its returned results are not stable:

mysql> SELECT t0.c0 FROM t0 GROUP BY (FALSE)NOT LIKE(1);
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.t0.c0' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> explain SELECT t0.c0 FROM t0 GROUP BY (FALSE)NOT LIKE(1); 
+--------------------------------+---------+-----------+------------------------+-------------------------------------------------------------------------------+
| id                             | estRows | task      | access object          | operator info                                                                 |
+--------------------------------+---------+-----------+------------------------+-------------------------------------------------------------------------------+
| HashAgg_12                     | 2.00    | root      |                        | group by:Column#3, funcs:firstrow(Column#4)->test.t0.c0                       |
| └─PartitionUnion_13            | 2.00    | root      |                        |                                                                               |
|   ├─HashAgg_16                 | 1.00    | root      |                        | group by:1, funcs:firstrow(test.t0.c0)->Column#4, funcs:firstrow(1)->Column#3 |
|   │ └─TableReader_21           | 1.00    | root      |                        | data:TableFullScan_20                                                         |
|   │   └─TableFullScan_20       | 1.00    | cop[tikv] | table:t0, partition:p0 | keep order:false, stats:pseudo                                                |
|   └─HashAgg_24                 | 1.00    | root      |                        | group by:1, funcs:firstrow(test.t0.c0)->Column#4, funcs:firstrow(1)->Column#3 |
|     └─TableReader_29           | 2.00    | root      |                        | data:TableFullScan_28                                                         |
|       └─TableFullScan_28       | 2.00    | cop[tikv] | table:t0, partition:p1 | keep order:false, stats:pseudo                                                |
+--------------------------------+---------+-----------+------------------------+-------------------------------------------------------------------------------+
8 rows in set, 1 warning (0.00 sec)

mysql> select v0.c0 from v0;
+------+
| c0   |
+------+
|    1 |
+------+
1 row in set, 1 warning (0.00 sec)

mysql> select v0.c0 from v0;
+------+
| c0   |
+------+
|    0 |
+------+
1 row in set, 1 warning (0.01 sec)

mysql> SELECT t0.c0 FROM t0 GROUP BY (FALSE)NOT LIKE(1);
+------+
| c0   |
+------+
|    1 |
+------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT t0.c0 FROM t0 GROUP BY (FALSE)NOT LIKE(1);
+------+
| c0   |
+------+
|    0 |
+------+
1 row in set, 1 warning (0.00 sec)

Maybe we can output a warning if the view definition breaks the only_full_group_by attribute.

[mjonss]

Closing as duplicate of #53175