oracle / oracle/python-oracledb

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

Open
#422 1 comment 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

bug Client Library or Database
Dominant language
Python
Stars
451
Forks
119
PR merge metrics
No merged PRs in 30d

Description

  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
  1. Is it an error or a hang or a crash?

Error.

  1. 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/
  1. Does your application call init_oracle_client()?

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

  1. 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

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.