JPQL视为/左外部联接

Question

我正在尝试JPA2.1(eclipselink)中的“treat as”函数，但遇到了一个来自JPA的错误：

异常说明：ReportQuery结果大小不匹配。应为[263]，但已检索到[197]

下面是我的JPQL查询（我更改了一些部分，以便更明确）：

String jpql = "select distinct s, accountAD "
            + "from SheetAccountUser s "
            + "left join fetch s.userTarget target "
            + "left join TREAT(target.accounts AS ADAccount) accountAD ";

SELECT DISTINCT 
t0.Id, --etc
t6.Id, t6.name, --etc
t7.userId --etc
FROM sheet t0 
LEFT OUTER JOIN user t6 ON (t6.Id = t0.userTargetId),
account t7 LEFT OUTER JOIN ad_account t8 ON ((t8.userId = t7.userId) AND (t8.idApp = t7.idApp))
WHERE (t7.userId = t6.Id) AND (t7.DTYPE = 'ADAccount');

我们可以看到account和ad_account之间的左外部连接。此外，ad_account表不存在于select子句中。（idApp字段是主键的一部分，并维护unique(userId，idApp)约束）。我不知道是我的理解还是JPA的问题。

谢谢你帮我！

Answer 1

 "select distinct s, accountAD "
                + "from SheetAccountUser s "
                + "left join fetch s.userTarget target "
                + "left join target.accounts accountAD where TYPE(accountAD) = ADAccount";

此请求不返回有目标但没有AD帐户的表单。

 "select distinct s, accountAD "
                + "from SheetAccountUser s "
                + "left join fetch s.userTarget target "
                + "left join target.accounts accounts "
                + " join TREAT(accounts AS ADAccount) accountAD";

这将生成与我的第一个jpql请求相同的SQL，并生成Eclipselink错误。

如果目标有一个AD帐户和至少一个其他帐户类型，我还为一个工作表提供多行：一行设置了AD帐户属性，其他行设置了null值（这些不同的值阻塞distinct子句，DTYPE值也阻塞了）。

    "select u, "
                // 0 if line with no target or no ADAccount or with another account type, else 1 (one 1 by sheet/target) 
                + "sum( "
                + "     case "
                + "     when accountAD.desactivated is not null then 1 "
                + "     else 0 "
                + "     end "
                + ") as ADAccountExists, "
                // the target have an AD Account desactivated
                + "sum( " 
                + "     case "
                + "     when compteAD.desactivated = 1 then 1 " 
                + "     else 0 "
                + "     end" 
                + ") as ADAccountDesactivated " 
                + "from SheetAccountUser s "
                + "left join fetch s.userTarget target "
                + "left join treat(target.accounts as ADAccount) accountAD "
                + "group by s,target " //the group by maintains unicity of the sheets