Saved filter with assignees doesn't work since 0.22.1
mvalois opened this issue · 13 comments
Description
I had a saved filter that shows me every tasks where I am assigned on. For that I just created a saved filter with assignees "myusername". It worked well on 0.22.0 until I upgraded to 0.22.1.
Since then the API call to the endpoint '/projects/-5/tasks' returns an empty list (and therefore the board is empty too).
If I filter by another field like label, it works as expected. I tried filtering with other usernames and still didn't get any result.
I observe the same behaviour using Firefox or the desktop app.
Vikunja Frontend Version
0.22.1
Vikunja API Version
0.22.1
Browser and version
Firefox
Can you reproduce the bug on the Vikunja demo site?
No
Screenshots
I'm unable to reproduce this with the latest unstable build.
Does your filter only contain the assignee? Or other attributes as well? Does it work if you recreate the same filter?
The filter only contains the assignee. It does not work if I recreate the same filter.
BTW we have migrated users from local auth to OpenID auth so we may have messed up with something. We only changed issuer from local to our SSO URL. But we done this before migrating to 0.22.1 where it worked well and it also returns empty list with assignees that have a local account.
Can you check the actual filter json? Does it contain the correct username? Does it work if you filter on a project (not with a saved filter)
the actual filter json is :
{
"sort_by": [
"done",
"id"
],
"order_by": [
"asc",
"desc"
],
"filter_by": [
"done",
"assignees"
],
"filter_value": [
"false",
"mvalois"
],
"filter_comparator": [
"equals",
"in"
],
"filter_concat": "and",
"filter_include_nulls": true
}When I filter my username on a project, it also returns an empty list (reproducible with other usernames).
And your username is mvalois on the Vikunja instance? And it is empty with all usernames?
Yes my username is mvalois :
MariaDB [vikunja]> select username from users where username='mvalois';
+----------+
| username |
+----------+
| mvalois |
+----------+
Yes it is empty with every username.
It's not really reproducible for me. Can you enable database logging and share the queries being done when you access the filter?
here are the queries :
2024-02-01T10:00:11.151768198+01:00: INFO ▶ [DATABASE] 190 [SQL] SELECT `id`, `filters`, `title`, `description`, `owner_id`, `is_favorite`, `created`, `updated` FROM `saved_filters` WHERE (id = ?) LIMIT 1 [4] - 1.87925ms
2024-02-01T10:00:11.152788164+01:00: INFO ▶ [DATABASE] 191 [SQL] SELECT `id`, `name`, `username`, `password`, `email`, `status`, `avatar_provider`, `avatar_file_id`, `issuer`, `subject`, `email_reminders_enabled`, `discoverable_by_name`, `discoverable_by_email`, `overdue_tasks_reminders_enabled`, `overdue_tasks_reminders_time`, `default_project_id`, `week_start`, `language`, `timezone`, `deletion_scheduled_at`, `deletion_last_reminder_sent`, `frontend_settings`, `export_file_id`, `created`, `updated` FROM `users` WHERE `id`=? LIMIT 1 [4] - 736.875µs
2024-02-01T10:00:11.154070475+01:00: INFO ▶ [DATABASE] 192 [SQL] SELECT `id`, `filters`, `title`, `description`, `owner_id`, `is_favorite`, `created`, `updated` FROM `saved_filters` WHERE (id = ?) LIMIT 1 [4] - 1.485371ms
2024-02-01T10:00:11.154628953+01:00: INFO ▶ [DATABASE] 193 [SQL] SELECT `id`, `filters`, `title`, `description`, `owner_id`, `is_favorite`, `created`, `updated` FROM `saved_filters` WHERE (id = ?) LIMIT 1 [4] - 410.7µs
2024-02-01T10:00:11.155260669+01:00: INFO ▶ [DATABASE] 194 [SQL] SELECT `id`, `name`, `username`, `password`, `email`, `status`, `avatar_provider`, `avatar_file_id`, `issuer`, `subject`, `email_reminders_enabled`, `discoverable_by_name`, `discoverable_by_email`, `overdue_tasks_reminders_enabled`, `overdue_tasks_reminders_time`, `default_project_id`, `week_start`, `language`, `timezone`, `deletion_scheduled_at`, `deletion_last_reminder_sent`, `frontend_settings`, `export_file_id`, `created`, `updated` FROM `users` WHERE `id`=? LIMIT 1 [4] - 455.112µs
2024-02-01T10:00:11.155644473+01:00: INFO ▶ [DATABASE] 195 [SQL] SELECT l.* FROM projects l LEFT JOIN team_projects tl ON tl.project_id = l.id LEFT JOIN team_members tm2 ON tm2.team_id = tl.team_id LEFT JOIN users_projects ul ON ul.project_id = l.id WHERE (tm2.user_id=? OR ul.user_id=? OR l.owner_id=?) AND l.title LIKE ? AND l.is_archived=? AND (l.parent_project_id IS NULL OR l.parent_project_id=? OR ((tm2.user_id IS NOT NULL OR ul.user_id IS NOT NULL) AND l.parent_project_id IS NOT NULL)) AND 0=0 GROUP BY l.id ORDER BY position [4 4 4 %% false 0] - 2.640235ms
2024-02-01T10:00:11.155949458+01:00: INFO ▶ [DATABASE] 196 [SQL] SELECT `entity_id`, `user_id`, `kind` FROM `favorites` WHERE (entity_id = ? AND user_id = ? AND kind = ?) LIMIT 1 [-5 4 2] - 521.015µs
2024-02-01T10:00:11.156857961+01:00: INFO ▶ [DATABASE] 197 [SQL] SELECT `id`, `entity_type`, `entity_id`, `user_id`, `created` FROM `subscriptions` WHERE (user_id = ?) AND entity_id IN (?) AND entity_type=? [4 -5 2] - 543.782µs
2024-02-01T10:00:11.157823448+01:00: INFO ▶ [DATABASE] 198 [SQL] SELECT count(*) FROM projects l LEFT JOIN team_projects tl ON tl.project_id = l.id LEFT JOIN team_members tm2 ON tm2.team_id = tl.team_id LEFT JOIN users_projects ul ON ul.project_id = l.id WHERE (tm2.user_id=? OR ul.user_id=? OR l.owner_id=?) AND l.title LIKE ? AND l.is_archived=? AND (l.parent_project_id IS NULL OR l.parent_project_id=? OR ((tm2.user_id IS NOT NULL OR ul.user_id IS NOT NULL) AND l.parent_project_id IS NOT NULL)) AND 0=0 GROUP BY l.id ORDER BY position [4 4 4 %% false 0] - 1.745573ms
2024-02-01T10:00:11.159167803+01:00: INFO ▶ [DATABASE] 199 [SQL] SELECT l.* FROM projects l LEFT JOIN team_projects tl ON tl.project_id = l.parent_project_id LEFT JOIN team_members tm2 ON tm2.team_id = tl.team_id LEFT JOIN users_projects ul ON ul.project_id = l.parent_project_id WHERE (tm2.user_id=? OR ul.user_id=? OR l.owner_id=?) AND l.title LIKE ? AND l.is_archived=? AND l.parent_project_id IN (?,?,?,?,?,?,?,?,?,?,?,?) AND l.id NOT IN (?,?,?,?,?,?,?,?,?,?,?,?) GROUP BY l.id ORDER BY position [4 4 4 %% false 11 13 17 31 29 12 27 51 52 54 61 22 11 13 17 31 29 12 27 51 52 54 61 22] - 1.197638ms
2024-02-01T10:00:11.159918791+01:00: INFO ▶ [DATABASE] 19a [SQL] SELECT count(*) FROM `favorites` WHERE user_id=? AND kind=? [4 1] - 420.324µs
2024-02-01T10:00:11.162018081+01:00: INFO ▶ [DATABASE] 19c [SQL] SELECT `id`, `title`, `description`, `done`, `done_at`, `due_date`, `project_id`, `repeat_after`, `repeat_mode`, `priority`, `start_date`, `end_date`, `hex_color`, `percent_done`, `index`, `uid`, `cover_image_attachment_id`, `created`, `updated`, `bucket_id`, `position`, `kanban_position`, `created_by_id` FROM `tasks` WHERE 0=1 ORDER BY `position` IS NULL, `position` asc, `position` IS NULL, `position` asc, `created` IS NULL, `created` desc [] - 267.237µs
I also noticed there is an error in the stderr when accessing the filter:
2024-02-01T10:00:11.160116957+01:00: ERROR ▶ models/convertFilterValues 19b Unknown search type for value [mvalois]
I also noticed there is an error in the stderr when accessing the filter:
That probably is the cause.
I've just pushed a change (8efdc96) which adds more logging to that case. Please pull the next unstable build (ready in ~45min) and check the log message again.
here is the log
2024-02-01T21:54:31.020532921+01:00: ERROR ▶ models/convertFilterValues 133 Unknown search type for value [mvalois] of type []string
Weird, I reimported the database (mysql) in my other test instance and the filter is working as expected.
I tried to reimport it in another database on the same mysql server but the filters do not work. So it looks it is due to the server itself. However they look to be utf8 compatible:
> SELECT default_character_set_name FROM information_schema.SCHEMATA WHERE schema_name = 'vikunja';
+----------------------------+
| default_character_set_name |
+----------------------------+
| utf8mb4 |
+----------------------------+
Should be fixed in b95b92a. Please check with the next unstable build (ready in ~45min) if the problem went away.
Yes, the problem went away with your fix. Thank you =)