Starrocks 聚合模型查询结果有重复的 key

为了更快的定位您的问题,请提供以下信息,谢谢
【详述】Starrocks 聚合模型查询结果有重复的 key
【背景】VEH_FACT_REF数据表是聚合模型,van 是AGGREGATE KEY,使用条件 left(van,5)查询的数据结果有重复,但是使用 left(van, 6)查询的结果就是唯一的。用like 也是同样的结果。VEH_FACT_REF的写入是多次 insert 的结果
【业务影响】影响数据喝茶
【是否存算分离】
【StarRocks版本】例如:3.1.5
【集群规模】4fe+18be(fe与be混部)
【机器信息】
【联系方式】请社区联系或者邮箱 392348681@qq.com
【附件】
VEH_FACT_REF表的建表语句:
VEH_FACT_REF.sql (11.0 KB)
查询以及截图:

  • left(van, 5)的情况

select * from DW.VEH_FACT_REF where left(van,5)=‘00701’ order by van desc;

explain select * from DW.VEH_FACT_REF where left(van,5)=‘00701’ order by van desc;

PLAN FRAGMENT 0
OUTPUT EXPRS:1: VAN | 2: FDP | 3: MMSTA | 4: SDCTR | 5: VBELN_F | 6: CPVEH | 7: ZSD_CMC | 8: KUNNR_FS | 9: ERDAT_F | 10: AUART_F | 11: ZAPRDATE | 12: SVA | 13: VIN | 14: ZPRVIN | 15: ZSD_DCR01 | 16: GDFF | 17: BSTKD | 18: DESCR | 19: CNFNO | 20: VBELN_PP | 21: ERDAT_PP | 22: KUNNR_PR | 23: EBOMC | 24: PBOMC | 25: BSTZD | 26: CMFRE | 27: ZAPRDATE_PP | 28: AUFNR | 29: RSNUM | 30: PPSOC | 31: SVACODE | 32: DAUAT | 33: PPREL | 34: GSTRI | 35: GETRI | 36: PPSEQ | 37: GSTRI_C | 38: PPINB | 39: SDINB | 40: AUFNR_R | 41: RSNUM_R | 42: DAUAT_R | 43: GSTRI_R | 44: GETRI_R | 45: RECNT | 46: VBELN_C | 47: ERDAT | 48: ZPRDT_L | 49: VKBUR | 50: KUNNR_L | 51: ZCCM | 52: ZTEXTCCM | 53: LIFNR | 54: ZDATAPRCCM | 55: ZDATAFINPRCCM | 56: ZRELPUR | 57: CCMPS | 58: EBELN | 59: RECIP | 60: ZACTSTDATCCM | 61: ZACTEDDATCCM | 62: CMSSD | 63: CMSED | 64: CMESD | 65: CMEED | 66: ZBBSTATUS | 67: ZPLANNO | 68: INVNO | 69: INVDT | 70: INVAM | 71: WAERK | 72: KUNRG | 73: WTYSD | 74: WTYED | 75: WTYCT | 76: WTYWH | 77: WTYMT | 78: WTYBT | 79: SPMSD | 80: SPMCD | 81: SPMCS | 82: SPMKD | 83: DELIV | 84: OFFLI | 85: QUALI | 86: STORA | 87: AUGRU_F | 88: VKBUR_F | 89: VBELN_L | 90: AUART_L | 91: AUGRU_L | 92: VKBUR_L | 93: ERDAT_L | 94: VBELN_S | 95: AUART_S | 96: AUGRU_S | 97: VKBUR_S | 98: ERDAT_S | 99: KUNNR_LS | 100: KUNNR_SS | 101: LIFSK_F | 102: LIFSK_S | 103: LIFSK_L | 104: BSTKD_L | 105: SVAAN | 106: VBDAT_L | 107: CMFRE_L | 108: PAYDT | 109: PAYTY | 110: PAYAM | 111: PAYST | 112: D_DATE | 113: ALCDT | 114: X_DATE | 115: CCMCM | 116: SPMPA | 117: SPMSC | 118: SPMPS | 119: SPMSV | 120: SPMDV | 121: SPMPU | 122: SPMSA | 123: SPMDA | 124: CCMPR | 125: VEHPS | 126: CKSDT | 127: CKSDP | 128: CKSAR | 129: SCPOS | 130: PPSER | 131: VKBUR_I | 132: KUNNR_I | 133: VBELN_I | 134: D_DATE_I | 135: NUMB_I | 136: MBTR_CHASIS | 137: MBTR_BOX | 138: FMBTR | 139: AJTS | 140: INRDT | 141: INRNO | 142: INRAC | 143: INRVN | 144: INRVK | 145: INRKU | 146: INRAM | 147: INVAT | 148: INVBT | 149: BINBD | 150: BINDD | 151: Z03DT | 152: Z04DT | 153: SDMOD | 154: BRAND | 155: VHCAN | 156: DEP_L | 157: COCPD | 158: ZZVP | 159: ORDVP | 160: ORDTY | 161: LOADL | 162: RCMVE | 163: EMSLV | 164: VKBUR_B | 165: KUNNR_B | 166: COMMS | 167: COMCT | 168: REGMS | 169: REGCT | 170: REG_B | 171: VIRTU | 172: CLASS | 173: PRTYP | 174: ORDSU | 175: PPRED | 176: ETAXD | 177: ETAXA | 178: MODAR | 179: MODDT | 180: VSTU_L | 181: BUDAT | 182: ORDSS | 183: IVUZD | 184: CREPE | 185: CUSNA | 186: MWONM | 187: RSOAM | 188: RSONM | 189: AUD_TIME | 190: AUD_TYPE | 191: AUD_USER
PARTITION: UNPARTITIONED

RESULT SINK

2:MERGING-EXCHANGE

PLAN FRAGMENT 1
OUTPUT EXPRS:
PARTITION: RANDOM

STREAM DATA SINK
EXCHANGE ID: 02
UNPARTITIONED

1:SORT
| order by: <slot 1> 1: VAN DESC
| offset: 0
|
0:OlapScanNode
TABLE: VEH_FACT_REF
PREAGGREGATION: OFF. Reason: None aggregate function
PREDICATES: left(1: VAN, 5) = ‘00701’
partitions=1/1
rollup: VEH_FACT_REF
tabletRatio=27/27
tabletList=5176554,5176558,5176562,5176566,5176570,5176574,5176578,5176582,5176586,5176590 …
cardinality=497408
avgRowSize=199.99992
numNodes=0

  • left(van,6)的情况

select * from DW.VEH_FACT_REF where left(van,6)=‘007019’ order by van desc ;

explain select * from DW.VEH_FACT_REF where left(van,6)=‘007019’ order by van desc ;
PLAN FRAGMENT 0
OUTPUT EXPRS:1: VAN | 2: FDP | 3: MMSTA | 4: SDCTR | 5: VBELN_F | 6: CPVEH | 7: ZSD_CMC | 8: KUNNR_FS | 9: ERDAT_F | 10: AUART_F | 11: ZAPRDATE | 12: SVA | 13: VIN | 14: ZPRVIN | 15: ZSD_DCR01 | 16: GDFF | 17: BSTKD | 18: DESCR | 19: CNFNO | 20: VBELN_PP | 21: ERDAT_PP | 22: KUNNR_PR | 23: EBOMC | 24: PBOMC | 25: BSTZD | 26: CMFRE | 27: ZAPRDATE_PP | 28: AUFNR | 29: RSNUM | 30: PPSOC | 31: SVACODE | 32: DAUAT | 33: PPREL | 34: GSTRI | 35: GETRI | 36: PPSEQ | 37: GSTRI_C | 38: PPINB | 39: SDINB | 40: AUFNR_R | 41: RSNUM_R | 42: DAUAT_R | 43: GSTRI_R | 44: GETRI_R | 45: RECNT | 46: VBELN_C | 47: ERDAT | 48: ZPRDT_L | 49: VKBUR | 50: KUNNR_L | 51: ZCCM | 52: ZTEXTCCM | 53: LIFNR | 54: ZDATAPRCCM | 55: ZDATAFINPRCCM | 56: ZRELPUR | 57: CCMPS | 58: EBELN | 59: RECIP | 60: ZACTSTDATCCM | 61: ZACTEDDATCCM | 62: CMSSD | 63: CMSED | 64: CMESD | 65: CMEED | 66: ZBBSTATUS | 67: ZPLANNO | 68: INVNO | 69: INVDT | 70: INVAM | 71: WAERK | 72: KUNRG | 73: WTYSD | 74: WTYED | 75: WTYCT | 76: WTYWH | 77: WTYMT | 78: WTYBT | 79: SPMSD | 80: SPMCD | 81: SPMCS | 82: SPMKD | 83: DELIV | 84: OFFLI | 85: QUALI | 86: STORA | 87: AUGRU_F | 88: VKBUR_F | 89: VBELN_L | 90: AUART_L | 91: AUGRU_L | 92: VKBUR_L | 93: ERDAT_L | 94: VBELN_S | 95: AUART_S | 96: AUGRU_S | 97: VKBUR_S | 98: ERDAT_S | 99: KUNNR_LS | 100: KUNNR_SS | 101: LIFSK_F | 102: LIFSK_S | 103: LIFSK_L | 104: BSTKD_L | 105: SVAAN | 106: VBDAT_L | 107: CMFRE_L | 108: PAYDT | 109: PAYTY | 110: PAYAM | 111: PAYST | 112: D_DATE | 113: ALCDT | 114: X_DATE | 115: CCMCM | 116: SPMPA | 117: SPMSC | 118: SPMPS | 119: SPMSV | 120: SPMDV | 121: SPMPU | 122: SPMSA | 123: SPMDA | 124: CCMPR | 125: VEHPS | 126: CKSDT | 127: CKSDP | 128: CKSAR | 129: SCPOS | 130: PPSER | 131: VKBUR_I | 132: KUNNR_I | 133: VBELN_I | 134: D_DATE_I | 135: NUMB_I | 136: MBTR_CHASIS | 137: MBTR_BOX | 138: FMBTR | 139: AJTS | 140: INRDT | 141: INRNO | 142: INRAC | 143: INRVN | 144: INRVK | 145: INRKU | 146: INRAM | 147: INVAT | 148: INVBT | 149: BINBD | 150: BINDD | 151: Z03DT | 152: Z04DT | 153: SDMOD | 154: BRAND | 155: VHCAN | 156: DEP_L | 157: COCPD | 158: ZZVP | 159: ORDVP | 160: ORDTY | 161: LOADL | 162: RCMVE | 163: EMSLV | 164: VKBUR_B | 165: KUNNR_B | 166: COMMS | 167: COMCT | 168: REGMS | 169: REGCT | 170: REG_B | 171: VIRTU | 172: CLASS | 173: PRTYP | 174: ORDSU | 175: PPRED | 176: ETAXD | 177: ETAXA | 178: MODAR | 179: MODDT | 180: VSTU_L | 181: BUDAT | 182: ORDSS | 183: IVUZD | 184: CREPE | 185: CUSNA | 186: MWONM | 187: RSOAM | 188: RSONM | 189: AUD_TIME | 190: AUD_TYPE | 191: AUD_USER
PARTITION: UNPARTITIONED

RESULT SINK

2:MERGING-EXCHANGE

PLAN FRAGMENT 1
OUTPUT EXPRS:
PARTITION: RANDOM

STREAM DATA SINK
EXCHANGE ID: 02
UNPARTITIONED

1:SORT
| order by: <slot 1> 1: VAN DESC
| offset: 0
|
0:OlapScanNode
TABLE: VEH_FACT_REF
PREAGGREGATION: OFF. Reason: None aggregate function
PREDICATES: left(1: VAN, 6) = ‘007019’
partitions=1/1
rollup: VEH_FACT_REF
tabletRatio=27/27
tabletList=5176554,5176558,5176562,5176566,5176570,5176574,5176578,5176582,5176586,5176590 …
cardinality=497408
avgRowSize=199.99992
numNodes=0

select current_version() 确认一下是 3.1.5版本么,另外是存算一体还是存算分离

set streaming_preaggregation_mode=force_streaming;
然后再执行 select * from DW.VEH_FACT_REF where left(van,5)=‘00701’ order by van desc; 看看结果

mysql> set streaming_preaggregation_mode=force_streaming;
set streaming_preaggregation_mode=force_streaming;
Query OK, 0 rows affected (0.00 sec)

mysql> select van, FDP, MMSTA from DW.VEH_FACT_REF where left(van,5)=‘00701’ order by van desc limit 10;
±-----------±------±------+
| van | FDP | MMSTA |
±-----------±------±------+
| 0070199999 | -CF6T | ZM50 |
| 0070199999 | NULL | NULL |
| 0070199998 | NULL | NULL |
| 0070199998 | -CH64 | ZM50 |
| 0070199997 | -CH64 | ZM50 |
| 0070199996 | -CEW8 | ZM50 |
| 0070199996 | NULL | NULL |
| 0070199995 | -CEW8 | ZM50 |
| 0070199995 | NULL | NULL |
| 0070199993 | NULL | NULL |
±-----------±------±------+
10 rows in set (0.16 sec)

mysql> select current_version();
±------------------+
| current_version() |
±------------------+
| 3.1.5-5d8438a |
±------------------+
1 row in set (0.02 sec)

存算一体