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

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

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Blocker
    • Resolution: Fixed
    • Affects Version/s: 2.0.1
    • Fix Version/s: 2.0.2
    • Component/s: Database SQL/XMLDB
    • Labels:
    • Environment:
    • Database:
      Microsoft SQL
    • Difficulty:
      Difficult
    • Affected Branches:
      MOODLE_20_STABLE
    • Fixed Branches:
      MOODLE_20_STABLE

      Description

      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.

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:
                  Fix Release Date:
                  21/Feb/11