fixing an error in a moodle ad-hoc query

One of the ad-hoc queries in one of our moodle servers was returning "Error reading from database" when executing certain queries with certain parameters, working with other parameters.

Troubleshooting - The general issue is that with ad-hoc queries, only the generic error "Error reading from database" is shown, and not the exact cause of the error, which would be some problem with the SQL query. Running the same query using DBeaver, (I had to find/replace { with ourprefix_ and } with a space) 

I get the more helpful error message

SQL Error [1242] [21000]: Subquery returns more than 1 row

Commenting out all except the first subquery, I still got the error. So, there was an issue is with the first subquery.

Looking at the table ourprefix_feedback_value I saw that there are some entries where the name field with match

like '%Chapter%' but the typ field is not a textfield.

Assuming that this is what is causing the problem, I modified the query by adding an additional line in bold below

where

fc.timemodified = fc2.timemodified

and fi2.name like '%Chapter%'

and fi2.typ = 'textfield') as "Chapter name",

This also did not solve the issue. Then, checked the ourprefix_feedback_completed table looking for duplicates in the timestamps like

select fc3.timemodified, count(fc3.timemodified)

from

ourprefix_feedback_value  fv3

left join vv_feedback_item  fi3 on fi3.id = fv3.item

left join vv_feedback_completed  fc3 on fc3.id = fv3.completed

where

fi3.name like '%Main%Script%'

GROUP BY fc3.timemodified

HAVING COUNT(fc3.timemodified) > 1

found 1646480361 was repeated twice. Two feedback items had exactly the same timestamp. Changed the second one to 1646480362 and the problem was solved.