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
Triage: D2 (Serious)

[4 Sep 2007 10:19] Ivan Pavlov
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.
[4 Sep 2007 10:36] Sveta Smirnova
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.
[4 Sep 2007 10:59] Ivan Pavlov
Please use the dump I provided and run the following query:

SELECT 
    (SELECT proc(col1)) as greet,
    (SELECT proc(col1) as greet)
FROM test;
[4 Sep 2007 11:17] Sveta Smirnova
Thank you for the feedback.

Verified as described.
[4 Sep 2007 11:17] Sveta Smirnova
test case loadable by our testsuite

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

[29 Jan 2008 20:28] 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/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.
[29 Jan 2008 21:02] 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/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.
[13 Mar 2008 19:28] Bugs System
Pushed into 6.0.5-alpha
[13 Mar 2008 19:35] Bugs System
Pushed into 5.1.24-rc
[13 Mar 2008 19:43] Bugs System
Pushed into 5.0.60
[30 Mar 2008 19:41] Jon Stephens
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.