[
https://jira.nuxeo.com/browse/NXP-8723?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Florent Guillaume updated NXP-8723:
-----------------------------------
Description:
Making the following query fails:
{code}
SELECT * FROM Document WHERE files/*/filename = 'myFile.txt' ORDER BY dc:title
{code}
was:
Making the following query fails:
{code}
SELECT * FROM Document WHERE files/*/filename = 'myFile.txt' ORDER BY dc:title
{code}
Stack trace summary:
{code}
2012-01-24 19:22:58,125 WARN
[org.nuxeo.ecm.platform.query.nxql.CoreQueryDocumentPageProvider] Failed to
execute query: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in
select list
Position : 1737
org.nuxeo.ecm.core.api.ClientException: Failed to execute query: ERROR: for
SELECT DISTINCT, ORDER BY expressions must appear in select list
Position : 1737
at
org.nuxeo.ecm.core.api.AbstractSession.query(AbstractSession.java:1571)
at
org.nuxeo.ecm.core.api.AbstractSession.query(AbstractSession.java:1481)
...
Caused by: org.nuxeo.ecm.core.api.WrappedException: Exception:
org.nuxeo.ecm.core.query.QueryException. message: Invalid query: Select * from
Document where files/*/filename = 'myFile.txt' order by dc:title
at
org.nuxeo.ecm.core.storage.sql.coremodel.SQLSession$SQLSessionQuery.execute(SQLSession.java:594)
at
org.nuxeo.ecm.core.api.AbstractSession.query(AbstractSession.java:1518)
... 169 more
Caused by: org.nuxeo.ecm.core.api.WrappedException: Exception:
org.nuxeo.ecm.core.storage.StorageException. message: Invalid query: Select *
from Document where files/*/filename = 'myFile.txt' order by dc:title
at
org.nuxeo.ecm.core.storage.sql.jdbc.JDBCMapper.query(JDBCMapper.java:769)
at
org.nuxeo.ecm.core.storage.sql.CachingMapper.query(CachingMapper.java:85)
at
org.nuxeo.ecm.core.storage.sql.SessionImpl.query(SessionImpl.java:1145)
at
org.nuxeo.ecm.core.storage.sql.ra.ConnectionImpl.query(ConnectionImpl.java:335)
at
org.nuxeo.ecm.core.storage.sql.coremodel.SQLSession$SQLSessionQuery.execute(SQLSession.java:589)
... 170 more
Caused by: org.nuxeo.ecm.core.api.WrappedException: Exception:
org.postgresql.util.PSQLException. message: ERROR: for SELECT DISTINCT, ORDER
BY expressions must appear in select list
Position : 1737
at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
{code}
Attached full stack trace.
PostgreSQL stack trace summary:
{noformat}
2012-01-24 19:22:58,125 WARN
[org.nuxeo.ecm.platform.query.nxql.CoreQueryDocumentPageProvider] Failed to
execute query: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in
select list
Position : 1737
org.nuxeo.ecm.core.api.ClientException: Failed to execute query: ERROR: for
SELECT DISTINCT, ORDER BY expressions must appear in select list
Position : 1737
at
org.nuxeo.ecm.core.api.AbstractSession.query(AbstractSession.java:1571)
at
org.nuxeo.ecm.core.api.AbstractSession.query(AbstractSession.java:1481)
...
Caused by: org.nuxeo.ecm.core.api.WrappedException: Exception:
org.nuxeo.ecm.core.query.QueryException. message: Invalid query: Select * from
Document where files/*/filename = 'myFile.txt' order by dc:title
at
org.nuxeo.ecm.core.storage.sql.coremodel.SQLSession$SQLSessionQuery.execute(SQLSession.java:594)
at
org.nuxeo.ecm.core.api.AbstractSession.query(AbstractSession.java:1518)
... 169 more
Caused by: org.nuxeo.ecm.core.api.WrappedException: Exception:
org.nuxeo.ecm.core.storage.StorageException. message: Invalid query: Select *
from Document where files/*/filename = 'myFile.txt' order by dc:title
at
org.nuxeo.ecm.core.storage.sql.jdbc.JDBCMapper.query(JDBCMapper.java:769)
at
org.nuxeo.ecm.core.storage.sql.CachingMapper.query(CachingMapper.java:85)
at
org.nuxeo.ecm.core.storage.sql.SessionImpl.query(SessionImpl.java:1145)
at
org.nuxeo.ecm.core.storage.sql.ra.ConnectionImpl.query(ConnectionImpl.java:335)
at
org.nuxeo.ecm.core.storage.sql.coremodel.SQLSession$SQLSessionQuery.execute(SQLSession.java:589)
... 170 more
Caused by: org.nuxeo.ecm.core.api.WrappedException: Exception:
org.postgresql.util.PSQLException. message: ERROR: for SELECT DISTINCT, ORDER
BY expressions must appear in select list
Position : 1737
at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
{noformat}
Attached full PostgreSQL stack trace.
Stack trace for H2:
{noformat}
org.nuxeo.ecm.core.storage.StorageException: Invalid query: SELECT * FROM
TestDoc WHERE tst:friends/*/firstname = 'John' ORDER BY tst:title
at
org.nuxeo.ecm.core.storage.sql.jdbc.JDBCMapper.query(JDBCMapper.java:769)
at
org.nuxeo.ecm.core.storage.sql.CachingMapper.query(CachingMapper.java:85)
at
org.nuxeo.ecm.core.storage.sql.SessionImpl.query(SessionImpl.java:1138)
...
Caused by: org.h2.jdbc.JdbcSQLException: Order by expression _C3 must be in the
result list in this case; SQL statement:
SELECT DISTINCT "_C1", "_C2" FROM (SELECT "HIERARCHY"."ID" AS "_C1",
"_F1"."TITLE" AS "_C2", "_F1"."TITLE" AS "_C3" FROM "HIERARCHY" LEFT JOIN
"TESTSCHEMA" "_F1" ON "HIERARCHY"."ID" = "_F1"."ID" LEFT JOIN "HIERARCHY" "_H1"
ON "HIERARCHY"."ID" = "_H1"."PARENTID" AND "_H1"."NAME" = ? LEFT JOIN "PERSON"
"_F2" ON "_H1"."ID" = "_F2"."ID" WHERE (("HIERARCHY"."PRIMARYTYPE" IN (?, ?))
AND ("_F2"."FIRSTNAME" = ?)) UNION ALL SELECT "_H"."ID" AS "_C1", "_F1"."TITLE"
AS "_C2", "_F1"."TITLE" AS "_C3" FROM "HIERARCHY" "_H" JOIN "PROXIES" ON
"_H"."ID" = "PROXIES"."ID" JOIN "HIERARCHY" ON "PROXIES"."TARGETID" =
"HIERARCHY"."ID" LEFT JOIN "TESTSCHEMA" "_F1" ON "HIERARCHY"."ID" = "_F1"."ID"
LEFT JOIN "HIERARCHY" "_H1" ON "HIERARCHY"."ID" = "_H1"."PARENTID" AND
"_H1"."NAME" = ? LEFT JOIN "PERSON" "_F2" ON "_H1"."ID" = "_F2"."ID" WHERE
(("HIERARCHY"."PRIMARYTYPE" IN (?, ?)) AND ("_F2"."FIRSTNAME" = ?))) ORDER BY
"_C3" [90068-114]
...
at
org.nuxeo.ecm.core.storage.sql.jdbc.JDBCMapper.query(JDBCMapper.java:703)
... 22 more
{noformat}
> Querying for some complex list types and ordering is failing
> ------------------------------------------------------------
>
> Key: NXP-8723
> URL: https://jira.nuxeo.com/browse/NXP-8723
> Project: Nuxeo Enterprise Platform
> Issue Type: Bug
> Components: Core SQL Storage
> Affects Versions: 5.5, 5.5.0-HF01
> Environment: PostgreSQL / h2
> Reporter: Antoine Taillefer
> Assignee: Florent Guillaume
> Priority: Major
> Attachments: complex_list_query_fail.log
>
>
> Making the following query fails:
> {code}
> SELECT * FROM Document WHERE files/*/filename = 'myFile.txt' ORDER BY dc:title
> {code}
--
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
