Issue72 simple selection subquery

select 
    c_name, 
    (select 
        count(O.o_orderkey) 
    from 
        orders O    
    where 
        O.o_custkey = 37
    ) as OrderCount 
from customer C 
order by c_name

before:

Total cost: 2416.59, memory=4350
PhysicOrder  (inccost=2416.59, cost=766.59, rows=150, memory=4350) (actual rows=150)
    Output: c_name[0],{@1}[1]
    <ScalarSubqueryExpr> cached 1
        -> PhysicHashAgg  (inccost=1541, cost=3, rows=1, memory=2) (actual rows=1)
            Output: {sum({count(o_orderkey)})}[0]
            Aggregates: sum({count(o_orderkey)}[0])
            -> PhysicGather Threads: 10 (inccost=1538, cost=10, rows=1) (actual rows=10)
                Output: {count(o_orderkey)}[0]
                -> PhysicHashAgg  (inccost=1528, cost=28, rows=1, memory=8) (actual rows=1, loops=10)
                    Output: {count(o_orderkey)}[0]
                    Aggregates: count(o_orderkey[0])
                    -> PhysicScanTable orders as o (inccost=1500, cost=1500, rows=26) (actual rows=2, loops=10)
                        Output: o_orderkey[0]
                        Filter: o_custkey[1]=37

    Order by: c_name[0]
    -> PhysicGather Threads: 10 (inccost=1650, cost=1500, rows=150) (actual rows=150)
        Output: c_name[1],@1
        <ScalarSubqueryExpr> cached 1
            -> PhysicHashAgg  (inccost=1541, cost=3, rows=1, memory=2) (actual rows=1)
                Output: {sum({count(o_orderkey)})}[0]
                Aggregates: sum({count(o_orderkey)}[0])
                -> PhysicGather Threads: 10 (inccost=1538, cost=10, rows=1) (actual rows=10)
                    Output: {count(o_orderkey)}[0]
                    -> PhysicHashAgg  (inccost=1528, cost=28, rows=1, memory=8) (actual rows=1, loops=10)
                        Output: {count(o_orderkey)}[0]
                        Aggregates: count(o_orderkey[0])
                        -> PhysicScanTable orders as o (inccost=1500, cost=1500, rows=26) (actual rows=2, loops=10)
                            Output: o_orderkey[0]
                            Filter: o_custkey[1]=37

        -> PhysicScanTable customer as c (inccost=150, cost=150, rows=150) (actual rows=15, loops=10)
            Output: c_name[1],@1
            <ScalarSubqueryExpr> cached 1
                -> PhysicHashAgg  (inccost=1541, cost=3, rows=1, memory=2) (actual rows=1)
                    Output: {sum({count(o_orderkey)})}[0]
                    Aggregates: sum({count(o_orderkey)}[0])
                    -> PhysicGather Threads: 10 (inccost=1538, cost=10, rows=1) (actual rows=10)
                        Output: {count(o_orderkey)}[0]
                        -> PhysicHashAgg  (inccost=1528, cost=28, rows=1, memory=8) (actual rows=1, loops=10)
                            Output: {count(o_orderkey)}[0]
                            Aggregates: count(o_orderkey[0])
                            -> PhysicScanTable orders as o (inccost=1500, cost=1500, rows=26) (actual rows=2, loops=10)
                                Output: o_orderkey[0]
                                Filter: o_custkey[1]=37

now:

Total cost: 5717.59, memory=4360
PhysicOrder  (inccost=5717.59, cost=766.59, rows=150, memory=4350) (actual rows=150)
    Output: c_name[0],{count(o_orderkey)}[1]
    Order by: c_name[0]
    -> PhysicNLJoin Left (inccost=4951, cost=1760, rows=150) (actual rows=150)
        Output: c_name[0],{count(o_orderkey)}[1]
        -> PhysicGather Threads: 10 (inccost=1650, cost=1500, rows=150) (actual rows=150)
            Output: c_name[1]
            -> PhysicScanTable customer as c (inccost=150, cost=150, rows=150) (actual rows=15, loops=10)
                Output: c_name[1]
        -> PhysicHashAgg  (inccost=1541, cost=3, rows=1, memory=2) (actual rows=1, loops=150)
            Output: {sum({count(o_orderkey)})}[0]
            Aggregates: sum({count(o_orderkey)}[0])
            Group by: 
            -> PhysicGather Threads: 10 (inccost=1538, cost=10, rows=1) (actual rows=9, loops=150)
                Output: {count(o_orderkey)}[0]
                -> PhysicHashAgg  (inccost=1528, cost=28, rows=1, memory=8) (actual rows=0, loops=10)
                    Output: {count(o_orderkey)}[0]
                    Aggregates: count(o_orderkey[0])
                    Group by: 
                    -> PhysicScanTable orders as o (inccost=1500, cost=1500, rows=26) (actual rows=2, loops=10)
                        Output: o_orderkey[0]
                        Filter: o_custkey[1]=37

zhouqingqing / qpmodel

Issue72 simple selection subquery #240