Bug #31797 error while parsing subqueries -- WHERE is parsed as HAVING
Submitted: 23 Oct 2007 18:58 Modified: 25 Jan 2008 19:28
Reporter: Maciej Pilichowski Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Errors Severity:S3 (Non-critical)
Version:5.0.45 OS:Any
Assigned to: Martin Hansson CPU Architecture:Any
Triage: D4 (Minor) / R3 (Medium) / E2 (Low)

[23 Oct 2007 18:58] Maciej Pilichowski
Description:
Don't look if this query is valid just look if it has HAVING clause:

select max(C) MC,(select T from B IB where IB.C=MC) from B;

And now check the error message:

ERROR 1463 (42000): non-grouping field 'MC' is used in HAVING clause

But the query does not have any HAVING clause!

How to repeat:
Just run or read the query.

Suggested fix:
Correct parsing and error messages.
[23 Oct 2007 21:03] Sveta Smirnova
Thank you for the report.

Please provide output of SHOW CREATE TABLE B;
[24 Oct 2007 7:49] Maciej Pilichowski
CREATE TABLE `B` (
  `T` varchar(100) collate utf8_polish_ci default NULL,
  `C` decimal(9,2) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci
[24 Oct 2007 9:57] Hartmut Holzgraefe
I'm getting 

  ERROR 1247 (42S22): Reference 'MC' not supported (reference to group function)

instead which is correct ...

mysql> CREATE TABLE `B` (
    ->   `T` varchar(100) collate utf8_polish_ci default NULL,
    ->   `C` decimal(9,2) default NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;
Query OK, 0 rows affected (0.50 sec)

mysql> select max(C) MC,(select T from B IB where IB.C=MC) from B;
ERROR 1247 (42S22): Reference 'MC' not supported (reference to group function)
[24 Oct 2007 10:09] Sveta Smirnova
Thank you for the feedback.

I get same correct results as Hartmut. Please indicate accurate version of package you use (file name), operating system and provide your configuration file.
[24 Oct 2007 10:23] Maciej Pilichowski
This is strange. This table is an excerpt from the bigger one I use, but the other fields are irrelevant here.

Anyway, data you asked for.

MySQL-client-community-5.0.45-0.sles10
mysql-administrator-5.0r12-1suse10
MySQL-server-community-5.0.45-0.sles10
mysql-migration-toolkit-5.0r12-1suse10
MySQL-devel-community-5.0.45-0.sles10
mysql-gui-tools-5.0r12-1suse10
MySQL-shared-community-5.0.45-0.sles10
mysql-query-browser-5.0r12-1suse10

And configuration file:
[mysql]
default-character-set = latin2

[mysqld]

character-set-server = utf8
collation-server = utf8_polish_ci
sql-mode=ONLY_FULL_GROUP_BY,PIPES_AS_CONCAT,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO
max_sp_recursion_depth = 255
[24 Oct 2007 10:50] Sveta Smirnova
Thank you for the provided information.

Cause is ONLY_FULL_GROUP_BY:

mysql> select max(C) MC,(select T from B IB where IB.C=MC) from B;
ERROR 1247 (42S22): Reference 'MC' not supported (reference to group function)

mysql> set @@sql_mode='ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.00 sec)

mysql> select max(C) MC,(select T from B IB where IB.C=MC) from B;
ERROR 1463 (42000): non-grouping field 'MC' is used in HAVING clause
[4 Jan 2008 12:37] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/40555

ChangeSet@1.2546, 2008-01-04 13:41:11+01:00, mhansson@linux-st28.site +3 -0
  Bug#31797: error while parsing subqueries -- WHERE is parsed as HAVING
  
  The name resolution for correlated subqueries and HAVING clauses
  failed to distinguish which of two was being performed when there 
  was a reference to an outer aliased field.
  Fixed by adding the condition that HAVING clause name resulotion
  is being performed.
[11 Jan 2008 11:55] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/40905

ChangeSet@1.2546, 2008-01-11 12:55:04+01:00, mhansson@client-10-129-10-216.upp.off.mysql.com +3 -0
  Bug#31797: error while parsing subqueries -- WHERE is parsed as HAVING
  
  The name resolution for correlated subqueries and HAVING clauses
  failed to distinguish which of two was being performed when there 
  was a reference to an outer aliased field.
  Fixed by adding the condition that HAVING clause name resulotion
  is being performed.
[11 Jan 2008 15:48] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/40913

ChangeSet@1.2546, 2008-01-11 16:48:33+01:00, mhansson@client-10-129-10-216.mysql.com +3 -0
  Bug#31797: error while parsing subqueries -- WHERE is parsed as HAVING
  
  The name resolution for correlated subqueries and HAVING clauses
  failed to distinguish which of two was being performed when there 
  was a reference to an outer aliased field.
  Fixed by adding the condition that HAVING clause name resulotion
  is being performed.
[11 Jan 2008 17:51] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/40927

ChangeSet@1.2546, 2008-01-11 18:50:54+01:00, mhansson@linux-st28.site +3 -0
  Bug#31797: error while parsing subqueries -- WHERE is parsed as HAVING
  
  The name resolution for correlated subqueries and HAVING clauses
  failed to distinguish which of two was being performed when there 
  was a reference to an outer aliased field.
  Fixed by adding the condition that HAVING clause name resulotion
  is being performed.
[24 Jan 2008 11:56] Bugs System
Pushed into 6.0.5-alpha
[24 Jan 2008 12:01] Bugs System
Pushed into 5.1.24-rc
[24 Jan 2008 12:02] Bugs System
Pushed into 5.0.56
[25 Jan 2008 19:28] Paul Dubois
Noted in 5.0.56, 5.1.23, 6.0.5 changelogs.

Name resolution for correlated subqueries and HAVING clauses failed
to distinguish which of two was being performed when there was a
reference to an outer aliased field. This could result in error
messages about a HAVING clause for queries that had no such clause.