Queryng With In Clause And Static Values

[macario1983]

Observed vs. expected behavior

Do the right query

Steps to reproduce

    private List<Long> findRealizacaoAvaliacoes(final ResetAvaliacaoCommand cmd) {
        return baseRepository.findAll(qRealizacaoAvaliacao, Projections.constructor(Long.class, qRealizacaoAvaliacao.id), where(cmd));
    }

    private BooleanBuilder where(final ResetAvaliacaoCommand cmd) {

        final BooleanBuilder builder = new BooleanBuilder();

        builder.and(Expressions.list(qRealizacaoAvaliacao.avaliacao.id, qRealizacaoAvaliacao.profissional.id)
                .in(
                        Expressions.list(Expressions.constant(cmd.getResetAvaliacoes().get(0).getAvaliacaoId()), Expressions.constant(cmd.getResetAvaliacoes().get(0).getProfissionalId())),
                        Expressions.list(Expressions.constant(cmd.getResetAvaliacoes().get(1).getAvaliacaoId()), Expressions.constant(cmd.getResetAvaliacoes().get(1).getProfissionalId()))));

        builder.and(qRealizacaoAvaliacao.reset.eq(Boolean.FALSE));

        return builder;
    }

The query output is:

select
	realizacao0_.id as col_0_0_
from
	realizacao_avaliacao realizacao0_
where
	((realizacao0_.avaliacao_id ,
	realizacao0_.profissional_id) in (? , ? , (? , ?)))
	and realizacao0_.reset =?

But the expected is

select
	realizacao0_.id as col_0_0_
from
	realizacao_avaliacao realizacao0_
where
	realizacao0_.reset = false 
	and	(realizacao0_.avaliacao_id , realizacao0_.profissional_id) in ((13 , 3), (13 , 2))

Environment

Docker And Manjaro

Querydsl version: 4.2.1

Database: Mysql

JDK: 1.8

[jwgmeligmeyling]

This is not really a bug, is just improper use of QueryDSL's internal list expressions for tuples. The template for list is simply {0}, {1} (so just comma separated arguments). I was more surprised about the rendered parentheses that do appear than the ones that do not. These are rendered because operation expressions are always wrapped in side parentheses when one of their arguments is an operation that has lower precedence.

So the parents between (realizacao0_.avaliacao_id , realizacao0_.profissional_id) are introduced because IN takes precedence over Querydsl's internal "list" operation. And the parents between the tuples are introduced because the inside and outside list expressions have the same precendence and this needs to be accounted for.

The solution is quite simply: don't use the list expression for this. This use a Template expression.

For example, the following test passes in SQLSerializerTest:

@Test
public void comparingTuples() {
    SQLSerializer serializer = new SQLSerializer(Configuration.DEFAULT);
    BooleanExpression expression = tuple(employee.firstname, employee.lastname).in(
            tuple(new Param<String>(String.class, "a"), new Param<String>(String.class, "b")),
            tuple(new Param<String>(String.class, "c"), new Param<String>(String.class, "d")));
    serializer.handle(expression);
    assertEquals("(EMPLOYEE.FIRSTNAME, EMPLOYEE.LASTNAME) in ((?, ?), (?, ?))", serializer.toString());
}

public SimpleExpression<Object> tuple(Expression<?>... args) {
    return Expressions.template(Object.class, "({0}, {1})", args);
}