Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-26025

Various problems with range (top) queries in the sqlsrv native driver



    • Bug
    • Status: Closed
    • Blocker
    • Resolution: Fixed
    • 2.0.1
    • 2.0.2
    • Database SQL/XMLDB
    • Microsoft SQL
    • Difficult


      I created a new instance of Moodle 2.0.1+ (Build: 20110112) on my Windows Server 2003 test machine. At the end of the setup I added Site News to my front page and got an error saying "The column 'name' was specified multiple times for 'q'". After turning the Moodle debugging option up to Developer level I see the following output:

      Warning: file_put_contents(D:\MoodleData2/cache/lang/en/core_error.php) [function.file-put-contents]: failed to open stream: Permission denied in D:\Moodle2\lib\moodlelib.php on line 5986

      Error reading from database

      More information about this error

      Debug info: SQLState: 42000<br>
      Error Code: 8156<br>
      Message: [Microsoft][SQL Server Native Client 10.0][SQL Server]The column 'name' was specified multiple times for 'q'.<br>

      SELECT , name, timemodified, usermodified, groupid, timestart, timeend, firstname, lastname, email, picture, imagealt FROM (SELECT TOP 9223372036854775806 ROW_NUMBER() OVER(ORDER BY modified DESC) AS sqlsrvrownumber, *, name, timemodified, usermodified, groupid, timestart, timeend, firstname, lastname, email, picture, imagealt FROM (SELECT p., d.name, d.timemodified, d.usermodified, d.groupid, d.timestart, d.timeend,
      u.firstname, u.lastname, u.email, u.picture, u.imagealt
      FROM mdl_forum_discussions d
      JOIN mdl_forum_posts p ON p.discussion = d.id
      JOIN mdl_user u ON p.userid = u.id

      WHERE d.forum = '1' AND p.parent = 0

      ) AS q) AS q WHERE q.sqlsrvrownumber > 0 AND q.sqlsrvrownumber <= 1
      [array (
      0 => '1',
      Stack trace:
      line 391 of \lib\dml\moodle_database.php: dml_read_exception thrown
      line 252 of \lib\dml\sqlsrv_native_moodle_database.php: call to moodle_database->query_end()
      line 363 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->query_end()
      line 761 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->do_query()
      line 905 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->get_recordset_sql()
      line 2596 of \mod\forum\lib.php: call to sqlsrv_native_moodle_database->get_records_sql()
      line 5084 of \mod\forum\lib.php: call to forum_get_discussions()
      line 175 of \index.php: call to forum_print_latest_discussions()

      I've attached the html output with the error.




            samhemelryk Sam Hemelryk
            libertymoodle Luis de Vasconcelos
            David Mudrák (@mudrd8mz) David Mudrák (@mudrd8mz)
            Andrew Lyons, Huong Nguyen, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze, Stevani Andolo
            0 Vote for this issue
            2 Start watching this issue