Overlapping partial key error with count(*) #145
Description
So I am trying to run this query (follows at the end), which I split into multiple subviews to circumvent some of the current quirks of the SQL noria supports.
Its rather long, I apologize, but because I don't really understand what the error is trying to tell me I am unable to reduce the example.
The actual failure occurs in noria-server/src/controller/migrate/materialization/mod.rs:606:41 and reads
Oct 29 10:58:28.895 CRIT partially overlapping partial indices, conflict: 1, cols: [Some(0), Some(1)], child: 16, pcols: [0], parent: 16
The part of the query affected is the aggregation node produced by the count(*) in pageview_counts.
And here is the query graph, as dumped by the system:
fail.dot.pdf
I previously had the error that it could not find a bogokey to aggregate the count(*) over. I then changed pageview_counts1 to also SELECT ts2, which I believe is what it is now using for the count(*).
It may also be that the conflict is between the GROUP BY, which is over user_id ts1and the actual key used for result lookups which is justuser_id`.
Let me know if you have any idea how to fix this or what I could be using as a workaround.
CREATE TABLE clicks
(user_id int,
pagetype int,
ts int);
-- Workaround because tables cant join on themselves
clicks2:
SELECT *
FROM clicks;
candidate_paths0:
SELECT
c1.user_id,
c1.ts as ts1,
c2.ts as ts2,
FROM
clicks c1 JOIN
clicks2 c2 ON c1.user_id = c2.user_id
WHERE
c1.pagetype = 0 AND
c2.pagetype = 1;
candidate_paths:
SELECT
user_id,
ts1,
ts2
FROM
candidate_paths0
WHERE
ts1 < ts2
ORDER BY
user_id, ts1, ts2
;
matching_paths:
SELECT
user_id, max(ts1) as ts1, ts2
FROM candidate_paths
GROUP BY user_id, ts2;
pageview_counts0:
SELECT c.user_id, ts1, ts2, ts
FROM
clicks c JOIN
matching_paths ON c.user_id = matching_paths.user_id;
pageview_counts1:
SELECT
user_id,
ts1,
ts2
FROM
pageview_counts0
WHERE
ts1 <= ts AND
ts2 >= ts;
pageview_counts:
SELECT
user_id,
count(*) as pageview_count
FROM
pageview_counts1
GROUP BY
user_id, ts1;
VIEW
clickstream_ana:
SELECT
user_id,
sum(pageview_count)
FROM pageview_counts
WHERE user_id = ?;