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

User sorting failure on SQL Server when initial sorting is on the user name

    XMLWordPrintable

Details

    Description

      This was detected during testing of MDL-79270 and commented here

      The same failure (only when the initial sorting of a report table is on the users name, and then user tries to re-sort the table on one of the name fields) , can be induced on stables on the custom report access tab so we should backport the fix from that issue for SQL Server

      Using the steps from testing instructions above results in the following exception on SQL Server:

      dmlreadexception
      Error reading from database
      File: /lib/dml/moodle_database.php
      Line: 494
      Stack trace:
       
      SQLState: 42000<br>
      Error Code: 169<br>
      Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]A column has been specified more than once in the order by list. Columns in the order by list must be unique.<br>
       
      SELECT u.firstname AS c1_firstname, u.middlename AS c1_middlename, u.lastname AS c1_lastname, u.id AS c1_id, u.picture AS c1_picture, u.firstnamephonetic AS c1_firstnamephonetic, u.lastnamephonetic AS c1_lastnamephonetic, u.alternatename AS c1_alternatename, u.imagealt AS c1_imagealt, u.email AS c1_email, u.email AS c2_email FROM m_user u  WHERE (u.id IN (
                          SELECT rbalias0.id
                            FROM m_user rbalias0
                                 
                           WHERE rbalias0.suspended = 0 AND rbalias0.deleted = 0 AND rbalias0.id <> '1'
                           )) AND u.deleted = '0'  ORDER BY middlename ASC, c1_firstname ASC, c1_middlename ASC, c1_lastname ASC OFFSET 0 ROWS  FETCH NEXT 30 ROWS ONLY
      [array (
        0 => '1',
        1 => 0,
      )]
      Error code: dmlreadexception
      * line 494 of /lib/dml/moodle_database.php: dml_read_exception thrown
      * line 331 of /lib/dml/sqlsrv_native_moodle_database.php: call to moodle_database->query_end()
      * line 438 of /lib/dml/sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->query_end()
      * line 907 of /lib/dml/sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->do_query()
      * line 180 of /reportbuilder/classes/table/base_report_table.php: call to sqlsrv_native_moodle_database->get_recordset_sql()
      * line 2210 of /lib/tablelib.php: call to core_reportbuilder\table\base_report_table->query_db()
      * line 261 of /lib/table/classes/external/dynamic/get.php: call to table_sql->out()
      * line ? of unknownfile: call to core_table\external\dynamic\get::execute()
      * line 253 of /lib/external/classes/external_api.php: call to call_user_func_array()
      * line 83 of /lib/ajax/service.php: call to core_external\external_api::call_external_function()
      

      Attachments

        Issue Links

          Activity

            People

              pholden Paul Holden
              pholden Paul Holden
              Carlos Castillo Carlos Castillo
              David Carrillo David Carrillo
              CiBoT CiBoT
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0 minutes
                  0m
                  Logged:
                  Time Spent - 1 hour, 11 minutes
                  1h 11m

                  Clockify

                    Error rendering 'clockify-timesheets-time-tracking-reports:timer-sidebar'. Please contact your Jira administrators.