Sushil Mohanty created LENS-101:
-----------------------------------
Summary: 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
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 ((d . 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)
