diff options
author | Varun Gupta <varun.gupta@mariadb.com> | 2019-05-11 20:44:18 +0530 |
---|---|---|
committer | Varun Gupta <varun.gupta@mariadb.com> | 2019-05-11 20:50:12 +0530 |
commit | 6a365e0bf212cd11001a0dad1e51fd6020905301 (patch) | |
tree | f9ec17b312c21e8e838fe8f51c8bb2fdc647170a /mysql-test/main/range_vs_index_merge.result | |
parent | 9965966a49b5b17da4f631664a35da1b46f03cb2 (diff) | |
download | mariadb-git-6a365e0bf212cd11001a0dad1e51fd6020905301.tar.gz |
MDEV-13628: ORed condition in pushed index condition is not removed from the WHERE
So to push index condition for each join tab we have calculate the index condition that can be pushed and then
remove this index condition from the original condition. This is done through the function make_cond_remainder.
The problem is the function make_cond_remainder does not remove index condition when there is an OR operator.
Fixed this by making the function make_cond_remainder to keep in mind of the OR operator.
Also updated results for multiple test files which were incorrectly updated by the commit e0c1b3f24246d22e6785315f9a8448bd9a590422
code which was supposed to remove the condition present in the index
condition was not getting executed when the condition had OR operator, with AND the pushed
index condition was getting removed from where.
This problem affects all versions starting from 5.5 but this is a performance improvement, so fixing it in 10.4
Diffstat (limited to 'mysql-test/main/range_vs_index_merge.result')
-rw-r--r-- | mysql-test/main/range_vs_index_merge.result | 14 |
1 files changed, 7 insertions, 7 deletions
diff --git a/mysql-test/main/range_vs_index_merge.result b/mysql-test/main/range_vs_index_merge.result index 65ac003b427..5c3e5441b8b 100644 --- a/mysql-test/main/range_vs_index_merge.result +++ b/mysql-test/main/range_vs_index_merge.result @@ -327,11 +327,11 @@ ID Name Country Population EXPLAIN SELECT * FROM City WHERE (ID < 10) OR (ID BETWEEN 100 AND 110); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY PRIMARY 4 NULL 20 Using index condition; Using where +1 SIMPLE City range PRIMARY PRIMARY 4 NULL 20 Using index condition EXPLAIN SELECT * FROM City WHERE (ID < 200) OR (ID BETWEEN 100 AND 200); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY PRIMARY 4 NULL 200 Using index condition; Using where +1 SIMPLE City range PRIMARY PRIMARY 4 NULL 200 Using index condition EXPLAIN SELECT * FROM City WHERE (ID < 600) OR (ID BETWEEN 900 AND 1500); id select_type table type possible_keys key key_len ref rows Extra @@ -343,11 +343,11 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN SELECT * FROM City WHERE Name LIKE 'H%' OR Name LIKE 'P%' ; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Name Name 35 NULL 223 Using index condition; Using where +1 SIMPLE City range Name Name 35 NULL 223 Using index condition EXPLAIN SELECT * FROM City WHERE Name LIKE 'Ha%' OR Name LIKE 'Pa%' ; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Name Name 35 NULL 72 Using index condition; Using where +1 SIMPLE City range Name Name 35 NULL 72 Using index condition EXPLAIN SELECT * FROM City WHERE ((ID < 10) AND (Name LIKE 'H%' OR (Country > 'A' AND Country < 'ARG'))) @@ -1079,7 +1079,7 @@ EXPLAIN SELECT Name, Country, Population FROM City WHERE (Name='Samara' AND Country='RUS') OR (Name='Seattle' AND Country='USA'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Country,CountryPopulation,CountryName,CityName CountryName 38 NULL 28 Using index condition; Using where +1 SIMPLE City range Country,CountryPopulation,CountryName,CityName CountryName 38 NULL 28 Using index condition SELECT Name, Country, Population FROM City WHERE (Name='Manila' AND Country='PHL') OR (Name='Addis Abeba' AND Country='ETH') OR @@ -1166,7 +1166,7 @@ EXPLAIN SELECT Name, Country, Population FROM City WHERE (Name='Samara' AND Country='RUS') OR (Name='Seattle' AND Country='USA'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Country,CountryPopulation,CountryName,CityName CountryName 38 NULL 28 Using index condition; Using where +1 SIMPLE City range Country,CountryPopulation,CountryName,CityName CountryName 38 NULL 28 Using index condition SELECT Name, Country, Population FROM City WHERE (Name='Manila' AND Country='PHL') OR (Name='Addis Abeba' AND Country='ETH') OR @@ -1938,6 +1938,6 @@ Country='POL' AND Name IN ('Warszawa', 'Wroclaw') OR Country='NOR' AND Name IN ('Oslo', 'Bergen') OR Country='ITA' AND Name IN ('Napoli', 'Venezia'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range CountryName,Name CountryName 38 NULL 20 Using index condition; Using where +1 SIMPLE City range CountryName,Name CountryName 38 NULL 20 Using index condition DROP DATABASE world; set session optimizer_switch='index_merge_sort_intersection=default'; |