Julia: No TimeZone config

[xcaptain]

What happens?

I found that the juliapkg doesn't contains a TimeZone config, which means when using julia, the timezone is always UTC and can't change to another one.

My test code:

using DBInterface
using DuckDB

con = DBInterface.connect(DuckDB.DB, ":memory:")
DBInterface.execute(con, "select current_timestamp;")

got

1×1 DataFrame
 Row │ get_current_timestamp() 
     │ DateTime?
─────┼─────────────────────────
   1 │ 2023-02-17T09:39:07.618

while using CLI, execute the query select current_timestamp I got

v0.7.0 f7827396d7
Enter ".help" for usage hints.
D select current_timestamp;
┌────────────────────────────┐
│  get_current_timestamp()   │
│  timestamp with time zone  │
├────────────────────────────┤
│ 2023-02-17 17:34:31.361+08 │
└────────────────────────────┘

To Reproduce

I tried to set timezone for the current connection session using the following code:

config = DuckDB.Config()
config = DuckDB.set_config(config, "TimeZone", "Asia/Shanghai")

got an error:

ERROR: Unrecognized configuration option "TimeZone"
Stacktrace:
 [1] set_config(config::DuckDB.Config, name::String, option::String)
   @ DuckDB C:\Users\capta\.julia\packages\DuckDB\B2yPk\src\config.jl:27
 [2] top-level scope
   @ REPL[32]:1

OS:

Windows

DuckDB Version:

v0.7.0 f782739

DuckDB Client:

julia

Full Name:

Joey

Affiliation:

Self-Employed

Have you tried this on the latest master branch?

• I agree

Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?

• I agree

[Tishj]

The TimeZone setting is part of the ICU extension, which probably isn't bundled with the Julia client.
You could install and load it, or build DuckDB with it, so it's statically linked

[xcaptain]

I tried to build with icu using:

BUILD_TPCH=1 BUILD_ICU=1 BUILD_JEMALLOC=1 make

This command is copied from the Julia.yml github action, should be right and the generated so file should contains icu support.

I then added a test case in juliapkg/test/test_config.jl:

@testset "Test Set TimeZone" begin
    config = DuckDB.Config()
    DuckDB.set_config(config, "TimeZone", "Asia/Shanghai")
    con = DBInterface.connect(DuckDB.DB, ":memory:", config)

    results = DBInterface.execute(con, "select current_timestamp")
    df = DataFrame(results)

    DBInterface.close!(config)
    DBInterface.close!(con)
end

Then I run ./test.sh test_config.jl, but still got an error

Test Set TimeZone: Error During Test at /home/joey/github/duckdb/tools/juliapkg/test/test_config.jl:34
  Got exception outside of a @test
  Unrecognized configuration option "TimeZone"
  Stacktrace:
   [1] set_config(config::DuckDB.Config, name::String, option::String)
     @ DuckDB ~/github/duckdb/tools/juliapkg/src/config.jl:27
   [2] macro expansion
     @ ~/github/duckdb/tools/juliapkg/test/test_config.jl:36 [inlined]
   [3] macro expansion
     @ /usr/share/julia/stdlib/v1.8/Test/src/Test.jl:1363 [inlined]
   [4] top-level scope
     @ ~/github/duckdb/tools/juliapkg/test/test_config.jl:35
   [5] include(fname::String)
     @ Base.MainInclude ./client.jl:476
   [6] top-level scope
     @ ~/github/duckdb/tools/juliapkg/test/runtests.jl:40
   [7] include(fname::String)
     @ Base.MainInclude ./client.jl:476
   [8] top-level scope
     @ none:1
Test Summary:     | Error  Total  Time
Test Set TimeZone |     1      1  1.4s

The JULIA_DUCKDB_LIBRARY is set to "pwd/../../build/release/src/libduckdb.so" in test.sh, so I'm not using DuckDB_Jll, where am I doing wrong? @Tishj

[Tishj]

In my zshrc I have it set to libduckdb.dylib - not entirely sure if that matters?
But otherwise this sounds like the way to do it, you might need to remove the julia cache rm -rf ~/.julia/compiled

[Mytherin]

Does INSTALL icu; LOAD icu not work for you?

[xcaptain]

I did built a libduckdb.so from source(added some debug informations) that has icu support. I tested on my linux dev machine that works well.

julia> con = DBInterface.connect(DuckDB.DB, ":memory:")
C++ GetOptionByName: threads
C++ duckdb_set_config:threads
C++ duckdb_set_config2:0x7f5cb04ca5c0
C++ GetOptionByName: external_threads
C++ duckdb_set_config:external_threads
C++ duckdb_set_config2:0x7f5cb04ca000
DuckDB.DB(":memory:")

julia> DBInterface.execute(con, "set TimeZone='Asia/Shanghai'")
C++ GetOptionByName: timezone
C++ GetOptionByName null:
0×1 DataFrame
 Row │ Success
     │ Bool
─────┴─────────

julia> DBInterface.execute(con, "LOAD icu")
0×1 DataFrame
 Row │ Success
     │ Bool
─────┴─────────

But the DuckDB package from the julia registry doesn't work(tested on windows)

julia> using DuckDB

julia> con = DBInterface.connect(DuckDB.DB, ":memory:")
DuckDB.DB(":memory:")

julia> DBInterface.execute(con, "SELECT current_setting('TimeZone');")
ERROR: Invalid Input Error: unrecognized configuration parameter "TimeZone"
Stacktrace:
 [1] DuckDB.Stmt(con::DuckDB.Connection, sql::String)
   @ DuckDB C:\Users\capta\.julia\packages\DuckDB\B2yPk\src\statement.jl:11
 [2] prepare
   @ C:\Users\capta\.julia\packages\DuckDB\B2yPk\src\result.jl:760 [inlined]
 [3] prepare
   @ C:\Users\capta\.julia\packages\DuckDB\B2yPk\src\result.jl:761 [inlined]
 [4] execute
   @ C:\Users\capta\.julia\packages\DBInterface\1Gmxx\src\DBInterface.jl:130 [inlined]
 [5] #execute#2
   @ C:\Users\capta\.julia\packages\DBInterface\1Gmxx\src\DBInterface.jl:152 [inlined]
 [6] execute(conn::DuckDB.DB, sql::String)
   @ DBInterface C:\Users\capta\.julia\packages\DBInterface\1Gmxx\src\DBInterface.jl:152
 [7] top-level scope
   @ REPL[3]:1

julia> DBInterface.execute(con, "set TimeZone='Asia/Shanghai'")
ERROR: Execute of query "set TimeZone='Asia/Shanghai'" failed: Catalog Error: unrecognized configuration parameter "TimeZone"

Did you mean: "threads"
Stacktrace:
 [1] execute(stmt::DuckDB.Stmt, params::NamedTuple{(), Tuple{}})
   @ DuckDB C:\Users\capta\.julia\packages\DuckDB\B2yPk\src\result.jl:699
 [2] execute
   @ C:\Users\capta\.julia\packages\DuckDB\B2yPk\src\result.jl:777 [inlined]
 [3] execute
   @ C:\Users\capta\.julia\packages\DBInterface\1Gmxx\src\DBInterface.jl:130 [inlined]
 [4] #execute#2
   @ C:\Users\capta\.julia\packages\DBInterface\1Gmxx\src\DBInterface.jl:152 [inlined]
 [5] execute(conn::DuckDB.DB, sql::String)
   @ DBInterface C:\Users\capta\.julia\packages\DBInterface\1Gmxx\src\DBInterface.jl:152
 [6] top-level scope
   @ REPL[4]:1

julia> DBInterface.execute(con, "INSTALL icu")
0×1 DataFrame
 Row │ Success
     │ Bool
─────┴─────────

julia> DBInterface.execute(con, "LOAD icu")

Please submit a bug report with steps to reproduce this fault, and any error messages that follow (in their entirety). Thanks.
Exception: EXCEPTION_ACCESS_VIOLATION at 0x7ffa4ad7a4d6 -- unknown function (ip: 00007ffa4ad7a4d6)
in expression starting at REPL[6]:1
unknown function (ip: 00007ffa4ad7a4d6)
unknown function (ip: 00007ffa4adeb5d7)
unknown function (ip: 00007ffa4ac3a7ec)
_ZN6duckdb15ExtensionHelper21LoadExternalExtensionERNS_16DatabaseInstanceEPNS_10FileOpenerERKNSt7__cxx1112basic_stringIcSt11char_traitsIcESaIcEEE at C:\Users\capta\.julia\artifacts\8eea02fcf7ce56840fb3c7e7785f171841ed2102\bin\libduckdb.dll (unknown line)
_ZN6duckdb16PipelineExecutor15FetchFromSourceERNS_9DataChunkE at C:\Users\capta\.julia\artifacts\8eea02fcf7ce56840fb3c7e7785f171841ed2102\bin\libduckdb.dll (unknown line)

I think the problem maybe that DuckDB_Jll didn't compile with icu, https://github.com/JuliaPackaging/Yggdrasil/blob/master/D/DuckDB/build_tarballs.jl#L23

Maybe we can consider adding a -DBUILD_ICU_EXTENSION=TRUE or just use make

[xcaptain]

I think it's a bug, so I created 2 PRs to fix it.

1. When compile libduckdb.so for julia, add -DBUILD_ICU_EXTENSION=TRUE option, see: [DuckDB] julia client support set timezone JuliaPackaging/Yggdrasil#6282
2. Added a test case about how to change current session's timezone setting, see: Julia: Support change timezone config #6358

Don't know if I got it right, please take a look.

[Mytherin]

Not bundling an extension in a client is not a bug. Nevertheless - I'm fine with bundling ICU with the Julia extension as we bundle it with other clients as well.