EmbeddedId in findByIdIn does not seem to be working

[berkum]

When trying to use findByIdIn with an EmbeddedId, micronaut does not seem to understand it is dealing with an embedded id and does not generate the correct SQL.

Task List

• [ ] Steps to reproduce provided I used the micronaut source code to reproduce. There is one entity with an EmbeddableId, class Project.

I added the method signature to io.micronaut.data.tck.repositories.ProjectRepository : List<Project> findByIdIn(List<ProjectId> ids);

Then in H2CompositePrimaryKeySpec, I used the first test ("test CRUD with composite ID") and inserted my call : project = projectRepository.findById(id).orElse(null) // already in place from micronaut projectRepository.findByIdIn(Arrays.asList(id)); // This is my call

Expected Behaviour

It should have found my record but most importantly, it should not generate an error.

Actual Behaviour

SQL Error preparing Query: Column "PROJECT_.PROJECT_ID" not found; SQL statement:

If you look at the SQL generated for findById, the where clause is OK : [Test worker] DEBUG io.micronaut.data.query - Executing Query: SELECT project_.project_id_department_id,project_.project_id_project_id,project_.name,UPPER(org) AS org FROM project project_ WHERE (project_.project_id_department_id = ? AND project_.project_id_project_id = ?)

And then the generated SQL for findByIdIn (incorrect) : Executing Query: SELECT project_.project_id_department_id,project_.project_id_project_id,project_.name,UPPER(org) AS org FROM project project_ WHERE (project_.project_id IN(?))

Environment Information

• Operating System: macOS Catalina
• Micronaut Version: micronaut-data 1.0.2
• JDK Version: openjdk version "1.8.0_242"

[jaksah]

I'm experiencing the same issue, using micronaut-data 1.1.3

[jaksah]

We've managed to do a workaround using JdbcOperations. Had to add some "reactive fluff" as well to since we're using the RxJavaCrudRepository. Something in line with this:

public abstract class EntityCrudRepository implements RxJavaCrudRepository<Entity, EntityKey> {
    private final JdbcOperations jdbcOperations;
    private final ExecutorService executorService;

    protected EntityCrudRepository(
        final JdbcOperations jdbcOperations,
        @Named(TaskExecutors.IO) final ExecutorService executorService
    ) {
        this.jdbcOperations = jdbcOperations;
        this.executorService = executorService;
    }

    @Transactional
    public Observable<Entity> findByKeyUserIdIn(
        final List<String> userIds
    ) {
        return Observable.<Entity>create(emitter -> {
                try {
                    jdbcOperations.prepareStatement(
                        "SELECT * FROM entity WHERE key_user_id = ANY (?)",
                        statement -> {
                            Array array = statement.getConnection()
                                              .createArrayOf(
                                                  "varchar",
                                                  userIds.toArray(new String[0])
                                              );
                            statement.setArray(1, array);
                            return jdbcOperations.entityStream(
                                statement.executeQuery(),
                                Entity.class
                            );
                        }
                    ).forEach(emitter::onNext);
                    emitter.onComplete();
                } catch (DataAccessException e) {
                    emitter.onError(e);
                }
            }
        ).subscribeOn(Schedulers.from(executorService));
    }
}

[VishGov]

Hi All,

I am using Micronaut 4.2.1 with micronaut data and micronaut jdbc.

If I have a repository like this with CompositeKey being an EmbeddedId

@JdbcRepository(dialect = Dialect.POSTGRES)
public interface TestRepository extends CrudRepository<TestEntity, CompositeKey> {

    List<TestEntity> findByTestCompositeKeyIn(List<CompositeKey> testCompositeKeys);
}

When I am trying findByXIn queries, the List<?> is not being taken in the query and I am getting the following error

io.micronaut.data.exceptions.DataAccessException: Unable to set PreparedStatement value: Can't infer the SQL type to use for an instance of com.entity.data.CompositeKey. Use setObject() with an explicit Types value to specify the type to use.
	at io.micronaut.data.jdbc.mapper.JdbcQueryStatement.newDataAccessException(JdbcQueryStatement.java:325)
	at io.micronaut.data.jdbc.mapper.JdbcQueryStatement.setValue(JdbcQueryStatement.java:163)
	at io.micronaut.data.jdbc.mapper.JdbcQueryStatement.setValue(JdbcQueryStatement.java:37)
	at io.micronaut.data.runtime.mapper.QueryStatement.setDynamic(QueryStatement.java:250)
	at io.micronaut.data.jdbc.mapper.JdbcQueryStatement.setDynamic(JdbcQueryStatement.java:114)
	at io.micronaut.data.jdbc.mapper.JdbcQueryStatement.setDynamic(JdbcQueryStatement.java:37)
	at io.micronaut.data.runtime.operations.internal.sql.AbstractSqlRepositoryOperations.setStatementParameter

The error maybe different, but the fundamental issue of the List not being considered properly to create the correct WHERE IN query is still present. Hope a fix for this issue will be able to resolve the error I have mentioned as well.

[VishGov]

@radovanradic I see you have mentioned this ticket as part of a fix. Do you know when this is planned to be released?

[radovanradic]

No, had PR but with incorrect approach so not sure when the fix can be done.

[VishGov]

@radovanradic I was reading the PR and you mentioned the following

"Don't think it ever worked, this issue is from 2020. Tried approach 2 (that is how Hibernate SQL looks like in the end) and that was not doable with how parameter expanding is implemented."

Do you mean in micronaut code currently if a list of composite key objects are given [ {field1:B, field2:1}, {field1:C, field2:2} ]

it cannot be expanded and the below example SQL cannot be created? SELECT * FROM table1 WHERE (table1.field1, table1.field2) IN ( ('B',1), ('C',2) );

[radovanradic]

Yes, and it requires more changes to support it. Not sure also that all DBs support this IN clause with multiple columns.

[VishGov]

I believe Spring Data is already handling this case, so it would be great Micronaut Data can also support this soon.

Maybe they are expanding and handling it like this to make it generic? (Not sure) SELECT * FROM tbl1 WHERE ID1 = 1 AND ID2 = 1 OR ID1 = 1 AND ID2 = 2 OR ID1 = 3 AND ID2 = 5 OR ID1 = 2 AND ID2 = 2