Relation includes non-null child records despite null condition

[mikhailovseychuk]

Version: 5.0-RC1

Team - TeamAverage Cayenne relation:

<db-relationship name="average" source="team" target="team_average" toMany="true">
    <db-attribute-pair source="game_type_id" target="game_type_id"/>
    <db-attribute-pair source="position_code" target="position_code"/>
    <db-attribute-pair source="season_id" target="season_id"/>
</db-relationship>

example of AgRest builder:

AgRequestBuilder requestBuilder = AgJaxrs.request(config)
        .addIncludes(includes)
        .andExp(Exp.equal(Team.TEAM_ID.getName(), teamId))
        .andExp(Exp.equal(Team.SEASON_ID.getName(), season))
        .andExp(Exp.equal(Team.GAME_TYPE_ID.getName(), gameType))
        .andExp(Exps.in(Team.POSITION_CODE.getName(), positionCodes))
        .andExp(Exp.equal(Team.AVERAGE.dot(TeamAverage.GAME_ID).getName(), null))
        .addSort(sort, direction);

Expected result
"average" list contains only TeamAverage objects with null gameId

Actual result:
"average" list contains TeamAverage objects with non-null gameId

As far as I can see in logs, my request creates two sql queries. Queries example (simplified):

1. first one looks ok, it will grab correct team_average rows, but it only joins table, without reading t1 columns

SELECT DISTINCT t0.*
FROM mydb.team t0
    JOIN mydb.team_average t1 ON ( ( t0.game_type_id = t1.game_type_id ) AND ( t0.position_code = t1.position_code ) ) AND ( t0.season_id = t1.season_id )

WHERE ( t0.team_id = 12 )
  AND ( t0.season_id = 20242025 )
  AND ( t1.game_id IS NULL )
  AND ( t0.game_type_id = 3 )
  AND ( t0.position_code = 'all' )
  AND ( t0.game_id IS NULL )

2.) second query grab all team_average rows, including unexpected rows with non-null game_id

SELECT DISTINCT t0.*, t1.id
FROM mydb.team_average t0
    JOIN mydb.team t1 ON ( ( t0.game_type_id = t1.game_type_id ) AND ( t0.position_code = t1.position_code ) ) AND ( t0.season_id = t1.season_id )
    JOIN mydb.team_average t2 ON ( ( t1.game_type_id = t2.game_type_id ) AND ( t1.position_code = t2.position_code ) ) AND ( t1.season_id = t2.season_id )

WHERE ( t1.team_id = 12 ) AND
      ( t1.season_id = 20242025 ) AND
      ( t2.game_id IS NULL ) AND
      ( t1.game_type_id = 3 ) AND
      ( t1.position_code = 'all' ) AND
      ( t1.game_id IS NULL );