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

Yes, Piwik 2.15.0 works same.

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

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.

@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!

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.

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

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.

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.

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).

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

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

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.

maybe working on this/solution is somehow releated to "display total number of visits in pagination at bottom of page" #9110
?

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 #9200 (comment))

closing as duplicate of #13861