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 matchedUPDATE: 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
Description
Dozer streaming SQL does not currently support the
INoperator. We need to extend support forINwith the following formats:Lookup from a static list of values
This is pretty straightforward. Whenever an
Operationis 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
WHEREoperator handles messages:INSERT: The insert message is propagated if the WHERE condition is matchedUPDATE: The previous and new values must be evaluated against the condition:DELETE: The insert message is propagated if the WHERE condition is matchedLookup from an inner
SELECT: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