SQL Developer suggesting I add aggregate to group by clause

oracleoracle-sql-developer

This isn't actually a bug, just an annoyance that I would like some explanation for. I have tried my Google-Fu, but I am lacking in skill today apparently.

I am using Oracle SQL Developer 4.1.5.21 Build MAIN-21.78
I used to use a really old version from 2010 (can't remember the version number), and it never made this suggestion, which is what has me stumped.

Essentially, I have the following query:

  SELECT
          STDT."Student_ID"
        , STDT."Study_Package_Code"
        , STDT."Availability_Year"
        , STDT.semester_agg AS "Semester"
        , MIN(STDT."Application_Date") AS APP_DATE
  FROM INT_COMMENCING_APPS STDT
  WHERE 1=1
  AND STDT."Availability_Year" >= 2017
  GROUP BY STDT."Student_ID"
        , STDT."Study_Package_Code"
        , STDT."Availability_Year"
        , STDT.semester_agg

It runs beautifully, no errors, and returns the expected data. HOWEVER, oracle developer tells me I'm stupid and wrong and should go die…okay maybe not that bad. But it does give me a query hint telling me I am wrong:

SELECT list inconsistent with GROUP BY;
amend GROUP BY clause to:
          STDT."Student_ID"
        , STDT."Study_Package_Code"
        , STDT."Availability_Year"
        , STDT.semester_agg
        , MIN(STDT."Application_Date")

Notice how it's telling me to add the aggregated MIN function to my GROUP BY clause, what gives? I have been writing SQL for years now and this is the first time I've ever been told to do such a thing…frankly, I'm ignoring Oracle for now, because it's being a jerk and can't tell me politely why. That being said, I would like an explanation from the community if anyone has one. 🙂

Thanks!

Edit 09/12/16

I just noticed that there is a pattern to this query suggestion. What I didn't mention above was that the above query was inside a CTE! Because…why the hell would that matter, but apparently it does.

Outside a CTE, Oracle SQL Developer doesn't care that the aggregate isn't in the group by clause (which it shouldn't regardless). However, as soon as the query is inside a CTE, BAM, it gets all hormonal and tells me it wants chocolate…I mean it tells me to add the aggregate to the group by clause.

Example (and it doesn't matter if I reference the CTE in the query):

WITH APPDT AS (
  SELECT /*+ materialize */
          STDT."Student_ID"
        , STDT."Study_Package_Code"
        , STDT."Availability_Year"
        , STDT.semester_agg AS "Semester"
        , MIN(STDT."Application_Date") AS APP_DATE--<=this aggregate
        --SQL Developer tells me to add the aggregate to the
        --GROUP BY clause below
  FROM INT_COMMENCING_APPS STDT
  WHERE 1=1
  AND STDT."Availability_Year" >= 2017
  GROUP BY STDT."Student_ID"
        , STDT."Study_Package_Code"
        , STDT."Availability_Year"
        , STDT.semester_agg
)

  SELECT
          STDT."Student_ID"
        , STDT."Study_Package_Code"
        , STDT."Availability_Year"
        , STDT.semester_agg AS "Semester"
        , MIN(STDT."Application_Date") AS APP_DATE--<=this aggregate
        --SQL Developer doesn't tell me to add the aggregate
        --to the GROUP BY clause below
  FROM INT_COMMENCING_APPS STDT
  WHERE 1=1
  AND STDT."Availability_Year" >= 2017
  GROUP BY STDT."Student_ID"
        , STDT."Study_Package_Code"
        , STDT."Availability_Year"
        , STDT.semester_agg

Best Answer

Looks like a bug in (the parser of) the specific version of SQL Developer (4.1.5.21) you use.

Update to the most recent version and if it shows the same behaviour, send a bug report to Oracle.

You could try to find a smaller example (something like the below, if it does exhibit the same buggy suggestions):

CREATE TABLE t
  ( a INT NOT NULL, 
    b DATE NOT NULL
  ) ;

-- no suggestions
SELECT /*+ materialize */ a, MIN(b) AS min_b
FROM t
GROUP BY a ;

-- buggy suggestions
WITH ct AS
  ( SELECT /*+ materialize */ a, MIN(b) AS min_b
    FROM t
    GROUP BY a 
  ) 
SELECT * 
FROM ct ;
Related Question