Range-based joins between tables not supported, alternative patterns?

[marigov]

I'm trying to join two Kafka-sourced UPSERT tables based on a range condition, but getting a "streaming nested-loop join not supported" error.

Tables

-- companies table (from Kafka UPSERT)
company_id VARCHAR PRIMARY KEY
market_cap BIGINT

-- market_cap_buckets table (from Kafka UPSERT) 
bucket_id VARCHAR PRIMARY KEY
min_cap BIGINT
max_cap BIGINT

Query

CREATE MATERIALIZED VIEW companies_bucketed AS
SELECT 
    c.company_id,
    c.market_cap,
    b.bucket_id
FROM companies c, market_cap_buckets b
WHERE c.market_cap >= b.min_cap 
AND c.market_cap < b.max_cap;

Error

ERROR: Failed to run the query
Caused by: Not supported: streaming nested-loop join

My use case is to dynamically categorize companies into market cap buckets where bucket definitions can change. What's the recommended pattern for range-based joins in streaming contexts?

[hzxa21]

Hi @marigov, unlike the batch world, streaming join without equi-join condition is generally inefficient and can hardly be parallelized because the two sides of join contain unbounded and continuous data set. Unlike traditional batch nested loop join, you cannot just wait for all data to arrive before doing the iteration. Therefore, RisingWave bans streaming join without equi-join condition unless one side of the join is a scalar sub-query (See https://docs.risingwave.com/processing/sql/dynamic-filters), which doesn't seem to be the case from you example.

However, if one side of the join is relatively small and the change frequency is low, you can workaround the limitation by adding a dummy equi-join condition to hint the optimizer to generate a single-parallelism hash join plan for execution. Example:

CREATE MATERIALIZED VIEW companies_bucketed AS
SELECT 
    c.company_id,
    c.market_cap,
    b.bucket_id
FROM companies c, market_cap_buckets b
WHERE c.market_cap >= b.min_cap 
AND c.market_cap < b.max_cap
AND 1 = 1;

[marigov]

Hello Patrick, thanks for the explanation! Yes, the buckets' table would be relatively small but can dynamically change from time to time. I've tried running the query with the dummy condition AND 1 = 1 but I'm still getting the same error. Could there be something else I'm missing in the syntax or approach?

CREATE MATERIALIZED VIEW companies_bucketed AS
SELECT 
    c.company_id,
    c.market_cap,
    b.bucket_id
FROM companies c, market_cap_buckets b
WHERE c.market_cap >= b.min_cap 
AND c.market_cap < b.max_cap
AND 1 = 1;

ERROR:  Failed to run the query

Caused by:
  Not supported: streaming nested-loop join
HINT: The non-equal join in the query requires a nested-loop join executor, which could be very expensive to run. Consider rewriting the query to use dynamic filter as a substitute if possible.
See also: https://docs.risingwave.com/docs/current/sql-pattern-dynamic-filters/

[hzxa21]

Let's try this:

CREATE MATERIALIZED VIEW companies_bucketed AS
WITH market_cap_with_dummy AS (SELECT company_id, market_cap, 1 as market_dummy_col),
WITH bucket_with_dummy AS (SELECT bucket_id, min_cap, max_cap, 1 as bucket_dummy_col)
SELECT 
    c.company_id,
    c.market_cap,
    b.bucket_id
FROM market_cap_with_dummy c
JOIN bucket_with_dummy b
on c.market_cap >= b.min_cap 
AND c.market_cap < b.max_cap
AND c.market_dummy_col = b.market_dummy_col

[marigov]

This is now working. Thank you very much Patrick for the prompt response!