Skip to content

File creation error when passing output file to COPY TO as a param #382

Closed
Closed
File creation error when passing output file to COPY TO as a param#382
Labels
reproduced

Description

@jgrg
jgrg
opened on Mar 15, 2026

What happens?

Using a placeholder for the output file in a COPY TO statement fails if the file does not already exist. Since I get the same error via both Rust and Python, it might not belong in either duckdb-rs or duckdb-python.

(Is a function to SQL escape parameters available via the API?)

To Reproduce

This Python code with an output file name as a param:

import duckdb
conn = duckdb.connect()
inp = "input.ndjson"
out = "output.ndjson"
conn.execute("COPY (FROM read_json(?)) TO ? (FORMAT JSON)", [inp, out])

fails with:

    conn.execute("COPY (FROM read_json(?)) TO ? (FORMAT JSON)", [inp, out])
    ~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
_duckdb.IOException: IO Error: No files found that match the pattern "output.ndjson"

LINE 1: COPY (FROM read_json(?)) TO ? (FORMAT JSON)
                   ^

Whereas incorporating the file name into the string works, and tmp.ndjson is created:

safe = out.replace("'", "''")
conn.execute(f"COPY (FROM read_json(?)) TO '{safe}' (FORMAT JSON)", [inp])

It seems that having the output file as a placeholder almost works, but the fopen() mode is perhaps wrong.

OS:

MacOS

DuckDB Package Version:

1.5.0

Python Version:

3.13

Full Name:

James Gilbert

Affiliation:

Wellcome Sanger Institute

What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have tested with a stable release

Did you include all relevant data sets for reproducing the issue?

Not applicable - the reproduction does not require a data set

Did you include all code required to reproduce the issue?

  • Yes, I have

Did you include all relevant configuration to reproduce the issue?

  • Yes, I have

Activity

  1. jgrg
    added
    needs triage
    on Mar 15, 2026
  2. evertlammerts
    added
    needs more info
    reproduced
    and removed
    needs more info
    needs triage
    on Mar 19, 2026

  3. evertlammerts commented on Mar 19, 2026

    @evertlammerts
    evertlammerts
    on Mar 19, 2026
    Member

    This seems to be a duckdb core issue related to the multifilereader, which had some changes recently. It reproduces with a prepared statement in the CLI:

    DuckDB v1.5.0 (Variegata)
Enter ".help" for usage hints.
memory D PREPARE query_copy AS COPY (FROM read_json(?)) TO ? (FORMAT JSON);
memory D EXECUTE query_copy('example.json', 'output.json');
IO Error:
No files found that match the pattern "output.json"

LINE 1: EXECUTE query_copy('example.json', 'output.json');

    For the record, the backtrace is:

    * thread #2, queue = 'com.apple.main-thread', stop reason = breakpoint 1.1
  * frame #0: 0x000000010d88d284 _duckdb.cpython-313-darwin.so`duckdb::FileSystem::GlobFileList(this=0x00006000026aedd0, pattern="output.json", input=0x0000600001d30a30) at file_system.cpp:704:4
    frame #1: 0x000000010d26e484 _duckdb.cpython-313-darwin.so`duckdb::GlobMultiFileList::ExpandNextPath(this=0x0000600001d309a0) const at multi_file_list.cpp:372:25
    frame #2: 0x000000010d26dbf4 _duckdb.cpython-313-darwin.so`duckdb::LazyMultiFileList::ExpandNextPathInternal(this=0x0000600001d309a0) const at multi_file_list.cpp:338:7
    frame #3: 0x000000010d26df84 _duckdb.cpython-313-darwin.so`duckdb::LazyMultiFileList::GetFile(this=0x0000600001d309a0, i=1) const at multi_file_list.cpp:323:8
    frame #4: 0x000000010d26de14 _duckdb.cpython-313-darwin.so`duckdb::LazyMultiFileList::GetExpandResult(this=0x0000600001d309a0) const at multi_file_list.cpp:304:8
    frame #5: 0x000000010d2773dc _duckdb.cpython-313-darwin.so`duckdb::MultiFileReader::CreateFileList(this=0x00006000024c5820, context=0x00000001010414b8, paths=0x000000016fdf6970, glob_input=0x000000016fdf6c38) at multi_file_reader.cpp:122:11
    frame #6: 0x000000010d277734 _duckdb.cpython-313-darwin.so`duckdb::MultiFileReader::CreateFileList(this=0x00006000024c5820, context=0x00000001010414b8, input=0x00006000031d8fc0, glob_input=0x000000016fdf6c38) at multi_file_reader.cpp:131:9
    frame #7: 0x000000010be9eba8 _duckdb.cpython-313-darwin.so`duckdb::MultiFileFunction<duckdb::JSONMultiFileInfo>::MultiFileBind(context=0x00000001010414b8, input=0x000000016fdf7470, return_types=0x000000016fdf74f0, names=0x000000016fdf74d8) at multi_file_function.hpp:174:39
    frame #8: 0x000000010c8668b0 _duckdb.cpython-313-darwin.so`duckdb::Binder::BindTableFunctionInternal(this=0x0000000105c4e190, table_function=0x000000016fdf7b58, ref=0x0000600000a8d700, parameters=vector<duckdb::Value, true, std::__1::allocator<duckdb::Value> > @ 0x000000016fdf78b8, named_parameters=size=0, input_table_types=vector<duckdb::LogicalType, true, std::__1::allocator<duckdb::LogicalType> > @ 0x000000016fdf7878, input_table_names=vector<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> >, true, std::__1::allocator<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > > > @ 0x000000016fdf7860) at bind_table_function.cpp:239:15
    frame #9: 0x000000010c8692ac _duckdb.cpython-313-darwin.so`duckdb::Binder::Bind(this=0x0000000105c4e190, ref=0x0000600000a8d700) at bind_table_function.cpp:462:9
    frame #10: 0x000000010c904c64 _duckdb.cpython-313-darwin.so`duckdb::Binder::Bind(this=0x0000000105c4e190, ref=0x0000600000a8d700) at binder.cpp:158:12
    frame #11: 0x000000010c75c7f0 _duckdb.cpython-313-darwin.so`duckdb::Binder::BindNode(this=0x0000000105c4e190, statement=0x0000600001ddf480) at bind_select_node.cpp:386:20
    frame #12: 0x000000010c7796e4 _duckdb.cpython-313-darwin.so`duckdb::Binder::BindNode(this=0x0000000105c4e190, node=0x0000600001ddf480) at bind_cte_node.cpp:29:33
    frame #13: 0x000000010c904aa0 _duckdb.cpython-313-darwin.so`duckdb::Binder::Bind(this=0x0000000105c4e190, node=0x0000600001ddf480) at binder.cpp:139:9
    frame #14: 0x000000010be09b24 _duckdb.cpython-313-darwin.so`duckdb::CopyToJSONPlan(binder=0x00000001055ce0c0, stmt=0x0000600000facf50) at copy_json.cpp:71:38
    frame #15: 0x000000010c797644 _duckdb.cpython-313-darwin.so`duckdb::Binder::BindCopyTo(this=0x00000001055ce0c0, stmt=0x0000600000facf50, function=0x000000011e12bb10, copy_to_type=COPY_TO_FILE) at bind_copy.cpp:92:10
    frame #16: 0x000000010c79d804 _duckdb.cpython-313-darwin.so`duckdb::Binder::Bind(this=0x00000001055ce0c0, stmt=0x0000600000facf50, copy_to_type=COPY_TO_FILE) at bind_copy.cpp:621:10
    frame #17: 0x000000010c902fc4 _duckdb.cpython-313-darwin.so`duckdb::Binder::Bind(this=0x00000001055ce0c0, statement=0x0000600000facf50) at binder.cpp:85:10
    frame #18: 0x000000010c92919c _duckdb.cpython-313-darwin.so`duckdb::Planner::CreatePlan(this=0x000000016fdfba60, statement=0x0000600000facf50) at planner.cpp:57:34
    frame #19: 0x000000010c92a5e0 _duckdb.cpython-313-darwin.so`duckdb::Planner::CreatePlan(this=0x000000016fdfba60, statement=unique_ptr<duckdb::SQLStatement, std::__1::default_delete<duckdb::SQLStatement>, true> @ 0x000000016fdfb9c8) at planner.cpp:163:3
    frame #20: 0x000000010dfd3b08 _duckdb.cpython-313-darwin.so`duckdb::ClientContext::CreatePreparedStatementInternal(this=0x00000001010414b8, lock=0x0000600002698610, query="COPY (FROM read_json(?)) TO ? (FORMAT JSON)", statement=unique_ptr<duckdb::SQLStatement, std::__1::default_delete<duckdb::SQLStatement>, true> @ 0x000000016fdfbca0, parameters=PendingQueryParameters @ 0x000000016fdfbba8) at client_context.cpp:404:18
    frame #21: 0x000000010dfd47bc _duckdb.cpython-313-darwin.so`duckdb::ClientContext::CreatePreparedStatement(this=0x00000001010414b8, lock=0x0000600002698610, query="COPY (FROM read_json(?)) TO ? (FORMAT JSON)", statement=unique_ptr<duckdb::SQLStatement, std::__1::default_delete<duckdb::SQLStatement>, true> @ 0x000000016fdfc058, parameters=PendingQueryParameters @ 0x000000016fdfbe28, mode=PREPARE_AND_EXECUTE) at client_context.cpp:498:9
    frame #22: 0x000000010dfd8630 _duckdb.cpython-313-darwin.so`duckdb::ClientContext::PendingStatementInternal(this=0x00000001010414b8, lock=0x0000600002698610, query="COPY (FROM read_json(?)) TO ? (FORMAT JSON)", statement=unique_ptr<duckdb::SQLStatement, std::__1::default_delete<duckdb::SQLStatement>, true> @ 0x000000016fdfc378, parameters=0x000000016fdfca40) at client_context.cpp:838:18
    frame #23: 0x000000010dfd9498 _duckdb.cpython-313-darwin.so`duckdb::ClientContext::PendingStatementOrPreparedStatement(this=0x00000001010414b8, lock=0x0000600002698610, query="COPY (FROM read_json(?)) TO ? (FORMAT JSON)", statement=unique_ptr<duckdb::SQLStatement, std::__1::default_delete<duckdb::SQLStatement>, true> @ 0x000000016fdfc5b0, prepared=0x000000016fdfc918, parameters=0x000000016fdfca40) at client_context.cpp:957:14
    frame #24: 0x000000010dfd8184 _duckdb.cpython-313-darwin.so`duckdb::ClientContext::PendingStatementOrPreparedStatementInternal(this=0x00000001010414b8, lock=0x0000600002698610, query="COPY (FROM read_json(?)) TO ? (FORMAT JSON)", statement=unique_ptr<duckdb::SQLStatement, std::__1::default_delete<duckdb::SQLStatement>, true> @ 0x000000016fdfc900, prepared=0x000000016fdfc918, parameters=0x000000016fdfca40) at client_context.cpp:930:9
    frame #25: 0x000000010dfd9024 _duckdb.cpython-313-darwin.so`duckdb::ClientContext::PendingQueryInternal(this=0x00000001010414b8, lock=0x0000600002698610, statement=unique_ptr<duckdb::SQLStatement, std::__1::default_delete<duckdb::SQLStatement>, true> @ 0x000000016fdfca30, parameters=0x000000016fdfca40, verify=true) at client_context.cpp:1149:10
    frame #26: 0x000000010dfdaaa0 _duckdb.cpython-313-darwin.so`duckdb::ClientContext::PendingQuery(this=0x00000001010414b8, statement=unique_ptr<duckdb::SQLStatement, std::__1::default_delete<duckdb::SQLStatement>, true> @ 0x000000016fdfcb18, values=size=2, parameters=(output_type = ALLOW_STREAMING, memory_type = IN_MEMORY)) at client_context.cpp:1136:10

    @szarnyasg can you move this to core?

  4. jgrg commented on Mar 24, 2026

    @jgrg
    jgrg
    on Mar 24, 2026
    Author

    ping @szarnyasg !

  5. evertlammerts commented on Mar 24, 2026

    @evertlammerts
    evertlammerts
    on Mar 24, 2026
    Member

    I opened a new issue on core to track this: duckdb/duckdb#21578

  6. evertlammerts
    closed this as completedon Mar 24, 2026
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    reproduced

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions