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: Waiting for peer review
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: 3.5.6, 3.6, 3.7, 3.8
    • Fix Version/s: None
    • Component/s: Database SQL/XMLDB
    • Labels:
    • Environment:
    • Database:
      Microsoft SQL
    • Testing Instructions:
      Hide

      (Unit tests IMHO should be enough)

      Unit tests

      (easy, requires a docker host and the Moodle HQ docker toolbox)

      # export MOODLE_DOCKER_WWWROOT=/path/to/moodle
      # export MOODLE_DOCKER_DB=mssql
      # cp config.docker-template.php $MOODLE_DOCKER_WWWROOT/config.php
      # bin/moodle-docker-compose up -d
      # bin/moodle-docker-wait-for-db
      # bin/moodle-docker-compose exec webserver php admin/tool/phpunit/cli/init.php
      # bin/moodle-docker-compose exec webserver vendor/bin/phpunit lib/dml/tests/sqlsrv_native_moodle_database_test.php
      # bin/moodle-docker-compose exec webserver vendor/bin/phpunit
      

      Using CR 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
      4. Run the report: it should be successful
      Show
      (Unit tests IMHO should be enough) Unit tests (easy, requires a docker host and the Moodle HQ docker toolbox) # export MOODLE_DOCKER_WWWROOT=/path/to/moodle # export MOODLE_DOCKER_DB=mssql # cp config.docker-template.php $MOODLE_DOCKER_WWWROOT/config.php # bin/moodle-docker-compose up -d # bin/moodle-docker-wait-for-db # bin/moodle-docker-compose exec webserver php admin/tool/phpunit/cli/init.php # bin/moodle-docker-compose exec webserver vendor/bin/phpunit lib/dml/tests/sqlsrv_native_moodle_database_test.php # bin/moodle-docker-compose exec webserver vendor/bin/phpunit Using CR 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 Run the report: it should be successful
    • 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
    • Pull from Repository:
    • Pull 3.6 Branch:
      m36_MDL-65811_Fix_Incorrect_ORDER_BY_Search
    • Pull 3.7 Branch:
      m37_MDL-65811_Fix_Incorrect_ORDER_BY_Search
    • 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

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

              Dates

              • Created:
                Updated: