Below is the list of changes that have just been committed into a local
5.0 repository of alexi. When alexi 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://dev.mysql.com/doc/mysql/en/installing-source-tree.html
ChangeSet
1.1898 05/08/26 23:21:17 aivanov@stripped +6 -0
Merge mysql.com:/home/alexi/mysql-4.1
into mysql.com:/home/alexi/dev/mysql-5.0-0
sql/sql_yacc.yy
1.419 05/08/26 23:21:08 aivanov@stripped +14 -16
Manual merge
sql/opt_range.cc
1.184 05/08/26 23:21:08 aivanov@stripped +43 -78
Manual merge
sql/item_cmpfunc.h
1.108 05/08/26 23:21:08 aivanov@stripped +3 -3
Manual merge
mysql-test/t/join_outer.test
1.33 05/08/26 23:21:08 aivanov@stripped +47 -48
Manual merge
mysql-test/r/join_outer.result
1.43 05/08/26 23:21:08 aivanov@stripped +49 -161
Manual merge
sql/item_cmpfunc.cc
1.171 05/08/26 20:33:47 aivanov@stripped +0 -1
Auto merged
# 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: aivanov
# Host: mysql.creware.com
# Root: /home/alexi/dev/mysql-5.0-0/RESYNC
--- 1.170/sql/item_cmpfunc.cc 2005-08-22 02:13:18 +04:00
+++ 1.171/sql/item_cmpfunc.cc 2005-08-26 20:33:47 +04:00
@@ -988,6 +988,53 @@
}
+/*
+ Perform context analysis of a BETWEEN item tree
+
+ SYNOPSIS:
+ fix_fields()
+ thd reference to the global context of the query thread
+ tables list of all open tables involved in the query
+ ref pointer to Item* variable where pointer to resulting "fixed"
+ item is to be assigned
+
+ DESCRIPTION
+ This function performs context analysis (name resolution) and calculates
+ various attributes of the item tree with Item_func_between as its root.
+ The function saves in ref the pointer to the item or to a newly created
+ item that is considered as a replacement for the original one.
+
+ NOTES
+ Let T0(e)/T1(e) be the value of not_null_tables(e) when e is used on
+ a predicate/function level. Then it's easy to show that:
+ T0(e BETWEEN e1 AND e2) = union(T1(e),T1(e1),T1(e2))
+ T1(e BETWEEN e1 AND e2) = union(T1(e),intersection(T1(e1),T1(e2)))
+ T0(e NOT BETWEEN e1 AND e2) = union(T1(e),intersection(T1(e1),T1(e2)))
+ T1(e NOT BETWEEN e1 AND e2) = union(T1(e),intersection(T1(e1),T1(e2)))
+
+ RETURN
+ 0 ok
+ 1 got error
+*/
+
+bool
+Item_func_between::fix_fields(THD *thd, struct st_table_list *tables, Item **ref)
+{
+ if (Item_func_opt_neg::fix_fields(thd, tables, ref))
+ return 1;
+
+ /* not_null_tables_cache == union(T1(e),T1(e1),T1(e2)) */
+ if (pred_level && !negated)
+ return 0;
+
+ /* not_null_tables_cache == union(T1(e), intersect(T1(e1),T1(e2))) */
+ not_null_tables_cache= args[0]->not_null_tables() |
+ (args[1]->not_null_tables() & args[2]->not_null_tables());
+
+ return 0;
+}
+
+
void Item_func_between::fix_length_and_dec()
{
max_length= 1;
@@ -1040,8 +1087,9 @@
a=args[1]->val_str(&value1);
b=args[2]->val_str(&value2);
if (!args[1]->null_value && !args[2]->null_value)
- return (sortcmp(value,a,cmp_collation.collation) >= 0 &&
- sortcmp(value,b,cmp_collation.collation) <= 0) ? 1 : 0;
+ return (longlong) ((sortcmp(value,a,cmp_collation.collation) >= 0 &&
+ sortcmp(value,b,cmp_collation.collation) <= 0) !=
+ negated);
if (args[1]->null_value && args[2]->null_value)
null_value=1;
else if (args[1]->null_value)
@@ -1063,7 +1111,7 @@
a=args[1]->val_int();
b=args[2]->val_int();
if (!args[1]->null_value && !args[2]->null_value)
- return (value >= a && value <= b) ? 1 : 0;
+ return (longlong) ((value >= a && value <= b) != negated);
if (args[1]->null_value && args[2]->null_value)
null_value=1;
else if (args[1]->null_value)
@@ -1101,7 +1149,7 @@
a= args[1]->val_real();
b= args[2]->val_real();
if (!args[1]->null_value && !args[2]->null_value)
- return (value >= a && value <= b) ? 1 : 0;
+ return (longlong) ((value >= a && value <= b) != negated);
if (args[1]->null_value && args[2]->null_value)
null_value=1;
else if (args[1]->null_value)
@@ -1113,7 +1161,7 @@
null_value= value >= a;
}
}
- return 0;
+ return (longlong) (!null_value && negated);
}
@@ -1244,6 +1292,49 @@
}
+/*
+ Perform context analysis of an IF item tree
+
+ SYNOPSIS:
+ fix_fields()
+ thd reference to the global context of the query thread
+ tables list of all open tables involved in the query
+ ref pointer to Item* variable where pointer to resulting "fixed"
+ item is to be assigned
+
+ DESCRIPTION
+ This function performs context analysis (name resolution) and calculates
+ various attributes of the item tree with Item_func_if as its root.
+ The function saves in ref the pointer to the item or to a newly created
+ item that is considered as a replacement for the original one.
+
+ NOTES
+ Let T0(e)/T1(e) be the value of not_null_tables(e) when e is used on
+ a predicate/function level. Then it's easy to show that:
+ T0(IF(e,e1,e2) = T1(IF(e,e1,e2))
+ T1(IF(e,e1,e2)) = intersection(T1(e1),T1(e2))
+
+ RETURN
+ 0 ok
+ 1 got error
+*/
+
+bool
+Item_func_if::fix_fields(THD *thd, struct st_table_list *tlist, Item **ref)
+{
+ DBUG_ASSERT(fixed == 0);
+ args[0]->top_level_item();
+
+ if (Item_func::fix_fields(thd, tlist, ref))
+ return 1;
+
+ not_null_tables_cache= (args[1]->not_null_tables()
+ & args[2]->not_null_tables());
+
+ return 0;
+}
+
+
void
Item_func_if::fix_length_and_dec()
{
@@ -2184,6 +2275,56 @@
}
+/*
+ Perform context analysis of an IN item tree
+
+ SYNOPSIS:
+ fix_fields()
+ thd reference to the global context of the query thread
+ tables list of all open tables involved in the query
+ ref pointer to Item* variable where pointer to resulting "fixed"
+ item is to be assigned
+
+ DESCRIPTION
+ This function performs context analysis (name resolution) and calculates
+ various attributes of the item tree with Item_func_in as its root.
+ The function saves in ref the pointer to the item or to a newly created
+ item that is considered as a replacement for the original one.
+
+ NOTES
+ Let T0(e)/T1(e) be the value of not_null_tables(e) when e is used on
+ a predicate/function level. Then it's easy to show that:
+ T0(e IN(e1,...,en)) = union(T1(e),intersection(T1(ei)))
+ T1(e IN(e1,...,en)) = union(T1(e),intersection(T1(ei)))
+ T0(e NOT IN(e1,...,en)) = union(T1(e),union(T1(ei)))
+ T1(e NOT IN(e1,...,en)) = union(T1(e),intersection(T1(ei)))
+
+ RETURN
+ 0 ok
+ 1 got error
+*/
+
+bool
+Item_func_in::fix_fields(THD *thd, TABLE_LIST *tables, Item **ref)
+{
+ Item **arg, **arg_end;
+
+ if (Item_func_opt_neg::fix_fields(thd, tables, ref))
+ return 1;
+
+ /* not_null_tables_cache == union(T1(e),union(T1(ei))) */
+ if (pred_level && negated)
+ return 0;
+
+ /* not_null_tables_cache = union(T1(e),intersection(T1(ei))) */
+ not_null_tables_cache= ~(table_map) 0;
+ for (arg= args + 1, arg_end= args + arg_count; arg != arg_end; arg++)
+ not_null_tables_cache&= (*arg)->not_null_tables();
+ not_null_tables_cache|= (*args)->not_null_tables();
+ return 0;
+}
+
+
static int srtcmp_in(CHARSET_INFO *cs, const String *x,const String *y)
{
return cs->coll->strnncollsp(cs,
@@ -2283,7 +2424,7 @@
{
int tmp=array->find(args[0]);
null_value=args[0]->null_value || (!tmp && have_null);
- return tmp;
+ return (longlong) (!null_value && tmp != negated);
}
in_item->store_value(args[0]);
if ((null_value=args[0]->null_value))
@@ -2292,11 +2433,11 @@
for (uint i=1 ; i < arg_count ; i++)
{
if (!in_item->cmp(args[i]) && !args[i]->null_value)
- return 1; // Would maybe be nice with i ?
+ return (longlong) (!negated);
have_null|= args[i]->null_value;
}
null_value= have_null;
- return 0;
+ return (longlong) (!null_value && negated);
}
--- 1.107/sql/item_cmpfunc.h 2005-08-18 13:07:10 +04:00
+++ 1.108/sql/item_cmpfunc.h 2005-08-26 23:21:08 +04:00
@@ -401,17 +401,49 @@
};
-class Item_func_between :public Item_int_func
+/*
+ The class Item_func_opt_neg is defined to factor out the functionality
+ common for the classes Item_func_between and Item_func_in. The objects
+ of these classes can express predicates or there negations.
+ The alternative approach would be to create pairs Item_func_between,
+ Item_func_notbetween and Item_func_in, Item_func_notin.
+
+*/
+
+class Item_func_opt_neg :public Item_int_func
+{
+public:
+ bool negated; /* <=> the item represents NOT <func> */
+ bool pred_level; /* <=> [NOT] <func> is used on a predicate level */
+public:
+ Item_func_opt_neg(Item *a, Item *b, Item *c)
+ :Item_int_func(a, b, c), negated(0), pred_level(0) {}
+ Item_func_opt_neg(List<Item> &list)
+ :Item_int_func(list), negated(0), pred_level(0) {}
+public:
+ inline void negate() { negated= !negated; }
+ inline void top_level_item() { pred_level= 1; }
+ Item *neg_transformer(THD *thd)
+ {
+ negated= !negated;
+ return this;
+ }
+};
+
+
+class Item_func_between :public Item_func_opt_neg
{
DTCollation cmp_collation;
public:
Item_result cmp_type;
String value0,value1,value2;
- Item_func_between(Item *a,Item *b,Item *c) :Item_int_func(a,b,c) {}
+ Item_func_between(Item *a, Item *b, Item *c)
+ :Item_func_opt_neg(a, b, c) {}
longlong val_int();
optimize_type select_optimize() const { return OPTIMIZE_KEY; }
enum Functype functype() const { return BETWEEN; }
const char *func_name() const { return "between"; }
+ bool fix_fields(THD *, Item **);
void fix_length_and_dec();
void print(String *str);
bool is_bool_func() { return 1; }
@@ -504,16 +536,10 @@
String *val_str(String *str);
my_decimal *val_decimal(my_decimal *);
enum Item_result result_type () const { return cached_result_type; }
- bool fix_fields(THD *thd, Item **ref)
- {
- DBUG_ASSERT(fixed == 0);
- args[0]->top_level_item();
- return Item_func::fix_fields(thd, ref);
- }
+ bool fix_fields(THD *, Item **);
void fix_length_and_dec();
uint decimal_precision() const;
const char *func_name() const { return "if"; }
- table_map not_null_tables() const { return 0; }
};
@@ -818,7 +844,7 @@
}
};
-class Item_func_in :public Item_int_func
+class Item_func_in :public Item_func_opt_neg
{
Item_result cmp_type;
in_vector *array;
@@ -827,11 +853,12 @@
DTCollation cmp_collation;
public:
Item_func_in(List<Item> &list)
- :Item_int_func(list), array(0), in_item(0), have_null(0)
+ :Item_func_opt_neg(list), array(0), in_item(0), have_null(0)
{
allowed_arg_cols= 0; // Fetch this value from first argument
}
longlong val_int();
+ bool fix_fields(THD *, Item **);
void fix_length_and_dec();
uint decimal_precision() const { return 1; }
void cleanup()
@@ -852,12 +879,6 @@
bool nulls_in_row();
bool is_bool_func() { return 1; }
CHARSET_INFO *compare_collation() { return cmp_collation.collation; }
- /*
- IN() protect from NULL only first argument, if construction like
- "expression IN ()" will be allowed, we will need to check number of
- argument here, because "NOT(NULL IN ())" is TRUE.
- */
- table_map not_null_tables() const { return args[0]->not_null_tables(); }
};
/* Functions used by where clause */
--- 1.183/sql/opt_range.cc 2005-07-18 19:36:11 +04:00
+++ 1.184/sql/opt_range.cc 2005-08-26 23:21:08 +04:00
@@ -3538,7 +3538,6 @@
}
else if (cond_func->select_optimize() == Item_func::OPTIMIZE_NONE)
DBUG_RETURN(0);
-
param->cond= cond;
switch (cond_func->functype()) {
@@ -3551,90 +3550,59 @@
case Item_func::IN_FUNC:
{
Item_func_in *func=(Item_func_in*) cond_func;
- if (func->key_item()->type() != Item::FIELD_ITEM)
- DBUG_RETURN(0);
- field_item= (Item_field*) (func->key_item());
- value= NULL;
- break;
- }
- case Item_func::MULT_EQUAL_FUNC:
- {
- Item_equal *item_equal= (Item_equal *) cond;
- if (!(value= item_equal->get_const()))
- DBUG_RETURN(0);
- Item_equal_iterator it(*item_equal);
- ref_tables= value->used_tables();
- while ((field_item= it++))
+ if (func->key_item()->type() == Item::FIELD_ITEM)
{
- Field *field= field_item->field;
- Item_result cmp_type= field->cmp_type();
- if (!((ref_tables | field->table->map) & param_comp))
+ Field *field=((Item_field*) (func->key_item()))->field;
+ Item_result cmp_type=field->cmp_type();
+ tree= get_mm_parts(param,cond_func,field,Item_func::EQ_FUNC,
+ func->arguments()[1],cmp_type);
+ if (!tree)
+ DBUG_RETURN(tree); // Not key field
+ for (uint i=2 ; i < func->argument_count(); i++)
{
- tree= get_mm_parts(param, cond, field, Item_func::EQ_FUNC,
- value,cmp_type);
- ftree= !ftree ? tree : tree_and(param, ftree, tree);
+ SEL_TREE *new_tree=get_mm_parts(param,cond_func,field,
+ Item_func::EQ_FUNC,
+ func->arguments()[i],cmp_type);
+ tree=tree_or(param,tree,new_tree);
}
+ DBUG_RETURN(tree);
}
-
- DBUG_RETURN(ftree);
- }
- default:
- if (cond_func->arguments()[0]->real_item()->type() == Item::FIELD_ITEM)
- {
- field_item= (Item_field*) (cond_func->arguments()[0]->real_item());
- value= cond_func->arg_count > 1 ? cond_func->arguments()[1] : 0;
- }
- else if (cond_func->have_rev_func() &&
- cond_func->arguments()[1]->real_item()->type() ==
- Item::FIELD_ITEM)
- {
- field_item= (Item_field*) (cond_func->arguments()[1]->real_item());
- value= cond_func->arguments()[0];
- }
- else
- DBUG_RETURN(0);
+ DBUG_RETURN(0); // Can't optimize this IN
}
- /*
- If the where condition contains a predicate (ti.field op const),
- then not only SELL_TREE for this predicate is built, but
- the trees for the results of substitution of ti.field for
- each tj.field belonging to the same multiple equality as ti.field
- are built as well.
- E.g. for WHERE t1.a=t2.a AND t2.a > 10
- a SEL_TREE for t2.a > 10 will be built for quick select from t2
- and
- a SEL_TREE for t1.a > 10 will be built for quick select from t1.
- */
-
- for (uint i= 0; i < cond_func->arg_count; i++)
+ if (ref_tables & ~(param->prev_tables | param->read_tables |
+ param->current_table))
+ DBUG_RETURN(0); // Can't be calculated yet
+ if (!(ref_tables & param->current_table))
+ DBUG_RETURN(new SEL_TREE(SEL_TREE::MAYBE)); // This may be false or true
+
+ /* check field op const */
+ /* btw, ft_func's arguments()[0] isn't FIELD_ITEM. SerG*/
+ if (cond_func->arguments()[0]->type() == Item::FIELD_ITEM)
{
- Item *arg= cond_func->arguments()[i]->real_item();
- if (arg != field_item)
- ref_tables|= arg->used_tables();
+ tree= get_mm_parts(param, cond_func,
+ ((Item_field*) (cond_func->arguments()[0]))->field,
+ cond_func->functype(),
+ cond_func->arg_count > 1 ? cond_func->arguments()[1] :
+ 0,
+ ((Item_field*) (cond_func->arguments()[0]))->field->
+ cmp_type());
}
- Field *field= field_item->field;
- Item_result cmp_type= field->cmp_type();
- if (!((ref_tables | field->table->map) & param_comp))
- ftree= get_func_mm_tree(param, cond_func, field, value, cmp_type, inv);
- Item_equal *item_equal= field_item->item_equal;
- if (item_equal)
+ /* check const op field */
+ if (!tree &&
+ cond_func->have_rev_func() &&
+ cond_func->arguments()[1]->type() == Item::FIELD_ITEM)
{
- Item_equal_iterator it(*item_equal);
- Item_field *item;
- while ((item= it++))
- {
- Field *f= item->field;
- if (field->eq(f))
- continue;
- if (!((ref_tables | f->table->map) & param_comp))
- {
- tree= get_func_mm_tree(param, cond_func, f, value, cmp_type, inv);
- ftree= !ftree ? tree : tree_and(param, ftree, tree);
- }
- }
+ DBUG_RETURN(get_mm_parts(param, cond_func,
+ ((Item_field*)
+ (cond_func->arguments()[1]))->field,
+ ((Item_bool_func2*) cond_func)->rev_functype(),
+ cond_func->arguments()[0],
+ ((Item_field*)
+ (cond_func->arguments()[1]))->field->cmp_type()
+ ));
}
- DBUG_RETURN(ftree);
+ DBUG_RETURN(tree);
}
--- 1.418/sql/sql_yacc.yy 2005-08-25 17:39:01 +04:00
+++ 1.419/sql/sql_yacc.yy 2005-08-26 23:21:08 +04:00
@@ -4325,6 +4325,31 @@
| ANY_SYM { $$ = 0; }
;
+/* expressions that begin with 'expr' */
+expr_expr:
+ expr IN_SYM '(' expr_list ')'
+ { $4->push_front($1); $$= new Item_func_in(*$4); }
+ | expr NOT IN_SYM '(' expr_list ')'
+ {
+ $5->push_front($1);
+ Item_func_in *item= new Item_func_in(*$5);
+ item->negate();
+ $$= item;
+ }
+ | expr IN_SYM in_subselect
+ { $$= new Item_in_subselect($1, $3); }
+ | expr NOT IN_SYM in_subselect
+ {
+ $$= new Item_func_not(new Item_in_subselect($1, $4));
+ }
+ | expr BETWEEN_SYM no_and_expr AND_SYM expr
+ { $$= new Item_func_between($1,$3,$5); }
+ | expr NOT BETWEEN_SYM no_and_expr AND_SYM expr
+ {
+ Item_func_between *item= new Item_func_between($1,$4,$6);
+ item->negate();
+ $$= item;
+ }
interval_expr:
INTERVAL_SYM expr { $$=$2; }
;
--- 1.42/mysql-test/r/join_outer.result 2005-08-23 19:08:00 +04:00
+++ 1.43/mysql-test/r/join_outer.result 2005-08-26 23:21:08 +04:00
@@ -200,7 +200,7 @@
INSERT INTO t1 VALUES (11403,'Projecte Fi de Carrera','Proyecto Fin de Carrera','Projecte Fi de Carrera','PFC',9.0,NULL,NULL,NULL);
INSERT INTO t1 VALUES (11404,'+lgebra lineal','Algebra lineal','+lgebra lineal','+lgebra lineal',15.0,NULL,NULL,NULL);
INSERT INTO t1 VALUES (11405,'+lgebra lineal','Algebra lineal','+lgebra lineal','+lgebra lineal',18.0,NULL,NULL,NULL);
-INSERT INTO t1 VALUES (11406,'Calcul Infinitesimal','Cßlculo Infinitesimal','Calcul Infinitesimal','Calcul Infinitesimal',15.0,NULL,NULL,NULL);
+INSERT INTO t1 VALUES (11406,'Calcul Infinitesimal','Cßlculo Infinitesimal','Calcul Infinitesimal','Calcul Infinitesimal',15.0,NULL,NULL,NULL);
CREATE TABLE t2 (
idAssignatura int(11) DEFAULT '0' NOT NULL,
Grup int(11) DEFAULT '0' NOT NULL,
@@ -654,7 +654,6 @@
i
1
select * from t1 natural left join t2 where (t2.i is not null) is not null;
-i
1
2
select * from t1 natural left join t2 where (i is not null)=0;
@@ -842,66 +841,7 @@
id text_id text_data
1 0 0-SV
2 10 10-SV
-DROP TABLE t1, t2;
-CREATE TABLE t0 (a0 int PRIMARY KEY);
-CREATE TABLE t1 (a1 int PRIMARY KEY);
-CREATE TABLE t2 (a2 int);
-CREATE TABLE t3 (a3 int);
-INSERT INTO t0 VALUES (1);
-INSERT INTO t1 VALUES (1);
-INSERT INTO t2 VALUES (1), (2);
-INSERT INTO t3 VALUES (1), (2);
-SELECT * FROM t1 LEFT JOIN t2 ON a1=0;
-a1 a2
-1 NULL
-EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON a1=0;
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 system NULL NULL NULL NULL 1
-1 SIMPLE t2 ALL NULL NULL NULL NULL 2
-SELECT * FROM t1 LEFT JOIN (t2,t3) ON a1=0;
-a1 a2 a3
-1 NULL NULL
-EXPLAIN SELECT * FROM t1 LEFT JOIN (t2,t3) ON a1=0;
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 system NULL NULL NULL NULL 1
-1 SIMPLE t2 ALL NULL NULL NULL NULL 2
-1 SIMPLE t3 ALL NULL NULL NULL NULL 2
-SELECT * FROM t0, t1 LEFT JOIN (t2,t3) ON a1=0 WHERE a0=a1;
-a0 a1 a2 a3
-1 1 NULL NULL
-EXPLAIN SELECT * FROM t0, t1 LEFT JOIN (t2,t3) ON a1=0 WHERE a0=a1;
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t0 system PRIMARY NULL NULL NULL 1
-1 SIMPLE t1 system PRIMARY NULL NULL NULL 1
-1 SIMPLE t2 ALL NULL NULL NULL NULL 2
-1 SIMPLE t3 ALL NULL NULL NULL NULL 2
-INSERT INTO t0 VALUES (0);
-INSERT INTO t1 VALUES (0);
-SELECT * FROM t0, t1 LEFT JOIN (t2,t3) ON a1=5 WHERE a0=a1 AND a0=1;
-a0 a1 a2 a3
-1 1 NULL NULL
-EXPLAIN SELECT * FROM t0, t1 LEFT JOIN (t2,t3) ON a1=5 WHERE a0=a1 AND a0=1;
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t0 const PRIMARY PRIMARY 4 const 1 Using index
-1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 Using index
-1 SIMPLE t2 ALL NULL NULL NULL NULL 2
-1 SIMPLE t3 ALL NULL NULL NULL NULL 2
-drop table t1,t2;
-create table t1 (a int, b int);
-insert into t1 values (1,1),(2,2),(3,3);
-create table t2 (a int, b int);
-insert into t2 values (1,1), (2,2);
-select * from t2 right join t1 on t2.a=t1.a;
-a b a b
-1 1 1 1
-2 2 2 2
-NULL NULL 3 3
-select straight_join * from t2 right join t1 on t2.a=t1.a;
-a b a b
-1 1 1 1
-2 2 2 2
-NULL NULL 3 3
-DROP TABLE t0,t1,t2,t3;
+DROP TABLE invoice, test_table;
CREATE TABLE t1 (a int PRIMARY KEY, b int);
CREATE TABLE t2 (a int PRIMARY KEY, b int);
INSERT INTO t1 VALUES (1,1), (2,1), (3,1), (4,2);
@@ -947,57 +887,138 @@
bbbbb
Warnings:
Warning 1260 2 line(s) were cut by GROUP_CONCAT()
-select group_concat(t1.b,t2.c) from t1 left join t2 using(a) group by a;
-group_concat(t1.b,t2.c)
-aaaaa
-bbbbb
-Warnings:
-Warning 1260 2 line(s) were cut by GROUP_CONCAT()
-select group_concat(t1.b,t2.c) from t1 inner join t2 using(a) group by a;
-group_concat(t1.b,t2.c)
-aaaaa
-bbbbb
-Warnings:
-Warning 1260 2 line(s) were cut by GROUP_CONCAT()
drop table t1, t2;
set group_concat_max_len=default;
-create table t1 (gid smallint(5) unsigned not null, x int(11) not null, y int(11) not null, art int(11) not null, primary key (gid,x,y));
-insert t1 values (1, -5, -8, 2), (1, 2, 2, 1), (1, 1, 1, 1);
-create table t2 (gid smallint(5) unsigned not null, x int(11) not null, y int(11) not null, id int(11) not null, primary key (gid,id,x,y), key id (id));
-insert t2 values (1, -5, -8, 1), (1, 1, 1, 1), (1, 2, 2, 1);
-create table t3 ( set_id smallint(5) unsigned not null, id tinyint(4) unsigned not null, name char(12) not null, primary key (id,set_id));
-insert t3 values (0, 1, 'a'), (1, 1, 'b'), (0, 2, 'c'), (1, 2, 'd'), (1, 3, 'e'), (1, 4, 'f'), (1, 5, 'g'), (1, 6, 'h');
-explain select name from t1 left join t2 on t1.x = t2.x and t1.y = t2.y
-left join t3 on t1.art = t3.id where t2.id =1 and t2.x = -5 and t2.y =-8
-and t1.gid =1 and t2.gid =1 and t3.set_id =1;
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 const PRIMARY PRIMARY 10 const,const,const 1
-1 SIMPLE t2 const PRIMARY,id PRIMARY 14 const,const,const,const 1 Using index
-1 SIMPLE t3 const PRIMARY PRIMARY 3 const,const 1
-drop tables t1,t2,t3;
-CREATE TABLE t1 (EMPNUM INT, GRP INT);
-INSERT INTO t1 VALUES (0, 10);
-INSERT INTO t1 VALUES (2, 30);
-CREATE TABLE t2 (EMPNUM INT, NAME CHAR(5));
-INSERT INTO t2 VALUES (0, 'KERI');
-INSERT INTO t2 VALUES (9, 'BARRY');
-CREATE VIEW v1 AS
-SELECT COALESCE(t2.EMPNUM,t1.EMPNUM) AS EMPNUM, NAME, GRP
-FROM t2 LEFT OUTER JOIN t1 ON t2.EMPNUM=t1.EMPNUM;
-SELECT * FROM v1;
-EMPNUM NAME GRP
-0 KERI 10
-9 BARRY NULL
-SELECT * FROM v1 WHERE EMPNUM < 10;
-EMPNUM NAME GRP
-0 KERI 10
-9 BARRY NULL
+CREATE TABLE t1 (a int PRIMARY KEY, b int);
+CREATE TABLE t2 (a int PRIMARY KEY, b int);
+INSERT INTO t1 VALUES (1,2), (2,1), (3,2), (4,3), (5,6), (6,5), (7,8), (8,7), (9,10);
+INSERT INTO t2 VALUES (3,0), (4,1), (6,4), (7,5);
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t2.b <= t1.a AND t1.a <= t1.b;
+a b a b
+7 8 7 5
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a BETWEEN t2.b AND t1.b;
+a b a b
+7 8 7 5
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a NOT BETWEEN t2.b AND t1.b);
+a b a b
+7 8 7 5
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t2.b > t1.a OR t1.a > t1.b;
+a b a b
+2 1 NULL NULL
+3 2 3 0
+4 3 4 1
+6 5 6 4
+8 7 NULL NULL
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a NOT BETWEEN t2.b AND t1.b;
+a b a b
+2 1 NULL NULL
+3 2 3 0
+4 3 4 1
+6 5 6 4
+8 7 NULL NULL
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a BETWEEN t2.b AND t1.b);
+a b a b
+2 1 NULL NULL
+3 2 3 0
+4 3 4 1
+6 5 6 4
+8 7 NULL NULL
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a = t2.a OR t2.b > t1.a OR t1.a > t1.b;
+a b a b
+2 1 NULL NULL
+3 2 3 0
+4 3 4 1
+6 5 6 4
+7 8 7 5
+8 7 NULL NULL
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a != t2.a AND t1.a BETWEEN t2.b AND t1.b);
+a b a b
+2 1 NULL NULL
+3 2 3 0
+4 3 4 1
+6 5 6 4
+7 8 7 5
+8 7 NULL NULL
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a = t2.a AND (t2.b > t1.a OR t1.a > t1.b);
+a b a b
+3 2 3 0
+4 3 4 1
+6 5 6 4
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a != t2.a OR t1.a BETWEEN t2.b AND t1.b);
+a b a b
+3 2 3 0
+4 3 4 1
+6 5 6 4
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a = t2.a OR t1.a = t2.b;
+a b a b
+3 2 3 0
+4 3 4 1
+6 5 6 4
+7 8 7 5
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a IN(t2.a, t2.b);
+a b a b
+3 2 3 0
+4 3 4 1
+6 5 6 4
+7 8 7 5
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a NOT IN(t2.a, t2.b));
+a b a b
+3 2 3 0
+4 3 4 1
+6 5 6 4
+7 8 7 5
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a != t1.b AND t1.a != t2.b;
+a b a b
+3 2 3 0
+4 3 4 1
+6 5 6 4
+7 8 7 5
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a NOT IN(t1.b, t2.b);
+a b a b
+3 2 3 0
+4 3 4 1
+6 5 6 4
+7 8 7 5
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a IN(t1.b, t2.b));
+a b a b
+3 2 3 0
+4 3 4 1
+6 5 6 4
+7 8 7 5
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t2.a != t2.b OR (t1.a != t2.a AND t1.a != t2.b);
+a b a b
+3 2 3 0
+4 3 4 1
+6 5 6 4
+7 8 7 5
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t2.a = t2.b AND t1.a IN(t2.a, t2.b));
+a b a b
+3 2 3 0
+4 3 4 1
+6 5 6 4
+7 8 7 5
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t2.a != t2.b AND t1.a != t1.b AND t1.a != t2.b;
+a b a b
+3 2 3 0
+4 3 4 1
+6 5 6 4
+7 8 7 5
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t2.a = t2.b OR t1.a IN(t1.b, t2.b));
+a b a b
+3 2 3 0
+4 3 4 1
+6 5 6 4
+7 8 7 5
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a = t2.a OR t1.a = t2.b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 4
+1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.a 1
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a IN(t2.a, t2.b);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 4
+1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.a 1 Using where
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a > IF(t1.a = t2.b-2, t2.b, t2.b-1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 4
+1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.a 1 Using where
DROP TABLE t1,t2;
-CREATE TABLE t1 (c11 int);
-CREATE TABLE t2 (c21 int);
-INSERT INTO t1 VALUES (30), (40), (50);
-INSERT INTO t2 VALUES (300), (400), (500);
-SELECT * FROM t1 LEFT JOIN t2 ON (c11=c21 AND c21=30) WHERE c11=40;
-c11 c21
-40 NULL
-DROP TABLE t1, t2;
--- 1.32/mysql-test/t/join_outer.test 2005-08-23 19:08:01 +04:00
+++ 1.33/mysql-test/t/join_outer.test 2005-08-26 23:21:08 +04:00
@@ -135,7 +135,7 @@
INSERT INTO t1 VALUES (11403,'Projecte Fi de Carrera','Proyecto Fin de Carrera','Projecte Fi de Carrera','PFC',9.0,NULL,NULL,NULL);
INSERT INTO t1 VALUES (11404,'+lgebra lineal','Algebra lineal','+lgebra lineal','+lgebra lineal',15.0,NULL,NULL,NULL);
INSERT INTO t1 VALUES (11405,'+lgebra lineal','Algebra lineal','+lgebra lineal','+lgebra lineal',18.0,NULL,NULL,NULL);
-INSERT INTO t1 VALUES (11406,'Calcul Infinitesimal','Cßlculo Infinitesimal','Calcul Infinitesimal','Calcul Infinitesimal',15.0,NULL,NULL,NULL);
+INSERT INTO t1 VALUES (11406,'Calcul Infinitesimal','Cßlculo Infinitesimal','Calcul Infinitesimal','Calcul Infinitesimal',15.0,NULL,NULL,NULL);
CREATE TABLE t2 (
idAssignatura int(11) DEFAULT '0' NOT NULL,
@@ -590,7 +590,6 @@
INSERT INTO t2 VALUES("0", "SV", "0-SV");
INSERT INTO t2 VALUES("10", "EN", "10-EN");
INSERT INTO t2 VALUES("10", "SV", "10-SV");
-
SELECT t1.id, t1.text_id, t2.text_data
FROM t1 LEFT JOIN t2
ON t1.text_id = t2.text_id
@@ -713,3 +712,49 @@
INSERT INTO t2 VALUES (300), (400), (500);
SELECT * FROM t1 LEFT JOIN t2 ON (c11=c21 AND c21=30) WHERE c11=40;
DROP TABLE t1, t2;
+#
+# Test for bugs
+# #12101: erroneously applied outer join elimination in case of WHERE NOT BETWEEN
+# #12102: erroneously missing outer join elimination in case of WHERE IN/IF
+#
+
+CREATE TABLE t1 (a int PRIMARY KEY, b int);
+CREATE TABLE t2 (a int PRIMARY KEY, b int);
+
+INSERT INTO t1 VALUES (1,2), (2,1), (3,2), (4,3), (5,6), (6,5), (7,8), (8,7), (9,10);
+INSERT INTO t2 VALUES (3,0), (4,1), (6,4), (7,5);
+
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t2.b <= t1.a AND t1.a <= t1.b;
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a BETWEEN t2.b AND t1.b;
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a NOT BETWEEN t2.b AND t1.b);
+
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t2.b > t1.a OR t1.a > t1.b;
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a NOT BETWEEN t2.b AND t1.b;
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a BETWEEN t2.b AND t1.b);
+
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a = t2.a OR t2.b > t1.a OR t1.a > t1.b;
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a != t2.a AND t1.a BETWEEN t2.b AND t1.b);
+
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a = t2.a AND (t2.b > t1.a OR t1.a > t1.b);
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a != t2.a OR t1.a BETWEEN t2.b AND t1.b);
+
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a = t2.a OR t1.a = t2.b;
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a IN(t2.a, t2.b);
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a NOT IN(t2.a, t2.b));
+
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a != t1.b AND t1.a != t2.b;
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a NOT IN(t1.b, t2.b);
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a IN(t1.b, t2.b));
+
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t2.a != t2.b OR (t1.a != t2.a AND t1.a != t2.b);
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t2.a = t2.b AND t1.a IN(t2.a, t2.b));
+
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t2.a != t2.b AND t1.a != t1.b AND t1.a != t2.b;
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t2.a = t2.b OR t1.a IN(t1.b, t2.b));
+
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a = t2.a OR t1.a = t2.b;
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a IN(t2.a, t2.b);
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a > IF(t1.a = t2.b-2, t2.b, t2.b-1);
+
+DROP TABLE t1,t2;
+
| Thread |
|---|
| • bk commit into 5.0 tree (aivanov:1.1898) | Alex Ivanov | 26 Aug |
