https://requirements-bazaar.org/projects/2/categories/2 throws a SQL error

[klamma]

SQL [select "public"."requirement"."id", "public"."requirement"."name", "public"."requirement"."description", "public"."requirement"."realized", "public"."requirement"."creation_date", "public"."requirement"."last_updated_date", "public"."requirement"."lead_developer_id", "public"."requirement"."creator_id", "public"."requirement"."project_id", "public"."requirement"."additional_properties", "public"."requirement"."last_updating_user_id", (select count() from "public"."requirement" where ("public"."requirement"."name" ilike ? and "public"."requirement_category_map"."category_id" = ?)) as "idCount", (select count() from "public"."comment" where "public"."comment"."requirement_id" = "public"."requirement"."id") as "commentCount", (select count() from "public"."attachment" where "public"."attachment"."requirement_id" = "public"."requirement"."id") as "attachmentCount", (select count() from "public"."requirement_follower_map" where "public"."requirement_follower_map"."requirement_id" = "public"."requirement"."id") as "followerCount", (select count() from "public"."requirement_follower_map" where ("public"."requirement_follower_map"."requirement_id" = "public"."requirement"."id" and "public"."requirement_follower_map"."user_id" = ?)) as "isFollower", (select count() from "public"."requirement_developer_map" where ("public"."requirement_developer_map"."requirement_id" = "public"."requirement"."id" and "public"."requirement_developer_map"."user_id" = ?)) as "isDeveloper", (select sum(case when "public"."requirement"."creator_id" = ? then 1 when "public"."requirement"."lead_developer_id" = ? then 1 when "public"."requirement_developer_map"."user_id" = ? then 1 when "public"."comment"."user_id" = ? then 1 when "public"."attachment"."user_id" = ? then 1 else 0 end) from "public"."requirement" left outer join "public"."requirement_developer_map" on "public"."requirement_developer_map"."requirement_id" = "public"."requirement"."id" left outer join "public"."comment" on "public"."comment"."requirement_id" = "public"."requirement"."id" left outer join "public"."attachment" on "public"."attachment"."requirement_id" = "public"."requirement"."id") as "isContributor", "creatorUser"."id", "creatorUser"."first_name", "creatorUser"."last_name", "creatorUser"."email", "creatorUser"."las2peer_id", "creatorUser"."user_name", "creatorUser"."profile_image", "creatorUser"."email_lead_subscription", "creatorUser"."email_follow_subscription", "creatorUser"."creation_date", "creatorUser"."last_updated_date", "creatorUser"."last_login_date", "creatorUser"."personalization_enabled", "lastUpdatingUser"."id", "lastUpdatingUser"."first_name", "lastUpdatingUser"."last_name", "lastUpdatingUser"."email", "lastUpdatingUser"."las2peer_id", "lastUpdatingUser"."user_name", "lastUpdatingUser"."profile_image", "lastUpdatingUser"."email_lead_subscription", "lastUpdatingUser"."email_follow_subscription", "lastUpdatingUser"."creation_date", "lastUpdatingUser"."last_updated_date", "lastUpdatingUser"."last_login_date", "lastUpdatingUser"."personalization_enabled", "leadDeveloperUser"."id", "leadDeveloperUser"."first_name", "leadDeveloperUser"."last_name", "leadDeveloperUser"."email", "leadDeveloperUser"."las2peer_id", "leadDeveloperUser"."user_name", "leadDeveloperUser"."profile_image", "leadDeveloperUser"."email_lead_subscription", "leadDeveloperUser"."email_follow_subscription", "leadDeveloperUser"."creation_date", "leadDeveloperUser"."last_updated_date", "leadDeveloperUser"."last_login_date", "leadDeveloperUser"."personalization_enabled", "public"."project"."id", "public"."project"."name", "public"."project"."description", "public"."project"."visibility", "public"."project"."creation_date", "public"."project"."last_updated_date", "public"."project"."leader_id", "public"."project"."default_category_id", "public"."project"."additional_properties", (select "alias_80572253"."last_activity" from (select "ACTIVITY"."id", "ACTIVITY"."last_activity", "ACTIVITY"."last_activity_user_id" from (select "public"."requirement"."id", "public"."requirement"."creation_date" as "last_activity", "public"."requirement"."creator_id" as "last_activity_user_id" from "public"."requirement" union all select "public"."requirement"."id", "public"."requirement"."last_updated_date", "public"."requirement"."last_updating_user_id" from "public"."requirement" union all select "public"."comment"."requirement_id", "public"."comment"."creation_date", "public"."comment"."user_id" from "public"."comment" union all select "public"."comment"."requirement_id", "public"."comment"."last_updated_date", "public"."comment"."user_id" from "public"."comment" union all select "public"."attachment"."requirement_id", "public"."attachment"."creation_date", "public"."attachment"."user_id" from "public"."attachment" union all select "public"."attachment"."requirement_id", "public"."attachment"."last_updated_date", "public"."attachment"."user_id" from "public"."attachment") as "ACTIVITY" where ("ACTIVITY"."id", "ACTIVITY"."last_activity") in (select "ACTIVITY"."id", max("ACTIVITY"."last_activity") from (select "public"."requirement"."id", "public"."requirement"."creation_date" as "last_activity", "public"."requirement"."creator_id" as "last_activity_user_id" from "public"."requirement" union all select "public"."requirement"."id", "public"."requirement"."last_updated_date", "public"."requirement"."last_updating_user_id" from "public"."requirement" union all select "public"."comment"."requirement_id", "public"."comment"."creation_date", "public"."comment"."user_id" from "public"."comment" union all select "public"."comment"."requirement_id", "public"."comment"."last_updated_date", "public"."comment"."user_id" from "public"."comment" union all select "public"."attachment"."requirement_id", "public"."attachment"."creation_date", "public"."attachment"."user_id" from "public"."attachment" union all select "public"."attachment"."requirement_id", "public"."attachment"."last_updated_date", "public"."attachment"."user_id" from "public"."attachment") as "ACTIVITY" group by "ACTIVITY"."id")) as "alias_80572253" where "alias_80572253"."id" = "public"."requirement"."id") as "lastActivity", "alias_80572253"."last_activity_user_id", "lastActivityUser"."id", "lastActivityUser"."first_name", "lastActivityUser"."last_name", "lastActivityUser"."email", "lastActivityUser"."las2peer_id", "lastActivityUser"."user_name", "lastActivityUser"."profile_image", "lastActivityUser"."email_lead_subscription", "lastActivityUser"."email_follow_subscription", "lastActivityUser"."creation_date", "lastActivityUser"."last_updated_date", "lastActivityUser"."last_login_date", "lastActivityUser"."personalization_enabled" from "public"."requirement" join "public"."user" as "creatorUser" on "creatorUser"."id" = "public"."requirement"."creator_id" left outer join "public"."user" as "lastUpdatingUser" on "lastUpdatingUser"."id" = "public"."requirement"."last_updating_user_id" left outer join "public"."user" as "leadDeveloperUser" on "leadDeveloperUser"."id" = "public"."requirement"."lead_developer_id" left outer join "public"."project" on "public"."project"."id" = "public"."requirement"."project_id" left outer join (select "ACTIVITY"."id", "ACTIVITY"."last_activity", "ACTIVITY"."last_activity_user_id" from (select "public"."requirement"."id", "public"."requirement"."creation_date" as "last_activity", "public"."requirement"."creator_id" as "last_activity_user_id" from "public"."requirement" union all select "public"."requirement"."id", "public"."requirement"."last_updated_date", "public"."requirement"."last_updating_user_id" from "public"."requirement" union all select "public"."comment"."requirement_id", "public"."comment"."creation_date", "public"."comment"."user_id" from "public"."comment" union all select "public"."comment"."requirement_id", "public"."comment"."last_updated_date", "public"."comment"."user_id" from "public"."comment" union all select "public"."attachment"."requirement_id", "public"."attachment"."creation_date", "public"."attachment"."user_id" from "public"."attachment" union all select "public"."attachment"."requirement_id", "public"."attachment"."last_updated_date", "public"."attachment"."user_id" from "public"."attachment") as "ACTIVITY" where ("ACTIVITY"."id", "ACTIVITY"."last_activity") in (select "ACTIVITY"."id", max("ACTIVITY"."last_activity") from (select "public"."requirement"."id", "public"."requirement"."creation_date" as "last_activity", "public"."requirement"."creator_id" as "last_activity_user_id" from "public"."requirement" union all select "public"."requirement"."id", "public"."requirement"."last_updated_date", "public"."requirement"."last_updating_user_id" from "public"."requirement" union all select "public"."comment"."requirement_id", "public"."comment"."creation_date", "public"."comment"."user_id" from "public"."comment" union all select "public"."comment"."requirement_id", "public"."comment"."last_updated_date", "public"."comment"."user_id" from "public"."comment" union all select "public"."attachment"."requirement_id", "public"."attachment"."creation_date", "public"."attachment"."user_id" from "public"."attachment" union all select "public"."attachment"."requirement_id", "public"."attachment"."last_updated_date", "public"."attachment"."user_id" from "public"."attachment") as "ACTIVITY" group by "ACTIVITY"."id")) as "alias_80572253" on "public"."requirement"."id" = "alias_80572253"."id" left outer join "public"."user" as "lastActivityUser" on "lastActivityUser"."id" = "alias_80572253"."last_activity_user_id" left outer join "public"."requirement_category_map" on "public"."requirement"."id" = "public"."requirement_category_map"."requirement_id" where ("public"."requirement"."name" ilike ? and "public"."requirement_category_map"."category_id" = ? and "public"."requirement"."project_id" in (select "public"."project"."id" from "public"."project" where ("public"."project"."id" = "public"."requirement"."project_id" and ("public"."project"."visibility" = true or "public"."project"."leader_id" = ?)))) limit ? offset ?]; ERROR: more than one row returned by a subquery used as an expression

[fxjordan]

Similar error for request https://requirements-bazaar.org/projects/14/categories/170

[fxjordan]

A LIMIT 1 in the 'lastActivity' subquery solves the error. However, the root cause is that we have multiple results for the same requirement

[fxjordan]

Problem summary for future reference:

We need to GROUP BY the last_activity results AGAIN and use MAX(last_activity) and MAX(last_activity_user_id)!

Otherwise, we can get:

• SQL subquery errors (subquery returning more than one result)
• duplicate requirements in responses if the following holds (this was a problem, e.g. with the ReqBaz project):
• a requirement has no last_updating_user (legacy reasons: value is -1)
• the creation_date and last_updated_date are equal (exact) This results in the TWO candidates matching the MAX last_activity during the JOIN, which would not be a problem if they did not have different user IDs.

This was horrible to debug, but that's the downside of using JOOQ with such large SQL queries as here: https://github.com/rwth-acis/RequirementsBazaar/blob/fa212d02dc30b064ca5285e3d66f05c7bc4b5df6/reqbaz/src/main/java/de/rwth/dbis/acis/bazaar/service/dal/repositories/RequirementRepositoryImpl.java#L151