[
https://issues.apache.org/jira/browse/LENS-102?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14239724#comment-14239724
]
Sushil Mohanty commented on LENS-102:
-------------------------------------
{CODE}
Final Rewritten Query :
SELECT (dim1___dim1 . date) date , sum(sum_fact___f_msr1) msr1 , (dim2___dim2 .
name) dim2_name , (dim3___dim3 . name) dim3_name , (dim4___dim4 . name)
dim4_name
FROM
(SELECT fact___f.dim1_id,
fact___f.dim2_id,
fact___f.dim3_id,
sum((fact___f . msr1)) AS sum_fact___f_msr1
FROM fact fact___f
WHERE fact___f.dim1_id IN
(SELECT dim1 .id
FROM dim1
WHERE ((dim1. date) = '2014-11-25 00:00:00'))
GROUP BY fact___f.dim1_id,
fact___f.dim2_id,
fact___f.dim3_id) fact___f
INNER JOIN dim1 dim1___dim1 ON ((fact___f . dim1_id) = (dim1___dim1 . id))
INNER JOIN dim2 dim2___dim2 ON ((fact___f . dim2_id) = (dim2___dim2 . id))
INNER JOIN dim3 dim3___dim3 ON ((fact___f . dim3_id) = (dim3___dim3 . id))
INNER JOIN dim4 dim4___dim4 ON ((dim2___dim2 . id_2) = (dim4___dim4 . id_2))
WHERE ((dim1___dim1 . date) = '2014-11-25 00:00:00')
GROUP BY (dim1___dim1 . date), (dim2___dim2 . name), (dim3___dim3 . name),
(dim4___dim4 . name)
{CODE}
> Keeping join order same in rewritten and input query
> -----------------------------------------------------
>
> Key: LENS-102
> URL: https://issues.apache.org/jira/browse/LENS-102
> Project: Apache Lens
> Issue Type: Bug
> Components: driver-jdbc
> Reporter: Sushil Mohanty
> Fix For: 2.0
>
>
> Constructing join tree by parsing recursively from AST reverses the order of
> join conditions. Same input order should be maintained in the rewritten query.
> {CODE}
> Input Query :
> SELECT (dim1 . date) date , sum((f . msr1)) msr1 , (dim2 . name) dim2_name,
> (dim3 . name) dim3_name , (dim4 . name) dim4_name
> FROM fact f
> INNER JOIN dim1 dim1 ON f.dim1_id = dim1.id
> INNER JOIN dim2 dim2 ON f.dim2_id = dim2.id
> INNER JOIN dim3 dim3 ON f.dim3_id = dim3.id
> INNER JOIN dim4 dim4 ON dim2.id_2 = dim4.id_2
> WHERE ((d . date) = '2014-11-25 00:00:00')
> GROUP BY (dim1 . date), (dim2 . name), (dim3 . name) , (dim4 . name) ;
> {CODE}
> {CODE}
> Current Rewritten Query :
> SELECT (dim1___dim1 . date) date , sum(sum_fact___f_msr1) msr1 , (dim2___dim2
> . name) dim2_name , (dim3___dim3 . name) dim3_name , (dim4___dim4 . name)
> dim4_name
> FROM
> (SELECT fact___f.dim1_id,
> fact___f.dim2_id,
> fact___f.dim3_id,
> sum((fact___f . msr1)) AS sum_fact___f_msr1
> FROM fact fact___f
> WHERE fact___f.dim1_id IN
> (SELECT dim1 .id
> FROM dim1
> WHERE ((dim1. date) = '2014-11-25 00:00:00'))
> GROUP BY fact___f.dim1_id,
> fact___f.dim2_id,
> fact___f.dim3_id) fact___f
> INNER JOIN dim4 dim4___dim4 ON ((dim2___dim2 . id_2) = (dim4___dim4 . id_2))
> INNER JOIN dim3 dim3___dim3 ON ((fact___f . dim3_id) = (dim3___dim3 . id))
> INNER JOIN dim2 dim2___dim2 ON ((fact___f . dim2_id) = (dim2___dim2 . id))
> INNER JOIN dim1 dim1___dim1 ON ((fact___f . dim1_id) = (dim1___dim1 . id))
> WHERE ((dim1___dim1 . date) = '2014-11-25 00:00:00')
> GROUP BY (dim1___dim1 . date), (dim2___dim2 . name), (dim3___dim3 . name),
> (dim4___dim4 . name)
> {CODE}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
