Error with Batch Update

[mateuszbrzezinski]

Version: '4.8.0'
Module: quill-jdbc
Database: postgresql

I have model class:

case class UserGameRound(
  id: String,
  userId: UserId,
  gameId: GameId,
  roundId: String,
  brandId: Int,
  startedAt: LocalDateTime,
  completedAt: Option[LocalDateTime],
  status: GameRoundStatus,
  jurisdictionId: String,
  wageredCashAmount: Long,
  wageredBonusAmount: Long,
  supplierId: Int
)

then DAO method:

val ctx = new PostgresZioJdbcContext(SnakeCase)
import ctx._

def batchUpdate(userGameRounds: List[UserGameRound]): Task[List[UserGameRound]] = {
    val q = quote {
      liftQuery(userGameRounds).foreach { round =>
        query[UserGameRound].filter(_.id == round.id).updateValue(round).returning(h => h)
      }
    }
    run(q).implicitly
  }

Because of returning(h => h) it generates this SQL:

UPDATE user_game_round AS x3
SET id                   = round.id1,
    user_id              = round.userId,
    game_id              = round.gameId,
    round_id             = round.roundId,
    brand_id             = round.brandId,
    started_at           = round.startedAt,
    completed_at         = round.completedAt,
    status               = round.status,
    jurisdiction_id      = round.jurisdictionId,
    wagered_cash_amount  = round.wageredCashAmount,
    wagered_bonus_amount = round.wageredBonusAmount,
    supplier_id          = round.supplierId
FROM (VALUES ('UK_1', 'UK_1', 421138996, 7, '1Wb', 89, '1970-01-01 00:00:00+01'::timestamp, NULL, 'CANCELLED', 'UK',
              392318, 28052, 1)) AS round(id, id1, userId, gameId, roundId, brandId, startedAt, completedAt, status,
                                          jurisdictionId, wageredCashAmount, wageredBonusAmount, supplierId)
WHERE x3.id = round.id
RETURNING round.id, round.user_id, round.game_id, round.round_id, round.brand_id, round.started_at, round.completed_at, round.status, round.jurisdiction_id, round.wagered_cash_amount, round.wagered_bonus_amount, round.supplier_id

Then the error is obvious: was aborted: ERROR: column round.user_id does not exist, I have configured SnakeCase, but it doesnt match.

The other issue is, when I remove returning command, then it fails on diffrent case, resulting SQL is:

UPDATE user_game_round AS x3
SET id                   = round.id1,
    user_id              = round.userId,
    game_id              = round.gameId,
    round_id             = round.roundId,
    brand_id             = round.brandId,
    started_at           = round.startedAt,
    completed_at         = round.completedAt,
    status               = round.status,
    jurisdiction_id      = round.jurisdictionId,
    wagered_cash_amount  = round.wageredCashAmount,
    wagered_bonus_amount = round.wageredBonusAmount,
    supplier_id          = round.supplierId
FROM (VALUES ('UK_1', 'UK_1', 421138996, 7, '1Wb', 89, '1970-01-01 00:00:00+01'::timestamp, NULL, 'CANCELLED', 'UK',
              392318, 28052, 1)) AS round(id, id1, userId, gameId, roundId, brandId, startedAt, completedAt, status,
                                          jurisdictionId, wageredCashAmount, wageredBonusAmount, supplierId)
WHERE x3.id = round.id

And the error message is: aborted: ERROR: column "completed_at" is of type timestamp without time zone but expression is of type text Hint: You will need to rewrite or cast the expression.
Here I have no idea why it fails.

@getquill/maintainers

[andreavs]

Hi! I ran into the same error message you mention in the second issue. I was able to make it work by downgrading quill to 4.3.0. Have not been able to dig into why it happens yet, but perhaps that is useful to you.

[gr1ev0us]

Hi, I have the same problem. Downgrading quill to 4.3.0 solves the problem, but causes another one (like this #2735). I hope this will be solved soon.

[kolemannix]

Is anyone aware of a workaround for these typing issues in batch queries?

[larshagencognite]

@deusaquilus Was this issue introduced by #2571 ?

[akash1987]

One workaround is to not use batch.

Second is to update using this method, is what I have found works.

.update( _.completedAt -> sql"${e.completedAt}::timestamp".as[Option[LocalDateTime]] )