Row Level Policy not working with null and not null values

Support
#1

Hello,

I’ve got a row level policy that works fine with either of two conditions, but not both at the same time.

    @JpqlRowLevelPolicy(entityClass = AvailableLoad.class,
            where = "{E}.assignedTo is null or {E}.assignedTo.userGroup = :current_user_userGroup")
    void availableLoad();

I’ve got another one that does the same thing when one field is null (salespersonAssigned is a nullable field):

    @JpqlRowLevelPolicy(entityClass = Lead.class,
            where = "{E}.user.userGroup = :current_user_userGroup or {E}.salespersonAssigned.userGroup = :current_user_userGroup")
    void lead();

In all cases above, the association attribute (assignedTo, user, and salespersonAssigned) is of my extended user class. There seems to be a problem when the policies are being evaluated with a nullable value. I’ve tried as many combinations as I can think of and can’t get the results I need.

Assistance is appreciated.

Adam

#2

Hi Adam,

Most probably your policy doesn’t work as expected because ORM generates an inner join and records with null references are discarded. So you should explicitly tell it to make a left outer join.

In my example, this works:

@JpqlRowLevelPolicy(
        entityClass = User.class,
        join = "left join {E}.department dept",
        where = "dept is null or dept.hrManager.id = :current_user_id")
void user();

It allows Users that have null department and those in a department whose manager is the current user.

I’d recommend looking at the SQL output when you encounter a problem with row-level policies.

Regards,
Konstantin

#3

Thanks for the help. Somehow I’d completely forgotten about outer joins. Silly. So where can I see the SQL output? This is one of the bigger blind spots for me and why I’ve had so much trouble with JPQL queries.

#4

In any new project you have this property in application.properties:

# 'debug' level logs SQL generated by EclipseLink ORM
logging.level.eclipselink.logging.sql = info

So just switch it to debug.