What are all the ways that a SELECT statement could be made to not terminate or take a *very* long time? One way is using WITH RECURSIVE:
WITH RECURSIVE
  t AS (
    SELECT 1 n
    UNION ALL
    SELECT n + 1 FROM t
  )
  SELECT * FROM t;

Joins can be made to return a lot of rows from even small tables.
Tables themselves could be huge.
Views can be done with any such SELECT statement and so SELECT statements that use them may also not terminate.
Is that it? If SELECT statements did not have WITH RECURSIVE syntax, could not do arbitrary joins, were limited to small tables or hard a small LIMIT enforced, and could not use any such view, would they all be guaranteed to terminate in a reasonable time*?
I'm particularly interested in MariaDB/MySQL.
I'm thinking of making a new language based on SQL's SELECT statements that transpiles/compiles to SQL and I'm wondering what I can do to guarantee query termination in reasonable time*.
* What's reasonable time is of course subjective. I just mention it to exclude queries that are set to terminate in a time frame so long that we may as well treat it as non-terminating.
MariaDB-10.1.1 has max_statement_time to abort long-running statements.  I don't think MySQL has anything equivalent.
(Of course, you need at least 10.2 to get Recursive CTEs.)