Summary
join() appends JOIN ... to text unconditionally and reorderSelectClauses doesn't account for JOIN, so where().join() yields SELECT ... WHERE age = $1 JOIN posts ON ... — JOIN after WHERE, rejected by all three dialects. Additionally join() (unlike innerJoin/joinSub) never sets built = null, so a join added after the statement is materialized is silently ignored.
Evidence (repro)
selectFrom('users').where({age:5}).join('posts','users.id','=','posts.user_id').toSQL()
-> ... WHERE age = $1 JOIN posts ON ...Location
src/client.ts:~4308-4312.
Suggested fix
Add JOIN to the clause-reorder model (or insert joins before any WHERE), and set built = null in join().
Severity: high. Found via internal code audit.