[ https://jira.nuxeo.com/browse/CMIS-27?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Julien Carsique updated CMIS-27: -------------------------------- Component/s: CMIS > 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 > Components: CMIS > 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 ECM-tickets@lists.nuxeo.com http://lists.nuxeo.com/mailman/listinfo/ecm-tickets