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

index merge got wrong result #41293

Closed
wjhuang2016 opened this issue Feb 10, 2023 · 2 comments · Fixed by #41361
Closed

index merge got wrong result #41293

wjhuang2016 opened this issue Feb 10, 2023 · 2 comments · Fixed by #41361
Assignees
@guo-shaoge @time-and-fate
Labels
affects-5.4 This bug affects the 5.4.x(LTS) versions. affects-6.0 affects-6.1 This bug affects the 6.1.x(LTS) versions. affects-6.2 affects-6.3 affects-6.4 affects-6.5 This bug affects the 6.5.x(LTS) versions. affects-6.6 duplicate Issues or pull requests already exists. fuzz/schrddl severity/critical sig/planner SIG: Planner type/bug The issue is confirmed as a bug.

Comments

@wjhuang2016
Copy link
Member

wjhuang2016 commented Feb 10, 2023 •
edited
Loading

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE `c2b87b4e` (
  `7d83ef7c` double DEFAULT '1.663803102055177',
  `7cabceba` set('os','me','n6t','d','p7','ai','2hts','0','j33yx','2k','y') DEFAULT 'os,me,n6t,0,y',
  `93b472a8` tinyint(4) DEFAULT '-115',
  `598ce136` set('eujoh','0ny','i5','qhzj','6nut','f','c82rq','i13l','t2gv','i','0') DEFAULT 'eujoh,0ny,qhzj,f,c82rq,t2gv,i,0',
  `38092777` float DEFAULT '1.7303925',
  KEY `207991a6` (`7d83ef7c`,`93b472a8`),
  KEY `eda4fc8a` (`7d83ef7c`),
  KEY `4aca75dc` (`7cabceba`),
  KEY `473811b5` (`93b472a8`),
  KEY `e11e954c` (`598ce136`),
  KEY `7016a347` (`93b472a8`,`7cabceba`),
  KEY `65cdc839` (`598ce136`)
) ENGINE=InnoDB DEFAULT CHARSET=ascii COLLATE=ascii_bin /*T! SHARD_ROW_ID_BITS=6 */ COMMENT='4076571c';

INSERT INTO `c2b87b4e` VALUES (1609.217822723485,'d',-109,'6nut',7910.752), (1609.217822723485,'d',-109,'c82rq',7896.913), (1609.217822723485,'d',-109,'0',3225.4304), (1609.217822723485,'d',-109,'0ny',2654.9243), (1609.217822723485,'d',-109,'c82rq',NULL), (1609.217822723485,'d',-109,'6nut',7910.752), (1609.217822723485,'d',-109,'6nut',7910.752), (1609.217822723485,'d',-109,'6nut',7910.752), (1609.217822723485,'d',-109,'6nut',7910.752), (1609.217822723485,'d',-109,'6nut',5638.71), (1609.217822723485,'d',-109,'6nut',1305.846), (1609.217822723485,'d',-109,'6nut',143.02635), (1609.217822723485,'d',-109,'6nut',3176.9207), (1609.217822723485,'d',-109,'6nut',1.7303925), (1609.217822723485,'d',-109,'c82rq',9444.325), (5364.0973229331175,'j33yx',-107,NULL,4724.1025), (1609.217822723485,'d',-109,'f',3683.1667), (1609.217822723485,'d',-109,'0ny',4718.6187), (1609.217822723485,'d',-109,'6nut',7910.752), (1609.217822723485,'d',-109,'6nut',7910.752), (1609.217822723485,'d',-109,'6nut',7910.752), (1609.217822723485,'d',-109,'6nut',7910.752), (1609.217822723485,'d',-109,'6nut',7910.752), (1609.217822723485,'d',-109,'6nut',7910.752), (1609.217822723485,'d',-109,'6nut',7910.752), (1609.217822723485,'d',-109,'6nut',5708.5044), (1609.217822723485,'d',-109,'6nut',7910.752), (1609.217822723485,'d',-109,'6nut',7910.752), (2696.974142687824,'d',-40,'0',1.7303925), (1609.217822723485,'d',-109,'6nut',7910.752), (1609.217822723485,'d',-109,'qhzj',598.6983), (1609.217822723485,'d',-109,'f',9319.082), (1609.217822723485,'d',-109,'eujoh',3091.5088), (1609.217822723485,'d',-109,'i5',7759.6816), (1609.217822723485,'d',-109,'6nut',7910.752), (1609.217822723485,'d',-109,'6nut',7910.752), (1609.217822723485,'d',-109,'6nut',7910.752), (1609.217822723485,'d',-109,'6nut',7910.752), (1609.217822723485,'d',-109,'6nut',7910.752), (1609.217822723485,'d',-109,'6nut',7910.752), (1609.217822723485,'d',-109,'6nut',7910.752), (1609.217822723485,'d',-109,'6nut',7910.752), (1609.217822723485,'d',-109,'6nut',990.95374), (1609.217822723485,'d',-109,'6nut',1482.077), (1609.217822723485,'d',-109,'6nut',7910.752), (1609.217822723485,'d',-109,'qhzj',6924.8604), (1609.217822723485,'d',-109,'6nut',614.1022), (1609.217822723485,'d',-109,'6nut',9491.7), (1609.217822723485,'d',-109,'t2gv',2365.1626), (1609.217822723485,'d',-109,'6nut',1021.16693), (6801.635763729652,'me',-115,'t2gv',1.7303925), (1609.217822723485,'d',-109,'6nut',7910.752), (1609.217822723485,'d',-109,'6nut',7910.752), (1609.217822723485,'d',-109,'6nut',7910.752), (1609.217822723485,'d',-109,'6nut',7910.752), (1609.217822723485,'d',-109,'c82rq',8647.832), (1609.217822723485,'d',-109,'eujoh',3219.7595), (1609.217822723485,'d',-109,'0',9907.7705), (1609.217822723485,'d',-109,'6nut',7910.752), (1609.217822723485,'d',-109,'6nut',2225.922), (1609.217822723485,'d',-109,'6nut',276.25302), (1609.217822723485,'d',-109,'6nut',826.02734), (435.7165360703686,'0',-100,NULL,1428.476), (1609.217822723485,'d',-109,'i5',7998.759), (1609.217822723485,'d',-109,'eujoh',7487.266), (7050.011747806327,'2hts',123,NULL,3761.7837), (1609.217822723485,'d',-109,'i5',8635.771);

select  /*+ use_index_merge( `c2b87b4e` ) */   `c2b87b4e`.`7d83ef7c` as r0 , `c2b87b4e`.`7cabceba` as r1 , `c2b87b4e`.`7d83ef7c`
 as r2 from `c2b87b4e` where `c2b87b4e`.`598ce136` = '0' or not( `c2b87b4e`.`7cabceba` between 'me' and 'y' );

select  `c2b87b4e`.`7d83ef7c` as r0 , `c2b87b4e`.`7cabceba` as r1 , `c2b87b4e`.`7d83ef7c` as r2 from `c2b87b4e` where `c2b87b4e`.`598ce136` = '0' or not( `c2b87b4e`.`7cabceba` between 'me' and 'y' );

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

Two queries got the same results.

3. What did you see instead (Required)

mysql>  select  /*+ use_index_merge( `c2b87b4e` ) */   `c2b87b4e`.`7d83ef7c` as r0 , `c2b87b4e`.`7cabceba` as r1 , `c2b87b4e`.`7d83ef7c` as r2 from `c2b87b4e` where `c2b87b4e`.`598ce136` = '0' or not( `c2b87b4e`.`7cabceba` between 'me' and 'y' );
+-------------------+------+-------------------+
| r0                | r1   | r2                |
+-------------------+------+-------------------+
| 1609.217822723485 | d    | 1609.217822723485 |
| 2696.974142687824 | d    | 2696.974142687824 |
| 1609.217822723485 | d    | 1609.217822723485 |
+-------------------+------+-------------------+
3 rows in set (0.01 sec)

mysql>  select   `c2b87b4e`.`7d83ef7c` as r0 , `c2b87b4e`.`7cabceba` as r1 , `c2b87b4e`.`7d83ef7c` as r2 from `c2b87b4e`;
+--------------------+-------+--------------------+
| r0                 | r1    | r2                 |
+--------------------+-------+--------------------+
|  1609.217822723485 | d     |  1609.217822723485 |
|  1609.217822723485 | d     |  1609.217822723485 |
|  1609.217822723485 | d     |  1609.217822723485 |
|  1609.217822723485 | d     |  1609.217822723485 |
|  1609.217822723485 | d     |  1609.217822723485 |
|  1609.217822723485 | d     |  1609.217822723485 |
|  1609.217822723485 | d     |  1609.217822723485 |
|  1609.217822723485 | d     |  1609.217822723485 |
|  1609.217822723485 | d     |  1609.217822723485 |
|  1609.217822723485 | d     |  1609.217822723485 |
|  1609.217822723485 | d     |  1609.217822723485 |
|  1609.217822723485 | d     |  1609.217822723485 |
|  1609.217822723485 | d     |  1609.217822723485 |
|  1609.217822723485 | d     |  1609.217822723485 |
|  1609.217822723485 | d     |  1609.217822723485 |
| 5364.0973229331175 | j33yx | 5364.0973229331175 |
|  1609.217822723485 | d     |  1609.217822723485 |
|  1609.217822723485 | d     |  1609.217822723485 |
|  1609.217822723485 | d     |  1609.217822723485 |
|  1609.217822723485 | d     |  1609.217822723485 |
|  1609.217822723485 | d     |  1609.217822723485 |
|  1609.217822723485 | d     |  1609.217822723485 |
|  1609.217822723485 | d     |  1609.217822723485 |
|  1609.217822723485 | d     |  1609.217822723485 |
|  1609.217822723485 | d     |  1609.217822723485 |
|  1609.217822723485 | d     |  1609.217822723485 |
|  1609.217822723485 | d     |  1609.217822723485 |
|  1609.217822723485 | d     |  1609.217822723485 |
|  2696.974142687824 | d     |  2696.974142687824 |
|  1609.217822723485 | d     |  1609.217822723485 |
|  1609.217822723485 | d     |  1609.217822723485 |
|  1609.217822723485 | d     |  1609.217822723485 |
|  1609.217822723485 | d     |  1609.217822723485 |
|  1609.217822723485 | d     |  1609.217822723485 |
|  1609.217822723485 | d     |  1609.217822723485 |
|  1609.217822723485 | d     |  1609.217822723485 |
|  1609.217822723485 | d     |  1609.217822723485 |
|  1609.217822723485 | d     |  1609.217822723485 |
|  1609.217822723485 | d     |  1609.217822723485 |
|  1609.217822723485 | d     |  1609.217822723485 |
|  1609.217822723485 | d     |  1609.217822723485 |
|  1609.217822723485 | d     |  1609.217822723485 |
|  1609.217822723485 | d     |  1609.217822723485 |
|  1609.217822723485 | d     |  1609.217822723485 |
|  1609.217822723485 | d     |  1609.217822723485 |
|  1609.217822723485 | d     |  1609.217822723485 |
|  1609.217822723485 | d     |  1609.217822723485 |
|  1609.217822723485 | d     |  1609.217822723485 |
|  1609.217822723485 | d     |  1609.217822723485 |
|  1609.217822723485 | d     |  1609.217822723485 |
|  6801.635763729652 | me    |  6801.635763729652 |
|  1609.217822723485 | d     |  1609.217822723485 |
|  1609.217822723485 | d     |  1609.217822723485 |
|  1609.217822723485 | d     |  1609.217822723485 |
|  1609.217822723485 | d     |  1609.217822723485 |
|  1609.217822723485 | d     |  1609.217822723485 |
|  1609.217822723485 | d     |  1609.217822723485 |
|  1609.217822723485 | d     |  1609.217822723485 |
|  1609.217822723485 | d     |  1609.217822723485 |
|  1609.217822723485 | d     |  1609.217822723485 |
|  1609.217822723485 | d     |  1609.217822723485 |
|  1609.217822723485 | d     |  1609.217822723485 |
|  435.7165360703686 | 0     |  435.7165360703686 |
|  1609.217822723485 | d     |  1609.217822723485 |
|  1609.217822723485 | d     |  1609.217822723485 |
|  7050.011747806327 | 2hts  |  7050.011747806327 |
|  1609.217822723485 | d     |  1609.217822723485 |
+--------------------+-------+--------------------+
67 rows in set, 1 warning (0.00 sec)

4. What is your TiDB version? (Required)

master
@wjhuang2016 wjhuang2016 added type/bug The issue is confirmed as a bug. fuzz/schrddl labels Feb 10, 2023
@jebter jebter added sig/planner SIG: Planner severity/critical affects-5.4 This bug affects the 5.4.x(LTS) versions. affects-6.1 This bug affects the 6.1.x(LTS) versions. affects-6.5 This bug affects the 6.5.x(LTS) versions. labels Feb 11, 2023
@ti-chi-bot ti-chi-bot added may-affects-4.0 This bug maybe affects 4.0.x versions. may-affects-5.0 This bug maybe affects 5.0.x versions. may-affects-5.1 This bug maybe affects 5.1.x versions. may-affects-5.2 This bug maybe affects 5.2.x versions. may-affects-5.3 This bug maybe affects 5.3.x versions. may-affects-6.0 may-affects-6.2 may-affects-6.3 may-affects-6.4 may-affects-6.6 labels Feb 11, 2023
@chrysan
Copy link
Contributor

chrysan commented Feb 11, 2023

The plan looks reasonable but the result is wrong.

mysql> explain select  /*+ use_index_merge( `c2b87b4e` ) */   `c2b87b4e`.`7d83ef7c` as r0 , `c2b87b4e`.`7cabceba` as r1 , `c2b87b4e`.`7d83ef7c` as r2 from `c2b87b4e` where `c2b87b4e`.`598ce136` = '0' or not( `c2b87b4e`.`7cabceba` between 'me' and 'y' );
+-----------------------------------+---------+-----------+------------------------------------------+------------------------------------------------------------------------------------------------------------------+
| id                                | estRows | task      | access object                            | operator info                                                                                                    |
+-----------------------------------+---------+-----------+------------------------------------------+------------------------------------------------------------------------------------------------------------------+
| Projection_4                      | 35.70   | root      |                                          | test.c2b87b4e.7d83ef7c, test.c2b87b4e.7cabceba, test.c2b87b4e.7d83ef7c                                           |
| └─Selection_5                     | 35.70   | root      |                                          | or(eq(test.c2b87b4e.598ce136, "0"), not(and(ge(test.c2b87b4e.7cabceba, "me"), le(test.c2b87b4e.7cabceba, "y")))) |
|   └─IndexMerge_10                 | 44.62   | root      |                                          |                                                                                                                  |
|     ├─IndexRangeScan_6(Build)     | 0.07    | cop[tikv] | table:c2b87b4e, index:e11e954c(598ce136) | range:["0","0"], keep order:false, stats:pseudo                                                                  |
|     ├─IndexRangeScan_7(Build)     | 22.27   | cop[tikv] | table:c2b87b4e, index:4aca75dc(7cabceba) | range:[-inf,"me"), keep order:false, stats:pseudo                                                                |
|     ├─IndexRangeScan_8(Build)     | 22.33   | cop[tikv] | table:c2b87b4e, index:4aca75dc(7cabceba) | range:("y",+inf], keep order:false, stats:pseudo                                                                 |
|     └─TableRowIDScan_9(Probe)     | 44.62   | cop[tikv] | table:c2b87b4e                           | keep order:false, stats:pseudo                                                                                   |
+-----------------------------------+---------+-----------+------------------------------------------+------------------------------------------------------------------------------------------------------------------+
7 rows in set (0.00 sec)

@guo-shaoge please take a look.

@time-and-fate time-and-fate added affects-6.0 and removed may-affects-4.0 This bug maybe affects 4.0.x versions. may-affects-5.1 This bug maybe affects 5.1.x versions. may-affects-5.2 This bug maybe affects 5.2.x versions. may-affects-5.3 This bug maybe affects 5.3.x versions. may-affects-5.0 This bug maybe affects 5.0.x versions. may-affects-6.0 may-affects-6.2 may-affects-6.3 may-affects-6.4 may-affects-6.6 labels Feb 14, 2023
@time-and-fate time-and-fate mentioned this issue Feb 14, 2023
Merged
12 tasks
ti-chi-bot pushed a commit that referenced this issue Feb 14, 2023
@time-and-fate
planner: add missing CanExprsPushDown checks when generating IndexM…
74875da 
…erge path for `or` (#41361)

close #41273, close #41293
This was referenced Feb 14, 2023
Open
Merged
Merged
ti-chi-bot added a commit that referenced this issue Feb 14, 2023
@ti-chi-bot
planner: add missing CanExprsPushDown checks when generating IndexM…
1b9e79c 
…erge path for `or` (#41361) (#41397)

close #41273, close #41293
blacktear23 pushed a commit to blacktear23/tidb that referenced this issue Feb 15, 2023
@time-and-fate @blacktear23
planner: add missing CanExprsPushDown checks when generating IndexM…
9bfee2b 
…erge path for `or` (pingcap#41361)

close pingcap#41273, close pingcap#41293
ghazalfamilyusa pushed a commit to ghazalfamilyusa/tidb that referenced this issue Feb 15, 2023
@time-and-fate @ghazalfamilyusa
planner: add missing CanExprsPushDown checks when generating IndexM…
e7a302f 
…erge path for `or` (pingcap#41361)

close pingcap#41273, close pingcap#41293
@guo-shaoge guo-shaoge added the duplicate Issues or pull requests already exists. label Mar 29, 2023
@guo-shaoge
Copy link
Collaborator

dup with #41273

ti-chi-bot added a commit that referenced this issue Mar 29, 2023
@ti-chi-bot
planner: add missing CanExprsPushDown checks when generating IndexM…
aac228a 
…erge path for `or` (#41361) (#41396)

close #41273, close #41293
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@guo-shaoge guo-shaoge

@time-and-fate time-and-fate

Labels
affects-5.4 This bug affects the 5.4.x(LTS) versions. affects-6.0 affects-6.1 This bug affects the 6.1.x(LTS) versions. affects-6.2 affects-6.3 affects-6.4 affects-6.5 This bug affects the 6.5.x(LTS) versions. affects-6.6 duplicate Issues or pull requests already exists. fuzz/schrddl severity/critical sig/planner SIG: Planner type/bug The issue is confirmed as a bug.
Projects
None yet
Milestone
No milestone
6 participants
@jebter @chrysan @guo-shaoge @wjhuang2016 @time-and-fate @ti-chi-bot