Open
Description
- 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
- Is it an error or a hang or a crash?
Error.
- 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/
- Does your application call init_oracle_client()?
No. Thin mode.
The error does not trigger in Thick mode.
- 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)