bug: explain select straight_join DISTINCT.......The key and rows in the test result is incorrect

[shangyanwen]

Describe the problem

CREATE TABLE t1(a int, b int, c int, KEY b(b), KEY c(c))engine=stonedb;
insert into t1 values (1,0,0),(2,0,0);
CREATE TABLE t2 (a int, b varchar(2), c varchar(2), PRIMARY KEY(a))engine=stonedb;
insert into t2 values (1,'',''), (2,'','');
CREATE TABLE t3 (a int, b int, PRIMARY KEY (a,b), KEY a (a), KEY b (b))engine=stonedb;
insert into t3 values (1,1),(1,2);
##must have "range checked" for t2
explain select straight_join DISTINCT t2.a,t2.b, t1.c from t1, t3, t2 
 where (t1.c=t2.a or (t1.c=t3.a and t2.a=t3.b)) and t1.b=556476786 and 
       t2.b like '%%' order by t2.b limit 0,1;
##test result
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra                                          |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+------------------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | ref  | b,c           | b    | 5       | const |   10 |   100.00 | Using temporary; Using filesort                |
|  1 | SIMPLE      | t3    | NULL       | ALL  | PRIMARY,a,b   | NULL | NULL    | NULL  |    2 |   100.00 | Using join buffer (Block Nested Loop)          |
|  1 | SIMPLE      | t2    | NULL       | ALL  | PRIMARY       | NULL | NULL    | NULL  |    2 |    50.00 | Range checked for each record (index map: 0x1) |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+------------------------------------------------+
3 rows in set, 1 warning (0.03 sec)
##note：key、key_len、rows，There is a numerical error

Expected behavior

## innodb test result
>explain select straight_join DISTINCT t2.a,t2.b, t1.c from t1, t3, t2 
    ->  where (t1.c=t2.a or (t1.c=t3.a and t2.a=t3.b)) and t1.b=556476786 and 
    ->        t2.b like '%%' order by t2.b limit 0,1;
+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref   | rows | filtered | Extra                                              |
+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | ref   | b,c           | b    | 5       | const |    1 |   100.00 | Using temporary; Using filesort                    |
|  1 | SIMPLE      | t3    | NULL       | index | PRIMARY,a,b   | a    | 4       | NULL  |    2 |   100.00 | Using index; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | t2    | NULL       | ALL   | PRIMARY       | NULL | NULL    | NULL  |    2 |    50.00 | Range checked for each record (index map: 0x1)     |
+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+----------------------------------------------------+
3 rows in set, 1 warning (0.01 sec)

How To Reproduce

CREATE TABLE t1(a int, b int, c int, KEY b(b), KEY c(c))engine=stonedb;
insert into t1 values (1,0,0),(2,0,0);
CREATE TABLE t2 (a int, b varchar(2), c varchar(2), PRIMARY KEY(a))engine=stonedb;
insert into t2 values (1,'',''), (2,'','');
CREATE TABLE t3 (a int, b int, PRIMARY KEY (a,b), KEY a (a), KEY b (b))engine=stonedb;
insert into t3 values (1,1),(1,2);
# must have "range checked" for t2
explain select straight_join DISTINCT t2.a,t2.b, t1.c from t1, t3, t2 
 where (t1.c=t2.a or (t1.c=t3.a and t2.a=t3.b)) and t1.b=556476786 and 
       t2.b like '%%' order by t2.b limit 0,1;

Environment

1. StoneDB for mysql5.7 (release)
2. Ubuntu 20.04.4

Are you interested in submitting a PR to solve the problem?

• [x] Yes, I will!

[konghaiya]

ACK

[konghaiya]

If the key is empty, it means there is no index. It is normal There is a join buffer behind it, which indicates that it is a nested loop