You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@tajo.apache.org by "Jihoon Son (JIRA)" <ji...@apache.org> on 2015/09/25 16:03:04 UTC
[jira] [Assigned] (TAJO-1894) Filter condition is ignored when a
query involves multiple subqueries and aggregations
[ https://issues.apache.org/jira/browse/TAJO-1894?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Jihoon Son reassigned TAJO-1894:
--------------------------------
Assignee: Jihoon Son
> Filter condition is ignored when a query involves multiple subqueries and aggregations
> --------------------------------------------------------------------------------------
>
> Key: TAJO-1894
> URL: https://issues.apache.org/jira/browse/TAJO-1894
> Project: Tajo
> Issue Type: Bug
> Components: Planner/Optimizer
> Reporter: Jihoon Son
> Assignee: Jihoon Son
>
> You can reproduce this bug as follows:
> {noformat}
> 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)
> {noformat}
> In the above query, the last filter condition 'ranking < 5' is disappeared.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)