Skip to content

Commit 5c99e66

Browse files
author
Sreeharsha Ramanavarapu
committed
Bug #20229614 : OR CONDITIONS ON MULTI-COLUMN INDEX MAY NOT
 USE ALL INDEX COLUMNS TO FILTER ROWS ISSUE: ------ While optimizing a range scan for the OR-operator, key_or incorrectly assumes an out-of-memory situation. This in turn results in an incomplete condition being considered for the query plan and gives an incorrect row estimate. Hence the over-estimation of the number of qualifying rows. SOLUTION: --------- In key_or(), a key condition is cloned if it's already in use in some other condition. In this case NULL should be returned only if allocation fails. The check of key_count > 0 was originally added as part of Bug #4157. This was done to check the key_count after the swap of keys. Here changing the "||" to "&&" ensures that when the new key_count is greater than 0, there will be an attempt to clone the tree.
1 parent 10ce5f9 commit 5c99e66

8 files changed

Lines changed: 142 additions & 1 deletion

File tree

‎mysql-test/include/range.inc‎

Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1796,3 +1796,24 @@ eval $query_ba;
17961796
--echo
17971797
DROP TABLE t1,t2;
17981798

1799+
--echo #Bug #20229614: OR CONDITIONS ON MULTI-COLUMN INDEX
1800+
--echo # MAY NOT USE ALL INDEX COLUMNS TO
1801+
--echo # FILTER ROWS
1802+
1803+
CREATE TABLE t1 (c1 INT, c2 INT, c3 INT,
1804+
PRIMARY KEY(c1, c2, c3)) ENGINE=INNODB;
1805+
1806+
INSERT INTO t1 VALUES (1, 1, 1), (1, 1, 2), (1, 1, 3),
1807+
(1, 1, 4), (1, 1, 5);
1808+
INSERT INTO t1 SELECT c1, 2, c3 FROM t1;
1809+
INSERT INTO t1 SELECT c1, 3, c3 FROM t1 WHERE c2 = 1;
1810+
1811+
SELECT COUNT(*) FROM t1;
1812+
1813+
# Rows considered should be 3.
1814+
EXPLAIN SELECT c1, c2, c3
1815+
FROM t1
1816+
WHERE (c1 = 1 AND c2 = 1 AND c3 = 1) OR
1817+
(c1 = 1 AND c2 = 2 AND c3 = 2) OR
1818+
(c1 = 1 AND c2 = 2 AND c3 = 3);
1819+
DROP TABLE t1;

‎mysql-test/r/range_all.result‎

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2457,4 +2457,24 @@ a b
24572457
2001-01-01 11:22:33 2001-01-01 11:22:33
24582458

24592459
DROP TABLE t1,t2;
2460+
#Bug #20229614: OR CONDITIONS ON MULTI-COLUMN INDEX
2461+
# MAY NOT USE ALL INDEX COLUMNS TO
2462+
# FILTER ROWS
2463+
CREATE TABLE t1 (c1 INT, c2 INT, c3 INT,
2464+
PRIMARY KEY(c1, c2, c3)) ENGINE=INNODB;
2465+
INSERT INTO t1 VALUES (1, 1, 1), (1, 1, 2), (1, 1, 3),
2466+
(1, 1, 4), (1, 1, 5);
2467+
INSERT INTO t1 SELECT c1, 2, c3 FROM t1;
2468+
INSERT INTO t1 SELECT c1, 3, c3 FROM t1 WHERE c2 = 1;
2469+
SELECT COUNT(*) FROM t1;
2470+
COUNT(*)
2471+
15
2472+
EXPLAIN SELECT c1, c2, c3
2473+
FROM t1
2474+
WHERE (c1 = 1 AND c2 = 1 AND c3 = 1) OR
2475+
(c1 = 1 AND c2 = 2 AND c3 = 2) OR
2476+
(c1 = 1 AND c2 = 2 AND c3 = 3);
2477+
id select_type table type possible_keys key key_len ref rows Extra
2478+
1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 3 Using where; Using index
2479+
DROP TABLE t1;
24602480
set optimizer_switch=default;

‎mysql-test/r/range_icp.result‎

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2457,4 +2457,24 @@ a b
24572457
2001-01-01 11:22:33 2001-01-01 11:22:33
24582458

24592459
DROP TABLE t1,t2;
2460+
#Bug #20229614: OR CONDITIONS ON MULTI-COLUMN INDEX
2461+
# MAY NOT USE ALL INDEX COLUMNS TO
2462+
# FILTER ROWS
2463+
CREATE TABLE t1 (c1 INT, c2 INT, c3 INT,
2464+
PRIMARY KEY(c1, c2, c3)) ENGINE=INNODB;
2465+
INSERT INTO t1 VALUES (1, 1, 1), (1, 1, 2), (1, 1, 3),
2466+
(1, 1, 4), (1, 1, 5);
2467+
INSERT INTO t1 SELECT c1, 2, c3 FROM t1;
2468+
INSERT INTO t1 SELECT c1, 3, c3 FROM t1 WHERE c2 = 1;
2469+
SELECT COUNT(*) FROM t1;
2470+
COUNT(*)
2471+
15
2472+
EXPLAIN SELECT c1, c2, c3
2473+
FROM t1
2474+
WHERE (c1 = 1 AND c2 = 1 AND c3 = 1) OR
2475+
(c1 = 1 AND c2 = 2 AND c3 = 2) OR
2476+
(c1 = 1 AND c2 = 2 AND c3 = 3);
2477+
id select_type table type possible_keys key key_len ref rows Extra
2478+
1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 3 Using where; Using index
2479+
DROP TABLE t1;
24602480
set optimizer_switch=default;

‎mysql-test/r/range_icp_mrr.result‎

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2457,4 +2457,24 @@ a b
24572457
2001-01-01 11:22:33 2001-01-01 11:22:33
24582458

24592459
DROP TABLE t1,t2;
2460+
#Bug #20229614: OR CONDITIONS ON MULTI-COLUMN INDEX
2461+
# MAY NOT USE ALL INDEX COLUMNS TO
2462+
# FILTER ROWS
2463+
CREATE TABLE t1 (c1 INT, c2 INT, c3 INT,
2464+
PRIMARY KEY(c1, c2, c3)) ENGINE=INNODB;
2465+
INSERT INTO t1 VALUES (1, 1, 1), (1, 1, 2), (1, 1, 3),
2466+
(1, 1, 4), (1, 1, 5);
2467+
INSERT INTO t1 SELECT c1, 2, c3 FROM t1;
2468+
INSERT INTO t1 SELECT c1, 3, c3 FROM t1 WHERE c2 = 1;
2469+
SELECT COUNT(*) FROM t1;
2470+
COUNT(*)
2471+
15
2472+
EXPLAIN SELECT c1, c2, c3
2473+
FROM t1
2474+
WHERE (c1 = 1 AND c2 = 1 AND c3 = 1) OR
2475+
(c1 = 1 AND c2 = 2 AND c3 = 2) OR
2476+
(c1 = 1 AND c2 = 2 AND c3 = 3);
2477+
id select_type table type possible_keys key key_len ref rows Extra
2478+
1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 3 Using where; Using index
2479+
DROP TABLE t1;
24602480
set optimizer_switch=default;

‎mysql-test/r/range_mrr.result‎

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2457,4 +2457,24 @@ a b
24572457
2001-01-01 11:22:33 2001-01-01 11:22:33
24582458

24592459
DROP TABLE t1,t2;
2460+
#Bug #20229614: OR CONDITIONS ON MULTI-COLUMN INDEX
2461+
# MAY NOT USE ALL INDEX COLUMNS TO
2462+
# FILTER ROWS
2463+
CREATE TABLE t1 (c1 INT, c2 INT, c3 INT,
2464+
PRIMARY KEY(c1, c2, c3)) ENGINE=INNODB;
2465+
INSERT INTO t1 VALUES (1, 1, 1), (1, 1, 2), (1, 1, 3),
2466+
(1, 1, 4), (1, 1, 5);
2467+
INSERT INTO t1 SELECT c1, 2, c3 FROM t1;
2468+
INSERT INTO t1 SELECT c1, 3, c3 FROM t1 WHERE c2 = 1;
2469+
SELECT COUNT(*) FROM t1;
2470+
COUNT(*)
2471+
15
2472+
EXPLAIN SELECT c1, c2, c3
2473+
FROM t1
2474+
WHERE (c1 = 1 AND c2 = 1 AND c3 = 1) OR
2475+
(c1 = 1 AND c2 = 2 AND c3 = 2) OR
2476+
(c1 = 1 AND c2 = 2 AND c3 = 3);
2477+
id select_type table type possible_keys key key_len ref rows Extra
2478+
1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 3 Using where; Using index
2479+
DROP TABLE t1;
24602480
set optimizer_switch=default;

‎mysql-test/r/range_mrr_cost.result‎

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2457,4 +2457,24 @@ a b
24572457
2001-01-01 11:22:33 2001-01-01 11:22:33
24582458

24592459
DROP TABLE t1,t2;
2460+
#Bug #20229614: OR CONDITIONS ON MULTI-COLUMN INDEX
2461+
# MAY NOT USE ALL INDEX COLUMNS TO
2462+
# FILTER ROWS
2463+
CREATE TABLE t1 (c1 INT, c2 INT, c3 INT,
2464+
PRIMARY KEY(c1, c2, c3)) ENGINE=INNODB;
2465+
INSERT INTO t1 VALUES (1, 1, 1), (1, 1, 2), (1, 1, 3),
2466+
(1, 1, 4), (1, 1, 5);
2467+
INSERT INTO t1 SELECT c1, 2, c3 FROM t1;
2468+
INSERT INTO t1 SELECT c1, 3, c3 FROM t1 WHERE c2 = 1;
2469+
SELECT COUNT(*) FROM t1;
2470+
COUNT(*)
2471+
15
2472+
EXPLAIN SELECT c1, c2, c3
2473+
FROM t1
2474+
WHERE (c1 = 1 AND c2 = 1 AND c3 = 1) OR
2475+
(c1 = 1 AND c2 = 2 AND c3 = 2) OR
2476+
(c1 = 1 AND c2 = 2 AND c3 = 3);
2477+
id select_type table type possible_keys key key_len ref rows Extra
2478+
1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 3 Using where; Using index
2479+
DROP TABLE t1;
24602480
set optimizer_switch=default;

‎mysql-test/r/range_none.result‎

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2456,4 +2456,24 @@ a b
24562456
2001-01-01 11:22:33 2001-01-01 11:22:33
24572457

24582458
DROP TABLE t1,t2;
2459+
#Bug #20229614: OR CONDITIONS ON MULTI-COLUMN INDEX
2460+
# MAY NOT USE ALL INDEX COLUMNS TO
2461+
# FILTER ROWS
2462+
CREATE TABLE t1 (c1 INT, c2 INT, c3 INT,
2463+
PRIMARY KEY(c1, c2, c3)) ENGINE=INNODB;
2464+
INSERT INTO t1 VALUES (1, 1, 1), (1, 1, 2), (1, 1, 3),
2465+
(1, 1, 4), (1, 1, 5);
2466+
INSERT INTO t1 SELECT c1, 2, c3 FROM t1;
2467+
INSERT INTO t1 SELECT c1, 3, c3 FROM t1 WHERE c2 = 1;
2468+
SELECT COUNT(*) FROM t1;
2469+
COUNT(*)
2470+
15
2471+
EXPLAIN SELECT c1, c2, c3
2472+
FROM t1
2473+
WHERE (c1 = 1 AND c2 = 1 AND c3 = 1) OR
2474+
(c1 = 1 AND c2 = 2 AND c3 = 2) OR
2475+
(c1 = 1 AND c2 = 2 AND c3 = 3);
2476+
id select_type table type possible_keys key key_len ref rows Extra
2477+
1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 3 Using where; Using index
2478+
DROP TABLE t1;
24592479
set optimizer_switch=default;

‎sql/opt_range.cc‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7804,7 +7804,7 @@ key_or(RANGE_OPT_PARAM *param, SEL_ARG *key1, SEL_ARG *key2)
78047804
{
78057805
swap_variables(SEL_ARG *,key1,key2);
78067806
}
7807-
if (key1->use_count > 0 || !(key1=key1->clone_tree(param)))
7807+
if (key1->use_count > 0 && (key1= key1->clone_tree(param)) == NULL)
78087808
return 0; // OOM
78097809
}
78107810

0 commit comments

Comments
 (0)