diff options
| -rwxr-xr-x | BUILD/compile-pentium-valgrind-max | 2 | ||||
| -rw-r--r-- | mysql-test/r/distinct.result | 6 | ||||
| -rw-r--r-- | mysql-test/r/null_key.result | 110 | ||||
| -rw-r--r-- | mysql-test/r/subselect.result | 2 | ||||
| -rw-r--r-- | mysql-test/t/null_key.test | 35 | ||||
| -rw-r--r-- | sql/sql_select.cc | 599 | ||||
| -rw-r--r-- | sql/sql_select.h | 7 | ||||
| -rw-r--r-- | sql/sql_yacc.yy | 1 | ||||
| -rw-r--r-- | sql/table.cc | 4 | ||||
| -rw-r--r-- | sql/unireg.cc | 4 |
10 files changed, 500 insertions, 270 deletions
diff --git a/BUILD/compile-pentium-valgrind-max b/BUILD/compile-pentium-valgrind-max index c6d8e4f21e8..20876d67472 100755 --- a/BUILD/compile-pentium-valgrind-max +++ b/BUILD/compile-pentium-valgrind-max @@ -3,7 +3,7 @@ path=`dirname $0` . "$path/SETUP.sh" -extra_flags="$pentium_cflags $debug_cflags -USAFEMALLOC -DHAVE_purify" +extra_flags="$pentium_cflags $debug_cflags -USAFEMALLOC -UFORCE_INIT_OF_VARS -DHAVE_purify" c_warnings="$c_warnings $debug_extra_warnings" cxx_warnings="$cxx_warnings $debug_extra_warnings" extra_configs="$pentium_configs $debug_configs" diff --git a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result index 9ebcd1fb915..c5841f28830 100644 --- a/mysql-test/r/distinct.result +++ b/mysql-test/r/distinct.result @@ -173,9 +173,9 @@ INSERT INTO t2 values (1),(2),(3); INSERT INTO t3 VALUES (1,'1'),(2,'2'),(1,'1'),(2,'2'); explain SELECT distinct t3.a FROM t3,t2,t1 WHERE t3.a=t1.b AND t1.a=t2.a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 index a a 5 NULL 6 Using index; Using temporary -1 SIMPLE t2 index a a 4 NULL 5 Using index; Distinct -1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.a 1 Using where; Distinct +1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 2 Using temporary +1 SIMPLE t2 ref a a 4 test.t1.a 2 Using index +1 SIMPLE t3 ref a a 5 test.t1.b 2 Using where; Using index SELECT distinct t3.a FROM t3,t2,t1 WHERE t3.a=t1.b AND t1.a=t2.a; a 1 diff --git a/mysql-test/r/null_key.result b/mysql-test/r/null_key.result index 289290ba08c..7dc0b4bfdd3 100644 --- a/mysql-test/r/null_key.result +++ b/mysql-test/r/null_key.result @@ -21,7 +21,10 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a,b a 9 NULL 3 Using where; Using index explain select * from t1 where (a is null or a = 7) and b=7; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref a,b b 4 const 2 Using where +1 SIMPLE t1 ref_or_null a,b a 9 const,const 2 Using where; Using index +explain select * from t1 where (a is null or a = 7) and b=7 order by a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref_or_null a,b a 9 const,const 2 Using where; Using index; Using filesort explain select * from t1 where (a is null and b>a) or a is null and b=7 limit 2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref a,b a 5 const 3 Using where; Using index @@ -56,13 +59,15 @@ NULL 9 NULL 9 select * from t1 where (a is null or a = 7) and b=7; a b -NULL 7 7 7 +NULL 7 select * from t1 where a is null and b=9 or a is null and b=7 limit 3; a b NULL 7 NULL 9 NULL 9 +create table t2 like t1; +insert into t2 select * from t1; alter table t1 modify b blob not null, add c int not null, drop key a, add unique key (a,b(20),c), drop key b, add key (b(10)); explain select * from t1 where a is null and b = 2; id select_type table type possible_keys key key_len ref rows Extra @@ -84,7 +89,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a,b a 5 NULL 5 Using where explain select * from t1 where (a is null or a = 7) and b=7 and c=0; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL a,b NULL NULL NULL 12 Using where +1 SIMPLE t1 ref_or_null a,b a 5 const 4 Using where explain select * from t1 where (a is null and b>a) or a is null and b=7 limit 2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref a,b a 5 const 3 Using where @@ -125,8 +130,8 @@ NULL 9 0 NULL 9 0 select * from t1 where (a is null or a = 7) and b=7 and c=0; a b c -NULL 7 0 7 7 0 +NULL 7 0 select * from t1 where a is null and b=9 or a is null and b=7 limit 3; a b c NULL 7 0 @@ -136,6 +141,103 @@ select * from t1 where b like "6%"; a b c 6 6 0 drop table t1; +rename table t2 to t1; +alter table t1 modify b int null; +insert into t1 values (7,null), (8,null), (8,7); +explain select * from t1 where a = 7 and (b=7 or b is null); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref_or_null a,b a 10 const,const 2 Using where; Using index +select * from t1 where a = 7 and (b=7 or b is null); +a b +7 7 +7 NULL +explain select * from t1 where (a = 7 or a is null) and (b=7 or b is null); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a,b a 10 NULL 4 Using where; Using index +select * from t1 where (a = 7 or a is null) and (b=7 or b is null); +a b +NULL 7 +7 NULL +7 7 +explain select * from t1 where (a = 7 or a is null) and (a = 7 or a is null); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref_or_null a a 5 const 5 Using where; Using index +select * from t1 where (a = 7 or a is null) and (a = 7 or a is null); +a b +7 NULL +7 7 +NULL 7 +NULL 9 +NULL 9 +create table t2 (a int); +insert into t2 values (7),(8); +explain select * from t2 straight_join t1 where t1.a=t2.a and b is null; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 +1 SIMPLE t1 ref a,b a 10 test.t2.a,const 2 Using where; Using index +drop index b on t1; +explain select * from t2,t1 where t1.a=t2.a and b is null; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 +1 SIMPLE t1 ref a a 10 test.t2.a,const 2 Using where; Using index +select * from t2,t1 where t1.a=t2.a and b is null; +a a b +7 7 NULL +8 8 NULL +explain select * from t2,t1 where t1.a=t2.a and (b= 7 or b is null); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 +1 SIMPLE t1 ref_or_null a a 10 test.t2.a,const 4 Using where; Using index +select * from t2,t1 where t1.a=t2.a and (b= 7 or b is null); +a a b +7 7 7 +7 7 NULL +8 8 7 +8 8 NULL +explain select * from t2,t1 where (t1.a=t2.a or t1.a is null) and b= 7; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 +1 SIMPLE t1 ref_or_null a a 10 test.t2.a,const 4 Using where; Using index +select * from t2,t1 where (t1.a=t2.a or t1.a is null) and b= 7; +a a b +7 7 7 +7 NULL 7 +8 8 7 +8 NULL 7 +explain select * from t2,t1 where (t1.a=t2.a or t1.a is null) and (b= 7 or b is null); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 +1 SIMPLE t1 ref_or_null a a 5 test.t2.a 4 Using where; Using index +select * from t2,t1 where (t1.a=t2.a or t1.a is null) and (b= 7 or b is null); +a a b +7 7 NULL +7 7 7 +7 NULL 7 +8 8 NULL +8 8 7 +8 NULL 7 +insert into t2 values (null),(6); +delete from t1 where a=8; +explain select * from t2,t1 where t1.a=t2.a or t1.a is null; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 4 +1 SIMPLE t1 ref_or_null a a 5 test.t2.a 4 Using where; Using index +explain select * from t2,t1 where t1.a<=>t2.a or (t1.a is null and t1.b <> 9); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 4 +1 SIMPLE t1 ref_or_null a a 5 test.t2.a 4 Using where; Using index +select * from t2,t1 where t1.a<=>t2.a or (t1.a is null and t1.b <> 9); +a a b +7 7 NULL +7 7 7 +7 NULL 7 +8 NULL 7 +NULL NULL 7 +NULL NULL 9 +NULL NULL 9 +6 6 6 +6 NULL 7 +drop table t1,t2; CREATE TABLE t1 ( id int(10) unsigned NOT NULL auto_increment, uniq_id int(10) unsigned default NULL, diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index c41434336e5..354394e804c 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -827,7 +827,7 @@ a t1.a in (select t2.a from t2) explain SELECT t1.a, t1.a in (select t2.a from t2) FROM t1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 Using index -2 DEPENDENT SUBQUERY t2 index a a 5 NULL 3 Using where; Using index +2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 const 2 Using where; Using index drop table t1,t2; create table t1 (a float); select 10.5 IN (SELECT * from t1 LIMIT 1); diff --git a/mysql-test/t/null_key.test b/mysql-test/t/null_key.test index 18d0d368891..7d9500e90dd 100644 --- a/mysql-test/t/null_key.test +++ b/mysql-test/t/null_key.test @@ -14,6 +14,7 @@ explain select * from t1 where a=2 and b = 2; explain select * from t1 where a<=>b limit 2; explain select * from t1 where (a is null or a > 0 and a < 3) and b < 5 limit 3; explain select * from t1 where (a is null or a = 7) and b=7; +explain select * from t1 where (a is null or a = 7) and b=7 order by a; explain select * from t1 where (a is null and b>a) or a is null and b=7 limit 2; explain select * from t1 where a is null and b=9 or a is null and b=7 limit 3; explain select * from t1 where a > 1 and a < 3 limit 1; @@ -25,6 +26,8 @@ select * from t1 where (a is null or a > 0 and a < 3) and b < 5 limit 3; select * from t1 where (a is null or a > 0 and a < 3) and b > 7 limit 3; select * from t1 where (a is null or a = 7) and b=7; select * from t1 where a is null and b=9 or a is null and b=7 limit 3; +create table t2 like t1; +insert into t2 select * from t1; alter table t1 modify b blob not null, add c int not null, drop key a, add unique key (a,b(20),c), drop key b, add key (b(10)); explain select * from t1 where a is null and b = 2; explain select * from t1 where a is null and b = 2 and c=0; @@ -47,8 +50,38 @@ select * from t1 where (a is null or a > 0 and a < 3) and b > 7 limit 3; select * from t1 where (a is null or a = 7) and b=7 and c=0; select * from t1 where a is null and b=9 or a is null and b=7 limit 3; select * from t1 where b like "6%"; -drop table t1; +# +# Test ref_or_null optimization +# +drop table t1; +rename table t2 to t1; +alter table t1 modify b int null; +insert into t1 values (7,null), (8,null), (8,7); +explain select * from t1 where a = 7 and (b=7 or b is null); +select * from t1 where a = 7 and (b=7 or b is null); +explain select * from t1 where (a = 7 or a is null) and (b=7 or b is null); +select * from t1 where (a = 7 or a is null) and (b=7 or b is null); +explain select * from t1 where (a = 7 or a is null) and (a = 7 or a is null); +select * from t1 where (a = 7 or a is null) and (a = 7 or a is null); +create table t2 (a int); +insert into t2 values (7),(8); +explain select * from t2 straight_join t1 where t1.a=t2.a and b is null; +drop index b on t1; +explain select * from t2,t1 where t1.a=t2.a and b is null; +select * from t2,t1 where t1.a=t2.a and b is null; +explain select * from t2,t1 where t1.a=t2.a and (b= 7 or b is null); +select * from t2,t1 where t1.a=t2.a and (b= 7 or b is null); +explain select * from t2,t1 where (t1.a=t2.a or t1.a is null) and b= 7; +select * from t2,t1 where (t1.a=t2.a or t1.a is null) and b= 7; +explain select * from t2,t1 where (t1.a=t2.a or t1.a is null) and (b= 7 or b is null); +select * from t2,t1 where (t1.a=t2.a or t1.a is null) and (b= 7 or b is null); +insert into t2 values (null),(6); +delete from t1 where a=8; +explain select * from t2,t1 where t1.a=t2.a or t1.a is null; +explain select * from t2,t1 where t1.a<=>t2.a or (t1.a is null and t1.b <> 9); +select * from t2,t1 where t1.a<=>t2.a or (t1.a is null and t1.b <> 9); +drop table t1,t2; # # The following failed for Matt Loschert diff --git a/sql/sql_select.cc b/sql/sql_select.cc index b8eb9e19cc7..6ada6a1bfb7 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -31,8 +31,11 @@ #include <ft_global.h> const char *join_type_str[]={ "UNKNOWN","system","const","eq_ref","ref", - "MAYBE_REF","ALL","range","index","fulltext" }; + "MAYBE_REF","ALL","range","index","fulltext", + "ref_or_null" +}; +static void optimize_keyuse(JOIN *join, DYNAMIC_ARRAY *keyuse_array); static bool make_join_statistics(JOIN *join,TABLE_LIST *tables,COND *conds, DYNAMIC_ARRAY *keyuse); static bool update_ref_and_keys(THD *thd, DYNAMIC_ARRAY *keyuse, @@ -106,6 +109,8 @@ static int join_read_prev_same(READ_RECORD *info); static int join_read_prev(READ_RECORD *info); static int join_ft_read_first(JOIN_TAB *tab); static int join_ft_read_next(READ_RECORD *info); +static int join_read_always_key_or_null(JOIN_TAB *tab); +static int join_read_next_same_or_null(READ_RECORD *info); static COND *make_cond_for_table(COND *cond,table_map table, table_map used_table); static Item* part_of_refkey(TABLE *form,Field *field); @@ -1456,8 +1461,9 @@ make_join_statistics(JOIN *join,TABLE_LIST *tables,COND *conds, DYNAMIC_ARRAY *keyuse_array) { int error; - uint i,table_count,const_count,found_ref,refs,key,const_ref,eq_part; - table_map found_const_table_map,all_table_map; + uint i,table_count,const_count,key; + table_map found_const_table_map, all_table_map, found_ref, refs; + key_map const_ref, eq_part; TABLE **table_vector; JOIN_TAB *stat,*stat_end,*s,**stat_ref; KEYUSE *keyuse,*start_keyuse; @@ -1475,7 +1481,7 @@ make_join_statistics(JOIN *join,TABLE_LIST *tables,COND *conds, join->best_ref=stat_vector; stat_end=stat+table_count; - found_const_table_map=all_table_map=0; + found_const_table_map= all_table_map=0; const_count=0; for (s=stat,i=0 ; tables ; s++,tables=tables->next,i++) @@ -1632,16 +1638,17 @@ make_join_statistics(JOIN *join,TABLE_LIST *tables,COND *conds, key=keyuse->key; s->keys|= (key_map) 1 << key; // QQ: remove this ? - refs=const_ref=eq_part=0; + refs=const_ref=0; + eq_part=0; do { - if (keyuse->val->type() != Item::NULL_ITEM) + if (keyuse->val->type() != Item::NULL_ITEM && !keyuse->optimize) { if (!((~found_const_table_map) & keyuse->used_tables)) const_ref|= (key_map) 1 << keyuse->keypart; else refs|=keyuse->used_tables; - eq_part|= (uint) 1 << keyuse->keypart; + eq_part|= (key_map) 1 << keyuse->keypart; } keyuse++; } while (keyuse->table == table && keyuse->key == key); @@ -1700,8 +1707,6 @@ make_join_statistics(JOIN *join,TABLE_LIST *tables,COND *conds, if (s->worst_seeks < 2.0) // Fix for small tables s->worst_seeks=2.0; - /* if (s->type == JT_EQ_REF) - continue; */ if (s->const_keys) { ha_rows records; @@ -1752,7 +1757,10 @@ make_join_statistics(JOIN *join,TABLE_LIST *tables,COND *conds, join->found_const_table_map=found_const_table_map; if (join->const_tables != join->tables) + { + optimize_keyuse(join, keyuse_array); find_best_combination(join,all_table_map & ~join->const_table_map); + } else { memcpy((gptr) join->best_positions,(gptr) join->positions, @@ -1774,13 +1782,26 @@ make_join_statistics(JOIN *join,TABLE_LIST *tables,COND *conds, typedef struct key_field_t { // Used when finding key fields Field *field; Item *val; // May be empty if diff constant - uint level,const_level; // QQ: Remove const_level + uint level; + uint optimize; bool eq_func; - bool exists_optimize; } KEY_FIELD; +/* Values in optimize */ +#define KEY_OPTIMIZE_EXISTS 1 +#define KEY_OPTIMIZE_REF_OR_NULL 2 + +/* + Merge new key definitions to old ones, remove those not used in both + + This is called for OR between different levels -/* merge new key definitions to old ones, remove those not used in both */ + To be able to do 'ref_or_null' we merge a comparison of a column + and 'column IS NULL' to one test. This is useful for sub select queries + that are internally transformed to something like: + + SELECT * FROM t1 WHERE t1.key=outer_ref_field or t1.key IS NULL +*/ static KEY_FIELD * merge_key_fields(KEY_FIELD *start,KEY_FIELD *new_fields,KEY_FIELD *end, @@ -1802,20 +1823,46 @@ merge_key_fields(KEY_FIELD *start,KEY_FIELD *new_fields,KEY_FIELD *end, { if (new_fields->val->used_tables()) { + /* + If the value matches, we can use the key reference. + If not, we keep it until we have examined all new values + */ if (old->val->eq(new_fields->val, old->field->binary())) { - old->level=old->const_level=and_level; - old->exists_optimize&=new_fields->exists_optimize; + old->level= and_level; + old->optimize= ((old->optimize & new_fields->optimize & + KEY_OPTIMIZE_EXISTS) | + ((old->optimize | new_fields->optimize) & + KEY_OPTIMIZE_REF_OR_NULL)); } } - else if (old->val->eq(new_fields->val, old->field->binary()) && - old->eq_func && new_fields->eq_func) + else if (old->eq_func && new_fields->eq_func && + old->val->eq(new_fields->val, old->field->binary())) + + { + old->level= and_level; + old->optimize= ((old->optimize & new_fields->optimize & + KEY_OPTIMIZE_EXISTS) | + ((old->optimize | new_fields->optimize) & + KEY_OPTIMIZE_REF_OR_NULL)); + } + else if (old->eq_func && new_fields->eq_func && + (old->val->is_null() || new_fields->val->is_null())) { - old->level=old->const_level=and_level; - old->exists_optimize&=new_fields->exists_optimize; + /* field = expression OR field IS NULL */ + old->level= and_level; + old->optimize= KEY_OPTIMIZE_REF_OR_NULL; + /* Remember the NOT NULL value */ + if (old->val->is_null()) + old->val= new_fields->val; } - else // Impossible; remove it + else { + /* + We are comparing two different const. In this case we can't + use a key-lookup on this so it's better to remove the value + and let the range optimzier handle it + */ if (old == --first_free) // If last item break; *old= *first_free; // Remove old value @@ -1827,7 +1874,7 @@ merge_key_fields(KEY_FIELD *start,KEY_FIELD *new_fields,KEY_FIELD *end, /* Remove all not used items */ for (KEY_FIELD *old=start ; old != first_free ;) { - if (old->level != and_level && old->const_level != and_level) + if (old->level != and_level) { // Not used in all levels if (old == --first_free) break; @@ -1840,32 +1887,53 @@ merge_key_fields(KEY_FIELD *start,KEY_FIELD *new_fields,KEY_FIELD *end, } +/* + Add a possible key to array of possible keys if it's usable as a key + + SYNPOSIS + add_key_field() + key_fields Pointer to add key, if usable + and_level And level, to be stored in KEY_FIELD + field Field used in comparision + eq_func True if we used =, <=> or IS NULL + value Value used for comparison with field + Is NULL for BETWEEN and IN + usable_tables Tables which can be used for key optimization + + NOTES + If we are doing a NOT NULL comparison on a NOT NULL field in a outer join + table, we store this to be able to do not exists optimization later. + + RETURN + *key_fields is incremented if we stored a key in the array +*/ + static void add_key_field(KEY_FIELD **key_fields,uint and_level, Field *field,bool eq_func,Item *value, table_map usable_tables) { - bool exists_optimize=0; + uint exists_optimize= 0; if (!(field->flags & PART_KEY_FLAG)) { // Don't remove column IS NULL on a LEFT JOIN table if (!eq_func || !value || value->type() != Item::NULL_ITEM || !field->table->maybe_null || field->null_ptr) return; // Not a key. Skip it - exists_optimize=1; + exists_optimize= KEY_OPTIMIZE_EXISTS; } else { table_map used_tables=0; - if (value && (used_tables=value->used_tables()) & - (field->table->map | RAND_TABLE_BIT)) + if (value && ((used_tables=value->used_tables()) & + (field->table->map | RAND_TABLE_BIT))) return; if (!(usable_tables & field->table->map)) { if (!eq_func || !value || value->type() != Item::NULL_ITEM || !field->table->maybe_null || field->null_ptr) return; // Can't use left join optimize - exists_optimize=1; + exists_optimize= KEY_OPTIMIZE_EXISTS; } else { @@ -1880,20 +1948,23 @@ add_key_field(KEY_FIELD **key_fields,uint and_level, return; // Can't be used as eq key } - /* Save the following cases: - Field op constant - Field LIKE constant where constant doesn't start with a wildcard - Field = field2 where field2 is in a different table - Field op formula - Field IS NULL - Field IS NOT NULL + /* + Save the following cases: + Field op constant + Field LIKE constant where constant doesn't start with a wildcard + Field = field2 where field2 is in a different table + Field op formula + Field IS NULL + Field IS NOT NULL */ stat[0].key_dependent|=used_tables; if (value->const_item()) stat[0].const_keys |= possible_keys; - /* We can't always use indexes when comparing a string index to a - number. cmp_type() is checked to allow compare of dates to numbers */ + /* + We can't always use indexes when comparing a string index to a + number. cmp_type() is checked to allow compare of dates to numbers + */ if (!eq_func || field->result_type() == STRING_RESULT && value->result_type() != STRING_RESULT && @@ -1902,11 +1973,11 @@ add_key_field(KEY_FIELD **key_fields,uint and_level, } } /* Store possible eq field */ - (*key_fields)->field=field; - (*key_fields)->eq_func=eq_func; - (*key_fields)->val=value; - (*key_fields)->level=(*key_fields)->const_level=and_level; - (*key_fields)->exists_optimize=exists_optimize; + (*key_fields)->field= field; + (*key_fields)->eq_func= eq_func; + (*key_fields)->val= value; + (*key_fields)->level= and_level; + (*key_fields)->optimize= exists_optimize; (*key_fields)++; } @@ -1926,12 +1997,7 @@ add_key_fields(JOIN_TAB *stat,KEY_FIELD **key_fields,uint *and_level, while ((item=li++)) add_key_fields(stat,key_fields,and_level,item,usable_tables); for (; org_key_fields != *key_fields ; org_key_fields++) - { - if (org_key_fields->const_level == org_key_fields->level) - org_key_fields->const_level=org_key_fields->level= *and_level; - else - org_key_fields->const_level= *and_level; - } + org_key_fields->level= *and_level; } else { @@ -2024,7 +2090,7 @@ add_key_part(DYNAMIC_ARRAY *keyuse_array,KEY_FIELD *key_field) TABLE *form= field->table; KEYUSE keyuse; - if (key_field->eq_func && !key_field->exists_optimize) + if (key_field->eq_func && !(key_field->optimize & KEY_OPTIMIZE_EXISTS)) { for (uint key=0 ; key < form->keys ; key++) { @@ -2042,7 +2108,9 @@ add_key_part(DYNAMIC_ARRAY *keyuse_array,KEY_FIELD *key_field) keyuse.val = key_field->val; keyuse.key = key; keyuse.keypart=part; + keyuse.keypart_map= (key_part_map) 1 << part; keyuse.used_tables=key_field->val->used_tables(); + keyuse.optimize= key_field->optimize & KEY_OPTIMIZE_REF_OR_NULL; VOID(insert_dynamic(keyuse_array,(gptr) &keyuse)); } } @@ -2126,16 +2194,23 @@ add_ft_keys(DYNAMIC_ARRAY *keyuse_array, VOID(insert_dynamic(keyuse_array,(gptr) &keyuse)); } + static int sort_keyuse(KEYUSE *a,KEYUSE *b) { + int res; if (a->table->tablenr != b->table->tablenr) return (int) (a->table->tablenr - b->table->tablenr); if (a->key != b->key) return (int) (a->key - b->key); if (a->keypart != b->keypart) return (int) (a->keypart - b->keypart); - return test(a->used_tables) - test(b->used_tables); // Place const first + // Place const values before other ones + if ((res= test(a->used_tables) - test(b->used_tables))) + return res; + /* Place rows that are not 'OPTIMIZE_REF_OR_NULL' first */ + return (int) ((a->optimize & KEY_OPTIMIZE_REF_OR_NULL) - + (b->optimize & KEY_OPTIMIZE_REF_OR_NULL)); } @@ -2151,30 +2226,27 @@ update_ref_and_keys(THD *thd, DYNAMIC_ARRAY *keyuse,JOIN_TAB *join_tab, SELECT_LEX *select_lex) { uint and_level,i,found_eq_constant; + KEY_FIELD *key_fields,*end; + 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; + if (cond) + add_key_fields(join_tab,&end,&and_level,cond,normal_tables); + for (i=0 ; i < tables ; i++) { - KEY_FIELD *key_fields,*end; - - 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; - if (cond) - add_key_fields(join_tab,&end,&and_level,cond,normal_tables); - for (i=0 ; i < tables ; i++) + if (join_tab[i].on_expr) { - if (join_tab[i].on_expr) - { - add_key_fields(join_tab,&end,&and_level,join_tab[i].on_expr, - join_tab[i].table->map); - } + add_key_fields(join_tab,&end,&and_level,join_tab[i].on_expr, + 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++) - add_key_part(keyuse,field); } + 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++) + add_key_part(keyuse,field); if (select_lex->ftfunc_list->elements) { @@ -2182,9 +2254,10 @@ update_ref_and_keys(THD *thd, DYNAMIC_ARRAY *keyuse,JOIN_TAB *join_tab, } /* - Remove ref if there is a keypart which is a ref and a const. - Remove keyparts without previous keyparts. Special treatment for ft-keys. + Remove the following things from KEYUSE: + - ref if there is a keypart which is a ref and a const. + - keyparts without previous keyparts. */ if (keyuse->elements) { @@ -2202,8 +2275,7 @@ update_ref_and_keys(THD *thd, DYNAMIC_ARRAY *keyuse,JOIN_TAB *join_tab, for (i=0 ; i < keyuse->elements-1 ; i++,use++) { if (!use->used_tables) - use->table->const_key_parts[use->key] |= - (key_part_map) 1 << use->keypart; + use->table->const_key_parts[use->key]|= use->keypart_map; if (use->keypart != FT_KEYPART) { if (use->key == prev->key && use->table == prev->table) @@ -2232,6 +2304,41 @@ update_ref_and_keys(THD *thd, DYNAMIC_ARRAY *keyuse,JOIN_TAB *join_tab, return FALSE; } +/* + Update some values in keyuse for faster find_best_combination() loop +*/ + +static void optimize_keyuse(JOIN *join, DYNAMIC_ARRAY *keyuse_array) +{ + KEYUSE *end,*keyuse= dynamic_element(keyuse_array, 0, KEYUSE*); + + for (end= keyuse+ keyuse_array->elements ; keyuse < end ; keyuse++) + { + table_map map; + /* + If we find a ref, assume this table matches a proportional + part of this table. + For example 100 records matching a table with 5000 records + gives 5000/100 = 50 records per key + Constant tables are ignored. + To avoid bad matches, we don't make ref_table_rows less than 100. + */ + keyuse->ref_table_rows= ~(table_map) 0; // If no ref + if (keyuse->used_tables & + (map= (keyuse->used_tables & ~join->const_table_map & + ~OUTER_REF_TABLE_BIT))) + { + uint tablenr; + for (tablenr=0 ; ! (map & 1) ; map>>=1, tablenr++) ; + if (map == 1) // Only one table + { + TABLE *tmp_table=join->all_tables[tablenr]; + keyuse->ref_table_rows= max(tmp_table->file->records, 100); + } + } + } +} + /***************************************************************************** Go through all combinations of not marked tables and find the one @@ -2318,7 +2425,7 @@ find_best(JOIN *join,table_map rest_tables,uint idx,double record_count, uint max_key_part=0; /* Test how we can use keys */ - rec= s->records/MATCHING_ROWS_IN_OTHER_TABLE; /* Assumed records/key */ + rec= s->records/MATCHING_ROWS_IN_OTHER_TABLE; // Assumed records/key for (keyuse=s->keyuse ; keyuse->table == table ;) { key_map found_part=0; @@ -2326,44 +2433,27 @@ find_best(JOIN *join,table_map rest_tables,uint idx,double record_count, uint key=keyuse->key; KEY *keyinfo=table->key_info+key; bool ft_key=(keyuse->keypart == FT_KEYPART); + uint found_ref_or_null= 0; + /* Calculate how many key segments of the current key we can use */ start_key=keyuse; do { uint keypart=keyuse->keypart; + uint found_part_ref_or_null= KEY_OPTIMIZE_REF_OR_NULL; do { - if (!ft_key) - { - table_map map; - if (!(rest_tables & keyuse->used_tables)) - { - found_part|= (key_part_map) 1 << keypart; - found_ref|= keyuse->used_tables; - } - /* - If we find a ref, assume this table matches a proportional - part of this table. - For example 100 records matching a table with 5000 records - gives 5000/100 = 50 records per key - Constant tables are ignored and to avoid bad matches, - we don't make rec less than 100. - */ - if (keyuse->used_tables & - (map=(keyuse->used_tables & ~join->const_table_map & - ~OUTER_REF_TABLE_BIT))) - { - uint tablenr; - for (tablenr=0 ; ! (map & 1) ; map>>=1, tablenr++) ; - if (map == 1) // Only one table - { - TABLE *tmp_table=join->all_tables[tablenr]; - if (rec > tmp_table->file->records && rec > 100) - rec=max(tmp_table->file->records,100); - } - } + if (!(rest_tables & keyuse->used_tables) && + !(found_ref_or_null & keyuse->optimize)) + { + found_part|=keyuse->keypart_map; + found_ref|= keyuse->used_tables; + if (rec > keyuse->ref_table_rows) + rec= keyuse->ref_table_rows; + found_part_ref_or_null&= keyuse->optimize; } keyuse++; + found_ref_or_null|= found_part_ref_or_null; } while (keyuse->table == table && keyuse->key == key && keyuse->keypart == keypart); } while (keyuse->table == table && keyuse->key == key); @@ -2373,8 +2463,8 @@ find_best(JOIN *join,table_map rest_tables,uint idx,double record_count, */ if (!found_part && !ft_key) continue; // Nothing usable found - if (rec == 0) - rec=1L; // Fix for small tables + if (rec < MATCHING_ROWS_IN_OTHER_TABLE) + rec= MATCHING_ROWS_IN_OTHER_TABLE; // Fix for small tables /* ft-keys require special treatment @@ -2393,7 +2483,8 @@ find_best(JOIN *join,table_map rest_tables,uint idx,double record_count, /* Check if we found full key */ - if (found_part == PREV_BITS(uint,keyinfo->key_parts)) + if (found_part == PREV_BITS(uint,keyinfo->key_parts) && + !found_ref_or_null) { /* use eq key */ max_key_part= (uint) ~0; if ((keyinfo->flags & (HA_NOSAME | HA_NULL_PART_KEY)) == HA_NOSAME) @@ -2446,7 +2537,8 @@ find_best(JOIN *join,table_map rest_tables,uint idx,double record_count, Set tmp to (previous record count) * (records / combination) */ if ((found_part & 1) && - !(table->file->index_flags(key) & HA_ONLY_WHOLE_INDEX)) + (!(table->file->index_flags(key) & HA_ONLY_WHOLE_INDEX) || + found_part == PREV_BITS(uint,keyinfo->key_parts))) { max_key_part=max_part_bit(found_part); /* @@ -2496,6 +2588,12 @@ find_best(JOIN *join,table_map rest_tables,uint idx,double record_count, } records=(ulong) tmp; } + if (found_ref_or_null) + { + /* We need to do two key searches to find key */ + tmp*= 2.0; + records*= 2.0; + } } if (table->used_keys & ((key_map) 1 << key)) { @@ -2745,9 +2843,7 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, KEYUSE *org_keyuse, TABLE *table; KEY *keyinfo; - /* - Use best key from find_best - */ + /* Use best key from find_best */ table=j->table; key=keyuse->key; keyinfo=table->key_info+key; @@ -2763,14 +2859,22 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, KEYUSE *org_keyuse, else { keyparts=length=0; + uint found_part_ref_or_null= 0; + /* + Calculate length for the used key + Stop if there is a missing key part or when we find second key_part + with KEY_OPTIMIZE_REF_OR_NULL + */ do { - if (!((~used_tables) & keyuse->used_tables)) + if (!(~used_tables & keyuse->used_tables)) { - if (keyparts == keyuse->keypart) + if (keyparts == keyuse->keypart && + !(found_part_ref_or_null & keyuse->optimize)) { keyparts++; - length+=keyinfo->key_part[keyuse->keypart].store_length; + length+= keyinfo->key_part[keyuse->keypart].store_length; + found_part_ref_or_null|= keyuse->optimize; } } keyuse++; @@ -2793,8 +2897,8 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, KEYUSE *org_keyuse, j->ref.key_err=1; keyuse=org_keyuse; - store_key **ref_key=j->ref.key_copy; - byte *key_buff=j->ref.key_buff; + store_key **ref_key= j->ref.key_copy; + byte *key_buff=j->ref.key_buff, *null_ref_key= 0; if (ftkey) { j->ref.items[0]=((Item_func*)(keyuse->val))->key_item(); @@ -2822,9 +2926,7 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, KEYUSE *org_keyuse, maybe_null ? (char*) key_buff : 0, keyinfo->key_part[i].length, keyuse->val); if (thd->is_fatal_error) - { return TRUE; - } tmp.copy(); } else @@ -2832,17 +2934,25 @@ 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) + null_ref_key= key_buff; key_buff+=keyinfo->key_part[i].store_length; } } /* not ftkey */ *ref_key=0; // end_marker - if (j->type == JT_FT) /* no-op */; - else if (j->type == JT_CONST) - j->table->const_table=1; + if (j->type == JT_FT) + return 0; + if (j->type == JT_CONST) + j->table->const_table= 1; else if (((keyinfo->flags & (HA_NOSAME | HA_NULL_PART_KEY)) - != HA_NOSAME) || - keyparts != keyinfo->key_parts) - j->type=JT_REF; /* Must read with repeat */ + != HA_NOSAME) || keyparts != keyinfo->key_parts || + null_ref_key) + { + /* Must read with repeat */ + j->type= null_ref_key ? JT_REF_OR_NULL : JT_REF; + j->null_ref_key= null_ref_key; + } else if (ref_key == j->ref.key_copy) { /* @@ -3153,6 +3263,7 @@ make_join_readinfo(JOIN *join, uint options) table->file->extra(HA_EXTRA_KEYREAD); } break; + case JT_REF_OR_NULL: case JT_REF: table->status=STATUS_NO_RECORD; if (tab->select) @@ -3163,14 +3274,22 @@ make_join_readinfo(JOIN *join, uint options) delete tab->quick; tab->quick=0; table->file->index_init(tab->ref.key); - tab->read_first_record= join_read_always_key; - tab->read_record.read_record= join_read_next_same; if (table->used_keys & ((key_map) 1 << tab->ref.key) && !table->no_keyread) { table->key_read=1; table->file->extra(HA_EXTRA_KEYREAD); } + if (tab->type == JT_REF) + { + tab->read_first_record= join_read_always_key; + tab->read_record.read_record= join_read_next_same; + } + else + { + tab->read_first_record= join_read_always_key_or_null; + tab->read_record.read_record= join_read_next_same_or_null; + } break; case JT_FT: table->status=STATUS_NO_RECORD; @@ -5181,6 +5300,40 @@ flush_cached_records(JOIN *join,JOIN_TAB *join_tab,bool skipp_last) The different ways to read a record Returns -1 if row was not found, 0 if row was found and 1 on errors *****************************************************************************/ + +/* Help function when we get some an error from the table handler */ + +static int report_error(TABLE *table, int error) +{ + if (error == HA_ERR_END_OF_FILE || error == HA_ERR_KEY_NOT_FOUND) + { + table->status= STATUS_GARBAGE; + return -1; // key not found; ok + } + /* + Locking reads can legally return also these errors, do not + print them to the .err log + */ + if (error != HA_ERR_LOCK_DEADLOCK && error != HA_ERR_LOCK_WAIT_TIMEOUT) + sql_print_error("Got error %d when reading table '%s'", + error, table->path); + table->file->print_error(error,MYF(0)); + return 1; +} + + +static int safe_index_read(JOIN_TAB *tab) +{ + int error; + TABLE *table= tab->table; + if ((error=table->file->index_read(table->record[0], + tab->ref.key_buff, + tab->ref.key_length, HA_READ_KEY_EXACT))) + return report_error(table, error); + return 0; +} + + static int join_read_const_table(JOIN_TAB *tab, POSITION *pos) { @@ -5235,10 +5388,7 @@ join_read_system(JOIN_TAB *tab) table->primary_key))) { if (error != HA_ERR_END_OF_FILE) - { - table->file->print_error(error,MYF(0)); - return 1; - } + return report_error(table, error); table->null_row=1; // This is ok. empty_record(table); // Make empty record return -1; @@ -5272,15 +5422,7 @@ join_read_const(JOIN_TAB *tab) table->null_row=1; empty_record(table); if (error != HA_ERR_KEY_NOT_FOUND) - { - /* Locking reads can legally return also these errors, do not - print them to the .err log */ - if (error != HA_ERR_LOCK_DEADLOCK && error != HA_ERR_LOCK_WAIT_TIMEOUT) - sql_print_error("read_const: Got error %d when reading table %s", - error, table->path); - table->file->print_error(error,MYF(0)); - return 1; - } + return report_error(table, error); return -1; } store_record(table,record[1]); @@ -5313,12 +5455,7 @@ join_read_key(JOIN_TAB *tab) tab->ref.key_buff, tab->ref.key_length,HA_READ_KEY_EXACT); if (error && error != HA_ERR_KEY_NOT_FOUND) - { - sql_print_error("read_key: Got error %d when reading table '%s'",error, - table->path); - table->file->print_error(error,MYF(0)); - return 1; - } + return report_error(table, error); } table->null_row=0; return table->status ? -1 : 0; @@ -5338,18 +5475,13 @@ join_read_always_key(JOIN_TAB *tab) tab->ref.key_length,HA_READ_KEY_EXACT))) { if (error != HA_ERR_KEY_NOT_FOUND) - { - if (error != HA_ERR_LOCK_DEADLOCK && error != HA_ERR_LOCK_WAIT_TIMEOUT) - sql_print_error("read_const: Got error %d when reading table %s",error, - table->path); - table->file->print_error(error,MYF(0)); - return 1; - } + return report_error(table, error); return -1; /* purecov: inspected */ } return 0; } + /* This function is used when optimizing away ORDER BY in SELECT * FROM t1 WHERE a=1 ORDER BY a DESC,b DESC @@ -5368,13 +5500,7 @@ join_read_last_key(JOIN_TAB *tab) tab->ref.key_length))) { if (error != HA_ERR_KEY_NOT_FOUND) - { - if (error != HA_ERR_LOCK_DEADLOCK && error != HA_ERR_LOCK_WAIT_TIMEOUT) - sql_print_error("read_const: Got error %d when reading table %s",error, - table->path); - table->file->print_error(error,MYF(0)); - return 1; - } + return report_error(table, error); return -1; /* purecov: inspected */ } return 0; @@ -5401,19 +5527,14 @@ join_read_next_same(READ_RECORD *info) tab->ref.key_length))) { if (error != HA_ERR_END_OF_FILE) - { - if (error != HA_ERR_LOCK_DEADLOCK && error != HA_ERR_LOCK_WAIT_TIMEOUT) - sql_print_error("read_next: Got error %d when reading table %s",error, - table->path); - table->file->print_error(error,MYF(0)); - return 1; - } + return report_error(table, error); table->status= STATUS_GARBAGE; return -1; } return 0; } + static int join_read_prev_same(READ_RECORD *info) { @@ -5422,23 +5543,9 @@ join_read_prev_same(READ_RECORD *info) JOIN_TAB *tab=table->reginfo.join_tab; if ((error=table->file->index_prev(table->record[0]))) - { - if (error != HA_ERR_END_OF_FILE) - { - if (error != HA_ERR_LOCK_DEADLOCK && error != HA_ERR_LOCK_WAIT_TIMEOUT) - sql_print_error("read_next: Got error %d when reading table %s",error, - table->path); - table->file->print_error(error,MYF(0)); - error= 1; - } - else - { - table->status= STATUS_GARBAGE; - error= -1; - } - } - else if (key_cmp(table, tab->ref.key_buff, tab->ref.key, - tab->ref.key_length)) + return report_error(table, error); + if (key_cmp(table, tab->ref.key_buff, tab->ref.key, + tab->ref.key_length)) { table->status=STATUS_NOT_FOUND; error= -1; @@ -5475,6 +5582,7 @@ join_init_read_record(JOIN_TAB *tab) return (*tab->read_record.read_record)(&tab->read_record); } + static int join_read_first(JOIN_TAB *tab) { @@ -5492,17 +5600,10 @@ join_read_first(JOIN_TAB *tab) tab->read_record.file=table->file; tab->read_record.index=tab->index; tab->read_record.record=table->record[0]; - error=tab->table->file->index_first(tab->table->record[0]); - if (error) + if ((error=tab->table->file->index_first(tab->table->record[0]))) { if (error != HA_ERR_KEY_NOT_FOUND && error != HA_ERR_END_OF_FILE) - { - if (error != HA_ERR_LOCK_DEADLOCK && error != HA_ERR_LOCK_WAIT_TIMEOUT) - sql_print_error("read_first_with_key: Got error %d when reading table", - error); - table->file->print_error(error,MYF(0)); - return 1; - } + report_error(table, error); return -1; } return 0; @@ -5512,23 +5613,13 @@ join_read_first(JOIN_TAB *tab) static int join_read_next(READ_RECORD *info) { - int error=info->file->index_next(info->record); - if (error) - { - if (error != HA_ERR_END_OF_FILE) - { - if (error != HA_ERR_LOCK_DEADLOCK && error != HA_ERR_LOCK_WAIT_TIMEOUT) - sql_print_error( - "read_next_with_key: Got error %d when reading table %s", - error, info->table->path); - info->file->print_error(error,MYF(0)); - return 1; - } - return -1; - } + int error; + if ((error=info->file->index_next(info->record))) + return report_error(info->table, error); return 0; } + static int join_read_last(JOIN_TAB *tab) { @@ -5546,19 +5637,8 @@ join_read_last(JOIN_TAB *tab) tab->read_record.file=table->file; tab->read_record.index=tab->index; tab->read_record.record=table->record[0]; - error=tab->table->file->index_last(tab->table->record[0]); - if (error) - { - if (error != HA_ERR_END_OF_FILE) - { - if (error != HA_ERR_LOCK_DEADLOCK && error != HA_ERR_LOCK_WAIT_TIMEOUT) - sql_print_error("read_last_with_key: Got error %d when reading table", - error, table->path); - table->file->print_error(error,MYF(0)); - return 1; - } - return -1; - } + if ((error= tab->table->file->index_last(tab->table->record[0]))) + return report_error(table, error); return 0; } @@ -5566,20 +5646,9 @@ join_read_last(JOIN_TAB *tab) static int join_read_prev(READ_RECORD *info) { - int error=info->file->index_prev(info->record); - if (error) - { - if (error != HA_ERR_END_OF_FILE) - { - if (error != HA_ERR_LOCK_DEADLOCK && error != HA_ERR_LOCK_WAIT_TIMEOUT) - sql_print_error( - "read_prev_with_key: Got error %d when reading table: %s", - error,info->table->path); - info->file->print_error(error,MYF(0)); - return 1; - } - return -1; - } + int error; + if ((error= info->file->index_prev(info->record))) + return report_error(info->table, error); return 0; } @@ -5596,42 +5665,57 @@ join_ft_read_first(JOIN_TAB *tab) #endif table->file->ft_init(); - error=table->file->ft_read(table->record[0]); - if (error) - { - if (error != HA_ERR_END_OF_FILE) - { - if (error != HA_ERR_LOCK_DEADLOCK && error != HA_ERR_LOCK_WAIT_TIMEOUT) - sql_print_error("ft_read_first: Got error %d when reading table %s", - error, table->path); - table->file->print_error(error,MYF(0)); - return 1; - } - return -1; - } + if ((error= table->file->ft_read(table->record[0]))) + return report_error(table, error); return 0; } static int join_ft_read_next(READ_RECORD *info) { - int error=info->file->ft_read(info->table->record[0]); - if (error) - { - if (error != HA_ERR_END_OF_FILE) - { - if (error != HA_ERR_LOCK_DEADLOCK && error != HA_ERR_LOCK_WAIT_TIMEOUT) - sql_print_error("ft_read_next: Got error %d when reading table %s", - error, info->table->path); - info->file->print_error(error,MYF(0)); - return 1; - } - return -1; - } + int error; + if ((error= info->file->ft_read(info->table->record[0]))) + return report_error(info->table, error); return 0; } +/* + Reading of key with key reference and one part that may be NULL +*/ + +static int +join_read_always_key_or_null(JOIN_TAB *tab) +{ + int res; + + /* First read according to key which is NOT NULL */ + *tab->null_ref_key=0; + if ((res= join_read_always_key(tab)) >= 0) + return res; + + /* Then read key with null value */ + *tab->null_ref_key= 1; + return safe_index_read(tab); +} + + +static int +join_read_next_same_or_null(READ_RECORD *info) +{ + int error; + if ((error= join_read_next_same(info)) >= 0) + return error; + JOIN_TAB *tab= info->table->reginfo.join_tab; + + /* Test if we have already done a read after null key */ + if (*tab->null_ref_key) + return -1; // All keys read + *tab->null_ref_key= 1; // Read null key + return safe_index_read(tab); +} + + /***************************************************************************** The different end of select functions These functions returns < 0 when end is reached, 0 on ok and > 0 if a @@ -6358,10 +6442,13 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit, } ref_key= -1; - if (tab->ref.key >= 0) // Constant range in WHERE + /* Test if constant range in WHERE */ + if (tab->ref.key >= 0) { ref_key= tab->ref.key; ref_key_parts= tab->ref.key_parts; + if (tab->type == JT_REF_OR_NULL) + DBUG_RETURN(0); } else if (select && select->quick) // Range found by opt_range { diff --git a/sql/sql_select.h b/sql/sql_select.h index df21d337b54..f29729fb5b3 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -27,8 +27,10 @@ typedef struct keyuse_t { TABLE *table; Item *val; /* or value if no field */ - uint key,keypart; table_map used_tables; + uint key, keypart, optimize; + key_map keypart_map; + ha_rows ref_table_rows; } KEYUSE; class store_key; @@ -73,7 +75,7 @@ typedef struct st_join_cache { */ enum join_type { JT_UNKNOWN,JT_SYSTEM,JT_CONST,JT_EQ_REF,JT_REF,JT_MAYBE_REF, - JT_ALL, JT_RANGE, JT_NEXT, JT_FT}; + JT_ALL, JT_RANGE, JT_NEXT, JT_FT, JT_REF_OR_NULL}; class JOIN; @@ -85,6 +87,7 @@ typedef struct st_join_table { QUICK_SELECT *quick; Item *on_expr; const char *info; + byte *null_ref_key; int (*read_first_record)(struct st_join_table *tab); int (*next_select)(JOIN *,struct st_join_table *,bool); READ_RECORD read_record; diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index e3fb81b39f2..435c892db63 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -1597,6 +1597,7 @@ alter_list_item: LEX *lex=Lex; lex->length=lex->dec=0; lex->type=0; lex->interval=0; lex->default_value=lex->comment=0; + lex->charset= NULL; lex->simple_alter=0; } type opt_attribute diff --git a/sql/table.cc b/sql/table.cc index 908d6807450..0fc2a09f749 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -395,7 +395,9 @@ int openfrm(const char *name, const char *alias, uint db_stat, uint prgflag, } else { - if (!(charset=get_charset((uint) strpos[14], MYF(0)))) + if (!strpos[14]) + charset= &my_charset_bin; + else if (!(charset=get_charset((uint) strpos[14], MYF(0)))) charset= (outparam->table_charset ? outparam->table_charset: default_charset_info); } diff --git a/sql/unireg.cc b/sql/unireg.cc index 5e723281d3f..3e634f54b4f 100644 --- a/sql/unireg.cc +++ b/sql/unireg.cc @@ -460,8 +460,10 @@ static bool pack_fields(File file,List<create_field> &create_fields) buff[13]= (uchar) field->sql_type; if (field->sql_type == FIELD_TYPE_GEOMETRY) buff[14]= (uchar) field->geom_type; - else + else if (field->charset) buff[14]= (uchar) field->charset->number; + else + buff[14]= 0; // Numerical int2store(buff+15, field->comment.length); comment_length+= field->comment.length; set_if_bigger(int_count,field->interval_id); |