Bug #16716 constant subselect - incorrect result on sequential rows
Submitted: 23 Jan 2006 0:39 Modified: 7 Jul 2006 20:36
Reporter: Dave Pullin (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.0.18-nt-log, also 5.0.17, 4.1.9 OS:Microsoft Windows (Windows 2000, Linux)
Assigned to: Evgeny Potemkin CPU Architecture:Any

[23 Jan 2006 0:39] Dave Pullin
Description:
Concatenating the results ('a' and 'b') of two constant subselects in consecutive rows of a resultset repeats the second value n times in row n. (ie ab, abb, abbb, abbbb, etc).

Looks like some buffer isn't being re-initialized. 

(Also, I would have expected the constant expression to have been optimized out, not recomputed for each row.).

Console log:
mysql> select concat(
    ->   (select x from (select 'a' as x) as s1 )
    ->  ,(select y from (select 'b' as y) as s2 )
    -> ) from (select 1 union select 2 ) as s3;
+-------------------------------------------------------------------------------
------------------+
| concat(
  (select x from (select 'a' as x) as s1 )
 ,(select y from (select 'b' as y) as s2 )
) |
+-------------------------------------------------------------------------------
------------------+
| ab
                  |
| abb
                  |
+-------------------------------------------------------------------------------
------------------+
2 rows in set (0.00 sec)

How to repeat:

select concat(
  (select x from (select 'a' as x) as s1 )
 ,(select y from (select 'b' as y) as s2 )
) from (select 1 union select 2 ) as s3;
[3 May 2006 22:34] Hartmut Holzgraefe
The statement is self-contained, see the constant subqueries in the from clauses of the subqueries, no extra schema definition needed ...
[25 May 2006 21:24] 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/6892
[19 Jun 2006 0:12] Evgeny Potemkin
Fixed in 4.1.21, 5.0.23, 5.1.12
[20 Jun 2006 10:58] Evgeny Potemkin
The Item_func_concat::val_str() function tries to make as less re-allocations
as possible. This results in appending strings returned by 2nd and next
arguments to the string returned by 1st argument if the buffer for the first
argument has enough free space. A constant subselect is evaluated only once 
and its result is stored in an Item_cache_str. In the case when the first
argument of the concat() function is such a subselect Item_cache_str returns
the stored value and Item_func_concat::val_str() append values of other
arguments to it. But for the next row the value in the Item_cache_str isn't
restored because the subselect is a constant one and it isn't evaluated second
time. This results in appending string values of 2nd and next arguments to the 
result of the previous Item_func_concat::val_str() call.
[7 Jul 2006 20:36] Mike Hillyer
Documented in 4.1.21, 5.0.23 and 5.1.12 changelogs.