4

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
  • 2
    @thatjeffsmith will be able to explain the processes behind the recommendations that SQL Developer makes – Philᵀᴹ Dec 6 '16 at 23:58
  • 1
    It might just be a bug in their parser that doesn't recognise MIN as an aggregate function (or has issue with the case sensitive column names). As @Philᵀᴹ said, thatjeffsmith can probably help. – JeromeFr Dec 7 '16 at 22:02
  • 1
    from your comment, "because it's being a jerk and can't tell me politely why", jerks are rarely polite. Having said that, feel free to ignore that recommendation and run your query. If DB executes the query and gives you the right results, then you are good. You can also use Oracle support site to log a bug against this, that would actually be better. – Raj Dec 8 '16 at 13:04
  • 2
    looks like the suggestion is wrong - go get 4.2, if its' wrong there, share your findings on the forums and maybe we can improve the advice going forward – thatjeffsmith Dec 12 '16 at 15:12
  • I will take another look at this in the new year with the update. On holiday at the moment. Will keep you posted when I get back. – Nexmilitis Dec 21 '16 at 2:33
2

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 ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.