3 Replies Latest reply: Feb 3, 2014 8:21 AM by PaoloM RSS

    Incorrect warning when parsing query with group by clause

    JamHan

      I am using SQL Developer 4.0.0.13.80 with JDK 1.7.0_51 x64 on Windows 8.1

       

      I have the following SQL, which works perfectly:

       

      select substr(to_char(tot_amount), 0, 1) as digit, count(*)
      from transactions
      where tot_amount <> 0
      group by substr(to_char(tot_amount), 0, 1)
      order by digit;
      

       

      However, SQL Developer is yellow-underlining the first line, telling me that:

       

      SELECT list is inconsistent with GROUP BY; amend GROUP BY clause to: substr(to_char(rep_tot_amount), 0, 1), substr(to_char(rep_tot_amount),
      

       

      which is clearly wrong.

       

      Message was edited by: JamHan Added code formatting.

        • 1. Re: Incorrect warning when parsing query with group by clause
          PaoloM

          Hello,

          I also have found the same issue with the GROUP BY hint. Another problem I found is that the hint suggests to amend the GROUP BY members to add also constant values that are included in the SELECTed columns and whenever those constants include strings with spaces, it generates an invalid query. Normally, constant values won't affect grouping functions and as such they can be omitted from the GROUP BY members, but it seems SQL Dev thinks it differently.


          For example, if you try the following query:


          SELECT d.DNAME, sum(e.sal) amt, 'Total salary' report_title, 100 report_nr
          FROM scott.emp e, scott.dept d
          WHERE e.DEPTNO = d.DEPTNO
          GROUP BY d.DNAME;
          
          

           

          when you hover the mouse pointer on the yellow hint, a popup will show the following message:

           

          SELECT list inconsistent with GROUP BY; amend GROUP BY clause to:

          d.DNAME, 'Total, 100

           

          If you click on the hint, it will amend the group by members to become:

           

          GROUP BY d.DNAME, 'Total, 100;

           

          that is clearly incorrect syntax. You may notice that after the change the yellow hint is still there, suggesting to amend further the GROUP BY members. If you click again on the hint, you will end with the following:

           

          GROUP BY d.DNAME, 'Total, 100;

          , 'Total, 100

           

          and so on.

           

          I am not sure if this behaviour was already known (Vadim??), but it would be nice if somebody could file a bug against it.

           

          Finally when writing big queries with complex functions and constant columns, those yellow lines extend all over the select list and they are visually annoying, so I wonder if there is a way to disable the GROUP BY hint until it gets fixed.

           

          Thanks for any suggestion,

          Paolo

          • 2. Re: Incorrect warning when parsing query with group by clause
            Jeff Smith Sqldev Pm-Oracle

            You can tweak the yellow lines to be different colors, boldness, or style of underline in the preferences, OR you can disable them via Tools > Preferences > Code Editor > Enable Semantic Analysis Info Tip

            • 3. Re: Incorrect warning when parsing query with group by clause
              PaoloM

              Thanks Jeff,

              it works. Please note that you have to close and reopen the existing worksheets to completely disable the "Enable Semantic Analysis Info Tip".

               

              Paolo