Using `from` from the experimental DSL can lead to "unknown column" errors (on MySQL/MariaDB)

Suppose this toy schema:

share
  [mkPersist sqlSettings]
  [persistLowerCase|
    User

    Post
      author (UserId)
|]

And this query code:

import Database.Esqueleto.Experimental

userPosts :: SqlExpr (Value UserId) -> SqlQuery (SqlExpr (Entity Post))
userPosts userId = do
  post <- from $ table @Post
  where_ $ post.author ==. userId
  pure post

userPostCount :: SqlExpr (Value UserId) -> SqlExpr (Value Word)
userPostCount userId =
  coalesceDefault
    [ subSelect $ do
        -- The `from` is unnecessary here, but not in the code where I ran into this bug
        -- (because it also involves a join).
        post <- from $ userPosts userId
        pure $ count post.id
    ]
    (val 0)

usersWithPostCounts :: SqlQuery (SqlExpr (Entity User), SqlExpr (Value Word))
usersWithPostCounts = do
  user <- from $ table @User
  pure (user, userPostCount user.id)

Printing userWithPostCounts produces SQL that looks like this:

SELECT
  `user`.`id`,
  COALESCE(
    (SELECT COUNT(`q`.`v_id`)
      FROM
        (SELECT `post`.`id` AS `v_id`, `post`.`author` AS `v_author`
          FROM `post`
          WHERE `post`.`author` = `user`.`id`
        ) AS `q`
      LIMIT 1
    ),
    0
  )
FROM `user`

For me on MariaDB at least, running this produces an error:

ERROR 1054 (42S22): Unknown column 'user.id' in 'where clause'

(The where clause being WHERE `post`.`author` = `user`.`id`.)

So it seems like using from with a subquery doesn't always produce well-scoped code: user.id isn't in scope in the subquery in the FROM clause.

bitemyapp / esqueleto

Using `from` from the experimental DSL can lead to "unknown column" errors (on MySQL/MariaDB) #366