Works just fine in Trino.

  trino> USE memory.default;
  USE
  trino:default> create table artist (artistid int);
  CREATE TABLE
  trino:default> create table album (albumid int, artistid int);
  CREATE TABLE
  trino:default> insert into artist values 1, 2;
  INSERT: 2 rows
  
  Query 20220804_182827_00005_n4rat, FINISHED, 1 node
  Splits: 19 total, 19 done (100.00%)
  0.52 [0 rows, 0B] [0 rows/s, 0B/s]
  
  trino:default> insert into album values (11, 1), (12, 1), (21, 2);
  INSERT: 3 rows
  
  Query 20220804_182857_00006_n4rat, FINISHED, 1 node
  Splits: 19 total, 19 done (100.00%)
  0.18 [0 rows, 0B] [0 rows/s, 0B/s]
  
  trino:default> select * from (select * from artist limit 2) a cross join lateral (select * from album where album.artistid = a.artistid limit 2);
   artistid | albumid | artistid
  ----------+---------+----------
          1 |      12 |        1
          1 |      11 |        1
          2 |      21 |        2
  (3 rows)
  
  Query 20220804_182930_00007_n4rat, FINISHED, 1 node
  Splits: 41 total, 41 done (100.00%)
  0.35 [8 rows, 232B] [22 rows/s, 661B/s]