@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 'idactionurl' in field list is ambigous.

clipboard01

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 Owner

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 <a class='mention' href='https://github.com/counter1'>@counter1</a> = 6 THEN 6
                WHEN `type` = 1 THEN <a class='mention' href='https://github.com/counter1'>@counter1</a>:=<a class='mention' href='https://github.com/counter1'>@counter1</a>+1
                WHEN `type` = 8 AND <a class='mention' href='https://github.com/counter8'>@counter8</a> = 6 THEN 6
                WHEN `type` = 8 THEN <a class='mention' href='https://github.com/counter8'>@counter8</a>:=<a class='mention' href='https://github.com/counter8'>@counter8</a>+1
                WHEN `type` = 2 AND <a class='mention' href='https://github.com/counter2'>@counter2</a> = 6 THEN 6
                WHEN `type` = 2 THEN <a class='mention' href='https://github.com/counter2'>@counter2</a>:=<a class='mention' href='https://github.com/counter2'>@counter2</a>+1
                WHEN `type` = 3 AND <a class='mention' href='https://github.com/counter3'>@counter3</a> = 6 THEN 6
                WHEN `type` = 3 THEN <a class='mention' href='https://github.com/counter3'>@counter3</a>:=<a class='mention' href='https://github.com/counter3'>@counter3</a>+1
                ELSE 0
            END
         AS counter
                , `type`, `3`
            FROM
                ( SELECT <a class='mention' href='https://github.com/counter1'>@counter1</a>:=0 ) initCounter1, ( SELECT <a class='mention' href='https://github.com/counter8'>@counter8</a>:=0 ) initCounter8, ( SELECT <a class='mention' href='https://github.com/counter2'>@counter2</a>:=0 ) initCounter2, ( SELECT <a class='mention' href='https://github.com/counter3'>@counter3</a>:=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 Owner
This Issue was closed on March 10th 2016
Powered by GitHub Issue Mirror