Subselect join fails

1

This SQL query fails:

SELECT id, name
FROM
    dev.backend.entity."user".v1."user" u
WHERE
    u.id NOT IN (
        SELECT DISTINCT ref_id FROM dev.backend.entity."signal".v1."signal" WHERE u.customer_id = customer_id AND ref_type = 'person' AND name IN ('Commits','ChangesPerCommit','Traceability','CodeOwnership','CycleTime','ReworkPercent')
    )
AND active = true

With the error:

org.apache.calcite.rel.logical.LogicalFilter cannot be cast to org.apache.calcite.rel.core.Join

Cannot seem to find a way to do a subselect type query (although other variations have worked in the past).

0f67da4e-ece6-481c-91bc-2dafd9324993.zip (5.8 KB)

2

Example of a query that does work fine:

SELECT
    s.customer_id,
    s.name AS "signal",
    s."value",
    s.time_unit,
    u.id AS user_id,
    u.name,
    u.location,
    u.team_id,
    u.team_name,
    u.cost
FROM
    dev.backend.entity."signal".v1."signal" s,
    dev.backend.entity."user".v1."user" u
WHERE
    s.name IN ('Commits','ChangesPerCommit','Traceability','CodeOwnership','CycleTime','ReworkPercent') AND
    s.date_ts = (SELECT MAX(date_ts) FROM dev.backend.entity."signal".v1."signal" WHERE customer_id = s.customer_id) AND
    s.ref_type = 'person' AND
    u.id = s.ref_id AND
    u.customer_id = s.customer_id
3

reworked the query and this seems to work (although not sure if this is going to be performant)

SELECT id, name
FROM
    dev.backend.entity."user".v1."user" u
WHERE
    u.id != (
        SELECT DISTINCT ref_id FROM dev.backend.entity."signal".v1."signal" WHERE u.customer_id = customer_id AND ref_type = 'person' AND name IN ('Commits','ChangesPerCommit','Traceability','CodeOwnership','CycleTime','ReworkPercent') LIMIT 1
    )
AND active = true
4

Another variation that might be better …

SELECT id, name
FROM
    dev.backend.entity."user".v1."user" u
WHERE
    (
        SELECT DISTINCT ref_id FROM dev.backend.entity."signal".v1."signal" WHERE u.customer_id = customer_id AND ref_id = u.id AND ref_type = 'person' AND name IN ('Commits','ChangesPerCommit','Traceability','CodeOwnership','CycleTime','ReworkPercent') LIMIT 1
    ) IS NULL
AND active = true
1 Like