List:Internals« Previous MessageNext Message »
From:Alex Ivanov Date:September 1 2005 12:47pm
Subject:bk commit into 5.0 tree (aivanov:1.1927) BUG#12101
View as plain text  
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.1927 05/09/01 14:46:58 aivanov@stripped +7 -0
  Merge mysql.com:/home/alexi/dev/mysql-4.1-0
  into  mysql.com:/home/alexi/dev/mysql-5.0-0
  Merge of BUG#12101 to 5.0, will need post-merge fixes

  BitKeeper/etc/RESYNC_TREE
    1.1 05/09/01 14:46:49 aivanov@stripped +0 -0
    New BitKeeper file ``BitKeeper/etc/RESYNC_TREE''

  sql/sql_yacc.yy
    1.416 05/09/01 14:46:49 aivanov@stripped +0 -21
    Merge BUG#12101 to 5.0, will need post-merge fixes

  BitKeeper/etc/RESYNC_TREE
    1.0 05/09/01 14:46:49 aivanov@stripped +0 -0
    BitKeeper file /home/alexi/dev/mysql-5.0-0/RESYNC/BitKeeper/etc/RESYNC_TREE

  sql/opt_range.cc
    1.186 05/09/01 14:46:48 aivanov@stripped +2 -112
    Merge of BUG#12101 to 5.0, will need post-merge fixes

  sql/item_cmpfunc.h
    1.109 05/09/01 14:46:48 aivanov@stripped +1 -1
    Merge of BUG#12101 to 5.0, will need post-merge fixes

  mysql-test/t/join_outer.test
    1.33 05/09/01 14:46:48 aivanov@stripped +5 -174
    Merge of BUG#12101 to 5.0, will need post-merge fixes
    

  mysql-test/r/join_outer.result
    1.43 05/09/01 14:46:48 aivanov@stripped +2 -246
    Merge of BUG#12101 to 5.0, will need post-merge fixes

  sql/item_cmpfunc.cc
    1.171 05/09/01 13:35:30 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-09-01 13:35:30 +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.108/sql/item_cmpfunc.h	2005-08-31 00:39:41 +04:00
+++ 1.109/sql/item_cmpfunc.h	2005-09-01 14:46:48 +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 *, struct st_table_list *, 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 *thd,struct st_table_list *tlist, Item **ref)
   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 *, struct st_table_list *, 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-01 14:46:48 +04:00
@@ -3524,117 +3524,10 @@
   }
 
   Item_func *cond_func= (Item_func*) cond;
-  if (cond_func->functype() == Item_func::NOT_FUNC)
-  {
-    /* Optimize NOT BETWEEN and NOT IN */
-    Item *arg= cond_func->arguments()[0];
-    if (arg->type() != Item::FUNC_ITEM)
-      DBUG_RETURN(0);
-    cond_func= (Item_func*) arg;
-    if (cond_func->functype() != Item_func::BETWEEN &&
-        cond_func->functype() != Item_func::IN_FUNC)
-      DBUG_RETURN(0);
-    inv= TRUE;
-  }
-  else if (cond_func->select_optimize() == Item_func::OPTIMIZE_NONE)
-    DBUG_RETURN(0);			       
-
-  param->cond= cond;
-
-  switch (cond_func->functype()) {
-  case Item_func::BETWEEN:
-    if (cond_func->arguments()[0]->type() != Item::FIELD_ITEM)
-      DBUG_RETURN(0);
-    field_item= (Item_field*) (cond_func->arguments()[0]);
-    value= NULL;
-    break;
-  case Item_func::IN_FUNC:
-  {
+  if (cond_func->select_optimize() == Item_func::OPTIMIZE_NONE)
+    DBUG_RETURN(0);				// Can't be calculated
     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++))
-    {
-      Field *field= field_item->field;
-      Item_result cmp_type= field->cmp_type();
-      if (!((ref_tables | field->table->map) & param_comp))
-      {
-        tree= get_mm_parts(param, cond, field, Item_func::EQ_FUNC,
-		           value,cmp_type);
-        ftree= !ftree ? tree : tree_and(param, ftree, 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);
-  }
-
-  /* 
-     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++)
-  {
-    Item *arg= cond_func->arguments()[i]->real_item();
-    if (arg != field_item)
-      ref_tables|= arg->used_tables();
-  }
-  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)
-  {
-    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(ftree);
+    if (!func->negated && func->key_item()->type() == Item::FIELD_ITEM)
 }
 
 

--- 1.415/sql/sql_yacc.yy	2005-08-29 23:10:49 +04:00
+++ 1.416/sql/sql_yacc.yy	2005-09-01 14:46:49 +04:00
@@ -4375,6 +4375,12 @@
         |   ANY_SYM { $$ = 0; }
         ;
 
+    {
+      $5->push_front($1);
+      Item_func_in *item= new Item_func_in(*$5);
+      item->negate();
+      $$= item;
+    }
 interval_expr:
          INTERVAL_SYM expr { $$=$2; }
         ;
--- New file ---
+++ BitKeeper/etc/RESYNC_TREE	05/09/01 14:46:49


--- 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-01 14:46:48 +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,
@@ -842,66 +842,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, text_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 +888,5 @@
 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
-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-09-01 14:46:48 +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,
@@ -586,130 +586,7 @@
   PRIMARY KEY  (text_id,language_id)
 );
 
-INSERT INTO t2 VALUES("0", "EN", "0-EN");
-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
-                  AND t2.language_id = 'SV'
-  WHERE (t1.id LIKE '%' OR t2.text_data LIKE '%');
-
-DROP TABLE t1, t2;
-
-# Test for bug #5896  
-
-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;
-EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON a1=0;
-SELECT * FROM t1 LEFT JOIN (t2,t3) ON a1=0;
-EXPLAIN SELECT * FROM t1 LEFT JOIN (t2,t3) ON a1=0;
-SELECT * FROM t0, t1 LEFT JOIN (t2,t3) ON a1=0 WHERE a0=a1;
-EXPLAIN SELECT * FROM t0, t1 LEFT JOIN (t2,t3) ON a1=0 WHERE a0=a1;
-
-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;
-EXPLAIN SELECT * FROM t0, t1 LEFT JOIN (t2,t3) ON a1=5 WHERE a0=a1 AND a0=1;
-
-# Test for BUG#4480
-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;
-select straight_join * from t2 right join t1 on t2.a=t1.a;
-
-DROP TABLE t0,t1,t2,t3;
-
-#
-# Test for bug #9017: left join mistakingly converted to inner join
-#
-
-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);
-INSERT INTO t2 VALUES (1,2), (2,2);
-
-SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a;
-SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t1.b=1;
-SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a
-  WHERE t1.b=1 XOR (NOT ISNULL(t2.a) AND t2.b=1);
-SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE not(0+(t1.a=30 and t2.b=1));
-
-DROP TABLE t1,t2;
-
-# Bug #8681: Bad warning message when group_concat() exceeds max length
-set group_concat_max_len=5;
-create table t1 (a int, b varchar(20));
-create table t2 (a int, c varchar(20));
-insert into t1 values (1,"aaaaaaaaaa"),(2,"bbbbbbbbbb");
-insert into t2 values (1,"cccccccccc"),(2,"dddddddddd");
-select group_concat(t1.b,t2.c) from t1 left join t2 using(a) group by t1.a;
-select group_concat(t1.b,t2.c) from t1 inner join t2 using(a) group by t1.a;
-select group_concat(t1.b,t2.c) from t1 left join t2 using(a) group by a;
-select group_concat(t1.b,t2.c) from t1 inner join t2 using(a) group by a;
-drop table t1, t2;
-set group_concat_max_len=default;
-
-# End of 4.1 tests
-
-#
-# BUG#10162 - ON is merged with WHERE, left join is convered to a regular join
-#
-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;
-drop tables t1,t2,t3;
-
-#
-# Test for bug #9938: invalid conversion from outer join to inner join 
-# for queries containing indirect reference in WHERE clause
-#
-
-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;
-SELECT * FROM v1 WHERE EMPNUM < 10;
-
-DROP TABLE t1,t2;
-
-#
-# Test for bug #11285: false Item_equal on expression in outer join
-# 
-
-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;
-DROP TABLE t1, t2;
+INSERT INTO text_table VALUES("0", "EN", "0-EN");
+INSERT INTO text_table VALUES("0", "SV", "0-SV");
+INSERT INTO text_table VALUES("10", "EN", "10-EN");
+INSERT INTO text_table VALUES("10", "SV", "10-SV");
Thread
• bk commit into 5.0 tree (aivanov:1.1927) BUG#12101Alex Ivanov1 Sep