patternMinor
SQL Developer suggesting I add aggregate to group by clause
Viewed 0 times
groupsqlsuggestingdeveloperclauseaggregateadd
Problem
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:
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:
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 (whi
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_aggIt 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 (whi
Solution
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):
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 ;Code Snippets
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 ;Context
StackExchange Database Administrators Q#157430, answer score: 3
Revisions (0)
No revisions yet.