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: 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.
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 =  */ 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.
(First the error was reproduced in a system test: https://github.com/piwik/piwik/commit/6f3faf8c9dea2772ab74891a4c3c7216e231b84c )