Thin mode only: "ORA-00979: not a GROUP BY expression"

[neykov]

1. What versions are you using?

DB version: 19.3.0

platform.platform: macOS-14.6.1-arm64-arm-64bit
(but same on platform.platform: Linux-5.15.0-1057-aws-x86_64-with-glibc2.35)
sys.maxsize > 2**32: True
platform.python_version: 3.11.7

oracledb.__version__: 2.5.0

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

Error.

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

I am seeing the error "ORA-00979: not a GROUP BY expression" when in thin mode, but the same program works in Thick mode. Seems to be triggered by the interaction of 1) Thin mode 2) CASE WHEN in GROUP BY 3) Bind variables 4) COUNT(DISTINCT).

To reproduce save the script in (7) to the file query.py and run:

python query.py

Traceback (most recent call last):
  File "query.py", line 20, in <module>
    for r in cursor.execute(sql, params):
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "venv/lib/python3.11/site-packages/oracledb/cursor.py", line 710, in execute
    impl.execute(self)
  File "src/oracledb/impl/thin/cursor.pyx", line 196, in oracledb.thin_impl.ThinCursorImpl.execute
  File "src/oracledb/impl/thin/protocol.pyx", line 440, in oracledb.thin_impl.Protocol._process_single_message
  File "src/oracledb/impl/thin/protocol.pyx", line 441, in oracledb.thin_impl.Protocol._process_single_message
  File "src/oracledb/impl/thin/protocol.pyx", line 433, in oracledb.thin_impl.Protocol._process_message
  File "src/oracledb/impl/thin/messages.pyx", line 74, in oracledb.thin_impl.Message._check_and_raise_exception
oracledb.exceptions.DatabaseError: ORA-00979: not a GROUP BY expression
Help: https://docs.oracle.com/error-help/db/ora-00979/

6. Does your application call init_oracle_client()?

No. Thin mode. The error does not trigger in Thick mode.

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

sql = """
SELECT 
CASE WHEN "DUAL"."DUMMY" IS NOT NULL THEN :arg0 ELSE NULL END,
COUNT(DISTINCT "DUAL"."DUMMY")
FROM "DUAL" 
GROUP BY CASE WHEN "DUAL"."DUMMY" IS NOT NULL THEN :arg0 ELSE NULL END
"""
params = {":arg0": "arg0"}

import oracledb

un = "user"
pw = "pass"
dsn = "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SID=ORCLCDB)))"

# Failure only in Thin mode. The program runs fine in Thick mode.
# oracledb.init_oracle_client()

with oracledb.connect(user=un, password=pw, dsn=dsn) as connection:
    with connection.cursor() as cursor:
        for r in cursor.execute(sql, params):
            print(r)

[anthony-tuininga]

I can replicate this issue and have asked internally about it. With Oracle Database 23c you get more information in the error message:

ORA-00979: "$vm_col_1": must appear in the GROUP BY clause or be used in an aggregate function
Help: https://docs.oracle.com/error-help/db/ora-00979/

Since $vm_col_1 doesn't appear in the statement something must be going haywire internally. Hopefully we can figure out the source of the issue and get this repaired. Thanks for reporting it!