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 15:31:04 UTC
[jira] [Created] (TAJO-1894) Filter condition is ignored when a
query involves multiple subqueries and aggregations
Jihoon Son created TAJO-1894:
--------------------------------
Summary: 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
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}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)