cuba-platform
Incorrect JPQL parsing in SQL
- Platform version: 2019.2
- Database: PostgreSQL
Description of the bug or enhancement
Original JPQL Query:
int count = dataManager.loadValue(" select count(e) from surveyParticipant e , survey s where 1=1 or 1=2 or (s.surveyClassification=e.surveyClassification) " , Integer.class) .one();
resulting SQL Query:
SELECT COUNT(t0.ID) FROM SURVEY_PARTICIPANT t0, SURVEY_CLASSIFICATION t2, SURVEY t1 WHERE (((((? = ?) OR (? = ?)) OR (t1.SURVEY_CLASSIFICATION_ID = t2.ID)) AND (t0.DELETE_TS IS NULL)) AND ((t1.DELETE_TS IS NULL) AND ((t2.ID = t0.SURVEY_CLASSIFICATION_ID) AND (t2.DELETE_TS IS NULL)))) LIMIT ? OFFSET ? bind => [1, 1, 1, 2, 1, 0]
When accessing the 'surveyParticipant' and 'survey' entities in the WHERE block there is a condition that returns TRUE, then through OR - any conditions that should no longer have any effect on the result.
But if we link entities through a link to the 3rd (also through OR), when forming the SQL query, the 3rd table is accessed (in which there may not be records) - already through AND, which leads to a result different from the expected. If the Survey_Classification table is empty - the result is 0 - if there are records in Survey and Survey_Participant.
You should use "left join" if you want to avoid DELETE_TS condition on the 3rd table.
s.surveyClassification=e.surveyClassification
--> this means inner join and 3rd table becomes mandatory
