H2 and MySQL report spurious errors in query analyzer.

H2 and MySql (likely other database engines as well) report incorrect warnings/errors in the query analyzer. This seems to be because JDBC is being provided the incorrect information from the database engine, or maybe the JDBC drivers themselves are bad. In any case, it does not seem to be a problem with doobie, but a problem with JDBC or the DB engine.

---

MySQL Problems

Prepared statement params reported as VARCHAR

All prepared statement parameters are inferred as VARCHAR. You must use checkOutput instead of check

N.B.: Because of this you can not properly test insert/update statements at all, using the query analyzer.

Functions casting columns to nullable

Mysql function calls will always report the result as being nullable. This means that calling MIN/MAX/Foo on a NOT NULL column in a select statement, the query analyzer will see the function result as NULLable, even when it is not.

---

H2 Problems

Some types reported as BINARY

Some types are reported as being binary blobs (demanding an Array[Char] based Meta instance)

Known types with this issue:

• UUID

Example:

✕ C01 ID   BINARY    (UUID)      NOT NULL  →  UUID
    - BINARY (UUID) is not coercible to UUID according to the JDBC specification or
      any defined mapping. Fix this by changing the schema type to OTHER or
      JAVAOBJECT, or the Scala type to Array[Byte].

VARCHAR column type reported for all writes

All insert, update and modify queries report VARCHAR types for all of the columns being inserted into/modified. The prepared parameter types are inferred correctly, but the columns in sql are reported incorrectly.

Example:

MERGE INTO fail(id, time, bool)
      KEY (id)
      VALUES (?, ?, ?)

  ✓ SQL Compiles and Typechecks
  ✕ P01 UUID     →  VARCHAR (VARCHAR)
    - UUID is not coercible to VARCHAR (VARCHAR) according to the JDBC specification.
      Fix this by changing the schema type to OTHER, or the Scala type to String.
  ✕ P02 Instant  →  VARCHAR (VARCHAR)
    - Instant is not coercible to VARCHAR (VARCHAR) according to the JDBC
      specification. Fix this by changing the schema type to TIMESTAMP, or the Scala
      type to String.
  ✕ P03 Boolean  →  VARCHAR (VARCHAR)
    - Boolean is not coercible to VARCHAR (VARCHAR) according to the JDBC
      specification. Fix this by changing the schema type to BIT, or the Scala type to
      String.

---

Old discussion/issue topic.

Continuing the discussion from gitter:

// using ammonite

import $ivy.`org.tpolecat::doobie-h2-cats:0.4.1`
import $ivy.`org.tpolecat::doobie-core-cats:0.4.1`
import doobie.imports._
import doobie.h2.imports._
val transactor = DriverManagerTransactor[IOLite]( 
  "org.h2.Driver", "jdbc:h2:./test:test", "", "" 
)
import transactor.yolo._
fr"""
      CREATE TABLE IF NOT EXISTS fail(
        id UUID PRIMARY KEY,
        time TIMESTAMP NOT NULL,
        bool BOOLEAN NOT NULL DEFAULT FALSE
      )
""".update.quick.unsafePerformIO

sql"select * from fail".query[(java.util.UUID, java.time.Instant, Boolean)].check.unsafePerformIO
/*
  select * from fail

  ✓ SQL Compiles and Typechecks
  ✕ C01 ID   BINARY    (UUID)      NOT NULL  →  UUID
    - BINARY (UUID) is not coercible to UUID according to the JDBC specification or
      any defined mapping. Fix this by changing the schema type to OTHER or
      JAVAOBJECT, or the Scala type to Array[Byte].
  ✓ C02 TIME TIMESTAMP (TIMESTAMP) NOT NULL  →  Instant
  ✓ C03 BOOL BOOLEAN   (BOOLEAN)   NOT NULL  →  Boolean
*/

Start by reproducing the H2 and MySQL query-analyzer cases described in the issue, including MySQL prepared parameters and H2 UUID and write-column reports. Compare check with checkOutput for the MySQL examples and inspect the reported JDBC metadata; done means the documented false warnings and errors no longer appear.