JPA CONCATE与IN运算符一起使用时引发异常

Question

我试图运行一个复杂的JPQL查询，它不会抛出异常，而且工作起来很奇怪。

下面是我的问题

SELECT b.pk.col1, b.pk.col2
  FROM table1 b
 WHERE     b.pk.col2 IN ('someString')
       AND b.pk.col3 IN (0, 1, 2)
       AND b.pk.sdate < :now
       AND b.edate > :now
       AND b.col4 = 1
       AND EXISTS
              (SELECT a.cmid
                 FROM table2 a
                WHERE a.col1 = 5 AND a.col2 = b.pk.col1)
       AND b.pk.col1 NOT IN
              (SELECT a.pk.col1
                 FROM table3 a
                WHERE CONCAT (a.pk.tagid, 'ayush') IN ('1ayush'))

    SELECT b.pk.col1, b.pk.col2
  FROM table1 b
 WHERE     b.pk.col2 IN ('someString')
       AND b.pk.col3 IN (0, 1, 2)
       AND b.pk.sdate < :now
       AND b.edate > :now
       AND b.col4 = 1
       AND EXISTS
              (SELECT a.cmid
                 FROM table2 a
                WHERE a.col1 = 5 AND a.col2 = b.pk.col1)
       AND b.pk.col1 NOT IN
              (SELECT a.pk.col1
                 FROM table3 a
                WHERE CONCAT (a.pk.tagid, 'ayush') IN ('1ayush'))

编辑：这是我的新JPA查询，我给它一个集合，而不是一些硬编码的值

SELECT b.pk.col1, b.pk.col2
  FROM table1 b
 WHERE     b.pk.col2 IN ('someString')
       AND b.pk.col3 IN (0, 1, 2)
       AND b.pk.sdate < :now
       AND b.edate > :now
       AND b.col4 = 1
       AND EXISTS
              (SELECT a.cmid
                 FROM table2 a
                WHERE a.col1 = 5 AND a.col2 = b.pk.col1)
       AND (b.pk.col1 NOT IN
               (SELECT a.pk.col1
                  FROM table3 a
                 WHERE CONCAT (a.pk.tagid, 'ayush') IN (:somelist)))

我仍然得到同样的例外

Answer 1

“xxx IN yyy”应为“{someValue}IN{someCollection}”。右侧没有集合，因此出现异常。如果您的查询现在确实在右边有一个集合（在post中没有），并且仍然得到一个异常，那么就发布实际的异常+堆栈跟踪。如果“xxx”和“yyy”应该相等，那么就使用“=”