You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@tajo.apache.org by "Hyunsik Choi (JIRA)" <ji...@apache.org> on 2015/09/04 08:14:45 UTC
[jira] [Commented] (TAJO-1800) WHERE clause is ignored with UNION
[ https://issues.apache.org/jira/browse/TAJO-1800?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14730369#comment-14730369 ]
Hyunsik Choi commented on TAJO-1800:
------------------------------------
Aside from this bug, the query statement causes a wrong query plan as follows:
{noformat}
explain
-------------------------------
TABLE_SUBQUERY(7) as default.a
=> Targets: default.a.c_custkey (INT4), default.a.ret (FLOAT8)
=> out schema: {(2) default.a.c_custkey (INT4), default.a.ret (FLOAT8)}
=> in schema: {(2) default.a.c_custkey (INT4), default.a.ret (FLOAT8)}
GROUP_BY(11)(c_custkey,ret)
=> target list: default.#SQ_0.c_custkey (INT4), default.#SQ_0.ret (FLOAT8)
=> out schema:{(2) default.#SQ_0.c_custkey (INT4), default.#SQ_0.ret (FLOAT8)}
=> in schema:{(2) default.#SQ_0.c_custkey (INT4), default.#SQ_0.ret (FLOAT8)}
TABLE_SUBQUERY(10) as default.#SQ_0
=> Targets: default.#SQ_0.c_custkey (INT4), default.#SQ_0.ret (FLOAT8)
=> out schema: {(2) default.#SQ_0.c_custkey (INT4), default.#SQ_0.ret (FLOAT8)}
=> in schema: {(2) default.#SQ_0.c_custkey (INT4), default.#SQ_0.ret (FLOAT8)}
UNION(6)
PROJECTION(5)
=> Targets: default.customer.c_custkey (INT4), round(?sum_5 (FLOAT8) / CAST (?sum_6 (INT8) AS FLOAT8),4) as ret
=> out schema: {(2) default.customer.c_custkey (INT4), ret (FLOAT8)}
=> in schema: {(3) ?sum_5 (FLOAT8), ?sum_6 (INT8), default.customer.c_custkey (INT4)}
GROUP_BY(4)(c_custkey)
=> exprs: (sum(?multiply_4 (FLOAT8)),sum(15000000))
=> target list: default.customer.c_custkey (INT4), ?sum_5 (FLOAT8), ?sum_6 (INT8)
=> out schema:{(3) ?sum_5 (FLOAT8), ?sum_6 (INT8), default.customer.c_custkey (INT4)}
=> in schema:{(3) ?multiply_4 (FLOAT8), default.customer.c_acctbal (FLOAT8), default.customer.c_custkey (INT4)}
SCAN(3) on default.customer
=> target list: default.customer.c_acctbal (FLOAT8), default.customer.c_custkey (INT4), default.customer.c_acctbal (FLOAT8) * 1.5E7 as ?multiply_4
=> out schema: {(3) ?multiply_4 (FLOAT8), default.customer.c_acctbal (FLOAT8), default.customer.c_custkey (INT4)}
=> in schema: {(8) default.customer.c_acctbal (FLOAT8), default.customer.c_address (TEXT), default.customer.c_comment (TEXT), default.customer.c_custkey (INT4), default.customer.c_mktsegment (TEXT), default.customer.c_name (TEXT), default.customer.c_nationkey (INT4), default.customer.c_phone (TEXT)}
PROJECTION(2)
=> Targets: default.customer.c_custkey (INT4), round(?sum_2 (FLOAT8) / CAST (?sum_3 (INT8) AS FLOAT8),4) as ret
=> out schema: {(2) default.customer.c_custkey (INT4), ret (FLOAT8)}
=> in schema: {(3) ?sum_2 (FLOAT8), ?sum_3 (INT8), default.customer.c_custkey (INT4)}
GROUP_BY(1)(c_custkey)
=> exprs: (sum(?multiply_1 (FLOAT8)),sum(15000000))
=> target list: default.customer.c_custkey (INT4), ?sum_2 (FLOAT8), ?sum_3 (INT8)
=> out schema:{(3) ?sum_2 (FLOAT8), ?sum_3 (INT8), default.customer.c_custkey (INT4)}
=> in schema:{(3) ?multiply_1 (FLOAT8), default.customer.c_acctbal (FLOAT8), default.customer.c_custkey (INT4)}
SCAN(0) on default.customer
=> target list: default.customer.c_acctbal (FLOAT8), default.customer.c_custkey (INT4), default.customer.c_acctbal (FLOAT8) * 1.5E7 as ?multiply_1
=> out schema: {(3) ?multiply_1 (FLOAT8), default.customer.c_acctbal (FLOAT8), default.customer.c_custkey (INT4)}
=> in schema: {(8) default.customer.c_acctbal (FLOAT8), default.customer.c_address (TEXT), default.customer.c_comment (TEXT), default.customer.c_custkey (INT4), default.customer.c_mktsegment (TEXT), default.customer.c_name (TEXT), default.customer.c_nationkey (INT4), default.customer.c_phone (TEXT)}
{noformat}
> WHERE clause is ignored with UNION
> ----------------------------------
>
> Key: TAJO-1800
> URL: https://issues.apache.org/jira/browse/TAJO-1800
> Project: Tajo
> Issue Type: Bug
> Reporter: Jihoon Son
> Assignee: Hyunsik Choi
> Fix For: 0.11.0
>
>
> You can reproduce as follows:
> {noformat}
> tpch> select c_custkey, ret
> > from (select c_custkey, ROUND(sum(c_acctbal*15000000)/sum(15000000),4) as ret from customer group by c_custkey
> > union
> > select c_custkey, ROUND(sum(c_acctbal*15000000)/sum(15000000),4) as ret from customer group by c_custkey) a
> > where ret > 0.02;
> c_custkey, ret
> -------------------------------
> 64568, 3631.24
> 26627, 9070.85
> 105296, 2863.05
> 51704, 7404.84
> 111424, -977.6
> 32824, 3718.26
> 138521, -510.01
> ...
> {noformat}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)