IT mysqltest delete failed with the plan changed

[aytrack]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

in ci https://ci.pingcap.net/blue/organizations/jenkins/tikv_ghpr_integration_common_test/detail/tikv_ghpr_integration_common_test/3248/pipeline
the case is

CREATE TABLE t1 (a int not null,b int not null);
CREATE TABLE t2 (a int not null, b int not null, primary key (a,b));
CREATE TABLE t3 (a int not null, b int not null, primary key (a,b));
insert into t1 values (1,1),(2,1),(1,3);
insert into t2 values (1,1),(2,2),(3,3);
insert into t3 values (1,1),(2,1),(1,3);
select * from t1,t2,t3 where t1.a=t2.a AND t2.b=t3.a and t1.b=t3.b order by t1.a,t1.b;
a	b	a	b	a	b
1	1	1	1	1	1
1	3	1	1	1	3
2	1	2	2	2	1
analyze table t1;
analyze table t2;
analyze table t3;
explain select * from t1,t2,t3 where t1.a=t2.a AND t2.b=t3.a and t1.b=t3.b;
id	estRows	task	access object	operator info
HashJoin_21	3.00	root		inner join, equal:[eq(delete.t2.b, delete.t3.a) eq(delete.t1.b, delete.t3.b)]
├─TableReader_41(Build)	3.00	root		data:TableFullScan_40
│ └─TableFullScan_40	3.00	cop[tikv]	table:t3	keep order:false
└─HashJoin_32(Probe)	3.00	root		inner join, equal:[eq(delete.t1.a, delete.t2.a)]
  ├─TableReader_37(Build)	3.00	root		data:TableFullScan_36
  │ └─TableFullScan_36	3.00	cop[tikv]	table:t2	keep order:false
  └─TableReader_35(Probe)	3.00	root		data:TableFullScan_34
    └─TableFullScan_34	3.00	cop[tikv]	table:t1	keep order:false

run test [delete] err: sql:explain select * from t1,t2,t3 where t1.a=t2.a AND t2.b=t3.a and t1.b=t3.b;: failed to run query
"explain select * from t1,t2,t3 where t1.a=t2.a AND t2.b=t3.a and t1.b=t3.b;"
 around line 100,
we need(743):
explain select * from t1,t2,t3 where t1.a=t2.a AND t2.b=t3.a and t1.b=t3.b;
id	estRows	task	access object	operator info
HashJoin_21	3.00	root		inner join, equal:[eq(delete.t2.b, delete.t3.a) eq(delete.t1.b, delete.t3.b)]
├─TableReader_41(Build)	3.00	root		data:TableFullScan_40
│ └─TableFullScan_40	3.00	cop[tikv]	table:t3	keep order:false
└─HashJoin_32(Probe)	3.00	root		inner join, equal:[eq(delete.t1.a, delete.t2.a)]
  ├─TableReader_37(Build)	3.00	root		data:TableFullScan_36
  │ └─TableFullScan_36	3.00	cop[tikv]	table:t2	keep order:false
  └─TableReader_35(Probe)	3.00	root		data:TableFullScan_34
    └─TableFullScan_34	3.00	cop[tikv]	table:t1	keep order:false
delete t2.*,t3.* from t1,t2,t3 where t1.a=t2
but got(743):
explain select * from t1,t2,t3 where t1.a=t2.a AND t2.b=t3.a and t1.b=t3.b;
id	estRows	task	access object	operator info
HashJoin_21	3.00	root		inner join, equal:[eq(delete.t2.b, delete.t3.a) eq(delete.t1.b, delete.t3.b)]
├─IndexReader_43(Build)	3.00	root		index:IndexFullScan_42
│ └─IndexFullScan_42	3.00	cop[tikv]	table:t3, index:PRIMARY(a, b)	keep order:false
└─HashJoin_32(Probe)	3.00	root		inner join, equal:[eq(delete.t1.a, delete.t2.a)]
  ├─IndexReader_39(Build)	3.00	root		index:IndexFullScan_38
  │ └─IndexFullScan_38	3.00	cop[tikv]	table:t2, index:PRIMARY(a, b)	keep order:false
  └─TableReader_35(Probe)	3.00	root		data:TableFullScan_34
    └─TableFullScan_34	3.00	cop[tikv]	table:t1	keep order:false

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

3. What did you see instead (Required)

4. What is your TiDB version? (Required)

[winoros]

Can we provide a script to find which commit changed the plan?
It's a little heavy work if all changes need to be checked by the people who didn't change it.

[yudongusa]

@aytrack please help to review winoros' question above

[aytrack]

This case has not been run before, and it looks like was added by https://github.com/pingcap/tidb-test/pull/1375 to fix #30281. Also, the first failure was found in #30294

[yudongusa]

This case has not been run before, and it looks like was added by pingcap/tidb-test#1375 to fix #30281. Also, the first failure was found in #30294

Neither of these issues seems related. Also further looking at the plan differences, both plan should have little actual performance differences, one tablescan 3 rows, the other indexscan (only) 3 rows.

[yudongusa]

@qw4990 please help to check if there is any potential issue/improvement from the costing side.

[mjonss]

This test works in the latest tested tidb repo commit c2279ce / ci.

[2022-01-13T12:27:07.707Z] ./t/delete.test: ok! 192 test cases passed, take time 7.809363475 s
[2022-01-13T12:27:07.708Z] time="2022-01-13T20:27:07+08:00" level=info msg="run test [delete] ok"

But it fails in the integration-common-test ci

Looking into how to make it more stable/passing both tests.

[qw4990]

The root cause is that the column sizes under TiKV and UniStore are different, so their costs are different:

mysql> show stats_histograms; -- in TiKV
+---------+------------+----------------+-------------+----------+---------------------+----------------+------------+--------------+--------------------+
| Db_name | Table_name | Partition_name | Column_name | Is_index | Update_time         | Distinct_count | Null_count | Avg_col_size | Correlation        |
+---------+------------+----------------+-------------+----------+---------------------+----------------+------------+--------------+--------------------+
| test    | t1         |                | a           |        0 | 2022-01-17 17:03:50 |              3 |          0 |            9 | 0.8285714285714286 |
| test    | t1         |                | b           |        0 | 2022-01-17 17:03:50 |              3 |          0 |            9 |                  1 |
| test    | t1         |                | PRIMARY     |        1 | 2022-01-17 17:03:50 |              6 |          0 |            0 |                  0 |
+---------+------------+----------------+-------------+----------+---------------------+----------------+------------+--------------+--------------------+
3 rows in set (0.00 sec)



mysql> show stats_histograms; -- in UniStore
+---------+------------+----------------+-------------+----------+---------------------+----------------+------------+--------------+--------------------+
| Db_name | Table_name | Partition_name | Column_name | Is_index | Update_time         | Distinct_count | Null_count | Avg_col_size | Correlation        |
+---------+------------+----------------+-------------+----------+---------------------+----------------+------------+--------------+--------------------+
| test    | t1         |                | a           |        0 | 2022-01-17 17:03:44 |              3 |          0 |            1 | 0.8285714285714286 |
| test    | t1         |                | b           |        0 | 2022-01-17 17:03:44 |              3 |          0 |            1 |                  1 |
| test    | t1         |                | PRIMARY     |        1 | 2022-01-17 17:03:44 |              6 |          0 |            0 |                  0 |
+---------+------------+----------------+-------------+----------+---------------------+----------------+------------+--------------+--------------------+
3 rows in set (0.00 sec)

Seems weird, I'm still investigating...

[qw4990]

The root cause is that the column sizes under TiKV and UniStore are different, so their costs are different:

mysql> show stats_histograms; -- in TiKV
+---------+------------+----------------+-------------+----------+---------------------+----------------+------------+--------------+--------------------+
| Db_name | Table_name | Partition_name | Column_name | Is_index | Update_time         | Distinct_count | Null_count | Avg_col_size | Correlation        |
+---------+------------+----------------+-------------+----------+---------------------+----------------+------------+--------------+--------------------+
| test    | t1         |                | a           |        0 | 2022-01-17 17:03:50 |              3 |          0 |            9 | 0.8285714285714286 |
| test    | t1         |                | b           |        0 | 2022-01-17 17:03:50 |              3 |          0 |            9 |                  1 |
| test    | t1         |                | PRIMARY     |        1 | 2022-01-17 17:03:50 |              6 |          0 |            0 |                  0 |
+---------+------------+----------------+-------------+----------+---------------------+----------------+------------+--------------+--------------------+
3 rows in set (0.00 sec)



mysql> show stats_histograms; -- in UniStore
+---------+------------+----------------+-------------+----------+---------------------+----------------+------------+--------------+--------------------+
| Db_name | Table_name | Partition_name | Column_name | Is_index | Update_time         | Distinct_count | Null_count | Avg_col_size | Correlation        |
+---------+------------+----------------+-------------+----------+---------------------+----------------+------------+--------------+--------------------+
| test    | t1         |                | a           |        0 | 2022-01-17 17:03:44 |              3 |          0 |            1 | 0.8285714285714286 |
| test    | t1         |                | b           |        0 | 2022-01-17 17:03:44 |              3 |          0 |            1 |                  1 |
| test    | t1         |                | PRIMARY     |        1 | 2022-01-17 17:03:44 |              6 |          0 |            0 |                  0 |
+---------+------------+----------------+-------------+----------+---------------------+----------------+------------+--------------+--------------------+
3 rows in set (0.00 sec)

Seems weird, I'm still investigating...

A minimal reproducible case for this:

create table tx (a int);
insert into tx values (1);
analyze table tx;
explain select * from tx where a=1; -- use the column a to let it can be loaded into memory
show stats_histograms where table_name="tx";

And the results are different between Unistore and TiKV:

mysql> show stats_histograms where table_name="tx"; -- Unistore
+---------+------------+----------------+-------------+----------+---------------------+----------------+------------+--------------+-------------+
| Db_name | Table_name | Partition_name | Column_name | Is_index | Update_time         | Distinct_count | Null_count | Avg_col_size | Correlation |
+---------+------------+----------------+-------------+----------+---------------------+----------------+------------+--------------+-------------+
| test    | tx         |                | a           |        0 | 2022-01-17 17:25:24 |              1 |          0 |            1 |           1 |
+---------+------------+----------------+-------------+----------+---------------------+----------------+------------+--------------+-------------+

mysql> show stats_histograms where table_name="tx"; -- In TiKV
+---------+------------+----------------+-------------+----------+---------------------+----------------+------------+--------------+-------------+
| Db_name | Table_name | Partition_name | Column_name | Is_index | Update_time         | Distinct_count | Null_count | Avg_col_size | Correlation |
+---------+------------+----------------+-------------+----------+---------------------+----------------+------------+--------------+-------------+
| test    | tx         |                | a           |        0 | 2022-01-17 17:26:31 |              1 |          0 |            9 |           1 |
+---------+------------+----------------+-------------+----------+---------------------+----------------+------------+--------------+-------------+

The Avg_col_size is 1 under Unistore while it's 9 under TiKV.

[qw4990]

I create a separate issue for this problem(#31744) and I'll comment related tests to make our tests stable and fix them thoroughly later.

[github-actions]

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