Failures with NCLOB text containing four byte Unicode emoji

[zzzeek]

Hi Anthony / Christopher -

I've narrowed down at least one of the failures I'm having re: LOB to the NCLOB datatype, and it seems to be independent of whether or not I'm using setinputsizes and whether or not I use an outputtype handler. What's most disturbing is that the failure is non-deterministic, failing only sometimes for large strings of text that have many random occurrences of characters in random orders.

This is using NLS_LANG=AMERICAN_AMERICA.AL32UTF8 so we would normally assume the full range of Unicode codepoints should round trip accurately.

The character in question is this: 🐍 , the Python snake (on some GUI elements it looks more like a duck, though if I look closely, it's still a snake).

Oracle server 18c, client version info:

>>> import sys
>>> import platform
>>> 
>>> print("platform.platform:", platform.platform())
platform.platform: Linux-5.14.9-200.fc34.x86_64-x86_64-with-glibc2.33
>>> print("sys.maxsize > 2**32:", sys.maxsize > 2**32)
sys.maxsize > 2**32: True
>>> print("platform.python_version:", platform.python_version())
platform.python_version: 3.10.0
>>> 
>>> import cx_Oracle
>>> print("cx_Oracle.version:", cx_Oracle.version)
cx_Oracle.version: 8.3.0
>>> print("cx_Oracle.clientversion:", cx_Oracle.clientversion())
cx_Oracle.clientversion: (19, 3, 0, 0, 0)

The test case below inserts and returns a row that contains the character embedded in a randomized field of characters. the larger the field of characters, the more chance that it fails. See sample run at the end.

import cx_Oracle
import random

conn = cx_Oracle.connect(user="scott", password="tiger", dsn="oracle18c")


def output_type_handler(cursor, name, default_type, size, precision, scale):
    if default_type in (
        cx_Oracle.CLOB,
        cx_Oracle.NCLOB,
    ):
        return cursor.var(cx_Oracle.LONG_STRING, size, cursor.arraysize)

# fetching LOB without the output type handler also doesn't help
conn.outputtypehandler = output_type_handler


def setup():
    cursor = conn.cursor()

    try:
        cursor.execute("drop table long_text")
    except:
        pass

    cursor.execute(
        """
    CREATE TABLE long_text (
        x INTEGER,
        y NCLOB,
        z INTEGER
    )
    """
    )

    cursor.close()


def run_test(datasize):

    cursor = conn.cursor()

    # the third character is the failure character
    word_seed = u"ab🐍’«cdefg"

    data = u" ".join(
        "".join(random.choice(word_seed) for j in range(150))
        for i in range(datasize)
    )

    # print(f"test data has {len(data.split('🐍'))} of the problematic character 🐍")

    # this actually doesn't help
    # cursor.setinputsizes(
    #     **{"x": cx_Oracle.NUMBER, "y": cx_Oracle.NCLOB, "z": cx_Oracle.NUMBER}
    # )

    cursor.execute(
        "INSERT INTO long_text (x, y, z) VALUES (:x, :y, :z)",
        {"x": 5, "y": data, "z": 10},
    )

    cursor.execute(
        "SELECT long_text.x, long_text.y, long_text.z FROM long_text"
    )
    row = cursor.fetchone()

    try:
        assert row[1] == data
    finally:
        cursor.close()
        conn.rollback()


def harness(datasize, num_runs):
    for i in range(num_runs):
        try:
            run_test(datasize)
        except AssertionError as err:
            print(f"datasize: {datasize} test run {i} failed")
        else:
            print(f"datasize: {datasize} test run {i} succeeded")

setup()

for datasize in (10, 25, 50, 100, 250):
    print(f"\ndatasize: {datasize}")
    harness(datasize, 10)

sample run

$ python test3.py 

datasize: 10
datasize: 10 test run 0 succeeded
datasize: 10 test run 1 succeeded
datasize: 10 test run 2 succeeded
datasize: 10 test run 3 succeeded
datasize: 10 test run 4 succeeded
datasize: 10 test run 5 succeeded
datasize: 10 test run 6 succeeded
datasize: 10 test run 7 succeeded
datasize: 10 test run 8 succeeded
datasize: 10 test run 9 succeeded

datasize: 25
datasize: 25 test run 0 succeeded
datasize: 25 test run 1 succeeded
datasize: 25 test run 2 succeeded
datasize: 25 test run 3 succeeded
datasize: 25 test run 4 succeeded
datasize: 25 test run 5 succeeded
datasize: 25 test run 6 succeeded
datasize: 25 test run 7 succeeded
datasize: 25 test run 8 succeeded
datasize: 25 test run 9 succeeded

datasize: 50
datasize: 50 test run 0 succeeded
datasize: 50 test run 1 succeeded
datasize: 50 test run 2 succeeded
datasize: 50 test run 3 succeeded
datasize: 50 test run 4 succeeded
datasize: 50 test run 5 succeeded
datasize: 50 test run 6 succeeded
datasize: 50 test run 7 succeeded
datasize: 50 test run 8 succeeded
datasize: 50 test run 9 succeeded

datasize: 100
datasize: 100 test run 0 failed
datasize: 100 test run 1 succeeded
datasize: 100 test run 2 succeeded
datasize: 100 test run 3 failed
datasize: 100 test run 4 succeeded
datasize: 100 test run 5 failed
datasize: 100 test run 6 succeeded
datasize: 100 test run 7 failed
datasize: 100 test run 8 failed
datasize: 100 test run 9 succeeded

datasize: 250
datasize: 250 test run 0 succeeded
datasize: 250 test run 1 failed
datasize: 250 test run 2 failed
datasize: 250 test run 3 failed
datasize: 250 test run 4 failed
datasize: 250 test run 5 failed
datasize: 250 test run 6 failed
datasize: 250 test run 7 failed
datasize: 250 test run 8 failed
datasize: 250 test run 9 failed

[zzzeek]

just to make sure I'm not doing anything silly I ported the above program to the mysqlclient driver and ran against MySQL with charset=utf8mb4, there's no failures on that platform, so this is definitely something on the Oracle side.

[zzzeek]

character set info reported by the server

>>> 
>>> cursor.execute("""
... SELECT value AS db_charset
... FROM nls_database_parameters
... WHERE parameter = 'NLS_CHARACTERSET'
... """)
<cx_Oracle.Cursor on <cx_Oracle.Connection to scott@oracle18c>>
>>> 
>>> cursor.fetchall()
[('AL32UTF8',)]
>>> cursor.execute("""
... SELECT value AS db_ncharset
... FROM nls_database_parameters
... WHERE parameter = 'NLS_NCHAR_CHARACTERSET'
... """)
<cx_Oracle.Cursor on <cx_Oracle.Connection to scott@oracle18c>>
>>> cursor.fetchall()
[('AL16UTF16',)]
>>> cursor.execute("""
... SELECT DISTINCT client_charset AS client_charset
... FROM v$session_connect_info
... WHERE sid = SYS_CONTEXT('USERENV', 'SID')
... """)
<cx_Oracle.Cursor on <cx_Oracle.Connection to scott@oracle18c>>
>>> cursor.fetchall()
[('AL32UTF8',)]

[anthony-tuininga]

I am able to replicate with the script you provided. It is not, in fact, intermittent, but very consistent. It depends on the data that is being supplied to the database -- it was intermittent for you simply because you are randomly creating the string to send to the database! I'll see what the problem is and report back once I figure it out. Thanks for supplying this to me!

[zzzeek]

Yes I figured there's some pattern in my random production of strings that causes the issue. looks like a fairly deep issue w/ the encoding.

[cjbj]

@anthony-tuininga has opened an Oracle bug against the DB itself. The number is 33691692

[anthony-tuininga]

To provide a little more information: the issue has nothing to do with insertng the data, only with fetching it using the output type handler.

[zzzeek]

OK for my script above, I can take the outputtypehandler away totally and it still fails in the same way; change the row[1] to row[1].read() and I get the same pattern of early success / late failures. can you try the script below?

import cx_Oracle
import random

conn = cx_Oracle.connect(user="scott", password="tiger", dsn="oracle18c")


def setup():
    cursor = conn.cursor()

    try:
        cursor.execute("drop table long_text")
    except:
        pass

    cursor.execute(
        """
    CREATE TABLE long_text (
        x INTEGER,
        y NCLOB,
        z INTEGER
    )
    """
    )

    cursor.close()


def run_test(datasize):

    cursor = conn.cursor()

    # the third character is the failure character
    word_seed = u"ab🐍’«cdefg"

    data = u" ".join(
        "".join(random.choice(word_seed) for j in range(150))
        for i in range(datasize)
    )

    cursor.execute(
        "INSERT INTO long_text (x, y, z) VALUES (:x, :y, :z)",
        {"x": 5, "y": data, "z": 10},
    )

    cursor.execute(
        "SELECT long_text.x, long_text.y, long_text.z FROM long_text"
    )
    row = cursor.fetchone()

    try:
        assert row[1].read() == data
    finally:
        cursor.close()
        conn.rollback()


def harness(datasize, num_runs):
    for i in range(num_runs):
        try:
            run_test(datasize)
        except AssertionError as err:
            print(f"datasize: {datasize} test run {i} failed")
        else:
            print(f"datasize: {datasize} test run {i} succeeded")

setup()

for datasize in (10, 25, 50, 100, 250):
    print(f"\ndatasize: {datasize}")
    harness(datasize, 10)

[anthony-tuininga]

I already tried that...and I don't get any failures that way! I am using 21.3 database, however, so not sure if this is something that may have been fixed? I'll try with an older database a little later and let you know.

[zzzeek]

im seeing it on 18c which is not too old

[anthony-tuininga]

Ok. I tried with a 12.1 database. It works fine, there. I did need to add this code:

cursor.setinputsizes(y=cx_Oracle.DB_TYPE_NVARCHAR)

That's because that database is using a single-byte character set, though. If the data type is NCLOB you should use that setinputsizes() call to avoid the double conversion (and possible data loss when the primary character set is a single-byte character set). For CLOB and BLOB no setinputsizes() call should be needed.

Since you're getting failures, can you show the first place in the string where things are failing?

[zzzeek]

are you able to try with an 18c database? it's the same test case, so I dont know what method you used to narrow down the pattern of characters that doesnt come back, I guess you could trap the error then compare the strings character by character?

[cjbj]

I just started 18c XE for @anthony-tuininga

(Also our doc on setinputsizes needs some love....)

[anthony-tuininga]

I am able to replicate with 18c. I will look into that as well and get back to you!