In a Direct Query report on an Azure SQL database I configured relationships from USER to ORGANISATION (n:1, filter in both directions and "Security filter in both directions") and from ORGANISATION to ERRORS (1:n). I also configured RLS for the user id to filter the USER table.
When I put a filter on user in the the report it work fine, but when I use the "Show as" feature to filter on [USER_ID] = USERNAME() no data is shown in visualizations containing the datetime column.
Looking at some of the generated SQL I see with filtering on a user in the where clause:
WHERE ( [t1].[USER_ID] = N'TWS-OrgUser1@cgi.com' )
With "Show as" for a specific user, 2 queries are executed. 1 with a similar where clause selecting all but the Organisation_id column and a second one with a very large WHERE clause containing every column and value from the first. This one returns 0 rows, because of the clauses on the Date/Time/Timezone column like:
[t2].[Log timestamp] = CAST( '20180706 11:10:23' AS datetime)
The issue disappears if I change the type of the column to text, but I want it to be a date time column for filtering.
My assumption is that the security filter in both directions causes the very large where clause including the filtering on date/time/timezone. Is this a bug or how can I prevent this?
