Incorrect multiple join query when selecting by entity identity

[Paullo612]

Expected Behavior

Correct SQL query should be built for select query with multiple joins with entity identity constriant.

Actual Behaviour

SQL query with incorrect where clause is built. Query uses root table alias instead of join alias as property prefix.

Steps To Reproduce

Add the following test to data-processor module:

    void "test multiple join query by identity"() {
        given:
            def repository = buildRepository('test.CitiesRepository', """
import io.micronaut.data.jdbc.annotation.JdbcRepository;
import io.micronaut.data.model.query.builder.sql.Dialect;
import io.micronaut.data.tck.entities.City;
import java.util.UUID;

@JdbcRepository(dialect= Dialect.MYSQL)
@io.micronaut.context.annotation.Executable
interface CitiesRepository extends CrudRepository<City, Long> {

    @Join("countryRegion")
    @Join("countryRegion.country")
    int countDistinctByCountryRegionCountryUuid(UUID id);
}
""")

            def query = getQuery(repository.getRequiredMethod("countDistinctByCountryRegionCountryUuid", UUID))

        expect:
            query == 'SELECT COUNT(*) FROM `T_CITY` city_ INNER JOIN `CountryRegion` city_country_region_ ON city_.`country_region_id`=city_country_region_.`id` INNER JOIN `country` city_country_region_country_ ON city_country_region_.`countryId`=city_country_region_country_.`uuid` WHERE (city_country_region_country_.`uuid` = ?)'

    }

This test is basically is the same as

micronaut-data/data-processor/src/test/groovy/io/micronaut/data/processor/sql/BuildQuerySpec.groovy

Lines 253 to 273 in 60246c7

	void "test multiple join query"() {
	given:
	def repository = buildRepository('test.MealRepository', """
	import io.micronaut.data.jdbc.annotation.JdbcRepository;
	import io.micronaut.data.model.query.builder.sql.Dialect;
	import io.micronaut.data.tck.entities.Meal;
	import java.util.UUID;
	@JdbcRepository(dialect= Dialect.MYSQL)
	@io.micronaut.context.annotation.Executable
	interface MealRepository extends CrudRepository<Meal, Long> {
	int countDistinctByFoodsAlternativeMealCurrentBloodGlucoseInList(List<Integer> list);
	}
	""")
	def query = getQuery(repository.getRequiredMethod("countDistinctByFoodsAlternativeMealCurrentBloodGlucoseInList", List))
	expect:
	query == 'SELECT COUNT(*) FROM `meal` meal_ INNER JOIN `food` meal_foods_ ON meal_.`mid`=meal_foods_.`fk_meal_id` INNER JOIN `meal` meal_foods_alternative_meal_ ON meal_foods_.`fk_alt_meal`=meal_foods_alternative_meal_.`mid` WHERE (meal_foods_alternative_meal_.`current_blood_glucose` IN (?))'
	}

but queries by entity identifier.

Looks like the bug is here. Join alias is really needed if entity id is not a part of first relation (or maybe in some other cases).

Environment Information

• Operation System: Ubuntu 20.04
• JDK Version: 11.0.13+8-Ubuntu-0ubuntu1.20.04

Example Application

No response

Version

3.2.2