Work at SourceForge, help us to make it a better place! We have an immediate need for a Support Technician in our San Francisco or Denver office.

Close

sql syntax error on search

Developers
Anonymous
2012-05-01
2013-03-22

  • Anonymous
    2012-05-01

    i created a user with guest only permission but when i try to search it returns null result while the other user's who are not guest only permitted the search functions returns the expected result.

    while i was log to the system as the admin wanted to see the log record and found these error

    ERROR: column "object_owner" does not exist LINE 1: … WHERE ( group_id IN (0,3) OR account_id='4' OR object_own…

    SELECT id,ts_rank(idxfti,to_tsquery('english','hello')) FROM docmgr.dm_view_search WHERE idxfti @@ to_tsquery('english','hello') AND id IN (SELECT object_id AS id FROM docmgr.dm_object_perm WHERE ( group_id IN (0,3) OR account_id='4' OR object_owner='4') INTERSECT SELECT object_id AS id FROM docmgr.dm_object_parent WHERE parent_id IN
    (SELECT object_id FROM docmgr.dm_object_perm WHERE ( group_id IN (0,3) OR account_id='4' OR object_owner='4')) INTERSECT SELECT id FROM docmgr.dm_object WHERE hidden='f') ORDER BY ts_rank(idxfti,to_tsquery('english','hello')) DESC

    these error shows exclusively for users who have guest only permission

     
  • Eric Lawman
    Eric Lawman
    2012-05-01

    Hi, I got your email also.  I'm looking into it and will post back here with what I find.

    Eric

     
  • Eric Lawman
    Eric Lawman
    2012-05-01

    Hi. It looks to be a bug in the dm_view_search view.  Run this query on your database to fix…

    DROP VIEW docmgr.dm_view_search;
    CREATE VIEW docmgr.dm_view_search AS
    SELECT dm_object.id, dm_object.name, dm_object.summary, dm_object.object_owner, dm_object.filesize, dm_object.last_modified, dm_index.idxfti
       FROM docmgr.dm_index
       LEFT JOIN docmgr.dm_object ON dm_index.object_id = dm_object.id;

    Eric

     

  • Anonymous
    2012-05-02

    thank you for the response i will try to run the sql and report back on the result

     

  • Anonymous
    2012-05-02

    will these also fix the error regarding the group permission set to guest account only?because a user with a group that has guest account only permission also returns error

     

  • Anonymous
    2012-05-02

    its now showing an error permission denied for relation dm_view_search on the same sql query

     

  • Anonymous
    2012-05-03

    its now fix i forgot to login as the owner of the database that is why its showing a permission denied error.tnx u