[
https://issues.apache.org/jira/browse/LENS-141?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Sushil Mohanty updated LENS-141:
--------------------------------
Fix Version/s: 2.0
Status: Patch Available (was: Open)
> Exclude join condition as filter in inner fact query
> ----------------------------------------------------
>
> Key: LENS-141
> URL: https://issues.apache.org/jira/browse/LENS-141
> Project: Apache Lens
> Issue Type: Bug
> Components: driver-jdbc
> Affects Versions: 2.0
> Reporter: Sushil Mohanty
> Assignee: Sushil Mohanty
> Fix For: 2.0
>
>
> Currently join conditions are getting added as filters in fact subquery. This
> need to be fixed by excluding them while building query.
> {CODE}
> Input Query :
> SELECT (dim1 . date) dim1_date , sum((f . msr1)) msr1 , (dim2 . name)
> dim2_name
> FROM fact f INNER JOIN dim1 dim1 ON f.dim1_id = dim1.id and f.m2 =
> '1234'
> INNER JOIN dim2 dim2 ON f.dim2_id = dim2.id and f.dim3_id = dim2.id
> WHERE ((dim1 . date) = '2014-11-25 00:00:00') and f.m4 is not null
> GROUP BY (dim1 . date), (dim2 . name) ORDER BY dim1_date;
> {CODE}
> {CODE}
> Current Rewritten Query :
> SELECT (dim1___dim1 . date) dim1_date , sum(sum_fact___f_msr1) msr1 ,
> (dim2___dim2 . name) dim2_name
> FROM
> (SELECT fact___f.dim2_id,
> fact___f.dim1_id,
> fact___f.dim3_id,
> fact___f.m4,
> fact___f.m2,
> sum((fact___f . msr1)) AS sum_fact___f_msr1
> FROM fact fact___f
> WHERE (fact___f . m4) IS NOT NULL
> AND ((fact___f . m2) = '1234')
> AND ((fact___f . dim3_id) = (dim2___dim2 . id))
> AND fact___f.dim1_id IN
> (SELECT dim1 .id
> FROM dim1
> WHERE ((dim1. date) = '2014-11-25 00:00:00'))
> AND fact___f.dim2_id IN
> (SELECT dim2 .id
> FROM dim2
> WHERE ((fact___f . dim3_id) = (dim2___dim2 . id)))
> AND fact___f.dim3_id IN
> (SELECT dim2 .id
> FROM dim2
> WHERE ((fact___f . dim3_id) = (dim2___dim2 . id)))
> GROUP BY fact___f.dim2_id,
> fact___f.dim1_id,
> fact___f.dim3_id,
> fact___f.m4,
> fact___f.m2) fact___f
> INNER JOIN dim1 dim1___dim1 ON (((fact___f . dim1_id) = (dim1___dim1 . id))
> AND ((fact___f . m2) = '1234'))
> INNER JOIN dim2 dim2___dim2 ON (((fact___f . dim2_id) = (dim2___dim2 . id))
> AND ((fact___f . dim3_id) = (dim2___dim2 .
> id)))
> WHERE (((dim1___dim1 . date) = '2014-11-25 00:00:00')
> AND (fact___f . m4) IS NOT NULL)
> GROUP BY (dim1___dim1 . date), (dim2___dim2 . name)
> ORDER BY dim1_date ASC
> {CODE}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
