OR condition doesnt work properly in JPA Query-CodePudding

i have the following entities

class ProductUnitOfMessure
@ManyToOne
@JoinColumn(name="PRODUCT_ID")
private Product product;

@ManyToOne
@JoinColumn(name="VARIANT_ID")
private Variant variant;

class Product
@OneToMany(mappedBy = "product", fetch = FetchType.LAZY)
List<Variant> variants;

class Variant
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "PRODUCT_ID")
private Product product;

Now when i run the query

select p from ProductUnitOfMeasures p where p.variant.sku=?1 or p.product.sku =?1 (sku of type String).

it return empty list, although there are some as per the below ProductUnitOfMessure table ProductUnitOfMeasures

when i only run select p from ProductUnitOfMeasures p where p.product.sku =?1 i get some values, why this is happening even though i use OR not AND ?

database is oracle

CodePudding user response：

JPA forces inner joins to be used for references when specifying '.' on a relationship by default. Inner joins automatically exclude nulls from the results - which might be inline with what you might want if you are calling ProductUnitOfMeasures.getVariant().getSku() == youValue as you wouldn't want to deal with NPEs. If you want nulls included in the results, database have what is known as outer join semantics, which allow rows with nulls to be included and still used for the rest of the filter.

Something like:

"select p from ProductUnitOfMeasures p left join p.variant variant left join p.product product where variant.sku = ?1 or product.sku = ?1"

Will allow ProductUnitOfMeasures with either null variant or null product references to still be considered, allowing your ProductUnitOfMeasures with a null variant but with a product.sku value matching your filter to still be returned.