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:
    • Database:
      Microsoft SQL
    • Testing Instructions:
      Hide

      Using Configurable Reports and the report described here

      (moderate, requires a Moodle instance running on MSSQL)

      1. Install Moodle using MSSQL as its DB
      2. Install Configurable Report block plug-in
      3. Configure a Report using the query described in this issue:

        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
        

      4. Run the report
      5. Confirm that you don't see any error.
      Show
      Using Configurable Reports and the report described here (moderate, requires a Moodle instance running on MSSQL) Install Moodle using MSSQL as its DB Install Configurable Report block plug-in Configure a Report using the query described in this issue: 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 Run the report Confirm that you don't see any error.
    • Workaround:
      Hide

      Whenever using ORDER BY in a "subquery" or a "clause" of a query in a report in CR on a Moodle running on MSSQL, please add " ORDER BY 1" at the end of the query

      Show
      Whenever using ORDER BY in a "subquery" or a "clause" of a query in a report in CR on a Moodle running on MSSQL, please add " ORDER BY 1" at the end of the query
    • Difficulty:
      Easy
    • Affected Branches:
      MOODLE_35_STABLE, MOODLE_36_STABLE, MOODLE_37_STABLE, MOODLE_38_STABLE
    • Fixed Branches:
      MOODLE_36_STABLE, MOODLE_37_STABLE
    • Pull from Repository:
    • Pull Master Branch:
      m38_MDL-65811_Fix_Incorrect_ORDER_BY_Search

      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

            Assignee:
            matteo Matteo Scaramuccia
            Reporter:
            libertymoodle Luis de Vasconcelos
            Peer reviewer:
            Eloy Lafuente (stronk7)
            Integrator:
            Jun Pataleta
            Tester:
            Janelle Barcega
            Participants:
            Component watchers:
            Andrew Nicols, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze
            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