[JIRA] Commented: (NXP-8001) querying for complex types and ordering is failing
[
https://jira.nuxeo.com/browse/NXP-8001?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=106899#comment-106899
]
Florent Guillaume commented on NXP-8001:
In this case the {{DISTINCT}} shouldn't be there and this would work.
But for a query like:
{code}
SELECT * from Document where ecm:isProxy = 0 and dc:subject/* = 'foo' ORDER BY
dc:title
{code}
there will be a {{DISTINCT}} but we can generate a proper SQL query
nevertheless by adding dc:title to the select list.
But an {{ORDER BY}} on an array or complex list element will still be illegal
in the {{SELECT *}} case, or in the {{SELECT DISTINCT}} case if it's not also
in the select list.
> 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
> Components: Core SQL Storage
>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
ECM-tickets@lists.nuxeo.com
http://lists.nuxeo.com/mailman/listinfo/ecm-tickets
[JIRA] Commented: (NXP-8001) querying for complex types and ordering is failing
[
https://jira.nuxeo.com/browse/NXP-8001?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=107018#comment-107018
]
Patrick Schmitz commented on NXP-8001:
--
I am not clear on this qualification. Does this mean the the NXQL described in
SUPNXP-4777, which is otherwise legal according to the NXQL docs, will fail?
How do we express this query legally (i.e., in a manner that works)?
{code}SELECT * FROM Loanin ORDER BY
loansin_common:lenderGroupList/*/lender{code}
Would this work?
{code}SELECT loansin_common:lenderGroupList/*/lender FROM Loanin ORDER BY
loansin_common:lenderGroupList/*/lender{code}
I have never been clear from the documentation what the difference is between
saying:
{code}SELECT * FROM Loanin...{code}
and
{code}SELECT loansin_common:foo, loansin_common:bar FROM Loanin...{code}
> 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
> Components: Core SQL Storage
>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
ECM-tickets@lists.nuxeo.com
http://lists.nuxeo.com/mailman/listinfo/ecm-tickets
[JIRA] Commented: (NXP-8001) querying for complex types and ordering is failing
[
https://jira.nuxeo.com/browse/NXP-8001?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=107059#comment-107059
]
Florent Guillaume commented on NXP-8001:
There are two methods to do queries in Nuxeo using NXQL, and they return
different kinds of results.
- {{CoreSession.query()}} returns documents, so it must get unique document ids
from the query. Whenever a query with a complex property like {{foo/*/bar}} is
used, there are internal {{JOINs}} used which mean that you could get several
rows for the same document id, so a {{DISTINCT}} has to be added internally to
the SQL query.
- {{CoreSession.queryAndFetch()}} returns arbitrary columns and closely mirrors
an actual SQL query, you can specify exactly which columns you want, and you
can put or not an explicit {{DISTINCT}}. You're in control of the query.
But in both cases you must deal with the fact that in SQL, if you do a {{SELECT
DISTINCT}} that has an {{ORDER BY}} then the {{ORDER BY}} columns must be in
the {{SELECT DISTINCT}} list. That's imposed by SQL.
In NXQL a {{SELECT *}} is actually turned into a {{SELECT ecm:uuid}} and is
mostly used for {{CoreSession.query()}}, conversely this method just needs the
document id so {{SELECT *}} is always enough for it.
For {{CoreSession.queryAndFetch()}} you usually specify the columns you want
explicitly, and when you just want the ids then {{SELECT ecm:uuid}} is usually
more explicit than doing {{SELECT *}}, but the latter is legal.
> 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
> Components: Core SQL Storage
>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
ECM-tickets@lists.nuxeo.com
http://lists.nuxeo.com/mailman/listinfo/ecm-tickets
[JIRA] Commented: (NXP-8001) querying for complex types and ordering is failing
[
https://jira.nuxeo.com/browse/NXP-8001?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=107060#comment-107060
]
Florent Guillaume commented on NXP-8001:
So the question about the illegal
{noformat}
SELECT * FROM Loanin ORDER BY loansin_common:lenderGroupList/*/lender
{noformat}
is what exactly do you want it to return, and in what order?
If you want it to return documents, and each may have several lenders, then I
don't see how you can find a good way to define the order on the documents
based on the lenders.
You could return document ids and each lender by doing:
{noformat}
SELECT loansin_common:lenderGroupList/*1/lender, ecm:uuid FROM Loanin ORDER BY
loansin_common:lenderGroupList/*1/lender
{noformat}
but here you may get the same document id several times.
> 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
> Components: Core SQL Storage
>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
ECM-tickets@lists.nuxeo.com
http://lists.nuxeo.com/mailman/listinfo/ecm-tickets
[JIRA] Commented: (NXP-8001) querying for complex types and ordering is failing
[
https://jira.nuxeo.com/browse/NXP-8001?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=107090#comment-107090
]
Patrick Schmitz commented on NXP-8001:
--
You are quite right - I should have been more careful in my request. I would
like to specify the following NXQL, to order the documents by the primary value
of the lender. Is this legal?
{code}SELECT * FROM Loanin ORDER BY
loansin_common:lenderGroupList/0/lender{code}
If not, it sounds like the following should work:
{code}SELECT loansin_common:lenderGroupList/0/lender, ecm:uuid FROM Loanin
ORDER BY loansin_common:lenderGroupList/0/lender{code}
Is this correct? Thanks.
> 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
> Components: Core SQL Storage
>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
ECM-tickets@lists.nuxeo.com
http://lists.nuxeo.com/mailman/listinfo/ecm-tickets
[JIRA] Commented: (NXP-8001) querying for complex types and ordering is failing
[
https://jira.nuxeo.com/browse/NXP-8001?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=107104#comment-107104
]
Florent Guillaume commented on NXP-8001:
I just made an additional fix to make your query legal, the code was overly
restrictive when no wildcards were involved.
http://hg.nuxeo.org/nuxeo/nuxeo-core/rev/2853b482b0e6
And your second query works as well.
> 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
> Components: Core SQL Storage
>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
ECM-tickets@lists.nuxeo.com
http://lists.nuxeo.com/mailman/listinfo/ecm-tickets
