oracle / oracle/python-oracledb
Thin mode only: "ORA-00979: not a GROUP BY expression"
Nobody has claimed this yet.
- Dominant language
- Python
- Stars
- 451
- Forks
- 119
- PR merge metrics
- No merged PRs in 30d
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)
Contributor guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
Start with query.py and reproduce the CASE WHEN, GROUP BY, bind-variable, and COUNT(DISTINCT) query in Thin mode, then compare it with Thick mode. Trace the Thin execution path through src/oracledb/impl/thin/cursor.pyx, protocol.pyx, and messages.pyx; done means the query works in Thin mode without ORA-00979 and matches Thick-mode behavior.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- python, sql
- Domain
- backend-api-design, databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 45/100