Question (Bug?) : User Defined Flags not received when using FETCH or OFFSET.

[solidmetanoia]

Hello.

There is an unexpected behavior with using (FETCH FIRST | OFFSET) X ROWS.
I was testing something with variable checks and there were no issues for the last couple weeks.
Simple constraint added to table with a VARCHAR2 that should always be a JSON string.

ALTER TABLE "USER"."EXAMPLE_TBL" ADD CONSTRAINT "TABLE-ENSURE_JSON" CHECK (VARIABLE IS JSON) ENABLE;

Running:

• SELECT * FROM EXAMPLE_TBL returns the variable VARCHAR2 as a dict - good.
• SELECT * FROM EXAMPLE_TBL WHERE ROWCOUNT=1 returns the same - good.
• SELECT * FROM EXAMPLE_TBL FETCH FIRST 1 ROWS returns the variable as a string - bad. Requires json.loads().

I went in deeper and saw that in src/oracledb/impl/thin/messages.pyx:680 , when using FETCH or OFFSET, the uds_flags are always receiving 0. Auto-incrementing columns, check constraints, everything.
When using SQLAlchemy, if you run a SELECT with .limit(1), it will use FETCH FIRST, giving you the raw string.
Tested on 19c and 21c (xe).

Has anyone here experienced this behavior?

[cjbj]

What exact DB versions did you test with? (And have you had a chance to try 23ai ? !)

[solidmetanoia]

Sorry for the delay, this error isn't really urgent, just interesting

Oracle 19c - 19.23.0.0.0 - AWS RDS
Oracle 21c - 21.3.0.0.0 - the latest docker XE version

Oracle 23ai free - latest docker version
FETCH - uds flags received ✅ → the variable is received as a dict
OFFSET - uds flags missing ❌ → the variable is received as a str

[anthony-tuininga]

I just tried this with 23ai (23.6) with the following SQL:

create table disc_428 (
    id number(9) not null,
    json_val json not null,
    constraint disc_428_ck_1 check (json_val is json)
);

insert into disc_428 values (
    1,
    '{"key1": 5, "key2": 6}'
);

insert into disc_428 values (
    2,
    '{"key3": 15, "key4": 16}'
);

insert into disc_428 values (
    3,
    '{"key5": 25, "key6": 26}'
);

commit;

I then ran this Python script and didn't notice any trouble:

import oracledb

conn = oracledb.connect("user/pw@host/service_name")
cursor = conn.cursor()

print("normal")
cursor.execute("select * from disc_428")
for row in cursor:
    print(row)
print()

print("with fetch first")
cursor.execute("select * from disc_428 fetch first 1 rows only")
for row in cursor:
    print(row)
print()

print("with offset")
cursor.execute(
    """
    select * from disc_428
    order by id
    offset 1 rows
    fetch next 1 rows only
    """)
for row in cursor:
    print(row)
print()

Can you try the same script and let me know if you run into any difficulties with it? I suspect it has something to do with the database itself.

[solidmetanoia]

Thank you. I did forget to check with making a temp table instead of using only the client's table.
Confirmed the JSON constriction works fine when the field is a JSON type, but in my case, the field is a VARCHAR2 (1024) .

create table disc_431 (
    id number(9) not null,
    json_val VARCHAR2 (1024) NOT NULL,
    constraint disc_431_ck_1 check (json_val is json) enable
);

insert into disc_431 values (
    1,
    '{"key1": 5, "key2": 6}'
);

insert into disc_431 values (
    2,
    '{"key3": 15, "key4": 16}'
);

insert into disc_431 values (
    3,
    '{"key5": 25, "key6": 26}'
);

commit;

the 3 numbers between the lines are from a print I added at impl/thin/messages.pyx:636

        buf.read_ub4(&uds_flags)
        print(uds_flags, TNS_UDS_FLAGS_IS_JSON, uds_flags & TNS_UDS_FLAGS_IS_JSON)
        metadata.is_json = uds_flags & TNS_UDS_FLAGS_IS_JSON

updated to 3.0.0, disc_428 / col = json table

normal
<oracledb.thin_impl.ReadBuffer object at 0x142be90>
0 256 0
7424 256 256
<oracledb.thin_impl.ReadBuffer object at 0x142be90>
self.out_var_impls [<oracledb.thin_impl.ThinVarImpl object at 0x7d1ae8fb8d50>, <oracledb.thin_impl.ThinVarImpl object at 0x7d1ae8fbbe20>]
<oracledb.thin_impl.ThinVarImpl object at 0x7d1ae8fb8d50> None None
<oracledb.thin_impl.ThinVarImpl object at 0x7d1ae8fbbe20> None None
(1, {'key1': Decimal('5'), 'key2': Decimal('6')})
(2, {'key3': Decimal('15'), 'key4': Decimal('16')})
self.out_var_impls [<oracledb.thin_impl.ThinVarImpl object at 0x7d1ae8fb8d50>, <oracledb.thin_impl.ThinVarImpl object at 0x7d1ae8fbbe20>]
<oracledb.thin_impl.ThinVarImpl object at 0x7d1ae8fb8d50> None None
<oracledb.thin_impl.ThinVarImpl object at 0x7d1ae8fbbe20> None None
(3, {'key5': Decimal('25'), 'key6': Decimal('26')})

with fetch first
<oracledb.thin_impl.ReadBuffer object at 0x142be90>
0 256 0
7424 256 256
<oracledb.thin_impl.ReadBuffer object at 0x142be90>
self.out_var_impls [<oracledb.thin_impl.ThinVarImpl object at 0x7d1ae8fb8ca0>, <oracledb.thin_impl.ThinVarImpl object at 0x7d1ae8fe37f0>]
<oracledb.thin_impl.ThinVarImpl object at 0x7d1ae8fb8ca0> None None
<oracledb.thin_impl.ThinVarImpl object at 0x7d1ae8fe37f0> None None
(1, {'key1': Decimal('5'), 'key2': Decimal('6')})

with offset
<oracledb.thin_impl.ReadBuffer object at 0x142be90>
0 256 0
3328 256 256
<oracledb.thin_impl.ReadBuffer object at 0x142be90>
self.out_var_impls [<oracledb.thin_impl.ThinVarImpl object at 0x7d1ae8fe38a0>, <oracledb.thin_impl.ThinVarImpl object at 0x7d1ae8fe3a00>]
<oracledb.thin_impl.ThinVarImpl object at 0x7d1ae8fe38a0> None None
<oracledb.thin_impl.ThinVarImpl object at 0x7d1ae8fe3a00> None None
(2, {'key3': Decimal('15'), 'key4': Decimal('16')})

disc_431, col = varchar2, the uds_flags remain 0
since the uds_flags come as 0, the json_converter_fn does not activate

normal
<oracledb.thin_impl.ReadBuffer object at 0x1b5dc40>
0 256 0
4352 256 256
self.out_var_impls [<oracledb.thin_impl.ThinVarImpl object at 0x74d4e1e80a40>, <oracledb.thin_impl.ThinVarImpl object at 0x74d4e1c049a0>]
<oracledb.thin_impl.ThinVarImpl object at 0x74d4e1e80a40> None None
<oracledb.thin_impl.ThinVarImpl object at 0x74d4e1c049a0> <cyfunction _build_json_converter_fn.<locals>.converter at 0x74d4e4599ff0> None

with fetch first
<oracledb.thin_impl.ReadBuffer object at 0x1b5dc40>
0 256 0
4352 256 256
self.out_var_impls [<oracledb.thin_impl.ThinVarImpl object at 0x74d4e1c04a40>, <oracledb.thin_impl.ThinVarImpl object at 0x74d4e1c04ae0>]
<oracledb.thin_impl.ThinVarImpl object at 0x74d4e1c04a40> None None
<oracledb.thin_impl.ThinVarImpl object at 0x74d4e1c04ae0> <cyfunction _build_json_converter_fn.<locals>.converter at 0x74d4e1e98930> None

with offset
<oracledb.thin_impl.ReadBuffer object at 0x1b5dc40>
0 256 0
0 256 0
self.out_var_impls [<oracledb.thin_impl.ThinVarImpl object at 0x74d4e1c04b80>, <oracledb.thin_impl.ThinVarImpl object at 0x74d4e1c04c20>]
<oracledb.thin_impl.ThinVarImpl object at 0x74d4e1c04b80> None None
<oracledb.thin_impl.ThinVarImpl object at 0x74d4e1c04c20> None None

[anthony-tuininga]

Thanks. With VARCHAR2(1024) I see the same results as you do -- the value is returned as a string instead of a dictionary. This is a bug in the database and not in the driver. I'll report it internally -- but if you can report this to Oracle Support as well, that would be helpful.

[anthony-tuininga]

Bug 37445305 has been reported. You can reference that when you contact Oracle Support.

[solidmetanoia]

Cheers,

I have no idea how to contact Oracle Support since it's a client's DB and the website brings me back and forth a bit.
Just figuring out the issue is good enough for me.

[jjspiegel]

Why use VARCHAR 1K rather than JSON type on 23ai?

[anthony-tuininga]

@solidmetanoia said he was using 19c and 21c and we only suggested using 23ai to see if the problem had been resolved or not. If upgrading to 23ai is an option, then switching to JSON will resolve this issue!