getdozer / getdozer/dozer

Support for `IN` clause in streaming SQL

Open
#1,659 29 comments 0 reactions 0 assignees View on GitHub
💎 Bounty
Dominant language
Rust
Stars
1.6k
Forks
145
PR merge metrics
No merged PRs in 30d

Description

## Description
Dozer streaming SQL does not currently support the `IN` operator. We need to extend support for `IN` with the following formats:

### Lookup from a static list of values
```
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);
```
This is pretty straightforward. Whenever an `Operation` is received, the value must be looked up from the list of static values provided in the SQL.

The behavior is similar to an equality condition. The following table explains how the `WHERE` operator handles messages:

- `INSERT`: The insert message is propagated if the WHERE condition is matched
- `UPDATE`: The previous and new values must be evaluated against the condition:
- If both the previous value and current value do not match the condition, no action is taken
- If both the previous value and current value match, the UPDATE is propagated downstream
- If the previous value does not match, but the new values match, an INSERT is propagated downstream
- If the previous value matches, but the new value does not match, a DELETE is propagated downstream
- `DELETE`: The insert message is propagated if the WHERE condition is matched

### Lookup from an inner `SELECT`:
```
SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT STATEMENT);
```
This case is more complex as the values of the inner SELECT can change. This can follow a similar behavior of a JOIN operator. Whenever an INSERT message is received for the inner SELECT, for example, a lookup of the parent table must be performed to emit the values that were not previously matched. This behavior is the same one implemented in the JOIN. For such a case, the DAG should be constructed using a JOIN operator.

@mediuminvader can provide more details about the JOIN implementation

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.