[prev in list] [next in list] [prev in thread] [next in thread]
List: mysql-internals
Subject: bk commit into 4.1 tree (1.1572)
From: monty () mysql ! com
Date: 2003-06-30 10:24:53
[Download RAW message or body]
Below is the list of changes that have just been committed into a local
4.1 repository of monty. When monty does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://www.mysql.com/doc/I/n/Installing_source_tree.html
ChangeSet
1.1572 03/06/30 13:23:54 monty@mashka.mysql.fi +10 -0
Remove FORCE_INIT_OF_VARS when compiling for valgrind/purify to spot wrong \
LINT_INIT() options Fixed bug in ALTER TABLE ... MODIFY integer-column
Added ref_or_null optimization (needed for subqueries)
sql/unireg.cc
1.27 03/06/30 13:23:50 monty@mashka.mysql.fi +3 -1
Safety fix for ALTER TABLE .. MODIFY
sql/table.cc
1.80 03/06/30 13:23:50 monty@mashka.mysql.fi +3 -1
Safety fix for ALTER TABLE .. MODIFY
sql/sql_yacc.yy
1.253 03/06/30 13:23:50 monty@mashka.mysql.fi +1 -0
Fixed bug in ALTER TABLE ... MODIFY integer-column
sql/sql_select.h
1.45 03/06/30 13:23:50 monty@mashka.mysql.fi +5 -2
Added ref_or_null optimization
sql/sql_select.cc
1.227 03/06/30 13:23:50 monty@mashka.mysql.fi +346 -259
Added ref_or_null optimization
Optimized find_best_combinations() and read-functions
mysql-test/t/null_key.test
1.8 03/06/30 13:23:50 monty@mashka.mysql.fi +34 -1
New tests for ref_or_null optimization
mysql-test/r/subselect.result
1.77 03/06/30 13:23:50 monty@mashka.mysql.fi +1 -1
Update after ref_or_null optimization
mysql-test/r/null_key.result
1.20 03/06/30 13:23:50 monty@mashka.mysql.fi +106 -4
Update after ref_or_null optimization
mysql-test/r/distinct.result
1.24 03/06/30 13:23:50 monty@mashka.mysql.fi +3 -3
Update of test results (new optimizer)
BUILD/compile-pentium-valgrind-max
1.4 03/06/30 13:23:50 monty@mashka.mysql.fi +1 -1
Remove FORCE_INIT_OF_VARS to spot wrong LINT_INIT() options
# This is a BitKeeper patch. What follows are the unified diffs for the
# set of deltas contained in the patch. The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User: monty
# Host: mashka.mysql.fi
# Root: /home/my/mysql-4.1
--- 1.226/sql/sql_select.cc Tue Jun 24 11:37:08 2003
+++ 1.227/sql/sql_select.cc Mon Jun 30 13:23:50 2003
@@ -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(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 @@
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 @@
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 @@
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 @@
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 @@
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 @@
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 @@
{
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)
- {
- old->level=old->const_level=and_level;
- old->exists_optimize&=new_fields->exists_optimize;
+ 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()))
+ {
+ /* 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 @@
/* 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 @@
}
+/*
+ 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 @@
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 @@
}
}
/* 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 @@
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 @@
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 @@
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 @@
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 @@
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)
- {
- 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 (join_tab[i].on_expr)
+ {
+ 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 (select_lex->ftfunc_list->elements)
{
@@ -2182,9 +2254,10 @@
}
/*
- 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 @@
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 @@
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 @@
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 @@
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 @@
*/
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 @@
/*
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 @@
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 @@
}
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 @@
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 @@
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 @@
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 @@
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 @@
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 @@
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 @@
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 @@
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 @@
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 @@
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 @@
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 @@
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 @@
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 @@
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_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 @@
return (*tab->read_record.read_record)(&tab->read_record);
}
+
static int
join_read_first(JOIN_TAB *tab)
{
@@ -5492,17 +5600,10 @@
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 @@
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 @@
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 @@
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 @@
#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 @@
}
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
{
--- 1.44/sql/sql_select.h Wed Jun 4 18:28:48 2003
+++ 1.45/sql/sql_select.h Mon Jun 30 13:23:50 2003
@@ -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 @@
*/
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 @@
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;
--- 1.252/sql/sql_yacc.yy Fri Jun 20 13:40:18 2003
+++ 1.253/sql/sql_yacc.yy Mon Jun 30 13:23:50 2003
@@ -1597,6 +1597,7 @@
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
--- 1.79/sql/table.cc Fri May 30 00:47:23 2003
+++ 1.80/sql/table.cc Mon Jun 30 13:23:50 2003
@@ -395,7 +395,9 @@
}
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);
}
--- 1.26/sql/unireg.cc Thu Mar 27 11:09:07 2003
+++ 1.27/sql/unireg.cc Mon Jun 30 13:23:50 2003
@@ -460,8 +460,10 @@
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);
--- 1.76/mysql-test/r/subselect.result Thu Jun 5 00:01:45 2003
+++ 1.77/mysql-test/r/subselect.result Mon Jun 30 13:23:50 2003
@@ -827,7 +827,7 @@
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);
--- 1.23/mysql-test/r/distinct.result Wed Jun 4 18:28:46 2003
+++ 1.24/mysql-test/r/distinct.result Mon Jun 30 13:23:50 2003
@@ -173,9 +173,9 @@
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
--- 1.19/mysql-test/r/null_key.result Wed May 21 21:40:32 2003
+++ 1.20/mysql-test/r/null_key.result Mon Jun 30 13:23:50 2003
@@ -21,7 +21,10 @@
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
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 @@
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
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 @@
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,
--- 1.7/mysql-test/t/null_key.test Mon Jan 6 01:48:52 2003
+++ 1.8/mysql-test/t/null_key.test Mon Jun 30 13:23:50 2003
@@ -14,6 +14,7 @@
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 > 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 = 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
--- 1.3/BUILD/compile-pentium-valgrind-max Tue May 13 11:15:05 2003
+++ 1.4/BUILD/compile-pentium-valgrind-max Mon Jun 30 13:23:50 2003
@@ -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"
--
MySQL Internals Mailing List
For list archives: http://lists.mysql.com/internals
To unsubscribe: http://lists.mysql.com/internals?unsub=mysql-internals@progressive-comp.com
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic