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

MySQL error in tag/lib.php cron - Invalid use of group function

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Minor
    • Resolution: Duplicate
    • Affects Version/s: 1.9.7, 2.0
    • Fix Version/s: None
    • Component/s: Blog
    • Labels:
      None
    • Environment:
      mysql 4.1.22, php 5.2.9, linux redhat el4
    • Database:
      MySQL
    • Affected Branches:
      MOODLE_19_STABLE, MOODLE_20_STABLE

      Description

      SQL error during tag cron process to cache tag correlations. In tag/lib.php function tag_compute_correlations()

      MySQL 4.1.22 responds with : ERROR 1111 (HY000): Invalid use of group function to queries in the following format:

      SELECT tb.tagid FROM mdl_tag_instance ta INNER JOIN mdl_tag_instance tb ON ta.itemid = tb.itemid WHERE ta.tagid = 2 AND tb.tagid != 2 GROUP BY tb.tagid HAVING COUNT > 2 ORDER BY COUNT DESC;

      This appears to be a limitation of MySQL 4.x where group by using aggregate functions fails unless it appears as an alias in select clause.

      See discussion here: http://bugs.mysql.com/bug.php?id=5478

      This can be made to work as follows

      SELECT tb.tagid, COUNT AS nr FROM mdl_tag_instance ta INNER JOIN mdl_tag_instance tb ON ta.itemid = tb.itemid WHERE ta.tagid = 2 AND tb.tagid != 2 GROUP BY tb.tagid HAVING nr > 2 ORDER BY nr DESC;

      Suggested patches for 1.9.7 and 2.0 attached.

      PHP Notice: Invalid use of group function<br /><br />SELECT tb.tagid FROM mdl_tag_instance ta INNER JOIN mdl_tag_instance tb ON ta.itemid = tb.item
      id WHERE ta.tagid = 10 AND tb.tagid != 10 GROUP BY tb.tagid HAVING COUNT > 2 ORDER BY COUNT DESC<ul style="text-align: left"><li>line 686 of lib/dmllib.php: call to debugging()</li><li>line 966
      of lib/dmllib.php: call to get_recordset_sql()</li><li>line 784 of tag/lib.php: call to get_records_sql()</li><li>line 816 of tag/lib.php: call to tag_compute_correlations()</li><li>line 369 of admin/c
      ron.php: call to tag_cron()</li></ul> in /websites/mysupa-19/lib/weblib.php on line 6994

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              poltawski Dan Poltawski
              Reporter:
              digitalsean Sean Farrell
              Participants:
              Component watchers:
              Adrian Greeve, Jake Dallimore, Mathew May, Mihail Geshoski, Peter Dias
              Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: