Moodle
  1. Moodle
  2. MDL-43719

Column 'mdl_user.firstnamephonetic' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 2.6.1
    • Fix Version/s: 2.6.3
    • Component/s: Tags
    • Labels:
    • Database:
      Microsoft SQL, Oracle
    • Testing Instructions:
      Hide

      Note Please test this on all databases.

      1. Log in as the administrator.
      2. Create a blog entry and add some fields to the tag section towards the bottom.
      3. Go to [Site administration ► Users ► Permissions ► User policies] and set "Full name format" to something like "firstname middlename lastname" and save.
      4. Go to [Site administration ► Appearance ► Manage tags] and ensure that no error messages are displayed.
      Show
      Note Please test this on all databases. Log in as the administrator. Create a blog entry and add some fields to the tag section towards the bottom. Go to [Site administration ► Users ► Permissions ► User policies] and set "Full name format" to something like "firstname middlename lastname" and save. Go to [Site administration ► Appearance ► Manage tags] and ensure that no error messages are displayed.
    • Affected Branches:
      MOODLE_26_STABLE
    • Fixed Branches:
      MOODLE_26_STABLE
    • Pull from Repository:
    • Pull 2.6 Branch:
      wip-MDL-43719-26
    • Pull Master Branch:
      wip-MDL-43719-master

      Description

      When clicking the Manage Tags link under Appearance in the Site Administration Menu.

      Error reading from database 
      Debug: Column 'mdl_user.firstnamephonetic' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
       SELECT TOP 30 tg.id, tg.name, tg.rawname, tg.tagtype, tg.flag, tg.timemodified,
                     u.id AS owner, u.firstnamephonetic,u.lastnamephonetic,u.middlename,u.alternatename,u.firstname,u.lastname,
                     COUNT(ti.id) AS count
                FROM mdl_tag tg
           LEFT JOIN mdl_tag_instance ti ON ti.tagid = tg.id
           LEFT JOIN mdl_user u ON u.id = tg.userid
                     
            GROUP BY tg.id, tg.name, tg.rawname, tg.tagtype, tg.flag, tg.timemodified,
                     u.id, u.firstname, u.lastname
               ORDER BY flag DESC
      [array (
      )]
      Error code: dmlreadexception
      * line 441 of \lib\dml\moodle_database.php: dml_read_exception thrown
      * line 242 of \lib\dml\mssql_native_moodle_database.php: call to moodle_database->query_end()
      * line 716 of \lib\dml\mssql_native_moodle_database.php: call to mssql_native_moodle_database->query_end()
      * line 750 of \lib\dml\mssql_native_moodle_database.php: call to mssql_native_moodle_database->get_recordset_sql()
      * line 250 of \tag\manage.php: call to mssql_native_moodle_database->get_records_sql()
      

      Referring to MDL-43283 and wondering if fix would be adding $allusernames to the end of the Group By clause in moodle\tag\manage.php:

      Line 234 u.id, u.firstname, u.lastname, $allusernames
      Line 235 $sort;

        Gliffy Diagrams

          Activity

          Hide
          Michael E added a comment - - edited

          I'm experiencing the same problem in 2.6 on Oracle:

          Error reading from database

          More information about this error
          Debug info: ORA-00979: not a GROUP BY expression
          SELECT *
          FROM (
          SELECT tg.id, tg.name, tg.rawname, tg.tagtype, tg.flag, tg.timemodified,
          u.id AS owner, u.firstnamephonetic,u.lastnamephonetic,u.middlename,u.alternatename,u.firstname,u.lastname,
          COUNT(ti.id) AS count
          FROM m_tag tg
          LEFT JOIN m_tag_instance ti ON ti.tagid = tg.id
          LEFT JOIN m_user u ON u.id = tg.userid

          GROUP BY tg.id, tg.name, tg.rawname, tg.tagtype, tg.flag, tg.timemodified,
          u.id, u.firstname, u.lastname
          ORDER BY flag DESC)
          WHERE rownum <= :o_oracle_num_rows
          [array (
          'o_oracle_num_rows' => 30,
          )]
          Error code: dmlreadexception
          Stack trace:

          line 441 of /lib/dml/moodle_database.php: dml_read_exception thrown
          line 271 of /lib/dml/oci_native_moodle_database.php: call to moodle_database->query_end()
          line 1122 of /lib/dml/oci_native_moodle_database.php: call to oci_native_moodle_database->query_end()
          line 254 of /tag/manage.php: call to oci_native_moodle_database->get_records_sql()

          Show
          Michael E added a comment - - edited I'm experiencing the same problem in 2.6 on Oracle: Error reading from database More information about this error Debug info: ORA-00979: not a GROUP BY expression SELECT * FROM ( SELECT tg.id, tg.name, tg.rawname, tg.tagtype, tg.flag, tg.timemodified, u.id AS owner, u.firstnamephonetic,u.lastnamephonetic,u.middlename,u.alternatename,u.firstname,u.lastname, COUNT(ti.id) AS count FROM m_tag tg LEFT JOIN m_tag_instance ti ON ti.tagid = tg.id LEFT JOIN m_user u ON u.id = tg.userid GROUP BY tg.id, tg.name, tg.rawname, tg.tagtype, tg.flag, tg.timemodified, u.id, u.firstname, u.lastname ORDER BY flag DESC) WHERE rownum <= :o_oracle_num_rows [array ( 'o_oracle_num_rows' => 30, )] Error code: dmlreadexception Stack trace: line 441 of /lib/dml/moodle_database.php: dml_read_exception thrown line 271 of /lib/dml/oci_native_moodle_database.php: call to moodle_database->query_end() line 1122 of /lib/dml/oci_native_moodle_database.php: call to oci_native_moodle_database->query_end() line 254 of /tag/manage.php: call to oci_native_moodle_database->get_records_sql()
          Hide
          Michael E added a comment -

          To resolve this issue, open tags/manage.php

          1. Look for the following code around line 228:

          $allusernames = get_all_user_name_fields(true, 'u');
          $query = "
          SELECT tg.id, tg.name, tg.rawname, tg.tagtype, tg.flag, tg.timemodified,
          u.id AS owner, $allusernames,
          COUNT(ti.id) AS count
          FROM

          {tag}

          tg
          LEFT JOIN

          {tag_instance}

          ti ON ti.tagid = tg.id
          LEFT JOIN

          {user}

          u ON u.id = tg.userid
          $where
          GROUP BY tg.id, tg.name, tg.rawname, tg.tagtype, tg.flag, tg.timemodified,
          u.id, u.firstname, u.lastname
          $sort";

          2. Replace the GROUP BY as follows:

          GROUP BY tg.id, tg.name, tg.rawname, tg.tagtype, tg.flag, tg.timemodified,
          u.id, $allusernames

          Show
          Michael E added a comment - To resolve this issue, open tags/manage.php 1. Look for the following code around line 228: $allusernames = get_all_user_name_fields(true, 'u'); $query = " SELECT tg.id, tg.name, tg.rawname, tg.tagtype, tg.flag, tg.timemodified, u.id AS owner, $allusernames, COUNT(ti.id) AS count FROM {tag} tg LEFT JOIN {tag_instance} ti ON ti.tagid = tg.id LEFT JOIN {user} u ON u.id = tg.userid $where GROUP BY tg.id, tg.name, tg.rawname, tg.tagtype, tg.flag, tg.timemodified, u.id, u.firstname, u.lastname $sort"; 2. Replace the GROUP BY as follows: GROUP BY tg.id, tg.name, tg.rawname, tg.tagtype, tg.flag, tg.timemodified, u.id, $allusernames
          Hide
          Michael de Raadt added a comment -

          I was able to replicate this on MSSQL and Oracle. MySQL and PostgreSQL were less fussy.

          Show
          Michael de Raadt added a comment - I was able to replicate this on MSSQL and Oracle. MySQL and PostgreSQL were less fussy.
          Hide
          Simon Coggins added a comment - - edited

          This is happening for us in postgres too (at least in Totara 2.6). See screenshot.

          Show
          Simon Coggins added a comment - - edited This is happening for us in postgres too (at least in Totara 2.6). See screenshot.
          Hide
          Simon Coggins added a comment -

          Ah, it only occurs on Postgres 8.3, the syntax seems to be ok in 9.1 as there is improved ability to 'guess' fields from the same table.

          http://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9.1#SQL_and_PL.2FPgSQL_features

          Show
          Simon Coggins added a comment - Ah, it only occurs on Postgres 8.3, the syntax seems to be ok in 9.1 as there is improved ability to 'guess' fields from the same table. http://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9.1#SQL_and_PL.2FPgSQL_features
          Hide
          Adrian Greeve added a comment -

          A note to myself that I should check other areas of moodle for similar sql problems.

          Show
          Adrian Greeve added a comment - A note to myself that I should check other areas of moodle for similar sql problems.
          Hide
          CiBoT added a comment -

          Results for MDL-43719

          • Remote repository: git://github.com/abgreeve/moodle.git
          Show
          CiBoT added a comment - Results for MDL-43719 Remote repository: git://github.com/abgreeve/moodle.git Remote branch wip- MDL-43719 -master to be integrated into upstream master Executed job http://integration.moodle.org/job/Precheck%20remote%20branch/2843 Details: http://integration.moodle.org/job/Precheck%20remote%20branch/2843/artifact/work/smurf.html
          Hide
          Ankit Agarwal added a comment -

          Looks good Adrian, however this definitely needs to be backported.

          Cheers

          Show
          Ankit Agarwal added a comment - Looks good Adrian, however this definitely needs to be backported. Cheers
          Hide
          Adrian Greeve added a comment -

          Thanks Ankit,

          I've added a branch for 2.6. Submitting for integration.

          Show
          Adrian Greeve added a comment - Thanks Ankit, I've added a branch for 2.6. Submitting for integration.
          Hide
          Dan Poltawski added a comment -

          Integrated to master and 26 - thanks

          Show
          Dan Poltawski added a comment - Integrated to master and 26 - thanks
          Hide
          Dan Poltawski added a comment -

          Stopping testing, as i'm going home (I did postgres and part way through oracle).

          Show
          Dan Poltawski added a comment - Stopping testing, as i'm going home (I did postgres and part way through oracle).
          Hide
          Rajesh Taneja added a comment -

          Thanks for fixing this Adrian,

          Tested on mssql, mysql, oracle and postgres, no error appeared.

          Passing...

          Show
          Rajesh Taneja added a comment - Thanks for fixing this Adrian, Tested on mssql, mysql, oracle and postgres, no error appeared. Passing...
          Hide
          Eloy Lafuente (stronk7) added a comment -

          Many thanks! Your awesome code is now upstream!

          Show
          Eloy Lafuente (stronk7) added a comment - Many thanks! Your awesome code is now upstream!

            People

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

              Dates

              • Created:
                Updated:
                Resolved: