【 TiDB 使用环境】生产环境
【 TiDB 版本】v6.5.1
表结构
CREATE TABLE `inventory_record` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`site_id` bigint(20) DEFAULT NULL COMMENT '分拣中心ID',
`provider_id` bigint(20) DEFAULT NULL COMMENT '供应商ID',
`project_id` varchar(36) DEFAULT NULL COMMENT '项目id',
`business_no` varchar(255) DEFAULT NULL COMMENT '出入库单号',
`business_id` bigint(20) DEFAULT NULL COMMENT '业务ID，如果是人库则是recycle_record的id，如果是出库则是sell_record表的ID，如果是调拨出库或调拨入库则是allocation_item表的ID',
`inventory_type` tinyint(4) DEFAULT NULL COMMENT '作业类型 1：入库 2：出库 ',
`trade_time` datetime(3) DEFAULT NULL COMMENT '交易时间',
`trade_type` tinyint(4) DEFAULT NULL COMMENT '交易类型 1：原材料入库 2：半成品入库 3：产成品入库',
`product_code` varchar(50) DEFAULT NULL COMMENT '品类ID',
`product_name` varchar(255) DEFAULT NULL COMMENT '品类名称',
`product_type` int(4) DEFAULT NULL COMMENT '品类类型',
`weight_before` decimal(12,2) DEFAULT NULL COMMENT '变更前重量',
`weight` decimal(12,3) DEFAULT NULL COMMENT '净重,单位公斤',
`weight_after` decimal(12,2) DEFAULT NULL COMMENT '变更后重量',
`price` decimal(10,3) DEFAULT NULL COMMENT '单价(单位元)',
`amount_before` decimal(12,2) DEFAULT NULL COMMENT '变更前金额',
`amount` decimal(12,2) DEFAULT NULL COMMENT '金额',
`amount_after` decimal(12,2) DEFAULT NULL COMMENT '变更后金额',
`inventory_id` bigint(20) DEFAULT NULL COMMENT '库存ID',
`deleted` tinyint(4) DEFAULT '0' COMMENT '是否删除 0：正常 1：删除',
`create_time` datetime(3) DEFAULT CURRENT_TIMESTAMP(3),
`modify_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`creator` varchar(255) DEFAULT NULL COMMENT '创建人',
`statistics_year_month` int(11) NOT NULL COMMENT '统计年月',
`statistics_year_month_day` int(11) NOT NULL COMMENT '统计年月日',
`provider_id_index` bigint(20) DEFAULT NULL COMMENT '供应商ID统计',
`inventory_channel` tinyint(4) DEFAULT NULL COMMENT '库存类型 1:混合 2:自营 3:外购',
PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
UNIQUE KEY `uniq_business_id_trade_type` (`business_id`,`trade_type`),
KEY `idx_create_time` (`create_time`),
KEY `idx_modify_time` (`modify_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=630001 COMMENT='库存明细表';
执行sql
SELECT
site_id,
inventory_channel,
product_code,
product_type,
statistics_year_month_day,
SUBSTRING_INDEX( GROUP_CONCAT( weight_before ORDER BY create_time ASC ), ',', 1 ) AS weight_before,
sum( CASE WHEN inventory_type = 1 THEN weight ELSE 0 END ) AS inWeight,
sum( CASE WHEN inventory_type = 2 THEN weight ELSE 0 END ) AS outWeight,
sum( CASE WHEN inventory_type = 3 THEN weight ELSE 0 END ) AS inventoryCountingWeight,
SUBSTRING_INDEX( GROUP_CONCAT( weight_after ORDER BY create_time DESC ), ',', 1 ) AS weight_after,
SUBSTRING_INDEX( GROUP_CONCAT( amount_before ORDER BY create_time ASC ), ',', 1 ) AS amount_before,
sum( CASE WHEN inventory_type = 1 THEN amount ELSE 0 END ) AS inAmount,
sum( CASE WHEN inventory_type = 2 THEN amount ELSE 0 END ) AS outAmount,
sum( CASE WHEN inventory_type = 3 THEN amount ELSE 0 END ) AS inventoryCountingAmount,
SUBSTRING_INDEX( GROUP_CONCAT( amount_after ORDER BY create_time DESC ), ',', 1 ) AS amount_after
FROM
inventory_record
WHERE
statistics_year_month_day >= '20230701'
AND statistics_year_month_day <= '20230731'
GROUP BY
site_id,
inventory_channel,
product_code,
product_type,
statistics_year_month_day
ORDER BY
statistics_year_month_day DESC,
site_id DESC,
product_type ASC
LIMIT 20
在使用tidb的时候是可以查询出来的,但是执行
ALTER TABLE inventory_record SET TIFLASH REPLICA 2;
后再进行查询报错
1105 - other error for mpp stream: From MPP<query:442844490967547945,task:3>: Code: 49, e.displayText() = DB::Exception: Check schema[i].type->equals(*storage_schema[i].type) failed, schema[i].name = table_scan_10, schema[i].type->getName() = Nullable(MyDateTime(3)), storage_schema[i].name = create_time, storage_schema[i].type->getName() = Nullable(MyDateTime(0)), e.what() = DB::Exception,
试了下
执行select * from inventory_record limit 1 都报错
