Hibernate传null报错、传空集合替换为null的原因咨询

原始SQL查询 #

SELECT
    DISTINCT c."data"->'property_data'->>'name' AS propertyName
    FROM contract.contract c LEFT JOIN other_property_owner opo ON c.contract_id = opo.c_id 
    WHERE c.organization_id = :orgId AND c.sublease_type = 'MAIN_CONTRACT'
    AND (:ownerIds IS NULL OR c.main_property_owner_id IN (:ownerIds) OR opo.po_id IN (:ownerIds))
    AND (:contractKey IS NULL OR c."key" = CAST(:contractKey as TEXT))
    AND (:propertyName IS NULL OR c."data"->'property_data'->>'name' ILIKE CONCAT('%', :propertyName ,'%'))
    AND (:tenantName IS NULL OR c."data"->'main_tenant_details'->>'full_name' = CAST(:tenantName AS TEXT))

传入ownerIds = null时的报错 #

Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: bigint = bytea
  Hint: No operator matches the given name and argument types. You might need to add explicit type casts.
  Position: 283

传入ownerIds = null时生成的SQL日志 #

SELECT
    DISTINCT c."data"->'property_data'->>'name' AS propertyName
    FROM contract.contract c LEFT JOIN other_property_owner opo ON c.contract_id = opo.c_id
    WHERE c.organization_id = ? AND c.sublease_type = 'MAIN_CONTRACT'
    AND (? IS NULL OR c.main_property_owner_id IN (?) OR opo.po_id IN (?))
    AND (? IS NULL OR c."key" = CAST(? as TEXT))
    AND (? IS NULL OR c."data"->'property_data'->>'name' ILIKE CONCAT('%', ? ,'%'))
    AND (? IS NULL OR c."data"->'main_tenant_details'->>'full_name' = CAST(? AS TEXT))

传入空集合作为ownerIds时生成的SQL日志 #

SELECT
    DISTINCT c."data"->'property_data'->>'name' AS propertyName
    FROM contract.contract c LEFT JOIN other_property_owner opo ON c.contract_id = opo.c_id
    WHERE c.organization_id = ? AND c.sublease_type = 'MAIN_CONTRACT'
    AND (null IS NULL OR c.main_property_owner_id IN (null) OR opo.po_id IN (null))
    AND (? IS NULL OR c."key" = CAST(? as TEXT))
    AND (? IS NULL OR c."data"->'property_data'->>'name' ILIKE CONCAT('%', ? ,'%'))
    AND (? IS NULL OR c."data"->'main_tenant_details'->>'full_name' = CAST(? AS TEXT))

1. Hibernate对null参数和空集合的处理逻辑差异 #

• 当传入null作为参数时，Hibernate遵循JDBC预编译SQL的标准规则，将null替换为占位符?。但PostgreSQL无法自动推断IN (?)中null参数的类型，会默认将其识别为bytea类型，而main_property_owner_id是bigint类型，两者类型不匹配，触发运算符不存在的错误。
• 当传入空集合时，Hibernate会触发特殊处理逻辑：直接将IN (:ownerIds)替换为IN (null)，而不是使用占位符。这是Hibernate为了兼容空集合查询场景的内部处理方式。

2. 为什么:ownerIds IS NULL条件未生效？ #

虽然:ownerIds IS NULL在逻辑上会让整个OR条件结果为true，但数据库的SQL解析机制会先处理所有条件分支，不会因为前面的条件为true就跳过后续分支的类型校验。也就是说，即使:ownerIds IS NULL成立，数据库仍然会尝试解析IN (?)部分的参数类型，而该参数的类型不匹配问题直接触发了报错，导致整个查询失败。

内容的提问来源于stack exchange，提问作者Vitaly Chura