Issue with certain type mapping when translating results from duckdb, "Json: error calling MarshalJSON for type orderedmap.OrderedMap: json: unsupported type: duckdb.Map"

[mskyttner]

See #49 for the .yaml used and for replicating this issue. There were some issues with queries 3 an 4, with complaints like this one:

{"reqIdx":-1,"error":"Json: error calling MarshalJSON for type orderedmap.OrderedMap: json: unsupported type: duckdb.Map"}

When I tried to restrict the queries that uses the "json" extension to select columns of specific regular types it worked nicely, for example this statement did not complain:

  - id: Q5
    sql: |
      from read_json_auto('https://api.openalex.org/works/W4388315306')
      select #3, #2, #1

Example where various types used in duckdb are fairly comprehensively serialized to json (I think) can be found here: https://github.com/quackscience/duckdb-extension-httpserver/pull/26/files#diff-5a3853867e339ba44486cb7f7036c326dd290818cef93b6212cf715528e3e7a7

[proofrock]

This is not an upstream issue, as the following minimal reproducer (using only go-duckdb) works. Good news.

package main

import (
        "fmt"
        "log"
        "database/sql"
        _ "github.com/marcboeker/go-duckdb"
)

func printTable(rows *sql.Rows) {                                                                                                                           cols, _ :=                         
        rows.Columns()
        n := len(cols)

        for i := 0; i < n; i++ {
                fmt.Print(cols[i], "\t")
        }
        fmt.Println()

        var fields []interface{}
        for i := 0; i < n; i++ {
                fields = append(fields, new(string))
        }
        for rows.Next() {
                rows.Scan(fields...)
                for i := 0; i < n; i++ {
                        fmt.Print(*(fields[i].(*string)), "\t")
                }
                fmt.Println()
        }
}

func main() {
        db, err := sql.Open("duckdb", "")
        defer db.Close() 
        if err != nil {
                log.Fatal("Uh-oh, can't open DuckDB connection: ", err)
        }
        _, err = db.Exec("load json")
        if err != nil {
                log.Fatal("Uh-oh, failed to load json extension: ", err)
        }
        rows , err := db.Query("from read_json_auto('https://api.openalex.org/works/W4388315306')")
        if err != nil {
                log.Fatal("Uh-oh, failed to query: ", err)
        }
        defer rows.Close()
        printTable(rows)
}

[proofrock]

Hi @mskyttner , I should have fixed it, or at least your case now works. I'll try and look if there are other non-marshallable types... anyway, could you kindly check that the fix actually works as you expect?

You can compile commit e3e3e90 or here's a linux/amd64 binary if that's your architecture:

https://drive.google.com/file/d/192Ir5pgXqWIxcy_HyDZOwP-B5j5U1-Gz/view?usp=sharing

Thanks!

[mskyttner]

Sure! I tried it out and I didn't compile but attempted to run the build a) natively and b) in a container.

The first query I tested earlier works (select 42), but I get this log with a floating point exception when attempting the other queries using that binary:

ws4sql ws4sql-v0.0.0
+ sqlite v3.46.1
+ duckdb v1.1.3
- Parsing config file: duck.yaml
  + Serving database 'duckserve'
  + With 6 stored statements
  + 1 init statements performed
  + Authentication enabled, with 1 credentials
- Web Service listening on 0.0.0.0:12321
SIGFPE: floating-point exception
PC=0x759bc116ee4b m=5 sigcode=1
signal arrived during cgo execution
instruction bytes: 0x49 0xf7 0xf0 0x48 0x29 0xd1 0x48 0x89 0xf2 0x48 0x8d 0x84 0xe 0x0 0x8 0x0

goroutine 18 gp=0xc000104380 m=5 mp=0xc000100008 [syscall]:
runtime.cgocall(0xbe8db0, 0xc000030d90)
        runtime/cgocall.go:167 +0x4b fp=0xc000030d68 sp=0xc000030d30 pc=0x71366b
github.com/marcboeker/go-duckdb._Cfunc_duckdb_prepare_extracted_statement(0x31a74ad0, 0x759ba80016b0, 0x0, 0xc00011e0f8)
        _cgo_gotypes.go:2397 +0x4b fp=0xc000030d90 sp=0xc000030d68 pc=0xb8c56b
github.com/marcboeker/go-duckdb.(*conn).prepareExtractedStmt.func1(0x0?, 0x759ba80016b0, 0x0, 0xc00011e0f8)
        github.com/marcboeker/go-duckdb@v1.8.3/connection.go:150 +0x9c fp=0xc000030dd0 sp=0xc000030d90 pc=0xb8f13c
github.com/marcboeker/go-duckdb.(*conn).prepareExtractedStmt(0xc00002d680, 0x759ba80016b0, 0x0)
        github.com/marcboeker/go-duckdb@v1.8.3/connection.go:150 +0x49 fp=0xc000030e18 sp=0xc000030dd0 pc=0xb8ef09
github.com/marcboeker/go-duckdb.(*conn).prepareStmts(0xc00002d680, {0x277b6a0, 0x34466a0}, {0xc000028280, 0x42})
        github.com/marcboeker/go-duckdb@v1.8.3/connection.go:186 +0x2b1 fp=0xc000030eb8 sp=0xc000030e18 pc=0xb8f451
github.com/marcboeker/go-duckdb.(*conn).QueryContext(0x0?, {0x277b6a0, 0x34466a0}, {0xc000028280?, 0x0?}, {0x34466a0, 0x0, 0x0})
        github.com/marcboeker/go-duckdb@v1.8.3/connection.go:52 +0x4f fp=0xc000030f70 sp=0xc000030eb8 pc=0xb8d84f
database/sql.ctxDriverQuery({0x277b6a0?, 0x34466a0?}, {0x759c084a02b8?, 0xc00002d680?}, {0x0?, 0x0?}, {0xc000028280?, 0x200000003?}, {0x34466a0, 0x0, ...})
        database/sql/ctxutil.go:48 +0xd7 fp=0xc000030ff8 sp=0xc000030f70 pc=0x7ada57
database/sql.(*DB).queryDC.func1()
        database/sql/sql.go:1778 +0x159 fp=0xc0000310b8 sp=0xc000030ff8 pc=0x7b4d59
database/sql.withLock({0x2779470, 0xc000038e80}, 0xc000031218)
        database/sql/sql.go:3566 +0x71 fp=0xc0000310f8 sp=0xc0000310b8 pc=0x7b9d31
database/sql.(*DB).queryDC(0x713b65?, {0x277b6a0, 0x34466a0}, {0x277b710, 0xc000150000}, 0xc000038e80, 0xc0001162a0, {0xc000028280, 0x42}, {0x34466a0, ...})
        database/sql/sql.go:1773 +0x1de fp=0xc0000312a8 sp=0xc0000310f8 pc=0x7b459e
database/sql.(*Tx).QueryContext(0xc00011a080, {0x277b6a0, 0x34466a0}, {0xc000028280, 0x42}, {0x34466a0, 0x0, 0x0})
        database/sql/sql.go:2527 +0xc5 fp=0xc000031318 sp=0xc0000312a8 pc=0x7b6905
main.processWithResultSet(0xc000031730, {0xc000028280, 0x42}, 0x0, {0x0?, {0x34466a0?, 0xc0000d8500?, 0x0?}}, {0x277c180, 0x34466a0})
        github.com/proofrock/ws4sql/web_service.go:98 +0xfc fp=0xc000031530 sp=0xc000031318 pc=0xbe343c
main.launch.handler.func5(0xc000140008)
        github.com/proofrock/ws4sql/web_service.go:413 +0xe1d fp=0xc0000319e0 sp=0xc000031530 pc=0xbe743d
github.com/gofiber/fiber/v2.(*Ctx).Next(0xc0001360e6?)
        github.com/gofiber/fiber/v2@v2.52.6/ctx.go:1025 +0x3d fp=0xc000031a00 sp=0xc0000319e0 pc=0xa79d3d
github.com/gofiber/fiber/v2/middleware/basicauth.New.func1(0xc000140008)
        github.com/gofiber/fiber/v2@v2.52.6/middleware/basicauth/basicauth.go:54 +0x233 fp=0xc000031a80 sp=0xc000031a00 pc=0xbbc1f3
github.com/gofiber/fiber/v2.(*App).next(0xc0000e4f08, 0xc000140008)
        github.com/gofiber/fiber/v2@v2.52.6/router.go:145 +0x1be fp=0xc000031b28 sp=0xc000031a80 pc=0xa8481e
github.com/gofiber/fiber/v2.(*Ctx).Next(0xc000100008?)
        github.com/gofiber/fiber/v2@v2.52.6/ctx.go:1028 +0x4d fp=0xc000031b48 sp=0xc000031b28 pc=0xa79d4d
main.launch.New.func4(0x25f8420?)
        github.com/gofiber/fiber/v2@v2.52.6/middleware/recover/recover.go:43 +0xbb fp=0xc000031bc0 sp=0xc000031b48 pc=0xbe7edb
github.com/gofiber/fiber/v2.(*App).next(0xc0000e4f08, 0xc000140008)
        github.com/gofiber/fiber/v2@v2.52.6/router.go:145 +0x1be fp=0xc000031c68 sp=0xc000031bc0 pc=0xa8481e
github.com/gofiber/fiber/v2.(*App).handler(0xc0000e4f08, 0x754baf?)
        github.com/gofiber/fiber/v2@v2.52.6/router.go:172 +0x69 fp=0xc000031cc8 sp=0xc000031c68 pc=0xa84a49
github.com/gofiber/fiber/v2.(*App).handler-fm(0xc000132268?)
        <autogenerated>:1 +0x26 fp=0xc000031ce8 sp=0xc000031cc8 pc=0xa87c46
github.com/valyala/fasthttp.(*Server).serveConn(0xc0000ea248, {0x277e8d8, 0xc00011e000})
        github.com/valyala/fasthttp@v1.58.0/server.go:2380 +0xed1 fp=0xc000031ec8 sp=0xc000031ce8 pc=0xa58891
github.com/valyala/fasthttp.(*Server).serveConn-fm({0x277e8d8?, 0xc00011e000?})
        <autogenerated>:1 +0x33 fp=0xc000031ef0 sp=0xc000031ec8 pc=0xa65253
github.com/valyala/fasthttp.(*workerPool).workerFunc(0xc000196480, 0xc000128000)
        github.com/valyala/fasthttp@v1.58.0/workerpool.go:225 +0x92 fp=0xc000031fa0 sp=0xc000031ef0 pc=0xa61ed2
github.com/valyala/fasthttp.(*workerPool).getCh.func1()
        github.com/valyala/fasthttp@v1.58.0/workerpool.go:197 +0x32 fp=0xc000031fe0 sp=0xc000031fa0 pc=0xa61c72
runtime.goexit({})
        runtime/asm_amd64.s:1700 +0x1 fp=0xc000031fe8 sp=0xc000031fe0 pc=0x7212a1
created by github.com/valyala/fasthttp.(*workerPool).getCh in goroutine 1
        github.com/valyala/fasthttp@v1.58.0/workerpool.go:196 +0x194

goroutine 1 gp=0xc0000061c0 m=nil [IO wait]:
runtime.gopark(0x0?, 0x0?, 0x0?, 0x0?, 0x0?)
        runtime/proc.go:424 +0xce fp=0xc000185278 sp=0xc000185258 pc=0x71984e
runtime.netpollblock(0x6d3c1b?, 0x6b20c6?, 0x0?)
        runtime/netpoll.go:575 +0xf7 fp=0xc0001852b0 sp=0xc000185278 pc=0x6de457
internal/poll.runtime_pollWait(0x759c084a11a0, 0x72)
        runtime/netpoll.go:351 +0x85 fp=0xc0001852d0 sp=0xc0001852b0 pc=0x718b45
internal/poll.(*pollDesc).wait(0xc000038f00?, 0x759c084507c8?, 0x0)
        internal/poll/fd_poll_runtime.go:84 +0x27 fp=0xc0001852f8 sp=0xc0001852d0 pc=0x78f0e7
internal/poll.(*pollDesc).waitRead(...)
        internal/poll/fd_poll_runtime.go:89
internal/poll.(*FD).Accept(0xc000038f00)
        internal/poll/fd_unix.go:620 +0x295 fp=0xc0001853a0 sp=0xc0001852f8 pc=0x790835
net.(*netFD).accept(0xc000038f00)
        net/fd_unix.go:172 +0x29 fp=0xc000185458 sp=0xc0001853a0 pc=0x860809
net.(*TCPListener).accept(0xc000026d00)
        net/tcpsock_posix.go:159 +0x1e fp=0xc0001854a8 sp=0xc000185458 pc=0x871c9e
net.(*TCPListener).Accept(0xc000026d00)
        net/tcpsock.go:372 +0x30 fp=0xc0001854d8 sp=0xc0001854a8 pc=0x870fb0
github.com/valyala/fasthttp.acceptConn(0xc0000ea248, {0x277aba0, 0xc000026d00}, 0xc000185668)
        github.com/valyala/fasthttp@v1.58.0/server.go:1937 +0x55 fp=0xc0001855a8 sp=0xc0001854d8 pc=0xa57255
github.com/valyala/fasthttp.(*Server).Serve(0xc0000ea248, {0x277aba0, 0xc000026d00})
        github.com/valyala/fasthttp@v1.58.0/server.go:1831 +0x4d2 fp=0xc0001856e8 sp=0xc0001855a8 pc=0xa56db2
github.com/gofiber/fiber/v2.(*App).Listen(0xc0000e4f08, {0xc000015290?, 0x3?})
        github.com/gofiber/fiber/v2@v2.52.6/listen.go:93 +0x1c5 fp=0xc000185798 sp=0xc0001856e8 pc=0xa7ce25
main.launch({{0x2684408, 0x7}, 0x3021, {0xc0001941c0, 0x1, 0x1}, 0x0}, 0x0?)
        github.com/proofrock/ws4sql/ws4sql.go:268 +0x168d fp=0xc000185e80 sp=0xc000185798 pc=0xbe638d
main.main()
        github.com/proofrock/ws4sql/ws4sql.go:61 +0x233 fp=0xc000185f50 sp=0xc000185e80 pc=0xbe4cd3
runtime.main()
        runtime/proc.go:272 +0x28b fp=0xc000185fe0 sp=0xc000185f50 pc=0x6e590b
runtime.goexit({})
        runtime/asm_amd64.s:1700 +0x1 fp=0xc000185fe8 sp=0xc000185fe0 pc=0x7212a1

goroutine 2 gp=0xc000006c40 m=nil [force gc (idle)]:
runtime.gopark(0x0?, 0x0?, 0x0?, 0x0?, 0x0?)
        runtime/proc.go:424 +0xce fp=0xc00006afa8 sp=0xc00006af88 pc=0x71984e
runtime.goparkunlock(...)
        runtime/proc.go:430
runtime.forcegchelper()
        runtime/proc.go:337 +0xb3 fp=0xc00006afe0 sp=0xc00006afa8 pc=0x6e5c53
runtime.goexit({})
        runtime/asm_amd64.s:1700 +0x1 fp=0xc00006afe8 sp=0xc00006afe0 pc=0x7212a1
created by runtime.init.7 in goroutine 1
        runtime/proc.go:325 +0x1a

goroutine 3 gp=0xc000007180 m=nil [GC sweep wait]:
runtime.gopark(0x0?, 0x0?, 0x0?, 0x0?, 0x0?)
        runtime/proc.go:424 +0xce fp=0xc00006b780 sp=0xc00006b760 pc=0x71984e
runtime.goparkunlock(...)
        runtime/proc.go:430
runtime.bgsweep(0xc000098000)
        runtime/mgcsweep.go:277 +0x94 fp=0xc00006b7c8 sp=0xc00006b780 pc=0x6d0514
runtime.gcenable.gowrap1()
        runtime/mgc.go:204 +0x25 fp=0xc00006b7e0 sp=0xc00006b7c8 pc=0x6c4c25
runtime.goexit({})
        runtime/asm_amd64.s:1700 +0x1 fp=0xc00006b7e8 sp=0xc00006b7e0 pc=0x7212a1
created by runtime.gcenable in goroutine 1
        runtime/mgc.go:204 +0x66

goroutine 4 gp=0xc000007340 m=nil [GC scavenge wait]:
runtime.gopark(0xc000098000?, 0x2772248?, 0x1?, 0x0?, 0xc000007340?)
        runtime/proc.go:424 +0xce fp=0xc00006bf78 sp=0xc00006bf58 pc=0x71984e
runtime.goparkunlock(...)
        runtime/proc.go:430
runtime.(*scavengerState).park(0x320c680)
        runtime/mgcscavenge.go:425 +0x49 fp=0xc00006bfa8 sp=0xc00006bf78 pc=0x6cdf49
runtime.bgscavenge(0xc000098000)
        runtime/mgcscavenge.go:653 +0x3c fp=0xc00006bfc8 sp=0xc00006bfa8 pc=0x6ce4bc
runtime.gcenable.gowrap2()
        runtime/mgc.go:205 +0x25 fp=0xc00006bfe0 sp=0xc00006bfc8 pc=0x6c4bc5
runtime.goexit({})
        runtime/asm_amd64.s:1700 +0x1 fp=0xc00006bfe8 sp=0xc00006bfe0 pc=0x7212a1
created by runtime.gcenable in goroutine 1
        runtime/mgc.go:205 +0xa5

goroutine 5 gp=0xc000007c00 m=nil [finalizer wait]:
runtime.gopark(0xc00006a648?, 0x6bb425?, 0xb0?, 0x1?, 0xc0000061c0?)
        runtime/proc.go:424 +0xce fp=0xc00006a620 sp=0xc00006a600 pc=0x71984e
runtime.runfinq()
        runtime/mfinal.go:193 +0x107 fp=0xc00006a7e0 sp=0xc00006a620 pc=0x6c3ca7
runtime.goexit({})
        runtime/asm_amd64.s:1700 +0x1 fp=0xc00006a7e8 sp=0xc00006a7e0 pc=0x7212a1
created by runtime.createfing in goroutine 1
        runtime/mfinal.go:163 +0x3d

goroutine 6 gp=0xc0000fe000 m=nil [chan receive]:
runtime.gopark(0x0?, 0x0?, 0x0?, 0x0?, 0x0?)
        runtime/proc.go:424 +0xce fp=0xc00006c718 sp=0xc00006c6f8 pc=0x71984e
runtime.chanrecv(0xc0000a42a0, 0x0, 0x1)
        runtime/chan.go:639 +0x41c fp=0xc00006c790 sp=0xc00006c718 pc=0x6b4bfc
runtime.chanrecv1(0x0?, 0x0?)
        runtime/chan.go:489 +0x12 fp=0xc00006c7b8 sp=0xc00006c790 pc=0x6b47b2
runtime.unique_runtime_registerUniqueMapCleanup.func1(...)
        runtime/mgc.go:1781
runtime.unique_runtime_registerUniqueMapCleanup.gowrap1()
        runtime/mgc.go:1784 +0x2f fp=0xc00006c7e0 sp=0xc00006c7b8 pc=0x6c7c4f
runtime.goexit({})
        runtime/asm_amd64.s:1700 +0x1 fp=0xc00006c7e8 sp=0xc00006c7e0 pc=0x7212a1
created by unique.runtime_registerUniqueMapCleanup in goroutine 1
        runtime/mgc.go:1779 +0x96

goroutine 10 gp=0xc0000fe540 m=nil [select]:
runtime.gopark(0xc00006df88?, 0x2?, 0xb8?, 0x95?, 0xc00006df84?)
        runtime/proc.go:424 +0xce fp=0xc00006de28 sp=0xc00006de08 pc=0x71984e
runtime.selectgo(0xc00006df88, 0xc00006df80, 0xc0000a4770?, 0x0, 0x0?, 0x1)
        runtime/select.go:335 +0x7a5 fp=0xc00006df50 sp=0xc00006de28 pc=0x6f7705
database/sql.(*DB).connectionOpener(0xc0000b1d40, {0x277b710, 0xc0000d85f0})
        database/sql/sql.go:1253 +0x87 fp=0xc00006dfb8 sp=0xc00006df50 pc=0x7b1047
database/sql.OpenDB.gowrap1()
        database/sql/sql.go:833 +0x28 fp=0xc00006dfe0 sp=0xc00006dfb8 pc=0x7aff48
runtime.goexit({})
        runtime/asm_amd64.s:1700 +0x1 fp=0xc00006dfe8 sp=0xc00006dfe0 pc=0x7212a1
created by database/sql.OpenDB in goroutine 1
        database/sql/sql.go:833 +0x130

goroutine 13 gp=0xc0000fe700 m=nil [sleep]:
runtime.gopark(0x29a50fb5502?, 0x6b515e?, 0xf0?, 0x4a?, 0x0?)
        runtime/proc.go:424 +0xce fp=0xc000066760 sp=0xc000066740 pc=0x71984e
time.Sleep(0x2540be400)
        runtime/time.go:300 +0xf2 fp=0xc000066798 sp=0xc000066760 pc=0x71d212
github.com/valyala/fasthttp.(*workerPool).Start.func2()
        github.com/valyala/fasthttp@v1.58.0/workerpool.go:68 +0x50 fp=0xc0000667e0 sp=0xc000066798 pc=0xa61410
runtime.goexit({})
        runtime/asm_amd64.s:1700 +0x1 fp=0xc0000667e8 sp=0xc0000667e0 pc=0x7212a1
created by github.com/valyala/fasthttp.(*workerPool).Start in goroutine 1
        github.com/valyala/fasthttp@v1.58.0/workerpool.go:60 +0xc5

goroutine 20 gp=0xc000104540 m=nil [chan receive]:
runtime.gopark(0x25ee560?, 0xc00009a000?, 0x98?, 0xe7?, 0x756e99?)
        runtime/proc.go:424 +0xce fp=0xc00012e708 sp=0xc00012e6e8 pc=0x71984e
runtime.chanrecv(0xc000200070, 0x0, 0x1)
        runtime/chan.go:639 +0x41c fp=0xc00012e780 sp=0xc00012e708 pc=0x6b4bfc
runtime.chanrecv1(0xc000150000?, 0x6ede1b?)
        runtime/chan.go:489 +0x12 fp=0xc00012e7a8 sp=0xc00012e780 pc=0x6b47b2
database/sql.(*Tx).awaitDone(0xc00011a080)
        database/sql/sql.go:2204 +0x2b fp=0xc00012e7c8 sp=0xc00012e7a8 pc=0x7b5d8b
database/sql.(*DB).beginDC.gowrap1()
        database/sql/sql.go:1917 +0x25 fp=0xc00012e7e0 sp=0xc00012e7c8 pc=0x7b5185
runtime.goexit({})
        runtime/asm_amd64.s:1700 +0x1 fp=0xc00012e7e8 sp=0xc00012e7e0 pc=0x7212a1
created by database/sql.(*DB).beginDC in goroutine 18
        database/sql/sql.go:1917 +0x1ef

rax    0xffffffffffffffff
rbx    0x0
rcx    0xffffffffffffffff
rdx    0x0
rdi    0x800000
rsi    0x1000
rbp    0x759bbb3f9d70
rsp    0x759bbb3f9cd0
r8     0x0
r9     0x7
r10    0x759bbb3f9cd0
r11    0x246
r12    0x9
r13    0x48
r14    0x1
r15    0x759ba800c2d0
rip    0x759bc116ee4b
rflags 0x10297
cs     0x33
fs     0x0
gs     0x0
make: *** [Makefile:10: custom] Error 2

[proofrock]

Shoot! Static linking is definitely broken with duckdb. Non-static binaries don't present your last issue but... they're non-static. I'll need to go deeper on this.

Have you got the possibility to make a non-static build (make build)? If not, I will build a temporary docker image for now.

Thanks!

[mskyttner]

I can try it out tomorrow...

[mskyttner]

Yes, nice! All the queries work when I did the local build and used that binary.

Pretty nice, and it seems somehow the json extension works.

I think next I will attempt to define some query against a remote S3 source to see if I can get that to work.

[proofrock]

Wonderful, thanks! I'll work on proper static compilation :-(

[proofrock]

I had some success compiling in musl, under alpine. Could you kindly try this?

https://drive.google.com/file/d/1_EVTMIRJGweu3HcZTMyq47sfKwONbI-l/view?usp=sharing

Important note: as you will notice, loading extension is disabled (for some reasons I don't understand). Keep this in mind. I compiled https, parquet and json for now.