Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp
Hibernate version: 3.0.5
Name and version of the database you are using: DB2 v8.2
The generated SQL (show_sql=true):
Hibernate: select year(contentite0_.PUBLISHDATE) as col_0_0_, month(contentite0_.PUBLISHDATE) as col_1_0_, count(contentite0_.PUBLISHDATE) as col_2_0_ from KC.CONTENTITEM contentite0_ group by year(contentite0_.PUBLISHDATE) , month(contentite0_.PUBLISHDATE)
I am a little unsure regarding the subselect within the select clause. Is this supposed to be supported in hibernate3.0.5?
I am attempting to return four columns from a table containing document details. The first column is the year the document was published, the second column is the month it was published, the third column is the total number of documents published that month, and the fourth column is the number of those published documents which have sinced expired. This can be achieved in SQL by have a nested subselect in the select clause. The SQL would look a little like this:
select
year(publishdate) as y,
month(publishdate) as m,
count(*),
(
select
count(*)
from
contentitem b
where
b.expirydate<current timestamp
and
year(b.publishdate)=year(i.publishdate)
and
month(b.publishdate)=month(i.publishdate)
) as expired
from
contentitem i
group by
year(publishdate),
month(publishdate);
When applying this same method in HQL, when converting to SQL, the subselect is just ignored.
From the logs, you can see that the select is definitely mapped in the HQL
18:53:28,397 DEBUG QueryTranslatorImpl:207 - parse() - HQL: select year(item.publishDate) as yearasdf ,month(item.publishDate) as monthasdf ,count(item.publishDate) ,(select count(nestedItem.id) from com.ibm.swg.kc.hibernate.ContentItem nestedItem where nestedItem.expiryDate<current_timestamp() and year(nestedItem.publishDate)=year(item.publishDate) and month(nestedItem.publishDate)=month(item.publishDate) ) from com.ibm.swg.kc.hibernate.ContentItem item group by year(item.publishDate),month(item.publishDate)
18:53:28,437 DEBUG AST:223 - --- HQL AST ---
\-[QUERY] 'query'
+-[SELECT_FROM] 'SELECT_FROM'
| +-[FROM] 'from'
| | \-[RANGE] 'RANGE'
| | +-[DOT] '.'
| | | +-[DOT] '.'
| | | | +-[DOT] '.'
| | | | | +-[DOT] '.'
| | | | | | +-[DOT] '.'
| | | | | | | +-[IDENT] 'com'
| | | | | | | \-[IDENT] 'ibm'
| | | | | | \-[IDENT] 'swg'
| | | | | \-[IDENT] 'kc'
| | | | \-[IDENT] 'hibernate'
| | | \-[IDENT] 'ContentItem'
| | \-[ALIAS] 'item'
| \-[SELECT] 'select'
| +-[AS] 'as'
| | +-[METHOD_CALL] '('
| | | +-[IDENT] 'year'
| | | \-[EXPR_LIST] 'exprList'
| | | \-[DOT] '.'
| | | +-[IDENT] 'item'
| | | \-[IDENT] 'publishDate'
| | \-[IDENT] 'yearasdf'
| +-[AS] 'as'
| | +-[METHOD_CALL] '('
| | | +-[IDENT] 'month'
| | | \-[EXPR_LIST] 'exprList'
| | | \-[DOT] '.'
| | | +-[IDENT] 'item'
| | | \-[IDENT] 'publishDate'
| | \-[IDENT] 'monthasdf'
| +-[COUNT] 'count'
| | \-[DOT] '.'
| | +-[IDENT] 'item'
| | \-[IDENT] 'publishDate'
| \-[QUERY] 'query'
| +-[SELECT_FROM] 'SELECT_FROM'
| | +-[FROM] 'from'
| | | \-[RANGE] 'RANGE'
| | | +-[DOT] '.'
| | | | +-[DOT] '.'
| | | | | +-[DOT] '.'
| | | | | | +-[DOT] '.'
| | | | | | | +-[DOT] '.'
| | | | | | | | +-[IDENT] 'com'
| | | | | | | | \-[IDENT] 'ibm'
| | | | | | | \-[IDENT] 'swg'
| | | | | | \-[IDENT] 'kc'
| | | | | \-[IDENT] 'hibernate'
| | | | \-[IDENT] 'ContentItem'
| | | \-[ALIAS] 'nestedItem'
| | \-[SELECT] 'select'
| | \-[COUNT] 'count'
| | \-[DOT] '.'
| | +-[IDENT] 'nestedItem'
| | \-[IDENT] 'id'
| \-[WHERE] 'where'
| \-[AND] 'and'
| +-[AND] 'and'
| | +-[LT] '<'
| | | +-[DOT] '.'
| | | | +-[IDENT] 'nestedItem'
| | | | \-[IDENT] 'expiryDate'
| | | \-[METHOD_CALL] '('
| | | +-[IDENT] 'current_timestamp'
| | | \-[EXPR_LIST] 'exprList'
| | \-[EQ] '='
| | +-[METHOD_CALL] '('
| | | +-[IDENT] 'year'
| | | \-[EXPR_LIST] 'exprList'
| | | \-[DOT] '.'
| | | +-[IDENT] 'nestedItem'
| | | \-[IDENT] 'publishDate'
| | \-[METHOD_CALL] '('
| | +-[IDENT] 'year'
| | \-[EXPR_LIST] 'exprList'
| | \-[DOT] '.'
| | +-[IDENT] 'item'
| | \-[IDENT] 'publishDate'
| \-[EQ] '='
| +-[METHOD_CALL] '('
| | +-[IDENT] 'month'
| | \-[EXPR_LIST] 'exprList'
| | \-[DOT] '.'
| | +-[IDENT] 'nestedItem'
| | \-[IDENT] 'publishDate'
| \-[METHOD_CALL] '('
| +-[IDENT] 'month'
| \-[EXPR_LIST] 'exprList'
| \-[DOT] '.'
| +-[IDENT] 'item'
| \-[IDENT] 'publishDate'
\-[GROUP] 'group'
+-[METHOD_CALL] '('
| +-[IDENT] 'year'
| \-[EXPR_LIST] 'exprList'
| \-[DOT] '.'
| +-[IDENT] 'item'
| \-[IDENT] 'publishDate'
\-[METHOD_CALL] '('
+-[IDENT] 'month'
\-[EXPR_LIST] 'exprList'
\-[DOT] '.'
+-[IDENT] 'item'
\-[IDENT] 'publishDate'
18:53:28,437 DEBUG ErrorCounter:72 - throwQueryException() : no errors
18:53:28,467 DEBUG HqlSqlBaseWalker:120 - query() << begin, level = 1
18:53:28,507 DEBUG FromElement:88 - FromClause{level=1} : com.ibm.swg.kc.hibernate.ContentItem (item) -> contentite0_
18:53:28,507 DEBUG FromReferenceNode:48 - Resolved : item -> contentite0_.IDENTITY
18:53:28,507 DEBUG DotNode:476 - getDataType() : publishDate -> org.hibernate.type.TimestampType@2db892e7
18:53:28,507 DEBUG FromReferenceNode:48 - Resolved : item.publishDate -> contentite0_.PUBLISHDATE
18:53:28,517 DEBUG FromReferenceNode:48 - Resolved : item -> contentite0_.IDENTITY
18:53:28,517 DEBUG DotNode:476 - getDataType() : publishDate -> org.hibernate.type.TimestampType@2db892e7
18:53:28,517 DEBUG FromReferenceNode:48 - Resolved : item.publishDate -> contentite0_.PUBLISHDATE
18:53:28,517 DEBUG FromReferenceNode:48 - Resolved : item -> contentite0_.IDENTITY
18:53:28,517 DEBUG DotNode:476 - getDataType() : publishDate -> org.hibernate.type.TimestampType@2db892e7
18:53:28,517 DEBUG FromReferenceNode:48 - Resolved : item.publishDate -> contentite0_.PUBLISHDATE
18:53:28,527 DEBUG FromReferenceNode:48 - Resolved : item -> contentite0_.IDENTITY
18:53:28,527 DEBUG DotNode:476 - getDataType() : publishDate -> org.hibernate.type.TimestampType@2db892e7
18:53:28,527 DEBUG FromReferenceNode:48 - Resolved : item.publishDate -> contentite0_.PUBLISHDATE
18:53:28,527 DEBUG FromReferenceNode:48 - Resolved : item -> contentite0_.IDENTITY
18:53:28,527 DEBUG DotNode:476 - getDataType() : publishDate -> org.hibernate.type.TimestampType@2db892e7
18:53:28,527 DEBUG FromReferenceNode:48 - Resolved : item.publishDate -> contentite0_.PUBLISHDATE
18:53:28,527 DEBUG HqlSqlBaseWalker:125 - query() : finishing up , level = 1
18:53:28,527 DEBUG HqlSqlWalker:331 - processQuery() : ( SELECT ( {select clause} ( ( year ( exprList ( contentite0_.PUBLISHDATE contentite0_.IDENTITY publishDate ) ) ) ( ( month ( exprList ( contentite0_.PUBLISHDATE contentite0_.IDENTITY publishDate ) ) ) ( count ( contentite0_.PUBLISHDATE contentite0_.IDENTITY publishDate ) ) ) ( FromClause{level=1} KC.CONTENTITEM contentite0_ ) ( group ( ( year ( exprList ( contentite0_.PUBLISHDATE contentite0_.IDENTITY publishDate ) ) ) ( ( month ( exprList ( contentite0_.PUBLISHDATE contentite0_.IDENTITY publishDate ) ) ) ) )
18:53:28,547 DEBUG JoinProcessor:112 - Using FROM fragment [KC.CONTENTITEM contentite0_]
18:53:28,547 DEBUG HqlSqlBaseWalker:128 - query() >> end, level = 1
but the final SQL doesn't reflect this:
Hibernate: select year(contentite0_.PUBLISHDATE) as col_0_0_, month(contentite0_.PUBLISHDATE) as col_1_0_, count(contentite0_.PUBLISHDATE) as col_2_0_ from KC.CONTENTITEM contentite0_ group by year(contentite0_.PUBLISHDATE) , month(contentite0_.PUBLISHDATE)
Any ideas?
Regards
Matt
Login
FAQ
Search