[FIXED] Hibernate Native SQL returns some nulls instead of partially filled objects

Issue

I have the following (postgress) SQL Query:

WITH cal AS(
  SELECT ts AS t_begin, ts + '6hours'::interval AS t_end 
  FROM generate_series('2021-05-09 00:00:00'::timestamp 
                      , '2021-06-15 09:51:34', '6hours'::interval) ts 
  ) 
  SELECT d.variable_id, cal.t_end AS date_time, d.sample_count, d.sample_period_ms, MIN(d.min_value) AS min_value, MAX(d.max_value) AS max_value, AVG(d.value) AS value 
  FROM cal 
  LEFT JOIN public.fp_data d 
      ON d.date_time >= cal.t_begin 
      AND d.date_time < cal.t_end 
      AND variable_id = 15
  GROUP BY cal.t_end, d.variable_id, d.sample_count, d.sample_period_ms 
  ORDER BY cal.t_end

And what it does is it creates a series of 6 hours per day and takes averages from it. A result may look like this:

---

variable_id	date_time	sample_count	sample_period_ms	min_value	max_value	value
15	2021-06-06 06:00:00	120	59577	-1.4960686	1.1995025	0.30439844254136744
15	2021-06-06 12:00:00	120	59577	-1.4887594	1.1997863	0.30570657099738263
15	2021-06-06 18:00:00	120	59577	-1.4972655	1.1999407	0.30465021305485984
15	2021-06-07 00:00:00	120	59577	-1.4703176	1.1985717	0.30615092198218197
15	2021-06-07 06:00:00	120	59577	-1.4983453	1.1998215	0.3049584258111712
15	2021-06-07 12:00:00	120	59577	-1.4996965	1.1996177	0.3047593224032149
15	2021-06-07 18:00:00	120	59577	-1.4949534	1.1998252	0.30591585460444787
15	2021-06-08 00:00:00	120	59577	-1.4997886	1.1995926	0.30432341914644556
15	2021-06-08 06:00:00	120	59577	-1.4956167	1.1996672	0.3085149774948756
15	2021-06-08 12:00:00	120	59577	-1.4986398	1.1998078	0.30561149754247613
15	2021-06-08 18:00:00	120	59577	-1.499255	1.1990205	0.3064040885648123
15	2021-06-09 00:00:00	120	59577	-1.4864591	1.1998134	0.3057553283664403
NULL	2021-06-09 06:00:00	NULL	NULL	NULL	NULL	NULL
NULL	2021-06-09 12:00:00	NULL	NULL	NULL	NULL	NULL
NULL	2021-06-09 18:00:00	NULL	NULL	NULL	NULL	NULL
NULL	2021-06-10 00:00:00	NULL	NULL	NULL	NULL	NULL
NULL	2021-06-10 06:00:00	NULL	NULL	NULL	NULL	NULL
NULL	2021-06-10 12:00:00	NULL	NULL	NULL	NULL	NULL
NULL	2021-06-10 18:00:00	NULL	NULL	NULL	NULL	NULL
NULL	2021-06-11 00:00:00	NULL	NULL	NULL	NULL	NULL
NULL	2021-06-11 06:00:00	NULL	NULL	NULL	NULL	NULL
NULL	2021-06-11 12:00:00	NULL	NULL	NULL	NULL	NULL
NULL	2021-06-11 18:00:00	NULL	NULL	NULL	NULL	NULL
NULL	2021-06-12 00:00:00	NULL	NULL	NULL	NULL	NULL

I have implemented this query as follows in my JPA interface:

@Transactional
public interface FloatingPointDataRepos extends CrudRepository<FloatingPointData, Integer> {
    
    List<FloatingPointData> findAllByVariable_IdAndDateTimeBetween(Integer variableId, Timestamp startTimeFrame, Timestamp endTimeFrame);

    @Query(value = "WITH cal AS( "+
                        "SELECT ts AS t_begin, ts + '6hours'\\:\\:interval AS t_end "+
                        "FROM generate_series(:startTimeFrame\\:\\:timestamp "+
                                            ", :endTimeFrame, '6hours'\\:\\:interval) ts "+
                        ") "+
                        "SELECT d.variable_id, cal.t_end AS date_time, d.sample_count, d.sample_period_ms, MIN(d.min_value) AS min_value, MAX(d.max_value) AS max_value, AVG(d.value) AS value "+
                        "FROM cal "+
                        "LEFT JOIN public.fp_data d "+
                            "ON d.date_time >= cal.t_begin "+
                            "AND d.date_time < cal.t_end "+
                            "AND variable_id = :variableId " +
                        "GROUP BY cal.t_end, d.variable_id, d.sample_count, d.sample_period_ms "+
                        "ORDER BY cal.t_end", nativeQuery = true)
    List<FloatingPointData> findAllByVariableId(Integer variableId, Timestamp startTimeFrame, Timestamp endTimeFrame);

}

The code works, but the problem is that it returns null for the rows where the variable_id and other values are not set.

A sample of what it now returns is as follows:

object
object
object
object
object
object
object
object
object
object
null
null
null
null
null
null

I want it to always return the date_time in the object, like you see in the table above.

How do I fix this?

Solution

variable_id is a primary key. When variable_id was null, Hibernate returned null instead of the object.

The fix was to change SELECT d.variable_id to SELECT :variableId AS variable_id

Answered By - Luuk Wuijster