missing where condition lead to wrong query result

[wjhuang2016]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE `t8f295b68` (
  `col_44` date NOT NULL,
  `col_45` json DEFAULT NULL,
  `col_46` blob NOT NULL,
  PRIMARY KEY (`col_44`) /*T![clustered_index] CLUSTERED */,
  UNIQUE KEY `idx_16` (`col_46`(3)),
  KEY `idx_17` ((cast(`col_45` as double array)))
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
INSERT INTO `t8f295b68` VALUES('1970-06-23','[0.4560478695486992, 0.803651626010387]',x'7536255340506e4c33316848237a35'),('1970-09-25','[0.15199642284684986, 0.6321478001858613, 0.7656785642600782]',x'46257656295764673d4d2879486a7064'),('1970-12-20','[0.1945757596453029, 0.805924917565015, 0.9514989635078432]',x'3237585f587937596440666c'),('1971-01-30','[0.8674221724194489, 0.5874297776348085, 0.953305932613583, 0.3597865188732217, 0.9685946450023771]',x'776435472d306a'),('1971-04-19','[0.3962760654250889, 0.3033630486210956, 0.5788307700170308, 0.45967119199449186, 0.18040912218010932]',x'5f6e595446'),('1971-07-13','[0.5430757366310748, 0.8018899907743144, 0.8380302518531714, 0.13858586403286638]',x'4c2a4a4f71402d476b61796e7975426859'),('1971-09-08','[0.5866539910043066, 0.47465227377222047, 0.3412140234543155, 0.8109510241444171, 0.7217020139220653]',x'21326e4240524d626451467226624d36473264'),('1974-03-11','[0.9692492406521573]',x'7625754e5e55'),('1974-06-21','[0.8026878685767292]',x'7233744b2340285f'),('1975-06-08','[0.9922869004082567, 0.13102472710817054, 0.6557189141392675, 0.7616974552959114, 0.9653986970900055]',x'384261614d'),('1977-10-19','[0.33915755092357786, 0.612813632848902, 0.31310578556186824, 0.7695374222593716, 0.6996560322741089]',x'436d37594d7529734e412d513339'),('1978-01-31','[0.9327357034423996, 0.5066011929178722, 0.35172580278260285]',x'5f2145376d3742284254726971374f6d62'),('1978-08-23','[0.05090948016614748]',x'42485a4e322d2444'),('1980-03-16','[0.49342945147794093, 0.3032388374311525, 0.7358262102414459, 0.28035992762300044, 0.9099270917809736]',x'244863456e506b4024366e7a5f4a4a4645'),('1981-11-13','[0.014281917443517428, 0.31751875796372186, 0.5653893294127147, 0.6252442676826157, 0.6354719008300445]',x'4959463d2d6359796e377854754d2470'),('1983-10-16','[0.6992056327393458, 0.6285491992936737]',x'5175714844556626514743434b7543314249'),('1984-04-16','[0.5253196030542662, 0.3561411260568334, 0.7687615143503639, 0.4271982783883207, 0.20382816433225448]',x'475176'),('1988-07-19','[0.9233398239291353, 0.9396459773262974, 0.540018481999012, 0.181978533893545]',x'652539286c5f7e6b482a7265575a'),('1988-08-27','[0.9206220563718938]',x'475f693338392b646847'),('1989-12-07','[0.7964395106523453, 0.15227139590401054, 0.008643835436697868, 0.49470337240413564, 0.421379488694248]',x'377a767a65517647364856'),('1994-09-19','[0.19716919785297451]',x'3d26'),('1994-10-09',NULL,x'2a475463505136376861'),('1997-01-28','[0.30777386345347096]',x'70335e5e506b34236231476c35465e5e6d7448'),('2003-03-20','[0.23787267537350917, 0.5250550691578201, 0.9997853645717626]',x'404925286e5e726b305e'),('2004-04-12','[0.5368094270222808, 0.5306352503604773]',x'3752686b497448347039462136405a4f7a32'),('2005-02-04','[0.6531851579067487, 0.43849173297844984]',x'7e472a33336e73374558386a47234d5578'),('2005-02-18','[0.6303813789290023, 0.8178417890758697, 0.5354859968496037, 0.8868549026347942, 0.7253817513425349]',x'7e2d426a566329774a566c24217729797824'),('2005-05-09','[0.4155497358943851, 0.7649690762144056, 0.08552617826430448, 0.8054846042993035, 0.40076060531272734]',x''),('2006-02-10','[0.0506631513523112]',x'54416c7752526b55695258'),('2006-05-28','[0.7391997763354717, 0.43722943543743453, 0.8497153956761379, 0.8287059733052305, 0.8111927284482152]',x'38306635334126614d3455717e6b5762'),('2006-12-11','[0.11237614634576035, 0.33642094098687275]',x'57'),('2008-11-15','[0.41624921945334875, 0.7943609327220645, 0.2305392033266452, 0.003126127180805482, 0.08258790143798625]',x'67255f572156'),('2010-04-12','[0.7426240813483267, 0.17460982411968, 0.5062606612544903, 0.8641725521049805, 0.9093439280689491]',x'4557'),('2010-07-01','[0.9644470375249405, 0.14310028546123896, 0.30118256015842687, 0.12793715319326038]',x'6b7279692374524d306955'),('2012-03-29','[0.3808266319303118, 0.7975392670993785, 0.9125172409606002]',x'29'),('2012-08-24','[0.2368334281622129, 0.42370258940822, 0.09738052754248587, 0.6459847041878707]',x'255864432531546d443d263357557665'),('2013-10-14','[0.6366359320820038, 0.5258960185446601, 0.005468600014788019, 0.19002172571117618, 0.797860044965581]',x'58'),('2014-09-11','[0.26022068165495205]',x'6367665a373d53435e'),('2014-12-13','[0.18800960481374288]',x'692a5e4a727a2a41617574427a55406d'),('2016-03-01','[0.25353352722827344, 0.4696086393527653]',x'61'),('2018-12-24','[0.8883173845659126, 0.18078860876275404, 0.782658946181266, 0.2313310335729729]',x'77685f6b2869412a362b51'),('2021-06-26','[0.7528824657017837, 0.5179168006354101]',x'754a5e426f5a'),('2024-09-14','[0.785269452318795, 0.17609269926690957]',x'78252d2a362b4970353d5344584b766a633676'),('2024-12-30','[0.00042683533117783784, 0.26595214790312577, 0.48558040788839835, 0.7869280150674137]',x'6b67723d515756'),('2026-09-06','[0.9365758574340017, 0.035546493857794695, 0.7754058903482861, 0.06044405480634493]',x'77682b6f25313955643539795a345a'),('2027-05-19','[0.8578090694848647]',x'242b766b2d77'),('2027-11-01','[0.24985659225233242, 0.30331796357453394, 0.7477630696060984, 0.2642551752530038]',x'43375f5e6f6a62'),('2029-08-02','[0.7763676611291416]',x'4d6f'),('2030-02-05','[0.009232987106033366, 0.00424470502234362, 0.2340937202479449, 0.796532430692608]',x'793769216c4345516953292b246f4a796a756d'),('2031-11-05','[0.3856369365849062, 0.5724653509287152, 0.10591783536660329, 0.9541071017537033, 0.40942237293930245]',x'2373472b5225524e6141'),('2033-04-19','[0.47756023582738844, 0.591267913674284, 0.8929064352436822]',x'49574372366e'),('2034-06-15','[0.02368921979891078, 0.3785450156472247, 0.7560160881159008]',x'37'),('2035-05-04','[0.9173342769673862, 0.09009465925826006, 0.42919053504258686, 0.9562530025069383]',x'7e5e2645487e563d615944363571413831'),('2036-08-13','[0.618229016298302]',x'6c50396b64334950624343545a716e');

SELECT `t8f295b68`.`col_44`, col_45 FROM `t8f295b68` WHERE NOT (`t8f295b68`.`col_44` BETWEEN '1980-03-18' AND '2011-10-24') GROUP BY `t8f295b68`.`col_46`,`t8f295b68`.`col_45` HAVING JSON_CONTAINS(`t8f295b68`.`col_45`, '0.540018481999012') OR JSON_OVERLAPS(`t8f295b68`.`col_45`, '[0.5785147169732324,0.8314968898215304,0.5226516826882698]');

desc SELECT `t8f295b68`.`col_44`, col_45 FROM `t8f295b68` WHERE NOT (`t8f295b68`.`col_44` BETWEEN '1980-03-18' AND '2011-10-24') GROUP BY `t8f295b68`.`col_46`,`t8f295b68`.`col_45` HAVING JSON_CONTAINS(`t8f295b68`.`col_45`, '0.540018481999012') OR JSON_OVERLAPS(`t8f295b68`.`col_45`, '[0.5785147169732324,0.8314968898215304,0.5226516826882698]');

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

Empty result

3. What did you see instead (Required)

mysql> SELECT `t8f295b68`.`col_44`, col_45 FROM `t8f295b68` WHERE NOT (`t8f295b68`.`col_44` BETWEEN '1980-03-18' AND '2011-10-24') GROUP BY `t8f295b68`.`col_46`,`t8f295b68`.`col_45` HAVING JSON_CONTAINS(`t8f295b68`.`col_45`, '0.540018481999012') OR JSON_OVERLAPS(`t8f295b68`.`col_45`, '[0.5785147169732324,0.8314968898215304,0.5226516826882698]');
+------------+--------------------------------------------------------------------------------+
| col_44     | col_45                                                                         |
+------------+--------------------------------------------------------------------------------+
| 1988-07-19 | [0.9233398239291353, 0.9396459773262974, 0.540018481999012, 0.181978533893545] |
+------------+--------------------------------------------------------------------------------+
1 row in set (0.02 sec)

mysql> desc SELECT `t8f295b68`.`col_44`, col_45 FROM `t8f295b68` WHERE NOT (`t8f295b68`.`col_44` BETWEEN '1980-03-18' AND '2011-10-24') GROUP BY `t8f295b68`.`col_46`,`t8f295b68`.`col_45` HAVING JSON_CONTAINS(`t8f295b68`.`col_45`, '0.540018481999012') OR JSON_OVERLAPS(`t8f295b68`.`col_45`, '[0.5785147169732324,0.8314968898215304,0.5226516826882698]');
+----------------------------------+---------+-----------+---------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                               | estRows | task      | access object                                                 | operator info                                                                                                                                                                                                                                 |
+----------------------------------+---------+-----------+---------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Selection_8                      | 0.17    | root      |                                                               | or(json_contains(test.t8f295b68.col_45, cast(cast("0.540018481999012", json BINARY), json BINARY)), json_overlaps(test.t8f295b68.col_45, cast(cast("[0.5785147169732324,0.8314968898215304,0.5226516826882698]", json BINARY), json BINARY))) |
| └─IndexMerge_16                  | 0.22    | root      |                                                               | type: union                                                                                                                                                                                                                                   |
|   ├─IndexRangeScan_11(Build)     | 0.05    | cop[tikv] | table:t8f295b68, index:idx_17(cast(`col_45` as double array)) | range:[0.540018481999012,0.540018481999012], keep order:false, stats:pseudo                                                                                                                                                                   |
|   ├─IndexRangeScan_12(Build)     | 0.05    | cop[tikv] | table:t8f295b68, index:idx_17(cast(`col_45` as double array)) | range:[0.5785147169732324,0.5785147169732324], keep order:false, stats:pseudo                                                                                                                                                                 |
|   ├─IndexRangeScan_13(Build)     | 0.05    | cop[tikv] | table:t8f295b68, index:idx_17(cast(`col_45` as double array)) | range:[0.8314968898215304,0.8314968898215304], keep order:false, stats:pseudo                                                                                                                                                                 |
|   ├─IndexRangeScan_14(Build)     | 0.05    | cop[tikv] | table:t8f295b68, index:idx_17(cast(`col_45` as double array)) | range:[0.5226516826882698,0.5226516826882698], keep order:false, stats:pseudo                                                                                                                                                                 |
|   └─TableRowIDScan_15(Probe)     | 0.22    | cop[tikv] | table:t8f295b68                                               | keep order:false, stats:pseudo                                                                                                                                                                                                                |
+----------------------------------+---------+-----------+---------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
7 rows in set, 5 warnings (0.01 sec

4. What is your TiDB version? (Required)

66a6d4c