MySQL Bugs: #31797: error while parsing subqueries -- WHERE is parsed as HAVING

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

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.

Thank you for the report.

Please provide output of SHOW CREATE TABLE B;

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

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)

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.

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

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

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.

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.

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.

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.

Pushed into 6.0.5-alpha

Pushed into 5.1.24-rc

Pushed into 5.0.56

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.