| 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: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 5.0.18-nt-log, also 5.0.17, 4.1.9 | OS: | Windows (Windows 2000, Linux) |
| Assigned to: | Evgeny Potemkin | CPU Architecture: | Any |
[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.
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;