@Marco Bellaelli:

Firstly ,Thank you for the suggestion - having an alias for the sub query works.

Secondly, is this a new change because the same query that i provided worked in older versions?

Also for my own understanding, it would be great if you can explain a bit more.

If i want to use the id attribute of any other tables from the top query, then i would be using it as u1.id (or) u2.id. I think if there is a alias used in the top query and no alias used in the sub query, then the auto interpretation should be that the id attribute if used as such (id.queryId) should belong to the subquery right?

Thank you. Appreciate it.