Uploaded image for project: '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
    • Status: Closed
    • Priority: 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 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
          icefresh 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
          icefresh 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
          icefresh 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
          icefresh 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
          salvetore Michael de Raadt added a comment -

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

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

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

          Show
          simoncoggins Simon Coggins added a comment - - edited This is happening for us in postgres too (at least in Totara 2.6). See screenshot.
          Hide
          simoncoggins 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
          simoncoggins 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
          abgreeve Adrian Greeve added a comment -

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

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

          Results for MDL-43719

          • Remote repository: git://github.com/abgreeve/moodle.git
          Show
          cibot 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_frenz Ankit Agarwal added a comment -

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

          Cheers

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

          Thanks Ankit,

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

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

          Integrated to master and 26 - thanks

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

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

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

          Thanks for fixing this Adrian,

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

          Passing...

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

          Many thanks! Your awesome code is now upstream!

          Show
          stronk7 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:
                Fix Release Date:
                12/May/14