Uploaded image for project: 'Tajo'
  1. Tajo
  2. TAJO-1894

Filter condition is ignored when a query involves multiple subqueries and aggregations

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 0.11.0
    • Component/s: Planner/Optimizer
    • Labels:
      None

      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.

        Attachments

          Activity

            People

            • Assignee:
              jihoonson Jihoon Son
              Reporter:
              jihoonson Jihoon Son
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: