'Results of group by null values are lost

I have table which has productId and productVariantId columns.

|id   |userId|productId|productVariantId|
----------------------------------------
|1    |1     |1        |1               |
|2    |1     |1        |2               |
|3    |1     |2        |56              |
|4    |1     |3        |(null)          |
|5    |2     |3        |(null)          |
|6    |1     |4        |(null)          |
|7    |2     |1        |1               |
...

productId and productVariantId are related to product and productVariant tables respectively (many to one). Some products has no variants, so productVariantId might be null. I should select all distinct products with variants. Variants of products should be considered as distinct. But, my query is not selecting rows with null valued productVariantIds.

I have used group by pt.product, productVariant. This works perfectly only when productVariantId is not null.

@Query("select new com.package.name.dto.ProductTG(pt.product, pt.productVariant, concat(:language))" +
        "from ProductT pt " +
        "where :language=:language " +
        "and (:productName = 'ALL' " +
        "or (upper(pt.product.nameEng) like upper(concat('%',:productName, '%'))) " +
        "or (upper(pt.product.nameRus) like upper(concat('%',:productName, '%')))) " +
        "group by pt.product, pt.productVariant")
    List<ProductTG> findAllProductTs(@Param("productName") String productName, @Param("language") String language);

This query selects

-------------------------------
|id|productId|productVariantId|
-------------------------------
|1 |1        |1               |
|2 |1        |2               |
|3 |2        |56              |
-------------------------------

However, I am expecting this one:

-------------------------------
|id|productId|productVariantId|
-------------------------------
|1 |1        |1               |
|2 |1        |2               |
|3 |2        |56              |
|4 |3        |(null)          |
|6 |4        |(null)          |
-------------------------------


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source