[LENS-141] Exclude join condition as filter in inner fact query

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 2.0
• Fix Version/s: 2.0
• Component/s: driver-jdbc
• Assignee: SushilMohanty
• Reporter: SushilMohanty
• Created: 18/Dec/14 09:31
• Updated: 19/Feb/15 07:34
• Resolved: 23/Dec/14 12:18

Description

Currently join conditions are getting added as filters in fact subquery. This need to be fixed by excluding them while building query.

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;

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