2.1.3 insertinto select 的时候插入的结果与单独执行select的结果不一致。并且每次执行的时候结果不稳定

Viewed 92

版本是2.1.3

建表语句是这样的

drop table if exists dim.dim_pub_product_suit_pack;
CREATE TABLE if not exists dim.dim_pub_product_suit_pack (
  `key_id`          bigint not null auto_increment comment '自增主键',
  `product_id`      VARCHAR(36) NULL COMMENT ,
  `suit_id`         VARCHAR(36) NULL COMMENT ,
  `package_id`      VARCHAR(36) NULL COMMENT ,
  `product_code`    VARCHAR(40) NULL COMMENT ,
  `product_name`    VARCHAR(512) NULL COMMENT ,
  `product_state`   VARCHAR(10) NULL COMMENT ,
  `suit_code`       VARCHAR(40) NULL COMMENT ,
  `suit_name`       VARCHAR(512) NULL COMMENT ,
  `suit_num`        DECIMAL(18, 2) NULL COMMENT,
  `pack_code`       VARCHAR(40) NULL COMMENT ,
  `pack_name`       VARCHAR(512) NULL COMMENT ,
  `emall_flag`      char(1) NULL COMMENT ,
  `pack_num`       decimal(18,2) NULL COMMENT ,
  `purchase_price` decimal(18,2) NULL COMMENT ,
  `supplier_id`    VARCHAR(40) NULL COMMENT ,
  `supplier_code`  VARCHAR(40) NULL COMMENT ,
  `supplier_name`  VARCHAR(512) NULL COMMENT ,
  `data_version`   bigint(20) null comment '数据版本',
  `delete_flag`    CHAR(1) NOT NULL DEFAULT "N" COMMENT '是否删除',
  `date_created`   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `created_by`     INT NOT NULL DEFAULT "0" COMMENT '创建人',
  `date_updated`   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  `updated_by`     INT NOT NULL DEFAULT "0" COMMENT '修改人'
) ENGINE=OLAP
UNIQUE KEY(`key_id`)
COMMENT '电商-产品包套件关系表'
DISTRIBUTED BY HASH(`key_id`) BUCKETS 3
PROPERTIES (
"replication_allocation" = "tag.location.default: 1"
);

插入脚本

insert into dim.dim_pub_product_suit_pack
(
      product_id    
      ,suit_id       
      ,package_id    
      ,product_code  
      ,product_name  
      ,product_state 
      ,suit_code     
      ,suit_name     
      ,suit_num      
      ,pack_code     
      ,pack_name     
      ,emall_flag    
      ,pack_num        
)

with base_product_info as (
   SELECT 
   t1.id as product_id,
   t1.is_product_suite,
   t1.is_product_pack,
   t1.is_sale_product,
   t1.code,
   t1.description,
   t1.pro_state,
   t1.is_elec_businiess
   FROM 
   ods.ods_ec_base_product_info t1
   left join (
      select 
         tt1.product_code
      from 
      ods.ods_ec_conf_goods_product tt1 
      where dr=0
      group by tt1.product_code
   ) as t2 on t1.code=t2.product_code
   where t1.status=1
   and t1.dr=0
   and (t1.is_elec_businiess=1 OR t2.product_code is not null) 
)

SELECT 
		v.product_id     as product_id, 
      v.suit_id        as suit_id,
      v.pack_id        as pack_id,
	   v.product_code   as product_code,
	   v.product_name   as product_name,
	   v.product_state  as product_state,
	   v.suit_code      as suit_code,
	   v.suit_name      as suit_name,
	   v.suit_num       as suit_num,
	   v.pack_code      as pack_code,
	   v.pack_name      as pack_name,
	   if(is_elec_businiess=1,'Y','N') as emall_flag,
	   v.pack_num       as pack_num
  FROM (
		select 
         b.product_id          product_id,
         b.code        product_code,
         b.description product_name,
         b.pro_state   product_state,
         d.product_id suit_id,
         d.code suit_code,
         d.description suit_name,
         c.child_num suit_num,
         g.product_id pack_id,
         g.code pack_code,
         g.description pack_name,
         g.is_elec_businiess,
         f.child_num * c.child_num pack_num
         from ods.ods_ec_base_product_bom a
         left join base_product_info b on b.product_id = a.product_info_id
         left join ods.ods_mp_base_product_bom_sub c on c.product_bom_id = a.id 
         left join base_product_info d on d.product_id = c.product_info_id
         left join ods.ods_ec_base_product_bom e on e.product_info_id = c.product_info_id
         left join ods.ods_mp_base_product_bom_sub f on f.product_bom_id = e.id and e.id is not null 
         left join base_product_info g on g.product_id = f.product_info_id
         where a.bom_type = '1'
           and a.is_valid = '1'
           and e.bom_type = '2'
           and e.is_valid = '1'
           and b.is_product_suite = '0'
           and b.is_product_pack = '0'
           and b.is_sale_product = '1'
           and d.is_product_pack = '0'
           and c.dr = '0'
           and f.dr = '0'
           and a.dr = '0'
) v
;

执行结果不稳定,查询的表的数据没有变化 都是在凌晨的时候更新的
image.png
image.png
image.png

单独执行select的查询结果,比较稳定。直接导出明细也是跟查询数量对得上。insert的时候会有问题
image.png
image.png

修改了一下表 改成了明细模型

drop table if exists dim.dim_pub_product_suit_pack;
CREATE TABLE if not exists dim.dim_pub_product_suit_pack (
  `product_id`      VARCHAR(36) NULL  ,
  `suit_id`         VARCHAR(36) NULL  ,
  `package_id`      VARCHAR(36) NULL  ,
  `product_code`    VARCHAR(40) NULL  ,
  `product_name`    VARCHAR(512) NULL  ,
  `product_state`   VARCHAR(10) NULL  ,
  `suit_code`       VARCHAR(40) NULL  ,
  `suit_name`       VARCHAR(512) NULL  ,
  `suit_num`        DECIMAL(18, 2) NULL ,
  `pack_code`       VARCHAR(40) NULL  ,
  `pack_name`       VARCHAR(512) NULL  ,
  `emall_flag`      char(1) NULL  ,
  `pack_num`       decimal(18,2) NULL  ,
  `purchase_price` decimal(18,2) NULL  ,
  `supplier_id`    VARCHAR(40) NULL  ,
  `supplier_code`  VARCHAR(40) NULL  ,
  `supplier_name`  VARCHAR(512) NULL  ,
  `data_version`   bigint(20) null comment '数据版本'
)
COMMENT '电商-产品包套件关系表'
DISTRIBUTED BY HASH(`product_id`) BUCKETS 3
PROPERTIES (
"replication_allocation" = "tag.location.default: 1"
);

insert into dim.dim_pub_product_suit_pack
(
      product_id    
      ,suit_id       
      ,package_id    
      ,product_code  
      ,product_name  
      ,product_state 
      ,suit_code     
      ,suit_name     
      ,suit_num      
      ,pack_code     
      ,pack_name     
      ,emall_flag    
      ,pack_num        
)

with base_product_info as (
   SELECT 
   t1.id as product_id,
   t1.is_product_suite,
   t1.is_product_pack,
   t1.is_sale_product,
   t1.code,
   t1.description,
   t1.pro_state,
   t1.is_elec_businiess
   FROM 
   ods.ods_ec_base_product_info t1
   left join (
      select 
         tt1.product_code
      from 
      ods.ods_ec_conf_goods_product tt1 
      where dr=0
      group by tt1.product_code
   ) as t2 on t1.code=t2.product_code
   where t1.status=1
   and t1.dr=0
   and (t1.is_elec_businiess=1 OR t2.product_code is not null) 
)

SELECT 
		v.product_id     as product_id, 
      v.suit_id        as suit_id,
      v.pack_id        as pack_id,
	   v.product_code   as product_code,
	   v.product_name   as product_name,
	   v.product_state  as product_state,
	   v.suit_code      as suit_code,
	   v.suit_name      as suit_name,
	   v.suit_num       as suit_num,
	   v.pack_code      as pack_code,
	   v.pack_name      as pack_name,
	   if(is_elec_businiess=1,'Y','N') as emall_flag,
	   v.pack_num       as pack_num
  FROM (
		select 
         b.product_id          product_id,
         b.code        product_code,
         b.description product_name,
         b.pro_state   product_state,
         d.product_id suit_id,
         d.code suit_code,
         d.description suit_name,
         c.child_num suit_num,
         g.product_id pack_id,
         g.code pack_code,
         g.description pack_name,
         g.is_elec_businiess,
         f.child_num * c.child_num pack_num
         from ods.ods_ec_base_product_bom a
         left join base_product_info b on b.product_id = a.product_info_id
         left join ods.ods_mp_base_product_bom_sub c on c.product_bom_id = a.id 
         left join base_product_info d on d.product_id = c.product_info_id
         left join ods.ods_ec_base_product_bom e on e.product_info_id = c.product_info_id
         left join ods.ods_mp_base_product_bom_sub f on f.product_bom_id = e.id and e.id is not null 
         left join base_product_info g on g.product_id = f.product_info_id
         where a.bom_type = '1'
           and a.is_valid = '1'
           and e.bom_type = '2'
           and e.is_valid = '1'
           and b.is_product_suite = '0'
           and b.is_product_pack = '0'
           and b.is_sale_product = '1'
           and d.is_product_pack = '0'
           and c.dr = '0'
           and f.dr = '0'
           and a.dr = '0'
) v
;

执行插入的结果依旧不稳定
image.png
image.png
image.png

直接运行select 查询总数
image.png

更奇怪的是 插入的数据有null值
image.png

但是实际执行查询的时候 这个null值字段是有值的

image.png

1 Answers

Unique MOR 表,INSERT INTO SELECT 的方式导入数据,查询结果不一致,基本上都是没有用seq列导致的。

解决方式:新建一张表,找一个能区分相同key,不同版本的字段,用这个字段设置一个 Seq列 ,然后insert into select 的方式拉一下数据。