Aggregate function on empty table produces exception when executing with option

[shannonbarnes]

//> using scala "3.2.0"
//> using lib "org.tpolecat::skunk-core::0.5.1"

import cats.effect.*
import skunk.*
import skunk.implicits.*
import skunk.codec.numeric.*
import natchez.Trace.Implicits.noop
import cats.effect.unsafe.implicits.global

val session: Resource[IO, Session[IO]] =
  Session.single(
    host = "localhost",
    port = 5432,
    user = "jimmy",
    password = Some("banana"),
    database = "world"
  )

//Script hangs if these are vals ??
def max = sql"SELECT MAX(percentage) FROM countrylanguage".query(float4)
def first = sql"SELECT percentage FROM countrylanguage LIMIT 1".query(float4)
def delete = sql"DELETE FROM countrylanguage".command

session
  .use { s =>
    s.option(max).debug("MAX") *>
      s.option(first).debug("FIRST") *>
      s.execute(delete).debug("DELETE") *>
      s.option(first).debug("FIRST") *>
      s.option(max).debug("MAX")
  }
  .unsafeRunSync()

Output:

MAX: Succeeded: Some(100.0)
FIRST: Succeeded: Some(52.4)
DELETE: Succeeded: Delete(984)
FIRST: Succeeded: None
MAX: Errored: skunk.exception.DecodeException: 
🔥  
🔥  DecodeException
🔥  
🔥    Problem: Decoding error.
🔥     Detail: This query's decoder was unable to decode a row of data.
🔥  
🔥  The statement under consideration was defined
🔥    at /Users/sbarne436/Documents/workspace/skunktestbed/src/cli/.scala-build/project_2022cfbd09-e78e1385dc/src_generated/main/Test.scala:26
🔥  
🔥    SELECT MAX(percentage) FROM countrylanguage
🔥  
🔥  The row in question returned the following values (truncated to 15 chars).
🔥  
🔥    max  float4  ->  NULL  ├── Unexpected NULL value in non-optional column.

My expectation would be that after all the rows have been deleted, just like the FIRST call, the MAX call would have returned None.

[mpilquist]

The MAX function always returns a single result, and that result is NULL when there are no (non-NULL) rows in the input.

Instead of using .option, use .unique along with an optional decoder.

s.unique(sql"SELECT MAX(percentage) FROM countrylanguage".query(float4.opt))

The issue with the original is that the option function on Session (or PreparedStatement) returns a None only when the result set is empty while the float4 decoder in the query doesn't support NULL values.

[shannonbarnes]

Oh yes, explanation makes perfect sense, thanks!