diff options
author | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-05-04 18:00:34 +0000 |
---|---|---|
committer | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-05-04 18:00:34 +0000 |
commit | 3f619478f796eddbba6e39502fe941b285dd97b1 (patch) | |
tree | e2c7b5777f728320e5b5542b6213fd3591ba51e2 /mysql-test/main/subselect_exists2in_costmat.result | |
parent | Initial commit. (diff) | |
download | mariadb-3f619478f796eddbba6e39502fe941b285dd97b1.tar.xz mariadb-3f619478f796eddbba6e39502fe941b285dd97b1.zip |
Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'mysql-test/main/subselect_exists2in_costmat.result')
-rw-r--r-- | mysql-test/main/subselect_exists2in_costmat.result | 105 |
1 files changed, 105 insertions, 0 deletions
diff --git a/mysql-test/main/subselect_exists2in_costmat.result b/mysql-test/main/subselect_exists2in_costmat.result new file mode 100644 index 00000000..1c9574aa --- /dev/null +++ b/mysql-test/main/subselect_exists2in_costmat.result @@ -0,0 +1,105 @@ +set @subselect_mat_cost=@@optimizer_switch; +set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; +set long_query_time=0.1; +drop database if exists world; +set names utf8; +create database world; +use world; +CREATE TABLE Country ( +Code char(3) NOT NULL default '', +Name char(52) NOT NULL default '', +SurfaceArea float(10,2) NOT NULL default '0.00', +Population int(11) NOT NULL default '0', +Capital int(11) default NULL, +PRIMARY KEY (Code), +UNIQUE INDEX (Name) +); +CREATE TABLE City ( +ID int(11) NOT NULL auto_increment, +Name char(35) NOT NULL default '', +Country char(3) NOT NULL default '', +Population int(11) NOT NULL default '0', +PRIMARY KEY (ID), +INDEX (Population), +INDEX (Country) +); +CREATE TABLE CountryLanguage ( +Country char(3) NOT NULL default '', +Language char(30) NOT NULL default '', +Percentage float(3,1) NOT NULL default '0.0', +PRIMARY KEY (Country, Language), +INDEX (Percentage) +); +Make the schema and data more diverse by adding more indexes, nullable +columns, and NULL data. +create index SurfaceArea on Country(SurfaceArea); +create index Language on CountryLanguage(Language); +create index CityName on City(Name); +alter table City change population population int(11) null default 0; +select max(id) from City into @max_city_id; +Warnings: +Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead +insert into City values (@max_city_id + 1,'Kilifarevo','BGR',NULL); +SELECT COUNT(*) FROM Country; +COUNT(*) +239 +SELECT COUNT(*) FROM City; +COUNT(*) +4080 +SELECT COUNT(*) FROM CountryLanguage; +COUNT(*) +984 +set @@optimizer_switch = 'exists_to_in=on,in_to_exists=on,semijoin=on,materialization=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on'; + +1. Subquery in a disjunctive WHERE clause of the outer query. + + +Q1.1m: +MATERIALIZATION: there are too many rows in the outer query +to be looked up in the inner table. +EXPLAIN +SELECT Name FROM Country +WHERE (EXISTS (select 1 from City where City.Population > 100000 and +Code = Country) OR +Name LIKE 'L%') AND +surfacearea > 1000000; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY Country ALL Name,SurfaceArea NULL NULL NULL 239 Using where +2 MATERIALIZED City ALL Population,Country NULL NULL NULL 4079 Using where +SELECT Name FROM Country +WHERE (EXISTS (select 1 from City where City.Population > 100000 and +Code = Country) OR +Name LIKE 'L%') AND +surfacearea > 1000000; +Name +Algeria +Angola +Argentina +Australia +Bolivia +Brazil +Egypt +South Africa +Ethiopia +Indonesia +India +Iran +Canada +Kazakstan +China +Colombia +Congo, The Democratic Republic of the +Libyan Arab Jamahiriya +Mali +Mauritania +Mexico +Mongolia +Niger +Peru +Saudi Arabia +Sudan +Chad +Russian Federation +United States +drop database world; +set optimizer_switch=@subselect_mat_cost; |