View Issue Details

IDProjectCategoryView StatusLast Update
0018295phpList 3 applicationInterface - Backendpublic15-05-17 15:37
Reporterrsecor 
PriorityurgentSeveritymajorReproducibilityalways
Status closedResolutionfixed 
Product Version3.2.5 
Target Versionnext patchFixed in Version 
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.

Relationships

related to 0018341 resolvedmichiel MySQL 5.7 changes the default sql mode 

Activities

rsecor

14-08-16 22:45

reporter   ~0058137

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

rsecor

14-08-16 22:45

reporter   ~0058138

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

duncanc

15-08-16 10:06

developer   ~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 user.email, user.id 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 = user.id
    and uml_click.forwardid = %d group by uml_click.userid', $GLOBALS['tables']['linktrack_uml_click'],
        $GLOBALS['tables']['user'],
        $fwdid);

rsecor

09-09-16 16:49

reporter   ~0058222

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

michiel

09-09-16 19:53

manager   ~0058223

Hi

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.

Thanks

duncanc

11-09-16 12:50

developer   ~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.

michiel

11-05-17 19:50

manager   ~0059055


rsecor, Please provide the feedback requested