Add guards for unsupported subqueries join plan, optimize the possible subqueriese

Why is DISTINCT being handled as a special case here? This is not correct. It will not allow "... FROM (SELECT DISTINCT PARTITION_ID D_ID ... ) D, P1 WHERE D.D_ID = P1.PARTITION_ID;" to be pushed down.