Spring JPA query null check condition is removing all rows with null values

Question

@Query("SELECT p FROM PostDAO p WHERE true OR p.user.institution IS NOT NULL") This condition should be equals to @Query("SELECT p FROM PostDAO p WHERE true")

The output should be same too, but null checking removes all rows where institution is null.

So second query should return all posts and it's working fine, the first one should do the same, but it's removing all posts where institution is null.

Maybe i got something wrong? Help me.

Tried to check for null with "p.user.institution <> null" or checking for id "p.user.institution.id = 1" So it was like this: SELECT p FROM PostDAO p WHERE true OR p.user.institution <> null and SELECT p FROM PostDAO p WHERE true OR p.user.institution.id = 1

But every post with institution = null was deleted from results.

PostDAO:

@Entity
@Table(name = "posts")
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
public class PostDAO {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    @Column(name = "date", nullable = false)
    private Date date;

    @Column(name = "expiration_date")
    private Date expirationDate;

    @Column(name = "text", columnDefinition = "TEXT")
    private String text;

    @ManyToOne
    @JoinColumn(name="user_id", nullable=false)
    private UserDAO user;

UserDAO:

@Data
@Entity
@Table(name = "users", uniqueConstraints = {
        @UniqueConstraint(columnNames = {"login"})
})
public class UserDAO {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    @Column(name = "login")
    private String login;
    @Column(name = "password")
    private String password;

    @OneToOne(cascade = CascadeType.ALL, mappedBy = "user")
    private CompanyDAO company;

    @OneToOne(cascade = CascadeType.ALL, mappedBy = "user")
    private InstitutionDAO institution;

    @ManyToMany
    @JoinTable(
            name = "users_roles",
            joinColumns = @JoinColumn(
                    name = "user_id", referencedColumnName = "id"),
            inverseJoinColumns = @JoinColumn(
                    name = "role_id", referencedColumnName = "id"))
    private Set<RoleDAO> roles;
}

Answer 1

Looks like the issue here is that "where true" isn't valid and instead it should be:

"where true = true or p.user.institution IS NOT NULL"

That should resolve your issue.

I presume "where true" is not actually being processed and so you are just being returned with all results from the query