CODEWITHSUNDEEP
javapostgresqlquerydsl
merz
merz

Reputation: 228

QueryDsl: generate wrong sql for condition with or() and not()

I have table in DB with columns date_start and date_finish. It's date range and date_start <= date_finish. I also have date range in my code as a class Range(dateStart, dateFinish) and dateStart <= dateFinish. Then I want to find in DB table records which date range has intersection (borders are included) with range in my code.

I wrote next function for generate BooleanExpression for this condition:

public static BooleanExpression dateRangeIntersection(Range<Date> range, TemporalExpression<Date> pathStart, TemporalExpression<Date> pathFinish) {
        return pathStart.gt(range.getLast()).
                or(pathFinish.lt(range.getFirst())).
                not();
    }

I expected to get next sql condition (example date range from 2016-01-01 to 2016-01-04 ):

SELECT * FROM table t WHERE NOT (t.date_start > '2016-01-04' OR t.date_finish < '2016-01-01')

By result is:

SELECT * FROM table t WHERE t.date_start <= '2016-01-04' AND t.date_finish >= '2016-01-01'

This condition is wrong, because it can serve only the one case, when ranges in table completely included in range [2016-01-01, 2016-01-04].

I think that QueryDsl trying to understand the condition and rewrite it simpler. But in this case it makes it wrong.

Does anyone know how to disable condition optimization in querydsl or rewrite condition to get expected sql?

Upvotes: 4

Views: 683

Answers (1)

Andreas
Andreas

Reputation: 159175

When merging a NOT into a subexpression, you invert all comparisons (e.g. = becomes <> and > becomes <=) and swap AND vs OR, so these two expression are the same:

NOT (a > b OR c < d)
a <= b AND c >= d

Which is exactly what you have. It did it correctly.

Your condition became: t.date_start <= '2016-01-04' AND t.date_finish >= '2016-01-01'

You are mistaken when you say only ranges in table completely included in range [2016-01-01, 2016-01-04] will work.

Try [2015-10-25, 2016-01-02]:

t.date_start <= '2016-01-04'   AND   t.date_finish >= '2016-01-01'
`2015-10-25` <= '2016-01-04'   AND   `2016-01-02`  >= '2016-01-01'
TRUE                           AND   TRUE

Yup, that worked as expected: [2015-10-25, 2016-01-02] intersects [2016-01-01, 2016-01-04].

Upvotes: 1

Related Questions