`cursor.rowcount` is higher than the number of rows returned by `cursor.fetchall`

[agrvz]

Hello,

1. What versions are you using?

oracle database: 19c (19.12.0.0.0)
platform.platform: Windows-10-10.0.19043-SP0
sys.maxsize > 2**32: True
platform.python_version: 3.8.10
oracledb.__version__: 1.2.2

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

Unexpected behaviour.

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

I originally posted here with more detail: https://stackoverflow.com/questions/75487587/why-is-cursor-rowcount-higher-than-the-number-of-rows-returned-by-cursor-fetchal

When calling cursor.fetchall() (or cursor.fetchmany() in batches), the cursor.rowcount attribute is higher than the actual number of rows returned. However, this issue does not appear when using the same methods in cx_Oracle.

For example, when selecting all records from a table with 93 rows, cursor.rowcount is 96, whereas I would expect it to be 93 to match the number of rows in the table.

Notes on output (see output section below):

1. After the first batch, rowcount is already 20 - I would have expected it to be 10 here

2. After batch 8, rowcount is 100 - it has 'overshot' the actual table row count - which seems to indicate rowcount does not necessarily reflect the number of rows actually fetched, but the number of rows attempted to be fetched, at least initially

3. After the final batch (and also after the fetchall() call), rowcount is 96 - I would expect it to be 93 here

   • From testing with other values for arraysize, it seems to follow the pattern r = t + (t mod a) where r = rowcount, t = total table rows, and a = arraysize
   • To illustrate, another example on the same table using arraysize = 20:

     After execute() call: cursor.rowcount=0
     After fetchall() call: cursor.rowcount=106

4. Does your application call init_oracle_client()?

No.

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

import oracledb

SQL = "select id from example_table"

un = 'abc'
pw = 'def'
cs = 'ghi'

with oracledb.connect(user=un, password=pw, dsn=cs) as connection:
    with connection.cursor() as cursor:
        cursor.prefetchrows = 0
        cursor.arraysize = 10
        cursor.execute(SQL)
        print(f"After execute() call: {cursor.rowcount=}")

        batch = 0
        while True:
            rows = cursor.fetchmany()
            if not rows:
                break
            print(f"Batch {batch}: {cursor.rowcount=}")
            batch += 1

        rows = cursor.fetchall()
        print(f"After fetchall() call: {cursor.rowcount=}")

SQL

-- Create a table of ids from 1 to 93
create table example_table as

select
  rownum as id
from
  dual
connect by rownum <= 93

Output

After execute() call: cursor.rowcount=0
Batch 0: cursor.rowcount=20
Batch 1: cursor.rowcount=30
Batch 2: cursor.rowcount=40
Batch 3: cursor.rowcount=50
Batch 4: cursor.rowcount=60
Batch 5: cursor.rowcount=70
Batch 6: cursor.rowcount=80
Batch 7: cursor.rowcount=90
Batch 8: cursor.rowcount=100
Batch 9: cursor.rowcount=96
After fetchall() call: cursor.rowcount=96

[anthony-tuininga]

Thanks for the report. I'll take a look and get back to you on this!

[anthony-tuininga]

I have pushed a patch that should correct this issue and added a relevant test case. If you are able to build from source you can verify that it corrects your issue as well.

[anthony-tuininga]

This has been included in version 1.3.0 which was just released.

[agrvz]

Just tested using 1.3.0 and working perfectly now. Thank you for fixing this issue, much appreciated!