ISISComputingGroup / ISISComputingGroup/IBEX

GUI: Gui does group by and then references items which it shouldn't

Open
#5,138 1 comment 0 reactions 0 assignees View on GitHub
bug
Dominant language
No language data
Stars
6
Forks
2
Avg merge
16h 40m
Merged PRs (30d)
2

Description

When searching for RB numbers the GUI performs the query:

```
use exp_data;

select experimentteams.experimentID, user.Name, experiment.StartDate,
user.Organisation, role.Name
from user, role, experiment, experimentteams
where role.roleID = experimentteams.roleID
and user.userID = experimentteams.UserID
and experiment.startDate = experimentteams.startDate
and user.Name like '%%'
group by experimentteams.experimentid, experiment.startDate, user.Name
```

see uk.ac.stfc.isis.ibex.experimentdetails.database.ExperimentIDQuery.buildSQL (line 73)

It should not user terms in the select which don't appear in the group by or are not aggregated. This is turned off by out my.ini. We should turn this back on and fix this query maybe by taking the maximum role and organisation name (partial solution?)

### Acceptance Criteria

- The SQL query no longer contains references to entitites which are either not agregatted or not in the group by.
- The SQL query uses bind parameters for user data
- It is decided whether to add `sql_mode=only_full_group_by`

### Notes

Found when the database on DEMO didn't have standard my.ini file. It complained of:

```
*2020-01-30 18:09:02.961 [main] ERROR uk.ac.stfc.isis.ibex.experimentdetails.database.SearchModel - Error searching experiments: Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'exp_data.user.organisation' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
```

Contributor guide

No contributing guide indexed for this repository

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.