Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-13091

"Reference not supported" with nested GROUP BY's on the same table and column

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Duplicate
    • 10.2.5, 10.2.6, 10.2(EOL)
    • N/A
    • Optimizer
    • Windows 7, x64

    Description

      I had a very complex query (it was quite a complex view which used another pretty complex view).
      It worked as expected in 10.1.*. I have tried it in 10.2.5 and 10.2.6 and throws the following error:
      "Reference '[FIELD NAME]' not supported (forward reference in item list)".

      I'm attaching the smallest example I have found which reproduces the issue (it's a useless construct for any other purpose).

      Attachments

        Issue Links

          is duplicated by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-12845 subquery with group by not using index

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            alice Alice Sherepa added a comment -

            I confirm it on 10.3.0 Ubuntu 16.04. There is no error, if there is no index on "i" column or group by is not used. 

            CREATE table  a ( i INT);
            		 
            INSERT INTO a VALUES (1),(2);
            		 
             
            		 
            SELECT (
            		 
                SELECT i FROM (
            		 
                	SELECT i 
            	 	FROM a
            		 
            		GROUP BY i) AS a1
            		 
                	WHERE a1.i = a2.i) AS result
            		 
            	FROM a AS a2
            		 
            WHERE a2.i = 1
            		 
            GROUP BY a2.i;
            		 
            --------------
            		 
             
            		 
            +--------+
            		 
            | result |
            		 
            +--------+
            		 
            |      1 |
            		 
            +--------+

            alice Alice Sherepa added a comment - I confirm it on 10.3.0 Ubuntu 16.04. There is no error, if there is no index on "i" column or group by is not used. CREATE table a ( i INT ); INSERT INTO a VALUES (1),(2);   SELECT ( SELECT i FROM ( SELECT i FROM a GROUP BY i) AS a1 WHERE a1.i = a2.i) AS result FROM a AS a2 WHERE a2.i = 1 GROUP BY a2.i; --------------   + --------+ | result | + --------+ | 1 | + --------+
            Estructure Estructure added a comment -

            There is no error either without any one of the two WHERE clauses (or both).

            Estructure Estructure added a comment - There is no error either without any one of the two WHERE clauses (or both).
            elenst Elena Stepanova added a comment -

            Estructure, alice, thank you for reporting and confirming. It looks like this particular problem was fixed in the scope of MDEV-12845, by this commit:

            commit 9f3622191df074d9f4e512320effe86f06b250fb
            		 
            Author: Igor Babaev <igor@askmonty.org>
            		 
            Date:   Thu Jun 22 00:41:44 2017 -0700
            		 
             
            		 
                Fixed the bug mdev-12845.

            It will be released in 10.2.7.

            However, since it obviously depended on the execution plan (hence the effect of the key, as Alice noted), it's also possible that the execution plan has changed and this is the reason why the problem is not visible anymore.

            Estructure, would you be willing to check your original complicated query on the current 10.2 build, before 10.2.7 is released, or do you prefer to wait for the release?

            elenst Elena Stepanova added a comment - Estructure , alice , thank you for reporting and confirming. It looks like this particular problem was fixed in the scope of MDEV-12845 , by this commit: commit 9f3622191df074d9f4e512320effe86f06b250fb Author: Igor Babaev <igor@askmonty.org> Date: Thu Jun 22 00:41:44 2017 -0700   Fixed the bug mdev-12845. It will be released in 10.2.7. However, since it obviously depended on the execution plan (hence the effect of the key, as Alice noted), it's also possible that the execution plan has changed and this is the reason why the problem is not visible anymore. Estructure , would you be willing to check your original complicated query on the current 10.2 build, before 10.2.7 is released, or do you prefer to wait for the release?
            elenst Elena Stepanova added a comment -

            Estructure, did you have a chance to upgrade? Are you still experiencing the problem?

            elenst Elena Stepanova added a comment - Estructure , did you have a chance to upgrade? Are you still experiencing the problem?
            elenst Elena Stepanova added a comment -

            For now I will consider it a duplicate of MDEV-12845. Please comment if it turns out that upgrade did not help.

            elenst Elena Stepanova added a comment - For now I will consider it a duplicate of MDEV-12845 . Please comment if it turns out that upgrade did not help.

            People

              Unassigned Unassigned
              Estructure Estructure
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.