Subquery result as function parameter

Question

I have two tables:

• Entities: | id UNSIGNED INT | pos POINT | ... |
• Relations: | id UNSIGNED INT | srcId UNSIGNED INT | dstId UNSIGNED INT | ... |

Columns id in both tables are primary keys.

Columns srcId and dstId of Relation are references (foreign key) to Entities id column.

I am trying to make a procedure that will select all Relations where either of following is true:

1. srcId Entity is inside of newRect and outside of oldRect AND dstId Entity is inside of newRect and outside of oldRect
2. srcId Entity is inside of newRect and outside of oldRect AND dstId Entity is outside of newRect and outside of oldRect
3. srcId Entity is outside of newRect and outside of oldRect AND dstId Entity is inside of newRect and outside of oldRect

DECLARE oldRect DEFAULT ENVELOPE(LINESTRING(POINT(0, 0), POINT(500, 500)));
DECLARE newRect DEFAULT ENVELOPE(LINESTRING(POINT(0, 0), POINT(1000, 1000)));

SELECT DISTINCT r.*
FROM Entities AS e
JOIN Relations AS r ON e.id IN (r.srcId, r.dstId)
WHERE
    ST_CONTAINS(newRect,
        SELECT re.pos
        FROM Entities AS re
        WHERE re.id = srcId
    )
AND NOT
    ST_CONTAINS(oldRect,
        SELECT re.pos
        FROM Entities AS re
        WHERE re.id = srcId
    )
AND
    ST_CONTAINS(newRect,
        SELECT re.pos
        FROM Entities AS re
        WHERE re.id = dstId
    )
AND NOT
    ST_CONTAINS(oldRect,
        SELECT re.pos
        FROM Entities AS re
        WHERE re.id = dstId
    )
.... ?;

EDIT: I figured out a way, am wondering if there is any better. If I create a new function and call that function.

CREATE FUNCTION getPos(id INT UNSIGNED) RETURNS POINT
BEGIN
    DECLARE pos POINT DEFAULT NULL;
    SELECT re.pos INTO pos
    FROM Entities AS re
    WHERE re.id = id;
RETURN pos;
END

Answer 1

As Bill says, subqueries must always be enclosed in parentheses.

But there's no point in doing select...from Entities e...join Relations when you don't appear to use e at all. You should select...from Relations. And if you join Entities twice (once for src, once for dst) you don't need subqueries at all. Then DISTINCT isn't needed, since you will only have one possible row per Relations row.

And your logic seems to boil down to:

dstId Entity is outside of oldRect
AND
srcId Entity is outside of oldRect
AND
(
    srcId Entity is inside of newRect
    OR
    dstId Entity is inside of newRect
)

So:

select r.*
from Relations r
join Entities se on se.id=r.srcId
join Entities de on de.id=r.dstId
where
    not st_contains(oldRect, de.pos)
    and not st_contains(oldRect, se.pos)
    and (
        st_contains(newRect, se.pos)
        or st_contains(newRect, de.pos)
    )