Multiple UNION query causes MariaDB to be stuck

Question

I have the following query:

SELECT
  col1
FROM
  table1
WHERE
  pid NOT IN (
    SELECT pid FROM table2 WHERE pid IS NOT NULL
    UNION
    SELECT pid FROM table3 WHERE pid IS NOT NULL
    UNION
    SELECT otherpid AS pid FROM table1 WHERE otherpid IS NOT NULL
)

All columns are of type BINARY(16).

pid is the primary key of table1.

table2.pid, table3.pid and otherpid are foreign keys pointing to the table1.pid primary key.

On MySQL 8.0.27 (Windows), the statement succeeds. On MariaDB 10.5.23 (Linux), the query does not return. It is stuck for 10 seconds, and errors out somehow (EDIT: it indeed succeeds after 10 seconds).

Could it be a bug of MariaDB, or just an (expected) difference of behavior? Is my syntax indeed valid?

It succeeds on MariaDB if I change the query to:

SELECT
  col1
FROM
  table1
WHERE
  pid NOT IN (
    SELECT pid FROM table2 WHERE pid IS NOT NULL
    UNION
    SELECT pid FROM table3 WHERE pid IS NOT NULL
  )
AND
  pid NOT IN (
    SELECT otherpid AS pid FROM table1 WHERE otherpid IS NOT NULL
  )

EDIT: here are the EXPLAIN outputs:

With the 2 NOT IN (fast version):

id      select_type        table type       possible_keys key    key_len ref    rows    Extra
1       PRIMARY            pic  range       sess        sid      17     NULL    504 Using index condition; Using where; Using temporary; Using filesort 
1       PRIMARY            sess range       PRIMARY,uid uid      16     NULL    2   Using where; Using index; Using join buffer (flat, BNL join)    
4       MATERIALIZED       pic  range       otherpid    otherpid 17     NULL    1   Using where; Using index    
2       DEPENDENT SUBQUERY gui  ref         pid         pid      16     func    1   Using index 
3       DEPENDENT UNION    gi   ref_or_null pid,iid     pid      17     func    4   Using index condition; Using where  

With 3 UNION ALL clauses:

1       PRIMARY            pic  range       sid         sid      17     NULL    504 Using index condition; Using where; Using temporary; Using filesort 
1       PRIMARY            sess range       PRIMARY,uid uid      16     NULL    2   Using where; Using index; Using join buffer (flat, BNL join)    
2       DEPENDENT SUBQUERY gui  ref         pid         pid      16     func    1   Using index 
3       DEPENDENT UNION    gi   ref_or_null pid,iid     pid      17     func    4   Using index condition; Using where  
4       DEPENDENT UNION    pic  ref_or_null otherpid    otherpid 17     func    38066   Using where; Using index

• table1 is pic
• table2 is gui
• table3 is gi

sess is not very important here.

I double checked and:

• I confirm the last subquery returns no row (so the performance penalty comes at a surprise here). It is easy enough to check because this subquery has no run-time parameters.
• there is (I verified) an non-unique INDEX on otherpid. This column may be NULL (often is). There also is a FOREIGN KEY relationship with pid.

EDIT2: running time is 10 seconds in the slow case, this is huge but is normal on my server, which is very low-end for the moment.