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.

        Activity

        Hide
        hudson Hudson added a comment -

        FAILURE: Integrated in Tajo-0.11.0-build #77 (See https://builds.apache.org/job/Tajo-0.11.0-build/77/)
        TAJO-1894: Filter condition is ignored when a query involves multiple subqueries and aggregations. (jihoonson: rev 31f79c4232b8ca9c3a4e8790b271556e1111d1e7)

        • tajo-core-tests/src/test/resources/results/TestTableSubQuery/testMultipleSubqueriesWithAggregation.1.result
        • tajo-core-tests/src/test/java/org/apache/tajo/engine/query/TestTableSubQuery.java
        • CHANGES
        • tajo-plan/src/main/java/org/apache/tajo/plan/rewrite/rules/FilterPushDownRule.java
        Show
        hudson Hudson added a comment - FAILURE: Integrated in Tajo-0.11.0-build #77 (See https://builds.apache.org/job/Tajo-0.11.0-build/77/ ) TAJO-1894 : Filter condition is ignored when a query involves multiple subqueries and aggregations. (jihoonson: rev 31f79c4232b8ca9c3a4e8790b271556e1111d1e7) tajo-core-tests/src/test/resources/results/TestTableSubQuery/testMultipleSubqueriesWithAggregation.1.result tajo-core-tests/src/test/java/org/apache/tajo/engine/query/TestTableSubQuery.java CHANGES tajo-plan/src/main/java/org/apache/tajo/plan/rewrite/rules/FilterPushDownRule.java
        Hide
        hudson Hudson added a comment -

        FAILURE: Integrated in Tajo-master-build #912 (See https://builds.apache.org/job/Tajo-master-build/912/)
        TAJO-1894: Filter condition is ignored when a query involves multiple subqueries and aggregations. (jihoonson: rev 54e848e56b6194974360564fa147c9463dfa94f8)

        • tajo-core-tests/src/test/resources/results/TestTableSubQuery/testMultipleSubqueriesWithAggregation.1.result
        • tajo-core-tests/src/test/java/org/apache/tajo/engine/query/TestTableSubQuery.java
        • CHANGES
        • tajo-plan/src/main/java/org/apache/tajo/plan/rewrite/rules/FilterPushDownRule.java
        Show
        hudson Hudson added a comment - FAILURE: Integrated in Tajo-master-build #912 (See https://builds.apache.org/job/Tajo-master-build/912/ ) TAJO-1894 : Filter condition is ignored when a query involves multiple subqueries and aggregations. (jihoonson: rev 54e848e56b6194974360564fa147c9463dfa94f8) tajo-core-tests/src/test/resources/results/TestTableSubQuery/testMultipleSubqueriesWithAggregation.1.result tajo-core-tests/src/test/java/org/apache/tajo/engine/query/TestTableSubQuery.java CHANGES tajo-plan/src/main/java/org/apache/tajo/plan/rewrite/rules/FilterPushDownRule.java
        Hide
        hudson Hudson added a comment -

        FAILURE: Integrated in Tajo-master-CODEGEN-build #543 (See https://builds.apache.org/job/Tajo-master-CODEGEN-build/543/)
        TAJO-1894: Filter condition is ignored when a query involves multiple subqueries and aggregations. (jihoonson: rev 54e848e56b6194974360564fa147c9463dfa94f8)

        • CHANGES
        • tajo-core-tests/src/test/java/org/apache/tajo/engine/query/TestTableSubQuery.java
        • tajo-core-tests/src/test/resources/results/TestTableSubQuery/testMultipleSubqueriesWithAggregation.1.result
        • tajo-plan/src/main/java/org/apache/tajo/plan/rewrite/rules/FilterPushDownRule.java
        Show
        hudson Hudson added a comment - FAILURE: Integrated in Tajo-master-CODEGEN-build #543 (See https://builds.apache.org/job/Tajo-master-CODEGEN-build/543/ ) TAJO-1894 : Filter condition is ignored when a query involves multiple subqueries and aggregations. (jihoonson: rev 54e848e56b6194974360564fa147c9463dfa94f8) CHANGES tajo-core-tests/src/test/java/org/apache/tajo/engine/query/TestTableSubQuery.java tajo-core-tests/src/test/resources/results/TestTableSubQuery/testMultipleSubqueriesWithAggregation.1.result tajo-plan/src/main/java/org/apache/tajo/plan/rewrite/rules/FilterPushDownRule.java
        Hide
        jihoonson Jihoon Son added a comment -

        Committed to master and 0.11.

        Show
        jihoonson Jihoon Son added a comment - Committed to master and 0.11.
        Hide
        githubbot ASF GitHub Bot added a comment -

        Github user asfgit closed the pull request at:

        https://github.com/apache/tajo/pull/791

        Show
        githubbot ASF GitHub Bot added a comment - Github user asfgit closed the pull request at: https://github.com/apache/tajo/pull/791
        Hide
        githubbot ASF GitHub Bot added a comment -

        Github user hyunsik commented on the pull request:

        https://github.com/apache/tajo/pull/791#issuecomment-143840071

        +1 LGTM!

        Show
        githubbot ASF GitHub Bot added a comment - Github user hyunsik commented on the pull request: https://github.com/apache/tajo/pull/791#issuecomment-143840071 +1 LGTM!
        Hide
        githubbot ASF GitHub Bot added a comment -

        GitHub user jihoonson opened a pull request:

        https://github.com/apache/tajo/pull/791

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

        You can merge this pull request into a Git repository by running:

        $ git pull https://github.com/jihoonson/tajo-2 TAJO-1894

        Alternatively you can review and apply these changes as the patch at:

        https://github.com/apache/tajo/pull/791.patch

        To close this pull request, make a commit to your master/trunk branch
        with (at least) the following in the commit message:

        This closes #791


        commit 6d93b6fcc56a3a7e68035e91597d2c6c1866d362
        Author: Jihoon Son <jihoonson@apache.org>
        Date: 2015-09-25T14:13:08Z

        TAJO-1894


        Show
        githubbot ASF GitHub Bot added a comment - GitHub user jihoonson opened a pull request: https://github.com/apache/tajo/pull/791 TAJO-1894 : Filter condition is ignored when a query involves multiple subqueries and aggregations You can merge this pull request into a Git repository by running: $ git pull https://github.com/jihoonson/tajo-2 TAJO-1894 Alternatively you can review and apply these changes as the patch at: https://github.com/apache/tajo/pull/791.patch To close this pull request, make a commit to your master/trunk branch with (at least) the following in the commit message: This closes #791 commit 6d93b6fcc56a3a7e68035e91597d2c6c1866d362 Author: Jihoon Son <jihoonson@apache.org> Date: 2015-09-25T14:13:08Z TAJO-1894

          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:

              Development