Anorm replacement of params gives error on usage of aggregate functions

Question

Table structure -

create table test
(month integer,
year integer,
thresholds decimal(18,2)
);

Static insert for simulation -

insert into test(month,year,threshold) values(4,2021,100),(5,2021,98),(6,2021,99);

If I query postgres database using anorm, it works for regular queries. However on adding aggregate functions like max, the RowParser is not able to find the alias column.

val queryString =
  """select max(month) as monthyear from test
     | where (month || '-' || year)
     | = {inQuery}""".stripMargin

    val inQuery1 = "'5-2021'"

The below on method causes the issue -

val latestInBenchmark = SQL(queryString).on("inQuery" -> inQuery1) // removing the on resolves the problem

logger.info("query for latest period ---> " + latestInBenchmark)

val latestYearMonthInInterval = database.withConnection(implicit conn => {
          latestInBenchmark.as(SqlParser.int("monthyear").*)
        })

Removing the on rectifies the problem and SqlParser.int(column-name) works as expected. This also does not affect queries that use count aggregate function.

Error encountered :

(Validated intervals with sorting -> ,Failure(anorm.AnormException: 'monthyear' not found, available columns: monthyear, monthyear))
[error] c.b.ThresholdController - 'monthyear' not found, available columns: monthyear, monthyear

Answer 1

The error you have is a bit misleading but it means the query either returns a row with a null value or no row.

In your case I think the issue is the WHERE clause: you have put single quotes around the value but Anorm will do it by itself when using .on(...) or Anorm interpolation.

Thus, replace:

val inQuery1 = "'5-2021'"

By:

val inQuery1 = "5-2021"