IT mysql_test row failed

[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/tidb_ghpr_integration_common_test/detail/tidb_ghpr_integration_common_test/9005/pipeline/

run test [row] err: sql:EXPLAIN SELECT * FROM t1,t2 WHERE t1.a=t2.a AND t1.b=2;: failed to run query
"EXPLAIN SELECT * FROM t1,t2 WHERE t1.a=t2.a AND t1.b=2;"
 around line 194,
we need(525):
EXPLAIN SELECT * FROM t1,t2 WHERE t1.a=t2.a AND t1.b=2;
id      estRows task    access object   operator info
HashJoin_28     4.67    root            inner join, equal:[eq(row.t1.a, row.t2.a)]
├─TableReader_36(Build) 2.00    root            data:Selection_35
│ └─Selection_35        2.00    cop[tikv]               eq(row.t1.b, 2)
│   └─TableFullScan_34  6.00    cop[tikv]       table:t1        keep order:false
└─TableReader_41(Probe) 7.00    root            data:TableFullScan_40
  └─TableFullScan_40    7.00    cop[tikv]       table:t2        keep order:false
EXPLAIN SELECT * FROM t1,t2 WHERE (t1.a,t1.b)=(t2.
but got(525):
EXPLAIN SELECT * FROM t1,t2 WHERE t1.a=t2.a AND t1.b=2;
id      estRows task    access object   operator info
MergeJoin_8     4.67    root            inner join, left key:row.t1.a, right key:row.t2.a
├─IndexReader_33(Build) 7.00    root            index:IndexFullScan_32
│ └─IndexFullScan_32    7.00    cop[tikv]       table:t2, index:PRIMARY(a, b, c)        keep order:true
└─IndexReader_31(Probe) 2.00    root            index:Selection_30
  └─Selection_30        2.00    cop[tikv]               eq(row.t1.b, 2)
    └─IndexFullScan_29  6.00    cop[tikv]       table:t1, index:PRIMARY(a, b)   keep order:true

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

3. What did you see instead (Required)

4. What is your TiDB version? (Required)

[djshow832]

This one is very similar to #30512. I think the reason is the same.

[djshow832]

I tried several times against unistore and TiKV. Sometimes I added another analyze table, and the result changes from time to time:

• The plan for each table may be IndexFullScan, TableFullScan, or IndexRangeScan
• The join type may be IndexHashJoin or MergeJoin

All of the plans are fine because the costs are almost the same. I think the plan itself is unstable and we can remove the explain. WDYT? @qw4990

[qw4990]

"EXPLAIN SELECT * FROM t1,t2 WHERE t1.a=t2.a AND t1.b=2;" 
 around line 194, 
we need(614):
EXPLAIN SELECT * FROM t1,t2 WHERE t1.a=t2.a AND t1.b=2;
id      estRows task    access object   operator info
HashJoin_28     4.67    root            inner join, equal:[eq(row.t1.a, row.t2.a)]
├─TableReader_36(Build) 2.00    root            data:Selection_35
│ └─Selection_35        2.00    cop[tikv]               eq(row.t1.b, 2)
│   └─TableFullScan_34  6.00    cop[tikv]       table:t1        keep order:false
└─TableReader_41(Probe) 7.00    root            data:TableFullScan_40
  └─TableFullScan_40    7.00    cop[tikv]       table:t2        keep order:false
EXPLAIN SELECT * FROM t1,t2 WHERE (t1.a,t1.b)=(t2.a,2);
id      estRows task    access object   operator info
HashJoin_28     4.67    root            inner join, equa
but got(614):
EXPLAIN SELECT * FROM t1,t2 WHERE t1.a=t2.a AND t1.b=2;
id      estRows task    access object   operator info
IndexHashJoin_13        0.01    root            inner join, inner:IndexReader_10, outer key:row.t1.a, inner key:row.t2.a, equal cond:eq(row.t1.a, row.t2.a)
├─TableReader_36(Build) 0.01    root            data:Selection_35
│ └─Selection_35        0.01    cop[tikv]               eq(row.t1.b, 2)
│   └─TableFullScan_34  6.00    cop[tikv]       table:t1        keep order:false
└─IndexReader_10(Probe) 2.33    root            index:IndexRangeScan_9
  └─IndexRangeScan_9    2.33    cop[tikv]       table:t2, index:PRIMARY(a, b, c)        range: decided by [eq(row.t2.a, row.t1.a)], keep order:false

I cannot reproduce the case in the description but I meet a new failed case which caused by the statistics of t1.b is not loaded when using it, so the estimation of t1.b=2 is 0.01 instead of 2.

[qw4990]

A minimal reproducible case:

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a int, b int, PRIMARY KEY (a,b));
INSERT INTO t1 VALUES (1,1), (2,1), (3,1), (1,2), (3,2), (3,3);
ANALYZE TABLE t1;
ANALYZE TABLE t1 COLUMNS b;
EXPLAIN SELECT * FROM t1 WHERE t1.b=2;
-- wait 5s
EXPLAIN SELECT * FROM t1 WHERE t1.b=2;

[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.