JOIN returns 0 value even though there is no 0 value in any row for that column

Version: 4.6.0.1 Module: quill-jdbc Database: h2 - jdbc:h2:mem:test;DB_CLOSE_DELAY=-1;MODE=PostgreSQL;DATABASE_TO_LOWER=TRUE;DEFAULT_NULL_ORDERING=HIGH

Repro

create table if not exists Asset2Box (
    tokenId         VARCHAR(64) NOT NULL REFERENCES Asset (tokenId) ON DELETE CASCADE,
    boxId           VARCHAR(64) NOT NULL REFERENCES Box (boxId) ON DELETE CASCADE,
    amount          BIGINT NOT NULL
);

create table if not exists Utxo (
    boxId           VARCHAR(64) NOT NULL PRIMARY KEY REFERENCES Box (boxId),
    txId            VARCHAR(64) NOT NULL,
    ergoTreeHash    VARCHAR(64) NOT NULL REFERENCES ErgoTree (hash) ON DELETE CASCADE,
    ergoTreeT8Hash  VARCHAR(64) REFERENCES ErgoTreeT8 (hash),
    ergValue        BIGINT NOT NULL,
    r4              VARCHAR,
    r5              VARCHAR,
    r6              VARCHAR,
    r7              VARCHAR,
    r8              VARCHAR,
    r9              VARCHAR
);

  def lookupUnspentAssetsByTokenId(tokenId: TokenId, columns: List[String], filter: Map[String, Any]): Task[Iterable[Asset2Box]] =
    ctx
      .run {
        quote {
          query[Utxo]
            .join(query[Asset2Box])
            .on((a, utxo) => a.boxId == utxo.boxId)
            .filter((_, a) => a.tokenId == lift(tokenId))
            .map((_, a) => Asset2Box(a.tokenId, a.boxId, a.amount))
            .filterByKeys(filter)
            .filterColumns(columns)
        }
      }

produces :

SELECT CASE WHEN $1 THEN utxo.tokenId ELSE null END AS tokenId, CASE WHEN $2 THEN utxo.boxId ELSE null END AS boxId, CASE WHEN $3 THEN utxo.amount ELSE null END AS amount FROM Utxo a INNER JOIN Asset2Box utxo ON a.boxId = utxo.boxId WHERE utxo.tokenId = $4 AND (utxo.tokenId = $5 OR $6) AND (utxo.boxId = $7 OR $8) AND (utxo.amount = $9 OR $10)

columns and filter are empty

Actual behavior

all results are amount: 0, however SELECT * FROM asset2box where amount = 0 limit 1; is empty and the values are Longs, not floats/double so IDK where the 0 comes from as it is not in database.

Expected behavior

Screenshot from 2023-08-30 18-14-45

However

Screenshot from 2023-08-30 18-40-12

Guess

So maybe in CASE WHEN $3 THEN utxo.amount ELSE null END the $3 is null which turns in to 0 amount value. But it should not be :shrug:

zio / zio-protoquill