You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@lens.apache.org by "Sushil Mohanty (JIRA)" <ji...@apache.org> on 2014/12/18 10:32:13 UTC
[jira] [Created] (LENS-141) Exclude join condition as filter in
inner fact query
Sushil Mohanty created LENS-141:
-----------------------------------
Summary: 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
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)