MySQL Bugs: #30787: Wrong aliasing with subselects

Bug #30787	Wrong aliasing with subselects
Submitted:	4 Sep 2007 10:19	Modified:	30 Mar 2008 19:41
Reporter:	Ivan Pavlov	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.0.45	OS:	Linux
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any
Tags:	regression

Description:
The result of the folowing query:

SELECT 
(SELECT cm_count(a.site_id)) as cm_count, 
(SELECT tags_list(a.site_id) as `tags`) 
FROM ( SELECT DISTINCT(v.site_id), url, private FROM user_sites v JOIN sites ON sites.id=v.site_id JOIN user_tags t USING ( site_id ) JOIN tags ON t.tag_id = tags.id WHERE name='community' AND private = 0 ORDER BY ts DESC LIMIT 10 ) AS a;

is 

+---------------------+-----------------------------------+
| cm_count(a.site_id) | tags                              |
+---------------------+-----------------------------------+
|                   0 | ,community,Sharing                | 
+---------------------+-----------------------------------+

As you can see the first alias (cm_count) is not understood by mysql. It has to be written as written for the `tags` alias in order to work. In other words, in some cases (which?) I cannot alias a subquery from outside the brackets.

This was not the behavior of other mysql releases (i.e. 5.0.38) where the same query returns the following:

+----------+-----------------------------------------+
| cm_count | (SELECT tags_list(a.site_id) as `tags`) |
+----------+-----------------------------------------+
|        1 | ,community,Sharing                      | 
+----------+-----------------------------------------+

I believe this is the correct behavior per sql specs, and it has been broken in 5.0.45.

How to repeat:
I can provide DDLs to recreate the problem if necessary.

Thank you for the report.

I can not repeat described behaviour with test data:

$mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 610
Server version: 5.0.50-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select 1 as f1, (select 2 as f2);
+----+------------------+
| f1 | (select 2 as f2) |
+----+------------------+
|  1 |                2 | 
+----+------------------+
1 row in set (0.12 sec)

mysql> select 1 as f1, (select 2 as f2) from (select 3) as f3;
+----+------------------+
| f1 | (select 2 as f2) |
+----+------------------+
|  1 |                2 | 
+----+------------------+
1 row in set (0.18 sec)

mysql> 

Please provide DDLs.

Please use the dump I provided and run the following query:

SELECT 
    (SELECT proc(col1)) as greet,
    (SELECT proc(col1) as greet)
FROM test;

Thank you for the feedback.

Verified as described.

test case loadable by our testsuite

Attachment: bug30787.test (application/octet-stream, text), 652 bytes.

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/41379

ChangeSet@1.2593, 2008-01-29 23:21:18+03:00, evgen@moonbone.local +3 -0
  Bug#30787: Stored function ignores user defined alias.
  
  Simple subselects are pulled into upper selects. This is done by substituting
  subselects with first item in the item list of the subselect. If an alias is
  set on the subselect it's set on the substituting item also. As this is done
  after fix_fields phase this alias isn't showed if substituting item is a
  stored function. This happens because the Item_func_sp::make_field function
  makes send field from its result_field and ignores user defined alias.
  
  Now when an alias is defined the Item_func_sp::make_field function sets it on
  the returned field.

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/41382

ChangeSet@1.2593, 2008-01-29 23:55:01+03:00, evgen@moonbone.local +3 -0
  Bug#30787: Stored function ignores user defined alias.
  
  Simple subselects are pulled into upper selects. This operation substitutes the
  pulled subselect for the first item from the select list of the subselect.
  If an alias is defined for a subselect it is inherited by the replacement item.
  As this is done after fix_fields phase this alias isn't showed if the
  replacement item is a stored function. This happens because the Item_func_sp::make_field
  function makes send field from its result_field and ignores the defined alias.
  
  Now when an alias is defined the Item_func_sp::make_field function sets it for
  the returned field.

Pushed into 6.0.5-alpha

Pushed into 5.1.24-rc

Pushed into 5.0.60

Documented in the 5.0.60, 5.1.23-ndb-6.3.11, 5.1.24, and 6.0.5 changelogs as follows:

        If an alias was used to refer to the value returned by a stored function
        within a subselect, the outer select recognized the alias but failed to
        retrieve the value assigned to it in the subselect.