Details
-
Type:
Bug
-
Status:
Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: 1.7, 1.7.1, 1.8
-
Component/s: Database SQL/XMLDB
-
Labels:None
-
Environment:Solaris 10,
Oracle 10g,
Firefox 2.0.0.2
-
Database:Oracle
-
URL:/mod/assignment/submissions.php?id=x
-
Affected Branches:MOODLE_17_STABLE, MOODLE_18_STABLE
-
Fixed Branches:MOODLE_17_STABLE, MOODLE_18_STABLE, MOODLE_19_STABLE
Description
Some of our students have uploaded files, but neither teachers nor admins can view these files...
(/mod/assignment/submissions.php?id=x)
The following ORA error (see screenshot) is displayed, if developer-messages is switched on.
ORA-00918: Spalte nicht eindeutig definiert (column ambiguously defined)
SELECT u.id, u.id, u.firstname, u.lastname, u.picture,
s.id AS submissionid, s.grade, s.submissioncomment,
s.timemodified, s.timemarked FROM m_user u LEFT JOIN m_assignment_submissions s ON u.id = s.userid
AND s.assignment = 1 WHERE u.id IN (4,5) ORDER BY lastname ASC
- line 677 of lib/dmllib.php: call to debugging()
- line 918 of lib/dmllib.php: call to get_recordset_sql()
- line 1116 of mod/assignment/lib.php: call to get_records_sql()
- line 560 of mod/assignment/lib.php: call to assignment_base->display_submissions()
- line 43 of mod/assignment/submissions.php: call to assignment_base->submissions()
Activity
Aha,
more info. Found at: http://opensource.atlassian.com/projects/hibernate/browse/HB-996
It seems that Oracle supports duplicate column names but, if they are used in nested queries, it throws the above error ORA-00918.
Inside Moodle, we use to wrap that query inside an outer one to provide pagination, so it sounds possible that the duplicate "u.id" is the cause.
Can you simply delete one of them and check that everything works ok? If so, I'll fix it in CVS ASAP. Thanks!
Ciao 
We have tested a bit ourselves and have also found out, that the deleting of one u.id indeed solves the problem!
[line 1100 /mod/assignment/lib.php]
Could you please take that out?
Btw, I have one tiny question is there only the developer version (now 1.9) available from cvs or are there other versions, too?
e.g. version 1.8.1 (or is 1.8.1 the daily build version from download.moodle.org?)
Thx a lot!
Ohh, theres another question! We have spotted (and anticipate) several other ORA errors, shall we put these all in the tracker or send them to a particular developer?
is there only the developer version (now 1.9) available from cvs or are there other versions, too?
e.g. version 1.8.1 (or is 1.8.1 the daily build version from download.moodle.org?)
Thx a lot!
Ohh, theres another question! We have spotted (and anticipate) several other ORA errors, shall we put these all in the tracker or send them to a particular developer? Hi Dennis,
thanks, I'll comit that change to 1.7, 1.8 and HEAD in the very next hours.
About available versions, each release has it own branch (MOODLE_17_STABLE, MOODLE_18_STABLE) that is created when a new Beta is released. All changes inside each version (1.7.x, 18.x) are performed in that branch. And the trunk (HEAD) is used to develop the next version (1.9 right now), that will be branched (MOODLE_19_STABLE) once 1.9beta is released.
So, yes, all the changes in MOODLE_18_STABLE branch (where the 1.8.x series is maintained) are acummulative and you can find them in nighly builds (1.8.x+). Minor releases (1.8.1, 1.8.2) use to happen when those acummulative changes recomends that (they are, points, in the evolution of the branch, but nothing else). So, updating to the lastest nighly 1.8.x+ release is the best alternative IMO.
And about the rest of ORA problems, absolutely! Please assign them to the Database/XMLDB category (and, optionally, to the real one - assignment, enrol....). They should be assigned to me (or, if you can, assign them to me directly). All those reposts will be really welcome (one of the major problems we are having with the Oracle version is the lack of feedback for now). Really Welcome!! B-)
Ciao
Hi Dennis,
it's in CVS (17_STABLE, 18_STABLE and HEAD) now! So it'll be available in next nightly build. It uses to be available when marked as resolved (I was going to commit it last Saturday, but finally went out) 
Thanks and ciao
Thanks and ciao 
Hi Dennis,
I've executed the SQL above against one Oracle10g box and it runs perfectly for me!
Also, all columns in the query have their table prefix ("u" for m_user and "s" for m_assignment_submissions). The only one not having that is the "lastname" used in the ORDER BY clause, but it shouldn't be a problem at all, because such column name is only present in the m_user table.
Uhm... can you confirm that your m_assignment_submissions table haven't such lastname column? Also, just to try it, could you take out one of the "u.id" columns at the beginning of the query? It shouldn't cause any problem (works here) but... with Oracle... who knows...
Thanks!