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

Database engine consistency with GROUP BY alias in report columns

XMLWordPrintable

    • Icon: Improvement Improvement
    • Resolution: Unresolved
    • Icon: Minor Minor
    • None
    • 4.3
    • Report builder
    • Microsoft SQL, Oracle
    • MOODLE_403_STABLE

      We currently can only GROUP BY <column_alias> on MySQL & Postgres (reference)

      The reason we want to support this is for report columns that have parameters, because we end up re-using the same SQL (with identical parameters) in the GROUP BY clause which causes DML errors due to mismatched params/counts. e.g.

      // Oracle/SQL Server
      SELECT :foo AS alias, SUM(bar)
      FROM ... WHERE ...
      GROUP BY :foo;
       
      ['foo' => 42]
       
      // MySQL/Postgres
      SELECT :foo AS alias, SUM(bar)
      FROM ... WHERE ...
      GROUP BY alias;
       
      ['foo' => 42]
      

      It's easy to workaround this limitation, just by calling ->set_groupby_sql(...) on the column, but it's nice to not have to remember to do that just because of Oracle & SQL Server

      Oracle 23c has this feature coming soon, see also this video - at the point at which this is the minimum requirement for Moodle, we should update the code referenced above

      SQL Server still does not have this feature, nor does it appear is it on the horizon. Let's keep an eye out for possible solutions

            Unassigned Unassigned
            pholden Paul Holden
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Created:
              Updated:

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