Skip to content

Hibernate Native SQL returns some nulls instead of partially filled objects

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?

Answer

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