Wrong query generated when an EmbeddedId is used in a WHERE clause using the IN operator

[dreab8]

Given

@Entity(name = "Delivery")
	@Table(name = "Delivery")
	public static class Delivery {

	@EmbeddedId
	private LocationId locationId;
}

@Embeddable
	public static class LocationId {

		@Column(name = "sp_country")
		private String country;

		@Column(name = "sp_city")
		private String city;
}

the following

s.createQuery( "from Delivery d where d.locationId in ?1" ).setParameter( 1, new LocationId( "Italy", "Verbania" ) ).getResultList()

generates the wtong sql query

select d1_0.sp_city,d1_0.sp_country,d1_0.field from Delivery d1_0 where (d1_0.sp_city,d1_0.sp_country) in (($1,$1))

the in operator values (($1,$1)) should be in (($1,$2))

[dreab8]

Caused by https://hibernate.atlassian.net/browse/HHH-19792

[gavinking]

Actually the query looks wrong though:

s.createQuery( "from Delivery d where d.locationId in ?1" )
    .setParameter( 1, new LocationId( "Italy", "Verbania" ) )
    .getResultList()

Shouldn't it be either this:

s.createQuery( "from Delivery d where d.locationId in ?1" )
    .setParameter( 1, List.of(new LocationId( "Italy", "Verbania" )) )
    .getResultList()

or this:

s.createQuery( "from Delivery d where d.locationId in (?1)" )
    .setParameter( 1, new LocationId( "Italy", "Verbania" ) )
    .getResultList()

[dreab8]

@gavinking tried both versions you proposed and the executed query is still

select d1_0.sp_city,d1_0.sp_country,d1_0.field from Delivery d1_0 where (d1_0.sp_city,d1_0.sp_country) in (($1,$1))

[gavinking]

Yeah, fine, more just pointing out that the tests should really be for those variations.

[dreab8]

I think I spotted the cause of the issue and also a fix 🤞

[dreab8]

Thanks @gavinking ,
I have changed the reproducer to use only the 2 variations you suggested.

[dreab8]

hibernate/hibernate-orm#10973

[gavinking]

Great, perfect, cheers.