[
https://issues.apache.org/jira/browse/TRAFODION-2444?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15933348#comment-15933348
]
Suresh Subbiah commented on TRAFODION-2444:
-------------------------------------------
There are correlated subqueries in the both queries given here. When the inner
table is a Hive table, performance is likely to be acceptable only if the query
is unnested. Nested joins on a Hive table are not expected to perform well.
Rather than fix the error 7000 issue, the first change here will be to unnest
subqueries.
The simple query can be unnested with a small change. Currently there is a
restriction that more than one subquery without a null rejecting predicate
cannot be unnested. This restriction is overly severe. We can handle more than
one subquery with non-null rejecting predicates, as long as these subqueries
are not nested on top of each other. The change will be to remove this
restriction. Nesting of non-null rejecting predicate subqueries is already
detected in code and subquery unnesting then avoided.
With this change the simplified query can be unnested with this plan
>>>>explain options 'f' s ;
LC RC OP OPERATOR OPT DESCRIPTION CARD
---- ---- ---- -------------------- -------- -------------------- ---------
28 . 29 root 5.00E+003
9 27 28 merge_union 5.00E+003
26 . 27 sort_partial_aggr_ro 1.00E+000
25 . 26 esp_exchange 1:4(hash2) 1.00E+000
24 . 25 sort_partial_aggr_le 1.00E+000
23 . 24 hash_groupby 3.29E+005
22 11 23 left_hybrid_hash_joi 3.29E+005
21 13 22 hybrid_hash_join 3.29E+005
20 . 21 sort_groupby 5.00E+003
16 19 20 left_merge_join 5.00E+003
18 . 19 sort 1.00E+002
17 . 18 esp_exchange 4(hash2):1 1.00E+002
. . 17 hive_scan D_METER 1.00E+002
15 . 16 sort 1.00E+002
14 . 15 esp_exchange 4(hash2):1 1.00E+002
. . 14 hive_scan C_METER 1.00E+002
12 . 13 esp_exchange 4(rep-b):1 6.59E+001
. . 12 hive_scan C_MP 6.59E+001
10 . 11 esp_exchange 4(rep-b):4(hash2) 1.00E+002
. . 10 trafodion_scan C_MP_IT_RELA 1.00E+002
8 1 9 hybrid_hash_join 5.00E+003
7 . 8 esp_exchange 1:4(hash2) 1.00E+002
6 . 7 hash_groupby 1.00E+002
5 3 6 left_hybrid_hash_joi 1.00E+002
4 . 5 esp_exchange 4(hash2):1 1.00E+002
. . 4 hive_scan C_MP 1.00E+002
2 . 3 esp_exchange 4(rep-b):1 1.00E+002
. . 2 trafodion_scan C_BILL_RELA 1.00E+002
. . 1 hive_scan C_CONS 1.00E+002
--- SQL operation complete.
>>
A test query (Q53) with the 3 union pattern has been added to
compGeneral/TEST011. This query will not compile without this change.
> compile failed with 3 union alls
> --------------------------------
>
> Key: TRAFODION-2444
> URL: https://issues.apache.org/jira/browse/TRAFODION-2444
> Project: Apache Trafodion
> Issue Type: Bug
> Reporter: Joshua Liu
> Assignee: Suresh Subbiah
> Attachments: 三个union all编译出错，单独编译都能通过.msg
>
>
> the sql is like:
> ----------
> query1
> union all
> query 2
> union all
> query3
> ----------
> it compiled failed
> but query1&2&3 can compile successfully respectively.
> see attachment for more details...
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
