doris2.14 select查询结果异常

Viewed 97
2.1

我有一个查询sql如下（SQL1）

-- 有BUG
SELECT
    2023
    , AM.MAX_VERSION
    , AU.AA
FROM log.T_BASE_CONTRACT A
LEFT JOIN (
    SELECT
        CONTRACT_NO
        , MAX(CONTRACT_VERSION) MAX_VERSION
    FROM log.T_BASE_CONTRACT A
    WHERE  CONTRACT_NO = 'CRT202201437'
    GROUP BY CONTRACT_NO
) AM ON A.CONTRACT_NO = AM.CONTRACT_NO
 
LEFT JOIN (
    SELECT T.CONTRACT_NO , T.CONTRACT_VERSION AA
    FROM (
        SELECT
            A.CONTRACT_NO , A.CONTRACT_VERSION
            , RANK()OVER(PARTITION BY A.CONTRACT_NO ORDER BY CONTRACT_VERSION DESC ) AS RK
        FROM log.T_BASE_CONTRACT A
        INNER JOIN (
            SELECT CONTRACT_NO
            FROM log.T_BASE_CONTRACT A
            GROUP BY CONTRACT_NO
        ) X ON  A.CONTRACT_NO = X.CONTRACT_NO
        WHERE A.CONTRACT_STATUS = 589350006
             AND A.CONTRACT_NO = 'CRT202201437'
    ) T
    WHERE T.RK = 1
    
) AU ON A.CONTRACT_NO = AU.CONTRACT_NO
    WHERE A.CONTRACT_NO = 'CRT202201437'

查询结果1：

image.png

问题：字段AA实际上是有值的，但是却查不出来。 但是如果我注释掉, AM.MAX_VERSION（最外层少查询一个字段），则AA字段就有值

SQL2

-- 有BUG
SELECT
    2023
--     , AM.MAX_VERSION
    , AU.AA
FROM log.T_BASE_CONTRACT A
LEFT JOIN (
    SELECT
        CONTRACT_NO
        , MAX(CONTRACT_VERSION) MAX_VERSION
    FROM log.T_BASE_CONTRACT A
    WHERE  CONTRACT_NO = 'CRT202201437'
    GROUP BY CONTRACT_NO
) AM ON A.CONTRACT_NO = AM.CONTRACT_NO
 
LEFT JOIN (
    SELECT T.CONTRACT_NO , T.CONTRACT_VERSION AA
    FROM (
        SELECT
            A.CONTRACT_NO , A.CONTRACT_VERSION
            , RANK()OVER(PARTITION BY A.CONTRACT_NO ORDER BY CONTRACT_VERSION DESC ) AS RK
        FROM log.T_BASE_CONTRACT A
        INNER JOIN (
            SELECT CONTRACT_NO
            FROM log.T_BASE_CONTRACT A
            GROUP BY CONTRACT_NO
        ) X ON  A.CONTRACT_NO = X.CONTRACT_NO
        WHERE A.CONTRACT_STATUS = 589350006
             AND A.CONTRACT_NO = 'CRT202201437'
    ) T
    WHERE T.RK = 1
    
) AU ON A.CONTRACT_NO = AU.CONTRACT_NO
    WHERE A.CONTRACT_NO = 'CRT202201437'

查询结果2：
image.png

问题

综上，同一个sql，我少查询一个字段，结果影响了另一个字段的结果，两个left join子查询中实际都有数据，且通过关联条件都能连上的。

SQL1的执行计划如下：

PLAN FRAGMENT 0
  OUTPUT EXPRS:
    2023[#77]
    MAX_VERSION[#78]
    AA[#79]
  PARTITION: UNPARTITIONED

  HAS_COLO_PLAN_NODE: false

  VRESULT SINK
     MYSQL_PROTOCAL

  18:VEXCHANGE
     offset: 0
     distribute expr lists: 

PLAN FRAGMENT 1

  PARTITION: HASH_PARTITIONED: CONTRACT_NO[#59]

  HAS_COLO_PLAN_NODE: false

  STREAM DATA SINK
    EXCHANGE ID: 18
    UNPARTITIONED

  17:VHASH JOIN(1351)
  |  join op: LEFT OUTER JOIN(PARTITIONED)[]
  |  equal join conjunct: (CONTRACT_NO[#72] = CONTRACT_NO[#14])
  |  cardinality=1
  |  vec output tuple id: 18
  |  output tuple id: 18
  |  vIntermediate tuple ids: 17 
  |  hash output slot ids: 71 15 
  |  final projections: 2023, MAX_VERSION[#76], AA[#73]
  |  final project output tuple id: 18
  |  distribute expr lists: CONTRACT_NO[#72]
  |  distribute expr lists: CONTRACT_NO[#14]
  |  
  |----3:VAGGREGATE (merge finalize)(1348)
  |    |  output: max(partial_max(CONTRACT_VERSION)[#13])[#15]
  |    |  group by: CONTRACT_NO[#12]
  |    |  cardinality=1
  |    |  distribute expr lists: CONTRACT_NO[#12]
  |    |  
  |    2:VEXCHANGE
  |       offset: 0
  |       distribute expr lists: 
  |    
  16:VHASH JOIN(1329)
  |  join op: LEFT OUTER JOIN(PARTITIONED)[]
  |  equal join conjunct: (CONTRACT_NO[#59] = CONTRACT_NO[#54])
  |  cardinality=1
  |  vec output tuple id: 16
  |  output tuple id: 16
  |  vIntermediate tuple ids: 15 
  |  hash output slot ids: 55 59 
  |  final projections: AA[#70], CONTRACT_NO[#68]
  |  final project output tuple id: 16
  |  distribute expr lists: CONTRACT_NO[#59]
  |  distribute expr lists: CONTRACT_NO[#54]
  |  
  |----13:VANALYTIC(1320)
  |    |  functions: [rank()]
  |    |  partition by: CONTRACT_NO[#51]
  |    |  order by: CONTRACT_VERSION[#52] DESC NULLS LAST
  |    |  window: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  |    |  predicates: (RK[#53] = 1)
  |    |  final projections: CONTRACT_NO[#51], CONTRACT_VERSION[#52]
  |    |  final project output tuple id: 13
  |    |  distribute expr lists: CONTRACT_NO[#51]
  |    |  
  |    12:VSORT(1317)
  |    |  order by: CONTRACT_NO[#51] ASC, CONTRACT_VERSION[#52] DESC
  |    |  offset: 0
  |    |  distribute expr lists: CONTRACT_NO[#49]
  |    |  
  |    11:VPartitionTopN(1314)
  |    |  functions: rank
  |    |  partition by: CONTRACT_NO[#47]
  |    |  order by: CONTRACT_VERSION[#50] DESC
  |    |  has global limit: false
  |    |  partition limit: 1
  |    |  partition topn phase: TWO_PHASE_LOCAL_PTOPN
  |    |  distribute expr lists: CONTRACT_NO[#47]
  |    |  
  |    10:VHASH JOIN(1308)
  |    |  join op: INNER JOIN(PARTITIONED)[]
  |    |  equal join conjunct: (CONTRACT_NO[#43] = CONTRACT_NO[#28])
  |    |  runtime filters: RF000[bloom] <- CONTRACT_NO[#28](1/1/1048576)
  |    |  cardinality=1
  |    |  vec output tuple id: 9
  |    |  output tuple id: 9
  |    |  vIntermediate tuple ids: 8 
  |    |  hash output slot ids: 28 29 
  |    |  final projections: CONTRACT_NO[#45], CONTRACT_VERSION[#46]
  |    |  final project output tuple id: 9
  |    |  distribute expr lists: CONTRACT_NO[#43]
  |    |  distribute expr lists: CONTRACT_NO[#28]
  |    |  
  |    |----5:VEXCHANGE
  |    |       offset: 0
  |    |       distribute expr lists: 
  |    |    
  |    9:VAGGREGATE (merge finalize)(1295)
  |    |  group by: CONTRACT_NO[#42]
  |    |  cardinality=1
  |    |  distribute expr lists: CONTRACT_NO[#42]
  |    |  
  |    8:VEXCHANGE
  |       offset: 0
  |       distribute expr lists: 
  |    
  15:VEXCHANGE
     offset: 0
     distribute expr lists: 

PLAN FRAGMENT 2

  PARTITION: HASH_PARTITIONED: CONTRACT_ID[#56]

  HAS_COLO_PLAN_NODE: false

  STREAM DATA SINK
    EXCHANGE ID: 15
    HASH_PARTITIONED: CONTRACT_NO[#59]

  14:VOlapScanNode(1270)
     TABLE: log.t_base_contract(t_base_contract), PREAGGREGATION: ON
     PREDICATES: (CONTRACT_NO[#59] = 'CRT202201437')
     partitions=1/1 (t_base_contract)
     tablets=10/10, tabletList=5349682,5349684,5349686 ...
     cardinality=3786, avgRowSize=0.0, numNodes=1
     pushAggOp=NONE

PLAN FRAGMENT 3

  PARTITION: HASH_PARTITIONED: CONTRACT_ID[#30]

  HAS_COLO_PLAN_NODE: false

  STREAM DATA SINK
    EXCHANGE ID: 08
    HASH_PARTITIONED: CONTRACT_NO[#42]

  7:VAGGREGATE (update serialize)(1289)
  |  STREAMING
  |  group by: CONTRACT_NO[#33]
  |  cardinality=1
  |  distribute expr lists: 
  |  
  6:VOlapScanNode(1280)
     TABLE: log.t_base_contract(t_base_contract), PREAGGREGATION: ON
     PREDICATES: (CONTRACT_NO[#33] = 'CRT202201437')
     runtime filters: RF000[bloom] -> CONTRACT_NO[#33]
     partitions=1/1 (t_base_contract)
     tablets=10/10, tabletList=5349682,5349684,5349686 ...
     cardinality=3786, avgRowSize=0.0, numNodes=1
     pushAggOp=NONE

PLAN FRAGMENT 4

  PARTITION: HASH_PARTITIONED: CONTRACT_ID[#16]

  HAS_COLO_PLAN_NODE: false

  STREAM DATA SINK
    EXCHANGE ID: 05
    HASH_PARTITIONED: CONTRACT_NO[#28]

  4:VOlapScanNode(1296)
     TABLE: log.t_base_contract(t_base_contract), PREAGGREGATION: ON
     PREDICATES: (CONTRACT_STATUS[#25] = 589350006) AND (CONTRACT_NO[#19] = 'CRT202201437')
     partitions=1/1 (t_base_contract)
     tablets=10/10, tabletList=5349682,5349684,5349686 ...
     cardinality=3786, avgRowSize=0.0, numNodes=1
     pushAggOp=NONE
     final projections: CONTRACT_NO[#19], CONTRACT_VERSION[#22]
     final project output tuple id: 4

PLAN FRAGMENT 5

  PARTITION: HASH_PARTITIONED: CONTRACT_ID[#0]

  HAS_COLO_PLAN_NODE: false

  STREAM DATA SINK
    EXCHANGE ID: 02
    HASH_PARTITIONED: CONTRACT_NO[#12]

  1:VAGGREGATE (update serialize)(1342)
  |  STREAMING
  |  output: partial_max(CONTRACT_VERSION[#6])[#13]
  |  group by: CONTRACT_NO[#3]
  |  cardinality=1
  |  distribute expr lists: 
  |  
  0:VOlapScanNode(1333)
     TABLE: log.t_base_contract(t_base_contract), PREAGGREGATION: ON
     PREDICATES: (CONTRACT_NO[#3] = 'CRT202201437')

SQL2的执行计划如下：

PLAN FRAGMENT 0
  OUTPUT EXPRS:
    2023[#55]
    AA[#56]
  PARTITION: UNPARTITIONED

  HAS_COLO_PLAN_NODE: false

  VRESULT SINK
     MYSQL_PROTOCAL

  13:VEXCHANGE
     offset: 0
     distribute expr lists: 

PLAN FRAGMENT 1

  PARTITION: HASH_PARTITIONED: CONTRACT_ID[#40]

  HAS_COLO_PLAN_NODE: false

  STREAM DATA SINK
    EXCHANGE ID: 13
    UNPARTITIONED

  12:VHASH JOIN(995)
  |  join op: LEFT OUTER JOIN(BROADCAST)[]
  |  equal join conjunct: (CONTRACT_NO[#43] = CONTRACT_NO[#38])
  |  cardinality=1
  |  vec output tuple id: 13
  |  output tuple id: 13
  |  vIntermediate tuple ids: 12 
  |  hash output slot ids: 39 
  |  final projections: 2023, AA[#54]
  |  final project output tuple id: 13
  |  distribute expr lists: 
  |  distribute expr lists: 
  |  
  |----10:VEXCHANGE
  |       offset: 0
  |       distribute expr lists: CONTRACT_NO[#38]
  |    
  11:VOlapScanNode(936)
     TABLE: log.t_base_contract(t_base_contract), PREAGGREGATION: ON
     PREDICATES: (CONTRACT_NO[#43] = 'CRT202201437')
     partitions=1/1 (t_base_contract)
     tablets=10/10, tabletList=5349682,5349684,5349686 ...
     cardinality=3786, avgRowSize=0.0, numNodes=1
     pushAggOp=NONE

PLAN FRAGMENT 2

  PARTITION: HASH_PARTITIONED: CONTRACT_NO[#26]

  HAS_COLO_PLAN_NODE: false

  STREAM DATA SINK
    EXCHANGE ID: 10
    UNPARTITIONED

  9:VANALYTIC(983)
  |  functions: [rank()]
  |  partition by: CONTRACT_NO[#35]
  |  order by: CONTRACT_VERSION[#36] DESC NULLS LAST
  |  window: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  |  predicates: (RK[#37] = 1)
  |  final projections: CONTRACT_NO[#35], CONTRACT_VERSION[#36]
  |  final project output tuple id: 10
  |  distribute expr lists: CONTRACT_NO[#35]
  |  
  8:VSORT(980)
  |  order by: CONTRACT_NO[#35] ASC, CONTRACT_VERSION[#36] DESC
  |  offset: 0
  |  distribute expr lists: CONTRACT_NO[#33]
  |  
  7:VPartitionTopN(977)
  |  functions: rank
  |  partition by: CONTRACT_NO[#31]
  |  order by: CONTRACT_VERSION[#34] DESC
  |  has global limit: false
  |  partition limit: 1
  |  partition topn phase: TWO_PHASE_LOCAL_PTOPN
  |  distribute expr lists: CONTRACT_NO[#31]
  |  
  6:VHASH JOIN(971)
  |  join op: INNER JOIN(PARTITIONED)[]
  |  equal join conjunct: (CONTRACT_NO[#27] = CONTRACT_NO[#12])
  |  runtime filters: RF000[bloom] <- CONTRACT_NO[#12](1/1/1048576)
  |  cardinality=1
  |  vec output tuple id: 6
  |  output tuple id: 6
  |  vIntermediate tuple ids: 5 
  |  hash output slot ids: 12 13 
  |  final projections: CONTRACT_NO[#29], CONTRACT_VERSION[#30]
  |  final project output tuple id: 6
  |  distribute expr lists: CONTRACT_NO[#27]
  |  distribute expr lists: CONTRACT_NO[#12]
  |  
  |----1:VEXCHANGE
  |       offset: 0
  |       distribute expr lists: 
  |    
  5:VAGGREGATE (merge finalize)(958)
  |  group by: CONTRACT_NO[#26]
  |  cardinality=1
  |  distribute expr lists: CONTRACT_NO[#26]
  |  
  4:VEXCHANGE
     offset: 0
     distribute expr lists: 

PLAN FRAGMENT 3

  PARTITION: HASH_PARTITIONED: CONTRACT_ID[#14]

  HAS_COLO_PLAN_NODE: false

  STREAM DATA SINK
    EXCHANGE ID: 04
    HASH_PARTITIONED: CONTRACT_NO[#26]

  3:VAGGREGATE (update serialize)(952)
  |  STREAMING
  |  group by: CONTRACT_NO[#17]
  |  cardinality=1
  |  distribute expr lists: 
  |  
  2:VOlapScanNode(943)
     TABLE: log.t_base_contract(t_base_contract), PREAGGREGATION: ON
     PREDICATES: (CONTRACT_NO[#17] = 'CRT202201437')
     runtime filters: RF000[bloom] -> CONTRACT_NO[#17]
     partitions=1/1 (t_base_contract)
     tablets=10/10, tabletList=5349682,5349684,5349686 ...
     cardinality=3786, avgRowSize=0.0, numNodes=1
     pushAggOp=NONE

PLAN FRAGMENT 4

  PARTITION: HASH_PARTITIONED: CONTRACT_ID[#0]

  HAS_COLO_PLAN_NODE: false

  STREAM DATA SINK
    EXCHANGE ID: 01
    HASH_PARTITIONED: CONTRACT_NO[#12]

  0:VOlapScanNode(959)
     TABLE: log.t_base_contract(t_base_contract), PREAGGREGATION: ON
     PREDICATES: (CONTRACT_STATUS[#9] = 589350006) AND (CONTRACT_NO[#3] = 'CRT202201437')
     partitions=1/1 (t_base_contract)
     tablets=10/10, tabletList=5349682,5349684,5349686 ...
     cardinality=3786, avgRowSize=0.0, numNodes=1
     pushAggOp=NONE
     final projections: CONTRACT_NO[#3], CONTRACT_VERSION[#6]
     final project output tuple id: 1
七彩黑虫7
2 Answers

1、建议升级至当前相对稳定的版本>= 2.1.6
2、查询sql中的基础表查询结果是否正常

先close，后续有问题可以继续追帖 或 ➕我W：yz-jayhua

张彬华7238

升级了2.1.5以后，还是有类似的错误
首先，sql改成如下：

-- 有BUG
SELECT
    2023
    , AU.AA
FROM (
	select CONTRACT_NO from log.T_BASE_CONTRACT
	WHERE CONTRACT_NO = 'CRT202201437'
-- 	limit 1 
) A

LEFT JOIN (
    SELECT T.CONTRACT_NO , T.CONTRACT_VERSION AA
    FROM (
        SELECT
            A.CONTRACT_NO , A.CONTRACT_VERSION
            , RANK()OVER(PARTITION BY A.CONTRACT_NO ORDER BY CONTRACT_VERSION DESC ) AS RK
        FROM log.T_BASE_CONTRACT A
        INNER JOIN (
            SELECT CONTRACT_NO
            FROM log.T_BASE_CONTRACT A
            GROUP BY CONTRACT_NO
        ) X ON  A.CONTRACT_NO = X.CONTRACT_NO
        WHERE A.CONTRACT_STATUS = 589350006
             AND A.CONTRACT_NO = 'CRT202201437'
    ) T
    WHERE T.RK = 1
    
) AU ON A.CONTRACT_NO = AU.CONTRACT_NO

其中两个子查询A和AU都有值，且能关联上
image.png
image.png

一旦运行整个sql，结果就出现了问题:AA的值就没取出来：

image.png

我把sql中的limit 1放开后，竟然结果就对了
image.png

七彩黑虫7