Generated query not following case sentitive properties

[cartagena]

Version: 3.0.1 Module: quill-async-mysql Database: MySQL 5.7

Expected behavior

SELECT X.ID FROM (SELECT DISTINCT X02.* FROM TABLE1 X02 INNER JOIN TABLE2 X11 ON X02.ID = X11.TABLE_2_ID WHERE (X11.TYPE <> 'SOME_TYPE')) X Note the uppercase X02 in the inner select.

Actual behavior

SELECT X.ID FROM (SELECT DISTINCT x02.* FROM TABLE1 X02 INNER JOIN TABLE2 X11 ON X02.ID = X11.TABLE_2_ID WHERE (X11.TYPE <> 'SOME_TYPE')) X

Steps to reproduce the behavior

Set lower_case_table_names system variable to 0 in MySQL and run the following code:

    val q = quote {
      for {
        (table1, table2) <- query[Table1] join query[Table2] on {
          case (t1, t2) => t2.id == t1.table2Id
        } if table2.type != lift(SOME_TYPE)
      } yield table1
    }

    ctx.run(q).distinct

I've noticed this error occurs only when I'm using distinct.

Workaround

Set lower_case_table_names system variable to 1 in MySQL

@getquill/maintainers

[deusaquilus]

@cartagena What does that code where you create your context look like? Are you using Literal for naming or something else?

[cartagena]

@deusaquilus I'm composing SnakeCase and UpperCase as below:

val ctx: MysqlAsyncContext[CompositeNamingStrategy2[SnakeCase.type, UpperCase.type]] = {
    val mysqlConfig = MysqlAsyncContextConfig(config)
    new MysqlAsyncContext(NamingStrategy(SnakeCase, UpperCase), mysqlConfig) with QueryProbing
  }

I've worked around the issue renaming the variable to be all uppercase.

    val q = quote {
      // upper case variable: work around as using distinct in query was breaking quill (https://github.com/getquill/quill/issues/1335).
      query[Table1].join(query[Table2]).on((P, table2) =>
          table2.table1Id == P.id &&
          P.type == lift(SOME_TYPE)
      ).map(_._1).distinct
    }

    ctx.run(q)

I suppose quill just reuses the variable name regardless its case. I've also had issue with a variable named KEY where the generated SQL query as invalid due the misuse of the reserved word KEY.