In PostgreSQL, the subquery `toExpr` will result in a syntax error.

[ETCHKILI]

TLDR;

The Select.toExpr function does not add parentheses to the subquery.

Minimal Reproduction

https://scastie.scala-lang.org/s1eHSaYhSV2yrJfhgqpksA

import scalasql.simple.*
import scalasql.PostgresDialect.*

case class Country(id: Long, name: String)
object Country extends SimpleTable[Country]

case class User(id: Long, countryId: Long)
object User extends SimpleTable[User]

val stmt = User
  .select
  .filter(_.id > 0)
  .filter(u =>
    Country
      .select
      .filter(_.id === u.countryId)
      .map(_.name === "xxx")
      .toExpr
  )

// ignoring the construction of dbClient

dbClient.transaction { db =>
  println(db `renderSql` stmt)
}
  

Result SQL

SELECT user0.id AS id, user0.country_id AS country_id FROM user user0 WHERE (user0.id > ?) AND SELECT (country1.name = ?) AS res FROM country country1 WHERE (country1.id = user0.country_id)

Which leads to an syntax error

[2025-11-04 15:39:14] [42601] ERROR: syntax error at or near "SELECT"
[2025-11-04 15:39:14] Position: 96

Possible fix

scalasql/scalasql/query/src/Select.scala

Line 241 in e6adf19

def toExpr(implicit mt: TypeMapper[R]): Expr[R] = Expr { implicit ctx => this.renderSql(ctx) }

-def toExpr(implicit mt: TypeMapper[R]): Expr[R] = Expr { implicit ctx => this.renderSql(ctx) }
+def toExpr(implicit mt: TypeMapper[R]): Expr[R] = Expr { implicit ctx => sql"(${this.renderSql(ctx)})" }

[ivan-klass]

@ETCHKILI It seems like the statement you're building is weird on it's own, it tries to use SELECT boolExpr result set (i.e. a set of rows) - as a scalar boolean, which tends to be unsound.

It should be something like EXISTS (SELECT ... WHERE boolExpr), which you can simply achieve with .filter instead of .map and .nonEmpty instead of .toExpr

Here's the valid statement for your case:

val stmt = User
  .select
  .filter(_.id > 0)
  .filter(u =>
    Country
      .select
      .filter(_.id === u.countryId)
      .filter(_.name === "xxx")
      .nonEmpty
  ) 

Or, it could be a simple join (I personally prefer this way as a more explicit and also optimization-friendly for the db engine):

val stmt = User
  .select
  .filter(_.id > 0)
  .join(
    Country
      .select
      .filter(_.name === "xxx")
  ){
     case (u, c) => u.countryId === c.id
  }
  .map {
    case (u, c) => u
  }

[ETCHKILI]

@ivan-klass
Yes, this is not a great example. Unfortunately, I couldn't find any other case that triggers this syntax error. My thinking is that since Postgres doesn't fully accept subqueries as expressions without parentheses at the syntax level, it seems safer to always wrap queries in parentheses within the toExpr method. That said, given that no more significant issues have surfaced, it may be reasonable to leave things as they are.