Correlated Subqueries: resolving ambiguous names
It resolves the column names in scope order. Inside the sub query columns will be resolved against those from the artists table if there is such ambiguity.

This can sometimes be quite useful though it can also be quite dangerous.

I usually use table aliases and two part names to avoid any unexpected issues and make it clearer to the reader which table each column is from.

SELECT p.id,
       p.title,
       (SELECT a.name
        FROM   artists a
        WHERE  p.artistid = a.id) AS artist
FROM   paintings p;