Skip to content

Commit a2bb846

Browse files
author
Mattias Jonsson
committed
Bug#18167648: WRONG RESULTS WITH PARTITIONING,
INDEX_MERGE AND NO PK Index merge intersect/union expects rowid-ordered-retrieval, sorted like handler::cmp_ref compares records. I.e. after sorting by KEY it should also be sorted by handler::ref (rowid). The partitioning engine only sorts by KEY, unless clustered primary key exists, when it also sorts by PK in case the KEY values are compares as equal. This means that for InnoDB tables without primary key or any other engine, which not clusters by primary key, index merge intersect/union can return wrong results. The fix is to also sort by handler::ref if KEY values compares as equal. To avoid extra sorting when not needed (where rowid-ordered-retrieval is not needed, like ordinary secondary key scanning) ha_extra(HA_EXTRA_SECONDARY_SORT_ROWID) is added for index merge intersect/union.
1 parent e12c309 commit a2bb846

File tree

9 files changed

+621
-28
lines changed

9 files changed

+621
-28
lines changed

‎include/my_base.h‎

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -204,7 +204,9 @@ enum ha_extra_function {
204204
Prepare table for export
205205
(e.g. quiesce the table and write table metadata).
206206
*/
207-
HA_EXTRA_EXPORT
207+
HA_EXTRA_EXPORT,
208+
/** Do secondary sort by handler::ref (rowid) after key sort. */
209+
HA_EXTRA_SECONDARY_SORT_ROWID
208210
};
209211

210212
/* Compatible option, to be deleted in 6.0 */

‎include/queues.h‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -44,6 +44,7 @@ typedef struct st_queue {
4444
#define queue_element(queue,index) ((queue)->root[index+1])
4545
#define queue_end(queue) ((queue)->root[(queue)->elements])
4646
#define queue_replaced(queue) _downheap(queue,1)
47+
#define queue_set_compare(queue, cmp) (queue)->compare= cmp
4748
#define queue_set_cmp_arg(queue, set_arg) (queue)->first_cmp_arg= set_arg
4849
#define queue_set_max_at_top(queue, set_arg) \
4950
(queue)->max_at_top= set_arg ? -1 : 1

‎mysql-test/r/partition_index_innodb.result‎

Lines changed: 110 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,110 @@
1+
#
2+
# Bug#18167648: WRONG RESULTS WITH PARTITIONING, INDEX_MERGE AND NO PK
3+
#
4+
CREATE TABLE t1
5+
(a smallint,
6+
b smallint,
7+
c smallint,
8+
KEY a (a),
9+
KEY b (b)
10+
) ENGINE=InnoDB
11+
PARTITION BY HASH(c) PARTITIONS 3;
12+
# c will simulate DB_ROW_ID + force to chosen partition.
13+
# c is unique so we can identify rows in the test.
14+
# Fillers to make the optimizer choose index_merge_intersect/union:
15+
INSERT INTO t1 VALUES (1,1,1), (1,1,1+3), (1,1,1+6), (1,1,1+9);
16+
# row N..N+3
17+
INSERT INTO t1 VALUES (1,2,1+12), (2,2,2+15), (2,2,2+18), (1,2,3+21);
18+
# More index matching rows for index_merge_intersect: N+4, N+5
19+
INSERT INTO t1 VALUES (2,2,1+24);
20+
INSERT INTO t1 VALUES (2,1,1+27);
21+
ANALYZE TABLE t1;
22+
Table Op Msg_type Msg_text
23+
test.t1 analyze status OK
24+
SET @old_opt_switch = @@session.optimizer_switch;
25+
SET SESSION optimizer_switch="index_merge=on";
26+
SET SESSION optimizer_switch="index_merge_intersection=on";
27+
SET SESSION optimizer_switch="index_merge_sort_union=off";
28+
SET SESSION optimizer_switch="index_merge_union=off";
29+
EXPLAIN SELECT a,b,c FROM t1 WHERE b = 2 AND a = 2 AND c > 0 AND c < 100;
30+
id select_type table type possible_keys key key_len ref rows Extra
31+
1 SIMPLE t1 index_merge a,b a,b 3,3 NULL 1 Using intersect(a,b); Using where
32+
SELECT a,b,c FROM t1 WHERE b = 2 AND a = 2 AND c > 0 AND c < 100;
33+
a b c
34+
2 2 17
35+
2 2 20
36+
2 2 25
37+
SET SESSION optimizer_switch="index_merge_intersection=off";
38+
SELECT a,b,c FROM t1 WHERE b = 2 AND a = 2 AND c > 0 AND c < 100;
39+
a b c
40+
2 2 25
41+
2 2 17
42+
2 2 20
43+
EXPLAIN SELECT a,b,c FROM t1 WHERE b = 2 AND a = 2 AND c > 0 AND c < 100;
44+
id select_type table type possible_keys key key_len ref rows Extra
45+
1 SIMPLE t1 ref a,b a 3 const 2 Using where
46+
# Adding more fillers to get index_merge_union
47+
INSERT INTO t1 VALUES (1,1,1+30), (1,1,1+33), (1,1,1+36), (1,1,1+39),
48+
(1,1,1+42), (1,1,1+45), (1,1,1+48), (1,1,1+51), (1,1,1+54), (1,1,1+57),
49+
(1,1,1+60), (1,1,1+63), (1,1,1+66), (1,1,1+69), (1,1,1+72), (1,1,1+75),
50+
(1,1,1+78);
51+
ANALYZE TABLE t1;
52+
Table Op Msg_type Msg_text
53+
test.t1 analyze status OK
54+
SET SESSION optimizer_switch="index_merge_union=on";
55+
EXPLAIN SELECT a,b,c FROM t1 WHERE (b = 2 OR a = 2) AND c > 0 AND c < 100;
56+
id select_type table type possible_keys key key_len ref rows Extra
57+
1 SIMPLE t1 index_merge a,b b,a 3,3 NULL 4 Using union(b,a); Using where
58+
SELECT a,b,c FROM t1 WHERE (b = 2 OR a = 2) AND c > 0 AND c < 100;
59+
a b c
60+
1 2 13
61+
2 2 17
62+
2 2 20
63+
1 2 24
64+
2 2 25
65+
2 1 28
66+
SET SESSION optimizer_switch="index_merge_union=off";
67+
SELECT a,b,c FROM t1 WHERE (b = 2 OR a = 2) AND c > 0 AND c < 100;
68+
a b c
69+
1 2 24
70+
1 2 13
71+
2 2 25
72+
2 1 28
73+
2 2 17
74+
2 2 20
75+
EXPLAIN SELECT a,b,c FROM t1 WHERE (b = 2 OR a = 2) AND c > 0 AND c < 100;
76+
id select_type table type possible_keys key key_len ref rows Extra
77+
1 SIMPLE t1 ALL a,b NULL NULL NULL 27 Using where
78+
Insert a few more rows to trigger sort_union
79+
INSERT INTO t1 VALUES (1,1,1+81), (1,1,1+84), (1,1,1+87), (1,1,1+90),
80+
(1,1,1+93);
81+
ANALYZE TABLE t1;
82+
Table Op Msg_type Msg_text
83+
test.t1 analyze status OK
84+
SET SESSION optimizer_switch="index_merge_sort_union=on";
85+
EXPLAIN SELECT a,b,c FROM t1 WHERE (b >= 2 OR a >= 2) AND c > 0 AND c < 100;
86+
id select_type table type possible_keys key key_len ref rows Extra
87+
1 SIMPLE t1 index_merge a,b b,a 3,3 NULL 4 Using sort_union(b,a); Using where
88+
# Not affected, added for completeness...
89+
SELECT a,b,c FROM t1 WHERE (b >= 2 OR a >= 2) AND c > 0 AND c < 100;
90+
a b c
91+
1 2 13
92+
2 2 17
93+
2 2 20
94+
1 2 24
95+
2 2 25
96+
2 1 28
97+
SET SESSION optimizer_switch="index_merge_sort_union=off";
98+
SELECT a,b,c FROM t1 WHERE (b >= 2 OR a >= 2) AND c > 0 AND c < 100;
99+
a b c
100+
1 2 24
101+
1 2 13
102+
2 2 25
103+
2 1 28
104+
2 2 17
105+
2 2 20
106+
EXPLAIN SELECT a,b,c FROM t1 WHERE (b >= 2 OR a >= 2) AND c > 0 AND c < 100;
107+
id select_type table type possible_keys key key_len ref rows Extra
108+
1 SIMPLE t1 ALL a,b NULL NULL NULL 32 Using where
109+
SET @@session.optimizer_switch = @old_opt_switch;
110+
DROP TABLE t1;

‎mysql-test/r/partition_index_myisam.result‎

Lines changed: 83 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,83 @@
1+
CREATE TABLE t1 (
2+
a smallint,
3+
b smallint,
4+
c smallint,
5+
KEY a (a),
6+
KEY b (b)
7+
) ENGINE = MyISAM
8+
PARTITION BY HASH (c) PARTITIONS 3;
9+
CREATE TABLE t2 (a tinyint) ENGINE = MyISAM;
10+
INSERT INTO t2 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),
11+
(14),(15),(16);
12+
SET SESSION optimizer_switch="index_merge=on";
13+
SET SESSION optimizer_switch="index_merge_intersection=on";
14+
SET SESSION optimizer_switch="index_merge_union=off";
15+
SET SESSION optimizer_switch="index_merge_sort_union=off";
16+
INSERT INTO t1 VALUES (1,1,0), (1,1,0), (2,1,0), (2,2,1), (1,1,1), (2,2,4);
17+
# Add some rows to make the index_merge_intersect possible
18+
INSERT INTO t1 SELECT 1,1,0 FROM t2 A, t2 B;
19+
INSERT INTO t1 SELECT 1,1,1 FROM t2 A, t2 B;
20+
INSERT INTO t1 SELECT 1,1,2 FROM t2 A, t2 B LIMIT 68;
21+
SELECT COUNT(*) FROM t1;
22+
COUNT(*)
23+
586
24+
ANALYZE TABLE t1;
25+
Table Op Msg_type Msg_text
26+
test.t1 analyze status OK
27+
EXPLAIN SELECT a,b,c FROM t1 WHERE b = 2 AND a = 2;
28+
id select_type table type possible_keys key key_len ref rows Extra
29+
1 SIMPLE t1 index_merge a,b b,a 3,3 NULL 1 Using intersect(b,a); Using where
30+
# Before fix:
31+
# (p0 - partition 0, R3 - 3rd record in that partition = offset)
32+
# Make 'a' read p0-R3, p1-R1, p1-R3
33+
# Make 'b' read p1-R1, p1-R3
34+
# 'b' will skip p1-R1 since R3 is bigger than R1.
35+
SELECT a,b,c FROM t1 WHERE b = 2 AND a = 2;
36+
a b c
37+
2 2 1
38+
2 2 4
39+
SET @old_opt_switch = @@session.optimizer_switch;
40+
SET SESSION optimizer_switch="index_merge_intersection=off";
41+
# Without index_merge_intersection
42+
SELECT a,b,c FROM t1 WHERE b = 2 AND a = 2;
43+
a b c
44+
2 2 1
45+
2 2 4
46+
EXPLAIN SELECT a,b,c FROM t1 WHERE b = 2 AND a = 2;
47+
id select_type table type possible_keys key key_len ref rows Extra
48+
1 SIMPLE t1 ref a,b b 3 const 2 Using where
49+
SET SESSION optimizer_switch="index_merge_union=on";
50+
EXPLAIN SELECT a,b,c FROM t1 WHERE (b = 2 OR a = 2) AND c > 0 AND c < 100;
51+
id select_type table type possible_keys key key_len ref rows Extra
52+
1 SIMPLE t1 index_merge a,b b,a 3,3 NULL 5 Using union(b,a); Using where
53+
SELECT a,b,c FROM t1 WHERE (b = 2 OR a = 2) AND c > 0 AND c < 100;
54+
a b c
55+
2 2 1
56+
2 2 4
57+
SET SESSION optimizer_switch="index_merge_union=off";
58+
SELECT a,b,c FROM t1 WHERE (b = 2 OR a = 2) AND c > 0 AND c < 100;
59+
a b c
60+
2 2 1
61+
2 2 4
62+
EXPLAIN SELECT a,b,c FROM t1 WHERE (b = 2 OR a = 2) AND c > 0 AND c < 100;
63+
id select_type table type possible_keys key key_len ref rows Extra
64+
1 SIMPLE t1 ALL a,b NULL NULL NULL 586 Using where
65+
SET SESSION optimizer_switch="index_merge_sort_union=on";
66+
EXPLAIN SELECT a,b,c FROM t1 WHERE (b >= 2 OR a >= 2) AND c > 0 AND c < 100;
67+
id select_type table type possible_keys key key_len ref rows Extra
68+
1 SIMPLE t1 index_merge a,b b,a 3,3 NULL 10 Using sort_union(b,a); Using where
69+
# Not affected, added for completeness...
70+
SELECT a,b,c FROM t1 WHERE (b >= 2 OR a >= 2) AND c > 0 AND c < 100;
71+
a b c
72+
2 2 1
73+
2 2 4
74+
SET SESSION optimizer_switch="index_merge_sort_union=off";
75+
SELECT a,b,c FROM t1 WHERE (b >= 2 OR a >= 2) AND c > 0 AND c < 100;
76+
a b c
77+
2 2 1
78+
2 2 4
79+
EXPLAIN SELECT a,b,c FROM t1 WHERE (b >= 2 OR a >= 2) AND c > 0 AND c < 100;
80+
id select_type table type possible_keys key key_len ref rows Extra
81+
1 SIMPLE t1 ALL a,b NULL NULL NULL 586 Using where
82+
SET @@session.optimizer_switch = @old_opt_switch;
83+
DROP TABLE t1, t2;

‎mysql-test/t/partition_index_innodb.test‎

Lines changed: 141 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,141 @@
1+
--echo #
2+
--echo # Bug#18167648: WRONG RESULTS WITH PARTITIONING, INDEX_MERGE AND NO PK
3+
--echo #
4+
CREATE TABLE t1
5+
(a smallint,
6+
b smallint,
7+
c smallint,
8+
KEY a (a),
9+
KEY b (b)
10+
) ENGINE=InnoDB
11+
PARTITION BY HASH(c) PARTITIONS 3;
12+
13+
--echo # c will simulate DB_ROW_ID + force to chosen partition.
14+
--echo # c is unique so we can identify rows in the test.
15+
--echo # Fillers to make the optimizer choose index_merge_intersect/union:
16+
INSERT INTO t1 VALUES (1,1,1), (1,1,1+3), (1,1,1+6), (1,1,1+9);
17+
--echo # row N..N+3
18+
INSERT INTO t1 VALUES (1,2,1+12), (2,2,2+15), (2,2,2+18), (1,2,3+21);
19+
--echo # More index matching rows for index_merge_intersect: N+4, N+5
20+
INSERT INTO t1 VALUES (2,2,1+24);
21+
INSERT INTO t1 VALUES (2,1,1+27);
22+
ANALYZE TABLE t1;
23+
24+
SET @old_opt_switch = @@session.optimizer_switch;
25+
SET SESSION optimizer_switch="index_merge=on";
26+
SET SESSION optimizer_switch="index_merge_intersection=on";
27+
SET SESSION optimizer_switch="index_merge_sort_union=off";
28+
SET SESSION optimizer_switch="index_merge_union=off";
29+
if ($use_optimizer_trace)
30+
{
31+
SET SESSION optimizer_trace="enabled=on";
32+
}
33+
EXPLAIN SELECT a,b,c FROM t1 WHERE b = 2 AND a = 2 AND c > 0 AND c < 100;
34+
if ($use_optimizer_trace)
35+
{
36+
SELECT TRACE FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
37+
}
38+
# Before fix:
39+
# Index start of scan (ha_partition::handle_ordered_index_scan)
40+
# 0xNNN is the DB_ROW_ID, kind of internal global auto increment.
41+
# a reads row N+4 (0x208) from p1 c = 25
42+
# a reads row N+1 (0x205) from p2 c = 17
43+
# a returns row N+4, c = 25 (All same, first insert, no cmp of ref/DB_ROW_ID!)
44+
# b reads row N+3 (0x207) from p0 c = 24
45+
# b reads row N (0x204) from p1 c = 13
46+
# b reads row N+1 (0x205) from p2 c = 17
47+
# b returns row N+3, c = 24 (All same, first insert, no cmp of ref/DB_ROW_ID!)
48+
# b skips to next (0x207 < 0x208)
49+
# b finds no more rows in p0.
50+
# b returns row from p2 (0x205) c = 17
51+
# b skips to next row (0x205 < 0x208)
52+
# b reads row N+2 (0x206) from p2 c = 20
53+
# b skips to next row (0x206 < 0x208)
54+
# b finds no more rows in p2.
55+
# b return row from p1 (0x204) c = 13
56+
# b skips to next row (0x204 < 0x208)
57+
# b reads row N+4 (0x208) from p1 c = 25
58+
# a == b Match found!
59+
# Fetches whole row for 0x208 (really p1 + 0x208) -> (2,2,25)
60+
# Continues with next a:
61+
# a reads row N+5 (0x209) from p1 c = 28
62+
# No more match...
63+
64+
SELECT a,b,c FROM t1 WHERE b = 2 AND a = 2 AND c > 0 AND c < 100;
65+
SET SESSION optimizer_switch="index_merge_intersection=off";
66+
SELECT a,b,c FROM t1 WHERE b = 2 AND a = 2 AND c > 0 AND c < 100;
67+
EXPLAIN SELECT a,b,c FROM t1 WHERE b = 2 AND a = 2 AND c > 0 AND c < 100;
68+
69+
--echo # Adding more fillers to get index_merge_union
70+
INSERT INTO t1 VALUES (1,1,1+30), (1,1,1+33), (1,1,1+36), (1,1,1+39),
71+
(1,1,1+42), (1,1,1+45), (1,1,1+48), (1,1,1+51), (1,1,1+54), (1,1,1+57),
72+
(1,1,1+60), (1,1,1+63), (1,1,1+66), (1,1,1+69), (1,1,1+72), (1,1,1+75),
73+
(1,1,1+78);
74+
ANALYZE TABLE t1;
75+
SET SESSION optimizer_switch="index_merge_union=on";
76+
EXPLAIN SELECT a,b,c FROM t1 WHERE (b = 2 OR a = 2) AND c > 0 AND c < 100;
77+
if ($use_optimizer_trace)
78+
{
79+
SELECT TRACE FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
80+
}
81+
# First there where a bug where index_init() was called with sorted=false :(
82+
# Before fix of secondary sort in ha_partition:
83+
# b reads row N+3 (0x207) from p0 c = 24
84+
# b reads row N (0x204) from p1 c = 13
85+
# b reads row N+1 (0x205) from p2 c = 17
86+
# b returns row N+3, c = 24 (All same, first insert, no cmp of ref/DB_ROW_ID!)
87+
# a reads row N+4 (0x208) from p1 c = 25
88+
# a reads row N+1 (0x205) from p2 c = 17
89+
# a returns row N+4, c = 25 (All same, first insert, no cmp of ref/DB_ROW_ID!)
90+
91+
# first row from both indexes done, first is b, 0x207 c = 24
92+
# Get next from b
93+
# b finds no more rows in p0.
94+
# b returns row from p2 (0x205) c = 17
95+
# Fetching whole row for (0x207) c = 24 (1,2,24) and sends it
96+
# b reads row N+2 (0x206) from p2 c = 20
97+
# b returns row from p2 (0x206) c = 20
98+
# No duplicate (0x206 != 0x208)
99+
# Fetching whole row for (0x205) c = 17 (2,2,17) and sends it
100+
# b finds no more rows in p2
101+
# b returns row from p1 (0x204) c = 13
102+
# No duplicate (0x205 != 0x206)
103+
# Fetching whole row for (0x206) c = 20 (2,2,20) and sends it
104+
# b reads row N+4 (0x208) from p1 c = 25
105+
# b returns row from p1 (0x208) c = 25
106+
# No duplicate (0x204 != 0x206)
107+
# Fetching whole row for (0x204) c = 13 (1,2,13) and sends it
108+
# Fetching whole row for (0x208) c = 25 (2,2,25)
109+
# a reads row N+5 (0x209) from p1 c = 28
110+
# a returns row from p1 (0x209) c = 28
111+
# Duplicate (0x209 == 0x209)
112+
# a returns row from p2 (0x205)
113+
# Fetching whole row for (0x209) c = 28 (2,1,28)
114+
# a reads row N+2 (0x206) from p2 c = 20
115+
# Fetching whole row for (0x205) c = 17 (2,2,17)
116+
# Fetching whole row for (0x206) c = 20 (2,2,20)
117+
118+
SELECT a,b,c FROM t1 WHERE (b = 2 OR a = 2) AND c > 0 AND c < 100;
119+
SET SESSION optimizer_switch="index_merge_union=off";
120+
SELECT a,b,c FROM t1 WHERE (b = 2 OR a = 2) AND c > 0 AND c < 100;
121+
EXPLAIN SELECT a,b,c FROM t1 WHERE (b = 2 OR a = 2) AND c > 0 AND c < 100;
122+
123+
--echo Insert a few more rows to trigger sort_union
124+
INSERT INTO t1 VALUES (1,1,1+81), (1,1,1+84), (1,1,1+87), (1,1,1+90),
125+
(1,1,1+93);
126+
ANALYZE TABLE t1;
127+
SET SESSION optimizer_switch="index_merge_sort_union=on";
128+
EXPLAIN SELECT a,b,c FROM t1 WHERE (b >= 2 OR a >= 2) AND c > 0 AND c < 100;
129+
if ($use_optimizer_trace)
130+
{
131+
SELECT TRACE FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
132+
SET SESSION optimizer_trace="enabled=off";
133+
}
134+
--echo # Not affected, added for completeness...
135+
SELECT a,b,c FROM t1 WHERE (b >= 2 OR a >= 2) AND c > 0 AND c < 100;
136+
SET SESSION optimizer_switch="index_merge_sort_union=off";
137+
SELECT a,b,c FROM t1 WHERE (b >= 2 OR a >= 2) AND c > 0 AND c < 100;
138+
EXPLAIN SELECT a,b,c FROM t1 WHERE (b >= 2 OR a >= 2) AND c > 0 AND c < 100;
139+
140+
SET @@session.optimizer_switch = @old_opt_switch;
141+
DROP TABLE t1;

0 commit comments

Comments
 (0)