[CAY-2863] DbEntity qualifiers are no longer applied to JOIN conditions

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 4.2, 4.2.1, 5.0-M2
• Fix Version/s: 4.2.2, 5.0-M2
• Component/s: Core Library
• Labels: Regression
• Assignee: Unassigned
• Reporter: Jurgen Doll
• Created: 02/Jul/24 14:04
• Updated: 10/Sep/24 10:23

Description

This is a regression from 3.1.3

DbEntity qualifiers used to be applied to JOIN conditions but have now moved to the WHERE clause. This results in queries returning no results when there are OR conditions in the WHERE part that could satisfy the query.

Here's a possible test case for DefaultSelectTranslatorIT.java

    @Test
    public void testDbEntityQualifier_JoinQuery() throws Exception {
        
        final DbEntity entity = context.getEntityResolver().getDbEntity("ARTIST");
        entity.setQualifier(ExpressionFactory.exp("ARTIST_NAME = 'Should be on JOIN condition and not WHERE'"));

        ObjectSelect<Painting> q = ObjectSelect.query(Painting.class)
            .where
            (
                Painting.TO_ARTIST.dot(Artist.DATE_OF_BIRTH).eq(new java.sql.Date(1,0,1))
                .orExp( Painting.TO_GALLERY.dot( Gallery.GALLERY_NAME ).like( "G%" ) )
            );

        // If the DbEntity qualifier is set on the WHERE condition then the OR expression will fail to find matches

        SelectTranslator transl = new DefaultSelectTranslator(q, dataNode.getAdapter(), dataNode.getEntityResolver());
        try {
            String generatedSql = transl.getSql();
            int whereNdx = generatedSql.indexOf(" WHERE ");
            int joinNdx = generatedSql.indexOf(" JOIN ARTIST ");
            assertTrue(generatedSql.substring(joinNdx, whereNdx).indexOf("ARTIST_NAME") > 0); // Should be in JOIN condition
            assertTrue(generatedSql.indexOf("ARTIST_NAME", whereNdx) < 0); // Should not be part of WHERE
        }
        finally {
            entity.setQualifier(null);
        }
    }