`SELECT ''` returns `None`

[cpcloud]

1. What versions are you using?

Database version: We're using the gvenzl/oracle-free docker image version 23.3-slim.

Python script output:

In [1]:     import sys
   ...:     import platform
   ...:
   ...:     print("platform.platform:", platform.platform())
   ...:     print("sys.maxsize > 2**32:", sys.maxsize > 2**32)
   ...:     print("platform.python_version:", platform.python_version())
platform.platform: Linux-6.1.77-x86_64-with-glibc2.38
sys.maxsize > 2**32: True
platform.python_version: 3.10.13

In [2]:     import oracledb
   ...:     print("oracledb.__version__:", oracledb.__version__)
oracledb.__version__: 2.0.1

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

Error

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

In [10]: import oracledb

In [11]: dsn = oracledb.makedsn(host="localhost", port=1521, service_name="IBIS_TESTING")

In [12]: dsn
Out[12]: '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=IBIS_TESTING)))'

In [13]: con = oracledb.connect(dsn, user="ibis", password="ibis", stmtcachesize=0)

In [14]: cur = con.cursor()

In [15]: cur.execute("select ''").fetchall()
Out[15]: [(None,)]

I would expect the output to be the empty string.

6. Does your application call init_oracle_client()?

Not that I am aware of.

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

Copy paste from above:

In [10]: import oracledb

In [11]: dsn = oracledb.makedsn(host="localhost", port=1521, service_name="IBIS_TESTING")

In [12]: dsn
Out[12]: '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=IBIS_TESTING)))'

In [13]: con = oracledb.connect(dsn, user="ibis", password="ibis", stmtcachesize=0)

In [14]: cur = con.cursor()

In [15]: cur.execute("select ''").fetchall()
Out[15]: [(None,)]

[anthony-tuininga]

This is expected. Oracle Database treats the empty string and null as the same value. Many people have complained about it but since it has been this way for decades there is very little chance of it changing in the future!

[cpcloud]

Wow, I didn't know that, thanks for the heads up.