unexpected query result using having clause and partition

[wjhuang2016]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE `t4365b15e` (
  `col_18` tinyblob DEFAULT NULL,
  `col_19` smallint unsigned NOT NULL DEFAULT '14683',
  `col_20` char(39) COLLATE utf8_bin DEFAULT '',
  `col_21` time NOT NULL DEFAULT '01:08:07',
  `col_22` char(153) COLLATE utf8_unicode_ci NOT NULL DEFAULT '-仂',
  UNIQUE KEY `idx_11` (`col_22`,`col_21`) /*T![global_index] GLOBAL */,
  UNIQUE KEY `idx_12` (`col_22`) /*T![global_index] GLOBAL */
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
PARTITION BY RANGE COLUMNS(`col_22`)
(PARTITION `p0` VALUES LESS THAN ('腅tD蠊KV吵d啗s'),
 PARTITION `p1` VALUES LESS THAN (MAXVALUE));

INSERT INTO `t4365b15e` VALUES(x'4a5955476d',32987,'uQKDBD99zce','09:35:47','y瘇=r賜莶)2g壅T敤q屉'),(x'4a5955476d',25115,'uQKDBD99zce','09:35:47','@j-yDA'),(x'406b6271',22117,'c^E@*ybYRTqNkAgTFC5','19:31:21','綑鞗'),(x'2d5e',4433,'d8G_Ic3Y*!rom**','09:38:32','1g0餪Pc诡Y&4y槄(Ou稠9嘪'),(x'69595e6467682b',48233,'nP)$V','14:32:31','埼dR鿱'),(x'6f5f2b5e7a3346235a64787954784975566f',57918,'o^8g*wCsmo@FBXmOS','09:35:45','鰻e=r+'),(x'4a5955476d',65016,'uQKDBD99zce','09:35:47','w层'),(x'6169262d4b243d792a4775',58947,'uQKDBD99zce','09:35:47','wqT8擟t獱Ip'),(x'6d6e6a40415e48395e423634',20706,'MMz1zdUDgztb@','22:11:26','rUN'),(x'4a517a6b3066716f426823',41270,'v67T4$LO7uy0','19:28:09','!鞤鏈鋎2p擛)nX4QI'),(x'36697124377042514a2521516c232862',23768,'c~~J','00:39:02','m'),(x'4a5955476d',61509,'uQKDBD99zce','09:35:47','鐩UQBb籹JQ57N韧S仂Fw'),(x'4d6f78586d6e4a68426e5e2d71',41755,'wICNk7+@HQ&j)&ojFL','20:10:51','0GIUbF賕#B'),(x'475a2444684c4a41',24780,'cPIPfD9Pd','20:30:43','YO5(e秬v鼾Bk扣竷6I韤$Zp'),(x'4a5955476d',51992,'uQKDBD99zce','09:35:47','N!SwEUsn堶I鐱'),(x'4a5955476d',65535,'uQKDBD99zce','09:35:47','Z5NL畏閻蚵-'),(x'7751514d',25824,'LO9TOd^CI_MtE7LR^z','22:42:19','@睾掘~坒cq(o6X鈞'),(x'38423771215333455e2d3842566b7641',49763,'yCyNZ$M8Q','12:52:23','嵌&)oW佞u蒧48嗡8帽TdRf*'),(x'4a5955476d',65217,'uQKDBD99zce','09:35:47','%qnr'),(x'4a5955476d',37678,'uQKDBD99zce','09:35:47','陂c隊n杭S鶔瓱p5');

CREATE TABLE `tf460485d` (
  `col_57` tinytext COLLATE utf8_unicode_ci DEFAULT NULL,
  `col_58` varchar(477) COLLATE utf8_general_ci NOT NULL DEFAULT '覽',
  `col_59` tinyint unsigned NOT NULL,
  `col_60` tinyint(1) NOT NULL DEFAULT '0',
  `col_61` tinyint(1) NOT NULL,
  `col_62` date DEFAULT '2031-12-15',
  `col_63` smallint unsigned DEFAULT '28366',
  `col_64` varbinary(389) NOT NULL,
  UNIQUE KEY `idx_17` (`col_58`),
  PRIMARY KEY (`col_58`) /*T![clustered_index] NONCLUSTERED */ /*T![global_index] GLOBAL */,
  UNIQUE KEY `idx_19` (`col_58`,`col_59`,`col_61`) /*T![global_index] GLOBAL */,
  UNIQUE KEY `idx_20` (`col_58`,`col_63`,`col_61`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci
PARTITION BY RANGE COLUMNS(`col_58`)
(PARTITION `p0` VALUES LESS THAN ('$0Wc櫙'),
 PARTITION `p1` VALUES LESS THAN ('襑蒢$堀$hKiV'),
 PARTITION `p2` VALUES LESS THAN (MAXVALUE));

INSERT INTO `tf460485d` VALUES('1','R獾',127,1,0,'1999-06-26',NULL,x'6f416e515e4e'),('-9Wd+h9FNWgtZ','~x',82,0,0,'2035-10-28',NULL,x''),('=I','UtEvX殃h瞉CpWOE',171,1,0,'1973-03-18',858,x'524228455f71'),('0ATpI@W+8uF','xq烱O',146,1,0,'1986-12-17',26667,x'76664d59257456233951366f506e'),('1','dT啛鋌PeJh喅AL',33,1,0,'2022-05-06',5881,x'296748734d6f7634525f655f26556a7161'),('1','Y掿麶絊磵夬$楯%悒也贒R',255,1,0,'2014-11-06',10258,x'6f6a2a624a414e445a686a32237a7a23536664'),('0ATpI@W+8uF','D7Xa',203,1,0,'1981-05-12',26055,x'695a45617445344c5a233872436d'),('1','-Jw瞲80z灧UsY觱艵X礋',156,1,0,'2007-09-27',31907,x'6b74'),('9Zv(^6PEL5%r','cm痵e缓R崻*駌Z*TuM',175,0,1,'2031-10-08',32717,x'6f462d634f4b795e58454a423246646b356666'),('0ATpI@W+8uF','(3攓i痈XDQ許WL薹F錓',119,0,1,'1981-02-19',56610,x'6c2163514c5026'),('cCfZwARDfmRnhT4V8D+','$4#Y助怔s',230,0,0,'2020-06-23',12937,x'5455554d744a5a75412a43365f41387a5456'),('s(O04','fQjXE#L櫘UOa膸',157,0,0,'2010-12-19',28999,x'26494a73216342313d344d514d'),('LR5^o$*2','跪熝葌0O冾湂~(D5犣Y@*',81,0,0,'2013-05-30',15558,x'7a78625e4c4f6b752d472854'),('6*Y$H62VB)','襾X_梼乵',25,0,1,'2024-03-29',40076,x'67576c505a21457a576b264f64523d5e4149'),('VH5P+UikL(','e坲V',31,1,1,'2005-05-07',1799,x'785e'),('0ATpI@W+8uF','%yr',61,0,1,'1976-07-20',65535,x'2d6566576a'),('!hJ7J!C#','d讦Il8鬏挻#R蜃顧',241,1,0,'1975-08-22',46070,x'37665f6f4f6d797a3243'),('DEhuX+rH(Tqnww','P溃挋%T',224,1,0,'2006-04-13',60626,x'5e2a712a4c234479436b796a455274525459'),('~6','1H罥詟蝮%n6',127,0,0,'2024-06-12',24793,x'3d3126474a5236284a'),('0ATpI@W+8uF','yyR寓卖=淙Q!',148,1,0,'1991-07-05',32767,x''),('0ATpI@W+8uF','澪',105,0,0,'1981-02-15',NULL,x'5f406767'),('0ATpI@W+8uF','9騞G眡gMJP+v+鋜襑鲯I',171,1,1,'1993-06-26',47054,x'74466e576550'),('tZ@aRBI8&','%酬dV遇5',81,0,0,'1975-09-16',48645,x'2366546f7573756b267832');

SELECT SUM(`t4365b15e`.`col_19`) AS `r0`, `tf460485d`.`col_60` FROM `tf460485d` JOIN `t4365b15e` ON `tf460485d`.`col_60`=`t4365b15e`.`col_22` GROUP BY `tf460485d`.`col_60` HAVING `tf460485d`.`col_60` IN (1);

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

Not empty

3. What did you see instead (Required)

Empty result

4. What is your TiDB version? (Required)

39c35fc

[hawkingrei]

good plan in v8.5

+-----------------------------+----------+-----------+-----------------+--------------------------------------------------------------------------------------------------------+
| id                          | estRows  | task      | access object   | operator info                                                                                          |
+-----------------------------+----------+-----------+-----------------+--------------------------------------------------------------------------------------------------------+
| HashAgg_10                  | 8.00     | root      |                 | group by:Column#18, funcs:sum(Column#17)->Column#16, funcs:firstrow(Column#18)->test.tf460485d.col_60  |
| └─Projection_19             | 80000.00 | root      |                 | cast(test.t4365b15e.col_19, decimal(5,0) UNSIGNED BINARY)->Column#17, test.tf460485d.col_60->Column#18 |
|   └─HashJoin_12             | 80000.00 | root      |                 | CARTESIAN inner join                                                                                   |
|     ├─TableReader_15(Build) | 10.00    | root      | partition:all   | data:Selection_14                                                                                      |
|     │ └─Selection_14        | 10.00    | cop[tikv] |                 | eq(test.tf460485d.col_60, 1)                                                                           |
|     │   └─TableFullScan_13  | 10000.00 | cop[tikv] | table:tf460485d | keep order:false, stats:pseudo                                                                         |
|     └─TableReader_18(Probe) | 8000.00  | root      | partition:all   | data:Selection_17                                                                                      |
|       └─Selection_17        | 8000.00  | cop[tikv] |                 | eq(1, cast(test.t4365b15e.col_22, double BINARY))                                                      |
|         └─TableFullScan_16  | 10000.00 | cop[tikv] | table:t4365b15e | keep order:false, stats:pseudo                                                                         |
+-----------------------------+----------+-----------+-----------------+--------------------------------------------------------------------------------------------------------+

[hawkingrei]

bad plan in nightly

+-----------------------------+----------+-----------+-----------------+--------------------------------------------------------------------------------------------------------+
| id                          | estRows  | task      | access object   | operator info                                                                                          |
+-----------------------------+----------+-----------+-----------------+--------------------------------------------------------------------------------------------------------+
| HashAgg_13                  | 8.00     | root      |                 | group by:Column#19, funcs:sum(Column#18)->Column#16, funcs:firstrow(Column#19)->test.tf460485d.col_60  |
| └─Projection_42             | 80000.00 | root      |                 | cast(test.t4365b15e.col_19, decimal(5,0) UNSIGNED BINARY)->Column#18, test.tf460485d.col_60->Column#19 |
|   └─HashJoin_31             | 80000.00 | root      |                 | CARTESIAN inner join                                                                                   |
|     ├─TableReader_34(Build) | 10.00    | root      | partition:all   | data:Selection_33                                                                                      |
|     │ └─Selection_33        | 10.00    | cop[tikv] |                 | eq(test.tf460485d.col_60, 1)                                                                           |
|     │   └─TableFullScan_32  | 10000.00 | cop[tikv] | table:tf460485d | keep order:false, stats:pseudo                                                                         |
|     └─TableReader_37(Probe) | 8000.00  | root      | partition:p1    | data:Selection_36                                                                                      |
|       └─Selection_36        | 8000.00  | cop[tikv] |                 | eq(1, cast(test.t4365b15e.col_22, double BINARY))                                                      |
|         └─TableFullScan_35  | 10000.00 | cop[tikv] | table:t4365b15e | keep order:false, stats:pseudo                                                                         |
+-----------------------------+----------+-----------+-----------------+--------------------------------------------------------------------------------------------------------+

[hawkingrei]

caused by #59155

[hawkingrei]

It is related with #62720