I need to handle data in tables using mat view.
CREATE TABLE test.zone
(
`id` UInt32,
`district` String,
`city` String,
`polygon` Polygon
)
ENGINE = Memory;
CREATE TABLE test.source1
(
id UInt32,
point Point
)
ENGINE = Null;
CREATE TABLE test.by_zone1
(
`id` UInt32,
`zone_id` UInt32,
`count` UInt32
)
ENGINE = AggregatingMergeTree
ORDER BY (zone_id, id);
CREATE MATERIALIZED VIEW test.mv_by_zone
TO test.by_zone1
AS SELECT
z.id AS zone_id,
s.id AS id,
count(s.id) AS count
FROM test.`source1` s
JOIN test.`zone` z ON pointInPolygon(s.point, z.polygon)
GROUP BY z.id, s.id;
For materialized view I got this error
SQL Error [403] [07000]: Code: 403. DB::Exception: JOIN test.source1 AS __table1
ALL INNER JOIN test.zone AS __table2 ON pointInPolygon(__table1.point, __table2.polygon)
join expression contains column from left and right table, you may try experimental support
of this feature by `SET allow_experimental_join_condition = 1`. (INVALID_JOIN_ON_EXPRESSION)
(version 24.5.1.1065 (official build))
Am I right that I can’t use pointInPolygon
in this case or there is some mistake in query?