PreviewCleanup query is incorrect

[mtillberg]

Steps to reproduce

1. Delete 4 files with thumbnails from OwnCloud, deleting from trashbin as well

Expected behaviour

Tell us what should happen

With old (v10.1) PreviewCleanup query, sql returns correct thumbnails to delete, albeit slowly:

+--------+-------+--------------------------------------+
| fileid | name  | user_id                              |
+--------+-------+--------------------------------------+
|  30689 | 30686 | 5019585e-4aee-103d-9ced-f379c4734244 |
|  30693 | 26    | 5019585e-4aee-103d-9ced-f379c4734244 |
|  30696 | 28    | 5019585e-4aee-103d-9ced-f379c4734244 |
|  30699 | 27    | 5019585e-4aee-103d-9ced-f379c4734244 |
+--------+-------+--------------------------------------+
4 rows in set (1 min 57.177 sec)

Actual behaviour

Tell us what happens instead

Current query returns extra rows. This will lead to deleting thumbnails unnecessarily.

+--------+-------+--------------------------------------+
| fileid | name  | user_id                              |
+--------+-------+--------------------------------------+
|  30649 | 30589 | 5019585e-4aee-103d-9ced-f379c4734244 |
|  30650 | 30594 | 5019585e-4aee-103d-9ced-f379c4734244 |
|  30651 | 30591 | 5019585e-4aee-103d-9ced-f379c4734244 |
|  30667 | 140   | 5019585e-4aee-103d-9ced-f379c4734244 |
|  30668 | 208   | 5019585e-4aee-103d-9ced-f379c4734244 |
|  30669 | 66    | 5019585e-4aee-103d-9ced-f379c4734244 |
|  30671 | 141   | 5019585e-4aee-103d-9ced-f379c4734244 |
|  30674 | 1174  | 5019585e-4aee-103d-9ced-f379c4734244 |
|  30676 | 30666 | 5019585e-4aee-103d-9ced-f379c4734244 |
|  30689 | 30686 | 5019585e-4aee-103d-9ced-f379c4734244 |
|  30693 | 26    | 5019585e-4aee-103d-9ced-f379c4734244 |
|  30696 | 28    | 5019585e-4aee-103d-9ced-f379c4734244 |
|  30699 | 27    | 5019585e-4aee-103d-9ced-f379c4734244 |
+--------+-------+--------------------------------------+
13 rows in set (0.135 sec)

Server configuration

Operating system: Debian

Web server: Apache

Database: MariaDB

PHP version: PHP 7.4.3-4ubuntu2.18

ownCloud version: 10.12.1.3

Updated from an older ownCloud or fresh install: Updated

Where did you install ownCloud from: Docker (owncloud/server:10.12)

[mtillberg]

With further testing, the current PreviewCleanup query will delete all thumbnails. To test:

Clean current docker install (owncloud/server:10.12)
Browse in web UI to generate thumbnails.
Verify thumbnail existence (thumbnail path, filename is an integer)

MariaDB [owncloud]> select count(1) from oc_filecache where path like 'thumbnail%' and cast(cast(name as int) as char(24)) = name;
+----------+
| count(1) |
+----------+
|       24 |
+----------+

Run occ previews:cleanup, verify all thumbnails are gone, even though no changes/deletes happened to the referenced files.

$ occ previews:cleanup
30589 - 5019585e-4aee-103d-9ced-f379c4734244: deleted
...
24 orphaned previews deleted

MariaDB [owncloud]> select count(1) from oc_filecache where path like 'thumbnail%' and cast(cast(name as int) as char(24)) = name;
+----------+
| count(1) |
+----------+
|        0 |
+----------+

This is because the current "not in" clause of the query evaluates to "thumbnail fileids not in the set of fileids of files that have a thumbnail", which will always be true.