Fixes broken global vulnerability audit view for MSSQL.
Tested with MSSQL, PostgreSQL, and H2.
Addressed Issue
Fixes #3692
Additional Details
MSSQL does not support DISTINCT for columns of type TEXT, which DESCRIPTION and RECOMMENDATION are.
Because the database schema is controlled by DataNucleus, and DataNucleus doesn't allow us to customize column types for specific RDBMSes, changing the respective columns to VARCHAR(MAX) is not possible.
DISTINCT was needed because finding rows are joined with the PROJECT_ACCESS_TEAMS table, to support portfolio ACLs. If a user is member of multiple teams, the query would yield a duplicate row for each permitted team the user is a member of.
The need for DISTINCT is eliminated by converting the ACL check from a LEFT JOIN to an EXISTS subquery.
Description
Fixes broken global vulnerability audit view for MSSQL.
Tested with MSSQL, PostgreSQL, and H2.
Addressed Issue
Fixes #3692
Additional Details
MSSQL does not support
DISTINCT
for columns of typeTEXT
, whichDESCRIPTION
andRECOMMENDATION
are.Because the database schema is controlled by DataNucleus, and DataNucleus doesn't allow us to customize column types for specific RDBMSes, changing the respective columns to
VARCHAR(MAX)
is not possible.DISTINCT
was needed because finding rows are joined with thePROJECT_ACCESS_TEAMS
table, to support portfolio ACLs. If a user is member of multiple teams, the query would yield a duplicate row for each permitted team the user is a member of.The need for
DISTINCT
is eliminated by converting the ACL check from aLEFT JOIN
to anEXISTS
subquery.Checklist