Duplication when adding fetched rows from DB in List

Question

There is this weird behavior that's been happening in my code that I can't seem to get my head into.

I have a query that fetches all tones from the DB. I tested it using pgAdmin4 and it's returning the rows accordingly with no duplicate records whatsoever. I even tried to debug within the DTO level to see if All records are being set right and everything seems fine as well. Only when the code reaches the implementation is where I start seeing duplicates.

I'll give a more sorrow example:

Query returns 4 records with albumIds: 78, 109, 113, and null. The query:

@Query(value = "SELECT new com.example.package.Search.DTO.ToneJPQL(ri, ri.toneCode, ar.artistName, a.albumId, w.id)" +
        " FROM ToneEntity ri " +
        " INNER JOIN ArtistEntity ar " +
        "   ON ar.artistId = ri.artistId " +
        " LEFT JOIN AlbumToneEntity at " +
        "   ON at.toneId = ri.toneId " +
        " LEFT JOIN AlbumEntity a " +
        "   ON a.albumId = at.albumId " +
        " left join RbtCoreToneEntity rbt on rbt.toneId = ri.toneId " +
        " left join WishlistEntity w on w.cmsToneId = ri.toneId and w.subscriberId = :subscriberId " +
        " WHERE ( soundex(ri.description) = soundex(:query) OR LOWER(ri.description) LIKE CONCAT('%', LOWER(:query), '%') OR LOWER(ri.searchKeywords) in (LOWER(:query)) ) AND ri.isPublished = TRUE AND ri.isActive = TRUE ")

ToneJPQL DTO:

     @Data
     public class ToneJPQL {
         private ToneEntity tone;
         @JsonIgnore
         private String cmsToneCode;
         @JsonIgnore
         private String artistName;
         @JsonIgnore
         private Long albumId;
         private Integer totalPages;
         private Integer totalElements;
        
         public ToneJPQL(ToneEntity tone, String cmsToneCode, String artistName, Long albumId, Long wishlistId) {
             this.tone = tone;
             this.tone.setCmsToneCode(cmsToneCode);
             this.tone.setAlbumId(albumId); // it shows here as well: 78, 109, 113
             this.tone.setArtistName(artistName);
        }
    }

The implementation. Here is where everything goes wrong:

    List tones = toneRepository.findAllByLikeSub((subscriber != null ? subscriber.getSubscriberId() : 0), payload.getQuery());

If I debug the following snippet, it returns the following records with the following albumId: 113, 113, 113, null

Duplication when adding fetched rows from DB in List

0 Answers