It is a common problem that postgres is very strict on GROUP BY clauses. This function is constructing a query with 'GROUP BY uvs '. I am trying to figure out what is the intention of this GROUP BY and replace it with other functions which will make postgres happy.
I understand it as distincting the results by field uvs.
Any thoughts or explainations?
Martin L just sent me a message
MD and I reworked the assignment module to use the new table class by PJ. We based the code on /mod/quiz/attempts.php including the SQL code
I'm no SQL expert so if you can find a way to make it work without the GROUP BY clause then you are welcome.
The uvs record entry will be something like (for userid = 34): 34#34 or 34#0 if there is no entry in the submission table for that user.
All other record entries should be straight forward except for status which is an SQL boolean (0 or 1)
The intent of the original code in /mod/quiz/attempts.php is to return one row for each attempt that was made on the quiz (many rows for many attempts by the same user) plus one row for each user that did not attempt the quiz at all (actually this doesn't happen all the time since there are options in the WHERE clause to make the query more restrictive, but I 'm talking about the generalized case here).
So far so good, this wouldn't need that funky uvsa field or a GROUP BY because joining mdl_user with mdl_quiz_attempts gives one or more rows per user as desired. However, if the teacher has selected to view marks for each question in detail (or have the DB sort by one of those marks), the issue gets complicated. In that case we need to JOIN the abone with quiz_states, which produces multiple rows for each user/quiz pair. We have to get that down to only one row.
And the solution to get that down to only one is construct the artificial field uvsa, which is (userid)#(attemptid), and GROUP BY it. This will produce the desired effect.
I admit that this isn't the most easy to read code on the planet but it seemed to be the perfect solution for the occasion.
The assignment SQL doesn't seem have those exotic requirements, so the GROUP BY can probably be removed from there. I 'll do that myself (the powers above help us).
Patrick's original report applies equally well to the quiz code though. What exactly is the problem with Postgres? I have very very little experience with that db...
What exactly is the problem with Postgres? I have very very little experience with that db...
It is not a problem with Postgres per se, but with the use of GROUP BY. Let me explain.
When you use GROUP BY, you have to explicitly define how you want each field treated. For example, if you select 4 fields (A, B, C, D), and you want to GROUP BY A,B of them you have to indicate how the other 2 must be resolved. Because when the query is resolved, it will only return rows where the AB pair is unique. So how does it get C and D in a deterministic way?
(SQL is designed to be deterministic, btw).
So you use aggregate functions: max(C), count(C), avg(C), etc. By definition, if using aggregate functions doesn't make sense for the query... you shouldn't be using group by (distinct perhaps?) or you don't need the field.
MySQL is quite strange in that it accepts queries with non-sensical GROUP BY, and then it returns one of the possible values for C and D. One, any random one. This is non-deterministic, and completely anathema to SQL. If you count on this MySQL behaviour, it'll end up messing up in weird ways.
So... Postgres is tight with regards to GROUP BY because it wants to know what you want from the query. And it is a good thing
So, in that particular query... do you need to use GROUP BY or perhaps you need drop some fields from the query and just use DISTINCT. If you are getting id just to satisfy Moodle's get records sql() expectation of having id as the first field, then use max(id) (or min(id)) and you'll have disambiguated the query.
That should teach me for snubbing Java people who use Strings and garbage collectors without knowing how the stuff actually works behind the scenes.
This specific issue with Assignment was a piece of cake to fix because the complexity copied over from Quiz wasn't needed here (GROUP BY included). The situation over at Quiz is a bit more grim though, as I discovered that apart from the SQL being invalid, the query stopped working correctly after the Big Quiz Refactoring changed table structures. Further discussion for that issue over here.
My thanks also to Martin for the clear explanation of the GROUP BY clause. Every time I read an SQL manual my head turns to mush