Invalid sql when using raw query

[tzaavi]

Version: 4.6.0 Module: quill-jdbc-zio Database: mysql

Getting invalid sql with raw query. for example this query

val q = quote {
   sql"""select id, name from users""".as[Query[(Int, Int)]]
}
val res = run(q)

Expected behavior

According to documentation is should generate this valid sql

SELECT x._1, x._2 FROM (SELECT id AS "_1", name AS "_2" FROM users) x

Actual behavior

but it generate this invalid sql

select x.1 as _1, x.2 as _2 from (select id, name from users) x

this create error: Unknown column 'x.1' in 'field list'

Workaround

the only way I could make it work is with this query

val q = quote {
   sql"""select id as "1", name as "2" from users""".as[Query[(Int, Int)]]
}
val res = run(q)

@getquill/maintainers

[fancellu]

Are raw queries still broken? I ask as I'm doing similar, for a query that runs fine in raw jdbc, but complains Invalid object on the table name with a rawQuery. i.e.

    val top2Messages = quote {
      sql"SELECT TOP (2) message FROM xyz.in".as[Query[String]]
    }
    println(top2Messages.toString())
    val ret=ctx.run(top2Messages)
    println(ret)

I get sql"SELECT TOP (2) message FROM xyz.in"

And that same query is fine in raw jdbc

[Ellzord]

This is still happening for me on 4.6.1 (postgres + zio), it's tuples specifically so single Query[String] is fine.

The workaround is to name the fields _1 not 1 from above.

[tnielens]

Seems still broken in 4.7.3. I'm using quill-cassandra-zio. The workarounds suggested precedently don't work for me for both version 4.6.1 and 4.7.3.

[guizmaii]

What about using a case class to replace the Tuple as a workaround?

final case class Result(id: Int, name: Int)
val q = quote {
   sql"""select id, name from users""".as[Query[Result]]
}
val res = run(q)

?

[tnielens]

Hi @guizmaii 👋 , thanks for quick response. I did try that as well without success. It's the same issue.