Content
View differences
Updated by Christophe Bliard 10 months ago
This is the AppSignal ticket for bug ##66677
### Steps to reproduce
1. active performance improvement of WorkPackage.visible for large instance (ticket #66405) with `Setting.large_instance_wp_allowed_to_sql = true`
2. Log in as a normal user (will not trigger for admins)
3. Go to work package list view
4. Apply baseline comparison with any value like "yesterday"
1. It can be noticed that the UI does not show any baseline differences. It's because there is already a 500 error, but it's not shown by the UI.
5. Reload the page unknown
### What is the buggy behavior?
* 500 error
* no work packages listed unknown
### What is the expected behavior?
1. no errors and list work package with baseline comparison unknown
### Workaround
Disable the performance trick by changing `large_instance_wp_allowed_to_sql` setting:
* export environment variable `OPENPROJECT_LARGE__INSTANCE__WP__ALLOWED__TO__SQL=false`
### Screenshots
on a dev instance
<img class="op-uc-image op-uc-image_inline" src="/api/v3/attachments/773854/content">
on a prod instance ([community](https://community.openproject.org/projects/openproject/work_packages?query_id=5307) but not reproducible now that `Setting.large_instance_wp_allowed_to_sql` has been set to `false`)
<img class="op-uc-image op-uc-image_inline" src="/api/v3/attachments/773856/content">
### **Logs**
**from AppSignal**
```text
PG::UndefinedTable: ERROR: missing FROM-clause entry for table "work_package_journals"
LINE 1: ...l::WorkPackageJournal' AND "journals"."data_id" = "work_pack...
lib/api/v3/work_packages/work_package_collection_representer.rb:226 API::V3::WorkPackages::WorkPackageCollectionRepresenter#paged_models
lib/api/decorators/offset_paginated_collection.rb:46 API::Decorators::OffsetPaginatedCollection#initialize
lib/api/v3/work_packages/work_package_collection_representer.rb:57 API::V3::WorkPackages::WorkPackageCollectionRepresenter#initialize
app/services/api/v3/work_package_collection_from_query_service.rb:153 Class#new
app/services/api/v3/work_package_collection_from_query_service.rb:153 API::V3::WorkPackageCollectionFromQueryService#collection_representer
app/services/api/v3/work_package_collection_from_query_service.rb:66 API::V3::WorkPackageCollectionFromQueryService#results_to_representer
app/services/api/v3/work_package_collection_from_query_service.rb:49 API::V3::WorkPackageCollectionFromQueryService#call
lib/api/v3/queries/helpers/query_representer_response.rb:38 block in API::V3::Queries::Helpers::QueryRepresenterResponse#query_representer_response
lib/api/root_api.rb:261 API::RootAPI::Helpers#raise_invalid_query_on_service_failure
lib/api/v3/queries/helpers/query_representer_response.rb:35 API::V3::Queries::Helpers::QueryRepresenterResponse#query_representer_response
lib/api/v3/queries/queries_api.rb:134 block (3 levels) in <class:QueriesAPI>
```
[https://appsignal.com/openproject-gmbh/sites/681222e4e96fb49ca261a080/exceptions/incidents/507](https://appsignal.com/openproject-gmbh/sites/681222e4e96fb49ca261a080/exceptions/incidents/507)
[https://appsignal.com/openproject-gmbh/sites/681222e4e96fb49ca261a080/exceptions/incidents/508](https://appsignal.com/openproject-gmbh/sites/681222e4e96fb49ca261a080/exceptions/incidents/508)
from local dev instance:
```text
F, [2025-08-19T11:49:27.010595 #46768] FATAL -- :
ActiveRecord::StatementInvalid (PG::UndefinedTable: ERROR: missing FROM-clause entry for table "work_package_journals"
LINE 1: ...l::WorkPackageJournal' AND "journals"."data_id" = "work_pack...
^
)
Caused by: PG::UndefinedTable (ERROR: missing FROM-clause entry for table "work_package_journals"
LINE 1: ...l::WorkPackageJournal' AND "journals"."data_id" = "work_pack...
^
)
Information for: ActiveRecord::StatementInvalid (PG::UndefinedTable: ERROR: missing FROM-clause entry for table "work_package_journals"
LINE 1: ...l::WorkPackageJournal' AND "journals"."data_id" = "work_pack...
^
):
Information for cause: PG::UndefinedTable (ERROR: missing FROM-clause entry for table "work_package_journals"
LINE 1: ...l::WorkPackageJournal' AND "journals"."data_id" = "work_pack...
^
):
lib/api/v3/work_packages/work_package_collection_representer.rb:226:in 'API::V3::WorkPackages::WorkPackageCollectionRepresenter#paged_models'
lib/api/decorators/offset_paginated_collection.rb:46:in 'API::Decorators::OffsetPaginatedCollection#initialize'
lib/api/v3/work_packages/work_package_collection_representer.rb:57:in 'API::V3::WorkPackages::WorkPackageCollectionRepresenter#initialize'
app/services/api/v3/work_package_collection_from_query_service.rb:153:in 'Class#new'
app/services/api/v3/work_package_collection_from_query_service.rb:153:in 'API::V3::WorkPackageCollectionFromQueryService#collection_representer'
app/services/api/v3/work_package_collection_from_query_service.rb:66:in 'API::V3::WorkPackageCollectionFromQueryService#results_to_representer'
app/services/api/v3/work_package_collection_from_query_service.rb:49:in 'API::V3::WorkPackageCollectionFromQueryService#call'
lib/api/v3/queries/helpers/query_representer_response.rb:38:in 'block in API::V3::Queries::Helpers::QueryRepresenterResponse#query_representer_response'
lib/api/root_api.rb:261:in 'API::RootAPI::Helpers#raise_invalid_query_on_service_failure'
lib/api/v3/queries/helpers/query_representer_response.rb:35:in 'API::V3::Queries::Helpers::QueryRepresenterResponse#query_representer_response'
lib/api/v3/queries/queries_by_project_api.rb:52:in 'block (3 levels) in <class:QueriesByProjectAPI>'
```
Full SQL from dev instance error:
```sql
SELECT "work_packages"."id" AS t0_r0,
"work_packages"."type_id" AS t0_r1,
"work_packages"."project_id" AS t0_r2,
"work_packages"."subject" AS t0_r3,
"work_packages"."description" AS t0_r4,
"work_packages"."due_date" AS t0_r5,
"work_packages"."category_id" AS t0_r6,
"work_packages"."status_id" AS t0_r7,
"work_packages"."assigned_to_id" AS t0_r8,
"work_packages"."priority_id" AS t0_r9,
"work_packages"."version_id" AS t0_r10,
"work_packages"."author_id" AS t0_r11,
"work_packages"."lock_version" AS t0_r12,
"work_packages"."done_ratio" AS t0_r13,
"work_packages"."estimated_hours" AS t0_r14,
"work_packages"."created_at" AS t0_r15,
"work_packages"."updated_at" AS t0_r16,
"work_packages"."start_date" AS t0_r17,
"work_packages"."responsible_id" AS t0_r18,
"work_packages"."budget_id" AS t0_r19,
"work_packages"."position" AS t0_r20,
"work_packages"."story_points" AS t0_r21,
"work_packages"."remaining_hours" AS t0_r22,
"work_packages"."derived_estimated_hours" AS t0_r23,
"work_packages"."schedule_manually" AS t0_r24,
"work_packages"."parent_id" AS t0_r25,
"work_packages"."duration" AS t0_r26,
"work_packages"."ignore_non_working_days" AS t0_r27,
"work_packages"."derived_remaining_hours" AS t0_r28,
"work_packages"."derived_done_ratio" AS t0_r29,
"work_packages"."project_phase_definition_id" AS t0_r30,
"projects"."id" AS t1_r0,
"projects"."name" AS t1_r1,
"projects"."description" AS t1_r2,
"projects"."public" AS t1_r3,
"projects"."parent_id" AS t1_r4,
"projects"."created_at" AS t1_r5,
"projects"."updated_at" AS t1_r6,
"projects"."identifier" AS t1_r7,
"projects"."lft" AS t1_r8,
"projects"."rgt" AS t1_r9,
"projects"."active" AS t1_r10,
"projects"."templated" AS t1_r11,
"projects"."status_code" AS t1_r12,
"projects"."status_explanation" AS t1_r13,
"projects"."settings" AS t1_r14,
"projects"."workspace_type" AS t1_r15
FROM "work_packages"
LEFT OUTER JOIN "projects" ON "projects"."id" = "work_packages"."project_id"
WHERE "work_packages"."id" IN
(WITH "member_projects" AS ((
(SELECT "projects"."id",
"members"."entity_id"
FROM "members"
INNER JOIN "projects" ON "projects"."active" = TRUE
AND "members"."project_id" = "projects"."id"
INNER JOIN "enabled_modules" ON "projects"."id" = "enabled_modules"."project_id"
AND "enabled_modules"."name" IN ('work_package_tracking')
AND "projects"."active" = TRUE
INNER JOIN "member_roles" ON "member_roles"."member_id" = "members"."id"
INNER JOIN "roles" ON "roles"."id" = "member_roles"."role_id"
INNER JOIN "role_permissions" ON "roles"."id" = "role_permissions"."role_id"
AND (FALSE
OR "role_permissions"."permission" = 'view_work_packages'
AND "enabled_modules"."name" = 'work_package_tracking')
WHERE "members"."user_id" = 10
AND ("members"."entity_type" IS NULL
OR "members"."entity_type" = 'WorkPackage'))
UNION ALL
(SELECT "projects"."id",
NULL AS entity_id
FROM "projects"
INNER JOIN "enabled_modules" ON "projects"."id" = "enabled_modules"."project_id"
AND "enabled_modules"."name" IN ('work_package_tracking')
AND "projects"."active" = TRUE
INNER JOIN "roles" ON "roles"."builtin" = 1
AND "projects"."active"
AND "projects"."public"
AND NOT (EXISTS
(SELECT 1
FROM "members"
WHERE "members"."user_id" = 10
AND "members"."entity_type" IS NULL
AND "members"."project_id" = "projects"."id"))
INNER JOIN "role_permissions" ON "roles"."id" = "role_permissions"."role_id"
AND (FALSE
OR "role_permissions"."permission" = 'view_work_packages'
AND "enabled_modules"."name" = 'work_package_tracking')))),
"entity_member_projects" AS
(SELECT *
FROM member_projects
WHERE entity_id IS NOT NULL),
"project_member_projects" AS
(SELECT *
FROM member_projects
WHERE entity_id IS NULL),
"entity_member_projects_without_duplicates" AS
(SELECT *
FROM entity_member_projects
WHERE NOT EXISTS
(SELECT 1
FROM project_member_projects
WHERE project_member_projects.id = entity_member_projects.id)),
"allowed_by_projects_and_work_packages" AS
(SELECT *
FROM work_packages
WHERE project_id IN
(SELECT id
FROM member_projects)
AND NOT EXISTS
(SELECT 1
FROM entity_member_projects_without_duplicates
WHERE entity_member_projects_without_duplicates.id = work_packages.project_id
AND entity_member_projects_without_duplicates.entity_id != work_packages.id)) SELECT journals.journable_id AS id
FROM allowed_by_projects_and_work_packages work_packages
INNER JOIN "journals" ON "journals"."data_type" = 'Journal::WorkPackageJournal'
AND "journals"."data_id" = "work_package_journals"."id"
INNER JOIN "statuses" ON "statuses"."id" = "work_package_journals"."status_id"
INNER JOIN
(SELECT id,
created_at
FROM "work_packages") AS journables ON "journables"."id" = "journals"."journable_id"
LEFT OUTER JOIN "projects" ON "projects"."id" = "work_package_journals"."project_id"
WHERE ((statuses.is_closed=FALSE)
AND (projects.id IN (1)))
AND "journals"."journable_type" = 'WorkPackage'
AND (validity_period @> timestamp WITH TIME ZONE '2025-08-18 08:00:00 UTC'
OR validity_period @> timestamp WITH TIME ZONE '2025-08-19 09:55:08 UTC'))
ORDER BY work_packages.id
LIMIT 20
OFFSET 0
``` https://appsignal.com/openproject-gmbh/sites/681222e4e96fb49ca261a080/exceptions/incidents/508
### Steps to reproduce
1. active performance improvement of WorkPackage.visible for large instance (ticket #66405) with `Setting.large_instance_wp_allowed_to_sql = true`
2. Log in as a normal user (will not trigger for admins)
3. Go to work package list view
4. Apply baseline comparison with any value like "yesterday"
1. It can be noticed that the UI does not show any baseline differences. It's because there is already a 500 error, but it's not shown by the UI.
5. Reload the page
### What is the buggy behavior?
* 500 error
* no work packages listed
### What is the expected behavior?
1. no errors and list work package with baseline comparison
### Workaround
Disable the performance trick by changing `large_instance_wp_allowed_to_sql` setting:
* export environment variable `OPENPROJECT_LARGE__INSTANCE__WP__ALLOWED__TO__SQL=false`
### Screenshots
on a dev instance
<img class="op-uc-image op-uc-image_inline" src="/api/v3/attachments/773854/content">
on a prod instance ([community](https://community.openproject.org/projects/openproject/work_packages?query_id=5307) but not reproducible now that `Setting.large_instance_wp_allowed_to_sql` has been set to `false`)
<img class="op-uc-image op-uc-image_inline" src="/api/v3/attachments/773856/content">
### **Logs**
**from AppSignal**
```text
PG::UndefinedTable: ERROR: missing FROM-clause entry for table "work_package_journals"
LINE 1: ...l::WorkPackageJournal' AND "journals"."data_id" = "work_pack...
lib/api/v3/work_packages/work_package_collection_representer.rb:226 API::V3::WorkPackages::WorkPackageCollectionRepresenter#paged_models
lib/api/decorators/offset_paginated_collection.rb:46 API::Decorators::OffsetPaginatedCollection#initialize
lib/api/v3/work_packages/work_package_collection_representer.rb:57 API::V3::WorkPackages::WorkPackageCollectionRepresenter#initialize
app/services/api/v3/work_package_collection_from_query_service.rb:153 Class#new
app/services/api/v3/work_package_collection_from_query_service.rb:153 API::V3::WorkPackageCollectionFromQueryService#collection_representer
app/services/api/v3/work_package_collection_from_query_service.rb:66 API::V3::WorkPackageCollectionFromQueryService#results_to_representer
app/services/api/v3/work_package_collection_from_query_service.rb:49 API::V3::WorkPackageCollectionFromQueryService#call
lib/api/v3/queries/helpers/query_representer_response.rb:38 block in API::V3::Queries::Helpers::QueryRepresenterResponse#query_representer_response
lib/api/root_api.rb:261 API::RootAPI::Helpers#raise_invalid_query_on_service_failure
lib/api/v3/queries/helpers/query_representer_response.rb:35 API::V3::Queries::Helpers::QueryRepresenterResponse#query_representer_response
lib/api/v3/queries/queries_api.rb:134 block (3 levels) in <class:QueriesAPI>
```
[https://appsignal.com/openproject-gmbh/sites/681222e4e96fb49ca261a080/exceptions/incidents/507](https://appsignal.com/openproject-gmbh/sites/681222e4e96fb49ca261a080/exceptions/incidents/507)
[https://appsignal.com/openproject-gmbh/sites/681222e4e96fb49ca261a080/exceptions/incidents/508](https://appsignal.com/openproject-gmbh/sites/681222e4e96fb49ca261a080/exceptions/incidents/508)
from local dev instance:
```text
F, [2025-08-19T11:49:27.010595 #46768] FATAL -- :
ActiveRecord::StatementInvalid (PG::UndefinedTable: ERROR: missing FROM-clause entry for table "work_package_journals"
LINE 1: ...l::WorkPackageJournal' AND "journals"."data_id" = "work_pack...
^
)
Caused by: PG::UndefinedTable (ERROR: missing FROM-clause entry for table "work_package_journals"
LINE 1: ...l::WorkPackageJournal' AND "journals"."data_id" = "work_pack...
^
)
Information for: ActiveRecord::StatementInvalid (PG::UndefinedTable: ERROR: missing FROM-clause entry for table "work_package_journals"
LINE 1: ...l::WorkPackageJournal' AND "journals"."data_id" = "work_pack...
^
):
Information for cause: PG::UndefinedTable (ERROR: missing FROM-clause entry for table "work_package_journals"
LINE 1: ...l::WorkPackageJournal' AND "journals"."data_id" = "work_pack...
^
):
lib/api/v3/work_packages/work_package_collection_representer.rb:226:in 'API::V3::WorkPackages::WorkPackageCollectionRepresenter#paged_models'
lib/api/decorators/offset_paginated_collection.rb:46:in 'API::Decorators::OffsetPaginatedCollection#initialize'
lib/api/v3/work_packages/work_package_collection_representer.rb:57:in 'API::V3::WorkPackages::WorkPackageCollectionRepresenter#initialize'
app/services/api/v3/work_package_collection_from_query_service.rb:153:in 'Class#new'
app/services/api/v3/work_package_collection_from_query_service.rb:153:in 'API::V3::WorkPackageCollectionFromQueryService#collection_representer'
app/services/api/v3/work_package_collection_from_query_service.rb:66:in 'API::V3::WorkPackageCollectionFromQueryService#results_to_representer'
app/services/api/v3/work_package_collection_from_query_service.rb:49:in 'API::V3::WorkPackageCollectionFromQueryService#call'
lib/api/v3/queries/helpers/query_representer_response.rb:38:in 'block in API::V3::Queries::Helpers::QueryRepresenterResponse#query_representer_response'
lib/api/root_api.rb:261:in 'API::RootAPI::Helpers#raise_invalid_query_on_service_failure'
lib/api/v3/queries/helpers/query_representer_response.rb:35:in 'API::V3::Queries::Helpers::QueryRepresenterResponse#query_representer_response'
lib/api/v3/queries/queries_by_project_api.rb:52:in 'block (3 levels) in <class:QueriesByProjectAPI>'
```
Full SQL from dev instance error:
```sql
SELECT "work_packages"."id" AS t0_r0,
"work_packages"."type_id" AS t0_r1,
"work_packages"."project_id" AS t0_r2,
"work_packages"."subject" AS t0_r3,
"work_packages"."description" AS t0_r4,
"work_packages"."due_date" AS t0_r5,
"work_packages"."category_id" AS t0_r6,
"work_packages"."status_id" AS t0_r7,
"work_packages"."assigned_to_id" AS t0_r8,
"work_packages"."priority_id" AS t0_r9,
"work_packages"."version_id" AS t0_r10,
"work_packages"."author_id" AS t0_r11,
"work_packages"."lock_version" AS t0_r12,
"work_packages"."done_ratio" AS t0_r13,
"work_packages"."estimated_hours" AS t0_r14,
"work_packages"."created_at" AS t0_r15,
"work_packages"."updated_at" AS t0_r16,
"work_packages"."start_date" AS t0_r17,
"work_packages"."responsible_id" AS t0_r18,
"work_packages"."budget_id" AS t0_r19,
"work_packages"."position" AS t0_r20,
"work_packages"."story_points" AS t0_r21,
"work_packages"."remaining_hours" AS t0_r22,
"work_packages"."derived_estimated_hours" AS t0_r23,
"work_packages"."schedule_manually" AS t0_r24,
"work_packages"."parent_id" AS t0_r25,
"work_packages"."duration" AS t0_r26,
"work_packages"."ignore_non_working_days" AS t0_r27,
"work_packages"."derived_remaining_hours" AS t0_r28,
"work_packages"."derived_done_ratio" AS t0_r29,
"work_packages"."project_phase_definition_id" AS t0_r30,
"projects"."id" AS t1_r0,
"projects"."name" AS t1_r1,
"projects"."description" AS t1_r2,
"projects"."public" AS t1_r3,
"projects"."parent_id" AS t1_r4,
"projects"."created_at" AS t1_r5,
"projects"."updated_at" AS t1_r6,
"projects"."identifier" AS t1_r7,
"projects"."lft" AS t1_r8,
"projects"."rgt" AS t1_r9,
"projects"."active" AS t1_r10,
"projects"."templated" AS t1_r11,
"projects"."status_code" AS t1_r12,
"projects"."status_explanation" AS t1_r13,
"projects"."settings" AS t1_r14,
"projects"."workspace_type" AS t1_r15
FROM "work_packages"
LEFT OUTER JOIN "projects" ON "projects"."id" = "work_packages"."project_id"
WHERE "work_packages"."id" IN
(WITH "member_projects" AS ((
(SELECT "projects"."id",
"members"."entity_id"
FROM "members"
INNER JOIN "projects" ON "projects"."active" = TRUE
AND "members"."project_id" = "projects"."id"
INNER JOIN "enabled_modules" ON "projects"."id" = "enabled_modules"."project_id"
AND "enabled_modules"."name" IN ('work_package_tracking')
AND "projects"."active" = TRUE
INNER JOIN "member_roles" ON "member_roles"."member_id" = "members"."id"
INNER JOIN "roles" ON "roles"."id" = "member_roles"."role_id"
INNER JOIN "role_permissions" ON "roles"."id" = "role_permissions"."role_id"
AND (FALSE
OR "role_permissions"."permission" = 'view_work_packages'
AND "enabled_modules"."name" = 'work_package_tracking')
WHERE "members"."user_id" = 10
AND ("members"."entity_type" IS NULL
OR "members"."entity_type" = 'WorkPackage'))
UNION ALL
(SELECT "projects"."id",
NULL AS entity_id
FROM "projects"
INNER JOIN "enabled_modules" ON "projects"."id" = "enabled_modules"."project_id"
AND "enabled_modules"."name" IN ('work_package_tracking')
AND "projects"."active" = TRUE
INNER JOIN "roles" ON "roles"."builtin" = 1
AND "projects"."active"
AND "projects"."public"
AND NOT (EXISTS
(SELECT 1
FROM "members"
WHERE "members"."user_id" = 10
AND "members"."entity_type" IS NULL
AND "members"."project_id" = "projects"."id"))
INNER JOIN "role_permissions" ON "roles"."id" = "role_permissions"."role_id"
AND (FALSE
OR "role_permissions"."permission" = 'view_work_packages'
AND "enabled_modules"."name" = 'work_package_tracking')))),
"entity_member_projects" AS
(SELECT *
FROM member_projects
WHERE entity_id IS NOT NULL),
"project_member_projects" AS
(SELECT *
FROM member_projects
WHERE entity_id IS NULL),
"entity_member_projects_without_duplicates" AS
(SELECT *
FROM entity_member_projects
WHERE NOT EXISTS
(SELECT 1
FROM project_member_projects
WHERE project_member_projects.id = entity_member_projects.id)),
"allowed_by_projects_and_work_packages" AS
(SELECT *
FROM work_packages
WHERE project_id IN
(SELECT id
FROM member_projects)
AND NOT EXISTS
(SELECT 1
FROM entity_member_projects_without_duplicates
WHERE entity_member_projects_without_duplicates.id = work_packages.project_id
AND entity_member_projects_without_duplicates.entity_id != work_packages.id)) SELECT journals.journable_id AS id
FROM allowed_by_projects_and_work_packages work_packages
INNER JOIN "journals" ON "journals"."data_type" = 'Journal::WorkPackageJournal'
AND "journals"."data_id" = "work_package_journals"."id"
INNER JOIN "statuses" ON "statuses"."id" = "work_package_journals"."status_id"
INNER JOIN
(SELECT id,
created_at
FROM "work_packages") AS journables ON "journables"."id" = "journals"."journable_id"
LEFT OUTER JOIN "projects" ON "projects"."id" = "work_package_journals"."project_id"
WHERE ((statuses.is_closed=FALSE)
AND (projects.id IN (1)))
AND "journals"."journable_type" = 'WorkPackage'
AND (validity_period @> timestamp WITH TIME ZONE '2025-08-18 08:00:00 UTC'
OR validity_period @> timestamp WITH TIME ZONE '2025-08-19 09:55:08 UTC'))
ORDER BY work_packages.id
LIMIT 20
OFFSET 0
```