[
https://issues.apache.org/jira/browse/LENS-101?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14239322#comment-14239322
]
Sushil Mohanty commented on LENS-101:
-------------------------------------
{CODE}
Final Rewritten :
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}
> Ignore snowflake join keys from fact subquery
> ----------------------------------------------
>
> Key: LENS-101
> URL: https://issues.apache.org/jira/browse/LENS-101
> Project: Apache Lens
> Issue Type: Bug
> Components: driver-jdbc
> Reporter: Sushil Mohanty
> Assignee: Sushil Mohanty
> Fix For: 2.0
>
> Attachments: LENS-101.patch
>
>
> For snowflake joins between dimensions the left join key has to be removed
> form the fact sub-query. It should have columns those can be queried from
> fact.
> {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 ((dim1 . date) = '2014-11-25 00:00:00')
> GROUP BY (dim1 . date), (dim2 . name), (dim3 . name) , (dim4 . name) ;
> {CODE}
> {CODE}
> Currently Rewritten as :
> 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,
> dim2___dim2.id_2,
> 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,
> dim2___dim2.id_2) 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)
