Panache Query With Mongo: and operator not working properly with multiple conditions on same field

[rmanibus]

The following test is failing:

    @Test
    public void failingQuery() {

        UUID videoId = UUID.randomUUID();
        LocalDateTime now = LocalDateTime.now();
        View view = new View(videoId, now.minusMinutes(10));
        view.persist();

        long count1 = View.count("createdAt > ?1", now.minusMinutes(2));
        logger.info("count 1: {}", count1);
        assertThat(count1).isEqualTo(0); // passing

        long count2 = View.count("createdAt > ?1 and createdAt < ?2", now.minusMinutes(2), now);
        logger.info("count 2: {}", count2);
        assertThat(count2).isEqualTo(0); // failing (count2 is equal to 1)
    }

I think the corresponding mongo query should be

    createdAt: {
        $gte: ISODate(date1),
        $lt: ISODate(date2)
    }

The actual mongo query created by panache is:

{
    'createdAt':{'$gt':ISODate('2020-09-14T18:49:36.897Z')},
    'createdAt':{'$lt':ISODate('2020-09-14T18:51:36.897Z')}
}

[quarkusbot]

/cc @loicmathieu, @evanchooly
/cc @FroMage, @loicmathieu

[rmanibus]

    public String visitAndPredicate(HqlParser.AndPredicateContext ctx) {
        StringBuilder sb = new StringBuilder();
        for (HqlParser.PredicateContext predicate : ctx.predicate()) {
            if (sb.length() > 0)
                sb.append(",");
            sb.append(predicate.accept(this));
        }
        return sb.toString();
    }

can we do something in this method to group by field names ?

[rmanibus]

Also the visitor method

    @Override
    public String visitLessThanOrEqualPredicate(HqlParser.LessThanOrEqualPredicateContext ctx) {
        return ctx.expression(0).accept(this) + ":{'$lte':" + ctx.expression(1).accept(this) + "}";
    }

cannot be used as it is because in that case we should not include the "{ }"

[loicmathieu]

@rmanibus I need to better understand the differencies between the two queries as the two forms of queries should be equivalent.

Can you provide a reproducer somewhere with the failing test to speed up my analysis ?

[rmanibus]

of course:
https://github.com/rmanibus/quarkus_12086

[rmanibus]

source for my expected query:
https://stackoverflow.com/questions/2943222/find-objects-between-two-dates-mongodb
https://www.tutorialspoint.com/find-objects-between-two-dates-in-mongodb

[loicmathieu]

@rmanibus so there is an issue, a query must be a valid json document, so if you use multiple condition on a query it must be defined as a sub document (and not defined as multiple times the same field as it is done today).

Today we use a string based query generator, we may need to revisit this to fix this issue.

To workaround this, you can use a native query untill we provide a proper fix:

View.count("{'createdAt': {'$gt':?1, '$lt': ?2}}", now.minusMinutes(2), now);

[rmanibus]

Yes thanks I already used this workaround on my side !

[rmanibus]

As the fix look's heavy and the problem can be avoided with a native query, maybe a warning in the doc could help !

[loicmathieu]

Yeah, I was thinking updating the document with a warning and a workaround and keeping this one openned to track this issue.