Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

executor: indexXXXJoin error #15686

Closed
fzhedu opened this issue Mar 25, 2020 · 1 comment · Fixed by #15727
Closed

executor: indexXXXJoin error #15686

fzhedu opened this issue Mar 25, 2020 · 1 comment · Fixed by #15727
Assignees
@XuHuaiyu
Labels
severity/moderate sig/execution SIG execution type/bug The issue is confirmed as a bug.

Comments

@fzhedu
Copy link
Contributor

fzhedu commented Mar 25, 2020 •
edited
Loading

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do?

download the file with all DDLs
randgen_index_merge_join.txt

source randgen_index_merge_join.txt

SELECT /*+INL_MERGE_JOIN(table1,table2,table3,table4,table5,table6,table7,table8,table9,table10,table11,table12)*/ table1 . `col_int` AS field1 , table1 . `pk` AS field2 , table2 . `col_int` AS field3 , table2 . `col_int` AS field4 , table2 . `col_int` AS field5 , table1 . `col_int` AS field6 FROM  K AS table1  LEFT  JOIN A AS table2 ON  table1 . `col_int_key` =  table2 . `col_int_key`  WHERE table2 . `pk` < table1 . `pk`   ORDER BY field1, field2,field3,field4,field5,field6 ;

2. What did you expect to see?

mysql is right, and using hash join also is right, but indexjoin and indexHashJoin are also wrong.

+--------+--------+--------+--------+--------+--------+
| field1 | field2 | field3 | field4 | field5 | field6 |
+--------+--------+--------+--------+--------+--------+
|   NULL |     14 |      1 |      1 |      1 |   NULL |
|   NULL |     28 |   NULL |   NULL |   NULL |   NULL |
|   NULL |     28 |   NULL |   NULL |   NULL |   NULL |
|   NULL |     28 |      6 |      6 |      6 |   NULL |
|   NULL |     28 |      7 |      7 |      7 |   NULL |
|   NULL |     28 |      8 |      8 |      8 |   NULL |
|   NULL |     33 |      7 |      7 |      7 |   NULL |
|   NULL |     35 |   NULL |   NULL |   NULL |   NULL |
|   NULL |     35 |      6 |      6 |      6 |   NULL |
|   NULL |     46 |   NULL |   NULL |   NULL |   NULL |
|   NULL |     46 |   NULL |   NULL |   NULL |   NULL |
|   NULL |     46 |      3 |      3 |      3 |   NULL |
|   NULL |     46 |      6 |      6 |      6 |   NULL |
|   NULL |     46 |      7 |      7 |      7 |   NULL |
|   NULL |     46 |      8 |      8 |      8 |   NULL |
|   NULL |     60 |      0 |      0 |      0 |   NULL |
|   NULL |     60 |      2 |      2 |      2 |   NULL |
|   NULL |     60 |      3 |      3 |      3 |   NULL |
|   NULL |     60 |      3 |      3 |      3 |   NULL |
|   NULL |     60 |      5 |      5 |      5 |   NULL |
|   NULL |     60 |      6 |      6 |      6 |   NULL |
|   NULL |     66 |   NULL |   NULL |   NULL |   NULL |
|   NULL |     66 |      4 |      4 |      4 |   NULL |
|      0 |     36 |      0 |      0 |      0 |      0 |
|      0 |     36 |      2 |      2 |      2 |      0 |
|      0 |     45 |   NULL |   NULL |   NULL |      0 |
|      0 |     45 |      1 |      1 |      1 |      0 |
|      0 |     45 |      2 |      2 |      2 |      0 |
|      0 |     45 |      3 |      3 |      3 |      0 |
|      0 |     45 |      6 |      6 |      6 |      0 |
|      1 |     23 |      6 |      6 |      6 |      1 |
|      1 |     34 |      4 |      4 |      4 |      1 |
|      1 |     52 |   NULL |   NULL |   NULL |      1 |
|      1 |     52 |      2 |      2 |      2 |      1 |
|      1 |     52 |      5 |      5 |      5 |      1 |
|      1 |     52 |      7 |      7 |      7 |      1 |
|      1 |     57 |      0 |      0 |      0 |      1 |
|      1 |     57 |      2 |      2 |      2 |      1 |
|      1 |     57 |      3 |      3 |      3 |      1 |
|      1 |     57 |      3 |      3 |      3 |      1 |
|      1 |     57 |      5 |      5 |      5 |      1 |
|      1 |     57 |      6 |      6 |      6 |      1 |
|      1 |     64 |      1 |      1 |      1 |      1 |
|      1 |     64 |      1 |      1 |      1 |      1 |
|      1 |     64 |      2 |      2 |      2 |      1 |
|      1 |     69 |   NULL |   NULL |   NULL |      1 |
|      1 |     69 |   NULL |   NULL |   NULL |      1 |
|      1 |     69 |      2 |      2 |      2 |      1 |
|      1 |     69 |      5 |      5 |      5 |      1 |
|      1 |     69 |      7 |      7 |      7 |      1 |
|      1 |     69 |      7 |      7 |      7 |      1 |
|      2 |     17 |      4 |      4 |      4 |      2 |
|      2 |     25 |      0 |      0 |      0 |      2 |
|      2 |     25 |      3 |      3 |      3 |      2 |
|      2 |     40 |   NULL |   NULL |   NULL |      2 |
|      2 |     40 |      4 |      4 |      4 |      2 |
|      2 |     42 |      0 |      0 |      0 |      2 |
|      2 |     42 |      2 |      2 |      2 |      2 |
|      2 |     42 |      3 |      3 |      3 |      2 |
|      2 |     61 |      0 |      0 |      0 |      2 |
|      2 |     61 |      8 |      8 |      8 |      2 |
|      3 |     65 |   NULL |   NULL |   NULL |      3 |
|      3 |     65 |   NULL |   NULL |   NULL |      3 |
|      3 |     65 |      3 |      3 |      3 |      3 |
|      3 |     65 |      5 |      5 |      5 |      3 |
|      3 |     65 |      6 |      6 |      6 |      3 |
|      3 |     65 |      7 |      7 |      7 |      3 |
|      3 |     65 |      8 |      8 |      8 |      3 |
|      3 |     68 |      0 |      0 |      0 |      3 |
|      3 |     68 |      2 |      2 |      2 |      3 |
|      3 |     68 |      3 |      3 |      3 |      3 |
|      3 |     68 |      3 |      3 |      3 |      3 |
|      3 |     68 |      5 |      5 |      5 |      3 |
|      3 |     68 |      6 |      6 |      6 |      3 |
|      4 |     32 |   NULL |   NULL |   NULL |      4 |
|      4 |     32 |   NULL |   NULL |   NULL |      4 |
|      4 |     32 |      6 |      6 |      6 |      4 |
|      4 |     32 |      7 |      7 |      7 |      4 |
|      4 |     32 |      8 |      8 |      8 |      4 |
|      5 |     43 |      1 |      1 |      1 |      5 |
|      5 |     43 |      1 |      1 |      1 |      5 |
|      5 |     43 |      2 |      2 |      2 |      5 |
|      5 |     48 |      0 |      0 |      0 |      5 |
|      5 |     48 |      1 |      1 |      1 |      5 |
|      5 |     48 |      3 |      3 |      3 |      5 |
|      5 |     48 |      4 |      4 |      4 |      5 |
|      5 |     59 |      8 |      8 |      8 |      5 |
|      6 |     26 |      6 |      6 |      6 |      6 |
|      6 |     67 |   NULL |   NULL |   NULL |      6 |
|      6 |     67 |      4 |      4 |      4 |      6 |
|      7 |     47 |   NULL |   NULL |   NULL |      7 |
|      7 |     47 |      1 |      1 |      1 |      7 |
|      7 |     47 |      2 |      2 |      2 |      7 |
|      7 |     47 |      3 |      3 |      3 |      7 |
|      7 |     47 |      6 |      6 |      6 |      7 |
|      7 |     49 |   NULL |   NULL |   NULL |      7 |
|      7 |     49 |      1 |      1 |      1 |      7 |
|      7 |     49 |      2 |      2 |      2 |      7 |
|      7 |     49 |      2 |      2 |      2 |      7 |
|      7 |     49 |      3 |      3 |      3 |      7 |
|      7 |     49 |      6 |      6 |      6 |      7 |
|      7 |     70 |      0 |      0 |      0 |      7 |
|      7 |     70 |      7 |      7 |      7 |      7 |
|      7 |     70 |      8 |      8 |      8 |      7 |
|      8 |      9 |      0 |      0 |      0 |      8 |
|      8 |      9 |      3 |      3 |      3 |      8 |
|      8 |     16 |      1 |      1 |      1 |      8 |
|      8 |     31 |      6 |      6 |      6 |      8 |
|      8 |     39 |   NULL |   NULL |   NULL |      8 |
|      8 |     39 |      4 |      4 |      4 |      8 |
|      8 |     41 |      0 |      0 |      0 |      8 |
|      8 |     41 |      1 |      1 |      1 |      8 |
|      8 |     41 |      3 |      3 |      3 |      8 |
|      8 |     41 |      4 |      4 |      4 |      8 |
|      9 |     12 |      1 |      1 |      1 |      9 |
|      9 |     13 |   NULL |   NULL |   NULL |      9 |
|      9 |     13 |      6 |      6 |      6 |      9 |
|      9 |     13 |      7 |      7 |      7 |      9 |
|      9 |     22 |   NULL |   NULL |   NULL |      9 |
|      9 |     22 |   NULL |   NULL |   NULL |      9 |
|      9 |     22 |      6 |      6 |      6 |      9 |
|      9 |     22 |      7 |      7 |      7 |      9 |
|      9 |     22 |      8 |      8 |      8 |      9 |
|      9 |     27 |      6 |      6 |      6 |      9 |
|      9 |     56 |   NULL |   NULL |   NULL |      9 |
|      9 |     56 |   NULL |   NULL |   NULL |      9 |
|      9 |     56 |      3 |      3 |      3 |      9 |
|      9 |     56 |      6 |      6 |      6 |      9 |
|      9 |     56 |      7 |      7 |      7 |      9 |
|      9 |     56 |      8 |      8 |      8 |      9 |
|      9 |     58 |      1 |      1 |      1 |      9 |
|      9 |     58 |      1 |      1 |      1 |      9 |
|      9 |     58 |      2 |      2 |      2 |      9 |
|      9 |     62 |   NULL |   NULL |   NULL |      9 |
|      9 |     62 |   NULL |   NULL |   NULL |      9 |
|      9 |     62 |      2 |      2 |      2 |      9 |
|      9 |     62 |      5 |      5 |      5 |      9 |
|      9 |     62 |      7 |      7 |      7 |      9 |
|      9 |     62 |      7 |      7 |      7 |      9 |
|      9 |     63 |   NULL |   NULL |   NULL |      9 |
|      9 |     63 |   NULL |   NULL |   NULL |      9 |
|      9 |     63 |      2 |      2 |      2 |      9 |
|      9 |     63 |      5 |      5 |      5 |      9 |
|      9 |     63 |      7 |      7 |      7 |      9 |
|      9 |     63 |      7 |      7 |      7 |      9 |
+--------+--------+--------+--------+--------+--------+
145 rows in set, 1 warning (0.01 sec)

3. What did you see instead?

+--------+--------+--------+--------+--------+--------+
| field1 | field2 | field3 | field4 | field5 | field6 |
+--------+--------+--------+--------+--------+--------+
|   NULL |     28 |   NULL |   NULL |   NULL |   NULL |
|   NULL |     28 |   NULL |   NULL |   NULL |   NULL |
|   NULL |     28 |      6 |      6 |      6 |   NULL |
|   NULL |     28 |      7 |      7 |      7 |   NULL |
|   NULL |     28 |      8 |      8 |      8 |   NULL |
|   NULL |     33 |      7 |      7 |      7 |   NULL |
|   NULL |     35 |   NULL |   NULL |   NULL |   NULL |
|   NULL |     35 |      6 |      6 |      6 |   NULL |
|   NULL |     46 |   NULL |   NULL |   NULL |   NULL |
|   NULL |     46 |   NULL |   NULL |   NULL |   NULL |
|   NULL |     46 |      3 |      3 |      3 |   NULL |
|   NULL |     46 |      6 |      6 |      6 |   NULL |
|   NULL |     46 |      7 |      7 |      7 |   NULL |
|   NULL |     46 |      8 |      8 |      8 |   NULL |
|   NULL |     60 |      0 |      0 |      0 |   NULL |
|   NULL |     60 |      2 |      2 |      2 |   NULL |
|   NULL |     60 |      3 |      3 |      3 |   NULL |
|   NULL |     60 |      3 |      3 |      3 |   NULL |
|   NULL |     60 |      5 |      5 |      5 |   NULL |
|   NULL |     60 |      6 |      6 |      6 |   NULL |
|   NULL |     66 |   NULL |   NULL |   NULL |   NULL |
|   NULL |     66 |      4 |      4 |      4 |   NULL |
|      0 |     36 |      0 |      0 |      0 |      0 |
|      0 |     36 |      2 |      2 |      2 |      0 |
|      0 |     45 |   NULL |   NULL |   NULL |      0 |
|      0 |     45 |      1 |      1 |      1 |      0 |
|      0 |     45 |      2 |      2 |      2 |      0 |
|      0 |     45 |      3 |      3 |      3 |      0 |
|      0 |     45 |      6 |      6 |      6 |      0 |
|      1 |     23 |      6 |      6 |      6 |      1 |
|      1 |     34 |      4 |      4 |      4 |      1 |
|      1 |     52 |   NULL |   NULL |   NULL |      1 |
|      1 |     52 |      2 |      2 |      2 |      1 |
|      1 |     52 |      5 |      5 |      5 |      1 |
|      1 |     52 |      7 |      7 |      7 |      1 |
|      1 |     57 |      0 |      0 |      0 |      1 |
|      1 |     57 |      2 |      2 |      2 |      1 |
|      1 |     57 |      3 |      3 |      3 |      1 |
|      1 |     57 |      3 |      3 |      3 |      1 |
|      1 |     57 |      5 |      5 |      5 |      1 |
|      1 |     57 |      6 |      6 |      6 |      1 |
|      1 |     69 |   NULL |   NULL |   NULL |      1 |
|      1 |     69 |   NULL |   NULL |   NULL |      1 |
|      1 |     69 |      2 |      2 |      2 |      1 |
|      1 |     69 |      5 |      5 |      5 |      1 |
|      1 |     69 |      7 |      7 |      7 |      1 |
|      1 |     69 |      7 |      7 |      7 |      1 |
|      2 |     17 |      4 |      4 |      4 |      2 |
|      2 |     25 |      0 |      0 |      0 |      2 |
|      2 |     25 |      3 |      3 |      3 |      2 |
|      2 |     40 |   NULL |   NULL |   NULL |      2 |
|      2 |     40 |      4 |      4 |      4 |      2 |
|      2 |     42 |      0 |      0 |      0 |      2 |
|      2 |     42 |      2 |      2 |      2 |      2 |
|      2 |     42 |      3 |      3 |      3 |      2 |
|      2 |     61 |      0 |      0 |      0 |      2 |
|      2 |     61 |      8 |      8 |      8 |      2 |
|      3 |     65 |   NULL |   NULL |   NULL |      3 |
|      3 |     65 |   NULL |   NULL |   NULL |      3 |
|      3 |     65 |      3 |      3 |      3 |      3 |
|      3 |     65 |      5 |      5 |      5 |      3 |
|      3 |     65 |      6 |      6 |      6 |      3 |
|      3 |     65 |      7 |      7 |      7 |      3 |
|      3 |     65 |      8 |      8 |      8 |      3 |
|      3 |     68 |      0 |      0 |      0 |      3 |
|      3 |     68 |      2 |      2 |      2 |      3 |
|      3 |     68 |      3 |      3 |      3 |      3 |
|      3 |     68 |      3 |      3 |      3 |      3 |
|      3 |     68 |      5 |      5 |      5 |      3 |
|      3 |     68 |      6 |      6 |      6 |      3 |
|      4 |     32 |   NULL |   NULL |   NULL |      4 |
|      4 |     32 |   NULL |   NULL |   NULL |      4 |
|      4 |     32 |      6 |      6 |      6 |      4 |
|      4 |     32 |      7 |      7 |      7 |      4 |
|      4 |     32 |      8 |      8 |      8 |      4 |
|      5 |     48 |      0 |      0 |      0 |      5 |
|      5 |     48 |      1 |      1 |      1 |      5 |
|      5 |     48 |      3 |      3 |      3 |      5 |
|      5 |     48 |      4 |      4 |      4 |      5 |
|      5 |     59 |      8 |      8 |      8 |      5 |
|      6 |     26 |      6 |      6 |      6 |      6 |
|      6 |     67 |   NULL |   NULL |   NULL |      6 |
|      6 |     67 |      4 |      4 |      4 |      6 |
|      7 |     47 |   NULL |   NULL |   NULL |      7 |
|      7 |     47 |      1 |      1 |      1 |      7 |
|      7 |     47 |      2 |      2 |      2 |      7 |
|      7 |     47 |      3 |      3 |      3 |      7 |
|      7 |     47 |      6 |      6 |      6 |      7 |
|      7 |     49 |   NULL |   NULL |   NULL |      7 |
|      7 |     49 |      1 |      1 |      1 |      7 |
|      7 |     49 |      2 |      2 |      2 |      7 |
|      7 |     49 |      2 |      2 |      2 |      7 |
|      7 |     49 |      3 |      3 |      3 |      7 |
|      7 |     49 |      6 |      6 |      6 |      7 |
|      7 |     70 |      0 |      0 |      0 |      7 |
|      7 |     70 |      7 |      7 |      7 |      7 |
|      7 |     70 |      8 |      8 |      8 |      7 |
|      8 |      9 |      0 |      0 |      0 |      8 |
|      8 |      9 |      3 |      3 |      3 |      8 |
|      8 |     31 |      6 |      6 |      6 |      8 |
|      8 |     39 |   NULL |   NULL |   NULL |      8 |
|      8 |     39 |      4 |      4 |      4 |      8 |
|      8 |     41 |      0 |      0 |      0 |      8 |
|      8 |     41 |      1 |      1 |      1 |      8 |
|      8 |     41 |      3 |      3 |      3 |      8 |
|      8 |     41 |      4 |      4 |      4 |      8 |
|      9 |     13 |   NULL |   NULL |   NULL |      9 |
|      9 |     13 |      6 |      6 |      6 |      9 |
|      9 |     13 |      7 |      7 |      7 |      9 |
|      9 |     22 |   NULL |   NULL |   NULL |      9 |
|      9 |     22 |   NULL |   NULL |   NULL |      9 |
|      9 |     22 |      6 |      6 |      6 |      9 |
|      9 |     22 |      7 |      7 |      7 |      9 |
|      9 |     22 |      8 |      8 |      8 |      9 |
|      9 |     27 |      6 |      6 |      6 |      9 |
|      9 |     56 |   NULL |   NULL |   NULL |      9 |
|      9 |     56 |   NULL |   NULL |   NULL |      9 |
|      9 |     56 |      3 |      3 |      3 |      9 |
|      9 |     56 |      6 |      6 |      6 |      9 |
|      9 |     56 |      7 |      7 |      7 |      9 |
|      9 |     56 |      8 |      8 |      8 |      9 |
|      9 |     62 |   NULL |   NULL |   NULL |      9 |
|      9 |     62 |   NULL |   NULL |   NULL |      9 |
|      9 |     62 |      2 |      2 |      2 |      9 |
|      9 |     62 |      5 |      5 |      5 |      9 |
|      9 |     62 |      7 |      7 |      7 |      9 |
|      9 |     62 |      7 |      7 |      7 |      9 |
|      9 |     63 |   NULL |   NULL |   NULL |      9 |
|      9 |     63 |   NULL |   NULL |   NULL |      9 |
|      9 |     63 |      2 |      2 |      2 |      9 |
|      9 |     63 |      5 |      5 |      5 |      9 |
|      9 |     63 |      7 |      7 |      7 |      9 |
|      9 |     63 |      7 |      7 |      7 |      9 |
+--------+--------+--------+--------+--------+--------+
133 rows in set, 2 warnings (0.01 sec)

4. What version of TiDB are you using? (tidb-server -V or run select tidb_version(); on TiDB)

| 5.7.25-TiDB-v4.0.0-beta-523-g7eba696bb-dirty |
@fzhedu fzhedu added the type/bug The issue is confirmed as a bug. label Mar 25, 2020
@fzhedu fzhedu added the sig/execution SIG execution label Mar 25, 2020
@fzhedu fzhedu changed the title executor: indexMergeJoin error executor: indexXXXJoin error Mar 25, 2020
@XuHuaiyu XuHuaiyu self-assigned this Mar 26, 2020
@XuHuaiyu
Copy link
Contributor

A minor case to reproduce:

create table k (a int, pk int primary key, index(a));

insert into k values(0,8),(0,23),(1,21),(1,33),(1,52),(2,17),(2,34),(2,39),(2,40),(2,66),(2,67),(3,9),(3,25),(3,41),(3,48),(4,4),(4,11),(4,15),(4,26),(4,27),(4,31),(4,35),(4,45),(4,47),(4,49);

create table t (a int, pk int primary key, index(a));

insert into t values(3,4),(3,5),(3,27),(3,29),(3,57),(3,58),(3,79),(3,84),(3,92),(3,95);

select /*+ inl_join(t) */ count(*) from k left join t on k.a = t.a and k.pk > t.pk;

got 29, expected: 33

@XuHuaiyu XuHuaiyu mentioned this issue Mar 26, 2020
Merged
This was referenced Mar 27, 2020
Merged
Merged
Merged
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@XuHuaiyu XuHuaiyu

Labels
severity/moderate sig/execution SIG execution type/bug The issue is confirmed as a bug.
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

util, executor: use UnionRanges build index kv range for INLJ XuHuaiyu/tidb
3 participants
@fzhedu @XuHuaiyu @sre-bot