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

MSSQL ROW_NUMBER() in a sql query crashes Configurable Reports

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 3.5.6, 3.6, 3.7, 3.8
    • Fix Version/s: 3.6.7, 3.7.3
    • Component/s: Database SQL/XMLDB
    • Labels:
    • Environment:

      Description

      I created a new SQL Report that will show me a count of the "Users who have logged in today". It uses the following SQL: 

      SELECT COUNT(*) AS 'Users who have logged in today' FROM (
      	SELECT
      		ROW_NUMBER() OVER(ORDER BY lastaccess DESC) AS Row
      	FROM prefix_user
      	WHERE lastaccess > DATEDIFF(s, '1970-01-01 02:00:00', (SELECT Convert(DateTime, DATEDIFF(DAY, 0, GETDATE()))))
      ) AS Logins

      When I run that query in my SQL editor (SQL Management Studio) it gives me a simple count of how many users have logged into Moodle:
       

       Users who have logged in today
        256

       
      But when I run the query in the Configurable Reports block Moodle throws the following error:
       

      [17-May-2019 11:08:50 Africa/Johannesburg] Default exception handler: Error reading from database Debug: SQLState: 42000<br>
       Error Code: 102<br>
       Message: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Incorrect syntax near 'OFFSET'.<br>
       SQLState: 42000<br>
       Error Code: 153<br>
       Message: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Invalid usage of the option NEXT in the FETCH statement.<br>SELECT COUNT(*) AS 'Users who have logged in today' FROM (
           SELECT
               ROW_NUMBER() OVER(ORDER BY lastaccess DESC) AS Row
           FROM mdl_user
           WHERE lastaccess > DATEDIFF(s, '1970-01-01 02:00:00', (SELECT Convert(DateTime, DATEDIFF(DAY, 0, GETDATE()))))
       ) AS LoginsOFFSET 0 ROWS  FETCH NEXT 20000 ROWS ONLY
       [array (
       )]
       Error code: dmlreadexception
       * line 486 of \lib\dml\moodle_database.php: dml_read_exception thrown
       * line 324 of \lib\dml\sqlsrv_native_moodle_database.php: call to moodle_database->query_end()
       * line 431 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->query_end()
       * line 897 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->do_query()
       * line 77 of \blocks\configurable_reports\reports\sql\report.class.php: call to sqlsrv_native_moodle_database->get_recordset_sql()
       * line 122 of \blocks\configurable_reports\reports\sql\report.class.php: call to report_sql->execute_query()
       * line 71 of \blocks\configurable_reports\viewreport.php: call to report_sql->create_report()

       

      • Moodle version: $release  = '3.5.2+ (Build: 20181027)';
      • Configurable Reports version: $plugin->release = '3.6.0';
      • OS: Windows Server 2012 R2
      • PHP v7.2.9 NTS Win32-VC15-x64
      • Database: Microsoft SQL Server 2016 (SP1) - 13.0.4001.0 (X64)
      • Database driver: $CFG->dbtype = 'sqlsrv';
      • PHP SQLSRV extension version: 5.3.0+11108

      I don't have access to a MySQL or Postgres database so I can't test it on those platforms.

      Please see the related forum post: https://moodle.org/mod/forum/discuss.php?d=386483

        Attachments

          Activity

            People

            • Votes:
              1 Vote for this issue
              Watchers:
              7 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:
                Fix Release Date:
                11/Nov/19

                Time Tracking

                Estimated:
                Original Estimate - 0 minutes
                0m
                Remaining:
                Remaining Estimate - 0 minutes
                0m
                Logged:
                Time Spent - 2 hours, 25 minutes
                2h 25m