tidb分区表 timestamp(3)类型作为分区键的无解问题

🌞 建议反馈 产品需求
,
1

在分区表分区字段写法 floor(UNIX_TIMESTAMP(“2022-11-10 00:59:59.996”) * 1000) 可建表不支持分区裁剪，在v6.5.0上该问题依然存在，在timestamp(3)后续持续证明大概率是时区问题，导致分区落错，数据查不出来，形成要么不支持分区裁剪，要么数据查出来不全的死结。

【期望的需求行为】
在新版上把问题解决掉，支持 floor(UNIX_TIMESTAMP(“2022-11-10 00:59:59.996”) * 1000) 分区裁剪

【需求可替代方案】

【背景信息】
如哪些用户将从中获益，以及一些使用场景，任何API设计，模型或者图标都会更有帮助。

2

希望引起重视，已经带来很多的不方便了

3

补充下，一些讨论和背景信息在这：

4

感谢表妹补充，这个case就是 floor的浮点数精度问题导致的数据落错了分区

5

@Billmay表妹 这个有继续跟进吗？

6

你在 6.5 版本测试下

image
image1920×486 82.9 KB

7

可以给我发下你的建表语句吗

8

静态分区下会一一扫描所有分区

image
image1920×1095 216 KB

9

动态分区时候是这样的，没有使用裁剪，我的版本是6.1.2

image
image1684×476 61.3 KB

10

老哥，还需要支持吗？我可以支持

11

就是你之前帖子发的建表语句。可以在 6.5 测试一下，收集统计信息后看看是否满足要求。

| history_wti_alarm | CREATE TABLE `history_wti_alarm` (
  `id` bigint(20) NOT NULL,
  `alarm_id` varchar(64) NOT NULL COMMENT 'unique alarm id',
  `start_time` timestamp(3) NOT NULL,
  `end_time` timestamp(3) NULL DEFAULT NULL COMMENT 'Last sample timestamp',
  PRIMARY KEY (`alarm_id`,`start_time`) /*T![clustered_index] CLUSTERED */,
  UNIQUE KEY `uniq_alarm_id` (`alarm_id`,`start_time`),
  KEY `idx_start_time` (`start_time`),
  KEY `idx_end_time` (`end_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
PARTITION BY RANGE (FLOOR(UNIX_TIMESTAMP(`start_time`)))
(PARTITION `p1` VALUES LESS THAN (1640966400),
 PARTITION `p2` VALUES LESS THAN (1641744000),
 PARTITION `p3` VALUES LESS THAN (1642608000),
 PARTITION `p4` VALUES LESS THAN (1643558400),
 PARTITION `p5` VALUES LESS THAN (1643644800),
 PARTITION `p6` VALUES LESS THAN (1644422400),
 PARTITION `p7` VALUES LESS THAN (1645286400),
 PARTITION `p8` VALUES LESS THAN (1645977600),
 PARTITION `p9` VALUES LESS THAN (1646064000),
 PARTITION `p10` VALUES LESS THAN (1646841600),
 PARTITION `p11` VALUES LESS THAN (1647705600),
 PARTITION `p12` VALUES LESS THAN (1648656000),
 PARTITION `p13` VALUES LESS THAN (1648742400),
 PARTITION `p14` VALUES LESS THAN (1649520000),
 PARTITION `p15` VALUES LESS THAN (1650384000),
 PARTITION `p16` VALUES LESS THAN (1651248000),
 PARTITION `p17` VALUES LESS THAN (1651334400),
 PARTITION `p18` VALUES LESS THAN (1652112000),
 PARTITION `p19` VALUES LESS THAN (1652976000),
 PARTITION `p20` VALUES LESS THAN (1653926400),
 PARTITION `p21` VALUES LESS THAN (1654012800),
 PARTITION `p22` VALUES LESS THAN (1654790400),
 PARTITION `p23` VALUES LESS THAN (1655654400),
 PARTITION `p24` VALUES LESS THAN (1656518400),
 PARTITION `p25` VALUES LESS THAN (1656604800),
 PARTITION `p26` VALUES LESS THAN (1657382400),
 PARTITION `p27` VALUES LESS THAN (1658246400),
 PARTITION `p28` VALUES LESS THAN (1659196800),
 PARTITION `p29` VALUES LESS THAN (1659283200),
 PARTITION `p30` VALUES LESS THAN (1660060800),
 PARTITION `p31` VALUES LESS THAN (1660924800),
 PARTITION `p32` VALUES LESS THAN (1661875200),
 PARTITION `p33` VALUES LESS THAN (1661961600),
 PARTITION `p34` VALUES LESS THAN (1662739200),
 PARTITION `p35` VALUES LESS THAN (1663603200),
 PARTITION `p36` VALUES LESS THAN (1664467200),
 PARTITION `p37` VALUES LESS THAN (1664553600),
 PARTITION `p38` VALUES LESS THAN (1665331200),
 PARTITION `p39` VALUES LESS THAN (1666195200),
 PARTITION `p40` VALUES LESS THAN (1667145600),
 PARTITION `p41` VALUES LESS THAN (1667232000),
 PARTITION `p42` VALUES LESS THAN (1668009600),
 PARTITION `p43` VALUES LESS THAN (1668873600),
 PARTITION `p44` VALUES LESS THAN (1669737600),
 PARTITION `p45` VALUES LESS THAN (1669824000),
 PARTITION `p46` VALUES LESS THAN (1670601600),
 PARTITION `p47` VALUES LESS THAN (1671465600),
 PARTITION `p48` VALUES LESS THAN (1672416000),
 PARTITION `p49` VALUES LESS THAN (1672502400),
 PARTITION `p50` VALUES LESS THAN (1673280000),
 PARTITION `p51` VALUES LESS THAN (1674144000),
 PARTITION `p52` VALUES LESS THAN (1675094400),
 PARTITION `p53` VALUES LESS THAN (1675180800),
 PARTITION `p54` VALUES LESS THAN (1675958400),
 PARTITION `p55` VALUES LESS THAN (1676822400),
 PARTITION `p56` VALUES LESS THAN (1677513600),
 PARTITION `p57` VALUES LESS THAN (1677600000),
 PARTITION `p58` VALUES LESS THAN (1678377600),
 PARTITION `p59` VALUES LESS THAN (1679241600),
 PARTITION `p60` VALUES LESS THAN (1680192000),
 PARTITION `p61` VALUES LESS THAN (1680278400),
 PARTITION `p62` VALUES LESS THAN (1681056000),
 PARTITION `p63` VALUES LESS THAN (1681920000),
 PARTITION `p64` VALUES LESS THAN (1682784000),
 PARTITION `p65` VALUES LESS THAN (1682870400),
 PARTITION `p66` VALUES LESS THAN (1683648000),
 PARTITION `p67` VALUES LESS THAN (1684512000),
 PARTITION `p68` VALUES LESS THAN (1685462400),
 PARTITION `p69` VALUES LESS THAN (1685548800),
 PARTITION `p70` VALUES LESS THAN (1686326400),
 PARTITION `p71` VALUES LESS THAN (1687190400),
 PARTITION `p72` VALUES LESS THAN (1688054400),
 PARTITION `p73` VALUES LESS THAN (1688140800),
 PARTITION `p74` VALUES LESS THAN (1688918400),
 PARTITION `p75` VALUES LESS THAN (1689782400),
 PARTITION `p76` VALUES LESS THAN (1690732800),
 PARTITION `p77` VALUES LESS THAN (1690819200),
 PARTITION `p78` VALUES LESS THAN (1691596800),
 PARTITION `p79` VALUES LESS THAN (1692460800),
 PARTITION `p80` VALUES LESS THAN (1693411200),
 PARTITION `p81` VALUES LESS THAN (1693497600),
 PARTITION `p82` VALUES LESS THAN (1694275200),
 PARTITION `p83` VALUES LESS THAN (1695139200),
 PARTITION `p84` VALUES LESS THAN (1696003200),
 PARTITION `p85` VALUES LESS THAN (1696089600),
 PARTITION `p86` VALUES LESS THAN (1696867200),
 PARTITION `p87` VALUES LESS THAN (1697731200),
 PARTITION `p88` VALUES LESS THAN (1698681600),
 PARTITION `p90` VALUES LESS THAN (1698768000),
 PARTITION `p91` VALUES LESS THAN (1699545600),
 PARTITION `p92` VALUES LESS THAN (1700409600),
 PARTITION `p93` VALUES LESS THAN (1701273600),
 PARTITION `p94` VALUES LESS THAN (1701360000),
 PARTITION `p95` VALUES LESS THAN (1702137600),
 PARTITION `p96` VALUES LESS THAN (1703001600),
 PARTITION `p97` VALUES LESS THAN (1703952000),
 PARTITION `p1024` VALUES LESS THAN (MAXVALUE))

MySQL [test]> SELECT start_time FROM history_wti_alarm WHERE start_time>=‘2022-11-10 00:00:00.000’ and alarm_id=‘abc’;
±------------------------+
| start_time |
±------------------------+
| 2022-11-10 00:59:59.996 |
±------------------------+
1 row in set (0.01 sec)

MySQL [test]> explain SELECT start_time FROM history_wti_alarm WHERE start_time>=‘2022-11-10 00:00:00.000’ and alarm_id=‘abc’;
±-------------------------±--------±----------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±-------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
±-------------------------±--------±----------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±-------------------------------------------------------------------+
| Projection_4 | 1.00 | root | | test.history_wti_alarm.start_time |
| └─IndexReader_8 | 1.00 | root | partition:p43,p44,p45,p46,p47,p48,p49,p50,p51,p52,p53,p54,p55,p56,p57,p58,p59,p60,p61,p62,p63,p64,p65,p66,p67,p68,p69,p70,p71,p72,p73,p74,p75,p76,p77,p78,p79,p80,p81,p82,p83,p84,p85,p86,p87,p88,p90,p91,p92,p93,p94,p95,p96,p97,p1024 | index:IndexRangeScan_7 |
| └─IndexRangeScan_7 | 1.00 | cop[tikv] | table:history_wti_alarm, index:uniq_alarm_id(alarm_id, start_time) | range:[“abc” 2022-11-10 00:00:00.000,“abc” +inf], keep order:false |
±-------------------------±--------±----------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±-------------------------------------------------------------------+
3 rows in set (0.00 sec)

12

小哥哥你好，这个建表语句下分区裁剪是没有问题的，但是floor()函数有一个浮点数的进度问题，数据会落错分区，导致分区裁剪后数据查不出来，在论坛沟通后(tidb 对应时间点的数据在所在时间范围内查询不到)帖子在这里，分区的写法变成了PARTITION BY RANGE (FLOOR(UNIX_TIMESTAMP(start_time)*1000))，之后分区裁剪不生效了。

13

@yilong 新的建表sql在这里

CREATE TABLE `history_wti_alarm` (
`id` bigint not null,
`vehicle_id` char(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT 'vehicle id',
`alarm_id` varchar(64) NOT NULL  COMMENT 'unique alarm id',
`wti_code` varchar(16) NOT NULL DEFAULT '' COMMENT 'wti code',
`start_time` timestamp(3) NOT NULL,
`end_time` timestamp(3) NULL DEFAULT NULL COMMENT 'Last sample timestamp',
`adc_version_s` varchar(32) DEFAULT NULL COMMENT 'adc software version',
`adc_version_h` varchar(32) DEFAULT NULL COMMENT 'adc hardware version',
`cdc_version_s` varchar(32) DEFAULT NULL COMMENT 'cdc software version',
`cdc_version_h` varchar(32) DEFAULT NULL COMMENT 'cdc hardware version',
`cgw_version_s` varchar(32) DEFAULT NULL COMMENT 'cgw software version',
`cgw_version_h` varchar(32) DEFAULT NULL COMMENT 'cgw hardware version',
`vehicle_version_s` varchar(32) DEFAULT NULL COMMENT 'vehicle software version',
`vehicle_version_h` varchar(32) DEFAULT NULL COMMENT 'vehicle hardware version',
`alarm_tag` tinyint(3) unsigned NOT NULL DEFAULT 0 COMMENT '0: not activated, 1: activated, 2:test',
 ` nio_encoding` varchar(32) DEFAULT NULL COMMENT 'battery package encoding for nio',
  `latitude` decimal(9,6) DEFAULT 0.000000 NOT NULL COMMENT 'latitude(0-90) : north (plus), south(minus)',
  `longitude` decimal(9,6) DEFAULT 0.000000 NOT NULL COMMENT 'longitude(0-180) : east (plus), west(minus)',
  `city_code` char(6) DEFAULT NULL COMMENT 'poi city code',
  `model` varchar(32) DEFAULT NULL COMMENT 'vehicle model',
  `soc` decimal(4,1) unsigned DEFAULT NULL COMMENT 'vehicle soc',
  `mileage` int(10) unsigned DEFAULT NULL COMMENT 'mileage, 0-9999999',
  `chrg_sts` tinyint(3) unsigned NOT NULL DEFAULT 0 COMMENT 'charging Status, 0 = no charging, 1 = processing, 2 = complete, 3 = fault',
  `vehl_sts` tinyint(3) unsigned DEFAULT NULL COMMENT 'vehicle state, 1= driving, 2 = parked vehicle, 3 = driver present, 4 = sw update, 254 = abnormal, 255 = invalid',
  `update_time` timestamp(3) DEFAULT current_timestamp(3) ON UPDATE current_timestamp(3)  COMMENT 'Last update timestamp',
  `reported_local_tag` tinyint(3) unsigned NOT NULL DEFAULT 0 COMMENT '0: not report, 1: reported[D',
  `reported_national_tag` tinyint(3) unsigned NOT NULL DEFAULT 0 COMMENT '0: not report, 1: reported[D',
  `charger_type` tinyint(3) unsigned DEFAULT NULL COMMENT 'type of charger, NO_REQUEST = 0; NORMAL = 1; AC = 2; DC = 3; POWER_EXPRESS = 4; INVALID = 5',
  `status` tinyint(3) unsigned NOT NULL DEFAULT '1' COMMENT '状态码 1未响应 2已响应 3响应完成',
  `respond_by` varchar(32) NOT NULL DEFAULT '' COMMENT 'operator id',
  `respond_time` timestamp(3) NULL DEFAULT NULL COMMENT 'respond time',
  `update_by` varchar(32) NOT NULL DEFAULT '' COMMENT '最后更新人',
  `finish_maintenance` tinyint(3) NOT NULL DEFAULT 2 COMMENT '是否修理完成 1完成 2未完成',
  `initial_situation` varchar(200) NOT NULL DEFAULT '' COMMENT '初步确认情况',
  `initial_disposition` varchar(200) NOT NULL DEFAULT '' COMMENT '初步处置',
  `fault_symptom` varchar(200) NOT NULL DEFAULT '' COMMENT '故障现象',
  `maintenance_manner` varchar(200) NOT NULL DEFAULT '' COMMENT '故障维修方式',
  `additional_remarks` varchar(200) NOT NULL DEFAULT '' COMMENT '其他情况说明',
  `last_edit_time` timestamp(3) NULL DEFAULT NULL COMMENT '车辆故障最后编辑时间',
  `fota_tag` tinyint(3) unsigned DEFAULT NULL COMMENT 'alarm FOTA标签',
  `diag_tag` tinyint(3) unsigned DEFAULT NULL COMMENT 'alarm 诊断仪OBD接入标签',
  `soc_tag` tinyint(3) unsigned DEFAULT NULL COMMENT 'alarm 换电标签',
  `repair_tag` tinyint(3) unsigned DEFAULT NULL COMMENT 'alarm  维修工单标签',
  PRIMARY KEY (`alarm_id`,`start_time`),
  KEY `idx_vehicle` (`vehicle_id`),
  KEY `idx_start_time` (`start_time`),
  KEY `idx_wti_st` (`wti_code`,`start_time`),
  KEY `idx_vid_wc_st`(`vehicle_id`,`wti_code`,`start_time`)
  ) PARTITION BY RANGE(floor(UNIX_TIMESTAMP(start_time)*1000)) ( 
  PARTITION p1 VALUES LESS THAN (UNIX_TIMESTAMP('2022-01-01 00:00:00')*1000),
  PARTITION p2 VALUES LESS THAN (UNIX_TIMESTAMP('2022-01-10 00:00:00')*1000),
  PARTITION p3 VALUES LESS THAN (UNIX_TIMESTAMP('2022-01-20 00:00:00')*1000),
  PARTITION p4 VALUES LESS THAN (UNIX_TIMESTAMP('2022-01-31 00:00:00')*1000),
  PARTITION p5 VALUES LESS THAN (UNIX_TIMESTAMP('2022-02-01 00:00:00')*1000),
  PARTITION p6 VALUES LESS THAN (UNIX_TIMESTAMP('2022-02-10 00:00:00')*1000),
  PARTITION p7 VALUES LESS THAN (UNIX_TIMESTAMP('2022-02-20 00:00:00')*1000),
  PARTITION p8 VALUES LESS THAN (UNIX_TIMESTAMP('2022-02-28 00:00:00')*1000),
  PARTITION p9 VALUES LESS THAN (UNIX_TIMESTAMP('2022-03-01 00:00:00')*1000),
  PARTITION p10 VALUES LESS THAN (UNIX_TIMESTAMP('2022-03-10 00:00:00')*1000),
  PARTITION p11 VALUES LESS THAN (UNIX_TIMESTAMP('2022-03-20 00:00:00')*1000),
  PARTITION p12 VALUES LESS THAN (UNIX_TIMESTAMP('2022-03-31 00:00:00')*1000),
  PARTITION p13 VALUES LESS THAN (UNIX_TIMESTAMP('2022-04-01 00:00:00')*1000),
  PARTITION p14 VALUES LESS THAN (UNIX_TIMESTAMP('2022-04-10 00:00:00')*1000),
  PARTITION p15 VALUES LESS THAN (UNIX_TIMESTAMP('2022-04-20 00:00:00')*1000),
  PARTITION p16 VALUES LESS THAN (UNIX_TIMESTAMP('2022-04-30 00:00:00')*1000),
  PARTITION p17 VALUES LESS THAN (UNIX_TIMESTAMP('2022-05-01 00:00:00')*1000),
  PARTITION p18 VALUES LESS THAN (UNIX_TIMESTAMP('2022-05-10 00:00:00')*1000),
  PARTITION p19 VALUES LESS THAN (UNIX_TIMESTAMP('2022-05-20 00:00:00')*1000),
  PARTITION p20 VALUES LESS THAN (UNIX_TIMESTAMP('2022-05-31 00:00:00')*1000),
  PARTITION p21 VALUES LESS THAN (UNIX_TIMESTAMP('2022-06-01 00:00:00')*1000),
  PARTITION p22 VALUES LESS THAN (UNIX_TIMESTAMP('2022-06-10 00:00:00')*1000),
  PARTITION p23 VALUES LESS THAN (UNIX_TIMESTAMP('2022-06-20 00:00:00')*1000),
  PARTITION p24 VALUES LESS THAN (UNIX_TIMESTAMP('2022-06-30 00:00:00')*1000),
  PARTITION p25 VALUES LESS THAN (UNIX_TIMESTAMP('2022-07-01 00:00:00')*1000),
  PARTITION p26 VALUES LESS THAN (UNIX_TIMESTAMP('2022-07-10 00:00:00')*1000),
  PARTITION p27 VALUES LESS THAN (UNIX_TIMESTAMP('2022-07-20 00:00:00')*1000),
  PARTITION p28 VALUES LESS THAN (UNIX_TIMESTAMP('2022-07-31 00:00:00')*1000),
  PARTITION p29 VALUES LESS THAN (UNIX_TIMESTAMP('2022-08-01 00:00:00')*1000),
  PARTITION p30 VALUES LESS THAN (UNIX_TIMESTAMP('2022-08-10 00:00:00')*1000),
  PARTITION p31 VALUES LESS THAN (UNIX_TIMESTAMP('2022-08-20 00:00:00')*1000),
  PARTITION p32 VALUES LESS THAN (UNIX_TIMESTAMP('2022-08-31 00:00:00')*1000),
  PARTITION p33 VALUES LESS THAN (UNIX_TIMESTAMP('2022-09-01 00:00:00')*1000),
  PARTITION p34 VALUES LESS THAN (UNIX_TIMESTAMP('2022-09-10 00:00:00')*1000),
  PARTITION p35 VALUES LESS THAN (UNIX_TIMESTAMP('2022-09-20 00:00:00')*1000),
  PARTITION p36 VALUES LESS THAN (UNIX_TIMESTAMP('2022-09-30 00:00:00')*1000),
  PARTITION p37 VALUES LESS THAN (UNIX_TIMESTAMP('2022-10-01 00:00:00')*1000),
  PARTITION p38 VALUES LESS THAN (UNIX_TIMESTAMP('2022-10-10 00:00:00')*1000),
  PARTITION p39 VALUES LESS THAN (UNIX_TIMESTAMP('2022-10-20 00:00:00')*1000),
  PARTITION p40 VALUES LESS THAN (UNIX_TIMESTAMP('2022-10-31 00:00:00')*1000),
  PARTITION p41 VALUES LESS THAN (UNIX_TIMESTAMP('2022-11-01 00:00:00')*1000),
  PARTITION p42 VALUES LESS THAN (UNIX_TIMESTAMP('2022-11-10 00:00:00')*1000),
  PARTITION p43 VALUES LESS THAN (UNIX_TIMESTAMP('2022-11-20 00:00:00')*1000),
  PARTITION p44 VALUES LESS THAN (UNIX_TIMESTAMP('2022-11-30 00:00:00')*1000),
  PARTITION p45 VALUES LESS THAN (UNIX_TIMESTAMP('2022-12-01 00:00:00')*1000),
  PARTITION p46 VALUES LESS THAN (UNIX_TIMESTAMP('2022-12-10 00:00:00')*1000),
  PARTITION p47 VALUES LESS THAN (UNIX_TIMESTAMP('2022-12-20 00:00:00')*1000),
  PARTITION p48 VALUES LESS THAN (UNIX_TIMESTAMP('2022-12-31 00:00:00')*1000),
  PARTITION p49 VALUES LESS THAN (UNIX_TIMESTAMP('2023-01-01 00:00:00')*1000),
  PARTITION p50 VALUES LESS THAN (UNIX_TIMESTAMP('2023-01-10 00:00:00')*1000),
  PARTITION p51 VALUES LESS THAN (UNIX_TIMESTAMP('2023-01-20 00:00:00')*1000),
  PARTITION p52 VALUES LESS THAN (UNIX_TIMESTAMP('2023-01-31 00:00:00')*1000),
  PARTITION p53 VALUES LESS THAN (UNIX_TIMESTAMP('2023-02-01 00:00:00')*1000),
  PARTITION p54 VALUES LESS THAN (UNIX_TIMESTAMP('2023-02-10 00:00:00')*1000),
  PARTITION p55 VALUES LESS THAN (UNIX_TIMESTAMP('2023-02-20 00:00:00')*1000),
  PARTITION p56 VALUES LESS THAN (UNIX_TIMESTAMP('2023-02-28 00:00:00')*1000),
  PARTITION p57 VALUES LESS THAN (UNIX_TIMESTAMP('2023-03-01 00:00:00')*1000),
  PARTITION p58 VALUES LESS THAN (UNIX_TIMESTAMP('2023-03-10 00:00:00')*1000),
  PARTITION p59 VALUES LESS THAN (UNIX_TIMESTAMP('2023-03-20 00:00:00')*1000),
  PARTITION p60 VALUES LESS THAN (UNIX_TIMESTAMP('2023-03-31 00:00:00')*1000),
  PARTITION p61 VALUES LESS THAN (UNIX_TIMESTAMP('2023-04-01 00:00:00')*1000),
  PARTITION p62 VALUES LESS THAN (UNIX_TIMESTAMP('2023-04-10 00:00:00')*1000),
  PARTITION p63 VALUES LESS THAN (UNIX_TIMESTAMP('2023-04-20 00:00:00')*1000),
  PARTITION p64 VALUES LESS THAN (UNIX_TIMESTAMP('2023-04-30 00:00:00')*1000),
  PARTITION p65 VALUES LESS THAN (UNIX_TIMESTAMP('2023-05-01 00:00:00')*1000),
  PARTITION p66 VALUES LESS THAN (UNIX_TIMESTAMP('2023-05-10 00:00:00')*1000),
  PARTITION p67 VALUES LESS THAN (UNIX_TIMESTAMP('2023-05-20 00:00:00')*1000),
  PARTITION p68 VALUES LESS THAN (UNIX_TIMESTAMP('2023-05-31 00:00:00')*1000),
  PARTITION p69 VALUES LESS THAN (UNIX_TIMESTAMP('2023-06-01 00:00:00')*1000),
  PARTITION p70 VALUES LESS THAN (UNIX_TIMESTAMP('2023-06-10 00:00:00')*1000),
  PARTITION p71 VALUES LESS THAN (UNIX_TIMESTAMP('2023-06-20 00:00:00')*1000),
  PARTITION p72 VALUES LESS THAN (UNIX_TIMESTAMP('2023-06-30 00:00:00')*1000),
  PARTITION p73 VALUES LESS THAN (UNIX_TIMESTAMP('2023-07-01 00:00:00')*1000),
  PARTITION p74 VALUES LESS THAN (UNIX_TIMESTAMP('2023-07-10 00:00:00')*1000),
  PARTITION p75 VALUES LESS THAN (UNIX_TIMESTAMP('2023-07-20 00:00:00')*1000),
  PARTITION p76 VALUES LESS THAN (UNIX_TIMESTAMP('2023-07-31 00:00:00')*1000),
  PARTITION p77 VALUES LESS THAN (UNIX_TIMESTAMP('2023-08-01 00:00:00')*1000),
  PARTITION p78 VALUES LESS THAN (UNIX_TIMESTAMP('2023-08-10 00:00:00')*1000),
  PARTITION p79 VALUES LESS THAN (UNIX_TIMESTAMP('2023-08-20 00:00:00')*1000),
  PARTITION p80 VALUES LESS THAN (UNIX_TIMESTAMP('2023-08-31 00:00:00')*1000),
  PARTITION p81 VALUES LESS THAN (UNIX_TIMESTAMP('2023-09-01 00:00:00')*1000),
  PARTITION p82 VALUES LESS THAN (UNIX_TIMESTAMP('2023-09-10 00:00:00')*1000),
  PARTITION p83 VALUES LESS THAN (UNIX_TIMESTAMP('2023-09-20 00:00:00')*1000),
  PARTITION p84 VALUES LESS THAN (UNIX_TIMESTAMP('2023-09-30 00:00:00')*1000),
  PARTITION p85 VALUES LESS THAN (UNIX_TIMESTAMP('2023-10-01 00:00:00')*1000),
  PARTITION p86 VALUES LESS THAN (UNIX_TIMESTAMP('2023-10-10 00:00:00')*1000),
  PARTITION p87 VALUES LESS THAN (UNIX_TIMESTAMP('2023-10-20 00:00:00')*1000),
  PARTITION p88 VALUES LESS THAN (UNIX_TIMESTAMP('2023-10-31 00:00:00')*1000),
  PARTITION p90 VALUES LESS THAN (UNIX_TIMESTAMP('2023-11-01 00:00:00')*1000),
  PARTITION p91 VALUES LESS THAN (UNIX_TIMESTAMP('2023-11-10 00:00:00')*1000),
  PARTITION p92 VALUES LESS THAN (UNIX_TIMESTAMP('2023-11-20 00:00:00')*1000),
  PARTITION p93 VALUES LESS THAN (UNIX_TIMESTAMP('2023-11-30 00:00:00')*1000),
  PARTITION p94 VALUES LESS THAN (UNIX_TIMESTAMP('2023-12-01 00:00:00')*1000),
  PARTITION p95 VALUES LESS THAN (UNIX_TIMESTAMP('2023-12-10 00:00:00')*1000),
  PARTITION p96 VALUES LESS THAN (UNIX_TIMESTAMP('2023-12-20 00:00:00')*1000),
  PARTITION p97 VALUES LESS THAN (UNIX_TIMESTAMP('2023-12-31 00:00:00')*1000),
  PARTITION p1024 VALUES LESS THAN MAXVALUE
  );
14

你是tidb官方内部的开发人员吗

15
  1. 哪一个查不到？ 请你把完整的信息反馈在一个帖子里。
    2022-11-10 00:59:59.996 我测试用的是你另一个帖子的数据。如果不是这个，请给出一个测试数据，哪一个数据分区查看有问题，多谢。
  2. *1000 的是由于之前的版本看你们另一个帖子沟通的 workaround，所以本质是要解决第一个问题。
16

就这个数据，分区裁剪以后找不到了，落到相邻分区里面，条件放宽就可以找到了。这个分区建表是没有*1000

image
image1380×350 43.4 KB

17

在 6.5 测试可以查到这个数据。你可以用 tiup playground 或者搭一个单机环境验证下。

MySQL [test]> select start_time from history_wti_alarm where start_time>=‘2022-11-10 00:00:00.000’ and alarm_id=‘abc’;
±------------------------+
| start_time |
±------------------------+
| 2022-11-10 00:59:59.996 |
±------------------------+
1 row in set (0.00 sec)

MySQL [test]> select start_time from history_wti_alarm where start_time<=‘2022-11-10 00:00:00.000’ and alarm_id=‘abc’;
Empty set (0.00 sec)

MySQL [test]> select start_time from history_wti_alarm where start_time>=‘2022-11-09 00:00:00.000’ and alarm_id=‘abc’;
±------------------------+
| start_time |
±------------------------+
| 2022-11-10 00:59:59.996 |
±------------------------+
1 row in set (0.00 sec)

MySQL [test]> explain select start_time from history_wti_alarm where start_time>=‘2022-11-09 00:00:00.000’ and alarm_id=‘abc’;
±-------------------------±--------±----------±--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±-------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
±-------------------------±--------±----------±--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±-------------------------------------------------------------------+
| Projection_4 | 1.00 | root | | test.history_wti_alarm.start_time |
| └─IndexReader_8 | 1.00 | root | partition:p42,p43,p44,p45,p46,p47,p48,p49,p50,p51,p52,p53,p54,p55,p56,p57,p58,p59,p60,p61,p62,p63,p64,p65,p66,p67,p68,p69,p70,p71,p72,p73,p74,p75,p76,p77,p78,p79,p80,p81,p82,p83,p84,p85,p86,p87,p88,p90,p91,p92,p93,p94,p95,p96,p97,p1024 | index:IndexRangeScan_7 |
| └─IndexRangeScan_7 | 1.00 | cop[tikv] | table:history_wti_alarm, index:uniq_alarm_id(alarm_id, start_time) | range:[“abc” 2022-11-09 00:00:00.000,“abc” +inf], keep order:false |
±-------------------------±--------±----------±--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±-------------------------------------------------------------------+
3 rows in set (0.00 sec)

19

我在v6.1.2上面稳定复现

20

我新搭建了6.5.0结果和你的一样，没有落错分区的情况，将原来的集群升级成6.5.0之后仍然存在这个问题

21

image
image1850×520 18.9 KB