{-# LANGUAGE BlockArguments #-}
{-# LANGUAGE DataKinds #-}
{-# LANGUAGE DeriveAnyClass #-}
{-# LANGUAGE DeriveGeneric #-}
{-# LANGUAGE DerivingVia #-}
{-# LANGUAGE LambdaCase #-}
{-# LANGUAGE NamedFieldPuns #-}
{-# LANGUAGE OverloadedStrings #-}
{-# LANGUAGE ScopedTypeVariables #-}
{-# LANGUAGE TypeApplications #-}
{-# LANGUAGE TypeOperators #-}
-- |
-- Module : Main
-- Description : Tasty-based test suite for duckdb-simple.
module Main (main) where
import ArrowTests (arrowTests)
import CancellationTests (callbackLockTests, cancellationTests)
import Control.Applicative ((<|>))
import Control.Concurrent (forkFinally, newEmptyMVar, putMVar, readMVar)
import Control.Exception (ErrorCall, Exception, SomeException, bracket, displayException, fromException, throwIO, try)
import Control.Monad (forM_, replicateM_, void, when)
import CopyTests (copyTests)
import CoreRegressionTests (coreRegressionTests)
import Data.Array (elems, listArray)
import qualified Data.ByteString as BS
import Data.IORef (atomicModifyIORef', newIORef, readIORef)
import Data.Int (Int16, Int32, Int64, Int8)
import Data.List (sortOn)
import qualified Data.Map.Strict as Map
import Data.Maybe (fromJust)
import Data.Proxy (Proxy (..))
import Data.Ratio ((%))
import Data.String (fromString)
import qualified Data.Text as Text
import Data.Time.Calendar (Day, fromGregorian)
import Data.Time.Clock (UTCTime (..), secondsToDiffTime)
import Data.Time.LocalTime (
LocalTime (..),
TimeOfDay (..),
localTimeToUTC,
timeOfDayToTime,
utc,
)
import qualified Data.UUID as UUID
import Data.Word (Word16, Word32, Word64, Word8)
import Database.DuckDB.Simple
import qualified Database.DuckDB.Simple.Catalog as Catalog
import qualified Database.DuckDB.Simple.Config as Config
import qualified Database.DuckDB.Simple.Copy as Copy
import qualified Database.DuckDB.Simple.FileSystem as FileSystem
import Database.DuckDB.Simple.FromField (
BigNum (..),
BitString (..),
DecimalValue (..),
Field (..),
FieldValue (..),
IntervalValue (..),
TimeWithZone (..),
bsFromBool,
fromBigNumBytes,
returnError,
toBigNumBytes,
)
import Database.DuckDB.Simple.Generic (
DuckValue (..),
ViaDuckDB (..),
genericFromFieldValue,
genericLogicalType,
genericToFieldValue,
genericToStructValue,
)
import qualified Database.DuckDB.Simple.Logging as Logging
import Database.DuckDB.Simple.LogicalRep (
LogicalTypeRep (..),
StructField (..),
StructValue (..),
UnionMemberType (..),
UnionValue (..),
)
import Database.DuckDB.Simple.Ok (Ok (..))
import Database.DuckDB.Simple.Time (Unbounded (..))
import Database.DuckDB.Simple.ToField (ToDuckValue (..))
import Database.DuckDB.Simple.Variant (Variant (..), variantObject)
import ExtensionRegressionTests (extensionRegressionTests)
import GHC.Generics (Generic)
import qualified GeometryRegressionTests
import Numeric.Natural (Natural)
import Properties (roundTripTests)
import RegistrationTests (registrationTests)
import SizedArrayTests (sizedArrayTests)
import qualified SqlFeatureTests
import StreamingTests (nativeStreamingTests)
import System.Directory (doesFileExist, getTemporaryDirectory, removeFile)
import System.IO (hClose, openBinaryTempFile)
import System.Timeout (timeout)
import Test.Tasty (TestTree, Timeout (..), adjustOption, defaultMain, mkTimeout, testGroup)
import Test.Tasty.HUnit
import Test.Tasty.QuickCheck (testProperty, (===))
import TestUtils (sized)
import TimeTests (timeTests)
import TypedRegistrationTests (typedRegistrationTests)
import UserCastTests (geometryArgumentTests, userCastTests)
import ValueRegressionTests (valueRegressionTests)
import qualified VariantRegressionTests
data Person = Person
{ personId :: Int
, personName :: Text.Text
}
deriving stock (Eq, Show, Generic)
deriving anyclass (FromRow)
data WithRemaining = WithRemaining Int Int
deriving (Eq, Show)
data GenericRecord = GenericRecord
{ grId :: Int
, grName :: Text.Text
}
deriving stock (Eq, Show, Generic)
data GenericSum
= SumInt Int
| SumBool Bool
| SumPair Int Text.Text
deriving stock (Eq, Show, Generic)
data GenericEnum
= EnumRed
| EnumBlue
deriving stock (Eq, Show, Generic)
data ViaUser = ViaUser
{ vuId :: Int64
, vuName :: Text.Text
}
deriving stock (Eq, Show, Generic)
deriving (DuckDBColumnType, ToDuckValue, ToField, FromField) via (ViaDuckDB ViaUser)
-- | A newtype with its own type text, for a scalar function argument.
newtype Price = Price DecimalValue
instance DuckDBColumnType Price where
duckdbColumnTypeFor _ = "DECIMAL(18,3)"
instance FromField Price where
fromField value = Price <$> fromField value
-- | A newtype with its own type text, for a scalar function result.
newtype Amount = Amount Double
instance DuckDBColumnType Amount where
duckdbColumnTypeFor _ = "DECIMAL(18,3)"
instance ToDuckValue Amount where
toDuckValue factory (Amount value) = toDuckValue factory value
data ViaShape
= ViaCircle Double
| ViaSquare Double
deriving stock (Eq, Show, Generic)
deriving (DuckDBColumnType, ToDuckValue, ToField, FromField) via (ViaDuckDB ViaShape)
-- | A UNION with a STRUCT payload that contains a VARIANT.
data VariantChoice = Payload Variant | NoPayload
deriving stock (Eq, Show, Generic)
deriving (DuckDBColumnType, ToField, FromField) via (ViaDuckDB VariantChoice)
-- Edge case test types
data TwoFieldStruct = TwoFieldStruct Int Text.Text
deriving stock (Eq, Show, Generic)
data LargeSumType
= LargeC1 Int
| LargeC2 Int
| LargeC3 Int
| LargeC4 Int
| LargeC5 Int
deriving stock (Eq, Show, Generic)
deriving (DuckDBColumnType, ToField, FromField) via (ViaDuckDB LargeSumType)
data EmptyStruct = EmptyStruct
deriving stock (Eq, Show, Generic)
deriving (DuckDBColumnType, ToField, FromField) via (ViaDuckDB EmptyStruct)
-- | A field of the type EmptyStruct uses the generic encoding.
instance DuckValue EmptyStruct where
duckToField = genericToFieldValue
duckFromField = genericFromFieldValue
duckLogicalType = genericLogicalType
-- | A record with two optional fields of a type without fields.
data OptionalEmpty = OptionalEmpty
{ oeFirst :: Maybe EmptyStruct
, oeSecond :: Maybe EmptyStruct
}
deriving stock (Eq, Show, Generic)
deriving (DuckDBColumnType, ToField, FromField) via (ViaDuckDB OptionalEmpty)
-- | A record with a field of a type without fields.
data WithEmpty = WithEmpty
{ weId :: Int64
, weEmpty :: EmptyStruct
}
deriving stock (Eq, Show, Generic)
deriving (DuckDBColumnType, ToField, FromField) via (ViaDuckDB WithEmpty)
-- | A sum with a constructor that has a field and a constructor without fields.
data Tally
= Counted Int64
| Uncounted
deriving stock (Eq, Show, Generic)
deriving (DuckDBColumnType, ToField, FromField) via (ViaDuckDB Tally)
data OptionalFields = OptionalFields
{ ofInt :: Maybe Int
, ofText :: Maybe Text.Text
}
deriving stock (Eq, Show, Generic)
deriving (DuckDBColumnType, ToField, FromField) via (ViaDuckDB OptionalFields)
instance FromRow WithRemaining where
fromRow = do
firstVal <- field
remaining <- numFieldsRemaining
replicateM_ remaining (fieldWith (const (pure ())))
pure (WithRemaining firstVal remaining)
newtype YesNo = YesNo Bool
deriving (Eq, Show)
instance FromRow YesNo where
fromRow = parseYes <|> parseNo
where
parseYes = YesNo True <$ fieldWith (match (Text.pack "yes"))
parseNo = YesNo False <$ fieldWith (match (Text.pack "no"))
match expected fld@Field{} = do
txt <- fromField fld
if Text.toLower txt == expected
then pure ()
else returnError ConversionFailed fld "failed to match exact string"
newtype NonEmptyText = NonEmptyText Text.Text
deriving (Eq, Show)
nonEmptyTextParser :: FieldParser NonEmptyText
nonEmptyTextParser f@Field{} = do
txt <- fromField f
if Text.null txt
then returnError ConversionFailed f "NonEmptyText requires a non-empty string"
else pure (NonEmptyText txt)
instance FromField NonEmptyText where
fromField = nonEmptyTextParser
-- | Run each case with a time limit of 10 minutes. Thus a deadlock fails its
-- case, and not the job. The limit is long, because the suite also runs under
-- Valgrind. The limit cannot stop a thread in a native call. A time limit of
-- the command line (@--timeout@) replaces this limit.
main :: IO ()
main = defaultMain (adjustOption limit tests)
where
limit NoTimeout = mkTimeout (10 * 60 * 1000000)
limit given = given
tests :: TestTree
tests =
testGroup
"duckdb-simple"
[ connectionTests
, coreRegressionTests
, arrowTests
, cancellationTests
, callbackLockTests
, nativeStreamingTests
, extensionRegressionTests
, registrationTests
, typedRegistrationTests
, copyTests
, GeometryRegressionTests.tests
, VariantRegressionTests.tests
, userCastTests
, geometryArgumentTests
, valueRegressionTests
, sizedArrayTests
, timeTests
, SqlFeatureTests.tests
, withConnectionTests
, statementTests
, roundTripTests
, typeTests
, streamingTests
, functionsTests
, v20Tests
, transactionTests
]
connectionTests :: TestTree
connectionTests =
testGroup
"open/close"
[ testCase "opens and closes an in-memory database" $ do
conn <- open ":memory:"
close conn
, testCase "allows closing the same connection twice" $ do
conn <- open ":memory:"
close conn
close conn
, testCase "opens with startup config" $ do
conn <- openWithConfig ":memory:" [("threads", "1")]
mValue <- Config.getConfigOption conn "threads"
close conn
case mValue of
Nothing -> assertFailure "expected threads config value"
Just value -> Config.configValueText value @?= Just "1"
]
v20Tests :: TestTree
v20Tests =
testGroup
"DuckDB 2.0 wrappers"
[ testCase "lists config flags" $ do
flags <- withConnection ":memory:" Config.listConfigFlags
assertBool "expected at least one config flag" (not (null flags))
assertBool "expected threads flag" (any ((== "threads") . Config.configFlagName) flags)
, testCase "catalog lookup works inside a transaction" $
withConnection ":memory:" \conn -> do
_ <- execute_ conn "CREATE TABLE catalog_probe(id INTEGER)"
withTransaction conn $ do
mType <- Catalog.catalogTypeName conn "memory"
assertBool "expected catalog type name" (mType /= Nothing)
mEntry <- Catalog.lookupCatalogEntry conn "memory" "main" "catalog_probe" Catalog.CatalogTable
case mEntry of
Nothing -> assertFailure "expected catalog entry"
Just entry -> Catalog.catalogEntryName entry @?= "catalog_probe"
, testCase "file system wrappers round-trip bytes" $ withTemporaryPath \path -> do
let payload = BS.pack [0, 1, 2, 3, 255]
withConnection ":memory:" \conn -> do
FileSystem.withFileHandle conn path [FileSystem.FileFlagCreate, FileSystem.FileFlagWrite] \handle -> do
written <- FileSystem.writeFileHandleBytes handle payload
written @?= fromIntegral (BS.length payload)
FileSystem.fileHandleSync handle
FileSystem.withFileHandle conn path [FileSystem.FileFlagRead] \handle -> do
size <- FileSystem.fileHandleSize handle
size @?= fromIntegral (BS.length payload)
bytes <- FileSystem.readFileHandleChunk handle 32
bytes @?= payload
removeFile path
, testCase "copy wrappers receive COPY TO rows" $
withTemporaryPath \path -> withConnection ":memory:" \conn -> do
capturedRef <- newIORef ([] :: [Int])
pathRef <- newIORef Nothing
Copy.registerCopyToFunction
conn
"hs_capture"
(\bindInfo -> pure (length (Copy.copyBindColumnTypes bindInfo)))
( \initInfo -> do
atomicModifyIORef' pathRef \_ -> (Just (Copy.copyInitFilePath initInfo), ())
pure (Copy.copyInitBindState initInfo)
)
( \sinkInfo rows -> do
Copy.copySinkBindState sinkInfo @?= 1
Copy.copySinkGlobalState sinkInfo @?= 1
values <- mapM fieldInt rows
atomicModifyIORef' capturedRef \existing -> (existing <> values, ())
)
( \finalizeInfo -> do
Copy.copyFinalizeBindState finalizeInfo @?= 1
Copy.copyFinalizeGlobalState finalizeInfo @?= 1
)
_ <- execute conn "COPY (SELECT * FROM (VALUES (1), (2), (3)) AS t(x)) TO ? (FORMAT hs_capture)" (Only (Text.pack path))
captured <- readIORef capturedRef
filePath <- readIORef pathRef
captured @?= [1, 2, 3]
filePath @?= Just path
, testCase "custom log storage registers a native sink" do
entries <- newIORef []
withConnection ":memory:" \conn -> do
Logging.registerLogStorage conn "hs_log_capture" (\entry -> atomicModifyIORef' entries (\values -> (entry : values, ())))
void $ execute_ conn "CALL enable_logging(); SET logging_storage = 'hs_log_capture'; SELECT write_log('registered')"
-- The sink receives the entry of write_log one time.
readIORef entries >>= (@?= ["registered"]) . filter (== "registered") . map Logging.logEntryMessage
]
fieldInt :: [Field] -> IO Int
fieldInt [fld] =
runConversion (fromField fld) >>= \case
Errors err -> assertFailure (show err) >> pure 0
Ok value -> pure value
fieldInt fields = assertFailure ("expected one field, got " <> show (length fields)) >> pure 0
withConnectionTests :: TestTree
withConnectionTests =
testGroup
"withConnection"
[ testCase "returns the action result" $ do
result <- withConnection ":memory:" \_ -> pure (21 :: Int)
assertEqual "action result" 21 result
, testCase "propagates exceptions from the action"
$ assertThrowsErrorCall
$ withConnection ":memory:" (\_ -> error "boom" :: IO ())
]
statementTests :: TestTree
statementTests =
testGroup
"statements"
[ testCase "prepares and closes a simple statement" $
withConnection ":memory:" \conn -> do
stmt <- openStatement conn "SELECT 42"
closeStatement stmt
, testCase "allows closing a statement twice" $
withConnection ":memory:" \conn -> do
stmt <- openStatement conn "SELECT 1"
closeStatement stmt
closeStatement stmt
, testCase "rejects preparing statements on a closed connection" $ do
conn <- open ":memory:"
close conn
assertThrowsSQLError $
openStatement conn "SELECT 1"
, testCase "throws SQLError for invalid SQL" $
withConnection ":memory:" \conn ->
assertThrowsSQLError $
openStatement conn "THIS IS NOT VALID SQL"
, testCase "withStatement closes the statement automatically" $
withConnection ":memory:" \conn -> do
(result, stmt) <- withStatement conn "SELECT 1" \stmt -> pure ("done" :: String, stmt)
assertEqual "withStatement result" "done" result
-- A read of the closed statement raises an error.
assertThrowsSQLError (nextRow stmt :: IO (Maybe (Only Int)))
, testCase "withStatement closes statements even when the action fails" $
withConnection ":memory:" \conn -> do
used <- newIORef Nothing
assertThrowsErrorCall $
withStatement conn "SELECT 1" (\stmt -> atomicModifyIORef' used (const (Just stmt, ())) >> error "boom" :: IO ())
-- A read of the closed statement raises an error.
readIORef used
>>= maybe (assertFailure "the action did not run") (\closed -> assertThrowsSQLError (nextRow closed :: IO (Maybe (Only Int))))
stmt <- openStatement conn "SELECT 1"
closeStatement stmt
, testCase "execute returns affected row count" $
withConnection ":memory:" \conn -> do
_ <- execute_ conn "CREATE TABLE test_exec (x INTEGER)"
count <- execute conn "INSERT INTO test_exec VALUES (?)" (Only (1 :: Int))
assertEqual "rows affected" 1 count
, testCase "execute runs with positional parameters" $
withConnection ":memory:" \conn -> do
_ <- execute_ conn "CREATE TABLE exec_params (a INTEGER, b TEXT)"
count <- execute conn "INSERT INTO exec_params VALUES (?, ?)" (5 :: Int, "hi" :: String)
assertEqual "rows affected" 1 count
, testCase "rejects invalid direct execution" $
withConnection ":memory:" \conn ->
assertThrowsSQLError $
execute_ conn "THIS IS NOT SQL"
, testCase "binds positional parameters" $
withConnection ":memory:" \conn -> do
_ <- execute_ conn "CREATE TABLE params (a INTEGER, b TEXT)"
withStatement conn "INSERT INTO params VALUES (?, ?)" \stmt -> do
bind stmt [toField (5 :: Int), toField ("hi" :: String)]
changed <- executeStatement stmt
assertEqual "rows affected" 1 changed
, testCase "executeMany inserts each row and gives the total count" $
withConnection ":memory:" \conn -> do
_ <- execute_ conn "CREATE TABLE params_many (a INTEGER, b TEXT)"
total <- executeMany conn "INSERT INTO params_many VALUES (?, ?)" [(1 :: Int, "x" :: String), (2 :: Int, "y" :: String)]
assertEqual "rows affected" 2 total
query_ conn "SELECT a, b FROM params_many ORDER BY a" >>= (@?= [(1 :: Int, "x" :: String), (2, "y")])
, testCase "executeNamed binds named parameters" $
withConnection ":memory:" \conn -> do
_ <- execute_ conn "CREATE TABLE named_params (a INTEGER, b TEXT)"
count <- executeNamed conn "INSERT INTO named_params VALUES ($a, $b)" ["$a" := (1 :: Int), "$b" := ("named" :: String)]
assertEqual "rows affected" 1 count
rows <- queryNamed conn "SELECT a FROM named_params WHERE b = $label" ["$label" := ("named" :: String)]
assertEqual "named query" [Only (1 :: Int)] rows
, testCase "rejects incorrect positional argument counts" $
withConnection ":memory:" \conn -> do
_ <- execute_ conn "CREATE TABLE param_count (a INTEGER, b INTEGER)"
assertThrowsFormatError
(execute conn "INSERT INTO param_count VALUES (?, ?)" (Only (1 :: Int)))
(Text.isInfixOf "parameter(s)" . formatErrorMessage)
, testCase "rejects positional bindings on named statements" $
withConnection ":memory:" \conn -> do
_ <- execute_ conn "CREATE TABLE mixed_mode (a INTEGER)"
assertThrowsFormatError
(execute conn "INSERT INTO mixed_mode VALUES ($a)" (Only (1 :: Int)))
(Text.isInfixOf "named parameters" . formatErrorMessage)
, testCase "reports error when mixing positional and named parameters" $
withConnection ":memory:" \conn -> do
_ <- execute_ conn "CREATE TABLE mixed_params (a INTEGER, b TEXT)"
assertThrows
( withStatement conn "INSERT INTO mixed_params VALUES (?, $label)" \stmt -> do
bind stmt [toField (1 :: Int)]
bindNamed stmt ["$label" := ("combo" :: String)]
_ <- executeStatement stmt
pure ()
)
( \(err :: SQLError) ->
Text.isInfixOf "Mixing named and positional parameters" (sqlErrorMessage err)
)
, testCase "generic FromRow derivation works" $
withConnection ":memory:" \conn -> do
_ <- execute_ conn "CREATE TABLE person (id INTEGER, name TEXT)"
_ <- executeMany conn "INSERT INTO person VALUES (?, ?)" [(1 :: Int, "Alice" :: String), (2 :: Int, "Bob" :: String)]
people <- query_ conn "SELECT id, name FROM person ORDER BY id" :: IO [Person]
assertEqual "person rows" [Person 1 (Text.pack "Alice"), Person 2 (Text.pack "Bob")] people
, testCase "(:.) composes row parsing and parameter encoding" $
withConnection ":memory:" \conn -> do
_ <- execute_ conn "CREATE TABLE dot_pair (a INTEGER, b INTEGER, label TEXT)"
let payload = Only (7 :: Int) :. (8 :: Int, Text.pack "hi")
_ <- execute conn "INSERT INTO dot_pair VALUES (?, ?, ?)" payload
rows <- query_ conn "SELECT a, b, label FROM dot_pair" :: IO [Only Int :. (Int, Text.Text)]
case rows of
[Only a :. (b, label)] -> do
assertEqual "(:.) first" 7 a
assertEqual "(:.) second" 8 b
assertEqual "(:.) label" (Text.pack "hi") label
other -> assertFailure ("unexpected (:.) rows: " <> show other)
, testCase "query_ fetches rows" $
withConnection ":memory:" \conn -> do
_ <- execute_ conn "CREATE TABLE query_rows (a INTEGER, b TEXT)"
_ <- executeMany conn "INSERT INTO query_rows VALUES (?, ?)" [(1 :: Int, "x" :: String), (2 :: Int, "y" :: String)]
rows <- query_ conn "SELECT a, b FROM query_rows ORDER BY a"
assertEqual "query rows" [(1 :: Int, "x" :: String), (2 :: Int, "y" :: String)] rows
, testCase "query decodes NULL as Maybe" $
withConnection ":memory:" \conn -> do
_ <- execute_ conn "CREATE TABLE maybe_vals (a TEXT)"
_ <- execute conn "INSERT INTO maybe_vals VALUES (?)" (Only (Just ("present" :: String)))
_ <- execute conn "INSERT INTO maybe_vals VALUES (?)" (Only (Nothing :: Maybe String))
rows <- query_ conn "SELECT a FROM maybe_vals ORDER BY a IS NULL, a"
assertEqual "maybe decoding" [Only (Just ("present" :: String)), Only Nothing] rows
, testCase "query fetches rows with parameters" $
withConnection ":memory:" \conn -> do
_ <- execute_ conn "CREATE TABLE query_params (a INTEGER, b TEXT)"
_ <- executeMany conn "INSERT INTO query_params VALUES (?, ?)" [(1 :: Int, "x" :: String), (2 :: Int, "y" :: String)]
rows <- query conn "SELECT a FROM query_params WHERE b = ?" (Only ("y" :: String))
assertEqual "query result" [Only (2 :: Int)] rows
, testCase "column mismatch surfaces as SQLError" $
withConnection ":memory:" \conn -> do
_ <- execute_ conn "CREATE TABLE mismatch (a INTEGER, b INTEGER)"
_ <- execute_ conn "INSERT INTO mismatch VALUES (1, 2)"
assertThrows
(query_ conn "SELECT a, b FROM mismatch" :: IO [Only Int])
(Text.isInfixOf "column index" . sqlErrorMessage)
, testCase "clears statement bindings without error" $
withConnection ":memory:" \conn -> do
stmt <- openStatement conn "SELECT ?"
clearStatementBindings stmt
closeStatement stmt
, testCase "resolves named parameter indices" $
withConnection ":memory:" \conn -> do
stmt <- openStatement conn "SELECT $named_param"
idx <- namedParameterIndex stmt "named_param"
assertEqual "named parameter index" (Just 1) idx
idxWithPrefix <- namedParameterIndex stmt "$named_param"
assertEqual "named parameter index with prefix" (Just 1) idxWithPrefix
colonIdx <- namedParameterIndex stmt ":named_param"
assertEqual "named parameter index with colon" (Just 1) colonIdx
none <- namedParameterIndex stmt "missing"
assertEqual "missing parameter index" Nothing none
closeStatement stmt
, testCase "reports column metadata for statements" $
withConnection ":memory:" \conn -> do
stmt <- openStatement conn "SELECT 1 AS a, 2 AS b"
count <- columnCount stmt
assertEqual "column count" 2 count
name0 <- columnName stmt 0
name1 <- columnName stmt 1
assertEqual "column names" [Text.pack "a", Text.pack "b"] [name0, name1]
assertThrows
(columnName stmt 2)
(Text.isInfixOf "out of bounds" . sqlErrorMessage)
closeStatement stmt
, testCase "bindNamed rejects statements without named placeholders" $
withConnection ":memory:" \conn -> do
stmt <- openStatement conn "SELECT ?"
assertThrowsFormatError
(bindNamed stmt [":value" := (1 :: Int)])
(Text.isInfixOf "does not define named parameters" . formatErrorMessage)
closeStatement stmt
, testCase "bindNamed rejects unknown parameters" $
withConnection ":memory:" \conn -> do
stmt <- openStatement conn "SELECT $known"
assertThrowsFormatError
(bindNamed stmt ["$missing" := (1 :: Int)])
(Text.isInfixOf "unknown named parameter" . formatErrorMessage)
closeStatement stmt
, testCase "numFieldsRemaining reports remaining columns" $
withConnection ":memory:" \conn -> do
_ <- execute_ conn "CREATE TABLE remaining (a INTEGER, b INTEGER, c INTEGER)"
_ <- execute conn "INSERT INTO remaining VALUES (?, ?, ?)" (1 :: Int, 2 :: Int, 3 :: Int)
rows <- query_ conn "SELECT a, b, c FROM remaining" :: IO [WithRemaining]
assertEqual "remaining count" [WithRemaining 1 2] rows
, testCase "RowParser alternatives fall back" $
withConnection ":memory:" \conn -> do
_ <- execute_ conn "CREATE TABLE yesno (answer TEXT)"
_ <- executeMany conn "INSERT INTO yesno VALUES (?)" [Only ("yes" :: String), Only ("no" :: String)]
rows <- query_ conn "SELECT answer FROM yesno ORDER BY answer" :: IO [YesNo]
assertEqual "yes/no parsing" [YesNo False, YesNo True] rows
]
data DuckDBExpectation
= ExpectEquals FieldValue
| ExpectSatisfies (FieldValue -> Assertion)
| ExpectException (SomeException -> Assertion)
data DuckDBExpression
= CastExpression Text.Text Text.Text
| DirectExpression Text.Text
data DuckDBCastCase = DuckDBCastCase
{ castLabel :: String
, castExpression :: DuckDBExpression
, castExpectation :: DuckDBExpectation
}
duckdbTypeCastTests :: TestTree
duckdbTypeCastTests =
testGroup
"duckdb type casts"
(map buildCase duckdbCastCases)
where
buildCase DuckDBCastCase{castLabel, castExpression, castExpectation} =
testCase castLabel $
withConnection ":memory:" \conn -> do
let sql =
Query
( case castExpression of
CastExpression expressionValue expressionType ->
Text.concat ["SELECT CAST(", expressionValue, " AS ", expressionType, ")"]
DirectExpression expressionSQL ->
Text.concat ["SELECT ", expressionSQL]
)
result <- try (query_ conn sql) :: IO (Either SomeException [Only FieldValue])
runExpectation castLabel castExpectation result
runExpectation :: String -> DuckDBExpectation -> Either SomeException [Only FieldValue] -> Assertion
runExpectation label expectation outcome =
case expectation of
ExpectEquals expected ->
case outcome of
Right [Only fieldValue] ->
assertEqual (label <> " result mismatch") expected fieldValue
Right other ->
assertFailure (label <> " expected single row, got " <> show other)
Left err ->
assertFailure (label <> " expected success, but query failed: " <> displayException err)
ExpectSatisfies predicate ->
case outcome of
Right [Only fieldValue] ->
predicate fieldValue
Right other ->
assertFailure (label <> " expected single row, got " <> show other)
Left err ->
assertFailure (label <> " expected success, but query failed: " <> displayException err)
ExpectException predicate ->
case outcome of
Left err ->
predicate err
Right rows ->
assertFailure (label <> " expected failure, but query succeeded with " <> show rows)
duckdbCastCases :: [DuckDBCastCase]
duckdbCastCases =
[ successCase "BOOLEAN" (quoted "true") "BOOLEAN" (ExpectEquals (FieldBool True))
, successCase "TINYINT" (quotedValue tinyIntValue) "TINYINT" (ExpectEquals (FieldInt8 tinyIntValue))
, successCase "SMALLINT" (quotedValue smallIntValue) "SMALLINT" (ExpectEquals (FieldInt16 smallIntValue))
, successCase "INTEGER" (quotedValue intValue) "INTEGER" (ExpectEquals (FieldInt32 intValue))
, successCase "BIGINT" (quotedValue bigIntValue) "BIGINT" (ExpectEquals (FieldInt64 bigIntValue))
, successCase "UTINYINT" (quotedValue uTinyValue) "UTINYINT" (ExpectEquals (FieldWord8 uTinyValue))
, successCase "USMALLINT" (quotedValue uSmallValue) "USMALLINT" (ExpectEquals (FieldWord16 uSmallValue))
, successCase "UINTEGER" (quotedValue uIntValue) "UINTEGER" (ExpectEquals (FieldWord32 uIntValue))
, successCase "UBIGINT" (quotedValue uBigValue) "UBIGINT" (ExpectEquals (FieldWord64 uBigValue))
, successCase "FLOAT" (quoted "3.25") "FLOAT" (ExpectEquals (FieldFloat floatValue))
, successCase "DOUBLE" (quoted "2.5") "DOUBLE" (ExpectEquals (FieldDouble doubleValue))
, successCase "TIMESTAMP" (quoted "2024-01-02 03:04:05.123456") "TIMESTAMP" (ExpectEquals (FieldTimestamp (Finite timestampLocalTimeMicros)))
, successCase "DATE" (quoted "2024-10-12") "DATE" (ExpectEquals (FieldDate (Finite dateValue)))
, successCase "TIME" (quoted "14:30:15.123456") "TIME" (ExpectEquals (FieldTime timeValue))
, successCase "INTERVAL" (quoted "1 day") "INTERVAL" (ExpectEquals (FieldInterval intervalValue))
, successCase "HUGEINT" (quotedValue hugeIntLiteral) "HUGEINT" (ExpectEquals (FieldHugeInt hugeIntLiteral))
, successCase "UHUGEINT" (quotedValue uHugeIntLiteral) "UHUGEINT" (ExpectEquals (FieldUHugeInt uHugeIntLiteral))
, successCase "VARCHAR" (quoted varcharText) "VARCHAR" (ExpectEquals (FieldText varcharText))
, successCase "BLOB" (quoted "duckdb") "BLOB" (ExpectEquals (FieldBlob blobValue))
, successCase "DECIMAL" (quoted "12345.6789") "DECIMAL(18,4)" (ExpectEquals (FieldDecimal decimalValue))
, successCase "TIMESTAMP_S" (quoted "2024-01-02 03:04:05") "TIMESTAMP_S" (ExpectEquals (FieldTimestamp (Finite timestampLocalTimeSeconds)))
, successCase
"TIMESTAMP_MS"
(quoted "2024-01-02 03:04:05.123")
"TIMESTAMP_MS"
(ExpectEquals (FieldTimestamp (Finite timestampLocalTimeMillis)))
, successCase
"TIMESTAMP_NS"
(quoted "2024-01-02 03:04:05.123456789")
"TIMESTAMP_NS"
(ExpectEquals (FieldTimestamp (Finite timestampLocalTimeNanos)))
, successCase "ENUM" (quoted "beta") "ENUM('alpha','beta')" (ExpectEquals (FieldEnum 1 "beta"))
, successCase "LIST" (quoted "[1,2,3]") "INTEGER[]" (ExpectEquals (FieldList listElements))
, successDirect "MAP" "MAP(['a','b'], [1,2])" (expectMapEntries mapPairs)
, successCase "TIMETZ" (quoted "03:04:05+02:30") "TIME WITH TIME ZONE" (ExpectEquals (FieldTimeTZ timeWithZoneValue))
, successCase
"TIMESTAMPTZ"
(quoted "2024-01-02 03:04:05+02:30")
"TIMESTAMP WITH TIME ZONE"
(ExpectEquals (FieldTimestampTZ (Finite timestampTzUtc)))
, successCase "BIGNUM" (quoted bigNumLiteralText) "BIGNUM" (ExpectEquals (FieldBigNum (BigNum bigNumLiteral)))
, successCase "UUID" (quoted $ UUID.toText uuid) "UUID" (ExpectEquals (FieldUUID uuid))
, successCase "BIT" (quoted $ Text.pack $ show bits) "BIT" (ExpectEquals (FieldBit bits))
, successCase "ARRAY" (quoted "[1,2,3]") "INTEGER[3]" (ExpectEquals (FieldArray arrayElements))
, successCase "TIME_NS" (quoted "03:04:05.123456789") "TIME_NS" (ExpectEquals (FieldTime (TimeOfDay 3 4 5.123456789)))
, successDirect "STRUCT" "{'a': 1, 'b': 2}" (expectStruct structFields)
, successDirect "UNION" "CAST(union_value(a := 42) AS UNION(a INTEGER, b VARCHAR))" (expectUnion 0 (Text.pack "a") (FieldInt32 42))
, -- These can never surface from a query, only internally.
failCaseOK "ANY" (quoted "1") "ANY" (expectSQLErrorContaining "ANY")
, failCaseOK "STRING_LITERAL" (quoted literalText) "STRING_LITERAL" (expectSQLErrorContaining "STRING_LITERAL")
, failCaseOK "INTEGER_LITERAL" (quoted "123") "INTEGER_LITERAL" (expectSQLErrorContaining "INTEGER_LITERAL")
, failCaseOK "INVALID" "0" "INVALID" (expectSQLErrorContaining "INVALID")
, failCaseOK "SQLNULL" "NULL" "SQLNULL" (expectSQLErrorContaining "SQLNULL")
]
where
bits = BitString 7 $ BS.pack [1, 255]
quoted t = Text.concat ["'", t, "'"]
quotedValue v = quoted (valueText v)
valueText :: (Show a) => a -> Text.Text
valueText = Text.pack . show
successCase label value ty expectation =
DuckDBCastCase
{ castLabel = label
, castExpression = CastExpression value ty
, castExpectation = expectation
}
successDirect label expr expectation =
DuckDBCastCase
{ castLabel = label
, castExpression = DirectExpression expr
, castExpectation = expectation
}
failCaseOK label value ty expectation =
DuckDBCastCase
{ castLabel = label
, castExpression = CastExpression value ty
, castExpectation = expectation
}
expectMapEntries expectedPairs =
ExpectSatisfies $
\case
FieldMap actualPairs ->
let normalize = sortOn (mapKey . fst)
mapKey (FieldText txt) = txt
mapKey other = Text.pack (show other)
in assertEqual "map entries" (normalize expectedPairs) (normalize actualPairs)
other ->
assertFailure ("expected FieldMap, but saw " <> show other)
structFields =
[ StructField{structFieldName = Text.pack "a", structFieldValue = FieldInt32 1}
, StructField{structFieldName = Text.pack "b", structFieldValue = FieldInt32 2}
]
expectStruct expectedFields =
ExpectSatisfies $
\case
FieldStruct StructValue{structValueFields, structValueTypes, structValueIndex} -> do
let actualFields = elems structValueFields
actualTypes = elems structValueTypes
expectedIndex = Map.fromList (zip (map structFieldName expectedFields) [0 ..])
typeNames = map structFieldName actualTypes
assertEqual "struct field names" (map structFieldName expectedFields) (map structFieldName actualFields)
assertEqual "struct field values" (map structFieldValue expectedFields) (map structFieldValue actualFields)
assertEqual "struct type field names" (map structFieldName expectedFields) typeNames
assertEqual "struct index map" expectedIndex structValueIndex
other ->
assertFailure ("expected FieldStruct, but saw " <> show other)
expectUnion expectedIndex expectedLabel expectedPayload =
ExpectSatisfies $
\case
FieldUnion UnionValue{unionValueIndex, unionValueLabel, unionValuePayload, unionValueMembers} -> do
assertEqual "union tag index" expectedIndex unionValueIndex
assertEqual "union tag label" expectedLabel unionValueLabel
assertEqual "union payload" expectedPayload unionValuePayload
let actualMembers = elems unionValueMembers
expectedMembers = [Text.pack "a", Text.pack "b"]
assertEqual "union member names" expectedMembers (map unionMemberName actualMembers)
other ->
assertFailure ("expected FieldUnion, but saw " <> show other)
expectSQLErrorContaining :: Text.Text -> DuckDBExpectation
expectSQLErrorContaining needle =
ExpectException $ \err ->
case fromException err :: Maybe SQLError of
Just sqlErr ->
let message = sqlErrorMessage sqlErr
in assertBool
("expected SQLError containing " <> Text.unpack needle <> ", but saw: " <> Text.unpack message)
(Text.isInfixOf needle message)
Nothing ->
assertFailure ("expected SQLError, but saw " <> displayException err)
bsFromString :: String -> BS.ByteString
bsFromString = BS.pack . map (fromIntegral . fromEnum)
secondsWithFraction :: Integer -> Integer -> Integer -> Rational
secondsWithFraction whole numerator denominator =
fromIntegral whole + numerator % denominator
tinyIntValue :: Int8
tinyIntValue = 42
smallIntValue :: Int16
smallIntValue = 32000
intValue :: Int32
intValue = 2147483647
bigIntValue :: Int64
bigIntValue = 9223372036854775807
uTinyValue :: Word8
uTinyValue = 200
uSmallValue :: Word16
uSmallValue = 60000
uIntValue :: Word32
uIntValue = maxBound
uBigValue :: Word64
uBigValue = maxBound
floatValue :: Float
floatValue = 3.25
doubleValue :: Double
doubleValue = 2.5
timestampDay = fromGregorian 2024 1 2
timestampLocalTimeMicros =
LocalTime timestampDay (TimeOfDay 3 4 (fromRational (secondsWithFraction 5 123456 1000000)))
timestampLocalTimeSeconds =
LocalTime timestampDay (TimeOfDay 3 4 5)
timestampLocalTimeMillis =
LocalTime timestampDay (TimeOfDay 3 4 (fromRational (secondsWithFraction 5 123 1000)))
timestampLocalTimeNanos =
LocalTime timestampDay (TimeOfDay 3 4 (fromRational (secondsWithFraction 5 123456789 1000000000)))
dateValue = fromGregorian 2024 10 12
timeValue =
TimeOfDay 14 30 (fromRational (secondsWithFraction 15 123456 1000000))
intervalValue =
IntervalValue{intervalMonths = 0, intervalDays = 1, intervalMicros = 0}
decimalValue =
DecimalValue{decimalWidth = 18, decimalScale = 4, decimalInteger = 123456789}
listElements =
[FieldInt32 1, FieldInt32 2, FieldInt32 3]
arrayElements =
listArray (0, 2) listElements
mapPairs =
[ (FieldText (Text.pack "a"), FieldInt32 1)
, (FieldText (Text.pack "b"), FieldInt32 2)
]
uuid :: UUID.UUID
uuid = fromJust $ UUID.fromText "123e4567-e89b-12d3-a456-426614174000"
timeWithZoneValue =
TimeWithZone{timeWithZoneTime = TimeOfDay 3 4 5, timeWithZoneOffsetSeconds = 9000}
timestampTzUtc =
UTCTime timestampDay (secondsToDiffTime 2045)
bigNumLiteral :: Integer
bigNumLiteral = 1234567890123456789012345678901234567890
bigNumLiteralText = valueText bigNumLiteral
varcharText :: Text.Text
varcharText = "Hello, DuckDB"
blobValue = bsFromString "duckdb"
hugeIntLiteral :: Integer
hugeIntLiteral = (2 ^ (120 :: Int)) + 123456789
uHugeIntLiteral :: Integer
uHugeIntLiteral = (2 ^ (128 :: Int)) - 1
literalText :: Text.Text
literalText = "literal"
typeTests :: TestTree
typeTests =
testGroup
"types"
[ duckdbTypeCastTests
, testCase "round-trips date/time/timestamp" $
withConnection ":memory:" \conn -> do
_ <- execute_ conn "CREATE TABLE temporals (d DATE, t TIME, ts TIMESTAMP)"
let dayVal = fromGregorian 2024 10 12
timeVal = TimeOfDay 14 30 15.123456
tsVal = LocalTime dayVal timeVal
_ <- execute conn "INSERT INTO temporals VALUES (?, ?, ?)" (dayVal, timeVal, tsVal)
[(dRes, tRes, tsRes)] <- query_ conn "SELECT d, t, ts FROM temporals"
assertEqual "date round-trip" dayVal dRes
assertEqual "time round-trip" timeVal tRes
assertEqual "timestamp round-trip" tsVal tsRes
-- A TIMESTAMP does not read as UTCTime. AT TIME ZONE 'UTC' gives the instant in UTC.
[Only utcRes] <- query_ conn "SELECT ts::TIMESTAMP AT TIME ZONE 'UTC' FROM temporals" :: IO [Only UTCTime]
assertEqual "timestamp at UTC" (localTimeToUTC utc tsVal) utcRes
(try (query_ conn "SELECT ts FROM temporals" :: IO [Only UTCTime])) >>= \case
Left SQLError{sqlErrorMessage} -> sqlErrorMessage @?= "duckdb-simple: column ts has type TIMESTAMP but expected UTCTime"
Right rows -> assertFailure ("expected Incompatible, got " <> show rows)
, testCase "round-trips blob payloads" $
withConnection ":memory:" \conn -> do
_ <- execute_ conn "CREATE TABLE blobs (payload BLOB)"
let payload = BS.pack [0, 1, 2, 3, 255]
_ <- execute conn "INSERT INTO blobs VALUES (?)" (Only payload)
[Only blobOut] <- query_ conn "SELECT payload FROM blobs"
assertEqual "blob round-trip" payload blobOut
, testCase "round-trips unsigned integers" $
withConnection ":memory:" \conn -> do
_ <- execute_ conn "CREATE TABLE unsigneds (u8 UTINYINT, u16 USMALLINT, u32 UINTEGER, u64 UBIGINT)"
let w8 = 200 :: Word8
w16 = 60000 :: Word16
w32 = 4000000000 :: Word32
w64 = maxBound :: Word64
_ <- execute conn "INSERT INTO unsigneds VALUES (?, ?, ?, ?)" (w8, w16, w32, w64)
[(r8, r16, r32, r64)] <- query_ conn "SELECT u8, u16, u32, u64 FROM unsigneds"
assertEqual "Word8 round-trip" w8 r8
assertEqual "Word16 round-trip" w16 r16
assertEqual "Word32 round-trip" w32 r32
assertEqual "Word64 round-trip" w64 r64
[Only (asWord :: Word)] <- query_ conn "SELECT u64 FROM unsigneds"
assertEqual "Word from UBIGINT" (fromIntegral w64) asWord
, testCase "round-trips fixed-length arrays" $
withConnection ":memory:" \conn -> do
_ <- execute_ conn "CREATE TABLE arrays (vals INTEGER[3])"
let inputs =
[ sized [1, 2, 3]
, sized [4, 5, 6]
]
_ <- executeMany conn "INSERT INTO arrays VALUES (?)" (Only <$> inputs)
rows <- query_ conn "SELECT vals FROM arrays ORDER BY rowid" :: IO [Only (SizedArray 3 Int)]
let expected =
fmap Only inputs
assertEqual "array round-trip" expected rows
, testCase "decodes huge integers as Integer" $
withConnection ":memory:" \conn -> do
let hugeValue = 170141183460469231731687303715884105727 :: Integer
[Only (hugeOut :: Integer)] <-
query_ conn "SELECT 170141183460469231731687303715884105727::HUGEINT"
assertEqual "hugeint" hugeValue hugeOut
, testCase "decodes huge integers as Natural" $
withConnection ":memory:" \conn -> do
let hugeValue = 170141183460469231731687303715884105727 :: Natural
[Only (naturalOut :: Natural)] <-
query_ conn "SELECT 170141183460469231731687303715884105727::HUGEINT"
assertEqual "hugeint natural" hugeValue naturalOut
, testCase "decodes unsigned huge integers as Natural" $
withConnection ":memory:" \conn -> do
let uhValue = 170141183460469231731687303715884105727 :: Natural
[Only (naturalOut :: Natural)] <-
query_ conn "SELECT 170141183460469231731687303715884105727::UHUGEINT"
assertEqual "uhugeint natural" uhValue naturalOut
, testCase "FromField Integer consumes FieldBigNum" $ do
let original = 1234567899876543210123456789 :: Integer
bigField =
Field
{ fieldName = "big"
, fieldIndex = 0
, fieldValue = FieldBigNum (BigNum original)
, fieldType = pure (LogicalTypeScalar LogicalTypeIdBigNum)
}
runConversion (fromField bigField :: Conversion Integer) >>= \case
Ok result -> assertEqual "FieldBigNum Integer" original result
Errors err -> assertFailure ("unexpected parse failure: " <> show err)
, testCase "FromField Natural rejects negative FieldBigNum" $ do
let bigValue = BigNum (-5)
bigField =
Field
{ fieldName = "big"
, fieldIndex = 0
, fieldValue = FieldBigNum bigValue
, fieldType = pure (LogicalTypeScalar LogicalTypeIdBigNum)
}
runConversion (fromField bigField :: Conversion Natural) >>= \case
Errors _ -> pure ()
Ok result -> assertFailure ("expected failure, got " <> show result)
, testCase "BigNum conversions round-trip Integer" $ do
let original = (2 ^ (200 :: Int)) + 8675309 :: Integer
converted = toBigNumBytes original
assertEqual "round-trip Integer" original (fromBigNumBytes converted)
, testProperty "fromBigNumBytes . toBigNumBytes is identity" \(n :: Integer) ->
fromBigNumBytes (toBigNumBytes n) === n
, testCase "selects BIGNUM values as BigNum" $
withConnection ":memory:" \conn -> do
rows <- concat <$> mapM (\bv -> query_ conn $ fromString $ "SELECT CAST('" <> show bv <> "' AS BIGNUM)") bigNumValues
assertEqual "BIGNUM results" (fmap (Only . BigNum) bigNumValues) rows
, testCase "round-trips BIGNUM values through the database" $
withConnection ":memory:" \conn -> do
_ <- execute_ conn "CREATE TABLE bignum_roundtrip (val BIGNUM)"
_ <- executeMany conn "INSERT INTO bignum_roundtrip VALUES (?)" (fmap (Only . BigNum) bigNumValues)
rows <- query_ conn "SELECT val FROM bignum_roundtrip ORDER BY rowid" :: IO [Only Integer]
assertEqual "BIGNUM round-trip" (fmap Only bigNumValues) rows
, testCase "duckdbColumnType reports BIGNUM for Integer" $
assertEqual
"duckdbColumnType Integer"
(Text.pack "BIGNUM")
(duckdbColumnType (Proxy @Integer))
, testCase "duckdbColumnType preserves Maybe parameter" $
assertEqual
"duckdbColumnType Maybe Word16"
(Text.pack "USMALLINT")
(duckdbColumnType (Proxy @(Maybe Word16)))
, testCase "decodes interval components" $
withConnection ":memory:" \conn -> do
[Only intervalVal] <-
query_ conn "SELECT INTERVAL '2 months 3 days 04:05:06.007008'"
let IntervalValue{intervalMonths, intervalDays, intervalMicros} = intervalVal
expectedMicros =
(((4 * 60 + 5) * 60) + 6) * 1000000 + 7008
assertEqual "interval months" 2 intervalMonths
assertEqual "interval days" 3 intervalDays
assertEqual "interval micros" expectedMicros intervalMicros
, testCase "decodes decimals via Double conversion" $
withConnection ":memory:" \conn -> do
[Only decimalAsDouble] <-
(query_ conn "SELECT CAST(12345.6789 AS DECIMAL(18,4))" :: IO [Only Double])
let diff = abs (decimalAsDouble - 12345.6789)
assertBool
("decimal as double: " <> show diff)
(diff < 1e-4)
, testCase "decodes time with time zone" $
withConnection ":memory:" \conn -> do
[Only tzVal] <- query_ conn "SELECT TIMETZ '14:30:15+02:30'"
let expectedTime = TimeOfDay 14 30 15
assertEqual "timetz time" expectedTime (timeWithZoneTime tzVal)
assertEqual "timetz offset" 9000 (timeWithZoneOffsetSeconds tzVal)
, testCase "decodes list columns into Haskell lists" $
withConnection ":memory:" \conn -> do
[Only (listOut :: [Int])] <-
query_ conn "SELECT LIST_VALUE(1, 2, 3)::INTEGER[]"
assertEqual "list decode" [1, 2, 3] listOut
, testCase "decodes map columns into strict Map" $
withConnection ":memory:" \conn -> do
[Only (mapOut :: Map.Map Text.Text Int)] <-
query_ conn "SELECT MAP(['a', 'b']::TEXT[], [1, 2]::INTEGER[])"
let expected = Map.fromList [(Text.pack "a", 1), (Text.pack "b", 2)]
assertEqual "map decode" expected mapOut
, testCase "decodes timestamp with time zone as UTC" $
withConnection ":memory:" \conn -> do
[Only utcVal] <-
(query_ conn "SELECT TIMESTAMPTZ '2024-10-12 14:30:15+02:30'" :: IO [Only UTCTime])
let expected =
UTCTime
(fromGregorian 2024 10 12)
(timeOfDayToTime (TimeOfDay 12 0 15))
assertEqual "timestamptz utc" expected utcVal
, testCase "custom FieldParser enforces invariants" $
withConnection ":memory:" \conn -> do
_ <- execute_ conn "CREATE TABLE nonempty (name TEXT)"
_ <- execute conn "INSERT INTO nonempty VALUES (?)" (Only (Text.pack "okay"))
rows <- query_ conn "SELECT name FROM nonempty" :: IO [Only NonEmptyText]
assertEqual "non-empty success" [Only (NonEmptyText (Text.pack "okay"))] rows
_ <- execute conn "INSERT INTO nonempty VALUES (?)" (Only (Text.pack ""))
assertThrows
(query_ conn "SELECT name FROM nonempty" :: IO [Only NonEmptyText])
(Text.isInfixOf "NonEmptyText requires a non-empty string" . sqlErrorMessage)
, testCase "round-trips uuid payloads" $
withConnection ":memory:" \conn -> do
_ <- execute_ conn "CREATE TABLE uuids (uuid UUID)"
let uuidTexts =
[ "123e4567-e89b-12d3-a456-426614174000"
, "923e4567-e89b-12d3-a456-426614174000"
, "ffffffff-ffff-ffff-ffff-ffffffffffff"
]
maybeUUIDs = traverse UUID.fromText uuidTexts
uuids <-
case maybeUUIDs of
Nothing -> assertFailure "invalid UUID literal in test data" >> pure []
Just parsed -> pure parsed
_ <- executeMany conn "INSERT INTO uuids VALUES (?)" (fmap Only uuids)
rows <- query_ conn "SELECT uuid FROM uuids ORDER BY rowid" :: IO [Only UUID.UUID]
assertEqual "uuid round-trip" (fmap Only uuids) rows
, testCase "round-trips bitstring payloads" $
withConnection ":memory:" \conn -> do
_ <- execute_ conn "CREATE TABLE bits (bit BIT)"
let bitValues =
[ bsFromBool [True]
, bsFromBool [False, False, False, True, True, True, False, True, False]
, bsFromBool [False, False, False, False, True, True, True, False, True, False]
, bsFromBool [True, False, False, False, True, True, True, False, True, False]
]
_ <- executeMany conn "INSERT INTO bits VALUES (?)" (fmap Only bitValues)
rows <- query_ conn "SELECT bit FROM bits ORDER BY rowid" :: IO [Only BitString]
assertEqual "bit round-trip" (fmap Only bitValues) rows
, testCase "round-trips struct and union parameters" $
withConnection ":memory:" \conn -> do
_ <- execute_ conn "CREATE TABLE struct_union (s STRUCT(a INT, b INT), u UNION(a INT, b VARCHAR))"
[(structVal, unionVal)] <-
query_ conn "SELECT {'a': 1, 'b': 2}, CAST(union_value(a := 42) AS UNION(a INTEGER, b VARCHAR))"
:: IO [(StructValue FieldValue, UnionValue FieldValue)]
_ <- execute conn "INSERT INTO struct_union VALUES (?, ?)" (structVal, unionVal)
rows <- query_ conn "SELECT s, u FROM struct_union" :: IO [(StructValue FieldValue, UnionValue FieldValue)]
assertEqual "struct/union round-trip" [(structVal, unionVal)] rows
, testCase "generic struct into database" $
withConnection ":memory:" \conn -> do
_ <- execute_ conn "CREATE TABLE generic_struct (payload STRUCT(grId INT, grName TEXT))"
let records = [GenericRecord 1 (Text.pack "alpha"), GenericRecord 2 (Text.pack "beta")]
structParams = traverse genericToStructValue records
case structParams of
Nothing -> assertFailure "expected struct encoding"
Just structs -> do
_ <- executeMany conn "INSERT INTO generic_struct VALUES (?)" (map Only structs)
rows <- query_ conn "SELECT payload FROM generic_struct ORDER BY payload.grId" :: IO [Only (StructValue FieldValue)]
let unwrap (Only s) = s
roundTripped <- runConversion (traverse (genericFromFieldValue . FieldStruct . unwrap) rows)
roundTripped @?= Ok records
, testCase "generic struct encode/decode" $ do
let rec = GenericRecord 42 (Text.pack "duck")
case genericToStructValue rec of
Just structVal -> do
map structFieldName (elems (structValueFields structVal)) @?= [Text.pack "grId", Text.pack "grName"]
map structFieldValue (elems (structValueFields structVal)) @?= [FieldInt64 42, FieldText (Text.pack "duck")]
runConversion (genericFromFieldValue (FieldStruct structVal)) >>= (@?= Ok rec)
Nothing -> assertFailure "expected struct encoding"
, testCase "generic union encode/decode" $ do
let sumVal = SumBool True
case genericToFieldValue sumVal of
FieldUnion uv -> do
map unionMemberName (elems (unionValueMembers uv))
@?= [ Text.pack "SumInt"
, Text.pack "SumBool"
, Text.pack "SumPair"
]
unionValueLabel uv @?= Text.pack "SumBool"
runConversion (genericFromFieldValue (FieldUnion uv)) >>= (@?= Ok sumVal)
other -> assertFailure ("expected FieldUnion, got " <> show other)
, testCase "generic union complex constructor" $ do
let sumVal = SumPair 7 (Text.pack "duck")
case genericToFieldValue sumVal of
FieldUnion uv -> do
unionValueLabel uv @?= Text.pack "SumPair"
case unionValuePayload uv of
FieldStruct StructValue{structValueFields} -> do
map structFieldName (elems structValueFields) @?= [Text.pack "field1", Text.pack "field2"]
map structFieldValue (elems structValueFields)
@?= [FieldInt64 7, FieldText (Text.pack "duck")]
other -> assertFailure ("expected struct payload, got " <> show other)
runConversion (genericFromFieldValue (FieldUnion uv)) >>= (@?= Ok sumVal)
other -> assertFailure ("expected FieldUnion, got " <> show other)
, testCase "deriving via generic struct round-trip" $
withConnection ":memory:" \conn -> do
_ <- execute_ conn "CREATE TABLE via_users (payload STRUCT(vuId BIGINT, vuName TEXT))"
let users = [ViaUser 10 (Text.pack "alice"), ViaUser 11 (Text.pack "bob")]
_ <- executeMany conn "INSERT INTO via_users VALUES (?)" (map Only users)
rows <- query_ conn "SELECT payload FROM via_users ORDER BY payload.vuId" :: IO [Only ViaUser]
rows @?= map Only users
, testCase "deriving via generic union round-trip" $
withConnection ":memory:" \conn -> do
_ <- execute_ conn "CREATE TABLE via_shapes (shape UNION(ViaCircle STRUCT(field1 DOUBLE), ViaSquare STRUCT(field1 DOUBLE)))"
let shapes = [ViaCircle 1.5, ViaSquare 3.0]
_ <- executeMany conn "INSERT INTO via_shapes VALUES (?)" (map Only shapes)
rows <- query_ conn "SELECT shape FROM via_shapes ORDER BY rowid" :: IO [Only ViaShape]
rows @?= map Only shapes
, testCase "generic enum payload is a STRUCT without fields" $ do
case genericToFieldValue EnumRed of
FieldUnion uv -> do
unionValuePayload uv @?= FieldStruct (StructValue (listArray (0, -1) []) (listArray (0, -1) []) Map.empty)
runConversion (genericFromFieldValue (FieldUnion uv)) >>= (@?= Ok EnumRed)
other -> assertFailure ("expected FieldUnion, got " <> show other)
, -- Edge case tests
testCase "struct with multiple basic fields" $ do
-- Test a struct with multiple fields
let twoField = TwoFieldStruct 42 (Text.pack "text")
case genericToFieldValue twoField of
FieldStruct sv -> do
runConversion (genericFromFieldValue (FieldStruct sv)) >>= (@?= Ok twoField)
other -> assertFailure ("expected FieldStruct for TwoFieldStruct, got " <> show other)
, testCase "large sum type with many constructors" $ do
let constructors = [LargeC1 1, LargeC2 2, LargeC3 3, LargeC4 4, LargeC5 5]
forM_ constructors $ \val -> do
case genericToFieldValue val of
FieldUnion uv -> do
runConversion (genericFromFieldValue (FieldUnion uv)) >>= (@?= Ok val)
other -> assertFailure ("expected FieldUnion, got " <> show other)
, testCase "empty struct encodes as a STRUCT without fields" $ do
case genericToFieldValue EmptyStruct of
FieldStruct structVal -> do
elems (structValueFields structVal) @?= []
runConversion (genericFromFieldValue (FieldStruct structVal)) >>= (@?= Ok EmptyStruct)
other -> assertFailure ("expected a STRUCT without fields for empty struct, got " <> show other)
, testCase "empty struct binds as a STRUCT without fields" $
withConnection ":memory:" \conn -> do
query conn "SELECT typeof(?)" (Only EmptyStruct) >>= (@?= [Only (Text.pack "STRUCT")])
rows <- query conn "SELECT ?" (Only EmptyStruct) :: IO [Only FieldValue]
case rows of
[Only (FieldStruct structVal)] -> elems (structValueFields structVal) @?= []
other -> assertFailure ("expected a STRUCT without fields, got " <> show other)
, testCase "Just and Nothing of a type without fields stay different in a record" $
withConnection ":memory:" \conn -> do
let value = OptionalEmpty (Just EmptyStruct) Nothing
_ <- execute_ conn "CREATE TABLE optional_empty (r STRUCT(oeFirst STRUCT, oeSecond STRUCT))"
_ <- execute conn "INSERT INTO optional_empty VALUES (?)" (Only value)
(query_ conn "SELECT r::VARCHAR, r.oeFirst IS NULL, r.oeSecond IS NULL, r FROM optional_empty" :: IO [(Text.Text, Bool, Bool, OptionalEmpty)])
>>= (@?= [(Text.pack "{'oeFirst': {}, 'oeSecond': NULL}", False, True, value)])
, testCase "a constructor without fields binds the STRUCT without fields as its payload" $
withConnection ":memory:" \conn -> do
forM_ [(Counted 5, Text.pack "Counted"), (Uncounted, Text.pack "Uncounted")] \(value, tag) ->
query conn "SELECT union_tag(?)::VARCHAR" (Only value) >>= (@?= [Only tag])
_ <- execute_ conn "CREATE TABLE tallies (s UNION(Counted STRUCT(field1 BIGINT), Uncounted STRUCT))"
_ <- executeMany conn "INSERT INTO tallies VALUES (?)" [Only (Counted 5), Only Uncounted]
(query_ conn "SELECT union_tag(s)::VARCHAR, s::VARCHAR, s FROM tallies ORDER BY rowid" :: IO [(Text.Text, Text.Text, Tally)])
>>= (@?= [(Text.pack "Counted", Text.pack "{'field1': 5}", Counted 5), (Text.pack "Uncounted", Text.pack "{}", Uncounted)])
, testCase "the NULL of version 0.3 for a type without fields decodes but does not equal a new parameter" $
withConnection ":memory:" \conn -> do
-- Version 0.3 wrote NULL in place of the STRUCT without fields.
runConversion (genericFromFieldValue FieldNull) >>= (@?= Ok EmptyStruct)
case genericToFieldValue Uncounted of
FieldUnion uv -> do
let oldValue = uv{unionValuePayload = FieldNull}
runConversion (genericFromFieldValue (FieldUnion oldValue)) >>= (@?= Ok Uncounted)
_ <- execute_ conn "CREATE TABLE old_tallies (v UNION(Counted STRUCT(field1 BIGINT), Uncounted STRUCT))"
_ <- execute conn "INSERT INTO old_tallies VALUES (?)" (Only oldValue)
(query_ conn "SELECT v FROM old_tallies" :: IO [Only Tally]) >>= (@?= [Only Uncounted])
(query conn "SELECT count(*) FROM old_tallies WHERE v = ?" (Only Uncounted) :: IO [Only Int64]) >>= (@?= [Only 0])
_ <- execute conn "INSERT INTO old_tallies VALUES (?)" (Only Uncounted)
(query conn "SELECT count(*) FROM old_tallies WHERE v = ?" (Only Uncounted) :: IO [Only Int64]) >>= (@?= [Only 1])
other -> assertFailure ("expected FieldUnion, got " <> show other)
case genericToFieldValue (WithEmpty 7 EmptyStruct) of
FieldStruct sv -> do
let oldField item = if structFieldName item == Text.pack "weEmpty" then item{structFieldValue = FieldNull} else item
oldValue = sv{structValueFields = fmap oldField (structValueFields sv)}
_ <- execute_ conn "CREATE TABLE old_with_empty (r STRUCT(weId BIGINT, weEmpty STRUCT))"
_ <- execute conn "INSERT INTO old_with_empty VALUES (?)" (Only oldValue)
(query_ conn "SELECT r FROM old_with_empty" :: IO [Only WithEmpty]) >>= (@?= [Only (WithEmpty 7 EmptyStruct)])
(query conn "SELECT count(*) FROM old_with_empty WHERE r = ?" (Only (WithEmpty 7 EmptyStruct)) :: IO [Only Int64]) >>= (@?= [Only 0])
_ <- execute conn "INSERT INTO old_with_empty VALUES (?)" (Only (WithEmpty 7 EmptyStruct))
(query conn "SELECT count(*) FROM old_with_empty WHERE r = ?" (Only (WithEmpty 7 EmptyStruct)) :: IO [Only Int64]) >>= (@?= [Only 1])
other -> assertFailure ("expected FieldStruct, got " <> show other)
, testCase "a record field of a type without fields round trips" $
withConnection ":memory:" \conn -> do
let value = WithEmpty 7 EmptyStruct
_ <- execute_ conn "CREATE TABLE with_empty (r STRUCT(weId BIGINT, weEmpty STRUCT))"
_ <- execute conn "INSERT INTO with_empty VALUES (?)" (Only value)
(query_ conn "SELECT r::VARCHAR, r FROM with_empty" :: IO [(Text.Text, WithEmpty)])
>>= (@?= [(Text.pack "{'weId': 7, 'weEmpty': {}}", value)])
, testCase "struct with Maybe fields" $ do
let withJust = OptionalFields (Just 42) (Just (Text.pack "text"))
withNothing = OptionalFields Nothing Nothing
case (genericToFieldValue withJust, genericToFieldValue withNothing) of
(FieldStruct sv1, FieldStruct sv2) -> do
runConversion (genericFromFieldValue (FieldStruct sv1)) >>= (@?= Ok withJust)
runConversion (genericFromFieldValue (FieldStruct sv2)) >>= (@?= Ok withNothing)
(other1, other2) -> assertFailure ("expected FieldStruct, got " <> show (other1, other2))
, -- Error case tests
testCase "decode wrong field type fails" $ do
let wrongType = FieldInt32 42 -- Trying to decode as GenericRecord
runConversion (genericFromFieldValue wrongType :: Conversion GenericRecord) >>= \case
Errors err -> assertBool "error mentions STRUCT" (Text.pack "STRUCT" `Text.isInfixOf` Text.pack (show err))
Ok _ -> assertFailure "expected decode error for wrong type"
, testCase "decode union as struct fails" $ do
case genericToFieldValue (SumInt 42) of
FieldUnion uv -> do
runConversion (genericFromFieldValue (FieldUnion uv) :: Conversion GenericRecord) >>= \case
Errors err -> assertBool "error mentions product type" (Text.pack "product type" `Text.isInfixOf` Text.pack (show err))
Ok _ -> assertFailure "expected decode error"
other -> assertFailure ("expected FieldUnion, got " <> show other)
, testCase "decode struct as union fails" $ do
case genericToFieldValue (GenericRecord 1 (Text.pack "test")) of
FieldStruct sv -> do
runConversion (genericFromFieldValue (FieldStruct sv) :: Conversion GenericSum) >>= \case
Errors err -> assertBool "error mentions sum type" (Text.pack "sum type" `Text.isInfixOf` Text.pack (show err))
Ok _ -> assertFailure "expected decode error"
other -> assertFailure ("expected FieldStruct, got " <> show other)
, testCase "decode struct with wrong field count fails" $
withConnection ":memory:" \conn -> do
-- Get a real struct with 3 fields from the database
_ <- execute_ conn "CREATE TABLE three_field (s STRUCT(a INT, b TEXT, c INT))"
[(Only threeField)] <- query_ conn "SELECT {'a': 1, 'b': 'test', 'c': 99}" :: IO [Only (StructValue FieldValue)]
-- Try to decode it as GenericRecord which only has 2 fields
runConversion (genericFromFieldValue (FieldStruct threeField) :: Conversion GenericRecord) >>= \case
Errors err -> assertBool "error mentions extra fields" (Text.pack "extra" `Text.isInfixOf` Text.pack (show err))
Ok _ -> assertFailure "expected decode error for extra fields"
, testCase "decode union with wrong tag fails" $
withConnection ":memory:" \conn -> do
-- Get a real union from the database
[(Only unionVal)] <-
query_ conn "SELECT CAST(union_value(x := 42) AS UNION(x INT, y VARCHAR))" :: IO [Only (UnionValue FieldValue)]
-- Manually create a modified version with an out-of-range tag
let wrongTag = unionVal{unionValueIndex = 99, unionValueLabel = Text.pack "Invalid"}
runConversion (genericFromFieldValue (FieldUnion wrongTag) :: Conversion GenericSum) >>= \case
Errors err ->
assertBool
"error mentions tag or index"
(Text.pack "tag" `Text.isInfixOf` Text.pack (show err) || Text.pack "99" `Text.isInfixOf` Text.pack (show err))
Ok _ -> assertFailure "expected decode error for wrong tag"
]
where
-- 7 ^ 2900 has 1,018 magnitude bytes. A value of at most 9 magnitude bytes
-- has inline storage.
bigNumValues :: [Integer]
bigNumValues =
[ (2 ^ (200 :: Int)) + 8675309
, 0
, negate ((2 ^ (180 :: Int)) + 12345)
, 7 ^ (2900 :: Int)
, negate (7 ^ (2900 :: Int))
, 1
, -1
, 255
, 256
, -256
]
streamingTests :: TestTree
streamingTests =
testGroup
"streaming"
[ testCase "fold_ accumulates large result sets" $
withConnection ":memory:" \conn -> do
_ <- execute_ conn "CREATE TABLE stream_fold (n INTEGER)"
let rows = fmap Only [1 .. 1000 :: Int]
_ <- executeMany conn "INSERT INTO stream_fold VALUES (?)" rows
total <- fold_ conn "SELECT n FROM stream_fold ORDER BY n" 0 \acc (Only n) -> pure (acc + n)
assertEqual "folded sum" (sum ([1 .. 1000] :: [Int])) total
, testCase "fold and foldNamed respect parameters" $
withConnection ":memory:" \conn -> do
_ <- execute_ conn "CREATE TABLE stream_filter (n INTEGER)"
let values = fmap Only [1 .. 50 :: Int]
_ <- executeMany conn "INSERT INTO stream_filter VALUES (?)" values
gtTotal <-
fold conn "SELECT n FROM stream_filter WHERE n > ?" (Only (40 :: Int)) 0 $
\acc (Only n) -> pure (acc + n)
let expected = sum ([41 .. 50] :: [Int])
assertEqual "filtered fold sum" expected gtTotal
leCount <-
foldNamed conn "SELECT n FROM stream_filter WHERE n <= $limit" ["$limit" := (10 :: Int)] 0 $
\acc (Only (_ :: Int)) -> pure (acc + 1)
assertEqual "foldNamed count" (10 :: Int) leCount
, testCase "nextRow streams rows sequentially" $
withConnection ":memory:" \conn -> do
_ <- execute_ conn "CREATE TABLE stream_cursor (n INTEGER)"
_ <- executeMany conn "INSERT INTO stream_cursor VALUES (?)" (fmap Only [1 .. 3 :: Int])
withStatement conn "SELECT n FROM stream_cursor ORDER BY n" \stmt -> do
first <- nextRow stmt
second <- nextRow stmt
third <- nextRow stmt
done <- nextRow stmt
assertEqual
"cursor iteration"
[Just (Only (1 :: Int)), Just (Only 2), Just (Only 3), Nothing]
[first, second, third, done]
]
functionsTests :: TestTree
functionsTests =
testGroup
"user-defined functions"
[ testCase "registers pure scalar function" $
withConnection ":memory:" \conn -> do
createFunction conn "hs_times_two" (\(x :: Int64) -> x * 2)
result <- query_ conn "SELECT hs_times_two(21)" :: IO [Only Int64]
assertEqual "times_two result" [Only 42] result
, testCase "handles nullable arguments and results" $
withConnection ":memory:" \conn -> do
createFunction conn "hs_optional" (\(mx :: Maybe Int64) -> fmap (+ 1) mx)
rows <-
query_ conn "SELECT hs_optional(x) FROM (VALUES (NULL), (41)) AS t(x)"
:: IO [Only (Maybe Int64)]
assertEqual "optional results" [Only Nothing, Only (Just 42)] rows
, testCase "keeps the SQL types of the arguments of version 0.3" $
withConnection ":memory:" \conn -> do
createFunction conn "hs_id_bool" (id :: Bool -> Bool)
createFunction conn "hs_id_double" (id :: Double -> Double)
createFunction conn "hs_id_float" (id :: Float -> Float)
createFunction conn "hs_id_int" (id :: Int -> Int)
createFunction conn "hs_id_int16" (id :: Int16 -> Int16)
createFunction conn "hs_id_int32" (id :: Int32 -> Int32)
createFunction conn "hs_id_int64" (id :: Int64 -> Int64)
createFunction conn "hs_id_maybe" (id :: Maybe Word16 -> Maybe Word16)
createFunction conn "hs_id_string" (id :: String -> String)
createFunction conn "hs_id_text" (id :: Text.Text -> Text.Text)
createFunction conn "hs_id_word" (id :: Word -> Word)
createFunction conn "hs_id_word16" (id :: Word16 -> Word16)
createFunction conn "hs_id_word32" (id :: Word32 -> Word32)
createFunction conn "hs_id_word64" (id :: Word64 -> Word64)
query_
conn
"SELECT parameter_types::VARCHAR FROM duckdb_functions() WHERE function_name LIKE 'hs\\_id\\_%' ESCAPE '\\' ORDER BY function_name"
>>= ( @?=
map
Only
[ "[BOOLEAN]" :: Text.Text
, "[DOUBLE]"
, "[FLOAT]"
, "[BIGINT]"
, "[SMALLINT]"
, "[INTEGER]"
, "[BIGINT]"
, "[USMALLINT]"
, "[VARCHAR]"
, "[VARCHAR]"
, "[UBIGINT]"
, "[USMALLINT]"
, "[UINTEGER]"
, "[UBIGINT]"
]
)
, testCase "takes an argument of each type with FromField and DuckDBColumnType" $
withConnection ":memory:" \conn -> do
let user = ViaUser 7 "anna"
createFunction conn "hs_arg_int8" (\(x :: Int8) -> fromIntegral x * 2 :: Int64)
createFunction conn "hs_arg_integer" (\(x :: Integer) -> Text.pack (show (x * 2)))
createFunction conn "hs_arg_bytes" (\(x :: BS.ByteString) -> BS.length x)
createFunction conn "hs_arg_uuid" UUID.toText
createFunction conn "hs_arg_day" (\(x :: Day) -> Text.pack (show x))
createFunction conn "hs_arg_local" (\(x :: LocalTime) -> Text.pack (show x))
createFunction conn "hs_arg_utc" (\(x :: UTCTime) -> Text.pack (show x))
createFunction conn "hs_arg_list" (\(xs :: [Int64]) -> sum xs)
createFunction conn "hs_arg_map" (\(pairs :: Map.Map Text.Text Int64) -> sum (Map.elems pairs))
createFunction conn "hs_arg_interval" (\(IntervalValue months days micros) -> Text.pack (show (months, days, micros)))
createFunction conn "hs_arg_record" (\(ViaUser identifier name) -> name <> Text.pack (show identifier))
createFunction conn "hs_arg_records" (\(users :: [ViaUser]) -> length users)
createFunction conn "hs_arg_maybe_record" (\(found :: Maybe ViaUser) -> maybe "none" vuName found)
createFunction conn "hs_arg_price" (\(Price DecimalValue{decimalInteger}) -> fromIntegral decimalInteger :: Int64)
query_
conn
"SELECT hs_arg_int8(-3), hs_arg_integer(1267650600228229401496703205376::HUGEINT), hs_arg_bytes('\\x01\\x02'::BLOB), hs_arg_uuid('00000000-0000-0000-0000-000000000001')"
>>= (@?= [(-6 :: Int64, Text.pack (show (2 * 2 ^ (100 :: Int) :: Integer)), 2 :: Int, "00000000-0000-0000-0000-000000000001" :: Text.Text)])
query_
conn
"SELECT hs_arg_day(DATE '2026-11-15'), hs_arg_local(TIMESTAMP '2026-11-15 12:34:56'), hs_arg_utc(TIMESTAMPTZ '2026-11-15 12:34:56+00')"
>>= (@?= [("2026-11-15" :: Text.Text, "2026-11-15 12:34:56" :: Text.Text, "2026-11-15 12:34:56 UTC" :: Text.Text)])
query_ conn "SELECT hs_arg_list([1, 2, 3]), hs_arg_map(MAP {'a': 1, 'b': 2}), hs_arg_interval(INTERVAL '1 month 2 days 3 microseconds')"
>>= (@?= [(6 :: Int64, 3 :: Int64, "(1,2,3)" :: Text.Text)])
query conn "SELECT hs_arg_record(?), hs_arg_records([?, ?]), hs_arg_maybe_record(?), hs_arg_maybe_record(NULL)" (user, user, user, user)
>>= (@?= [("anna7" :: Text.Text, 2 :: Int, "anna" :: Text.Text, "none" :: Text.Text)])
query_ conn "SELECT hs_arg_price(1.25)" >>= (@?= [Only (1250 :: Int64)])
query_
conn
"SELECT function_name, parameter_types::VARCHAR FROM duckdb_functions() WHERE function_name IN ('hs_arg_int8', 'hs_arg_integer', 'hs_arg_record', 'hs_arg_records', 'hs_arg_price') ORDER BY function_name"
>>= ( @?=
[ ("hs_arg_int8" :: Text.Text, "[TINYINT]" :: Text.Text)
, ("hs_arg_integer", "[HUGEINT]")
, ("hs_arg_price", "['DECIMAL(18,3)']")
, ("hs_arg_record", "['STRUCT(vuId BIGINT, vuName VARCHAR)']")
, ("hs_arg_records", "['STRUCT(vuId BIGINT, vuName VARCHAR)[]']")
]
)
, testCase "a UTCTime argument with a TIMESTAMP value gets the cast of DuckDB to TIMESTAMPTZ" $
withConnection ":memory:" \conn -> do
-- DuckDB casts the argument to the declared TIMESTAMPTZ with the
-- TimeZone setting of the session. The function reads that instant.
createFunction conn "hs_arg_utc_text" (\(x :: UTCTime) -> Text.pack (show x))
_ <- execute_ conn "SET TimeZone = 'Pacific/Auckland'"
query_ conn "SELECT hs_arg_utc_text(TIMESTAMP '2024-01-02 03:04:05'), (TIMESTAMP '2024-01-02 03:04:05')::TIMESTAMPTZ"
>>= (@?= [("2024-01-01 14:04:05 UTC" :: Text.Text, UTCTime (fromGregorian 2024 1 1) (secondsToDiffTime 50645))])
, testCase "declares the SQL type of each result type" $
withConnection ":memory:" \conn -> do
createFunction conn "hs_res_bool" True
createFunction conn "hs_res_int" (1 :: Int)
createFunction conn "hs_res_int8" (1 :: Int8)
createFunction conn "hs_res_int16" (1 :: Int16)
createFunction conn "hs_res_int32" (1 :: Int32)
createFunction conn "hs_res_int64" (1 :: Int64)
createFunction conn "hs_res_word" (1 :: Word)
createFunction conn "hs_res_word8" (1 :: Word8)
createFunction conn "hs_res_word16" (1 :: Word16)
createFunction conn "hs_res_word32" (1 :: Word32)
createFunction conn "hs_res_word64" (1 :: Word64)
createFunction conn "hs_res_float" (1.5 :: Float)
createFunction conn "hs_res_double" (1.5 :: Double)
createFunction conn "hs_res_text" ("x" :: Text.Text)
createFunction conn "hs_res_string" ("x" :: String)
createFunction conn "hs_res_io" (pure (1 :: Int16) :: IO Int16)
createFunction conn "hs_res_integer" (2 ^ (100 :: Int) :: Integer)
createFunction conn "hs_res_bytes" (BS.pack [1, 2])
createFunction conn "hs_res_uuid" UUID.nil
createFunction conn "hs_res_day" (fromGregorian 2026 11 15)
createFunction conn "hs_res_interval" (IntervalValue 1 2 3)
createFunction conn "hs_res_amount" (Amount 1.25)
forM_
[ ("hs_res_bool", "BOOLEAN")
, ("hs_res_int", "BIGINT")
, ("hs_res_int8", "TINYINT")
, ("hs_res_int16", "SMALLINT")
, ("hs_res_int32", "INTEGER")
, ("hs_res_int64", "BIGINT")
, ("hs_res_word", "UBIGINT")
, ("hs_res_word8", "UTINYINT")
, ("hs_res_word16", "USMALLINT")
, ("hs_res_word32", "UINTEGER")
, ("hs_res_word64", "UBIGINT")
, ("hs_res_float", "FLOAT")
, ("hs_res_double", "DOUBLE")
, ("hs_res_text", "VARCHAR")
, ("hs_res_string", "VARCHAR")
, ("hs_res_io", "SMALLINT")
, ("hs_res_integer", "HUGEINT")
, ("hs_res_bytes", "BLOB")
, ("hs_res_uuid", "UUID")
, ("hs_res_day", "DATE")
, ("hs_res_interval", "INTERVAL")
, ("hs_res_amount", "DECIMAL(18,3)")
]
\(name, expected) -> query_ conn (Query ("SELECT typeof(" <> name <> "())")) >>= (@?= [Only (expected :: Text.Text)])
-- An Integer argument or result is HUGEINT, and a Natural one is UHUGEINT.
-- DuckDB casts no BIGNUM or UHUGEINT argument to HUGEINT, and
-- toDuckValue raises an error for a result outside the range.
createFunction conn "hs_arg_wide" (\(x :: Integer) -> x)
assertThrows
(query_ conn "SELECT hs_arg_wide('5'::BIGNUM)" :: IO [Only Integer])
(Text.isInfixOf "No function matches" . sqlErrorMessage)
assertThrows
(query_ conn "SELECT hs_arg_wide(5::UHUGEINT)" :: IO [Only Integer])
(Text.isInfixOf "No function matches" . sqlErrorMessage)
createFunction conn "hs_res_natural" (2 ^ (127 :: Int) :: Natural)
createFunction conn "hs_res_wide" (2 ^ (127 :: Int) :: Integer)
query_ conn "SELECT typeof(hs_res_natural()), hs_res_natural()" >>= (@?= [("UHUGEINT" :: Text.Text, 2 ^ (127 :: Int) :: Natural)])
assertThrows
(query_ conn "SELECT hs_res_wide()" :: IO [Only Integer])
(\err -> sqlErrorType err == Just ErrorTypeConversion && Text.isInfixOf "is outside the range of HUGEINT" (sqlErrorMessage err))
-- DuckDB casts a native result value to the declared type as the SQL cast does.
createFunction conn "hs_res_rounded" (Amount 1.2345)
createFunction conn "hs_res_too_large" (Amount 1e20)
query_ conn "SELECT hs_res_rounded()::VARCHAR, (1.2345::DOUBLE)::DECIMAL(18,3)::VARCHAR"
>>= (@?= [("1.235" :: Text.Text, "1.235" :: Text.Text)])
assertThrows
(query_ conn "SELECT hs_res_too_large()" :: IO [Only Double])
(Text.isInfixOf "to DECIMAL(18,3)" . sqlErrorMessage)
query_ conn "SELECT hs_res_int8(), hs_res_word16(), hs_res_float(), hs_res_string(), hs_res_integer(), hs_res_amount()::DOUBLE"
>>= (@?= [(1 :: Int8, 1 :: Word16, 1.5 :: Float, "x" :: Text.Text, 2 ^ (100 :: Int) :: Integer, 1.25 :: Double)])
, testCase "writes nested, record, union, and NULL results" $
withConnection ":memory:" \conn -> do
createFunction conn "hs_res_user" (\(i :: Int64) -> ViaUser i ("u" <> Text.pack (show i)))
createFunction conn "hs_res_maybe_user" (\(i :: Int64) -> if odd i then Nothing else Just (ViaUser i "even"))
createFunction conn "hs_res_shape" (\(i :: Int64) -> if even i then ViaCircle (fromIntegral i) else ViaSquare 1)
createFunction conn "hs_res_list" (\(i :: Int64) -> [0 .. i])
createFunction conn "hs_res_map" (\(i :: Int64) -> Map.fromList [(Text.pack (show i), fromIntegral i :: Int16)])
createFunction conn "hs_res_maybe_small" (\(i :: Int64) -> if odd i then Nothing else Just (fromIntegral i :: Int16))
query_
conn
"SELECT typeof(hs_res_user(1)), typeof(hs_res_maybe_user(1)), typeof(hs_res_shape(1)), typeof(hs_res_list(1)), typeof(hs_res_map(1)), typeof(hs_res_maybe_small(1))"
>>= ( @?=
[
( "STRUCT(vuId BIGINT, vuName VARCHAR)" :: Text.Text
, "STRUCT(vuId BIGINT, vuName VARCHAR)" :: Text.Text
, "UNION(ViaCircle STRUCT(field1 DOUBLE), ViaSquare STRUCT(field1 DOUBLE))" :: Text.Text
, "BIGINT[]" :: Text.Text
, "MAP(VARCHAR, SMALLINT)" :: Text.Text
, "SMALLINT" :: Text.Text
)
]
)
rows <-
query_
conn
"SELECT hs_res_user(i), hs_res_maybe_user(i), hs_res_shape(i), hs_res_list(i), hs_res_map(i), hs_res_maybe_small(i) FROM range(5) t(i) ORDER BY i"
rows
@?= [ ( ViaUser i ("u" <> Text.pack (show i))
, if odd i then Nothing else Just (ViaUser i "even")
, if even i then ViaCircle (fromIntegral i) else ViaSquare 1
, [0 .. i]
, Map.fromList [(Text.pack (show i), fromIntegral i :: Int16)]
, if odd i then Nothing else Just (fromIntegral i :: Int16)
)
| i <- [0 .. 4 :: Int64]
]
-- 5000 rows fill more than one chunk. Each odd row is NULL.
query_ conn "SELECT count(*) FILTER (hs_res_maybe_user(i) IS NULL), count(*) FILTER (hs_res_maybe_small(i) IS NULL) FROM range(5000) t(i)"
>>= (@?= [(2500 :: Int64, 2500 :: Int64)])
, testCase "keeps the SQL type of a narrow result in arithmetic" $
withConnection ":memory:" \conn -> do
createFunction conn "hs_small" (id :: Int16 -> Int16)
createFunction conn "hs_unsigned" (id :: Word16 -> Word16)
-- DuckDB computes SMALLINT * SMALLINT as SMALLINT, and USMALLINT - USMALLINT as USMALLINT.
assertThrows
(query_ conn "SELECT hs_small(30000) * hs_small(30000)" :: IO [Only Int64])
(Text.isInfixOf "Overflow in multiplication of INT16" . sqlErrorMessage)
assertThrows
(query_ conn "SELECT hs_unsigned(1) - hs_unsigned(2)" :: IO [Only Int64])
(Text.isInfixOf "Overflow in subtraction of UINT16" . sqlErrorMessage)
query_ conn "SELECT hs_small(30000)::INTEGER * hs_small(30000), hs_unsigned(1)::INTEGER - hs_unsigned(2)"
>>= (@?= [(900000000 :: Int64, -1 :: Int64)])
, testCase "rejects an argument or result type with no declaration" $
withConnection ":memory:" \conn -> do
let rejects expected action = assertThrows action (Text.isInfixOf expected . sqlErrorMessage)
rejects "hs_bad_struct: argument arg0: StructValue has no static field types" $
createFunction conn "hs_bad_struct" (\(_ :: StructValue FieldValue) -> True)
rejects "hs_bad_result: result: StructValue has no static field types" $
createFunction conn "hs_bad_result" (\(_ :: Int64) -> (undefined :: StructValue FieldValue))
query_ conn "SELECT count(*) FROM duckdb_functions() WHERE function_name LIKE 'hs\\_bad\\_%' ESCAPE '\\'" >>= (@?= [Only (0 :: Int64)])
, testCase "scalar callbacks read nested VARIANTs after the registering connection closes" $
withEnvironment \environment -> withInstance environment [] \instance' -> do
let payload = Variant (variantObject [("numbers", FieldList [FieldInt64 42, FieldNull])])
values = Map.singleton "a" [Just payload, Nothing]
withConnectionOn instance' \conn -> do
createFunction conn "hs_nested_variant" ((== values) :: Map.Map Text.Text [Maybe Variant] -> Bool)
createFunction conn "hs_variant_union" ((== Payload payload) :: VariantChoice -> Bool)
createFunctionWithState conn "hs_stateful_variant" (pure payload) (\expected (value :: Maybe Variant) -> Just expected == value)
withConnectionOn instance' \conn ->
query
conn
"SELECT hs_nested_variant(?), hs_variant_union(?), hs_variant_union(?), hs_stateful_variant(?), hs_stateful_variant(NULL)"
(values, Payload payload, NoPayload, payload)
>>= (@?= [(True, True, False, True, False)])
, testCase "two connections run the same VARIANT callback concurrently" $
withEnvironment \environment -> withInstance environment [("threads", "1")] \instance' ->
withConnectionOn instance' \first -> withConnectionOn instance' \second -> do
enteredFirst <- newEmptyMVar
enteredSecond <- newEmptyMVar
let meet entered other = do
putMVar entered ()
ready <- timeout 30000000 (readMVar other)
when (ready == Nothing) (fail "the other VARIANT callback did not enter")
createFunction first "hs_variant_read" \case
Variant (FieldInt64 number) -> do
when (number == 0) (meet enteredFirst enteredSecond)
when (number == 5000) (meet enteredSecond enteredFirst)
pure number :: IO Int64
other -> fail ("unexpected VARIANT: " <> show other)
let run conn offset =
query conn "SELECT sum(hs_variant_read((i + ?::BIGINT)::VARIANT))::BIGINT FROM range(5000) t(i)" (Only offset)
>>= (@?= [Only (12497500 + 5000 * offset :: Int64)])
completed <- newEmptyMVar
bracket (forkFinally (run second 5000) (putMVar completed)) (const (void (readMVar completed))) \_ -> do
run first 0
readMVar completed >>= either throwIO pure
, testCase "rejects an empty name or a name with NUL at registration" $
withConnection ":memory:" \conn ->
forM_ ["", "hs_bad\0name"] \name ->
assertThrows
(createFunction conn name (id :: Int64 -> Int64))
(\err -> sqlErrorType err == Just ErrorInputInvalid && sqlErrorMessage err == "duckdb-simple: invalid scalar function name")
, testCase "raises the name error before the state of a closed connection" $ do
conn <- open ":memory:"
close conn
assertThrows
(createFunction conn "" (id :: Int64 -> Int64))
(\err -> sqlErrorType err == Just ErrorInputInvalid && sqlErrorMessage err == "duckdb-simple: invalid scalar function name")
, testCase "a failing call with constant arguments fails as with column arguments" $
withConnection ":memory:" \conn -> do
createFunction conn "hs_fail_negative" (\(x :: Int64) -> (if x < 0 then throwIO (userError "negative") else pure x) :: IO Int64)
let failsWith expected action = assertThrows action (\err -> sqlErrorType err == Just ErrorInputInvalid && expected `Text.isInfixOf` sqlErrorMessage err)
failsWith "negative" (query_ conn "SELECT hs_fail_negative(-1)" :: IO [Only Int64])
failsWith "negative" (query_ conn "SELECT hs_fail_negative(i - 1) FROM range(3) t(i)" :: IO [Only Int64])
failsWith
"duckdb-simple: argument arg0 is NULL, but Int64 cannot hold NULL"
(query_ conn "SELECT hs_fail_negative(NULL::BIGINT)" :: IO [Only Int64])
-- A failing constant in a CASE branch that is not taken does not raise.
query_ conn "SELECT CASE WHEN i > 5 THEN hs_fail_negative(-1) ELSE i END FROM range(3) t(i)" >>= (@?= map Only [0, 1, 2 :: Int64])
, testCase "names the argument that does not convert" $
withConnection ":memory:" \conn -> do
createFunction conn "hs_sum" (\(x :: Int64) (y :: Int64) -> x + y)
assertThrows
(query_ conn "SELECT hs_sum(1, NULL)" :: IO [Only Int64])
(Text.isInfixOf "duckdb-simple: argument arg1 is NULL, but Int64 cannot hold NULL" . sqlErrorMessage)
, testCase "supports IO-based functions" $
withConnection ":memory:" \conn -> do
ref <- newIORef (0 :: Int)
createFunction conn "hs_counter" $ do
atomicModifyIORef' ref \n ->
let next = n + 1
in (next, next)
first <- query_ conn "SELECT hs_counter()" :: IO [Only Int]
second <- query_ conn "SELECT hs_counter()" :: IO [Only Int]
assertEqual "first counter call" [Only 1] first
assertEqual "second counter call" [Only 2] second
, testCase "supports thread-local function state" $ do
conn <- openWithConfig ":memory:" [("threads", "1")]
createFunctionWithState conn "hs_stateful_counter" (newIORef (0 :: Int)) \ref -> do
atomicModifyIORef' ref \n ->
let next = n + 1
in (next, next)
rows <- query_ conn "SELECT hs_stateful_counter() FROM range(3)" :: IO [Only Int]
close conn
assertEqual "stateful rows" [Only 1, Only 2, Only 3] rows
, testCase "returns text rows of each length over more than one chunk" $
withConnection ":memory:" \conn -> do
let texts =
[ Just "a"
, Nothing
, Just "b"
, Just ""
, Just "a\0b"
, Just "123456789012"
, Just "1234567890123"
, Just (Text.replicate 4000 "abcdefghijklmnopqrstuvwxy")
, Just "liður ð þ ✓ 😀"
]
pick (i :: Int64) = texts !! fromIntegral (i `mod` 9) :: Maybe Text.Text
createFunction conn "hs_pick" pick
createFunction conn "hs_echo" (\(text :: Maybe Text.Text) -> text)
-- 5000 rows fill more than one chunk. A NULL row is between two text rows.
rows <- query_ conn "SELECT hs_pick(i) FROM range(5000) t(i) ORDER BY i"
assertEqual "text rows" (map (Only . pick) [0 .. 4999]) rows
-- DuckDB compares the bytes in the vector, also the inline bytes after a short text.
query_
conn
"SELECT bool_and(hs_pick(i) IS NOT DISTINCT FROM CASE i % 9 WHEN 0 THEN 'a' WHEN 1 THEN NULL WHEN 2 THEN 'b' WHEN 3 THEN '' WHEN 4 THEN 'a' || chr(0) || 'b' WHEN 5 THEN '123456789012' WHEN 6 THEN '1234567890123' WHEN 7 THEN repeat('abcdefghijklmnopqrstuvwxy', 4000) ELSE 'liður ð þ ✓ 😀' END) FROM range(5000) t(i)"
>>= (@?= [Only True])
query_ conn "SELECT hs_echo('x'), hs_echo(NULL)" >>= (@?= [(Just "x" :: Maybe Text.Text, Nothing :: Maybe Text.Text)])
]
transactionTests :: TestTree
transactionTests =
testGroup
"transactions"
[ testCase "withTransaction commits on success" $
withConnection ":memory:" \conn -> do
_ <- execute_ conn "CREATE TABLE tx_commit (x INTEGER)"
withTransaction conn $ do
_ <- execute conn "INSERT INTO tx_commit VALUES (?)" (Only (1 :: Int))
pure ()
rows <- query_ conn "SELECT COUNT(*) FROM tx_commit" :: IO [Only Int]
assertEqual "commit" [Only 1] rows
, testCase "withTransaction rolls back on exception" $
withConnection ":memory:" \conn -> do
_ <- execute_ conn "CREATE TABLE tx_rollback (x INTEGER)"
assertThrowsErrorCall $
withTransaction conn $ do
_ <- execute conn "INSERT INTO tx_rollback VALUES (?)" (Only (1 :: Int))
error "boom"
rows <- query_ conn "SELECT COUNT(*) FROM tx_rollback" :: IO [Only Int]
assertEqual "rollback" [Only 0] rows
]
assertThrows :: forall e a. (Exception e) => IO a -> (e -> Bool) -> Assertion
assertThrows action predicate = do
outcome <- try action
case outcome of
Left (err :: e) -> assertBool "unexpected exception" (predicate err)
Right _ -> assertFailure "expected exception, but action succeeded"
assertThrowsSQLError :: IO a -> Assertion
assertThrowsSQLError action =
assertThrows action (const True :: SQLError -> Bool)
assertThrowsErrorCall :: IO a -> Assertion
assertThrowsErrorCall action =
assertThrows action (const True :: ErrorCall -> Bool)
assertThrowsFormatError :: IO a -> (FormatError -> Bool) -> Assertion
assertThrowsFormatError = assertThrows
-- | Give the action a unique temporary path where no file exists. Remove the
-- file after the action if the action created it. DuckDB writes a COPY TO
-- target that exists through a second file, so the path has no file.
withTemporaryPath :: (FilePath -> IO a) -> IO a
withTemporaryPath = bracket acquire release
where
acquire = do
directory <- getTemporaryDirectory
(path, handle) <- openBinaryTempFile directory "duckdb-simple-spec"
hClose handle
removeFile path
pure path
release path = doesFileExist path >>= \exists -> when exists (removeFile path)