Oracle.ProgrammingError: positional and named binds cannot be intermixed (==6.4)

[jakereps]

Discovered in unit testing due to no version pinning in the project's Pipfile. Locally (6.3.1) remains functional, while all database set up tasks fail in our CI pipeline on cx_Oracle==6.4.

Binding errors stating that positional and named binds cannot be intermixed when there are no positional binds being used.

Answer the following questions:

1. What is your version of Python? Is it 32-bit or 64-bit?

$ python -V
Python 3.6.5
$ python -c 'import sys; print(sys.maxsize)'
9223372036854775807  # 64bit

2. What is your version of cx_Oracle?

Locally:

$ python -c 'import cx_Oracle; print(cx_Oracle.__version__)'
6.3.1

Remote (issue causing)

$ python -c 'import cx_Oracle; print(cx_Oracle.__version__)'
6.4

3. What is your version of the Oracle client (e.g. Instant Client)? How was it
   installed? Where is it installed?

$ ls /opt/oracle/
instantclient_12_2

4. What is your version of the Oracle Database?

$ docker ps
CONTAINER ID        IMAGE                                            COMMAND                  CREATED             STATUS                    PORTS                                 NAMES
ffe765d011a3        store/oracle/database-enterprise:12.2.0.1-slim   "/bin/sh -c '/bin/ba…"   29 minutes ago      Up 29 minutes (healthy)   5500/tcp, 127.0.0.1:XXXXX->1521/tcp   inspiring_shtern

5. What is your OS and version?

$ uname -a
Darwin Jordens-MacBook-Pro.local 17.6.0 Darwin Kernel Version 17.6.0: Tue May  8 15:22:16 PDT 2018; root:xnu-4570.61.1~1/RELEASE_X86_64 x86_64
$ system_profiler SPSoftwareDataType
Software:

    System Software Overview:

      System Version: macOS 10.13.5 (17F77)
      Kernel Version: Darwin 17.6.0
      Boot Volume: Macintosh HD
      Boot Mode: Normal
      Computer Name: Jorden’s MacBook Pro
      User Name: Jorden Kreps (jordenkreps)
      Secure Virtual Memory: Enabled
      System Integrity Protection: Enabled
      Time since boot: 6 days 1:57

6. What compiler version did you use? For example, with GCC, run
   gcc --version.

$ gcc --version
Configured with: --prefix=/Library/Developer/CommandLineTools/usr --with-gxx-include-dir=/usr/include/c++/4.2.1
Apple LLVM version 9.1.0 (clang-902.0.39.2)
Target: x86_64-apple-darwin17.6.0
Thread model: posix
InstalledDir: /Library/Developer/CommandLineTools/usr/bin

7. What environment variables did you set? How exactly did you set them?

N/A

8. What exact command caused the problem (e.g. what command did you try to
   install with)? Who were you logged in as?

$ pytest --cov <package_name>

9. What error(s) you are seeing?

6.3.1

...
<package_name>/tests/test_util.py ...............                            [100%]
...

6.4

...
<package_name>/tests/test_util.py EEEEEE.........                            [100%]
...

self = <sqlalchemy.dialects.oracle.cx_oracle.OracleDialect_cx_oracle object at 0x7f977ca76550>
cursor = <cx_Oracle.Cursor on <cx_Oracle.Connection to SYSTEM@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=XXXXX))(CONNECT_DATA=(SID=ORCLCDB)(SERVICE_NAME=ORCLPDB1.localdomain)))>>
statement = 'SELECT table_name FROM all_tables WHERE table_name = :name AND owner = :schema_name'
parameters = {'name': 'MYTABLE', 'schema_name': 'MYUSER'}
context = <sqlalchemy.dialects.oracle.cx_oracle.OracleExecutionContext_cx_oracle object at 0x7f977cb570b8>

    def do_execute(self, cursor, statement, parameters, context=None):
>       cursor.execute(statement, parameters)
E       cx_Oracle.ProgrammingError: positional and named binds cannot be intermixed

Pinning to 6.3.1 resolves the issue.

[anthony-tuininga]

Can you provide a test case that fails? Or provide instructions on getting the unit tests you are referencing up and running so I can see what is going on? Thanks!

[jakereps]

For sure, it happens in the setUp portion of the unittest.TestCase, where we are making and destroying tables before and after every test so the data remains at the initial state for each test. Since the tests are erring out at the set up stage, the test code itself isn't even being ran.

Here is essentially our setUp code that recreates the error on cx_Oracle==6.4, but works as expected on ==6.3.1.

import sqlalchemy
import cx_Oracle
from sqlalchemy import Table, Column, MetaData, String

user = "SYSTEM"
password = "Oradoc_db1"

dsn = cx_Oracle.makedsn("127.0.0.1", <DOCKER_ASSIGNED_PORT>, "ORCLCDB", 
                        service_name="ORCLPDB1.localdomain")
connection_str = f"oracle+cx_oracle://{user}:{password}@{dsn}"
engine = sqlalchemy.create_engine(connection_str)

md = MetaData(bind=engine)

schema_name = "sone"
table_name = "table1"

engine.execute(f"CREATE USER {schema_name} IDENTIFIED BY test")
engine.execute(f"ALTER USER {schema_name} QUOTA UNLIMITED ON USERS")

columns = []
for column in ('foo', 'bar', 'baz'):
    columns.append(Column(column.upper(), String(50), nullable=True, 
                          quote=False))

_ = Table(table_name, md, *columns, schema=schema_name, quote=False, 
          quote_schema=False)

md.create_all()

[anthony-tuininga]

Thanks for the test case. That was sufficient to track this issue down. Essentially a bug fix in cx_Oracle 6.4 inadvertently introduced this issue. Calling cursor.setinputsizes() with no parameters at all would make the assumption that you were binding by position, so a subsequent call to cursor.execute() with named arguments would immediately raise this issue. Although it makes no sense to call cursor.setinputsizes() with no parameters, it also shouldn't cause any problems! I'll get this corrected.

[anthony-tuininga]

I have corrected this issue and added a test case that should prevent this issue from happening in the future. I'll wait a while before releasing cx_Oracle 6.4.1, just to ensure there aren't any other issues. Thanks again for reporting this issue.

[jakereps]

Awesome, thanks! No problem on holding out a hot fix, as we’ll just keep an eye out and unpin the version at that time!

[anthony-tuininga]

Re-opening as requested. Will close again once 6.4.1 has been released.

[zzzeek]

yep...i'm getting this too :) can you refer to the patch, and/or did you add tests for this?

[zzzeek]

oh sorry i see it above