I've been using the SQLServerDialect, but would prefer to use the 2005 and 2008 versions when appropriate, however, I am having problems in the following scenario:
1. I have an entity that contains a Formula mapping for example:
Code:
@Formula(" ( SELECT count(lnk.TASK_ID) FROM TASK_TO_TASK_LINK lnk WHERE lnk.ANTECEDENT_TASK_ID = TASK_ID )")
private int dependentCount;
2. I query this entity in HQL using setMaxResults:
Code:
Query q = getEntityManager().createQuery(" from Task where task = :task and completedDate is null order by plannedStartDate DESC ");
q.setParameter("task", task);
q.setMaxResults(10);
return (TaskHistory) getSingleResultOrNull(q);
3. The generated SQL fails with:
Quote:
Msg 116, Level 16, State 1, Line 17
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Code:
WITH query AS (select
taskhistor0_.tracker_task_history_id as tracker1_56_,
taskhistor0_.actual_start_date as actual2_56_,
taskhistor0_.completed_userid as completed16_56_,
taskhistor0_.completed_date as completed4_56_,
taskhistor0_.planned_end_date as planned9_56_,
taskhistor0_.planned_start_date as planned10_56_,
taskhistor0_.reviewed_userid as reviewed19_56_,
taskhistor0_.reviewed_date as reviewed11_56_,
taskhistor0_.started_userid as started20_56_,
taskhistor0_.status as status56_,
taskhistor0_.tracker_task_id as tracker21_56_,
( select
count(lnk.tracker_task_history_id),
ROW_NUMBER() OVER (
order by
taskhistor0_.planned_start_date desc) as __hibernate_row_nr__
from
tracker_hstry_to_hstry_link lnk
where
lnk.antecedent_task_hstry_id = taskhistor0_.tracker_task_history_id ) as formula12_
from
tracker_task_history taskhistor0_
where
taskhistor0_.tracker_task_id > 1
and (taskhistor0_.completed_date is null) ) SELECT
*
FROM
query
WHERE
__hibernate_row_nr__ >= 1
AND __hibernate_row_nr__ < 1000
4. The problem seems to be that the ROW_NUMBER() is inserted after my formula SELECT. If I rewrite the query as follows it works:
Code:
WITH query AS (select
taskhistor0_.tracker_task_history_id as tracker1_56_,
taskhistor0_.actual_start_date as actual2_56_,
taskhistor0_.completed_userid as completed16_56_,
taskhistor0_.completed_date as completed4_56_,
taskhistor0_.tracker_group_id as tracker18_56_,
taskhistor0_.planned_end_date as planned9_56_,
taskhistor0_.planned_start_date as planned10_56_,
taskhistor0_.reviewed_userid as reviewed19_56_,
taskhistor0_.reviewed_date as reviewed11_56_,
taskhistor0_.started_userid as started20_56_,
taskhistor0_.status as status56_,
taskhistor0_.tracker_task_id as tracker21_56_,
( select
count(lnk.tracker_task_history_id)
from
tracker_hstry_to_hstry_link lnk
where
lnk.antecedent_task_hstry_id = taskhistor0_.tracker_task_history_id ) as formula12_ ,
ROW_NUMBER() OVER (
order by
taskhistor0_.planned_start_date desc) as __hibernate_row_nr__
from
tracker_task_history taskhistor0_
where
taskhistor0_.tracker_task_id > 1
and (taskhistor0_.completed_date is null) ) SELECT
*
FROM
query
WHERE
__hibernate_row_nr__ >= 1
AND __hibernate_row_nr__ < 1000
I haven't been able to find anything in the forums or JIRA about this, so I am wondering if it is a known issue or if there is a work-around or perhaps something in my configuration. The issue is that I would like to be able to "auto-detect" the dialect for other reasons, but when auto-detect finds sqlserver 2005/2008 I run into this problem. Unfortunately, its a large system that uses @Formula a fair amount as well as setMaxResults. I can't really avoid this combination.
Login
FAQ
Search
