diff options
| -rw-r--r-- | mysql-test/r/subselect.result | 56 | ||||
| -rw-r--r-- | mysql-test/t/subselect.test | 15 | ||||
| -rw-r--r-- | sql/item_subselect.cc | 6 | ||||
| -rw-r--r-- | sql/sql_select.cc | 8 |
4 files changed, 80 insertions, 5 deletions
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 03bb588fec9..06408c414cb 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -1624,3 +1624,59 @@ select 2 in (select * from t1); 1 SET SQL_SELECT_LIMIT=default; drop table t1; +create table t1 (id int not null, text varchar(20) not null default '', primary key (id)); +insert into t1 (id, text) values (1, 'text1'), (2, 'text2'), (3, 'text3'), (4, 'text4'), (5, 'text5'), (6, 'text6'), (7, 'text7'), (8, 'text8'), (9, 'text9'), (10, 'text10'), (11, 'text11'), (12, 'text12'); +select * from t1 where id not in (select id from t1 where id < 8); +id text +8 text8 +9 text9 +10 text10 +11 text11 +12 text12 +select * from t1 as tt where not exists (select id from t1 where id < 8 and (id = tt.id or id is null) having id is not null); +id text +8 text8 +9 text9 +10 text10 +11 text11 +12 text12 +explain extended select * from t1 where id not in (select id from t1 where id < 8); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 12 Using where +2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 Using index; Using where +Warnings: +Note 1003 select high_priority test.t1.id AS `id`,test.t1.text AS `text` from test.t1 where not(<in_optimizer>(test.t1.id,<exists>(<primary_index_lookup>(<cache>(test.t1.id) in t1 on PRIMARY where (test.t1.id < 8))))) +explain extended select * from t1 as tt where not exists (select id from t1 where id < 8 and (id = tt.id or id is null) having id is not null); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY tt ALL NULL NULL NULL NULL 12 Using where +2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 test.tt.id 7 Using where; Using index +Warnings: +Note 1275 Field or reference 'tt.id' of SELECT #2 was resolved in SELECT #1 +Note 1003 select high_priority test.tt.id AS `id`,test.tt.text AS `text` from test.t1 tt where not(exists(select test.t1.id AS `id` from test.t1 where ((test.t1.id < 8) and ((test.t1.id = test.tt.id) or isnull(test.t1.id))) having (test.t1.id is not null) limit 1)) +insert into t1 (id, text) values (1000, 'text1000'), (1001, 'text1001'); +create table t2 (id int not null, text varchar(20) not null default '', primary key (id)); +insert into t2 (id, text) values (1, 'text1'), (2, 'text2'), (3, 'text3'), (4, 'text4'), (5, 'text5'), (6, 'text6'), (7, 'text7'), (8, 'text8'), (9, 'text9'), (10, 'text10'), (11, 'text1'), (12, 'text2'), (13, 'text3'), (14, 'text4'), (15, 'text5'), (16, 'text6'), (17, 'text7'), (18, 'text8'), (19, 'text9'), (20, 'text10'),(21, 'text1'), (22, 'text2'), (23, 'text3'), (24, 'text4'), (25, 'text5'), (26, 'text6'), (27, 'text7'), (28, 'text8'), (29, 'text9'), (30, 'text10'), (31, 'text1'), (32, 'text2'), (33, 'text3'), (34, 'text4'), (35, 'text5'), (36, 'text6'), (37, 'text7'), (38, 'text8'), (39, 'text9'), (40, 'text10'), (41, 'text1'), (42, 'text2'), (43, 'text3'), (44, 'text4'), (45, 'text5'), (46, 'text6'), (47, 'text7'), (48, 'text8'), (49, 'text9'), (50, 'text10'); +select * from t1 a left join t2 b on (a.id=b.id or b.id is null) join t1 c on (if(isnull(b.id), 1000, b.id)=c.id); +id text id text id text +1 text1 1 text1 1 text1 +2 text2 2 text2 2 text2 +3 text3 3 text3 3 text3 +4 text4 4 text4 4 text4 +5 text5 5 text5 5 text5 +6 text6 6 text6 6 text6 +7 text7 7 text7 7 text7 +8 text8 8 text8 8 text8 +9 text9 9 text9 9 text9 +10 text10 10 text10 10 text10 +11 text11 11 text1 11 text11 +12 text12 12 text2 12 text12 +1000 text1000 NULL NULL 1000 text1000 +1001 text1001 NULL NULL 1000 text1000 +explain extended select * from t1 a left join t2 b on (a.id=b.id or b.id is null) join t1 c on (if(isnull(b.id), 1000, b.id)=c.id); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE a ALL NULL NULL NULL NULL 14 +1 SIMPLE b eq_ref PRIMARY PRIMARY 4 test.a.id 2 +1 SIMPLE c eq_ref PRIMARY PRIMARY 4 func 1 Using where +Warnings: +Note 1003 select high_priority test.a.id AS `id`,test.a.text AS `text`,test.b.id AS `id`,test.b.text AS `text`,test.c.id AS `id`,test.c.text AS `text` from test.t1 a left join test.t2 b on(((test.a.id = test.b.id) or isnull(test.b.id))) join test.t1 c where (if(isnull(test.b.id),1000,test.b.id) = test.c.id) +drop table t1,t2; diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 1a33890360d..b86d432ee0d 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -1066,3 +1066,18 @@ select 2 in (select * from t1); SET SQL_SELECT_LIMIT=default; drop table t1; +# +# ref_or_null replacing with ref +# +create table t1 (id int not null, text varchar(20) not null default '', primary key (id)); +insert into t1 (id, text) values (1, 'text1'), (2, 'text2'), (3, 'text3'), (4, 'text4'), (5, 'text5'), (6, 'text6'), (7, 'text7'), (8, 'text8'), (9, 'text9'), (10, 'text10'), (11, 'text11'), (12, 'text12'); +select * from t1 where id not in (select id from t1 where id < 8); +select * from t1 as tt where not exists (select id from t1 where id < 8 and (id = tt.id or id is null) having id is not null); +explain extended select * from t1 where id not in (select id from t1 where id < 8); +explain extended select * from t1 as tt where not exists (select id from t1 where id < 8 and (id = tt.id or id is null) having id is not null); +insert into t1 (id, text) values (1000, 'text1000'), (1001, 'text1001'); +create table t2 (id int not null, text varchar(20) not null default '', primary key (id)); +insert into t2 (id, text) values (1, 'text1'), (2, 'text2'), (3, 'text3'), (4, 'text4'), (5, 'text5'), (6, 'text6'), (7, 'text7'), (8, 'text8'), (9, 'text9'), (10, 'text10'), (11, 'text1'), (12, 'text2'), (13, 'text3'), (14, 'text4'), (15, 'text5'), (16, 'text6'), (17, 'text7'), (18, 'text8'), (19, 'text9'), (20, 'text10'),(21, 'text1'), (22, 'text2'), (23, 'text3'), (24, 'text4'), (25, 'text5'), (26, 'text6'), (27, 'text7'), (28, 'text8'), (29, 'text9'), (30, 'text10'), (31, 'text1'), (32, 'text2'), (33, 'text3'), (34, 'text4'), (35, 'text5'), (36, 'text6'), (37, 'text7'), (38, 'text8'), (39, 'text9'), (40, 'text10'), (41, 'text1'), (42, 'text2'), (43, 'text3'), (44, 'text4'), (45, 'text5'), (46, 'text6'), (47, 'text7'), (48, 'text8'), (49, 'text9'), (50, 'text10'); +select * from t1 a left join t2 b on (a.id=b.id or b.id is null) join t1 c on (if(isnull(b.id), 1000, b.id)=c.id); +explain extended select * from t1 a left join t2 b on (a.id=b.id or b.id is null) join t1 c on (if(isnull(b.id), 1000, b.id)=c.id); +drop table t1,t2; diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 372f086d464..682b8d73330 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -740,9 +740,9 @@ Item_in_subselect::single_value_transformer(JOIN *join, select_lex->ref_pointer_array[0]= select_lex->item_list.head(); if (select_lex->table_list.elements) { - Item *having= item, *isnull= item; + Item *having= item, *orig_item= item; item= func->create(expr, item); - if (!abort_on_null) + if (!abort_on_null && orig_item->maybe_null) { having= new Item_is_not_null_test(this, having); /* @@ -762,7 +762,7 @@ Item_in_subselect::single_value_transformer(JOIN *join, } select_lex->having_fix_field= 0; item= new Item_cond_or(item, - new Item_func_isnull(isnull)); + new Item_func_isnull(orig_item)); } item->name= (char *)in_additional_cond; /* diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 7605016dbe1..cf1dea8e85a 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -3266,8 +3266,12 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, KEYUSE *org_keyuse, keyuse,join->const_table_map, &keyinfo->key_part[i], (char*) key_buff,maybe_null); - /* Remmeber if we are going to use REF_OR_NULL */ - if (keyuse->optimize & KEY_OPTIMIZE_REF_OR_NULL) + /* + Remmeber if we are going to use REF_OR_NULL + But only if field _really_ can be null i.e. we force JT_REF + instead of JT_REF_OR_NULL in case if field can't be null + */ + if (keyuse->optimize & KEY_OPTIMIZE_REF_OR_NULL && maybe_null) null_ref_key= key_buff; key_buff+=keyinfo->key_part[i].store_length; } |