CHECK IS JSON in Oracle19 will not return a dict/list when using NVL(:bind, ...) with Join.

[mkmoisen]

Unfortunately I am not able to replicate this issue using new tables.

Given the lack of replication, I am hoping someone might be able to guess what is going on. Otherwise, please close the issue as I've found a workaround using CAST.

---

In Oracle 19 I have a table with a varchar2 column and a IS JSON constraint.

Normally when I query from this table in oracledb, it will return a dict and I do not need to do json.loads.

However, depending on whether I use NVL with a bind variable, or whether I'm joining more than one table, it will either return a string or a dict.

For example this NVL with a join returns a string, not a dict:

cur.execute(
    """
        select
            oracledb_json.content
        from 
            oracledb_json
            join oracledb_join
                on 1=1
                and oracledb_json.project_id = oracledb_join.project_id
                and oracledb_json.id = oracledb_join.id
        where 1=1
            and oracledb_json.project_id = NVL(:project_id, oracledb_json.project_id)
            and oracledb_join.id = NVL(:id, oracledb_join.id)
    """,
    dict(
        project_id=10,
        id=1,
    )
)

assert not cur.description[0].is_json

assert isinstance(cur.fetchone()[0], str)

Yet if I remove the NVL, or if I keep the NVL but remove the join, the metadata is_json is true and it will return a dict.

In addition, if I change the NVL to:

and oracledb_join.id = NVL(CAST(:id AS NUMBER), oracledb_join.id)

It also works as expected.

---

Does anyone know what might be the cause of this?

Again, unfortunately I was not able to replicate when creating new tables.

1. What versions are you using?

Oracle 19
platform.platform: Windows-11-10.0.26100-SP0
sys.maxsize > 2**32: True
platform.python_version: 3.13.0
oracledb.version: 3.1.1

2. Is it an error or a hang or a crash?

error

3. What error(s) or behavior you are seeing?

4. Does your application call init_oracle_client()?

No, I am in thin mode.

5. Include a runnable Python script that shows the problem.

[cjbj]

This reminded me of #428