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

Sort
    collation: [C_CUSTOMER_ID ASC, C_SALUTATION ASC, C_FIRST_NAME ASC, C_LAST_NAME ASC, C_PREFERRED_CUST_FLAG ASC, C_BIRTH_DAY ASC, C_BIRTH_MONTH ASC, C_BIRTH_YEAR ASC, C_BIRTH_COUNTRY ASC, C_LOGIN ASC, C_EMAIL_ADDRESS ASC, C_LAST_REVIEW_DATE_SK ASC, CTR_TOTAL_RETURN ASC]
    fetch: 100
    est: (rows=100)
  Project
      fieldNames: [C_CUSTOMER_ID, C_SALUTATION, C_FIRST_NAME, C_LAST_NAME, C_PREFERRED_CUST_FLAG, C_BIRTH_DAY, C_BIRTH_MONTH, C_BIRTH_YEAR, C_BIRTH_COUNTRY, C_LOGIN, C_EMAIL_ADDRESS, C_LAST_REVIEW_DATE_SK, CTR_TOTAL_RETURN]
      projection: [C_CUSTOMER_ID, C_SALUTATION, C_FIRST_NAME, C_LAST_NAME, C_PREFERRED_CUST_FLAG, C_BIRTH_DAY, C_BIRTH_MONTH, C_BIRTH_YEAR, C_BIRTH_COUNTRY, C_LOGIN, C_EMAIL_ADDRESS, C_LAST_REVIEW_DATE_SK, CTR_TOTAL_RETURN]
      est: (rows=1663576)
    MergeJoin
        predicate: =(CA_ADDRESS_SK, C_CURRENT_ADDR_SK)
        type: inner
        est: (rows=1663576)
      Exchange
          distribution: single
          est: (rows=16650)
        Sort
            collation: [CA_ADDRESS_SK ASC]
            est: (rows=16650)
          TableScan
              table: PUBLIC.CUSTOMER_ADDRESS
              predicate: =(CA_STATE, _UTF-8'GA')
              fieldNames: [CA_ADDRESS_SK, CA_STATE]
              est: (rows=16650)
      HashJoin
          predicate: =(CTR_CUSTOMER_SK, C_CUSTOMER_SK)
          type: inner
          est: (rows=4995724)
        Exchange
            distribution: single
            est: (rows=100000)
          Sort
              collation: [C_CURRENT_ADDR_SK ASC]
              est: (rows=100000)
            TableScan
                table: PUBLIC.CUSTOMER
                fieldNames: [C_CUSTOMER_SK, C_CUSTOMER_ID, C_CURRENT_ADDR_SK, C_SALUTATION, C_FIRST_NAME, C_LAST_NAME, C_PREFERRED_CUST_FLAG, C_BIRTH_DAY, C_BIRTH_MONTH, C_BIRTH_YEAR, C_BIRTH_COUNTRY, C_LOGIN, C_EMAIL_ADDRESS, C_LAST_REVIEW_DATE_SK]
                est: (rows=100000)
        HashJoin
            predicate: AND(=(CTR_STATE, CTR_STATE$0), >(CAST(CTR_TOTAL_RETURN):DECIMAL(30, 17), *($f1, 1.2:DECIMAL(2, 1))))
            fieldNames: [CTR_CUSTOMER_SK, CTR_STATE, CTR_TOTAL_RETURN, CTR_STATE$0, $f1]
            type: inner
            est: (rows=4995724)
          ColocatedHashAggregate
              fieldNames: [CTR_CUSTOMER_SK, CTR_STATE, CTR_TOTAL_RETURN]
              group: [CTR_CUSTOMER_SK, CTR_STATE]
              aggregation: [SUM(WR_RETURN_AMT)]
              est: (rows=8603)
            Project
                fieldNames: [CTR_CUSTOMER_SK, CTR_STATE, WR_RETURN_AMT]
                projection: [WR_RETURNING_CUSTOMER_SK, CA_STATE, WR_RETURN_AMT]
                est: (rows=23897)
              HashJoin
                  predicate: =(WR_RETURNING_ADDR_SK, CA_ADDRESS_SK)
                  type: inner
                  est: (rows=23897)
                Exchange
                    distribution: single
                    est: (rows=50000)
                  TableScan
                      table: PUBLIC.CUSTOMER_ADDRESS
                      fieldNames: [CA_ADDRESS_SK, CA_STATE]
                      est: (rows=50000)
                HashJoin
                    predicate: =(WR_RETURNED_DATE_SK, D_DATE_SK)
                    type: inner
                    est: (rows=23897)
                  Exchange
                      distribution: single
                      est: (rows=71763)
                    TableScan
                        table: PUBLIC.WEB_RETURNS
                        fieldNames: [WR_RETURNED_DATE_SK, WR_RETURNING_CUSTOMER_SK, WR_RETURNING_ADDR_SK, WR_RETURN_AMT]
                        est: (rows=71763)
                  Exchange
                      distribution: single
                      est: (rows=24325)
                    TableScan
                        table: PUBLIC.DATE_DIM
                        predicate: =(D_YEAR, 2000)
                        fieldNames: [D_DATE_SK, D_YEAR]
                        est: (rows=24325)
          ColocatedHashAggregate
              fieldNames: [CTR_STATE, $f1]
              group: [CTR_STATE]
              aggregation: [AVG(CTR_TOTAL_RETURN)]
              est: (rows=7743)
            Project
                fieldNames: [CTR_STATE, CTR_TOTAL_RETURN]
                projection: [CTR_STATE, CTR_TOTAL_RETURN]
                est: (rows=7743)
              ColocatedHashAggregate
                  fieldNames: [CTR_CUSTOMER_SK, CTR_STATE, CTR_TOTAL_RETURN]
                  group: [CTR_CUSTOMER_SK, CTR_STATE]
                  aggregation: [SUM(WR_RETURN_AMT)]
                  est: (rows=7743)
                Project
                    fieldNames: [CTR_CUSTOMER_SK, CTR_STATE, WR_RETURN_AMT]
                    projection: [WR_RETURNING_CUSTOMER_SK, CA_STATE, WR_RETURN_AMT]
                    est: (rows=21507)
                  HashJoin
                      predicate: =(WR_RETURNING_ADDR_SK, CA_ADDRESS_SK)
                      type: inner
                      est: (rows=21507)
                    Exchange
                        distribution: single
                        est: (rows=45000)
                      TableScan
                          table: PUBLIC.CUSTOMER_ADDRESS
                          predicate: IS NOT NULL(CA_STATE)
                          fieldNames: [CA_ADDRESS_SK, CA_STATE]
                          est: (rows=45000)
                    HashJoin
                        predicate: =(WR_RETURNED_DATE_SK, D_DATE_SK)
                        type: inner
                        est: (rows=23897)
                      Exchange
                          distribution: single
                          est: (rows=71763)
                        TableScan
                            table: PUBLIC.WEB_RETURNS
                            fieldNames: [WR_RETURNED_DATE_SK, WR_RETURNING_CUSTOMER_SK, WR_RETURNING_ADDR_SK, WR_RETURN_AMT]
                            est: (rows=71763)
                      Exchange
                          distribution: single
                          est: (rows=24325)
                        TableScan
                            table: PUBLIC.DATE_DIM
                            predicate: =(D_YEAR, 2000)
                            fieldNames: [D_DATE_SK, D_YEAR]
                            est: (rows=24325)