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.1967 05/09/06 23:49:54 aivanov@stripped +7 -0
Merge mysql.com:/home/alexi/dev/mysql-4.1-0
into mysql.com:/home/alexi/dev/mysql-5.0-0
BitKeeper/etc/RESYNC_TREE
1.1 05/09/06 23:49:45 aivanov@stripped +0 -0
New BitKeeper file ``BitKeeper/etc/RESYNC_TREE''
sql/sql_yacc.yy
1.417 05/09/06 23:49:45 aivanov@stripped +6 -27
Manual merge
sql/opt_range.cc
1.186 05/09/06 23:49:45 aivanov@stripped +0 -4
Manual merge
sql/item_cmpfunc.h
1.109 05/09/06 23:49:45 aivanov@stripped +3 -3
Manual merge
mysql-test/t/join_outer.test
1.33 05/09/06 23:49:45 aivanov@stripped +47 -48
Manual merge
mysql-test/r/join_outer.result
1.43 05/09/06 23:49:45 aivanov@stripped +134 -134
Manual merge
BitKeeper/etc/RESYNC_TREE
1.0 05/09/06 23:49:45 aivanov@stripped +0 -0
BitKeeper file /home/alexi/dev/mysql-5.0-0/RESYNC/BitKeeper/etc/RESYNC_TREE
sql/item_cmpfunc.cc
1.172 05/09/06 23:23:32 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.171/sql/item_cmpfunc.cc 2005-09-02 17:21:07 +04:00
+++ 1.172/sql/item_cmpfunc.cc 2005-09-06 23:23:32 +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), intersection(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.108/sql/item_cmpfunc.h 2005-08-31 00:39:41 +04:00
+++ 1.109/sql/item_cmpfunc.h 2005-09-06 23:49:45 +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; }
@@ -505,16 +537,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; }
};
@@ -819,7 +845,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;
@@ -828,11 +854,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()
@@ -853,12 +880,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.185/sql/opt_range.cc 2005-08-30 14:15:03 +04:00
+++ 1.186/sql/opt_range.cc 2005-09-06 23:49:45 +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()) {
--- 1.416/sql/sql_yacc.yy 2005-09-03 03:13:10 +04:00
+++ 1.417/sql/sql_yacc.yy 2005-09-06 23:49:45 +04:00
@@ -4350,6 +4350,12 @@
| ANY_SYM { $$ = 0; }
;
+
+
+
+
+
+
interval_expr:
INTERVAL_SYM expr { $$=$2; }
;
--- New file ---
+++ BitKeeper/etc/RESYNC_TREE 05/09/06 23:49:45
--- 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-09-06 23:49:45 +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);
CREATE TABLE t2 (
idAssignatura int(11) DEFAULT '0' NOT NULL,
Grup int(11) DEFAULT '0' NOT NULL,
@@ -1001,3 +1001,136 @@
c11 c21
40 NULL
DROP TABLE t1, t2;
+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;
--- 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-09-06 23:49:45 +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);
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.1967) | Alex Ivanov | 6 Sep |
