查询结果不稳定 #6484

qzsee · 2021-08-22T10:18:23Z

qzsee
Aug 22, 2021

一、查询SQL

with hpr as (
        select region_id
          from (
                select region_id,
                       count(distinct bu_id) as all_bu,
                       count(distinct if(hp=1,bu_id,null)) as hp_bu
                  from (
                        select region_id,
                               bu_id,
                               1 as hp 
                          from A.b
                    where region_name not like '%KA%'
                       ) tt
                 group by 1
               ) t3
         where t3.all_bu=t3.hp_bu)
         
select t1.date_scale,
               t1.time_tag,
               t2.region_id,
               t2.region_name,
               t1.cat_id,
               sum(t1.arranged_amt_3p)
          from A.c t1
           left join (
                select bu_id,
                       region_id,
                       max(region_name) as region_name
                  from A.b
                 group by 1,
                          2
               ) t2
            on t1.customer_bu_id=t2.bu_id
          join hpr t3
          on t2.region_id=t3.region_id
         where t1.date_scale='202107'
           and t1.time_tag=2
           and t1.customer_bu_id<>-1
           and t1.cat_id=-1
           group by t1.date_scale,
               t1.time_tag,
               t2.region_id,
               t2.region_name,
               t1.cat_id

二、查询结果

+------------+----------+-----------+--------------+--------+-----------------------------+
| date_scale | time_tag | region_id | region_name | cat_id | sum(t1.arranged_amt_3p) |
+------------+----------+-----------+--------------+--------+-----------------------------+
| 202107 | 2 | 22304 | A | -1 | 106839459.9 |
| 202107 | 2 | 22303 | B | -1 | 131133774.81 |
| 202107 | 2 | 22306 | C | -1 | 66322643.32 |
| 202107 | 2 | 22307 | D | -1 | 77013996.36 |
| 202107 | 2 | 22302 | E | -1 | 56652868.22 |
+------------+----------+-----------+--------------+--------+-----------------------------+

每次的查询结果中，sum列的值都会变。

profile

Query:
  Summary:
     - Query ID: a5c68792068a454d-8119d8839ac19b6d
     - Start Time: 2021-08-17 12:38:05
     - End Time: 2021-08-17 12:38:06
     - Total: 34ms
     - Query Type: Query
     - Query State: EOF
     - Doris Version: 0.13.11
     - User: root
     - Default Db: xx:xx
     - Sql Statement: with hpr as (
        select region_id
          from (
                select region_id,
                       count(distinct bu_id) as all_bu,
                       count(distinct if(hp=1,bu_id,null)) hp_bu
                  from (
                        select region_id,
                               bu_id,
                               1 as hp 
                          from A.b
                    where region_name not like '%KA%'
                       ) tt
                 group by 1
               ) t3
         where t3.all_bu=t3.hp_bu)
         
select t1.date_scale,
               t1.time_tag,
               t2.region_id,
               t2.region_name,
               t1.cat_id,
               sum(t1.arranged_amt_3p)
          from A.c t1
          left join (
                select bu_id,
                       region_id,
                       max(region_name) as region_name
                  from A.b
                 group by 1,
                          2
               ) t2
            on t1.customer_bu_id=t2.bu_id
          join hpr t3
          on t2.region_id=t3.region_id
         where t1.date_scale='202107'
           and t1.time_tag=2
           and t1.customer_bu_id<>-1
           and t1.cat_id=-1
           group by t1.date_scale,
               t1.time_tag,
               t2.region_id,
               t2.region_name,
               t1.cat_id
     - Is Cached: No
  Execution Profile a5c68792068a454d-8119d8839ac19b6d:(Active: 33.573ms, % non-child: 100.00%)
    Fragment 0:
      Instance a5c68792068a454d-8119d8839ac19b6e (host=TNetworkAddress(hostname:x, port:x)):(Active: 13.855ms, % non-child: 0.00%)
         - AverageThreadTokens: 0.00
         - FragmentCpuTime: 966.233us
         - MemoryLimit: 2.00 GB
         - PeakMemoryUsage: 34.46 MB
         - PeakReservation: 34.00 MB
         - PeakUsedReservation: 0.00 
         - RowsProduced: 5
        BlockMgr:
           - BlockWritesOutstanding: 0
           - BlocksCreated: 0
           - BlocksRecycled: 0
           - BufferedPins: 0
           - BytesWritten: 0.00 
           - MaxBlockSize: 8.00 MB
           - TotalBufferWaitTime: 0ns
           - TotalEncryptionTime: 0ns
           - TotalIntegrityCheckTime: 0ns
           - TotalReadBlockTime: 0ns
        DataBufferSender (dst_fragment_instance_id=a5c68792068a454d-8119d8839ac19b6e):
           - AppendBatchTime: 38.620us
             - ResultRendTime: 25.779us
             - TupleConvertTime: 10.789us
           - NumSentRows: 5
        AGGREGATION_NODE (id=7):(Active: 14.139ms, % non-child: 3.09%)
           - Probe Method: HashTable Linear Probing
           - BuildTime: 59.521us
           - GetResultsTime: 4.307us
           - HTResize: 0
           - HTResizeTime: 839ns
           - HashBuckets: 5.12K (5120)
           - HashCollisions: 0
           - HashFailedProbe: 0
           - HashFilledBuckets: 5
           - HashProbe: 90
           - HashTravelLength: 0
           - LargestPartitionPercent: 1
           - MaxPartitionLevel: 0
           - NumRepartitions: 0
           - PartitionsCreated: 16
           - PeakMemoryUsage: 34.28 MB
           - RowsProcessed: 90
           - RowsRepartitioned: 0
           - RowsReturned: 5
           - RowsReturnedRate: 353
           - SpilledPartitions: 0
          HASH_JOIN_NODE (id=6):(Active: 13.100ms, % non-child: 0.24%)
             - ExecOption: Hash Table Built Asynchronously
             - BuildBuckets: 1.024K (1024)
             - BuildRows: 5
             - BuildTime: 3.951us
             - LoadFactor: 4572279521687896100.00
             - PeakMemoryUsage: 260.00 KB
             - ProbeRows: 94
             - ProbeTime: 10.226us
             - PushDownComputeTime: 4.401us
             - PushDownTime: 3.415us
             - RowsReturned: 90
             - RowsReturnedRate: 6.869K /sec
            EXCHANGE_NODE (id=9):(Active: 7.343ms, % non-child: 21.87%)
               - BytesReceived: 175.00 B
               - ConvertRowBatchTime: 3.139us
               - DataArrivalWaitTime: 7.282ms
               - DeserializeRowBatchTimer: 21.454us
               - FirstBatchArrivalWaitTime: 6.939ms
               - PeakMemoryUsage: 4.02 KB
               - RowsReturned: 5
               - RowsReturnedRate: 680
               - SendersBlockedTotalTimer(*): 0ns
            HASH_JOIN_NODE (id=3):(Active: 5.675ms, % non-child: 0.17%)
               - ExecOption: Hash Table Built Asynchronously
               - BuildBuckets: 1.024K (1024)
               - BuildRows: 90
               - BuildTime: 6.252us
               - LoadFactor: 4590997607639154700.00
               - PeakMemoryUsage: 296.00 KB
               - ProbeRows: 94
               - ProbeTime: 12.606us
               - PushDownComputeTime: 0ns
               - PushDownTime: 0ns
               - RowsReturned: 94
               - RowsReturnedRate: 16.563K /sec
              EXCHANGE_NODE (id=8):(Active: 4.391ms, % non-child: 13.08%)
                 - BytesReceived: 2.60 KB
                 - ConvertRowBatchTime: 20.755us
                 - DataArrivalWaitTime: 4.256ms
                 - DeserializeRowBatchTimer: 77.311us
                 - FirstBatchArrivalWaitTime: 2.941ms
                 - PeakMemoryUsage: 4.16 KB
                 - RowsReturned: 90
                 - RowsReturnedRate: 20.494K /sec
                 - SendersBlockedTotalTimer(*): 0ns
              OLAP_SCAN_NODE (id=0):(Active: 1.227ms, % non-child: 3.66%)
                 - BlockLookupCacheTime: 13.626us
                 - BlockPutCacheTime: 0ns
                 - BytesRead: 17.96 KB
                 - GetNextTime: 1.226ms
                 - MaxWaitScanTime: 104.742us
                 - NumDiskAccess: 1
                 - NumScanners: 1
                 - PeakMemoryUsage: 0.00 
                 - RowsRead: 95
                 - RowsReturned: 94
                 - RowsReturnedRate: 76.574K /sec
                 - RowsetNum: 2
                 - RowsetReaderInitTime: 799.633us
                 - ScanCpuTime: 39.826us
                 - ScannerBlockPutTimer: 4.74us
                 - ScannerMaxPendingTimer: 36.2us
                 - SegmentNum: 1
                 - StartScanTime: 98.378us
                 - TabletCount : 1
                 - TotalReadThroughput: 0.0 /sec
                OlapScanner:
                   - BlockConvertTime: 13.594us
                   - BlockFetchTime: 658.724us
                   - ReaderInitTime: 891.181us
                   - RowsDelFiltered: 0
                   - RowsPushedCondFiltered: 0
                   - ScanTime: 40.139us
                   - ShowHintsTime_V1: 0ns
                  SegmentIterator:
                     - BitmapIndexFilterTimer: 575ns
                     - BlockLoadTime: 641.638us
                     - BlockSeekCount: 118
                     - BlockSeekTime: 488.368us
                     - BlocksLoad: 1
                     - CachedPagesNum: 10
                     - CompressedBytesRead: 0.00 
                     - DecompressorTimer: 0ns
                     - IOTimer: 0ns
                     - IndexLoadTime_V1: 0ns
                     - NumSegmentFiltered: 0
                     - NumSegmentTotal: 1
                     - RawRowsRead: 380
                     - RowsBitmapIndexFiltered: 0
                     - RowsBloomFilterFiltered: 0
                     - RowsConditionsFiltered: 0
                     - RowsKeyRangeFiltered: 5.373K (5373)
                     - RowsStatsFiltered: 0
                     - RowsVectorPredFiltered: 285
                     - TotalPagesNum: 10
                     - UncompressedBytesRead: 0.00 
                     - VectorPredEvalTime: 4.847us
          Buffer pool:
             - AllocTime: 10.853us
             - CumulativeAllocationBytes: 12.00 MB
             - CumulativeAllocations: 6
             - PeakReservation: 34.00 MB
             - PeakUnpinnedBytes: 0.00 
             - PeakUsedReservation: 12.00 MB
             - ReservationLimit: 80.00 GB
    Fragment 1:
      Instance a5c68792068a454d-8119d8839ac19b70 (host=TNetworkAddress(hostname:x, port:x)):(Active: 2.551ms, % non-child: 0.00%)
         - AverageThreadTokens: 0.00
         - FragmentCpuTime: 1.541ms
         - MemoryLimit: 2.00 GB
         - PeakMemoryUsage: 36.10 MB
         - PeakReservation: 36.00 MB
         - PeakUsedReservation: 0.00 
         - RowsProduced: 5
        BlockMgr:
           - BlockWritesOutstanding: 0
           - BlocksCreated: 0
           - BlocksRecycled: 0
           - BufferedPins: 0
           - BytesWritten: 0.00 
           - MaxBlockSize: 8.00 MB
           - TotalBufferWaitTime: 0ns
           - TotalEncryptionTime: 0ns
           - TotalIntegrityCheckTime: 0ns
           - TotalReadBlockTime: 0ns
        DataStreamSender (dst_id=9, dst_fragments=[a5c68792068a454d-8119d8839ac19b6e]):(Active: 267.83us, % non-child: 0.80%)
           - BytesSent: 175.00 B
           - IgnoreRows: 0
           - OverallThroughput: 639.8701171875 KB/sec
           - PeakMemoryUsage: 4.00 KB
           - SerializeBatchTime: 8.915us
           - UncompressedRowBatchSize: 200.00 B
        AGGREGATION_NODE (id=5):(Active: 2.350ms, % non-child: 4.24%)
           - Probe Method: HashTable Linear Probing
           - BuildTime: 127.62us
           - GetResultsTime: 45.955us
           - HTResize: 0
           - HTResizeTime: 2.53us
           - HashBuckets: 5.12K (5120)
           - HashCollisions: 0
           - HashFailedProbe: 0
           - HashFilledBuckets: 5
           - HashProbe: 106
           - HashTravelLength: 0
           - LargestPartitionPercent: 0
           - MaxPartitionLevel: 0
           - NumRepartitions: 0
           - PartitionsCreated: 16
           - PeakMemoryUsage: 36.09 MB
           - RowsProcessed: 106
           - RowsRepartitioned: 0
           - RowsReturned: 5
           - RowsReturnedRate: 2.126K /sec
           - SpilledPartitions: 0
          OLAP_SCAN_NODE (id=4):(Active: 927.462us, % non-child: 2.76%)
             - BlockLookupCacheTime: 29.201us
             - BlockPutCacheTime: 0ns
             - BytesRead: 16.69 KB
             - GetNextTime: 877.187us
             - MaxWaitScanTime: 125.161us
             - NumDiskAccess: 1
             - NumScanners: 1
             - PeakMemoryUsage: 0.00 
             - RowsRead: 111
             - RowsReturned: 106
             - RowsReturnedRate: 114.29K /sec
             - RowsetNum: 2
             - RowsetReaderInitTime: 496.477us
             - ScanCpuTime: 44.392us
             - ScannerBlockPutTimer: 5.401us
             - ScannerMaxPendingTimer: 30.9us
             - SegmentNum: 1
             - StartScanTime: 110.477us
             - TabletCount : 1
             - TotalReadThroughput: 0.0 /sec
            OlapScanner:
               - BlockConvertTime: 17.770us
               - BlockFetchTime: 356.404us
               - ReaderInitTime: 536.59us
               - RowsDelFiltered: 0
               - RowsPushedCondFiltered: 0
               - ScanTime: 44.572us
               - ShowHintsTime_V1: 0ns
              SegmentIterator:
                 - BitmapIndexFilterTimer: 323ns
                 - BlockLoadTime: 334.951us
                 - BlockSeekCount: 7
                 - BlockSeekTime: 237.424us
                 - BlocksLoad: 1
                 - CachedPagesNum: 19
                 - CompressedBytesRead: 0.00 
                 - DecompressorTimer: 0ns
                 - IOTimer: 0ns
                 - IndexLoadTime_V1: 0ns
                 - NumSegmentFiltered: 0
                 - NumSegmentTotal: 1
                 - RawRowsRead: 111
                 - RowsBitmapIndexFiltered: 0
                 - RowsBloomFilterFiltered: 0
                 - RowsConditionsFiltered: 0
                 - RowsKeyRangeFiltered: 0
                 - RowsStatsFiltered: 0
                 - RowsVectorPredFiltered: 0
                 - TotalPagesNum: 19
                 - UncompressedBytesRead: 0.00 
                 - VectorPredEvalTime: 0ns
          Buffer pool:
             - AllocTime: 18.548us
             - CumulativeAllocationBytes: 12.00 MB
             - CumulativeAllocations: 6
             - PeakReservation: 36.00 MB
             - PeakUnpinnedBytes: 0.00 
             - PeakUsedReservation: 12.00 MB
             - ReservationLimit: 80.00 GB
    Fragment 2:
      Instance a5c68792068a454d-8119d8839ac19b6f (host=TNetworkAddress(hostname:x, port:x)):(Active: 4.132ms, % non-child: 0.00%)
         - AverageThreadTokens: 0.00
         - FragmentCpuTime: 1.664ms
         - MemoryLimit: 2.00 GB
         - PeakMemoryUsage: 36.17 MB
         - PeakReservation: 36.00 MB
         - PeakUsedReservation: 0.00 
         - RowsProduced: 96
        BlockMgr:
           - BlockWritesOutstanding: 0
           - BlocksCreated: 0
           - BlocksRecycled: 0
           - BufferedPins: 0
           - BytesWritten: 0.00 
           - MaxBlockSize: 8.00 MB
           - TotalBufferWaitTime: 0ns
           - TotalEncryptionTime: 0ns
           - TotalIntegrityCheckTime: 0ns
           - TotalReadBlockTime: 0ns
        DataStreamSender (dst_id=8, dst_fragments=[a5c68792068a454d-8119d8839ac19b6e]):(Active: 1.167ms, % non-child: 3.48%)
           - BytesSent: 2.60 KB
           - IgnoreRows: 0
           - OverallThroughput: 2.1729326248168945 MB/sec
           - PeakMemoryUsage: 3.20 KB
           - SerializeBatchTime: 41.800us
           - UncompressedRowBatchSize: 5.29 KB
        AGGREGATION_NODE (id=2):(Active: 2.983ms, % non-child: 3.95%)
           - Probe Method: HashTable Linear Probing
           - BuildTime: 171.693us
           - GetResultsTime: 59.979us
           - HTResize: 0
           - HTResizeTime: 2.312us
           - HashBuckets: 16.384K (16384)
           - HashCollisions: 0
           - HashFailedProbe: 0
           - HashFilledBuckets: 96
           - HashProbe: 96
           - HashTravelLength: 0
           - LargestPartitionPercent: 16
           - MaxPartitionLevel: 0
           - NumRepartitions: 0
           - PartitionsCreated: 16
           - PeakMemoryUsage: 36.16 MB
           - RowsProcessed: 96
           - RowsRepartitioned: 0
           - RowsReturned: 96
           - RowsReturnedRate: 32.177K /sec
           - SpilledPartitions: 0
          OLAP_SCAN_NODE (id=1):(Active: 1.658ms, % non-child: 4.94%)
             - BlockLookupCacheTime: 6.598us
             - BlockPutCacheTime: 0ns
             - BytesRead: 3.56 KB
             - GetNextTime: 1.658ms
             - MaxWaitScanTime: 1.222ms
             - NumDiskAccess: 1
             - NumScanners: 1
             - PeakMemoryUsage: 0.00 
             - RowsRead: 96
             - RowsReturned: 96
             - RowsReturnedRate: 57.884K /sec
             - RowsetNum: 2
             - RowsetReaderInitTime: 222.778us
             - ScanCpuTime: 17.800us
             - ScannerBlockPutTimer: 5.386us
             - ScannerMaxPendingTimer: 1.157ms
             - SegmentNum: 1
             - StartScanTime: 93.784us
             - TabletCount : 1
             - TotalReadThroughput: 0.0 /sec
            OlapScanner:
               - BlockConvertTime: 4.96us
               - BlockFetchTime: 127.50us
               - ReaderInitTime: 265.144us
               - RowsDelFiltered: 0
               - RowsPushedCondFiltered: 0
               - ScanTime: 18.200us
               - ShowHintsTime_V1: 0ns
              SegmentIterator:
                 - BitmapIndexFilterTimer: 214ns
                 - BlockLoadTime: 119.527us
                 - BlockSeekCount: 7
                 - BlockSeekTime: 60.580us
                 - BlocksLoad: 1
                 - CachedPagesNum: 4
                 - CompressedBytesRead: 0.00 
                 - DecompressorTimer: 0ns
                 - IOTimer: 0ns
                 - IndexLoadTime_V1: 0ns
                 - NumSegmentFiltered: 0
                 - NumSegmentTotal: 1
                 - RawRowsRead: 111
                 - RowsBitmapIndexFiltered: 0
                 - RowsBloomFilterFiltered: 0
                 - RowsConditionsFiltered: 0
                 - RowsKeyRangeFiltered: 0
                 - RowsStatsFiltered: 0
                 - RowsVectorPredFiltered: 0
                 - TotalPagesNum: 4
                 - UncompressedBytesRead: 0.00 
                 - VectorPredEvalTime: 0ns
          Buffer pool:
             - AllocTime: 35.89us
             - CumulativeAllocationBytes: 34.00 MB
             - CumulativeAllocations: 17
             - PeakReservation: 36.00 MB
             - PeakUnpinnedBytes: 0.00 
             - PeakUsedReservation: 34.00 MB
             - ReservationLimit: 80.00 GB

三、强制shuffle

with hpr as (
        select region_id
          from (
                select region_id,
                       count(distinct bu_id) as all_bu,
                       count(distinct if(hp=1,bu_id,null)) as hp_bu
                  from (
                        select region_id,
                               bu_id,
                               1 as hp 
                          from A.b
                    where region_name not like '%KA%'
                       ) tt
                 group by 1
               ) t3
         where t3.all_bu=t3.hp_bu)
         
select t1.date_scale,
               t1.time_tag,
               t2.region_id,
               t2.region_name,
               t1.cat_id,
               sum(t1.arranged_amt_3p)
          from A.c t1
           left join (
                select bu_id,
                       region_id,
                       max(region_name) as region_name
                  from A.b
                 group by 1,
                          2
               ) t2
            on t1.customer_bu_id=t2.bu_id
          join [shuffle] hpr t3  -- 强制shuffle
          on t2.region_id=t3.region_id
         where t1.date_scale='202107'
           and t1.time_tag=2
           and t1.customer_bu_id<>-1
           and t1.cat_id=-1
           group by t1.date_scale,
               t1.time_tag,
               t2.region_id,
               t2.region_name,
               t1.cat_id

我们对 hpr 的表变量强制shuffle join 结果就稳定了。

profile

Query:
  Summary:
     - Query ID: 21ae6d249284eb0-82f18110a8be71de
     - Start Time: 2021-08-17 12:41:20
     - End Time: 2021-08-17 12:41:20
     - Total: 33ms
     - Query Type: Query
     - Query State: EOF
     - Doris Version: 0.13.11
     - User: root
     - Default Db: xx:xx
     - Sql Statement: with hpr as (
        select region_id
          from (
                select region_id,
                       count(distinct bu_id) as all_bu,
                       count(distinct if(hp=1,bu_id,null)) hp_bu
                  from (
                        select region_id,
                               bu_id,
                               1 as hp 
                          from A.b
                    where region_name not like '%KA%'
                       ) tt
                 group by 1
               ) t3
         where t3.all_bu=t3.hp_bu)
         
select t1.date_scale,
               t1.time_tag,
               t2.region_id,
               t2.region_name,
               t1.cat_id,
               sum(t1.arranged_amt_3p)
          from A.c t1
          left join (
                select bu_id,
                       region_id,
                       max(region_name) as region_name
                  from A.b
                 group by 1,
                          2
               ) t2
            on t1.customer_bu_id=t2.bu_id
          join [shuffle] hpr t3
          on t2.region_id=t3.region_id
         where t1.date_scale='202107'
           and t1.time_tag=2
           and t1.customer_bu_id<>-1
           and t1.cat_id=-1
           group by t1.date_scale,
               t1.time_tag,
               t2.region_id,
               t2.region_name,
               t1.cat_id
     - Is Cached: No
  Execution Profile 21ae6d249284eb0-82f18110a8be71de:(Active: 32.667ms, % non-child: 100.00%)
    Fragment 0:
      Instance 21ae6d249284eb0-82f18110a8be71e2 (host=TNetworkAddress(hostname:x, port:x)):(Active: 15.198ms, % non-child: 0.00%)
         - AverageThreadTokens: 0.00
         - FragmentCpuTime: 911.310us
         - MemoryLimit: 2.00 GB
         - PeakMemoryUsage: 34.18 MB
         - PeakReservation: 34.00 MB
         - PeakUsedReservation: 0.00 
         - RowsProduced: 5
        BlockMgr:
           - BlockWritesOutstanding: 0
           - BlocksCreated: 0
           - BlocksRecycled: 0
           - BufferedPins: 0
           - BytesWritten: 0.00 
           - MaxBlockSize: 8.00 MB
           - TotalBufferWaitTime: 0ns
           - TotalEncryptionTime: 0ns
           - TotalIntegrityCheckTime: 0ns
           - TotalReadBlockTime: 0ns
        DataBufferSender (dst_fragment_instance_id=21ae6d249284eb0-82f18110a8be71e2):
           - AppendBatchTime: 39.736us
             - ResultRendTime: 26.843us
             - TupleConvertTime: 10.837us
           - NumSentRows: 5
        AGGREGATION_NODE (id=7):(Active: 15.348ms, % non-child: 3.10%)
           - Probe Method: HashTable Linear Probing
           - BuildTime: 45.565us
           - GetResultsTime: 4.192us
           - HTResize: 0
           - HTResizeTime: 980ns
           - HashBuckets: 5.12K (5120)
           - HashCollisions: 0
           - HashFailedProbe: 0
           - HashFilledBuckets: 5
           - HashProbe: 91
           - HashTravelLength: 0
           - LargestPartitionPercent: 1
           - MaxPartitionLevel: 0
           - NumRepartitions: 0
           - PartitionsCreated: 16
           - PeakMemoryUsage: 34.11 MB
           - RowsProcessed: 91
           - RowsRepartitioned: 0
           - RowsReturned: 5
           - RowsReturnedRate: 325
           - SpilledPartitions: 0
          HASH_JOIN_NODE (id=6):(Active: 14.335ms, % non-child: 0.00%)
             - ExecOption: Hash Table Built Asynchronously
             - BuildBuckets: 1.024K (1024)
             - BuildRows: 5
             - BuildTime: 4.320us
             - LoadFactor: 4572279521687896100.00
             - PeakMemoryUsage: 84.00 KB
             - ProbeRows: 94
             - ProbeTime: 10.312us
             - PushDownComputeTime: 0ns
             - PushDownTime: 0ns
             - RowsReturned: 91
             - RowsReturnedRate: 6.347K /sec
            EXCHANGE_NODE (id=10):(Active: 8.50ms, % non-child: 24.64%)
               - BytesReceived: 175.00 B
               - ConvertRowBatchTime: 3.417us
               - DataArrivalWaitTime: 8.15ms
               - DeserializeRowBatchTimer: 29.337us
               - FirstBatchArrivalWaitTime: 7.452ms
               - PeakMemoryUsage: 4.02 KB
               - RowsReturned: 5
               - RowsReturnedRate: 621
               - SendersBlockedTotalTimer(*): 0ns
            EXCHANGE_NODE (id=9):(Active: 14.302ms, % non-child: 43.78%)
               - BytesReceived: 4.00 KB
               - ConvertRowBatchTime: 2.606us
               - DataArrivalWaitTime: 14.294ms
               - DeserializeRowBatchTimer: 14.978us
               - FirstBatchArrivalWaitTime: 14.256ms
               - PeakMemoryUsage: 17.47 KB
               - RowsReturned: 94
               - RowsReturnedRate: 6.572K /sec
               - SendersBlockedTotalTimer(*): 0ns
          Buffer pool:
             - AllocTime: 7.496us
             - CumulativeAllocationBytes: 12.00 MB
             - CumulativeAllocations: 6
             - PeakReservation: 34.00 MB
             - PeakUnpinnedBytes: 0.00 
             - PeakUsedReservation: 12.00 MB
             - ReservationLimit: 80.00 GB
    Fragment 1:
      Instance 21ae6d249284eb0-82f18110a8be71e1 (host=TNetworkAddress(hostname:x, port:x)):(Active: 3.93ms, % non-child: 0.00%)
         - AverageThreadTokens: 0.00
         - FragmentCpuTime: 1.517ms
         - MemoryLimit: 2.00 GB
         - PeakMemoryUsage: 36.10 MB
         - PeakReservation: 36.00 MB
         - PeakUsedReservation: 0.00 
         - RowsProduced: 5
        BlockMgr:
           - BlockWritesOutstanding: 0
           - BlocksCreated: 0
           - BlocksRecycled: 0
           - BufferedPins: 0
           - BytesWritten: 0.00 
           - MaxBlockSize: 8.00 MB
           - TotalBufferWaitTime: 0ns
           - TotalEncryptionTime: 0ns
           - TotalIntegrityCheckTime: 0ns
           - TotalReadBlockTime: 0ns
        DataStreamSender (dst_id=10, dst_fragments=[021ae6d249284eb0-82f18110a8be71e2]):(Active: 503.809us, % non-child: 1.54%)
           - BytesSent: 175.00 B
           - IgnoreRows: 0
           - OverallThroughput: 339.2119140625 KB/sec
           - PeakMemoryUsage: 4.00 KB
           - SerializeBatchTime: 11.693us
           - UncompressedRowBatchSize: 200.00 B
        AGGREGATION_NODE (id=5):(Active: 2.609ms, % non-child: 4.09%)
           - Probe Method: HashTable Linear Probing
           - BuildTime: 124.869us
           - GetResultsTime: 25.393us
           - HTResize: 0
           - HTResizeTime: 2.595us
           - HashBuckets: 5.12K (5120)
           - HashCollisions: 0
           - HashFailedProbe: 0
           - HashFilledBuckets: 5
           - HashProbe: 106
           - HashTravelLength: 0
           - LargestPartitionPercent: 0
           - MaxPartitionLevel: 0
           - NumRepartitions: 0
           - PartitionsCreated: 16
           - PeakMemoryUsage: 36.09 MB
           - RowsProcessed: 106
           - RowsRepartitioned: 0
           - RowsReturned: 5
           - RowsReturnedRate: 1.915K /sec
           - SpilledPartitions: 0
          OLAP_SCAN_NODE (id=4):(Active: 1.273ms, % non-child: 3.90%)
             - BlockLookupCacheTime: 19.967us
             - BlockPutCacheTime: 0ns
             - BytesRead: 16.69 KB
             - GetNextTime: 1.237ms
             - MaxWaitScanTime: 133.203us
             - NumDiskAccess: 1
             - NumScanners: 1
             - PeakMemoryUsage: 0.00 
             - RowsRead: 111
             - RowsReturned: 106
             - RowsReturnedRate: 83.253K /sec
             - RowsetNum: 2
             - RowsetReaderInitTime: 511.152us
             - ScanCpuTime: 43.662us
             - ScannerBlockPutTimer: 2.672us
             - ScannerMaxPendingTimer: 16.203us
             - SegmentNum: 1
             - StartScanTime: 140.619us
             - TabletCount : 1
             - TotalReadThroughput: 0.0 /sec
            OlapScanner:
               - BlockConvertTime: 19.728us
               - BlockFetchTime: 339.846us
               - ReaderInitTime: 544.846us
               - RowsDelFiltered: 0
               - RowsPushedCondFiltered: 0
               - ScanTime: 43.799us
               - ShowHintsTime_V1: 0ns
              SegmentIterator:
                 - BitmapIndexFilterTimer: 220ns
                 - BlockLoadTime: 315.982us
                 - BlockSeekCount: 7
                 - BlockSeekTime: 206.823us
                 - BlocksLoad: 1
                 - CachedPagesNum: 19
                 - CompressedBytesRead: 0.00 
                 - DecompressorTimer: 0ns
                 - IOTimer: 0ns
                 - IndexLoadTime_V1: 0ns
                 - NumSegmentFiltered: 0
                 - NumSegmentTotal: 1
                 - RawRowsRead: 111
                 - RowsBitmapIndexFiltered: 0
                 - RowsBloomFilterFiltered: 0
                 - RowsConditionsFiltered: 0
                 - RowsKeyRangeFiltered: 0
                 - RowsStatsFiltered: 0
                 - RowsVectorPredFiltered: 0
                 - TotalPagesNum: 19
                 - UncompressedBytesRead: 0.00 
                 - VectorPredEvalTime: 0ns
          Buffer pool:
             - AllocTime: 17.313us
             - CumulativeAllocationBytes: 12.00 MB
             - CumulativeAllocations: 6
             - PeakReservation: 36.00 MB
             - PeakUnpinnedBytes: 0.00 
             - PeakUsedReservation: 12.00 MB
             - ReservationLimit: 80.00 GB
    Fragment 2:
      Instance 21ae6d249284eb0-82f18110a8be71df (host=TNetworkAddress(hostname:x, port:x)):(Active: 6.564ms, % non-child: 0.29%)
         - AverageThreadTokens: 0.00
         - FragmentCpuTime: 380.688us
         - MemoryLimit: 2.00 GB
         - PeakMemoryUsage: 314.45 KB
         - PeakReservation: 0.00 
         - PeakUsedReservation: 0.00 
         - RowsProduced: 94
        BlockMgr:
           - BlockWritesOutstanding: 0
           - BlocksCreated: 0
           - BlocksRecycled: 0
           - BufferedPins: 0
           - BytesWritten: 0.00 
           - MaxBlockSize: 8.00 MB
           - TotalBufferWaitTime: 0ns
           - TotalEncryptionTime: 0ns
           - TotalIntegrityCheckTime: 0ns
           - TotalReadBlockTime: 0ns
        DataStreamSender (dst_id=9, dst_fragments=[021ae6d249284eb0-82f18110a8be71e2]):(Active: 152.197us, % non-child: 0.47%)
           - BytesSent: 4.00 KB
           - IgnoreRows: 0
           - OverallThroughput: 25.690813064575195 MB/sec
           - PeakMemoryUsage: 2.45 KB
           - SerializeBatchTime: 96.654us
           - UncompressedRowBatchSize: 11.93 KB
        HASH_JOIN_NODE (id=3):(Active: 6.316ms, % non-child: 0.22%)
           - ExecOption: Hash Table Built Asynchronously
           - BuildBuckets: 1.024K (1024)
           - BuildRows: 96
           - BuildTime: 11.722us
           - LoadFactor: 4591419820104220700.00
           - PeakMemoryUsage: 296.00 KB
           - ProbeRows: 94
           - ProbeTime: 20.819us
           - PushDownComputeTime: 0ns
           - PushDownTime: 0ns
           - RowsReturned: 94
           - RowsReturnedRate: 14.881K /sec
          EXCHANGE_NODE (id=8):(Active: 5.120ms, % non-child: 15.67%)
             - BytesReceived: 2.60 KB
             - ConvertRowBatchTime: 9.205us
             - DataArrivalWaitTime: 5.23ms
             - DeserializeRowBatchTimer: 100.752us
             - FirstBatchArrivalWaitTime: 4.94ms
             - PeakMemoryUsage: 4.16 KB
             - RowsReturned: 96
             - RowsReturnedRate: 18.747K /sec
             - SendersBlockedTotalTimer(*): 0ns
          OLAP_SCAN_NODE (id=0):(Active: 1.124ms, % non-child: 3.44%)
             - BlockLookupCacheTime: 14.329us
             - BlockPutCacheTime: 0ns
             - BytesRead: 17.96 KB
             - GetNextTime: 1.123ms
             - MaxWaitScanTime: 86.953us
             - NumDiskAccess: 1
             - NumScanners: 1
             - PeakMemoryUsage: 0.00 
             - RowsRead: 95
             - RowsReturned: 94
             - RowsReturnedRate: 83.572K /sec
             - RowsetNum: 2
             - RowsetReaderInitTime: 721.83us
             - ScanCpuTime: 40.610us
             - ScannerBlockPutTimer: 4.488us
             - ScannerMaxPendingTimer: 13.28us
             - SegmentNum: 1
             - StartScanTime: 166.708us
             - TabletCount : 1
             - TotalReadThroughput: 0.0 /sec
            OlapScanner:
               - BlockConvertTime: 11.840us
               - BlockFetchTime: 601.202us
               - ReaderInitTime: 788.363us
               - RowsDelFiltered: 0
               - RowsPushedCondFiltered: 0
               - ScanTime: 41.22us
               - ShowHintsTime_V1: 0ns
              SegmentIterator:
                 - BitmapIndexFilterTimer: 397ns
                 - BlockLoadTime: 586.104us
                 - BlockSeekCount: 118
                 - BlockSeekTime: 433.873us
                 - BlocksLoad: 1
                 - CachedPagesNum: 10
                 - CompressedBytesRead: 0.00 
                 - DecompressorTimer: 0ns
                 - IOTimer: 0ns
                 - IndexLoadTime_V1: 0ns
                 - NumSegmentFiltered: 0
                 - NumSegmentTotal: 1
                 - RawRowsRead: 380
                 - RowsBitmapIndexFiltered: 0
                 - RowsBloomFilterFiltered: 0
                 - RowsConditionsFiltered: 0
                 - RowsKeyRangeFiltered: 5.373K (5373)
                 - RowsStatsFiltered: 0
                 - RowsVectorPredFiltered: 285
                 - TotalPagesNum: 10
                 - UncompressedBytesRead: 0.00 
                 - VectorPredEvalTime: 3.434us
    Fragment 3:
      Instance 21ae6d249284eb0-82f18110a8be71e0 (host=TNetworkAddress(hostname:x, port:x)):(Active: 2.469ms, % non-child: 0.00%)
         - AverageThreadTokens: 0.00
         - FragmentCpuTime: 1.760ms
         - MemoryLimit: 2.00 GB
         - PeakMemoryUsage: 36.17 MB
         - PeakReservation: 36.00 MB
         - PeakUsedReservation: 0.00 
         - RowsProduced: 96
        BlockMgr:
           - BlockWritesOutstanding: 0
           - BlocksCreated: 0
           - BlocksRecycled: 0
           - BufferedPins: 0
           - BytesWritten: 0.00 
           - MaxBlockSize: 8.00 MB
           - TotalBufferWaitTime: 0ns
           - TotalEncryptionTime: 0ns
           - TotalIntegrityCheckTime: 0ns
           - TotalReadBlockTime: 0ns
        DataStreamSender (dst_id=8, dst_fragments=[021ae6d249284eb0-82f18110a8be71df]):(Active: 791.445us, % non-child: 2.42%)
           - BytesSent: 2.60 KB
           - IgnoreRows: 0
           - OverallThroughput: 3.2064476013183594 MB/sec
           - PeakMemoryUsage: 3.20 KB
           - SerializeBatchTime: 49.708us
           - UncompressedRowBatchSize: 5.29 KB
        AGGREGATION_NODE (id=2):(Active: 1.772ms, % non-child: 4.08%)
           - Probe Method: HashTable Linear Probing
           - BuildTime: 161.192us
           - GetResultsTime: 65.809us
           - HTResize: 0
           - HTResizeTime: 2.265us
           - HashBuckets: 16.384K (16384)
           - HashCollisions: 0
           - HashFailedProbe: 0
           - HashFilledBuckets: 96
           - HashProbe: 96
           - HashTravelLength: 0
           - LargestPartitionPercent: 16
           - MaxPartitionLevel: 0
           - NumRepartitions: 0
           - PartitionsCreated: 16
           - PeakMemoryUsage: 36.16 MB
           - RowsProcessed: 96
           - RowsRepartitioned: 0
           - RowsReturned: 96
           - RowsReturnedRate: 54.172K /sec
           - SpilledPartitions: 0
          OLAP_SCAN_NODE (id=1):(Active: 440.772us, % non-child: 1.35%)
             - BlockLookupCacheTime: 6.303us
             - BlockPutCacheTime: 0ns
             - BytesRead: 3.56 KB
             - GetNextTime: 440.674us
             - MaxWaitScanTime: 93.655us
             - NumDiskAccess: 1
             - NumScanners: 1
             - PeakMemoryUsage: 0.00 
             - RowsRead: 96
             - RowsReturned: 96
             - RowsReturnedRate: 217.799K /sec
             - RowsetNum: 2
             - RowsetReaderInitTime: 193.987us
             - ScanCpuTime: 15.877us
             - ScannerBlockPutTimer: 7.832us
             - ScannerMaxPendingTimer: 16.477us
             - SegmentNum: 1
             - StartScanTime: 118.399us
             - TabletCount : 1
             - TotalReadThroughput: 0.0 /sec
            OlapScanner:
               - BlockConvertTime: 4.844us
               - BlockFetchTime: 121.667us
               - ReaderInitTime: 218.745us
               - RowsDelFiltered: 0
               - RowsPushedCondFiltered: 0
               - ScanTime: 16.285us
               - ShowHintsTime_V1: 0ns
              SegmentIterator:
                 - BitmapIndexFilterTimer: 183ns
                 - BlockLoadTime: 113.567us
                 - BlockSeekCount: 7
                 - BlockSeekTime: 70.163us
                 - BlocksLoad: 1
                 - CachedPagesNum: 4
                 - CompressedBytesRead: 0.00 
                 - DecompressorTimer: 0ns
                 - IOTimer: 0ns
                 - IndexLoadTime_V1: 0ns
                 - NumSegmentFiltered: 0
                 - NumSegmentTotal: 1
                 - RawRowsRead: 111
                 - RowsBitmapIndexFiltered: 0
                 - RowsBloomFilterFiltered: 0
                 - RowsConditionsFiltered: 0
                 - RowsKeyRangeFiltered: 0
                 - RowsStatsFiltered: 0
                 - RowsVectorPredFiltered: 0
                 - TotalPagesNum: 4
                 - UncompressedBytesRead: 0.00 
                 - VectorPredEvalTime: 0ns
          Buffer pool:
             - AllocTime: 25.710us
             - CumulativeAllocationBytes: 34.00 MB
             - CumulativeAllocations: 17
             - PeakReservation: 36.00 MB
             - PeakUnpinnedBytes: 0.00 
             - PeakUsedReservation: 34.00 MB
             - ReservationLimit: 80.00 GB

四、疑问

doris版本：0.13.11

不加shuffle hint，都是走的broadcast，结果是异常的。

异常能稳定复现，即使我将数据导入到测试进去进行测试，异常依旧稳定复现。

我应该从什么角度去排查这个问题呢？

目前从profile看出，不加shuffle hint 的 profile 在往exchange node id=8 中发送数据的条数貌似被过滤掉了一部分。

加shuffle hint的profile，exchange node id=8中返回的数据条数和接收条数一致，没有被过滤。

不知道原因是否在这个地方。

morningman · 2021-08-22T13:12:31Z

morningman
Aug 22, 2021
Collaborator

0.13 is an old release version. I am not sure this bug has been fixed or not in the new version.
I suggest you to upgrade to the latest version to see if this bug still exist.

0 replies

EmmyMiao87 · 2021-08-23T07:24:59Z

EmmyMiao87
Aug 23, 2021
Collaborator

可以减少查询的数据量，和化简 sql 来精确定位是哪一部分，甚至是哪一条的数据导致了这个问题。
然后再 exchange node 中增加一点日志，就能直到具体问题是哪一部分代码，就好排查了。

还有一点是，可以把增加 shuffle 和不加 shuffle 声明的 plan 贴出来。先看一下规划层面的 diff

2 replies

qzsee Aug 23, 2021
Author

我正尝试在每个涉及到的exec node上打写日志，查看一下。

以下是explain

不加shuffle

-------------------------+
| Explain String                                                                                                                                                                                      |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| PLAN FRAGMENT 0                                                                                                                                                                                     |
|  OUTPUT EXPRS:<slot 30> `t1`.`date_scale` | <slot 31> `t1`.`time_tag` | <slot 32> `t2`.`region_id` | <slot 33> `t2`.`region_name` | <slot 34> `t1`.`cat_id` | <slot 35> sum(`t1`.`arranged_amt_3p`) |
|   PARTITION: RANDOM                                                                                                                                                                                 |
|                                                                                                                                                                                                     |
|   RESULT SINK                                                                                                                                                                                       |
|                                                                                                                                                                                                     |
|   7:AGGREGATE (update finalize)                                                                                                                                                                     |
|   |  output: sum(`t1`.`arranged_amt_3p`)                                                                                                                                                            |
|   |  group by: `t1`.`date_scale`, `t1`.`time_tag`, if(TupleIsNull(2), NULL, <slot 4> `region_id`), if(TupleIsNull(2), NULL, <slot 5> max(`region_name`)), `t1`.`cat_id`                             |
|   |                                                                                                                                                                                                 |
|   6:HASH JOIN                                                                                                                                                                                       |
|   |  join op: INNER JOIN (BROADCAST)                                                                                                                                                                |
|   |  hash predicates:                                                                                                                                                                               |
|   |  colocate: false, reason: Node type not match                                                                                                                                                   |
|   |  equal join conjunct: if(TupleIsNull(2), NULL, <slot 4> `region_id`) = <slot 19> `region_id`                                                                                                    |
|   |                                                                                                                                                                                                 |
|   |----9:EXCHANGE                                                                                                                                                                                   |
|   |                                                                                                                                                                                                 |
|   3:HASH JOIN                                                                                                                                                                                       |
|   |  join op: LEFT OUTER JOIN (BROADCAST)                                                                                                                                                           |
|   |  hash predicates:                                                                                                                                                                               |
|   |  colocate: false, reason: Node type not match                                                                                                                                                   |
|   |  equal join conjunct: `t1`.`customer_bu_id` = <slot 3> `bu_id`                                                                                                                                  |
|   |                                                                                                                                                                                                 |
|   |----8:EXCHANGE                                                                                                                                                                                   |
|   |                                                                                                                                                                                                 |
|   0:OlapScanNode                                                                                                                                                                                    |
|      TABLE: x                                                                                                                                        |
|      PREAGGREGATION: OFF. Reason: Aggregate Operator not match: SUM <--> REPLACE                                                                                                                    |
|      PREDICATES: `t1`.`date_scale` = '202107', `t1`.`time_tag` = 2, `t1`.`customer_bu_id` != -1, `t1`.`cat_id` = -1                                                                                 |
|      partitions=1/1                                                                                                                                                                                 |
|      rollup: x                                                                                                                                       |
|      tabletRatio=1/10                                                                                                                                                                               |
|      tabletList=884032                                                                                                                                                                              |
|      cardinality=5753                                                                                                                                                                               |
|      avgRowSize=55.481663                                                                                                                                                                           |
|      numNodes=3                                                                                                                                                                                     |
|                                                                                                                                                                                                     |
| PLAN FRAGMENT 1                                                                                                                                                                                     |
|  OUTPUT EXPRS:                                                                                                                                                                                      |
|   PARTITION: RANDOM                                                                                                                                                                                 |
|                                                                                                                                                                                                     |
|   STREAM DATA SINK                                                                                                                                                                                  |
|     EXCHANGE ID: 09                                                                                                                                                                                 |
|     UNPARTITIONED                                                                                                                                                                                   |
|                                                                                                                                                                                                     |
|   5:AGGREGATE (update finalize)                                                                                                                                                                     |
|   |  output: multi_distinct_count(DISTINCT `bu_id`), multi_distinct_count(DISTINCT if(1 = 1, `bu_id`, NULL))                                                                                        |
|   |  group by: `region_id`                                                                                                                                                                          |
|   |  having: <slot 20> multi_distinct_count(DISTINCT `bu_id`) = <slot 21> multi_distinct_count(DISTINCT if(`hp` = 1, `bu_id`, NULL))                                                                |
|   |                                                                                                                                                                                                 |
|   4:OlapScanNode                                                                                                                                                                                    |
|      TABLE: x                                                                                                                                                             |
|      PREAGGREGATION: OFF. Reason: conjunct on `region_id` which is StorageEngine value column                                                                                                       |
|      PREDICATES: NOT `region_name` LIKE '%KA%'                                                                                                                                                      |
|      partitions=1/1                                                                                                                                                                                 |
|      rollup: x                                                                                                                                                            |
|      tabletRatio=1/1                                                                                                                                                                                |
|      tabletList=884017                                                                                                                                                                              |
|      cardinality=111                                                                                                                                                                                |
|      avgRowSize=73.03603                                                                                                                                                                            |
|      numNodes=3                                                                                                                                                                                     |
|                                                                                                                                                                                                     |
| PLAN FRAGMENT 2                                                                                                                                                                                     |
|  OUTPUT EXPRS:                                                                                                                                                                                      |
|   PARTITION: RANDOM                                                                                                                                                                                 |
|                                                                                                                                                                                                     |
|   STREAM DATA SINK                                                                                                                                                                                  |
|     EXCHANGE ID: 08                                                                                                                                                                                 |
|     UNPARTITIONED                                                                                                                                                                                   |
|                                                                                                                                                                                                     |
|   2:AGGREGATE (update finalize)                                                                                                                                                                     |
|   |  output: max(`region_name`)                                                                                                                                                                     |
|   |  group by: `bu_id`, `region_id`                                                                                                                                                                 |
|   |                                                                                                                                                                                                 |
|   1:OlapScanNode                                                                                                                                                                                    |
|      TABLE: x                                                                                                                                                             |
|      PREAGGREGATION: ON                                                                                                                                                                             |
|      partitions=1/1                                                                                                                                                                                 |
|      rollup: x                                                                                                                                                            |
|      tabletRatio=1/1                                                                                                                                                                                |
|      tabletList=884017                                                                                                                                                                              |
|      cardinality=111                                                                                                                                                                                |
|      avgRowSize=73.03603                                                                                                                                                                            |
|      numNodes=3                                                                                                                                                                                     |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

shuffle

-------------------------+
| Explain String                                                                                                                                                                                      |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| PLAN FRAGMENT 0                                                                                                                                                                                     |
|  OUTPUT EXPRS:<slot 30> `t1`.`date_scale` | <slot 31> `t1`.`time_tag` | <slot 32> `t2`.`region_id` | <slot 33> `t2`.`region_name` | <slot 34> `t1`.`cat_id` | <slot 35> sum(`t1`.`arranged_amt_3p`) |
|   PARTITION: HASH_PARTITIONED: if(TupleIsNull(2), NULL, <slot 4> `region_id`)                                                                                                                       |
|                                                                                                                                                                                                     |
|   RESULT SINK                                                                                                                                                                                       |
|                                                                                                                                                                                                     |
|   7:AGGREGATE (update finalize)                                                                                                                                                                     |
|   |  output: sum(`t1`.`arranged_amt_3p`)                                                                                                                                                            |
|   |  group by: `t1`.`date_scale`, `t1`.`time_tag`, if(TupleIsNull(2), NULL, <slot 4> `region_id`), if(TupleIsNull(2), NULL, <slot 5> max(`region_name`)), `t1`.`cat_id`                             |
|   |                                                                                                                                                                                                 |
|   6:HASH JOIN                                                                                                                                                                                       |
|   |  join op: INNER JOIN (PARTITIONED)                                                                                                                                                              |
|   |  hash predicates:                                                                                                                                                                               |
|   |  colocate: false, reason: Has join hint                                                                                                                                                         |
|   |  equal join conjunct: if(TupleIsNull(2), NULL, <slot 4> `region_id`) = <slot 19> `region_id`                                                                                                    |
|   |                                                                                                                                                                                                 |
|   |----10:EXCHANGE                                                                                                                                                                                  |
|   |                                                                                                                                                                                                 |
|   9:EXCHANGE                                                                                                                                                                                        |
|                                                                                                                                                                                                     |
| PLAN FRAGMENT 1                                                                                                                                                                                     |
|  OUTPUT EXPRS:                                                                                                                                                                                      |
|   PARTITION: RANDOM                                                                                                                                                                                 |
|                                                                                                                                                                                                     |
|   STREAM DATA SINK                                                                                                                                                                                  |
|     EXCHANGE ID: 10                                                                                                                                                                                 |
|     HASH_PARTITIONED: <slot 19> `region_id`                                                                                                                                                         |
|                                                                                                                                                                                                     |
|   5:AGGREGATE (update finalize)                                                                                                                                                                     |
|   |  output: multi_distinct_count(DISTINCT `bu_id`), multi_distinct_count(DISTINCT if(1 = 1, `bu_id`, NULL))                                                                                        |
|   |  group by: `region_id`                                                                                                                                                                          |
|   |  having: <slot 20> multi_distinct_count(DISTINCT `bu_id`) = <slot 21> multi_distinct_count(DISTINCT if(`hp` = 1, `bu_id`, NULL))                                                                |
|   |                                                                                                                                                                                                 |
|   4:OlapScanNode                                                                                                                                                                                    |
|      TABLE: x                                                                                                                                                             |
|      PREAGGREGATION: OFF. Reason: conjunct on `region_id` which is StorageEngine value column                                                                                                       |
|      PREDICATES: NOT `region_name` LIKE '%KA%'                                                                                                                                                      |
|      partitions=1/1                                                                                                                                                                                 |
|      rollup: x                                                                                                                                                            |
|      tabletRatio=1/1                                                                                                                                                                                |
|      tabletList=884017                                                                                                                                                                              |
|      cardinality=111                                                                                                                                                                                |
|      avgRowSize=73.03603                                                                                                                                                                            |
|      numNodes=3                                                                                                                                                                                     |
|                                                                                                                                                                                                     |
| PLAN FRAGMENT 2                                                                                                                                                                                     |
|  OUTPUT EXPRS:                                                                                                                                                                                      |
|   PARTITION: RANDOM                                                                                                                                                                                 |
|                                                                                                                                                                                                     |
|   STREAM DATA SINK                                                                                                                                                                                  |
|     EXCHANGE ID: 09                                                                                                                                                                                 |
|     HASH_PARTITIONED: if(TupleIsNull(2), NULL, <slot 4> `region_id`)                                                                                                                                |
|                                                                                                                                                                                                     |
|   3:HASH JOIN                                                                                                                                                                                       |
|   |  join op: LEFT OUTER JOIN (BROADCAST)                                                                                                                                                           |
|   |  hash predicates:                                                                                                                                                                               |
|   |  colocate: false, reason: Node type not match                                                                                                                                                   |
|   |  equal join conjunct: `t1`.`customer_bu_id` = <slot 3> `bu_id`                                                                                                                                  |
|   |                                                                                                                                                                                                 |
|   |----8:EXCHANGE                                                                                                                                                                                   |
|   |                                                                                                                                                                                                 |
|   0:OlapScanNode                                                                                                                                                                                    |
|      TABLE: x                                                                                                                                        |
|      PREAGGREGATION: OFF. Reason: Aggregate Operator not match: SUM <--> REPLACE                                                                                                                    |
|      PREDICATES: `t1`.`date_scale` = '202107', `t1`.`time_tag` = 2, `t1`.`customer_bu_id` != -1, `t1`.`cat_id` = -1                                                                                 |
|      partitions=1/1                                                                                                                                                                                 |
|      rollup: x                                                                                                                                       |
|      tabletRatio=1/10                                                                                                                                                                               |
|      tabletList=884032                                                                                                                                                                              |
|      cardinality=5753                                                                                                                                                                               |
|      avgRowSize=55.481663                                                                                                                                                                           |
|      numNodes=3                                                                                                                                                                                     |
|                                                                                                                                                                                                     |
| PLAN FRAGMENT 3                                                                                                                                                                                     |
|  OUTPUT EXPRS:                                                                                                                                                                                      |
|   PARTITION: RANDOM                                                                                                                                                                                 |
|                                                                                                                                                                                                     |
|   STREAM DATA SINK                                                                                                                                                                                  |
|     EXCHANGE ID: 08                                                                                                                                                                                 |
|     UNPARTITIONED                                                                                                                                                                                   |
|                                                                                                                                                                                                     |
|   2:AGGREGATE (update finalize)                                                                                                                                                                     |
|   |  output: max(`region_name`)                                                                                                                                                                     |
|   |  group by: `bu_id`, `region_id`                                                                                                                                                                 |
|   |                                                                                                                                                                                                 |
|   1:OlapScanNode                                                                                                                                                                                    |
|      TABLE: x                                                                                                                                                             |
|      PREAGGREGATION: ON                                                                                                                                                                             |
|      partitions=1/1                                                                                                                                                                                 |
|      rollup: x                                                                                                                                                            |
|      tabletRatio=1/1                                                                                                                                                                                |
|      tabletList=884017                                                                                                                                                                              |
|      cardinality=111                                                                                                                                                                                |
|      avgRowSize=73.03603                                                                                                                                                                            |
|      numNodes=3                                                                                                                                                                                     |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

EmmyMiao87 Aug 24, 2021
Collaborator

根据这个描述，exchange id = 8 的下层节点，和上层单个节点。在shuffle 和非shuffle 之间并不存在diff。反而区别在于更上层的节点。
两种可能

不是exchange id=8 这里出现的问题，而是上层节点的问题
runtimefilter 的问题。用 runtime_filter_type=0 这个session 变量关闭后，再对比 shuffle 和非shuffle 的数据看看是否存在问题。

Navigation Menu

Uh oh!

查询结果不稳定 #6484

Uh oh!

{{title}}

Uh oh!

Replies: 2 comments 2 replies

Uh oh!

{{title}}

Uh oh!

Uh oh!

{{title}}

Uh oh!

Uh oh!

{{title}}

Uh oh!

Uh oh!

{{title}}

Uh oh!

Select a reply

Uh oh!

Uh oh!

查询结果不稳定 #6484

Uh oh!

qzsee Aug 22, 2021

一、查询SQL

二、查询结果

三、强制shuffle

四、疑问

Replies: 2 comments · 2 replies

Uh oh!

morningman Aug 22, 2021 Collaborator

Uh oh!

EmmyMiao87 Aug 23, 2021 Collaborator

Uh oh!

qzsee Aug 23, 2021 Author

不加shuffle

shuffle

Uh oh!

EmmyMiao87 Aug 24, 2021 Collaborator

qzsee
Aug 22, 2021

Replies: 2 comments 2 replies

morningman
Aug 22, 2021
Collaborator

EmmyMiao87
Aug 23, 2021
Collaborator

qzsee Aug 23, 2021
Author

EmmyMiao87 Aug 24, 2021
Collaborator