Group search fails when Group Category is specified

Bug #1836325 reported by Ghada El-Zoghbi on 2019-07-12
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Mahara
High
Ghada El-Zoghbi
19.04
High
Unassigned
19.10
High
Ghada El-Zoghbi

Bug Description

Mahara: 19.04.0
Linux: 16.04
DB: Postgres
Browser: FF

If a group search is conducted when a group with a '.' in the name and a category is specified, the following error occurs:

WAR] 3c (lib/errors.php:858) Failed to get a recordset: postgres8 error: [-1: ERROR: invalid input syntax for integer: "19.04"] in EXECUTE("SELECT COUNT(*) FROM "group" g
            INNER JOIN (
                SELECT g.id, 'admin' AS membershiptype, '' AS reason, 'admin' AS role
                FROM "group" g
                INNER JOIN "group_member" gm ON (gm.group = g.id AND gm.member = ? AND gm.role = 'admin')
                UNION
                SELECT g.id, 'member' AS membershiptype, '' AS reason, gm.role AS role
                FROM "group" g
                INNER JOIN "group_member" gm ON (g.id = gm.group AND gm.member = ? AND gm.role != 'admin')
                UNION
                SELECT g.id, 'invite' AS membershiptype, gmi.reason, gmi.role
                FROM "group" g
                INNER JOIN "group_member_invite" gmi ON (gmi.group = g.id AND gmi.member = ?)
                UNION SELECT g.id, 'request' AS membershiptype, gmr.reason, '' AS role
                FROM "group" g
                INNER JOIN "group_member_request" gmr ON (gmr.group = g.id AND gmr.member = ?)
            ) t ON t.id = g.id WHERE g.deleted = ?
            AND (
                g.name ILIKE '%' || ? || '%'
                OR g.description ILIKE '%' || ? || '%'
                OR g.shortname ILIKE '%' || ? || '%'
            ) AND g.category = ?")Command was: SELECT COUNT(*) FROM "group" g
            INNER JOIN (
                SELECT g.id, 'admin' AS membershiptype, '' AS reason, 'admin' AS role
                FROM "group" g
                INNER JOIN "group_member" gm ON (gm.group = g.id AND gm.member = ? AND gm.role = 'admin')
                UNION
                SELECT g.id, 'member' AS membershiptype, '' AS reason, gm.role AS role
                FROM "group" g
                INNER JOIN "group_member" gm ON (g.id = gm.group AND gm.member = ? AND gm.role != 'admin')
                UNION
                SELECT g.id, 'invite' AS membershiptype, gmi.reason, gmi.role
                FROM "group" g
                INNER JOIN "group_member_invite" gmi ON (gmi.group = g.id AND gmi.member = ?)
                UNION SELECT g.id, 'request' AS membershiptype, gmr.reason, '' AS role
                FROM "group" g
                INNER JOIN "group_member_request" gmr ON (gmr.group = g.id AND gmr.member = ?)
            ) t ON t.id = g.id WHERE g.deleted = ?
            AND (
                g.name ILIKE '%' || ? || '%'
                OR g.description ILIKE '%' || ? || '%'
                OR g.shortname ILIKE '%' || ? || '%'
            ) AND g.category = ? and values was (0:2,1:2,2:2,3:2,4:0,5:2,6:19.04,7:19.04,8:19.04)
Call stack (most recent first):

    log_message("Failed to get a recordset: postgres8 error: [-1: E...", 8, true, true) at /var/www/mahara/htdocs/lib/errors.php:95
    log_warn("Failed to get a recordset: postgres8 error: [-1: E...") at /var/www/mahara/htdocs/lib/errors.php:858
    SQLException->__construct("Failed to get a recordset: postgres8 error: [-1: E...") at /var/www/mahara/htdocs/lib/dml.php:517
    get_recordset_sql("SELECT COUNT(*) FROM "group" g INNER ...", array(size 9)) at /var/www/mahara/htdocs/lib/dml.php:276
    count_records_sql("SELECT COUNT(*) FROM {group} g INNER ...", array(size 9)) at /var/www/mahara/htdocs/lib/group.php:2288
    group_get_associated_groups("2", "all", 10, 0, 2, "19.04") at /var/www/mahara/htdocs/group/index.php:146

The issue is not due to the '.' in the group name but rather because the SQL is incorrectly applying the values. i.e. $catsql and $query_where are concatenated in the wrong order in group_get_associated_groups().

Ghada El-Zoghbi (ghada-z) wrote :

To reproduce:

1. Ensure group categories are enabled (and enter a few categories: Category 1, Category 2, etc)
2. Create a group with name "19.04 test group"
3. Search on the group:
- search = 19.04
- category = Category 1
4. Click Search

What happens now:
You will be presented with the above error

What should happen:
The page should display a list of matching groups (or none)

Changed in mahara:
assignee: nobody → Ghada El-Zoghbi (ghada-z)
Steven (stevens-q) wrote :

Commit hash: fa5752090fb6e1728da71afc3e03e63aa31dda26
Environment tested: Master
Browser tested: Chrome
Theme used: Raw

PRECONDITIONS:
------------------------
1) Groups Exists - various groups with various settings
2 Group categories are enabled and Categories exist as follows
   a) Category 1
   b) Category 2
   c) Category 3
3) Group Exists as follows:
   a) Name = 19.04 Test Group
   b) Open = Yes
   c) Roles = Standard: ...
   d) Group category = Category 1
4) Mahara user exists (User1 = no roles)

TEST STEPS:
------------------------
1) Log in as Mahara User1
2) Browse to Main menu > Engage > Groups
3) Enter the search parameter = 19.04
4) Enter the Group category = Category 1
5) Click the Search button
6) Verify that the 19.04 Test Group is displayed in the results table ✔
7) Verify that there are no error messages ✔

Catalyst QA Approved ✔

Reviewed: https://reviews.mahara.org/10156
Committed: https://git.mahara.org/mahara/mahara/commit/fa5752090fb6e1728da71afc3e03e63aa31dda26
Submitter: Robert Lyon (<email address hidden>)
Branch: master

commit fa5752090fb6e1728da71afc3e03e63aa31dda26
Author: Ghada El-Zoghbi <email address hidden>
Date: Fri Jul 12 17:41:09 2019 +1000

Bug bug1836325: fix error with group search

If a group category is specified in the search,
it was failing due to incorrect order of values
passed to the SQL query.

Sponsored by The Australian National University

behatnotneeded

Change-Id: I53b822c16e9316d572b018c33f44deea53650845

Robert Lyon (robertl-9) on 2019-07-16
Changed in mahara:
milestone: none → 19.10.0
status: New → Fix Committed
importance: Undecided → High

Reviewed: https://reviews.mahara.org/10164
Committed: https://git.mahara.org/mahara/mahara/commit/8f26dfc2ed591bad86acb760df016d1ef553e10b
Submitter: Robert Lyon (<email address hidden>)
Branch: 19.04_STABLE

commit 8f26dfc2ed591bad86acb760df016d1ef553e10b
Author: Ghada El-Zoghbi <email address hidden>
Date: Fri Jul 12 17:41:09 2019 +1000

Bug bug1836325: fix error with group search

If a group category is specified in the search,
it was failing due to incorrect order of values
passed to the SQL query.

Sponsored by The Australian National University

behatnotneeded

Change-Id: I53b822c16e9316d572b018c33f44deea53650845
(cherry picked from commit fa5752090fb6e1728da71afc3e03e63aa31dda26)

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers