I have created a VDS that I later use for row-level security. The VDS is called faculty_i_can_view and has two columns (byu_id and academic_unit_id).
I use this VDS everywhere as a subquery to determine what rows the logged in user is allowed to see. The VDS works great on its own.
However, I ran into a weird issue when I used the VDS in a subquery:
SELECT *
FROM mytable
WHERE the_id IN (SELECT byu_id FROM Faculty_Profile.faculty_i_can_view)
This is invalid SQL because the byu_id column does not exist in faculty_i_can_view (it should have been typed faculty_byu_id). However, instead of throwing an error, Dremio just ignored the whole WHERE clause. In effect, there was no row-level security. I expected an error but instead, the subquery was ignored completely.
Why didn’t Dremio throw an error saying the subquery is invalid?