MySQL Bugs: #35478: sort_union() returns bad data when sort_buffer

Bug #35478	sort_union() returns bad data when sort_buffer_size is hit
Submitted:	21 Mar 2008 11:21	Modified:	28 Jul 2008 15:14
Reporter:	Domas Mituzas	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.0-bk	OS:	Any
Assigned to:	Sergey Petrunya	CPU Architecture:	Any

Description:
Offspring from Bug#35477, same data structure, queries, etc, different problem:

How to repeat:
mysql>  select * from jtest where a like '5%' or b like '5%' order by c limit 10 ;
+-------+------+------+
| a     | b    | c    |
+-------+------+------+
| 4001  | 5    | 1    | 
| 5001  | 6    | 1    | 
| 49001 | 50   | 1    | 
| 50001 | 51   | 1    | 
| 51001 | 52   | 1    | 
| 52001 | 53   | 1    | 
| 53001 | 54   | 1    | 
| 54001 | 55   | 1    | 
| 55001 | 56   | 1    | 
| 56001 | 57   | 1    | 
+-------+------+------+
10 rows in set (0.19 sec)

mysql> set sort_buffer_size=100000;
Query OK, 0 rows affected (0.00 sec)

mysql>  select * from jtest where a like '5%' or b like '5%' order by c limit 10 ;
+-------+------+------+
| a     | b    | c    |
+-------+------+------+
| 4213  | 5    | 213  | 
| 5212  | 6    | 212  | 
| 49221 | 50   | 221  | 
| 50220 | 51   | 220  | 
| 51219 | 52   | 219  | 
| 52218 | 53   | 218  | 
| 53217 | 54   | 217  | 
| 54216 | 55   | 216  | 
| 55215 | 56   | 215  | 
| 56214 | 57   | 214  | 
+-------+------+------+
10 rows in set (0.15 sec)

Suggested fix:
return valid data :-)

unassigning

The returned rows are actually present in the table, and match the WHERE clause. The problem is that these are not the first 10 records which "ORDER BY c LIMIT 10" tells mysqld to return.

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

ChangeSet@1.2602, 2008-04-21 08:14:30+04:00, sergefp@mysql.com +11 -0
  BUG#35478: sort_union() returns bad data when sort_buffer_size is hit
  - In QUICK_INDEX_MERGE_SELECT::read_keys_and_merge: when we got table->sort from Unique,
    tell init_read_record() not to use rr_from_cache() because a) rowids are already sorted
    and b) it might be that the the data is used by filesort(), which will need record rowids
    (which rr_from_cache() cannot provide).
  - Fully de-initialize the table->sort read in QUICK_INDEX_MERGE_SELECT::get_next(). This fixes BUG#35477.
  (bk trigger: file as fix for BUG#35478).

why is this stalled?

Shane, it is not tagged, so the only place I can push it is 6.0. My attempt to convince the triage team to tag this bug was not successful.

Dear developers, triage team, and anyone else evaluating this bug. 
Lower sort_buffer_size was chosen just to make testcase easier.

This bug is very evil with large sort_buffer_sizes as well. 
Whenever dataset doesn't fit in sort buffer, in case of sort_union() memory/fds are leaked. 

As not everyone has 1MB-sized 'test' databases, but also run production, this bug should be taken care off.

This _must_ go to 5.0.

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

2648 Sergey Petrunia	2008-07-15
      BUG#35478: sort_union() returns bad data when sort_buffer_size is hit
      - In QUICK_INDEX_MERGE_SELECT::read_keys_and_merge: when we got table->sort from Unique,
        tell init_read_record() not to use rr_from_cache() because a) rowids are already sorted
        and b) it might be that the the data is used by filesort(), which will need record rowids
        (which rr_from_cache() cannot provide).
      - Fully de-initialize the table->sort read in QUICK_INDEX_MERGE_SELECT::get_next(). This fixes BUG#35477.
      (bk trigger: file as fix for BUG#35478).

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

2699 Sergey Petrunia	2008-07-15 [merge]
      Merge fix for BUG#35478 into 5.1

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

2701 Sergey Petrunia	2008-07-17 [merge]
      Merge BUG#35478 into mysql-5.1-bugteam

Pushed into {5.0,5.1,6.0}-bugteam

Pushed into 5.1.28

Pushed into 5.1.28

Pushed into 5.0.68

Pushed into 5.0.68

Noted in 5.0.68, 5.1.28 changelogs.

Index scans performed with the sort_union() access method returned
bad data when the sort_buffer_size limit was reached.

Setting report to Patch queued pending push of fix into 6.0.x.

Pushed into 5.0.68  (revid:kpettersson@mysql.com-20080716141220-83aobe5fdgd9ye5l) (pib:2)
(Retry automatic marking, to ensure nothing is missed. cm01)

Pushed into 6.0.7-alpha  (revid:alik@mysql.com-20080725172155-fnc73o50e4tgl23k) (version source revid:alik@mysql.com-20080725172155-fnc73o50e4tgl23k) (pib:3)

Noted in 6.0.7 changelog.

Pushed into 5.1.28  (revid:davi.arnaut@sun.com-20080722182431-0i2f1yc4uocime9q) (version source revid:davi.arnaut@sun.com-20080722182431-0i2f1yc4uocime9q) (pib:3)

Pushed into 6.0.7-alpha  (revid:sergefp@mysql.com-20080715174602-852vg6b80ygdalwa) (version source revid:vvaintroub@mysql.com-20080804094710-jb2qpqxpf2ir2gf3) (pib:3)