Relationship Graph

Relationship Graph
related to related to child of child of duplicate of duplicate of

View Issue Details

IDProjectCategoryView StatusLast Update
0018295phpList 3 applicationInterface - Backendpublic15-05-17 16:37
Reporterrsecor Assigned To 
Status closedResolutionfixed 
Product Version3.2.5 
Target Versionnext patch 
Summary0018295: SQL GROUP BY Errors
DescriptionVarious SQL Group by Errors
Fixes included.
Steps To ReproduceClick into some areas of the admin area that respond with SQL errors.
TagsNo tags attached.


related to 0018341 resolvedmichiel MySQL 5.7 changes the default sql mode 



14-08-16 23:45

reporter   ~0058137

diff userclicks.php-3.2.5 userclicks.php-3.2.5.fixed
< and uml_click.forwardid = %d group by uml_click.userid', $GLOBALS['tables']['linktrack_uml_click'],
> and uml_click.forwardid = %d group by,userid,firstclick,lastclick,clicked', $GLOBALS['tables']['linktrack_uml_click'],
< and uml_click.messageid = %d group by', $GLOBALS['tables']['linktrack_uml_click'],
> and uml_click.forwardid = %d group by,userid,firstclick,lastclick,clicked', $GLOBALS['tables']['linktrack_uml_click'],
< and uml_click.userid = %d and = uml_click.forwardid group by url',
> and uml_click.userid = %d and = uml_click.forwardid group by htmlclicked,textclicked,,userid,firstclick,latestclick,clicked,messageid,forwardid,url', $GLOBALS['tables']['linktrack_uml_click'], $GLOBALS['tables']['user'], $GLOBALS['tables']['linktrack_forward'],


14-08-16 23:45

reporter   ~0058138

diff uclicks.php-3.2.5 uclicks.php-3.2.5.fixed
< where clicked %s and = ml.forwardid and latestclick > date_sub(now(),interval 12 month) group by url order by latestclick desc limit 50',
> where clicked %s and = ml.forwardid and latestclick > date_sub(now(),interval 12 month) group by,url,latestclick order by latestclick desc limit 50',


15-08-16 11:06

updater   ~0058142

I can see that some of the queries are incorrect as they refer to non-aggregated columns but I'm not sure about your changes. Firstly they refer to a non-existant column, lastclick, and after having fixed that, don't aggregate the values for each user.

For example the page that shows user clicks for a url is meant to show one row for each user who has clicked the link in any message, summarising the clicks. It needs to select the earliest value of firstclick, the most recent value of latestclick, and to sum the number of clicks.

    $query = sprintf('select, as userid,min(firstclick) as firstclick,date_format(max(latestclick),
    "%%e %%b %%Y %%H:%%i") as latestclick,sum(clicked) as clicked from %s as uml_click, %s as user where uml_click.userid =
    and uml_click.forwardid = %d group by uml_click.userid', $GLOBALS['tables']['linktrack_uml_click'],


09-09-16 17:49

reporter   ~0058222

I am up for suggestions as to the "right" queries for the reports that reflect SQL errors when ran.


09-09-16 20:53

administrator   ~0058223


Pasting diffs in an issue tracker is hard to process. Can you send a PR on github and link to it from here? Then it's easier to see what you're trying to achieve. Also a little explanation on what the problem is and why you are suggesting a change will help to understand it better.



11-09-16 13:50

updater   ~0058228

@rsecor please can you show the actual error messages that occur with the original phplist code?

I'm guessing that your installation of mysql has ONLY_FULL_GROUP_BY mode, so if you can also check that too by running

SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;

in phpmyadmin.


11-05-17 20:50

administrator   ~0059055

rsecor, Please provide the feedback requested