Phrase-base searcing with PostgreSQL

I have a new clean 5.5 install running against a PostgreSQL 9.1 and phrase-based searching is not working.

For example if I enter “data migration” (with the double-quotes) I get documents with the words data AND migration no matter where in the document they are, i.e the same as using data migration with no enclosing double-quotes. I have tried in the quick-search, the advanced search and using the Administrator nxql search.

NXP-6720 states that it should work…. https://jira.nuxeo.com/browse/NXP-6720

Any ideas?

Thanks,

Sean

1 votes

0 answers

836 views

ANSWER

Could you activate debugging for the VCS queries and check what is executed? Set log category org.nuxeo.ecm.core.storage.sql.jdbc to TRACE.
11/14/2012

2012-11-14 22:09:07,091 TRACE org.nuxeo.ecm.core.storage.sql.jdbc.JDBCLogger SQL: SELECT "C1" FROM (SELECT "hierarchy"."id" AS "C1", "F2"."title" AS "C2" FROM "hierarchy" JOIN "fulltext" ON "fulltext"."id" = "hierarchy"."id" LEFT JOIN "misc" "F1" ON "hierarchy"."id" = "F1"."id" LEFT JOIN "dublincore" "F2" ON "hierarchy"."id" = "F2"."id", TO_TSQUERY('english', '(data & migration)') "nxquery" WHERE (("hierarchy"."primarytype" IN ('SectionRoot', 'Workspace', 'RelationSearch', 'BlogSite', 'ManagementRoot', 'Tag', 'AdministrativeStatusContainer', 'search_results', 'Thread', 'Space', 'TemplateRoot', 'Domain', 'WorkspaceRoot', 'Comment', 'QueryNav', 'TaskList', 'WebPage', 'WCOpenSocial', 'TaskLists', 'MailMessage', 'Forum', 'ContextualLink', 'PictureBook', 'Unit', 'TaskRoot', 'Post', 'WCHtml', 'AdministrativeStatus', 'Picture', 'UserWorkspacesRoot', 'Document', 'Folder', 'FacetedSearch', 'WCPicture', 'File', 'WebSite', 'FacetedSearchDefault', 'AdvancedSearch', 'Gadget', 'Note', 'TaskDoc', 'HiddenFolder', 'Section', 'UserProfile', 'MailFolder', 'OrderedFolder', 'BasicAuditSearch', 'CommentRoot', 'BlogPost')) AND ((nxquery @@ NX_TO_TSVECTOR("fulltext"."fulltext"))) AND ("hierarchy"."primarytype" IN ('SectionRoot', 'Workspace', 'RelationSearch', 'BlogSite', 'Thread', 'TemplateRoot', 'Domain', 'WorkspaceRoot', 'QueryNav', 'WebPage', 'MailMessage', 'Forum', 'PictureBook', 'ContextualLink', 'Picture', 'Relation', 'Document', 'Folder', 'FacetedSearch', 'File', 'AdvancedSearch', 'FacetedSearchDefault', 'WebSite', 'Note', 'Section', 'Root', 'MailFolder', 'OrderedFolder', 'BasicAuditSearch', 'BlogPost')) AND ("hierarchy"."primarytype" IN ('SectionRoot', 'Workspace', 'RelationSearch', 'PublicationRelation', 'BlogSite', 'Tag', 'ManagementRoot', 'AdministrativeStatusContainer', 'Tagging', 'Thread', 'CommentRelation', 'search_results', 'Space', 'TemplateRoot', 'Domain', 'WorkspaceRoot', 'Comment', 'QueryNav', 'TaskList', 'DefaultRelation', 'WebPage', 'WCOpenSocial', 'TaskLists', 'MailMessage', 'Forum', 'PictureBook', 'ContextualLink', 'Unit', 'TaskRoot', 'WCHtml', 'Post', 'AdministrativeStatus', 'UserWorkspacesRoot', 'Picture', 'Relation', 'Document', 'Folder', 'WCPicture', 'File', 'AdvancedSearch', 'WebSite', 'Note', 'Gadget', 'TaskDoc', 'HiddenFolder', 'Section', 'Root', 'UserProfile', 'MailFolder', 'OrderedFolder', 'CommentRoot', 'BasicAuditSearch', 'BlogPost') AND (NOT(ARRAY['HiddenInFacetedSearch'] <@ "hierarchy"."mixintypes") OR "hierarchy"."mixintypes" IS NULL)) AND ("hierarchy"."isversion" IS NULL) AND ("F1"."lifecyclestate" <> 'deleted')) UNION ALL SELECT "H"."id" AS "C1", "F2"."title" AS "C2" FROM "hierarchy" "H" JOIN "proxies" ON "H"."id" = "proxies"."id" JOIN "hierarchy" ON "proxies"."targetid" = "hierarchy"."id" JOIN "fulltext" ON "fulltext"."id" = "hierarchy"."id" LEFT JOIN "misc" "F1" ON "hierarchy"."id" = "F1"."id" LEFT JOIN "dublincore" "F2" ON "hierarchy"."id" = "F2"."id", TO_TSQUERY('english', '(data & migration)') "nxquery" WHERE (("hierarchy"."primarytype" IN ('SectionRoot', 'Workspace', 'RelationSearch', 'BlogSite', 'ManagementRoot', 'Tag', 'AdministrativeStatusContainer', 'search_results', 'Thread', 'Space', 'TemplateRoot', 'Domain', 'WorkspaceRoot', 'Comment', 'QueryNav', 'TaskList', 'WebPage', 'WCOpenSocial', 'TaskLists', 'MailMessage', 'Forum', 'ContextualLink', 'PictureBook', 'Unit', 'TaskRoot', 'Post', 'WCHtml', 'AdministrativeStatus', 'Picture', 'UserWorkspacesRoot', 'Document', 'Folder', 'FacetedSearch', 'WCPicture', 'File', 'WebSite', 'FacetedSearchDefault', 'AdvancedSearch', 'Gadget', 'Note', 'TaskDoc', 'HiddenFolder', 'Section', 'UserProfile', 'MailFolder', 'OrderedFolder', 'BasicAuditSearch', 'CommentRoot', 'BlogPost')) AND ((nxquery @@ NX_TO_TSVECTOR("fulltext"."fulltext"))) AND ("hierarchy"."primarytype" IN ('SectionRoot', 'Workspace', 'RelationSearch', 'BlogSite', 'Thread', 'TemplateRoot', 'Domain', 'WorkspaceRoot', 'QueryNav', 'WebPage', 'MailMessage', 'Forum', 'PictureBook', 'ContextualLink', 'Picture', 'Relation', 'Document', 'Folder', 'FacetedSearch', 'File', 'AdvancedSearch', 'FacetedSearchDefault', 'WebSite', 'Note', 'Section', 'Root', 'MailFolder', 'OrderedFolder', 'BasicAuditSearch', 'BlogPost')) AND ("hierarchy"."primarytype" IN ('SectionRoot', 'Workspace', 'RelationSearch', 'PublicationRelation', 'BlogSite', 'Tag', 'ManagementRoot', 'AdministrativeStatusContainer', 'Tagging', 'Thread', 'CommentRelation', 'search_results', 'Space', 'TemplateRoot', 'Domain', 'WorkspaceRoot', 'Comment', 'QueryNav', 'TaskList', 'DefaultRelation', 'WebPage', 'WCOpenSocial', 'TaskLists', 'MailMessage', 'Forum', 'PictureBook', 'ContextualLink', 'Unit', 'TaskRoot', 'WCHtml', 'Post', 'AdministrativeStatus', 'UserWorkspacesRoot', 'Picture', 'Relation', 'Document', 'Folder', 'WCPicture', 'File', 'AdvancedSearch', 'WebSite', 'Note', 'Gadget', 'TaskDoc', 'HiddenFolder', 'Section', 'Root', 'UserProfile', 'MailFolder', 'OrderedFolder', 'CommentRoot', 'BasicAuditSearch', 'BlogPost') AND (NOT(ARRAY['HiddenInFacetedSearch'] <@ "hierarchy"."mixintypes") OR "hierarchy"."mixintypes" IS NULL)) AND ("H"."isversion" IS NULL) AND ("F1"."lifecyclestate" <> 'deleted'))) AS "T" ORDER BY "_C2" – LIMIT 20 OFFSET 0 – COUNT TOTAL

(the query looks the same for both simple and phrase based searching)

11/14/2012

The logged query is the execution of just data migration, not &quot;data migration&quot; with quotes, because if it was the query would contain something like:

_nxquery @@ NX_TO_TSVECTOR(&quot;fulltext&quot;.&quot;fulltext&quot;) AND &quot;fulltext&quot;.&quot;fulltext&quot; LIKE &apos;% data migration %&apos;

So something strips your double quotes. It may have been a bug in Nuxeo 5.5, I don't remember. Could you check with Nuxeo 5.6 please?

11/16/2012