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

This is exactly like the GROUP BY partition key that we do on the next couple of lines.

Create a small ticket to optimize this edge case for DISTINCT partition column. Currently, user has to rewrite with group by.