diff options
| -rw-r--r-- | mysql-test/r/join_outer.result | 17 | ||||
| -rw-r--r-- | mysql-test/t/join_outer.test | 13 | ||||
| -rw-r--r-- | sql/sql_select.cc | 30 |
3 files changed, 52 insertions, 8 deletions
diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index debec01fbdc..ae4d99e6241 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -667,3 +667,20 @@ left outer join t2 using (f2) left outer join t3 using (f3); Unknown column 'test.t2.f3' in 'on clause' drop table t1,t2,t3; +create table t1 (a1 int, a2 int); +create table t2 (b1 int not null, b2 int); +create table t3 (c1 int, c2 int); +insert into t1 values (1,2), (2,2), (3,2); +insert into t2 values (1,3), (2,3); +insert into t3 values (2,4), (3,4); +select * from t1 left join t2 on b1 = a1 left join t3 on c1 = a1 and b1 is null; +a1 a2 b1 b2 c1 c2 +1 2 1 3 NULL NULL +2 2 2 3 NULL NULL +3 2 NULL NULL 3 4 +explain select * from t1 left join t2 on b1 = a1 left join t3 on c1 = a1 and b1 is null; +table type possible_keys key key_len ref rows Extra +t1 ALL NULL NULL NULL NULL 3 +t2 ALL NULL NULL NULL NULL 2 +t3 ALL NULL NULL NULL NULL 2 +drop table t1, t2, t3; diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test index ee7d55d2a4e..bed4d4b033b 100644 --- a/mysql-test/t/join_outer.test +++ b/mysql-test/t/join_outer.test @@ -437,3 +437,16 @@ select * from t1 left outer join t2 using (f2) left outer join t3 using (f3); drop table t1,t2,t3; + +create table t1 (a1 int, a2 int); +create table t2 (b1 int not null, b2 int); +create table t3 (c1 int, c2 int); + +insert into t1 values (1,2), (2,2), (3,2); +insert into t2 values (1,3), (2,3); +insert into t3 values (2,4), (3,4); + +select * from t1 left join t2 on b1 = a1 left join t3 on c1 = a1 and b1 is null; +explain select * from t1 left join t2 on b1 = a1 left join t3 on c1 = a1 and b1 is null; + +drop table t1, t2, t3; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 892ad02bb5b..d0d6c1e5fa2 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1683,10 +1683,6 @@ add_key_part(DYNAMIC_ARRAY *keyuse_array,KEY_FIELD *key_field) } } } - /* Mark that we can optimize LEFT JOIN */ - if (key_field->val->type() == Item::NULL_ITEM && - !key_field->field->real_maybe_null()) - key_field->field->table->reginfo.not_exists_optimize=1; } @@ -1777,13 +1773,26 @@ update_ref_and_keys(THD *thd, DYNAMIC_ARRAY *keyuse,JOIN_TAB *join_tab, { KEY_FIELD *key_fields,*end; + KEY_FIELD *field; if (!(key_fields=(KEY_FIELD*) thd->alloc(sizeof(key_fields[0])*(thd->cond_count+1)*2))) return TRUE; /* purecov: inspected */ - and_level=0; end=key_fields; + and_level=0; field=end=key_fields; + if (my_init_dynamic_array(keyuse,sizeof(KEYUSE),20,64)) + return TRUE; if (cond) + { add_key_fields(join_tab,&end,&and_level,cond,normal_tables); + for (; field != end ; field++) + { + add_key_part(keyuse,field); + /* Mark that we can optimize LEFT JOIN */ + if (field->val->type() == Item::NULL_ITEM && + !field->field->real_maybe_null()) + field->field->table->reginfo.not_exists_optimize=1; + } + } for (i=0 ; i < tables ; i++) { if (join_tab[i].on_expr) @@ -1792,11 +1801,16 @@ update_ref_and_keys(THD *thd, DYNAMIC_ARRAY *keyuse,JOIN_TAB *join_tab, join_tab[i].table->map); } } - if (my_init_dynamic_array(keyuse,sizeof(KEYUSE),20,64)) - return TRUE; /* fill keyuse with found key parts */ - for (KEY_FIELD *field=key_fields ; field != end ; field++) + for (; field != end ; field++) + { add_key_part(keyuse,field); + /* Mark that we can optimize LEFT JOIN */ + if (field->field->table == join_tab->table && + field->val->type() == Item::NULL_ITEM && + !field->field->real_maybe_null()) + join_tab->table->reginfo.not_exists_optimize=1; + } } if (thd->lex.select->ftfunc_list.elements) |