[MDEV-2672] LP:640419 - Wrong result with sort_union/index_merge in maria-5.1 and a large table - JIRA

Details

Type: Bug
Status: Closed
Priority: Major
Resolution: Fixed
Affects Version/s: None
Fix Version/s: None
Component/s: None
Labels:
- Launchpad

Description

The following query:

SELECT * FROM `table100000_innodb_int_autoinc` WHERE ( ( `col_bigint_key` >= 1 ) OR `pk` BETWEEN 8 AND 40 + 64 ) OR ( `pk` BETWEEN 1 AND 1 + 185 AND `col_bigint_key` IS NULL )

Returns a different number of rows when executed with an index_merge/sort_union strategy.

Gliffy Diagrams

Attachments

Options
- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

Attachments

LPexportBug640419_bug640419.diff
6 kB
2010-09-17 08:43
LPexportBug640419_bug640419.zip
307 kB
2010-09-16 12:14
LPexportBug640419_bug640419-2.test
171 kB
2010-09-17 08:42
LPexportBug640419.xml
17 kB
2012-10-03 11:32

Activity

Ascending order - Click to sort in descending order

Hide

Permalink

Philip Stoev added a comment - 2010-09-16 12:14

The test case appears to require at least 10K rows to work. Further simplification was not possible.
Test case
LPexportBug640419_bug640419.zip

Show

Philip Stoev added a comment - 2010-09-16 12:14 The test case appears to require at least 10K rows to work. Further simplification was not possible. Test case LPexportBug640419_bug640419.zip

7 older comments

Hide

Permalink

Philip Stoev added a comment - 2010-09-17 08:43

Re: Wrong result with sort_union/index_merge in maria-5.1 and a large table

Show

Philip Stoev added a comment - 2010-09-17 08:43 Re: Wrong result with sort_union/index_merge in maria-5.1 and a large table

Hide

Permalink

Philip Stoev added a comment - 2010-09-17 08:44

Re: Wrong result with sort_union/index_merge in maria-5.1 and a large table
The procedure to repeat is as follows:

0. Take a 64-bit machine

1. Branch a fresh tree

bzr branch lp:maria/5.1 maria-5.1-bug637962

2. Clear ccache

ccache -C

3. Compile

./BUILD/compile-pentium-debug-max

4. Run.

perl mysql-test-run.pl --record --no-check-testcases t/bug640419-2.test

If successfull, MTR will report

mysqltest: At line 1462: "Bug is repeatable"

Show

Philip Stoev added a comment - 2010-09-17 08:44 Re: Wrong result with sort_union/index_merge in maria-5.1 and a large table The procedure to repeat is as follows: 0. Take a 64-bit machine 1. Branch a fresh tree bzr branch lp:maria/5.1 maria-5.1-bug637962 2. Clear ccache ccache -C 3. Compile ./BUILD/compile-pentium-debug-max 4. Run. perl mysql-test-run.pl --record --no-check-testcases t/bug640419-2.test If successfull, MTR will report mysqltest: At line 1462: "Bug is repeatable"

Hide

Permalink

Igor Babaev added a comment - 2010-09-19 20:11

Re: Wrong result with sort_union/index_merge in maria-5.1 and a large table
The bug can be demonstrated on any platform with the following test case:

CREATE TABLE t1 (
pk int NOT NULL AUTO_INCREMENT PRIMARY KEY,
a int,
b int,
INDEX idx(a))
ENGINE=INNODB;

INSERT INTO t1(a,b) VALUES
(11, 1100), (2, 200), (1, 100), (14, 1400), (5, 500),
(3, 300), (17, 1700), (4, 400), (12, 1200), (8, 800),
(6, 600), (18, 1800), (9, 900), (10, 1000), (7, 700),
(13, 1300), (15, 1500), (19, 1900), (16, 1600), (20, 2000);
INSERT INTO t1(a,b) SELECT a+20, b+2000 FROM t1;
INSERT INTO t1(a,b) SELECT a+40, b+4000 FROM t1;
INSERT INTO t1(a,b) SELECT a+80, b+8000 FROM t1;
INSERT INTO t1(a,b) SELECT a,b FROM t1;
INSERT INTO t1(a,b) SELECT a,b FROM t1;
INSERT INTO t1(a,b) SELECT a,b FROM t1;
INSERT INTO t1(a,b) SELECT a,b FROM t1;
INSERT INTO t1(a,b) SELECT a,b FROM t1;
INSERT INTO t1(a,b) SELECT a,b FROM t1;
INSERT INTO t1(a,b) SELECT a,b FROM t1;
INSERT INTO t1(a,b) SELECT a,b FROM t1;
INSERT INTO t1(a,b) SELECT a,b FROM t1;
INSERT INTO t1(a,b) SELECT a,b FROM t1;
INSERT INTO t1 VALUES (1000000, 0, 0);

SET SESSION sort_buffer_size = 1024*36;

EXPLAIN
SELECT COUNT FROM
(SELECT * FROM t1
WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t;
SELECT COUNT FROM
(SELECT * FROM t1
WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t;

EXPLAIN
SELECT COUNT FROM
(SELECT * FROM t1 IGNORE INDEX(idx)
WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t;
SELECT COUNT FROM
(SELECT * FROM t1 IGNORE INDEX(idx)
WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t;

Running this test case one can see that the execution with index merge returns a wrong result:

MariaDB [test]> EXPLAIN
-> SELECT COUNT FROM
-> (SELECT * FROM t1
-> WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t;
-----------------------------------------------------------------------------------------------------------------+

id

select_type

table

type

possible_keys

key

key_len

ref

rows

Extra

-----------------------------------------------------------------------------------------------------------------+

1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
2	DERIVED	t1	index_merge	PRIMARY,idx	idx,PRIMARY	5,4	NULL	11419	Using sort_union(idx,PRIMARY); Using where

-----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.12 sec)

MariaDB [test]> SELECT COUNT FROM
-> (SELECT * FROM t1
-> WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t;
----------

COUNT

----------

6144

----------
1 row in set (0.12 sec)

MariaDB [test]> EXPLAIN
-> SELECT COUNT FROM
-> (SELECT * FROM t1 IGNORE INDEX(idx)
-> WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t;
--------------------------------------------------------------------------------------+

id

select_type

table

type

possible_keys

key

key_len

ref

rows

Extra

--------------------------------------------------------------------------------------+

1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
2	DERIVED	t1	ALL	PRIMARY	NULL	NULL	NULL	164439	Using where

--------------------------------------------------------------------------------------+
2 rows in set (0.50 sec)

MariaDB [test]> SELECT COUNT FROM
-> (SELECT * FROM t1 IGNORE INDEX(idx)
-> WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t;
----------

COUNT

----------

6145

----------
1 row in set (0.51 sec)

Show

Igor Babaev added a comment - 2010-09-19 20:11 Re: Wrong result with sort_union/index_merge in maria-5.1 and a large table The bug can be demonstrated on any platform with the following test case: CREATE TABLE t1 ( pk int NOT NULL AUTO_INCREMENT PRIMARY KEY, a int, b int, INDEX idx(a)) ENGINE=INNODB; INSERT INTO t1(a,b) VALUES (11, 1100), (2, 200), (1, 100), (14, 1400), (5, 500), (3, 300), (17, 1700), (4, 400), (12, 1200), (8, 800), (6, 600), (18, 1800), (9, 900), (10, 1000), (7, 700), (13, 1300), (15, 1500), (19, 1900), (16, 1600), (20, 2000); INSERT INTO t1(a,b) SELECT a+20, b+2000 FROM t1; INSERT INTO t1(a,b) SELECT a+40, b+4000 FROM t1; INSERT INTO t1(a,b) SELECT a+80, b+8000 FROM t1; INSERT INTO t1(a,b) SELECT a,b FROM t1; INSERT INTO t1(a,b) SELECT a,b FROM t1; INSERT INTO t1(a,b) SELECT a,b FROM t1; INSERT INTO t1(a,b) SELECT a,b FROM t1; INSERT INTO t1(a,b) SELECT a,b FROM t1; INSERT INTO t1(a,b) SELECT a,b FROM t1; INSERT INTO t1(a,b) SELECT a,b FROM t1; INSERT INTO t1(a,b) SELECT a,b FROM t1; INSERT INTO t1(a,b) SELECT a,b FROM t1; INSERT INTO t1(a,b) SELECT a,b FROM t1; INSERT INTO t1 VALUES (1000000, 0, 0); SET SESSION sort_buffer_size = 1024*36; EXPLAIN SELECT COUNT FROM (SELECT * FROM t1 WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t; SELECT COUNT FROM (SELECT * FROM t1 WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t; EXPLAIN SELECT COUNT FROM (SELECT * FROM t1 IGNORE INDEX(idx) WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t; SELECT COUNT FROM (SELECT * FROM t1 IGNORE INDEX(idx) WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t; Running this test case one can see that the execution with index merge returns a wrong result: MariaDB [test] > EXPLAIN -> SELECT COUNT FROM -> (SELECT * FROM t1 -> WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t; --- ----------- ----- ----------- ------------- ----------- ------- ---- ----- -------------------------------------------+ id select_type table type possible_keys key key_len ref rows Extra --- ----------- ----- ----------- ------------- ----------- ------- ---- ----- -------------------------------------------+ 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away 2 DERIVED t1 index_merge PRIMARY,idx idx,PRIMARY 5,4 NULL 11419 Using sort_union(idx,PRIMARY); Using where --- ----------- ----- ----------- ------------- ----------- ------- ---- ----- -------------------------------------------+ 2 rows in set (0.12 sec) MariaDB [test] > SELECT COUNT FROM -> (SELECT * FROM t1 -> WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t; ---------- COUNT ---------- 6144 ---------- 1 row in set (0.12 sec) MariaDB [test] > EXPLAIN -> SELECT COUNT FROM -> (SELECT * FROM t1 IGNORE INDEX(idx) -> WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t; --- ----------- ----- ---- ------------- ---- ------- ---- ------ -----------------------------+ id select_type table type possible_keys key key_len ref rows Extra --- ----------- ----- ---- ------------- ---- ------- ---- ------ -----------------------------+ 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away 2 DERIVED t1 ALL PRIMARY NULL NULL NULL 164439 Using where --- ----------- ----- ---- ------------- ---- ------- ---- ------ -----------------------------+ 2 rows in set (0.50 sec) MariaDB [test] > SELECT COUNT FROM -> (SELECT * FROM t1 IGNORE INDEX(idx) -> WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t; ---------- COUNT ---------- 6145 ---------- 1 row in set (0.51 sec)

Hide

Permalink

Igor Babaev added a comment - 2010-09-19 21:04

Re: Wrong result with sort_union/index_merge in maria-5.1 and a large table
The bug has just been reported for mysql-5.1 as bug #56862

Show

Igor Babaev added a comment - 2010-09-19 21:04 Re: Wrong result with sort_union/index_merge in maria-5.1 and a large table The bug has just been reported for mysql-5.1 as bug #56862

Hide

Permalink

Rasmus Johansson added a comment - 2011-12-13 15:29

Launchpad bug id: 640419

Show

Rasmus Johansson added a comment - 2011-12-13 15:29 Launchpad bug id: 640419

LP:640419 - Wrong result with sort_union/index_merge in maria-5.1 and a large table

Details

Description

Gliffy Diagrams

Attachments

Attachments

Activity

People

Dates

Agile