Failed to update non PK value in data in Python but works in CLI

[hermeschen1116]

What happens?

I have a table whose schema is like this

CREATE TABLE IF NOT EXISTS User.Session(
	create_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
	revoke_at TIMESTAMPTZ,
	session_id UUID PRIMARY KEY,
	user_id UUID NOT NULL,
	user_agent TEXT,
	client_ip TEXT
);

I tried to execute the SQL below using Python API to update it and I confirmed there's eligible data to update but nothing changed and also no exception. While update the record with session_id works fine. And also, the SQL below works fine from CLI.

WITH
    Constant AS (
        SELECT
            get_current_timestamp() AS revoke_time
    )
UPDATE User.Session
SET
    revoke_at = c.revoke_time
FROM
    Constant AS c
WHERE
    (user_id == $user_id)
    AND (revoke_at IS NULL)
RETURNING
    session_id;

def revoke_user_sessions_by_user(
	*,
	user_id: uuid.UUID,
	cursor: duckdb.DuckDBPyConnection,
) -> None:
	parameters: dict[str, typing.Any] = {
		"user_id": user_id,
	}
	cursor.execute(REMOVE_SESSION_BY_USER_ID_CLAUSE, parameters)

	cursor.execute(
		CLEANUP_SESSION_CLAUSE,
		{
			"cleanup_period": TLPR_USER_SESSION_CLEANUP_PERIOD,
			"session_max_age": REFRESH_TOKEN_MAX_AGE,
		},
	)

	LOGGER.debug("Successfully removing the targeted record")

Below is CLEANUP_SESSION_CLAUSE

WITH
    Constant AS (
        SELECT
            (
                get_current_timestamp() - $cleanup_period * INTERVAL '1 day'
            ) AS cleanup_before,
            (
                get_current_timestamp() - $session_max_age * INTERVAL '1 second'
            ) AS already_expired_before
    )
DELETE FROM User.Session USING Constant AS c
WHERE
    (
        (revoke_at IS NOT NULL)
        AND (revoke_at <= c.cleanup_before)
    )
    OR (create_at <= c.already_expired_before);

To Reproduce

import uuid

import duckdb

con = duckdb.connect(":memory:")

con.execute("""
CREATE SCHEMA IF NOT EXISTS "User";

CREATE TABLE IF NOT EXISTS "User".Session(
    create_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    revoke_at TIMESTAMPTZ,
    session_id UUID PRIMARY KEY,
    user_id UUID NOT NULL,
    user_agent TEXT,
    client_ip TEXT
);
""")

user_id = uuid.uuid4()
session_id = uuid.uuid4()

con.execute(
    """
    INSERT INTO "User".Session (create_at, revoke_at, session_id, user_id, user_agent, client_ip)
    VALUES (CURRENT_TIMESTAMP, NULL, ?, ?, 'test-agent', '127.0.0.1')
    """,
    [session_id, user_id],
)

print("Before UPDATE:")
print(con.execute('SELECT create_at, revoke_at, session_id, user_id FROM "User".Session').fetchall())

UPDATE_SQL = """
WITH
    Constant AS (
        SELECT
            get_current_timestamp() AS revoke_time
    )
UPDATE "User".Session
SET
    revoke_at = c.revoke_time
FROM
    Constant AS c
WHERE
    (user_id == $user_id)
    AND (revoke_at IS NULL)
RETURNING
    session_id;
"""

params = {"user_id": user_id}

res = con.execute(UPDATE_SQL, params).fetchall()
print("UPDATE result (RETURNING session_id):", res)

print("After UPDATE:")
print(con.execute('SELECT create_at, revoke_at, session_id, user_id FROM "User".Session').fetchall())

con.close()

OS:

Ubuntu 26.04 x86_64

DuckDB Package Version:

1.5.2

Python Version:

3.13

Full Name:

ヘルメス

Affiliation:

EZCon

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?

Yes

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