[TAJO-1894] Filter condition is ignored when a query involves multiple subqueries and aggregations

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 0.11.0
• Component/s: Planner/Optimizer
• Assignee: Jihoon Son
• Reporter: Jihoon Son
• Created: 25/Sep/15 13:30
• Updated: 28/Sep/15 23:01
• Resolved: 28/Sep/15 21:20

Description

You can reproduce this bug as follows:

tpch1> explain select 
>   o_custkey, cnt 
> from 
>   ( 
>     select 
>       o_custkey, cnt, row_number() over (partition by o_custkey order by cnt desc) ranking 
>     from 
>       (
>         select 
>           o_custkey, l_suppkey, count(*) cnt
>         from 
>           orders, lineitem
>         where 
>           l_orderkey = o_orderkey
>         group by 
>           o_custkey, l_suppkey
>         having cnt > 0
>       ) t
>   ) t2 
> where 
>   ranking < 5;
explain
-------------------------------
TABLE_SUBQUERY(8) as tpch1.t2
  => Targets: tpch1.t2.o_custkey (INT8), tpch1.t2.cnt (INT8)
  => out schema: {(2) tpch1.t2.o_custkey (INT8), tpch1.t2.cnt (INT8)}
  => in  schema: {(3) tpch1.t2.o_custkey (INT8), tpch1.t2.cnt (INT8), tpch1.t2.ranking (INT8)}
   PROJECTION(7)
     => Targets: tpch1.t.o_custkey (INT8), tpch1.t.cnt (INT8), ?windowfunction_1 (INT8) as ranking
     => out schema: {(3) tpch1.t.o_custkey (INT8), tpch1.t.cnt (INT8), ranking (INT8)}
     => in  schema: {(3) tpch1.t.o_custkey (INT8), tpch1.t.cnt (INT8), ?windowfunction_1 (INT8)}
      WINDOW_AGG(12)(PARTITION BY o_custkey)
        => exprs: (row_number(row_number()ORDER BY tpch1.t.cnt (INT8) (desc))
        => target list: tpch1.t.o_custkey (INT8), tpch1.t.cnt (INT8), ?windowfunction_1 (INT8)
        => out schema:{(3) tpch1.t.o_custkey (INT8), tpch1.t.cnt (INT8), ?windowfunction_1 (INT8)}
        => in schema:{(2) tpch1.t.o_custkey (INT8), tpch1.t.cnt (INT8)}
         TABLE_SUBQUERY(6) as tpch1.t
           => Targets: tpch1.t.o_custkey (INT8), tpch1.t.cnt (INT8)
           => out schema: {(2) tpch1.t.o_custkey (INT8), tpch1.t.cnt (INT8)}
           => in  schema: {(3) tpch1.t.o_custkey (INT8), tpch1.t.l_suppkey (INT8), tpch1.t.cnt (INT8)}
            HAVING(4) (cnt (INT8) > 0)
               GROUP_BY(3)(o_custkey,l_suppkey)
                 => exprs: (count())
                 => target list: tpch1.orders.o_custkey (INT8), tpch1.lineitem.l_suppkey (INT8), cnt (INT8)
                 => out schema:{(3) tpch1.orders.o_custkey (INT8), tpch1.lineitem.l_suppkey (INT8), cnt (INT8)}
                 => in schema:{(2) tpch1.orders.o_custkey (INT8), tpch1.lineitem.l_suppkey (INT8)}
                  JOIN(14)(INNER)
                    => Join Cond: tpch1.lineitem.l_orderkey (INT8) = tpch1.orders.o_orderkey (INT8)
                    => target list: tpch1.orders.o_custkey (INT8), tpch1.lineitem.l_suppkey (INT8)
                    => out schema: {(2) tpch1.orders.o_custkey (INT8), tpch1.lineitem.l_suppkey (INT8)}
                    => in schema: {(4) tpch1.lineitem.l_suppkey (INT8), tpch1.lineitem.l_orderkey (INT8), tpch1.orders.o_custkey (INT8), tpch1.orders.o_orderkey (INT8)}
                     SCAN(0) on tpch1.orders
                       => target list: tpch1.orders.o_custkey (INT8), tpch1.orders.o_orderkey (INT8)
                       => out schema: {(2) tpch1.orders.o_custkey (INT8), tpch1.orders.o_orderkey (INT8)}
                       => in schema: {(9) tpch1.orders.o_orderkey (INT8), tpch1.orders.o_custkey (INT8), tpch1.orders.o_orderstatus (TEXT), tpch1.orders.o_totalprice (FLOAT8), tpch1.orders.o_orderdate (DATE), tpch1.orders.o_orderpriority (TEXT), tpch1.orders.o_clerk (TEXT), tpch1.orders.o_shippriority (INT4), tpch1.orders.o_comment (TEXT)}
                     SCAN(1) on tpch1.lineitem
                       => target list: tpch1.lineitem.l_suppkey (INT8), tpch1.lineitem.l_orderkey (INT8)
                       => out schema: {(2) tpch1.lineitem.l_suppkey (INT8), tpch1.lineitem.l_orderkey (INT8)}
                       => in schema: {(16) tpch1.lineitem.l_orderkey (INT8), tpch1.lineitem.l_partkey (INT8), tpch1.lineitem.l_suppkey (INT8), tpch1.lineitem.l_linenumber (INT8), tpch1.lineitem.l_quantity (FLOAT8), tpch1.lineitem.l_extendedprice (FLOAT8), tpch1.lineitem.l_discount (FLOAT8), tpch1.lineitem.l_tax (FLOAT8), tpch1.lineitem.l_returnflag (TEXT), tpch1.lineitem.l_linestatus (TEXT), tpch1.lineitem.l_shipdate (DATE), tpch1.lineitem.l_commitdate (DATE), tpch1.lineitem.l_receiptdate (DATE), tpch1.lineitem.l_shipinstruct (TEXT), tpch1.lineitem.l_shipmode (TEXT), tpch1.lineitem.l_comment (TEXT)}
(36 rows, 0.073 sec, 0 B selected)

In the above query, the last filter condition 'ranking < 5' is disappeared.