[
https://jira.nuxeo.com/browse/NXP-8001?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Florent Guillaume updated NXP-8001:
-----------------------------------
Description:
Cannot execute queries with constraints on complex types and with an ordering
clause. The generated SQL statements is not correct. The ordering field is not
included in the output fields, but it's required (on PostgreSQL at least) when
there's a {{DISTINCT}}.
{code}
SELECT * from Document where ecm:isProxy = 0 and content/name = 'testfile.txt'
ORDER BY dc:title
{code}
{noformat}
SELECT DISTINCT "HIERARCHY"."ID" AS "_C1"
FROM "HIERARCHY"
JOIN "HIERARCHY" "_H1" ON "HIERARCHY"."ID" = "_H1"."PARENTID"
LEFT JOIN "CONTENT" "_F1" ON "_H1"."ID" = "_F1"."ID"
LEFT JOIN "DUBLINCORE" "_F2" ON "HIERARCHY"."ID" = "_F2"."ID"
WHERE "HIERARCHY"."PRIMARYTYPE" IN ('Document', '...')
AND "_F1"."NAME" = 'testfile.txt'
AND "_H1"."NAME" = 'content'
ORDER BY "_F2"."TITLE"
{noformat}
{noformat}
ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list
{noformat}
was:
Cannot execute queries with constraints on complex types and with an ordering
clause. The generated SQL statements is not correct. The ordering field is not
included in the output fields.
{code}
SELECT * from Document where ecm:isProxy = 0 and content/name = 'testfile.txt'
ORDER BY dc:title
{code}
{noformat}
SELECT DISTINCT "HIERARCHY"."ID" AS "_C1"
FROM "HIERARCHY"
JOIN "HIERARCHY" "_H1" ON "HIERARCHY"."ID" = "_H1"."PARENTID"
LEFT JOIN "CONTENT" "_F1" ON "_H1"."ID" = "_F1"."ID"
LEFT JOIN "DUBLINCORE" "_F2" ON "HIERARCHY"."ID" = "_F2"."ID"
WHERE "HIERARCHY"."PRIMARYTYPE" IN ('Document', '...')
AND "_F1"."NAME" = 'testfile.txt'
AND "_H1"."NAME" = 'content'
ORDER BY "_F2"."TITLE"
{noformat}
{noformat}
ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list
{noformat}
> querying for complex types and ordering is failing
> --------------------------------------------------
>
> Key: NXP-8001
> URL: https://jira.nuxeo.com/browse/NXP-8001
> Project: Nuxeo Enterprise Platform
> Issue Type: Bug
> Affects Versions: 5.5-SNAPSHOT
> Reporter: Stéphane Lacoin
> Assignee: Florent Guillaume
> Priority: Major
> Fix For: 5.5
>
>
> Cannot execute queries with constraints on complex types and with an ordering
> clause. The generated SQL statements is not correct. The ordering field is
> not included in the output fields, but it's required (on PostgreSQL at least)
> when there's a {{DISTINCT}}.
> {code}
> SELECT * from Document where ecm:isProxy = 0 and content/name =
> 'testfile.txt' ORDER BY dc:title
> {code}
> {noformat}
> SELECT DISTINCT "HIERARCHY"."ID" AS "_C1"
> FROM "HIERARCHY"
> JOIN "HIERARCHY" "_H1" ON "HIERARCHY"."ID" = "_H1"."PARENTID"
> LEFT JOIN "CONTENT" "_F1" ON "_H1"."ID" = "_F1"."ID"
> LEFT JOIN "DUBLINCORE" "_F2" ON "HIERARCHY"."ID" = "_F2"."ID"
> WHERE "HIERARCHY"."PRIMARYTYPE" IN ('Document', '...')
> AND "_F1"."NAME" = 'testfile.txt'
> AND "_H1"."NAME" = 'content'
> ORDER BY "_F2"."TITLE"
> {noformat}
> {noformat}
> ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list
> {noformat}
--
This message is automatically generated by JIRA.
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
