ISISComputingGroup / ISISComputingGroup/IBEX
GUI: Gui does group by and then references items which it shouldn't
- 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.