modules/sql-engine/src/test/resources/tpcds/plan/q9.plan

Project
    fieldNames: [BUCKET1, BUCKET2, BUCKET3, BUCKET4, BUCKET5]
    projection: [CASE(>(EXPR$0, 98972190), EXPR$0$0, EXPR$0$1), CASE(>(EXPR$0$2, 160856845), EXPR$0$3, EXPR$0$4), CASE(>(EXPR$0$5, 12733327), EXPR$0$6, EXPR$0$7), CASE(>(EXPR$0$8, 96251173), EXPR$0$9, EXPR$0$10), CASE(>(EXPR$0$11, 80049606), EXPR$0$12, EXPR$0$13)]
    est: (rows=1)
  NestedLoopJoin
      predicate: true
      fieldNames: [R_REASON_SK, EXPR$0, EXPR$0$0, EXPR$0$1, EXPR$0$2, EXPR$0$3, EXPR$0$4, EXPR$0$5, EXPR$0$6, EXPR$0$7, EXPR$0$8, EXPR$0$9, EXPR$0$10, EXPR$0$11, EXPR$0$12, EXPR$0$13]
      type: left
      est: (rows=1)
    NestedLoopJoin
        predicate: true
        fieldNames: [R_REASON_SK, EXPR$0, EXPR$0$0, EXPR$0$1, EXPR$0$2, EXPR$0$3, EXPR$0$4, EXPR$0$5, EXPR$0$6, EXPR$0$7, EXPR$0$8, EXPR$0$9, EXPR$0$10, EXPR$0$11, EXPR$0$12]
        type: left
        est: (rows=1)
      NestedLoopJoin
          predicate: true
          fieldNames: [R_REASON_SK, EXPR$0, EXPR$0$0, EXPR$0$1, EXPR$0$2, EXPR$0$3, EXPR$0$4, EXPR$0$5, EXPR$0$6, EXPR$0$7, EXPR$0$8, EXPR$0$9, EXPR$0$10, EXPR$0$11]
          type: left
          est: (rows=1)
        NestedLoopJoin
            predicate: true
            fieldNames: [R_REASON_SK, EXPR$0, EXPR$0$0, EXPR$0$1, EXPR$0$2, EXPR$0$3, EXPR$0$4, EXPR$0$5, EXPR$0$6, EXPR$0$7, EXPR$0$8, EXPR$0$9, EXPR$0$10]
            type: left
            est: (rows=1)
          NestedLoopJoin
              predicate: true
              fieldNames: [R_REASON_SK, EXPR$0, EXPR$0$0, EXPR$0$1, EXPR$0$2, EXPR$0$3, EXPR$0$4, EXPR$0$5, EXPR$0$6, EXPR$0$7, EXPR$0$8, EXPR$0$9]
              type: left
              est: (rows=1)
            NestedLoopJoin
                predicate: true
                fieldNames: [R_REASON_SK, EXPR$0, EXPR$0$0, EXPR$0$1, EXPR$0$2, EXPR$0$3, EXPR$0$4, EXPR$0$5, EXPR$0$6, EXPR$0$7, EXPR$0$8]
                type: left
                est: (rows=1)
              NestedLoopJoin
                  predicate: true
                  fieldNames: [R_REASON_SK, EXPR$0, EXPR$0$0, EXPR$0$1, EXPR$0$2, EXPR$0$3, EXPR$0$4, EXPR$0$5, EXPR$0$6, EXPR$0$7]
                  type: left
                  est: (rows=1)
                NestedLoopJoin
                    predicate: true
                    fieldNames: [R_REASON_SK, EXPR$0, EXPR$0$0, EXPR$0$1, EXPR$0$2, EXPR$0$3, EXPR$0$4, EXPR$0$5, EXPR$0$6]
                    type: left
                    est: (rows=1)
                  NestedLoopJoin
                      predicate: true
                      fieldNames: [R_REASON_SK, EXPR$0, EXPR$0$0, EXPR$0$1, EXPR$0$2, EXPR$0$3, EXPR$0$4, EXPR$0$5]
                      type: left
                      est: (rows=1)
                    NestedLoopJoin
                        predicate: true
                        fieldNames: [R_REASON_SK, EXPR$0, EXPR$0$0, EXPR$0$1, EXPR$0$2, EXPR$0$3, EXPR$0$4]
                        type: left
                        est: (rows=1)
                      NestedLoopJoin
                          predicate: true
                          fieldNames: [R_REASON_SK, EXPR$0, EXPR$0$0, EXPR$0$1, EXPR$0$2, EXPR$0$3]
                          type: left
                          est: (rows=1)
                        NestedLoopJoin
                            predicate: true
                            fieldNames: [R_REASON_SK, EXPR$0, EXPR$0$0, EXPR$0$1, EXPR$0$2]
                            type: left
                            est: (rows=1)
                          NestedLoopJoin
                              predicate: true
                              fieldNames: [R_REASON_SK, EXPR$0, EXPR$0$0, EXPR$0$1]
                              type: left
                              est: (rows=1)
                            NestedLoopJoin
                                predicate: true
                                fieldNames: [R_REASON_SK, EXPR$0, EXPR$0$0]
                                type: left
                                est: (rows=1)
                              NestedLoopJoin
                                  predicate: true
                                  type: left
                                  est: (rows=1)
                                Exchange
                                    distribution: single
                                    est: (rows=1)
                                  IndexScan
                                      table: PUBLIC.REASON
                                      index: REASON_PK
                                      type: HASH
                                      predicate: =(R_REASON_SK, 1)
                                      searchBounds: <1>
                                      fieldNames: [R_REASON_SK]
                                      est: (rows=1)
                                Project
                                    fieldNames: [EXPR$0]
                                    projection: [CAST(f0_0):BIGINT NOT NULL]
                                    est: (rows=1)
                                  ReduceSortAggregate
                                      fieldNames: [f0_0]
                                      collation: []
                                      group: []
                                      aggregation: [$SUM0(_ACC0)]
                                      est: (rows=1)
                                    Exchange
                                        distribution: single
                                        est: (rows=1)
                                      MapSortAggregate
                                          fieldNames: [_ACC0]
                                          collation: []
                                          group: []
                                          aggregation: [COUNT()]
                                          est: (rows=1)
                                        TableScan
                                            table: PUBLIC.STORE_SALES
                                            predicate: SEARCH(SS_QUANTITY, Sarg[[1..20]])
                                            fieldNames: [SS_QUANTITY]
                                            est: (rows=720101)
                              Project
                                  fieldNames: [EXPR$0]
                                  projection: [CASE(=(CAST(f0_0):DECIMAL(14, 2), 0:DECIMAL(38, 0)), null:DECIMAL(21, 16), DECIMAL_DIVIDE(CAST(f0_0):DECIMAL(14, 2), f1_1, 21, 16))]
                                  est: (rows=1)
                                ReduceSortAggregate
                                    fieldNames: [f0_0, f1_1]
                                    collation: []
                                    group: []
                                    aggregation: [SUM(0), $SUM0(1)]
                                    est: (rows=1)
                                  Project
                                      fieldNames: [0, 1]
                                      projection: [CAST(_ACC0):DECIMAL(28, 2), CAST(_ACC1):DECIMAL(38, 0) NOT NULL]
                                      est: (rows=1)
                                    Exchange
                                        distribution: single
                                        est: (rows=1)
                                      MapSortAggregate
                                          fieldNames: [_ACC0, _ACC1]
                                          collation: []
                                          group: []
                                          aggregation: [SUM(SS_EXT_DISCOUNT_AMT), COUNT(SS_EXT_DISCOUNT_AMT)]
                                          est: (rows=1)
                                        TableScan
                                            table: PUBLIC.STORE_SALES
                                            predicate: SEARCH(SS_QUANTITY, Sarg[[1..20]])
                                            fieldNames: [SS_EXT_DISCOUNT_AMT]
                                            est: (rows=720101)
                            Project
                                fieldNames: [EXPR$0]
                                projection: [CASE(=(CAST(f0_0):DECIMAL(14, 2), 0:DECIMAL(38, 0)), null:DECIMAL(21, 16), DECIMAL_DIVIDE(CAST(f0_0):DECIMAL(14, 2), f1_1, 21, 16))]
                                est: (rows=1)
                              ReduceSortAggregate
                                  fieldNames: [f0_0, f1_1]
                                  collation: []
                                  group: []
                                  aggregation: [SUM(0), $SUM0(1)]
                                  est: (rows=1)
                                Project
                                    fieldNames: [0, 1]
                                    projection: [CAST(_ACC0):DECIMAL(28, 2), CAST(_ACC1):DECIMAL(38, 0) NOT NULL]
                                    est: (rows=1)
                                  Exchange
                                      distribution: single
                                      est: (rows=1)
                                    MapSortAggregate
                                        fieldNames: [_ACC0, _ACC1]
                                        collation: []
                                        group: []
                                        aggregation: [SUM(SS_NET_PROFIT), COUNT(SS_NET_PROFIT)]
                                        est: (rows=1)
                                      TableScan
                                          table: PUBLIC.STORE_SALES
                                          predicate: SEARCH(SS_QUANTITY, Sarg[[1..20]])
                                          fieldNames: [SS_NET_PROFIT]
                                          est: (rows=720101)
                          Project
                              fieldNames: [EXPR$0]
                              projection: [CAST(f0_0):BIGINT NOT NULL]
                              est: (rows=1)
                            ReduceSortAggregate
                                fieldNames: [f0_0]
                                collation: []
                                group: []
                                aggregation: [$SUM0(_ACC0)]
                                est: (rows=1)
                              Exchange
                                  distribution: single
                                  est: (rows=1)
                                MapSortAggregate
                                    fieldNames: [_ACC0]
                                    collation: []
                                    group: []
                                    aggregation: [COUNT()]
                                    est: (rows=1)
                                  TableScan
                                      table: PUBLIC.STORE_SALES
                                      predicate: SEARCH(SS_QUANTITY, Sarg[[21..40]])
                                      fieldNames: [SS_QUANTITY]
                                      est: (rows=720101)
                        Project
                            fieldNames: [EXPR$0]
                            projection: [CASE(=(CAST(f0_0):DECIMAL(14, 2), 0:DECIMAL(38, 0)), null:DECIMAL(21, 16), DECIMAL_DIVIDE(CAST(f0_0):DECIMAL(14, 2), f1_1, 21, 16))]
                            est: (rows=1)
                          ReduceSortAggregate
                              fieldNames: [f0_0, f1_1]
                              collation: []
                              group: []
                              aggregation: [SUM(0), $SUM0(1)]
                              est: (rows=1)
                            Project
                                fieldNames: [0, 1]
                                projection: [CAST(_ACC0):DECIMAL(28, 2), CAST(_ACC1):DECIMAL(38, 0) NOT NULL]
                                est: (rows=1)
                              Exchange
                                  distribution: single
                                  est: (rows=1)
                                MapSortAggregate
                                    fieldNames: [_ACC0, _ACC1]
                                    collation: []
                                    group: []
                                    aggregation: [SUM(SS_EXT_DISCOUNT_AMT), COUNT(SS_EXT_DISCOUNT_AMT)]
                                    est: (rows=1)
                                  TableScan
                                      table: PUBLIC.STORE_SALES
                                      predicate: SEARCH(SS_QUANTITY, Sarg[[21..40]])
                                      fieldNames: [SS_EXT_DISCOUNT_AMT]
                                      est: (rows=720101)
                      Project
                          fieldNames: [EXPR$0]
                          projection: [CASE(=(CAST(f0_0):DECIMAL(14, 2), 0:DECIMAL(38, 0)), null:DECIMAL(21, 16), DECIMAL_DIVIDE(CAST(f0_0):DECIMAL(14, 2), f1_1, 21, 16))]
                          est: (rows=1)
                        ReduceSortAggregate
                            fieldNames: [f0_0, f1_1]
                            collation: []
                            group: []
                            aggregation: [SUM(0), $SUM0(1)]
                            est: (rows=1)
                          Project
                              fieldNames: [0, 1]
                              projection: [CAST(_ACC0):DECIMAL(28, 2), CAST(_ACC1):DECIMAL(38, 0) NOT NULL]
                              est: (rows=1)
                            Exchange
                                distribution: single
                                est: (rows=1)
                              MapSortAggregate
                                  fieldNames: [_ACC0, _ACC1]
                                  collation: []
                                  group: []
                                  aggregation: [SUM(SS_NET_PROFIT), COUNT(SS_NET_PROFIT)]
                                  est: (rows=1)
                                TableScan
                                    table: PUBLIC.STORE_SALES
                                    predicate: SEARCH(SS_QUANTITY, Sarg[[21..40]])
                                    fieldNames: [SS_NET_PROFIT]
                                    est: (rows=720101)
                    Project
                        fieldNames: [EXPR$0]
                        projection: [CAST(f0_0):BIGINT NOT NULL]
                        est: (rows=1)
                      ReduceSortAggregate
                          fieldNames: [f0_0]
                          collation: []
                          group: []
                          aggregation: [$SUM0(_ACC0)]
                          est: (rows=1)
                        Exchange
                            distribution: single
                            est: (rows=1)
                          MapSortAggregate
                              fieldNames: [_ACC0]
                              collation: []
                              group: []
                              aggregation: [COUNT()]
                              est: (rows=1)
                            TableScan
                                table: PUBLIC.STORE_SALES
                                predicate: SEARCH(SS_QUANTITY, Sarg[[41..60]])
                                fieldNames: [SS_QUANTITY]
                                est: (rows=720101)
                  Project
                      fieldNames: [EXPR$0]
                      projection: [CASE(=(CAST(f0_0):DECIMAL(14, 2), 0:DECIMAL(38, 0)), null:DECIMAL(21, 16), DECIMAL_DIVIDE(CAST(f0_0):DECIMAL(14, 2), f1_1, 21, 16))]
                      est: (rows=1)
                    ReduceSortAggregate
                        fieldNames: [f0_0, f1_1]
                        collation: []
                        group: []
                        aggregation: [SUM(0), $SUM0(1)]
                        est: (rows=1)
                      Project
                          fieldNames: [0, 1]
                          projection: [CAST(_ACC0):DECIMAL(28, 2), CAST(_ACC1):DECIMAL(38, 0) NOT NULL]
                          est: (rows=1)
                        Exchange
                            distribution: single
                            est: (rows=1)
                          MapSortAggregate
                              fieldNames: [_ACC0, _ACC1]
                              collation: []
                              group: []
                              aggregation: [SUM(SS_EXT_DISCOUNT_AMT), COUNT(SS_EXT_DISCOUNT_AMT)]
                              est: (rows=1)
                            TableScan
                                table: PUBLIC.STORE_SALES
                                predicate: SEARCH(SS_QUANTITY, Sarg[[41..60]])
                                fieldNames: [SS_EXT_DISCOUNT_AMT]
                                est: (rows=720101)
                Project
                    fieldNames: [EXPR$0]
                    projection: [CASE(=(CAST(f0_0):DECIMAL(14, 2), 0:DECIMAL(38, 0)), null:DECIMAL(21, 16), DECIMAL_DIVIDE(CAST(f0_0):DECIMAL(14, 2), f1_1, 21, 16))]
                    est: (rows=1)
                  ReduceSortAggregate
                      fieldNames: [f0_0, f1_1]
                      collation: []
                      group: []
                      aggregation: [SUM(0), $SUM0(1)]
                      est: (rows=1)
                    Project
                        fieldNames: [0, 1]
                        projection: [CAST(_ACC0):DECIMAL(28, 2), CAST(_ACC1):DECIMAL(38, 0) NOT NULL]
                        est: (rows=1)
                      Exchange
                          distribution: single
                          est: (rows=1)
                        MapSortAggregate
                            fieldNames: [_ACC0, _ACC1]
                            collation: []
                            group: []
                            aggregation: [SUM(SS_NET_PROFIT), COUNT(SS_NET_PROFIT)]
                            est: (rows=1)
                          TableScan
                              table: PUBLIC.STORE_SALES
                              predicate: SEARCH(SS_QUANTITY, Sarg[[41..60]])
                              fieldNames: [SS_NET_PROFIT]
                              est: (rows=720101)
              Project
                  fieldNames: [EXPR$0]
                  projection: [CAST(f0_0):BIGINT NOT NULL]
                  est: (rows=1)
                ReduceSortAggregate
                    fieldNames: [f0_0]
                    collation: []
                    group: []
                    aggregation: [$SUM0(_ACC0)]
                    est: (rows=1)
                  Exchange
                      distribution: single
                      est: (rows=1)
                    MapSortAggregate
                        fieldNames: [_ACC0]
                        collation: []
                        group: []
                        aggregation: [COUNT()]
                        est: (rows=1)
                      TableScan
                          table: PUBLIC.STORE_SALES
                          predicate: SEARCH(SS_QUANTITY, Sarg[[61..80]])
                          fieldNames: [SS_QUANTITY]
                          est: (rows=720101)
            Project
                fieldNames: [EXPR$0]
                projection: [CASE(=(CAST(f0_0):DECIMAL(14, 2), 0:DECIMAL(38, 0)), null:DECIMAL(21, 16), DECIMAL_DIVIDE(CAST(f0_0):DECIMAL(14, 2), f1_1, 21, 16))]
                est: (rows=1)
              ReduceSortAggregate
                  fieldNames: [f0_0, f1_1]
                  collation: []
                  group: []
                  aggregation: [SUM(0), $SUM0(1)]
                  est: (rows=1)
                Project
                    fieldNames: [0, 1]
                    projection: [CAST(_ACC0):DECIMAL(28, 2), CAST(_ACC1):DECIMAL(38, 0) NOT NULL]
                    est: (rows=1)
                  Exchange
                      distribution: single
                      est: (rows=1)
                    MapSortAggregate
                        fieldNames: [_ACC0, _ACC1]
                        collation: []
                        group: []
                        aggregation: [SUM(SS_EXT_DISCOUNT_AMT), COUNT(SS_EXT_DISCOUNT_AMT)]
                        est: (rows=1)
                      TableScan
                          table: PUBLIC.STORE_SALES
                          predicate: SEARCH(SS_QUANTITY, Sarg[[61..80]])
                          fieldNames: [SS_EXT_DISCOUNT_AMT]
                          est: (rows=720101)
          Project
              fieldNames: [EXPR$0]
              projection: [CASE(=(CAST(f0_0):DECIMAL(14, 2), 0:DECIMAL(38, 0)), null:DECIMAL(21, 16), DECIMAL_DIVIDE(CAST(f0_0):DECIMAL(14, 2), f1_1, 21, 16))]
              est: (rows=1)
            ReduceSortAggregate
                fieldNames: [f0_0, f1_1]
                collation: []
                group: []
                aggregation: [SUM(0), $SUM0(1)]
                est: (rows=1)
              Project
                  fieldNames: [0, 1]
                  projection: [CAST(_ACC0):DECIMAL(28, 2), CAST(_ACC1):DECIMAL(38, 0) NOT NULL]
                  est: (rows=1)
                Exchange
                    distribution: single
                    est: (rows=1)
                  MapSortAggregate
                      fieldNames: [_ACC0, _ACC1]
                      collation: []
                      group: []
                      aggregation: [SUM(SS_NET_PROFIT), COUNT(SS_NET_PROFIT)]
                      est: (rows=1)
                    TableScan
                        table: PUBLIC.STORE_SALES
                        predicate: SEARCH(SS_QUANTITY, Sarg[[61..80]])
                        fieldNames: [SS_NET_PROFIT]
                        est: (rows=720101)
        Project
            fieldNames: [EXPR$0]
            projection: [CAST(f0_0):BIGINT NOT NULL]
            est: (rows=1)
          ReduceSortAggregate
              fieldNames: [f0_0]
              collation: []
              group: []
              aggregation: [$SUM0(_ACC0)]
              est: (rows=1)
            Exchange
                distribution: single
                est: (rows=1)
              MapSortAggregate
                  fieldNames: [_ACC0]
                  collation: []
                  group: []
                  aggregation: [COUNT()]
                  est: (rows=1)
                TableScan
                    table: PUBLIC.STORE_SALES
                    predicate: SEARCH(SS_QUANTITY, Sarg[[81..100]])
                    fieldNames: [SS_QUANTITY]
                    est: (rows=720101)
      Project
          fieldNames: [EXPR$0]
          projection: [CASE(=(CAST(f0_0):DECIMAL(14, 2), 0:DECIMAL(38, 0)), null:DECIMAL(21, 16), DECIMAL_DIVIDE(CAST(f0_0):DECIMAL(14, 2), f1_1, 21, 16))]
          est: (rows=1)
        ReduceSortAggregate
            fieldNames: [f0_0, f1_1]
            collation: []
            group: []
            aggregation: [SUM(0), $SUM0(1)]
            est: (rows=1)
          Project
              fieldNames: [0, 1]
              projection: [CAST(_ACC0):DECIMAL(28, 2), CAST(_ACC1):DECIMAL(38, 0) NOT NULL]
              est: (rows=1)
            Exchange
                distribution: single
                est: (rows=1)
              MapSortAggregate
                  fieldNames: [_ACC0, _ACC1]
                  collation: []
                  group: []
                  aggregation: [SUM(SS_EXT_DISCOUNT_AMT), COUNT(SS_EXT_DISCOUNT_AMT)]
                  est: (rows=1)
                TableScan
                    table: PUBLIC.STORE_SALES
                    predicate: SEARCH(SS_QUANTITY, Sarg[[81..100]])
                    fieldNames: [SS_EXT_DISCOUNT_AMT]
                    est: (rows=720101)
    Project
        fieldNames: [EXPR$0]
        projection: [CASE(=(CAST(f0_0):DECIMAL(14, 2), 0:DECIMAL(38, 0)), null:DECIMAL(21, 16), DECIMAL_DIVIDE(CAST(f0_0):DECIMAL(14, 2), f1_1, 21, 16))]
        est: (rows=1)
      ReduceSortAggregate
          fieldNames: [f0_0, f1_1]
          collation: []
          group: []
          aggregation: [SUM(0), $SUM0(1)]
          est: (rows=1)
        Project
            fieldNames: [0, 1]
            projection: [CAST(_ACC0):DECIMAL(28, 2), CAST(_ACC1):DECIMAL(38, 0) NOT NULL]
            est: (rows=1)
          Exchange
              distribution: single
              est: (rows=1)
            MapSortAggregate
                fieldNames: [_ACC0, _ACC1]
                collation: []
                group: []
                aggregation: [SUM(SS_NET_PROFIT), COUNT(SS_NET_PROFIT)]
                est: (rows=1)
              TableScan
                  table: PUBLIC.STORE_SALES
                  predicate: SEARCH(SS_QUANTITY, Sarg[[81..100]])
                  fieldNames: [SS_NET_PROFIT]
                  est: (rows=720101)