[
https://jira.nuxeo.com/browse/CMIS-27?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Florent Guillaume updated CMIS-27:
----------------------------------
Description:
With H2 dialect:
{noformat}
SELECT Ent.cmis:objectId FROM Person Ent JOIN Occurrence Occ ON
Occ.relation:target = Ent.cmis:objectId WHERE Occ.relation:source =
'fec8e975-53df-47cd-bc14-0744dd648912' ORDER BY Ent.dc:title
{noformat}
Generates:
{noformat}
SELECT "_Ent_HIERARCHY"."ID", "_Ent_HIERARCHY"."PRIMARYTYPE",
"_Occ_HIERARCHY"."ID", "_Occ_HIERARCHY"."PRIMARYTYPE"
FROM "HIERARCHY" "_Ent_HIERARCHY"
LEFT JOIN "DUBLINCORE" "_Ent_DUBLINCORE" ON "_Ent_DUBLINCORE"."ID" =
"_Ent_HIERARCHY"."ID"
LEFT JOIN "MISC" "_Ent_MISC" ON "_Ent_MISC"."ID" = "_Ent_HIERARCHY"."ID"
JOIN hierarchy_read_acl nxr ON "_Ent_HIERARCHY"."ID" = nxr.id
JOIN "RELATION" "_Occ_RELATION" ON "_Occ_RELATION"."TARGET" =
"_Ent_HIERARCHY"."ID"
LEFT JOIN "HIERARCHY" "_Occ_HIERARCHY" ON "_Occ_HIERARCHY"."ID" =
"_Occ_RELATION"."ID"
LEFT JOIN "MISC" "_Occ_MISC" ON "_Occ_MISC"."ID" = "_Occ_RELATION"."ID"
JOIN hierarchy_read_acl nxr0 ON "_Occ_RELATION"."ID" = nxr0.id
WHERE "_Ent_HIERARCHY"."PRIMARYTYPE" IN (?)
AND "_Ent_MISC"."LIFECYCLESTATE" <> ?
AND nxr.acl_id IN (SELECT * FROM nx_get_read_acls_for(?, ','))
AND "_Occ_HIERARCHY"."PRIMARYTYPE" IN (?)
AND "_Occ_MISC"."LIFECYCLESTATE" <> ?
AND nxr0.acl_id IN (SELECT * FROM nx_get_read_acls_for(?, ','))
AND "_Occ_RELATION"."SOURCE" = ?
ORDER BY "_Ent_DUBLINCORE"."TITLE"
{noformat}
Which throws exception:
{noformat}
org.h2.jdbc.JdbcSQLException. message: Column _Occ_HIERARCHY.ID not found
{noformat}
Actually the culprit is the nxr-1 which is an invalid identifier.
was:
With H2 dialect:
SELECT Ent.cmis:objectId FROM Person Ent JOIN Occurrence Occ ON
Occ.relation:target = Ent.cmis:objectId WHERE Occ.relation:source =
'fec8e975-53df-47cd-bc14-0744dd648912' ORDER BY Ent.dc:title
Generates:
SELECT "_Ent_HIERARCHY"."ID", "_Ent_HIERARCHY"."PRIMARYTYPE",
"_Occ_HIERARCHY"."ID", "_Occ_HIERARCHY"."PRIMARYTYPE"
FROM "HIERARCHY" "_Ent_HIERARCHY"
LEFT JOIN "DUBLINCORE" "_Ent_DUBLINCORE" ON "_Ent_DUBLINCORE"."ID" =
"_Ent_HIERARCHY"."ID"
LEFT JOIN "MISC" "_Ent_MISC" ON "_Ent_MISC"."ID" = "_Ent_HIERARCHY"."ID"
JOIN hierarchy_read_acl nxr ON "_Ent_HIERARCHY"."ID" = nxr.id
JOIN "RELATION" "_Occ_RELATION" ON "_Occ_RELATION"."TARGET" =
"_Ent_HIERARCHY"."ID"
LEFT JOIN "HIERARCHY" "_Occ_HIERARCHY" ON "_Occ_HIERARCHY"."ID" =
"_Occ_RELATION"."ID"
LEFT JOIN "MISC" "_Occ_MISC" ON "_Occ_MISC"."ID" = "_Occ_RELATION"."ID"
JOIN hierarchy_read_acl nxr0 ON "_Occ_RELATION"."ID" = nxr0.id
WHERE "_Ent_HIERARCHY"."PRIMARYTYPE" IN (?)
AND "_Ent_MISC"."LIFECYCLESTATE" <> ?
AND nxr.acl_id IN (SELECT * FROM nx_get_read_acls_for(?, ','))
AND "_Occ_HIERARCHY"."PRIMARYTYPE" IN (?)
AND "_Occ_MISC"."LIFECYCLESTATE" <> ?
AND nxr0.acl_id IN (SELECT * FROM nx_get_read_acls_for(?, ','))
AND "_Occ_RELATION"."SOURCE" = ?
ORDER BY "_Ent_DUBLINCORE"."TITLE"
Which throws exception:
org.h2.jdbc.JdbcSQLException. message: Column _Occ_HIERARCHY.ID not found
Actually the culprit is the nxr-1 which is an invalid identifier.
> Incorrect SQL generation for query with JOIN and ACLs on H2
> -----------------------------------------------------------
>
> Key: CMIS-27
> URL: https://jira.nuxeo.com/browse/CMIS-27
> Project: Nuxeo Chemistry
> Issue Type: Bug
> Reporter: Florent Guillaume
> Assignee: Florent Guillaume
> Priority: Major
> Fix For: nuxeo-5.4.2
>
>
> With H2 dialect:
> {noformat}
> SELECT Ent.cmis:objectId FROM Person Ent JOIN Occurrence Occ ON
> Occ.relation:target = Ent.cmis:objectId WHERE Occ.relation:source =
> 'fec8e975-53df-47cd-bc14-0744dd648912' ORDER BY Ent.dc:title
> {noformat}
> Generates:
> {noformat}
> SELECT "_Ent_HIERARCHY"."ID", "_Ent_HIERARCHY"."PRIMARYTYPE",
> "_Occ_HIERARCHY"."ID", "_Occ_HIERARCHY"."PRIMARYTYPE"
> FROM "HIERARCHY" "_Ent_HIERARCHY"
> LEFT JOIN "DUBLINCORE" "_Ent_DUBLINCORE" ON "_Ent_DUBLINCORE"."ID" =
> "_Ent_HIERARCHY"."ID"
> LEFT JOIN "MISC" "_Ent_MISC" ON "_Ent_MISC"."ID" = "_Ent_HIERARCHY"."ID"
> JOIN hierarchy_read_acl nxr ON "_Ent_HIERARCHY"."ID" = nxr.id
> JOIN "RELATION" "_Occ_RELATION" ON "_Occ_RELATION"."TARGET" =
> "_Ent_HIERARCHY"."ID"
> LEFT JOIN "HIERARCHY" "_Occ_HIERARCHY" ON "_Occ_HIERARCHY"."ID" =
> "_Occ_RELATION"."ID"
> LEFT JOIN "MISC" "_Occ_MISC" ON "_Occ_MISC"."ID" = "_Occ_RELATION"."ID"
> JOIN hierarchy_read_acl nxr0 ON "_Occ_RELATION"."ID" = nxr0.id
> WHERE "_Ent_HIERARCHY"."PRIMARYTYPE" IN (?)
> AND "_Ent_MISC"."LIFECYCLESTATE" <> ?
> AND nxr.acl_id IN (SELECT * FROM nx_get_read_acls_for(?, ','))
> AND "_Occ_HIERARCHY"."PRIMARYTYPE" IN (?)
> AND "_Occ_MISC"."LIFECYCLESTATE" <> ?
> AND nxr0.acl_id IN (SELECT * FROM nx_get_read_acls_for(?, ','))
> AND "_Occ_RELATION"."SOURCE" = ?
> ORDER BY "_Ent_DUBLINCORE"."TITLE"
> {noformat}
> Which throws exception:
> {noformat}
> org.h2.jdbc.JdbcSQLException. message: Column _Occ_HIERARCHY.ID not found
> {noformat}
> Actually the culprit is the nxr-1 which is an invalid identifier.
--
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
