@uedatakeshi opened this Issue on November 12th 2015

piwik 2.14.3

conditions;
period=range
date=2015-10-01,2015-10-09
segment=customVariablePageValue1==ABC123

Visitors overview shows 250 visits.
But Visitor Log show only 32 lines.
Click Next> shows no data.

If I added '&filter_limit=-1' to the end of URL and hit enter, then it show 250 lines.

@hpvd commented on November 12th 2015

@uedatakeshi Thanks for report! Is it still same behaviour in piwik 2.15?

@uedatakeshi commented on November 13th 2015

Yes, Piwik 2.15.0 works same.

@mattab commented on November 18th 2015 Member

Hi @uedatakeshi can you reproduce this bug on our demo @ https://demo.piwik.org/index.php?module=CoreHome&action=index&idSite=7&period=day&date=yesterday ? we have a couple custom variables there but when applying the segment it seems to work: link with segment shows as many visits in Visitor log as in Visitors>Overview

@uedatakeshi commented on November 21st 2015

Thank you Matt.
I have no account to demo.piwik.org.
So I show you the difference between existing segment 'Logged in users' and ours.

demo.piwik.org
Logged in users
=customVariableName3==Forum status;customVariableValue3==LoggedIn user

our site
School segment
segment=customVariablePageName1==School;customVariablePageValue1==ABC123

Would you please create custome variable with scope="page"?
And then add segment to test.

@mattab commented on November 25th 2015 Member

@uedatakeshi I put piwikjapan.org statistics as public on demo.piwik.org, so you are welcome to add the custom variable of scope 'page' and try to reproduce the issue there. See the demo: http://demo.piwik.org/index.php?module=CoreHome&action=index&date=today&period=month&idSite=33

Let me know if this is an issue that I put piwikjapan.org stats as public, I could remove it. Thanks!

@uedatakeshi commented on November 27th 2015

Hi, Matt!
Thank you for your advice.
I'm sorry I was reminded I have an account to login demo.piwik.org.

I added the custom variable of scope 'page' to piwikjapan.org.

INDEX : 1
NAME : IsTop
VALUE : TopPage or NotTopPage
SCOPE : page

I added segment named TopPage and NotTopPage to demo.piwik.org.

segment : TopPage
Custom Variable valu1 (scope page) Is "TopPage"

segment : NotTopPage
Custom Variable valu1 (scope page) Is "NotTopPage"

And then when I view visitor log.

Period : day (November 26)
segment : NotTopPage

It shows only 14 rows.

But when I add "&filter_limit=-1" to the end of URL, it shows 48 rows.

@tsteur commented on January 11th 2016 Member

I can reproduce it and noticed problem is this: https://github.com/piwik/piwik/blob/2.16.0-b1/core/DataAccess/LogQueryBuilder.php#L287-L290

A inner group by is specified but unset because a limit is used. This is done to make the query faster but returns an incomplete result set in this case. We need to discuss how to fix this issue.

Here's a query to reproduce this issue:

            SELECT sub.* FROM (
            SELECT
                log_inner.*
            FROM    
        (

            SELECT
                log_visit.*
            FROM
                piwik_log_visit AS log_visit
                LEFT JOIN piwik_log_link_visit_action AS log_link_visit_action ON log_link_visit_action.idvisit = log_visit.idvisit
                LEFT JOIN piwik_log_action AS log_action ON log_link_visit_action.idaction_url = log_action.idaction
            WHERE
                ( log_visit.idsite in ('1') 
                AND log_visit.visit_last_action_time >= '2015-01-25 11:00:00'
                AND  log_visit.visit_last_action_time <= '2015-01-26 11:00:00' )
                AND
                ( log_action.type = '1' )
            ORDER BY
                idsite, visit_last_action_time DESC LIMIT 20
        ) AS log_inner
            ORDER BY
                idsite, visit_last_action_time DESC LIMIT 20
            ) AS sub
            GROUP BY sub.idvisit
            ORDER BY sub.visit_last_action_time DESC

To get a correct result there should be a GROUP BY log_visit.idvisit before the first ORDER BY and optionally before the second ORDER BY

@tsteur commented on January 12th 2016 Member

We had a look and it's not easy to fix without regressing performance of Visitor Log unfortunately. The performance improvement was added in #6786 initially.

A workaround could be the following patch:

diff --git a/plugins/Live/Model.php b/plugins/Live/Model.php
index 2152aee..d94a2b6 100644
--- a/plugins/Live/Model.php
+++ b/plugins/Live/Model.php
@@ -398,6 +398,10 @@ class Model
         $orderBy = "idsite, visit_last_action_time " . $filterSortOrder;
         $orderByParent = "sub.visit_last_action_time " . $filterSortOrder;

+        if (!$segment->isEmpty()) {
+            $limit = $limit * 10;
+        }
+
         $subQuery = $segment->getSelectQuery($select, $from, $where, $whereBind, $orderBy, $groupBy, $limit, $offset);

         $bind = $subQuery['bind'];
@@ -408,7 +412,8 @@ class Model
            ) AS sub
            GROUP BY sub.idvisit
            ORDER BY $orderByParent
-       ";
+           LIMIT $limit
+       "; // offset is already applied in getSelectQuery()
         return array($sql, $bind);
     }

but it would only make it a little better. Idea would be to always fetch eg 10 times more visits in the inner query and then apply the actual limit in the outer query when there is a segment given. It's rather a hack though and would not actually fix it.

@mattab commented on February 6th 2016 Member

Hi @tsteur FYI we may have to solve this problem in 2.16.1 so let's discuss the best solution for it next week.

@mattab commented on February 13th 2016 Member

PR #9774 will improve the situation: at least now the data can be returned by paging through the pages. But the Limit is not actually working properly. the screenshot below shows the inner query resultset: it returns the correct number of rows - but because many rows share the same idvisit (this is the case when the segment is an action segment), then the GROUP BY in the outer query will make the final resultset having only 4 rows - instead of expected 20).

pasted_image_at_2016_02_13_11_32_am

We still don't know how to completely fix this...

@mattab commented on February 13th 2016 Member

instead of using a numeric offset to page through the result sets, maybe we could use the "min timestamp" ?

@tsteur commented on February 14th 2016 Member

FYI: This is done in Piwik Mobile 1 like this see https://github.com/piwik/piwik-mobile/blob/8048e38975df83b32025b3181244f427316feedb/Resources/windows/statistics/visitorlog.js#L228-L246 . minTimestamp was used for next, maxIdVisit was used for previous (I think).

Piwik Mobile 2 uses filter_limit / filter_offset instead. I think there was no special reason for it, just kinda easier to implement it this way.

@hpvd commented on February 15th 2016

maybe working on this/solution is somehow releated to "display total number of visits in pagination at bottom of page" https://github.com/piwik/piwik/issues/9110
?

@mattab commented on April 11th 2016 Member

This problem has been minimised in 2.16.1 (two pull requests have been merged: #9774 and #10024) but this problem still exists so I leave the issue opened.

(to accurately fix this issue without introducing performance overhead, maybe we need to https://github.com/piwik/piwik/issues/9200#issuecomment-183700023)

@tsteur commented on May 12th 2019 Member
This Issue was closed on May 12th 2019
Powered by GitHub Issue Mirror