[MDEV-13091] "Reference not supported" with nested GROUP BY's on the same table and column Created: 2017-06-14  Updated: 2017-08-29  Resolved: 2017-08-29

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.2.5, 10.2.6, 10.2
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Estructure Assignee: Unassigned
Resolution: Duplicate Votes: 0
Labels: need_feedback
Environment:

Windows 7, x64

Attachments: File mariadb_bug_test.sql    
Issue Links:
Duplicate
is duplicated by MDEV-12845 subquery with group by not using index Closed

 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).

 Comments   
Comment by Alice Sherepa [ 2017-06-14 ]

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 |
 
+--------+

Comment by Estructure [ 2017-06-14 ]

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

Comment by Elena Stepanova [ 2017-06-25 ]

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?

Comment by Elena Stepanova [ 2017-07-31 ]

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

Comment by Elena Stepanova [ 2017-08-29 ]

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

Generated at Thu Feb 08 08:02:50 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.