text: [sql]
  select
      l_shipmode,
      sum(case
              when o_orderpriority = '1-URGENT'
                  or o_orderpriority = '2-HIGH'
                  then cast (1 as bigint)
              else cast(0 as bigint)
          end) as high_line_count,
      sum(case
              when o_orderpriority <> '1-URGENT'
                  and o_orderpriority <> '2-HIGH'
                  then cast (1 as bigint)
              else cast(0 as bigint)
          end) as low_line_count
  from
      orders,
      lineitem
  where
          o_orderkey = l_orderkey
    and l_shipmode in ('REG AIR', 'MAIL')
    and l_commitdate < l_receiptdate
    and l_shipdate < l_commitdate
    and l_receiptdate >= date '1997-01-01'
    and l_receiptdate < date '1998-01-01'
  group by
      l_shipmode
  order by
      l_shipmode ;
  [plan-1]
  TOP-N (order by [[25: L_SHIPMODE ASC NULLS FIRST]])
      TOP-N (order by [[25: L_SHIPMODE ASC NULLS FIRST]])
          AGGREGATE ([GLOBAL] aggregate [{30: sum(28: expr)=sum(28: expr), 31: sum(29: expr)=sum(29: expr)}] group by [[25: L_SHIPMODE]] having [null]
              EXCHANGE SHUFFLE[25]
                  INNER JOIN (join-predicate [1: O_ORDERKEY = 11: L_ORDERKEY] post-join-predicate [null])
                      SCAN (columns[1: O_ORDERKEY, 6: O_ORDERPRIORITY] predicate[null])
                      EXCHANGE BROADCAST
                          SCAN (columns[21: L_SHIPDATE, 22: L_COMMITDATE, 23: L_RECEIPTDATE, 25: L_SHIPMODE, 11: L_ORDERKEY] predicate[25: L_SHIPMODE IN (REG AIR, MAIL) AND 22: L_COMMITDATE < 23: L_RECEIPTDATE AND 21: L_SHIPDATE < 22: L_COMMITDATE AND 23: L_RECEIPTDATE >= 1997-01-01 AND 23: L_RECEIPTDATE < 1998-01-01])
  [end]
  [plan-2]
  TOP-N (order by [[25: L_SHIPMODE ASC NULLS FIRST]])
      TOP-N (order by [[25: L_SHIPMODE ASC NULLS FIRST]])
          AGGREGATE ([GLOBAL] aggregate [{30: sum(28: expr)=sum(28: expr), 31: sum(29: expr)=sum(29: expr)}] group by [[25: L_SHIPMODE]] having [null]
              EXCHANGE SHUFFLE[25]
                  INNER JOIN (join-predicate [1: O_ORDERKEY = 11: L_ORDERKEY] post-join-predicate [null])
                      SCAN (columns[1: O_ORDERKEY, 6: O_ORDERPRIORITY] predicate[null])
                      EXCHANGE SHUFFLE[11]
                          SCAN (columns[21: L_SHIPDATE, 22: L_COMMITDATE, 23: L_RECEIPTDATE, 25: L_SHIPMODE, 11: L_ORDERKEY] predicate[25: L_SHIPMODE IN (REG AIR, MAIL) AND 22: L_COMMITDATE < 23: L_RECEIPTDATE AND 21: L_SHIPDATE < 22: L_COMMITDATE AND 23: L_RECEIPTDATE >= 1997-01-01 AND 23: L_RECEIPTDATE < 1998-01-01])
  [end]
  [plan-3]
  TOP-N (order by [[25: L_SHIPMODE ASC NULLS FIRST]])
      TOP-N (order by [[25: L_SHIPMODE ASC NULLS FIRST]])
          AGGREGATE ([GLOBAL] aggregate [{30: sum(28: expr)=sum(30: sum(28: expr)), 31: sum(29: expr)=sum(31: sum(29: expr))}] group by [[25: L_SHIPMODE]] having [null]
              EXCHANGE SHUFFLE[25]
                  AGGREGATE ([LOCAL] aggregate [{30: sum(28: expr)=sum(28: expr), 31: sum(29: expr)=sum(29: expr)}] group by [[25: L_SHIPMODE]] having [null]
                      INNER JOIN (join-predicate [1: O_ORDERKEY = 11: L_ORDERKEY] post-join-predicate [null])
                          SCAN (columns[1: O_ORDERKEY, 6: O_ORDERPRIORITY] predicate[null])
                          EXCHANGE BROADCAST
                              SCAN (columns[21: L_SHIPDATE, 22: L_COMMITDATE, 23: L_RECEIPTDATE, 25: L_SHIPMODE, 11: L_ORDERKEY] predicate[25: L_SHIPMODE IN (REG AIR, MAIL) AND 22: L_COMMITDATE < 23: L_RECEIPTDATE AND 21: L_SHIPDATE < 22: L_COMMITDATE AND 23: L_RECEIPTDATE >= 1997-01-01 AND 23: L_RECEIPTDATE < 1998-01-01])
  [end]
  [plan-4]
  TOP-N (order by [[25: L_SHIPMODE ASC NULLS FIRST]])
      TOP-N (order by [[25: L_SHIPMODE ASC NULLS FIRST]])
          AGGREGATE ([GLOBAL] aggregate [{30: sum(28: expr)=sum(30: sum(28: expr)), 31: sum(29: expr)=sum(31: sum(29: expr))}] group by [[25: L_SHIPMODE]] having [null]
              EXCHANGE SHUFFLE[25]
                  AGGREGATE ([LOCAL] aggregate [{30: sum(28: expr)=sum(28: expr), 31: sum(29: expr)=sum(29: expr)}] group by [[25: L_SHIPMODE]] having [null]
                      INNER JOIN (join-predicate [1: O_ORDERKEY = 11: L_ORDERKEY] post-join-predicate [null])
                          SCAN (columns[1: O_ORDERKEY, 6: O_ORDERPRIORITY] predicate[null])
                          EXCHANGE SHUFFLE[11]
                              SCAN (columns[21: L_SHIPDATE, 22: L_COMMITDATE, 23: L_RECEIPTDATE, 25: L_SHIPMODE, 11: L_ORDERKEY] predicate[25: L_SHIPMODE IN (REG AIR, MAIL) AND 22: L_COMMITDATE < 23: L_RECEIPTDATE AND 21: L_SHIPDATE < 22: L_COMMITDATE AND 23: L_RECEIPTDATE >= 1997-01-01 AND 23: L_RECEIPTDATE < 1998-01-01])
  [end]