Spring Boot+PostgreSQL嵌套EXCEPT查询失效，返回全部结果求助

环境 #

• Spring Boot v2.5.5
• PostgreSQL 10

问题代码 #

String query = "select d.*
                from (
                  ( select a.*
                    from table1 a )            --subquery1
                EXCEPT
                  ( select c.*                 --subquery2                
                    from table2 b, table1 c
                    where b.table1_id = c.id AND b.h_id = 1 
                        AND b.h_id = c.h_id 
                        AND ( tstzrange(  b.start_date , b.end_date , 
                              '[]')  
                        && tstzrange('2023-04-16 10:42:00.0','2023-04-16 14:42:00.0', '[]' )) 
                  ) 
                ) d";

List<D> dlist = new ArrayList<>();
dlist = entityManager.createNativeQuery(query, D.class).getResultList();

现象 #

• subquery1返回6条记录，subquery2返回1条记录
• 在PGAdmin中执行该查询，EXCEPT正常工作，返回5条记录
• 在Spring Boot中执行时，EXCEPT未生效，返回subquery1的全部6条记录

排查与解决方案 #

1. 优先修正SQL转义错误 #

原代码中&&是HTML转义后的符号，在Java字符串中需要直接使用PostgreSQL的交集操作符&&。如果保留&&，Hibernate会将其作为普通字符串传递给数据库，导致subquery2的时间范围交集条件失效，无法匹配到任何记录，最终EXCEPT相当于减去空集，返回全部6条数据。

修正后的SQL字符串：

String query = "select d.*\n" +
               "from (\n" +
               "  ( select a.*\n" +
               "    from table1 a )            --subquery1\n" +
               "EXCEPT\n" +
               "  ( select c.*                 --subquery2                \n" +
               "    from table2 b, table1 c\n" +
               "    where b.table1_id = c.id AND b.h_id = 1 \n" +
               "        AND b.h_id = c.h_id \n" +
               "        AND ( tstzrange(b.start_date, b.end_date, '[]')\n" +
               "        && tstzrange('2023-04-16 10:42:00.0','2023-04-16 14:42:00.0', '[]') )\n" +
               "  )\n" +
               ") d";

2. 验证Hibernate实际执行的SQL #

开启Hibernate SQL日志，对比实际发送到数据库的SQL与PGAdmin中执行的SQL是否一致：
在application.properties中添加日志配置：

logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE

检查日志中的SQL是否存在语法错误、参数替换异常等问题。

3. 确认实体类映射准确性 #

使用createNativeQuery(query, D.class)时，Hibernate需要将查询结果完整映射到实体类D。如果实体类字段与table1列的映射（包括数据类型、列名）存在偏差，会导致EXCEPT依赖的"全列完全匹配"规则失效。确保D类的所有字段都与table1的列一一对应，比如时间类型的精度、ID类型的匹配。

4. 明确指定查询列避免隐式问题 #

EXCEPT依赖结果集的列顺序和值完全匹配，建议在subquery1和subquery2中明确指定列名，而非使用a.*或c.*，避免因列顺序、隐式类型转换导致的匹配失败。

总结 #

大概率是SQL字符串中的转义错误导致条件失效，优先修正&&为&&，再通过日志验证执行的SQL是否符合预期。

内容的提问来源于stack exchange，提问作者acdc71 Liverpool