relation_deletedate_d not set properly

[vmario89]

Hi, i figured out that i got some documents which have broken relations. So i have some documents which show clickable relation buttons to other docs but i if click on them i get an error "document not found".

I cannot find out how it happened over time but it seems it does not carefully set rel_deletedate_d to the correct delete date sometimes. Have some relation delete dates which are still NULL even if the linked documents are deleted.

I used this query to investigate and found ~10 documents in our instance with thousands of docs. I fixed them manually but maybe somebody finds this useful or somebody made similar experience with some documents

SELECT
    F.doc_title_c "From",
    T.doc_title_c "To"
FROM
    t_relation AS R
JOIN t_document AS T ON R.rel_iddocfrom_c = T.doc_id_c
JOIN t_document AS F ON R.rel_iddocto_c = F.doc_id_c
WHERE
    R.rel_deletedate_d IS NULL AND
    T.doc_deletedate_d IS NOT NULL AND
    F.doc_deletedate_d IS NULL

UNION

SELECT
    T.doc_title_c "From",
    F.doc_title_c "To"
FROM
    t_relation AS R
JOIN t_document AS T ON R.rel_iddocfrom_c = T.doc_id_c
JOIN t_document AS F ON R.rel_iddocto_c = F.doc_id_c
WHERE
    R.rel_deletedate_d IS NULL AND
    T.doc_deletedate_d IS NULL AND
    F.doc_deletedate_d IS NOT NULL
    
ORDER BY "From"
;

[vmario89]

made some quick docu at https://stadtfabrikanten.org/display/TEED/Manually+fix+broken+document+relations+in+database