Bug #48665 sql-bench's insert test fails due to wrong result
Submitted: 10 Nov 2009 11:49 Modified: 13 Nov 2010 17:08
Reporter: Olav Sandstå Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.1.41, 5.5.0, 6.0.14 OS:Any
Assigned to: Georgi Kodinov CPU Architecture:Any
Triage: Triaged: D2 (Serious)

[10 Nov 2009 11:49] Olav Sandstå
Description:
When running sql-bench's insert test it fails with the following error:

Test of compares with simple ranges
'select * from bench1 where (id>=1 and id<=2 or id>=4 and id<=5) or (id>=0 and id <=10)' returned wrong number of rows: 12 instead of 11

How to repeat:
1. Use the latest source from the mysql-6.0-codebase-bugfixing branch.

2. Start the server using MTR:

    ./mysql-test-run.pl --start --mem

3. Start the test client:

    a. cd sql-bench
    b. ./test-insert --server=mysql --fast --log --user=root --hires --socket=/home/olav/..../var/tmp/mysqld.1.sock
[10 Nov 2009 12:47] Miguel Solorzano
Thank you for the bug report. Which source tree mysql-6.0?. Thanks in advance.
[11 Nov 2009 12:03] Olav Sandstå
The test failure occurs for me using the mysql-6.0-codebase-bugfixing tree. The revision I used is from November 3rd 2009.
[13 Nov 2009 1:06] Miguel Solorzano
Thank you for the bug report. Verified as described:

Time for select_key_return_key (200000): 219 wallclock secs (42.74 usr  6.41 sys +  0.00 cusr  0.00 csys = 49.15 CPU)
Time for select_key2 (200000): 299 wallclock secs (45.31 usr  5.90 sys +  0.00 cusr  0.00 csys = 51.21 CPU)
Time for select_key2_return_key (200000): 289 wallclock secs (49.86 usr  7.13 sys +  0.00 cusr  0.00 csys = 56.99 CPU)
Time for select_key2_return_prim (200000): 300 wallclock secs (46.83 usr  6.44 sys +  0.00 cusr  0.00 csys = 53.27 CPU)

Test of compares with simple ranges
'select * from bench1 where (id>=1 and id<=2 or id>=4 and id<=5) or (id>=0 and id <=10)' returned wrong number of rows: 12 instead of 11
miguel@quetzal3:~/bzr/mysql-6.0-codebase-bugfixing/sql-bench$
[13 Nov 2009 22:07] Alexey Stroganov
This test works ok for 5.1.40 but fails for 5.1.41.

Server version: 5.1.40-enterprise-commercial-pro MySQL Enterprise Server - Pro Edition (Commercial)

mysql> select * from bench1 where  (id>=1 and id<=2 or id>=4 and id<=5) or (id>=0 and id <=10) ;
+----+-----+-----+------------+
| id | id2 | id3 | dummy1     |
+----+-----+-----+------------+
|  0 |   0 |   0 | ABCDEFGHIJ |
|  1 |   1 |   1 | ABCDEFGHIJ |
|  2 |   2 |   2 | ABCDEFGHIJ |
|  3 |   3 |   3 | ABCDEFGHIJ |
|  4 |   4 |   4 | ABCDEFGHIJ |
|  5 |   5 |   5 | ABCDEFGHIJ |
|  6 |   6 |   6 | ABCDEFGHIJ |
|  7 |   7 |   7 | ABCDEFGHIJ |
|  8 |   8 |   8 | ABCDEFGHIJ |
|  9 |   9 |   9 | ABCDEFGHIJ |
| 10 |  10 |  10 | ABCDEFGHIJ |
+----+-----+-----+------------+
11 rows in set (0.00 sec)

mysql> explain select * from bench1 where  (id>=1 and id<=2 or id>=4 and id<=5) or (id>=0 and id <=10) ;
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | bench1 | range | PRIMARY       | PRIMARY | 4       | NULL |   11 | Using where |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

Server version: 5.1.41-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

mysql> select * from bench1 where  (id>=1 and id<=2 or id>=4 and id<=5) or (id>=0 and id <=10) ;
+----+-----+-----+------------+
| id | id2 | id3 | dummy1     |
+----+-----+-----+------------+
|  0 |   0 |   0 | ABCDEFGHIJ |
|  0 |   0 |   0 | ABCDEFGHIJ |
|  1 |   1 |   1 | ABCDEFGHIJ |
|  2 |   2 |   2 | ABCDEFGHIJ |
|  3 |   3 |   3 | ABCDEFGHIJ |
|  4 |   4 |   4 | ABCDEFGHIJ |
|  5 |   5 |   5 | ABCDEFGHIJ |
|  6 |   6 |   6 | ABCDEFGHIJ |
|  7 |   7 |   7 | ABCDEFGHIJ |
|  8 |   8 |   8 | ABCDEFGHIJ |
|  9 |   9 |   9 | ABCDEFGHIJ |
| 10 |  10 |  10 | ABCDEFGHIJ |
+----+-----+-----+------------+
12 rows in set (0.00 sec)

mysql> explain select * from bench1 where  (id>=1 and id<=2 or id>=4 and id<=5) or (id>=0 and id <=10) ;
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | bench1 | range | PRIMARY       | PRIMARY | 4       | NULL |   12 | Using where |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

I've attached simplified dump file with 60 rows that will help easily to reproduce issue.
[13 Nov 2009 22:08] Alexey Stroganov
Dump file for the issue

Attachment: bench1.sql (application/octet-stream, text), 3.05 KiB.

[16 Nov 2009 8:54] Georgi Kodinov
What is the correct result ? the one returned by 5.1.41 (12 rows) or the one returned in 5.1.40 (11 rows) ? 
Looking at the script provided by Alexey the new result seems to be the correct one.
[16 Nov 2009 12:03] Georgi Kodinov
Sorry, was reading it wrong. 5.1.41 is the one returning 2 0 rows.
[17 Nov 2009 10:16] Georgi Kodinov
This is a regression from the fix of bug #42846.
[17 Nov 2009 15:49] 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/90692

3201 Georgi Kodinov	2009-11-17
      Bug #48665: sql-bench's insert test fails due to wrong result
      
      When merging ranges during calculation of the result of OR
      to two range sets the current range may be obsoleted by the 
      resulting merged range.
      The first overlapping range can be obsoleted as well.
      
      Fixed by moving the pointer to the first overlapping range to the
      pointer of the resulting union range.
      Added few comments at key places in key_or().
[19 Nov 2009 16:26] 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/90979

3201 Georgi Kodinov	2009-11-19
      Bug #48665: sql-bench's insert test fails due to wrong result
      
      When merging ranges during calculation of the result of OR
      to two range sets the current range may be obsoleted by the 
      resulting merged range.
      The first overlapping range can be obsoleted as well.
      
      Fixed by moving the pointer to the first overlapping range to the
      pointer of the resulting union range.
      Added few comments at key places in key_or().
[20 Nov 2009 12:43] 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/91066

3209 Georgi Kodinov	2009-11-19
      Bug #48665: sql-bench's insert test fails due to wrong result
      
      When merging ranges during calculation of the result of OR
      to two range sets the current range may be obsoleted by the 
      resulting merged range.
      The first overlapping range can be obsoleted as well.
      
      Fixed by moving the pointer to the first overlapping range to the
      pointer of the resulting union range.
      Added few comments at key places in key_or().
[20 Nov 2009 12:54] 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/91078

3208 Georgi Kodinov	2009-11-19
      Bug #48665: sql-bench's insert test fails due to wrong result
      
      When merging ranges during calculation of the result of OR
      to two range sets the current range may be obsoleted by the 
      resulting merged range.
      The first overlapping range can be obsoleted as well.
      
      Fixed by moving the pointer to the first overlapping range to the
      pointer of the resulting union range.
      Added few comments at key places in key_or().
[2 Dec 2009 8:03] Bugs System
Pushed into 5.1.42 (revid:joro@sun.com-20091202080033-mndu4sxwx19lz2zs) (version source revid:davi.arnaut@sun.com-20091125130912-d7hrln14ef7y5d7i) (merge vers: 5.1.42) (pib:13)
[10 Dec 2009 2:01] Paul Dubois
Noted in 5.1.42 changelog.

During query execution, ranges could be merged incorrectly for OR
operations and return an incorrect result.

Setting report to NDI pending push to 5.5.x+.
[16 Dec 2009 8:38] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091216083311-xorsasf5kopjxshf) (version source revid:alik@sun.com-20091214191830-wznm8245ku8xo702) (merge vers: 6.0.14-alpha) (pib:14)
[16 Dec 2009 8:45] Bugs System
Pushed into 5.5.0-beta (revid:alik@sun.com-20091216082430-s0gtzibcgkv4pqul) (version source revid:alexey.kopytov@sun.com-20091124083136-iqm136jm31sfdwg3) (merge vers: 5.5.0-beta) (pib:14)
[16 Dec 2009 8:51] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20091216083231-rp8ecpnvkkbhtb27) (version source revid:alik@sun.com-20091212203859-fx4rx5uab47wwuzd) (merge vers: 5.6.0-beta) (pib:14)
[18 Dec 2009 2:06] Paul Dubois
Noted in 5.5.1, 6.0.14 changelogs.
[25 Feb 2010 19:47] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100225194420-p60r4u90tszj8q2x) (version source revid:olav.sandstaa@sun.com-20100222142955-2rxum4ls3icj95ti) (merge vers: 6.0.14-alpha) (pib:16)
[25 Feb 2010 19:54] Paul Dubois
Setting report to Need Merge pending push of Celosia to release tree.
[11 Mar 2010 11:21] Valeriy Kravchuk
Bug #51946 was marked as a duplicate of this one.
[12 Mar 2010 14:11] Bugs System
Pushed into 5.1.44-ndb-7.0.14 (revid:jonas@mysql.com-20100312135944-t0z8s1da2orvl66x) (version source revid:jonas@mysql.com-20100312115609-woou0te4a6s4ae9y) (merge vers: 5.1.44-ndb-7.0.14) (pib:16)
[12 Mar 2010 14:27] Bugs System
Pushed into 5.1.44-ndb-6.2.19 (revid:jonas@mysql.com-20100312134846-tuqhd9w3tv4xgl3d) (version source revid:jonas@mysql.com-20100312060623-mx6407w2vx76h3by) (merge vers: 5.1.44-ndb-6.2.19) (pib:16)
[12 Mar 2010 14:41] Bugs System
Pushed into 5.1.44-ndb-6.3.33 (revid:jonas@mysql.com-20100312135724-xcw8vw2lu3mijrhn) (version source revid:jonas@mysql.com-20100312103652-snkltsd197l7q2yg) (merge vers: 5.1.44-ndb-6.3.33) (pib:16)
[12 Mar 2010 16:53] Paul Dubois
Fixed in earlier 5.1.x, 5.5.x.
[16 Aug 2010 6:34] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100816062819-bluwgdq8q4xysmlg) (version source revid:alik@sun.com-20100816062612-enatdwnv809iw3s9) (pib:20)
[13 Nov 2010 16:18] Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (merge vers: 5.6.99-m4) (pib:21)