[
https://jira.nuxeo.org/browse/NXP-5495?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=80017#action_80017
]
Florent Guillaume commented on NXP-5495:
----------------------------------------
This is a bizarre restriction of SQL Server: a column alias that is defined in
the SELECT list cannot be used to specify a GROUP BY column
(http://msdn.microsoft.com/en-us/library/ms177673.aspx). The same thing happens
with a HAVING clause.
So the real column name must be used:
SELECT [tag].[label] AS [_C1], COUNT(DISTINCT [relation].[source]) AS [_C2]
FROM [hierarchy]
LEFT JOIN [relation] ON [hierarchy].[id] = [relation].[id]
LEFT JOIN [tag] ON [relation].[target] = [tag].[id]
WHERE [hierarchy].[primarytype] IN ('Tagging')
GROUP BY [tag].[label]
> SQL Server fails to navigate by tags
> ------------------------------------
>
> Key: NXP-5495
> URL: https://jira.nuxeo.org/browse/NXP-5495
> Project: Nuxeo Enterprise Platform
> Issue Type: Bug
> Components: Core SQL Storage, Tagging
> Affects Versions: 5.3.2
> Reporter: Florent Guillaume
> Assignee: Florent Guillaume
> Priority: Major
> Fix For: 5.4
>
>
> When navigating by tag using SQL Server, we get this error:
> TRACE [JDBCMapperLogger] (3) SQL: SELECT [tag].[label] AS [_C1],
> COUNT(DISTINCT [relation].[source]) AS [_C2] FROM [hierarchy] LEFT JOIN
> [relation] ON [hierarchy].[id] = [relation].[id] LEFT JOIN [tag] ON
> [relation].[target] = [tag].[id] WHERE [hierarchy].[primarytype] IN
> ('Tagging') GROUP BY [_C1]
> ERROR [viewhandler] ... Invalid query: NXTAG: COUNTSOURCE: SELECT
> tag:label, relation:source FROM Tagging
> Caused by: java.sql.SQLException. message: Invalid column name '_C1'.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
https://jira.nuxeo.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
_______________________________________________
ECM-tickets mailing list
[email protected]
http://lists.nuxeo.com/mailman/listinfo/ecm-tickets
