[
https://issues.apache.org/jira/browse/TAJO-1894?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Jihoon Son updated TAJO-1894:
-----------------------------
Description:
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.
was:
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}
> 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}
> In the above query, the last filter condition 'ranking < 5' is disappeared.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
