Duplicate rows in pivoted table

[Holger_Lange]

We set up an Explore with two dimension (one as rows, one as columns/pivot) and one measure. When the output is being shown in Looker, we see multiple rows for one and the same element of the dimension:

This is some very unexpected behaviour that also leads to error messages (“Unable to chart pivoted data when dimension Marketing Controlling Partner has repeated values. This commonly happens when you create a query with a second dimension and hide that dimension from the chart.”).
That’s the SQL that Looker creates:

SELECT * FROM (
SELECT *, DENSE_RANK() OVER (ORDER BY z___min_rank) as z___pivot_row_rank, RANK() OVER (PARTITION BY z__pivot_col_rank ORDER BY z___min_rank) as z__pivot_col_ordering FROM (
SELECT *, MIN(z___rank) OVER (PARTITION BY "marketing_controlling.partner") as z___min_rank FROM (
SELECT *, RANK() OVER (ORDER BY "marketing_controlling.partner" ASC, z__pivot_col_rank) AS z___rank FROM (
SELECT *, DENSE_RANK() OVER (ORDER BY "marketing_controlling.date_month" NULLS LAST) AS z__pivot_col_rank FROM (
SELECT 
	upper(marketing_controlling.partner)  AS "marketing_controlling.partner",
	TO_CHAR(DATE_TRUNC('month', marketing_controlling.date ), 'YYYY-MM') AS "marketing_controlling.date_month",
	COALESCE(SUM(case when marketing_controlling.measure like '%cost%' then coalesce( marketing_controlling.actual, 0 ) else 0 end), 0) AS "marketing_controlling.cost_actual"
FROM fact.marketing_controlling  AS marketing_controlling

WHERE (marketing_controlling.date  >= TIMESTAMP '2018-04-01') AND (((upper(marketing_controlling.partner)) = 'STROER'))
GROUP BY 1,DATE_TRUNC('month', marketing_controlling.date )) ww
) bb WHERE z__pivot_col_rank <= 16384
) aa
) xx
) zz
 WHERE z___pivot_row_rank <= 500 OR z__pivot_col_ordering = 1 ORDER BY z___pivot_row_rank

and this is the result from the SQL runner: