I'm encountering a strange issue where a native query in Spring Data JPA returns 0 results even though the same SQL query returns records when executed directly in the database.

The Problem

// This returns 0
List allByFuelRecordId = dgReadingService.findAllByFuelRecordId(785L);
System.out.println("The size: "+allByFuelRecordId.size()); // Prints 0

// This returns all 777 records
List allByFuelRecordId2 = dgReadingService.findAll();
System.out.println("The size 2: "+allByFuelRecordId2.size()); // Prints 777

Code Structure

Repository Layer

@Query(value = "SELECT * FROM ritam_site_fuel_dg_reading where fuel_record_id = :fuelRecordId", nativeQuery = true)
List findAllByFuelRecordId(Long fuelRecordId);

@Query(value = "SELECT * FROM ritam_site_fuel_dg_reading ", nativeQuery = true)
List findAllRecs();

Service Layer

public List findAll() {
    List list = dgReadingRepo.findAllRecs();
    if(list.size() > 0){
        return list;
    } else {
        throw new CustomErrorException(HttpStatus.NOT_FOUND, "Previous DG details for record missing - ");
    }
}

public List findAllByFuelRecordId(Long fuelRecordId) {
    List list = dgReadingRepo.findAllByFuelRecordId(fuelRecordId);
    if(list.size() > 0){
        return list;
    } else {
        throw new CustomErrorException(HttpStatus.NOT_FOUND, "Previous DG details for record missing - " + fuelRecordId);
    }
}

Entity Mapping

@Entity
@Table(name = "ritam_site_fuel_dg_reading")
public class DgReading {
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    @JsonIgnore
    @ToString.Exclude
    @NotNull(message = "Fuel record is required")
    @ManyToOne(fetch = FetchType.LAZY, optional = false)
    @JoinColumn(name = "fuel_record_id", nullable = false)
    private FuelRecord fuelRecord;
    
    // other fields...
}

Database Details

• Column type: fuel_record_id is BIGINT in MySQL database
• Data exists: Value 785 exists and has two records with this fuel_record_id
• Records age: The records were saved two days ago (so it's not a transaction/flush issue)

What Works

Direct SQL query in database client returns 2 records:

SELECT * FROM ritam_site_fuel_dg_reading WHERE fuel_record_id = 785;

What I've Tried (None worked)

1. Added @Param annotation

@Query(value = "SELECT * FROM ritam_site_fuel_dg_reading where fuel_record_id = :fuelRecordId", nativeQuery = true)
List findAllByFuelRecordId(@Param("fuelRecordId") Long fuelRecordId);

2. Used JPQL instead of native query

@Query("SELECT d FROM DgReading d WHERE d.fuelRecord.fuelRecordId = :fuelRecordId")
List findAllByFuelRecordId(@Param("fuelRecordId") Long fuelRecordId);

3. Added entityManager flush/clear

entityManager.flush();
entityManager.clear();

4. Added direct field mapping

@Column(name = "fuel_record_id", insertable = false, updatable = false)
private Long fuelRecordIdDirect;

5. Used EntityManager directly

@Autowired
private EntityManager entityManager;

public List debugFindByFuelRecordId(Long fuelRecordId) {
    String sql = "SELECT * FROM ritam_site_fuel_dg_reading WHERE fuel_record_id = ?";
    Query query = entityManager.createNativeQuery(sql, DgReading.class);
    query.setParameter(1, fuelRecordId);
    return query.getResultList();
}

6. Tried different parameter types

// As String
query.setParameter(1, "785");

// As Integer
query.setParameter(1, 785);

7. Checked COUNT

String countSql = "SELECT COUNT(*) FROM ritam_site_fuel_dg_reading WHERE fuel_record_id = ?";
Query countQuery = entityManager.createNativeQuery(countSql);
countQuery.setParameter(1, 785L);
Long count = ((Number) countQuery.getSingleResult()).longValue(); // Also returns 0

Environment

• Spring Boot: 2.x.x
• Spring Data JPA: 2.x.x
• Database: MySQL 8.x
• Java: 11/17

Question

Why does the native query with WHERE clause return 0 results when the same SQL works directly in the database? What could be causing this discrepancy and how can I fix it?

The findAll() method works perfectly and returns all 777 records, but any query filtering by fuel_record_id returns 0 results, even though the column exists and contains the values I'm searching for.