@tomasztomik opened this issue on February 22nd 2016

Hello,

One of our customers wants to view transition for a certain page, using also segment based on events.

As we know, there is a limitation that when segment is based on events, there are no reports for other kinds of actions. To bypass it, customer defined a goal by "send an event", after that he created a segment on "Visit converted for specific Goal ID".

Problem is that if he wants to open the transition for a certain page (using segment as above), he got the following error message: SQLSTATE[23000]: Integrity constraint vilation: 1052 Column 'idaction_url' in field list is ambigous.

Seems that wrong SELECT was generated, using segments and transitions as described above. Customer is using Piwik 2.15.0.

@tsteur commented on February 23rd 2016

I can reproduce it. This is the query that is eg failing (possibly also other queries in transitions API)

            SELECT

                CASE
                    WHEN counter = 6 THEN 'Others'
                    ELSE `name`
                END AS `name`
            , 
                CASE
                    WHEN counter = 6 THEN 'Others'
                    ELSE `url_prefix`
                END AS `url_prefix`

                , `type`, sum(`3`) AS `3`
            FROM ( 
            SELECT
                `name`, `url_prefix`,

            CASE
                WHEN `type` = 1 AND @counter1 = 6 THEN 6
                WHEN `type` = 1 THEN @counter1:=@counter1+1
                WHEN `type` = 8 AND @counter8 = 6 THEN 6
                WHEN `type` = 8 THEN @counter8:=@counter8+1
                WHEN `type` = 2 AND @counter2 = 6 THEN 6
                WHEN `type` = 2 THEN @counter2:=@counter2+1
                WHEN `type` = 3 AND @counter3 = 6 THEN 6
                WHEN `type` = 3 THEN @counter3:=@counter3+1
                ELSE 0
            END
         AS counter
                , `type`, `3`
            FROM
                ( SELECT @counter1:=0 ) initCounter1, ( SELECT @counter8:=0 ) initCounter8, ( SELECT @counter2:=0 ) initCounter2, ( SELECT @counter3:=0 ) initCounter3, 
                ( /* idSegments = [11] */

            SELECT
                if ( idaction_url IS NULL, idaction_name, idaction_url ), 
            count(*) AS `3`, 
            log_action.name, 
            log_action.url_prefix, 
            log_action.type
            FROM
                piwik_log_link_visit_action AS log_link_visit_action
                LEFT JOIN piwik_log_action AS log_action ON log_action.idaction = if ( idaction_url IS NULL, idaction_name, idaction_url )
                LEFT JOIN piwik_log_conversion AS log_conversion ON log_conversion.idvisit = log_link_visit_action.idvisit
            WHERE
                ( log_link_visit_action.server_time >= ?
                AND log_link_visit_action.server_time <= ?
                AND log_link_visit_action.idsite IN (?) AND log_link_visit_action.idaction_url_ref = 1525 AND (log_link_visit_action.idaction_url IS NULL OR log_link_visit_action.idaction_url != 1525) )
                AND
                ( log_conversion.idgoal = ? )
            GROUP BY
                if ( idaction_url IS NULL, idaction_name, idaction_url )
            ORDER BY
                `3` DESC ) actualQuery
         ) AS withCounter
            GROUP BY counter, `type`

We can possibly fix the error by prefixing idaction_url. Not sure if it will break anything.

@mattab commented on March 3rd 2016

PR: https://github.com/piwik/piwik/pull/9873

(First the error was reproduced in a system test: https://github.com/piwik/piwik/commit/6f3faf8c9dea2772ab74891a4c3c7216e231b84c )

This issue was closed on March 10th 2016
Powered by GitHub Issue Mirror