MySQL Lists are EOL. Please join:

List:Internals« Previous MessageNext Message »
From:timour Date:August 19 2005 11:12pm
Subject:bk commit into 5.0 tree (timour:1.1999)
View as plain text  
Below is the list of changes that have just been committed into a local
5.0 repository of timka. When timka 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.1999 05/08/20 02:12:41 timour@stripped +9 -0
  WL#2486 - natural and using join according to SQL:2003
  
  Provide backwards compatibility extension to name resolution of
  coalesced columns. The patch allows such columns to be qualified
  with a table (and db) name, as it is in 4.1.
  
  NOTICE: the patch is not fully tested, and will not be pushed as
          is now.

  sql/table.h
    1.108 05/08/20 02:12:38 timour@stripped +5 -5
    Qualified oalesced columns are not excluded from name resolution any more.

  sql/table.cc
    1.182 05/08/20 02:12:38 timour@stripped +0 -9
    Qualified oalesced columns are not excluded from name resolution any more.

  sql/sql_base.cc
    1.287 05/08/20 02:12:38 timour@stripped +113 -35
    - Add all equivalent columns to a list attached to the column selected as
      a coalesced column. The list is used during name resolution to find all
      equivalent columns (not just the coalesced one as it was before).
    - Allow name resolution of qualified coalesced columns.

  mysql-test/t/select.test
    1.65 05/08/20 02:12:38 timour@stripped +13 -0
    Put back old tests to test that coalesced columns of natural joins can be qualified.

  mysql-test/t/join_outer.test
    1.32 05/08/20 02:12:37 timour@stripped +12 -8
    Put back old tests to test that coalesced columns of natural joins can be qualified.

  mysql-test/t/join.test
    1.27 05/08/20 02:12:37 timour@stripped +15 -0
    Put back old tests to test that coalesced columns of natural joins can be qualified.
    
    NOTICE: There are couple of queries that do not pass yet, marked with WL#2486.
            These need more investigation.

  mysql-test/r/select.result
    1.80 05/08/20 02:12:37 timour@stripped +38 -0
    Put back old tests to test that coalesced columns of natural joins can be qualified.

  mysql-test/r/join_outer.result
    1.42 05/08/20 02:12:37 timour@stripped +32 -16
    Put back old tests to test that coalesced columns of natural joins can be qualified.

  mysql-test/r/join.result
    1.32 05/08/20 02:12:37 timour@stripped +28 -0
    Put back old tests to test that coalesced columns of natural joins can be qualified.
    
    NOTICE: There are couple of queries that do not pass yet, marked with WL#2486.
            These need more investigation.

# 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:	timour
# Host:	lamia.home
# Root:	/home/timka/mysql/src/5.0-2486

--- 1.286/sql/sql_base.cc	2005-08-19 15:22:26 +03:00
+++ 1.287/sql/sql_base.cc	2005-08-20 02:12:38 +03:00
@@ -2580,6 +2580,110 @@
 
 
 /*
+  Find a natural join column by (qualified) name in a list of columns
+
+  SYNOPSIS
+    find_nj_column_in_list
+      column_list       list of natural join columns to search
+      field_name        name of field to search for
+      table_name        optional qualifying table name
+      db_name           optional qualifying database name
+      search_coalesced  if TRUE search the lists of equivalent columns
+                        of each column in 'column_list'
+
+  DESCRIPTION
+    Search a list of natural join columns for an optionally qualified
+    column. If 'search_coalesced' is TRUE, also for each column in the
+    list, search the list of its equivalent columns. Notice that:
+    - the equivalent columns of a column originate from different tables
+    - the lists of equivalent columns are true sets (no duplicates)
+    - 'column_list' and the equivalent columns are disjoint sets
+
+  RETURN
+    #     found column
+    NULL  if no column was found
+*/
+
+static Natural_join_column *
+find_nj_column_in_list(List<Natural_join_column> *column_list,
+                       const char *field_name, const char *table_name,
+                       const char *db_name, bool search_coalesced)
+{
+  List_iterator_fast<Natural_join_column> field_it(*column_list);
+  Natural_join_column *nj_col;
+
+  for (;;)
+  {
+    bool search_equivalent_cols;
+
+    if (!(nj_col= field_it++))
+      return NULL;  /* End of columns. */
+
+    search_equivalent_cols= search_coalesced &&
+                            !nj_col->equivalent_columns.is_empty();
+    if (table_name)
+    {
+      if (table_name[0] &&
+          my_strcasecmp(table_alias_charset, nj_col->table_name(), table_name))
+      {
+        if (search_equivalent_cols)
+          goto search_equivalent;
+        else
+          continue;
+      }
+      if (db_name && db_name[0])
+      {
+        const char *cur_db_name= nj_col->db_name();
+        if (cur_db_name && strcmp(db_name, cur_db_name))
+        {
+          if (search_equivalent_cols)
+            goto search_equivalent;
+          else
+            continue;
+        }
+      }
+    }
+
+    if (my_strcasecmp(system_charset_info, nj_col->name(), field_name))
+    {
+      if (search_equivalent_cols)
+        goto search_equivalent;
+      else
+        continue;
+    }
+    else
+      break; /* The column is found. */
+
+    /*
+      Search in the list of equivalent columns because these columns
+      are not visible in the list of columns of the top-most
+      NATURAL/USING join. Since this procedure is called only after
+      setup_tables, it is guaranteed that it will be called for the
+      top-most NATURAL join. Therefore that join's coalsced columns
+      contain complete lists of their equivalent columns, and there is
+      no need to recurisevely call this procedure for those columns, so
+      we pass search_coalesced == FALSE.
+
+      NOTICE:
+      The algorithm that determines the equivalent columns of a NATURAL
+      join column guarantees that there are no duplicate columns.
+    */
+search_equivalent:
+    {
+      Natural_join_column *common_nj_col;
+      if ((common_nj_col=
+           find_nj_column_in_list(&(nj_col->equivalent_columns),
+                                  field_name, table_name, db_name,
+                                  FALSE)))
+        return common_nj_col;
+    }
+  }
+
+  return nj_col;
+}
+
+
+/*
   Find field by name in a NATURAL/USING join table reference.
 
   SYNOPSIS
@@ -2612,8 +2716,6 @@
                            bool register_tree_change,
                            TABLE_LIST **actual_table)
 {
-  List_iterator_fast<Natural_join_column>
-    field_it(*(table_ref->join_columns));
   Natural_join_column *nj_col;
   Field *found_field;
   DBUG_ENTER("find_field_in_natural_join");
@@ -2624,35 +2726,9 @@
 
   LINT_INIT(found_field);
 
-  for (;;)
-  {
-    if (!(nj_col= field_it++))
-      DBUG_RETURN(NULL);
-
-    if (table_name)
-    {
-      /*
-        Coalesced columns cannot be qualified unless this is the execute phase
-        of prepared statements. The reason is that they do not belong to any
-        table, but for PS the prepare phase already resolves and stores
-        items, so during the execution phase we resolve fully qualified items.
-      */
-      if (!thd->current_arena->is_stmt_execute() && nj_col->is_coalesced)
-        continue;
-      if (table_name[0] &&
-          my_strcasecmp(table_alias_charset, nj_col->table_name(), table_name))
-        continue;
-      if (db_name && db_name[0])
-      {
-        const char *cur_db_name= nj_col->db_name();
-        if (cur_db_name && strcmp(db_name, cur_db_name))
-          continue;
-      }
-    }
-
-    if (!my_strcasecmp(system_charset_info, nj_col->name(), name))
-      break;
-  }
+  if (!(nj_col= find_nj_column_in_list(table_ref->join_columns,
+                                       name, table_name, db_name, TRUE)))
+    DBUG_RETURN(NULL);
 
 #ifndef NO_EMBEDDED_ACCESS_CHECKS
   if (check_grants && nj_col->check_grants(thd, name, length))
@@ -3560,7 +3636,10 @@
                   eq_cond);
 
       nj_col_1->is_common= nj_col_2->is_common= TRUE;
-      nj_col_1->is_coalesced= nj_col_2->is_coalesced= TRUE;
+
+      /* Update the set of equivalent columns of the first column. */
+      nj_col_1->equivalent_columns.push_back(nj_col_2);
+      nj_col_1->equivalent_columns.concat(&(nj_col_2->equivalent_columns));
 
       if (field_1)
       {
@@ -4372,13 +4451,12 @@
       /*
         If this is a column of a NATURAL/USING join, and the star was
         qualified with a table (and database) name, check if the
-        column is not a coalesced one, and if not, that is belongs to
+        column is not a coalesced one, and if not, that it belongs to
         the same table.
       */
       if (tables->is_natural_join && table_name)
       {
-        if (field_iterator.is_coalesced() ||
-            my_strcasecmp(table_alias_charset, table_name,
+        if (my_strcasecmp(table_alias_charset, table_name,
                           field_iterator.table_name()) ||
             (db_name && strcmp(db_name, field_iterator.db_name())))
           continue;

--- 1.181/sql/table.cc	2005-08-19 15:22:26 +03:00
+++ 1.182/sql/table.cc	2005-08-20 02:12:38 +03:00
@@ -2289,7 +2289,6 @@
   table_field= NULL;
   table_ref= tab;
   is_common= FALSE;
-  is_coalesced= FALSE;
 }
 
 
@@ -2301,7 +2300,6 @@
   view_field= NULL;
   table_ref= tab;
   is_common= FALSE;
-  is_coalesced= FALSE;
 }
 
 
@@ -2640,13 +2638,6 @@
   return &(table_ref->table->grant);
 }
 
-
-bool Field_iterator_table_ref::is_coalesced()
-{
-  if (table_ref->is_natural_join)
-    return natural_join_it.column_ref()->is_coalesced;
-  return FALSE;
-}
 
 /*
   Create new or return existing column reference to a column of a

--- 1.107/sql/table.h	2005-08-19 15:22:26 +03:00
+++ 1.108/sql/table.h	2005-08-20 02:12:38 +03:00
@@ -389,11 +389,12 @@
   */
   bool is_common;
   /*
-    A column is coalesced if it was common in some of several nested NATURAL/
-    USING joins. We have to know this, because according to ANSI, coalesced
-    columns cannot be qualified. 
+    Set of columns equivalent to this one. This set is filled during the
+    processing of the NATURAL/USING joins of a FROM clause. After this
+    phase, the set contains all common columns at all levels, equivalent
+    to 'this'.
   */
-  bool is_coalesced;
+  List<Natural_join_column> equivalent_columns;
 public:
   Natural_join_column(Field_translator *field_param, st_table_list *tab);
   Natural_join_column(Field *field_param, st_table_list *tab);
@@ -741,7 +742,6 @@
   const char *table_name();
   const char *db_name();
   GRANT_INFO *grant();
-  bool is_coalesced();
   Item *create_item(THD *thd) { return field_it->create_item(thd); }
   Field *field() { return field_it->field(); }
   Natural_join_column *get_or_create_column_ref(THD *thd, bool *is_created);

--- 1.31/mysql-test/r/join.result	2005-08-19 15:22:26 +03:00
+++ 1.32/mysql-test/r/join.result	2005-08-20 02:12:37 +03:00
@@ -121,10 +121,16 @@
 drop table t1, t2;
 create table t1 (a int primary key);
 insert into t1 values(1),(2);
+select t1.a from t1 as t1 left join t1 as t2 using (a) left join t1 as t3 using (a) left join t1 as t4 using (a) left join t1 as t5 using (a) left join t1 as t6 using (a) left join t1 as t7 using (a) left join t1 as t8 using (a) left join t1 as t9 using (a) left join t1 as t10 using (a) left join t1 as t11 using (a) left join t1 as t12 using (a) left join t1 as t13 using (a) left join t1 as t14 using (a) left join t1 as t15 using (a) left join t1 as t16 using (a) left join t1 as t17 using (a) left join t1 as t18 using (a) left join t1 as t19 using (a) left join t1 as t20 using (a) left join t1 as t21 using (a) left join t1 as t22 using (a) left join t1 as t23 using (a) left join t1 as t24 using (a) left join t1 as t25 using (a) left join t1 as t26 using (a) left join t1 as t27 using (a) left join t1 as t28 using (a) left join t1 as t29 using (a) left join t1 as t30 using (a) left join t1 as t31 using (a);
+a
+1
+2
 select a from t1 as t1 left join t1 as t2 using (a) left join t1 as t3 using (a) left join t1 as t4 using (a) left join t1 as t5 using (a) left join t1 as t6 using (a) left join t1 as t7 using (a) left join t1 as t8 using (a) left join t1 as t9 using (a) left join t1 as t10 using (a) left join t1 as t11 using (a) left join t1 as t12 using (a) left join t1 as t13 using (a) left join t1 as t14 using (a) left join t1 as t15 using (a) left join t1 as t16 using (a) left join t1 as t17 using (a) left join t1 as t18 using (a) left join t1 as t19 using (a) left join t1 as t20 using (a) left join t1 as t21 using (a) left join t1 as t22 using (a) left join t1 as t23 using (a) left join t1 as t24 using (a) left join t1 as t25 using (a) left join t1 as t26 using (a) left join t1 as t27 using (a) left join t1 as t28 using (a) left join t1 as t29 using (a) left join t1 as t30 using (a) left join t1 as t31 using (a);
 a
 1
 2
+select t1.a from t1 as t1 left join t1 as t2 using (a) left join t1 as t3 using (a) left join t1 as t4 using (a) left join t1 as t5 using (a) left join t1 as t6 using (a) left join t1 as t7 using (a) left join t1 as t8 using (a) left join t1 as t9 using (a) left join t1 as t10 using (a) left join t1 as t11 using (a) left join t1 as t12 using (a) left join t1 as t13 using (a) left join t1 as t14 using (a) left join t1 as t15 using (a) left join t1 as t16 using (a) left join t1 as t17 using (a) left join t1 as t18 using (a) left join t1 as t19 using (a) left join t1 as t20 using (a) left join t1 as t21 using (a) left join t1 as t22 using (a) left join t1 as t23 using (a) left join t1 as t24 using (a) left join t1 as t25 using (a) left join t1 as t26 using (a) left join t1 as t27 using (a) left join t1 as t28 using (a) left join t1 as t29 using (a) left join t1 as t30 using (a) left join t1 as t31 using (a) left join t1 as t32 using (a) left join t1 as t33 using (a) left join t
 1 as t34 using (a) left join t1 as t35 using (a) left join t1 as t36 using (a) left join t1 as t37 using (a) left join t1 as t38 using (a) left join t1 as t39 using (a) left join t1 as t40 using (a) left join t1 as t41 using (a) left join t1 as t42 using (a) left join t1 as t43 using (a) left join t1 as t44 using (a) left join t1 as t45 using (a) left join t1 as t46 using (a) left join t1 as t47 using (a) left join t1 as t48 using (a) left join t1 as t49 using (a) left join t1 as t50 using (a) left join t1 as t51 using (a) left join t1 as t52 using (a) left join t1 as t53 using (a) left join t1 as t54 using (a) left join t1 as t55 using (a) left join t1 as t56 using (a) left join t1 as t57 using (a) left join t1 as t58 using (a) left join t1 as t59 using (a) left join t1 as t60 using (a) left join t1 as t61 using (a) left join t1 as t62 using (a) left join t1 as t63 using (a) left join t1 as t64 using (a) left join t1 as t65 using (a);
+ERROR HY000: Too many tables; MySQL can only use 61 tables in a join
 select a from t1 as t1 left join t1 as t2 using (a) left join t1 as t3 using (a) left join t1 as t4 using (a) left join t1 as t5 using (a) left join t1 as t6 using (a) left join t1 as t7 using (a) left join t1 as t8 using (a) left join t1 as t9 using (a) left join t1 as t10 using (a) left join t1 as t11 using (a) left join t1 as t12 using (a) left join t1 as t13 using (a) left join t1 as t14 using (a) left join t1 as t15 using (a) left join t1 as t16 using (a) left join t1 as t17 using (a) left join t1 as t18 using (a) left join t1 as t19 using (a) left join t1 as t20 using (a) left join t1 as t21 using (a) left join t1 as t22 using (a) left join t1 as t23 using (a) left join t1 as t24 using (a) left join t1 as t25 using (a) left join t1 as t26 using (a) left join t1 as t27 using (a) left join t1 as t28 using (a) left join t1 as t29 using (a) left join t1 as t30 using (a) left join t1 as t31 using (a) left join t1 as t32 using (a) left join t1 as t33 using (a) left join t1 a
 s t34 using (a) left join t1 as t35 using (a) left join t1 as t36 using (a) left join t1 as t37 using (a) left join t1 as t38 using (a) left join t1 as t39 using (a) left join t1 as t40 using (a) left join t1 as t41 using (a) left join t1 as t42 using (a) left join t1 as t43 using (a) left join t1 as t44 using (a) left join t1 as t45 using (a) left join t1 as t46 using (a) left join t1 as t47 using (a) left join t1 as t48 using (a) left join t1 as t49 using (a) left join t1 as t50 using (a) left join t1 as t51 using (a) left join t1 as t52 using (a) left join t1 as t53 using (a) left join t1 as t54 using (a) left join t1 as t55 using (a) left join t1 as t56 using (a) left join t1 as t57 using (a) left join t1 as t58 using (a) left join t1 as t59 using (a) left join t1 as t60 using (a) left join t1 as t61 using (a) left join t1 as t62 using (a) left join t1 as t63 using (a) left join t1 as t64 using (a) left join t1 as t65 using (a);
 ERROR HY000: Too many tables; MySQL can only use XX tables in a join
 drop table t1;
@@ -145,6 +151,10 @@
 CREATE TABLE t1 (d DATE NOT NULL);
 CREATE TABLE t2 (d DATE NOT NULL);
 INSERT INTO t1 (d) VALUES ('2001-08-01'),('0000-00-00');
+SELECT * FROM t1 LEFT JOIN t2 USING (d) WHERE t2.d IS NULL;
+d
+2001-08-01
+0000-00-00
 SELECT * FROM t1 LEFT JOIN t2 USING (d) WHERE d IS NULL;
 d
 0000-00-00
@@ -264,6 +274,12 @@
 FULLTEXT KEY rate_code (rate_code)
 ) ENGINE=MyISAM;
 INSERT INTO t2 VALUES ('rivercats','cust',20);
+SELECT emp.rate_code, lr.base_rate FROM t1 AS emp LEFT JOIN t2 AS lr USING (siteid, rate_code) WHERE emp.emp_id = 'psmith' AND lr.siteid = 'rivercats';
+rate_code	base_rate
+cust	20
+SELECT emp.rate_code, lr.base_rate FROM t1 AS emp LEFT JOIN t2 AS lr USING (siteid, rate_code) WHERE lr.siteid = 'rivercats' AND emp.emp_id = 'psmith';
+rate_code	base_rate
+cust	20
 SELECT rate_code, lr.base_rate FROM t1 AS emp LEFT JOIN t2 AS lr USING (siteid, rate_code) WHERE emp.emp_id = 'psmith' AND siteid = 'rivercats';
 rate_code	base_rate
 cust	20
@@ -326,6 +342,12 @@
 i	i	i
 NULL	NULL	1
 2	2	2
+select * from t1,t2 natural left join t3 order by t1.i,t2.i,t3.i;
+i	i
+1	2
+1	3
+2	2
+2	3
 select * from t1,t2 natural left join t3 order by 1,2;
 i	i
 1	2
@@ -333,6 +355,12 @@
 2	2
 2	3
 select * from t1,t2 left join t3 on (t2.i=t3.i) order by t1.i,t2.i,t3.i;
+i	i	i
+1	2	2
+1	3	NULL
+2	2	2
+2	3	NULL
+select t1.i,t2.i,t3.i from t2 natural left join t3,t1 order by t1.i,t2.i,t3.i;
 i	i	i
 1	2	2
 1	3	NULL

--- 1.41/mysql-test/r/join_outer.result	2005-08-12 17:57:13 +03:00
+++ 1.42/mysql-test/r/join_outer.result	2005-08-20 02:12:37 +03:00
@@ -36,7 +36,7 @@
 3	5	C	3	5	B	5
 3	6	D	3	6	C	6
 NULL	NULL	NULL	4	7	D	7
-select * from t1 left join t2 using (a);
+select t1.*,t2.* from t1 left join t2 using (a);
 a	grp	c	id	c	d
 1	1	a	1	a	1
 2	2	b	NULL	NULL	NULL
@@ -45,16 +45,13 @@
 5	3	C	3	B	5
 6	3	D	3	C	6
 NULL	NULL		NULL	NULL	NULL
-select t1.*,t2.* from t1 left join t2 on t1.a=t2.a;
-grp	a	c	id	a	c	d
-1	1	a	1	1	a	1
-2	2	b	NULL	NULL	NULL	NULL
-2	3	c	NULL	NULL	NULL	NULL
-3	4	E	3	4	A	4
-3	5	C	3	5	B	5
-3	6	D	3	6	C	6
-NULL	NULL		NULL	NULL	NULL	NULL
-select * from t1 left join t2 using (a,c);
+select t1.*,t2.* from t1 left join t2 using (a) where t1.a=t2.a;
+a	grp	c	id	c	d
+1	1	a	1	a	1
+4	3	E	3	A	4
+5	3	C	3	B	5
+6	3	D	3	C	6
+select t1.*,t2.* from t1 left join t2 using (a,c);
 a	c	grp	id	d
 1	a	1	1	1
 2	b	2	NULL	NULL
@@ -63,7 +60,7 @@
 5	C	3	NULL	NULL
 6	D	3	NULL	NULL
 NULL		NULL	NULL	NULL
-select * from t1 left join t2 using (c);
+select t1.*,t2.* from t1 left join t2 using (c);
 c	grp	a	id	a	d
 a	1	1	1	1	1
 a	1	1	3	4	4
@@ -73,7 +70,7 @@
 C	3	5	3	6	6
 D	3	6	4	7	7
 	NULL	NULL	NULL	NULL	NULL
-select * from t1 natural left outer join t2;
+select t1.*,t2.* from t1 natural left outer join t2;
 a	c	grp	id	d
 1	a	1	1	1
 2	b	2	NULL	NULL
@@ -114,7 +111,7 @@
 ERROR 42S22: Unknown column 't3.a' in 'on clause'
 select t1.*,t2.*,t3.a from t1 left join t2 on (t3.a=t2.a) left join t1 as t3 on (t2.a=t3.a);
 ERROR 42S22: Unknown column 't3.a' in 'on clause'
-select * from t1 inner join t2 using (a);
+select t1.*,t2.* from t1 inner join t2 using (a);
 a	grp	c	id	c	d
 1	1	a	1	a	1
 4	3	E	3	A	4
@@ -126,7 +123,7 @@
 3	4	E	3	4	A	4
 3	5	C	3	5	B	5
 3	6	D	3	6	C	6
-select * from t1 natural join t2;
+select t1.*,t2.* from t1 natural join t2;
 a	c	grp	id	d
 1	a	1	1	1
 drop table t1,t2;
@@ -435,7 +432,7 @@
 SELECT t2.id2 FROM t2 LEFT OUTER JOIN t1 ON t1.id2 = t2.id2 WHERE id1 IS NULL;
 id2
 3
-SELECT id2 FROM t2 NATURAL LEFT OUTER JOIN t1 WHERE id1 IS NULL;
+SELECT t2.id2 FROM t2 NATURAL LEFT OUTER JOIN t1 WHERE id1 IS NULL;
 id2
 3
 drop table t1,t2;
@@ -653,6 +650,13 @@
 i
 1
 2
+select * from t1 natural left join t2 where (t2.i is not null)=0;
+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;
 i
 select * from t1 natural left join t2 where (i is not null) is not null;
@@ -931,6 +935,18 @@
 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;
+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 t1.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 left join t2 using(a) group by a;
 group_concat(t1.b,t2.c)
 aaaaa

--- 1.79/mysql-test/r/select.result	2005-08-18 05:56:11 +03:00
+++ 1.80/mysql-test/r/select.result	2005-08-20 02:12:37 +03:00
@@ -1350,6 +1350,20 @@
 250504	250505
 250505	250505
 insert into t2 (fld1, companynr) values (999999,99);
+select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null;
+companynr	companyname
+99	NULL
+select count(*) from t2 left join t4 using (companynr) where t4.companynr is not null;
+count(*)
+1199
+explain select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1200	
+1	SIMPLE	t4	eq_ref	PRIMARY	PRIMARY	1	test.t2.companynr	1	Using where; Not exists
+explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr is null;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1200	Using where; Not exists
 select companynr,companyname from t2 left join t4 using (companynr) where companynr is null;
 companynr	companyname
 select count(*) from t2 left join t4 using (companynr) where companynr is not null;
@@ -1362,6 +1376,18 @@
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 delete from t2 where fld1=999999;
+explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where
+1	SIMPLE	t4	eq_ref	PRIMARY	PRIMARY	1	test.t2.companynr	1	
+explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where
+1	SIMPLE	t4	eq_ref	PRIMARY	PRIMARY	1	test.t2.companynr	1	
+explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 and t4.companynr > 0;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where
+1	SIMPLE	t4	eq_ref	PRIMARY	PRIMARY	1	test.t2.companynr	1	
 explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t4	ALL	PRIMARY	NULL	NULL	NULL	12	Using where
@@ -1374,6 +1400,18 @@
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t4	ALL	PRIMARY	NULL	NULL	NULL	12	Using where
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	
+explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr is null;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where
+explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0 or t4.companynr > 0;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t4	ALL	PRIMARY	NULL	NULL	NULL	12	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where
+explain select t2.companynr,companyname from t4 left join t2 using (companynr) where ifnull(t2.companynr,1)>0;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where
 explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr is null;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t4	ALL	PRIMARY	NULL	NULL	NULL	12	Using where

--- 1.26/mysql-test/t/join.test	2005-08-19 15:22:26 +03:00
+++ 1.27/mysql-test/t/join.test	2005-08-20 02:12:37 +03:00
@@ -111,10 +111,14 @@
 
 create table t1 (a int primary key);
 insert into t1 values(1),(2);
+select t1.a from t1 as t1 left join t1 as t2 using (a) left join t1 as t3 using (a) left join t1 as t4 using (a) left join t1 as t5 using (a) left join t1 as t6 using (a) left join t1 as t7 using (a) left join t1 as t8 using (a) left join t1 as t9 using (a) left join t1 as t10 using (a) left join t1 as t11 using (a) left join t1 as t12 using (a) left join t1 as t13 using (a) left join t1 as t14 using (a) left join t1 as t15 using (a) left join t1 as t16 using (a) left join t1 as t17 using (a) left join t1 as t18 using (a) left join t1 as t19 using (a) left join t1 as t20 using (a) left join t1 as t21 using (a) left join t1 as t22 using (a) left join t1 as t23 using (a) left join t1 as t24 using (a) left join t1 as t25 using (a) left join t1 as t26 using (a) left join t1 as t27 using (a) left join t1 as t28 using (a) left join t1 as t29 using (a) left join t1 as t30 using (a) left join t1 as t31 using (a);
 select a from t1 as t1 left join t1 as t2 using (a) left join t1 as t3 using (a) left join t1 as t4 using (a) left join t1 as t5 using (a) left join t1 as t6 using (a) left join t1 as t7 using (a) left join t1 as t8 using (a) left join t1 as t9 using (a) left join t1 as t10 using (a) left join t1 as t11 using (a) left join t1 as t12 using (a) left join t1 as t13 using (a) left join t1 as t14 using (a) left join t1 as t15 using (a) left join t1 as t16 using (a) left join t1 as t17 using (a) left join t1 as t18 using (a) left join t1 as t19 using (a) left join t1 as t20 using (a) left join t1 as t21 using (a) left join t1 as t22 using (a) left join t1 as t23 using (a) left join t1 as t24 using (a) left join t1 as t25 using (a) left join t1 as t26 using (a) left join t1 as t27 using (a) left join t1 as t28 using (a) left join t1 as t29 using (a) left join t1 as t30 using (a) left join t1 as t31 using (a);
+--error 1116
+select t1.a from t1 as t1 left join t1 as t2 using (a) left join t1 as t3 using (a) left join t1 as t4 using (a) left join t1 as t5 using (a) left join t1 as t6 using (a) left join t1 as t7 using (a) left join t1 as t8 using (a) left join t1 as t9 using (a) left join t1 as t10 using (a) left join t1 as t11 using (a) left join t1 as t12 using (a) left join t1 as t13 using (a) left join t1 as t14 using (a) left join t1 as t15 using (a) left join t1 as t16 using (a) left join t1 as t17 using (a) left join t1 as t18 using (a) left join t1 as t19 using (a) left join t1 as t20 using (a) left join t1 as t21 using (a) left join t1 as t22 using (a) left join t1 as t23 using (a) left join t1 as t24 using (a) left join t1 as t25 using (a) left join t1 as t26 using (a) left join t1 as t27 using (a) left join t1 as t28 using (a) left join t1 as t29 using (a) left join t1 as t30 using (a) left join t1 as t31 using (a) left join t1 as t32 using (a) left join t1 as t33 using (a) left join t
 1 as t34 using (a) left join t1 as t35 using (a) left join t1 as t36 using (a) left join t1 as t37 using (a) left join t1 as t38 using (a) left join t1 as t39 using (a) left join t1 as t40 using (a) left join t1 as t41 using (a) left join t1 as t42 using (a) left join t1 as t43 using (a) left join t1 as t44 using (a) left join t1 as t45 using (a) left join t1 as t46 using (a) left join t1 as t47 using (a) left join t1 as t48 using (a) left join t1 as t49 using (a) left join t1 as t50 using (a) left join t1 as t51 using (a) left join t1 as t52 using (a) left join t1 as t53 using (a) left join t1 as t54 using (a) left join t1 as t55 using (a) left join t1 as t56 using (a) left join t1 as t57 using (a) left join t1 as t58 using (a) left join t1 as t59 using (a) left join t1 as t60 using (a) left join t1 as t61 using (a) left join t1 as t62 using (a) left join t1 as t63 using (a) left join t1 as t64 using (a) left join t1 as t65 using (a);
 --replace_result "31 tables" "XX tables" "61 tables" "XX tables"
 --error 1116
 select a from t1 as t1 left join t1 as t2 using (a) left join t1 as t3 using (a) left join t1 as t4 using (a) left join t1 as t5 using (a) left join t1 as t6 using (a) left join t1 as t7 using (a) left join t1 as t8 using (a) left join t1 as t9 using (a) left join t1 as t10 using (a) left join t1 as t11 using (a) left join t1 as t12 using (a) left join t1 as t13 using (a) left join t1 as t14 using (a) left join t1 as t15 using (a) left join t1 as t16 using (a) left join t1 as t17 using (a) left join t1 as t18 using (a) left join t1 as t19 using (a) left join t1 as t20 using (a) left join t1 as t21 using (a) left join t1 as t22 using (a) left join t1 as t23 using (a) left join t1 as t24 using (a) left join t1 as t25 using (a) left join t1 as t26 using (a) left join t1 as t27 using (a) left join t1 as t28 using (a) left join t1 as t29 using (a) left join t1 as t30 using (a) left join t1 as t31 using (a) left join t1 as t32 using (a) left join t1 as t33 using (a) left join t1 a
 s t34 using (a) left join t1 as t35 using (a) left join t1 as t36 using (a) left join t1 as t37 using (a) left join t1 as t38 using (a) left join t1 as t39 using (a) left join t1 as t40 using (a) left join t1 as t41 using (a) left join t1 as t42 using (a) left join t1 as t43 using (a) left join t1 as t44 using (a) left join t1 as t45 using (a) left join t1 as t46 using (a) left join t1 as t47 using (a) left join t1 as t48 using (a) left join t1 as t49 using (a) left join t1 as t50 using (a) left join t1 as t51 using (a) left join t1 as t52 using (a) left join t1 as t53 using (a) left join t1 as t54 using (a) left join t1 as t55 using (a) left join t1 as t56 using (a) left join t1 as t57 using (a) left join t1 as t58 using (a) left join t1 as t59 using (a) left join t1 as t60 using (a) left join t1 as t61 using (a) left join t1 as t62 using (a) left join t1 as t63 using (a) left join t1 as t64 using (a) left join t1 as t65 using (a);
+--replace_result "31 tables" "XX tables" "61 tables" "XX tables"
 drop table t1;
 
 #
@@ -144,6 +148,7 @@
 CREATE TABLE t1 (d DATE NOT NULL);
 CREATE TABLE t2 (d DATE NOT NULL);
 INSERT INTO t1 (d) VALUES ('2001-08-01'),('0000-00-00');
+SELECT * FROM t1 LEFT JOIN t2 USING (d) WHERE t2.d IS NULL;
 SELECT * FROM t1 LEFT JOIN t2 USING (d) WHERE d IS NULL;
 SELECT * from t1 WHERE t1.d IS NULL;
 SELECT * FROM t1 WHERE 1/0 IS NULL;
@@ -266,6 +271,8 @@
   FULLTEXT KEY rate_code (rate_code)
 ) ENGINE=MyISAM;
 INSERT INTO t2 VALUES ('rivercats','cust',20);
+SELECT emp.rate_code, lr.base_rate FROM t1 AS emp LEFT JOIN t2 AS lr USING (siteid, rate_code) WHERE emp.emp_id = 'psmith' AND lr.siteid = 'rivercats';
+SELECT emp.rate_code, lr.base_rate FROM t1 AS emp LEFT JOIN t2 AS lr USING (siteid, rate_code) WHERE lr.siteid = 'rivercats' AND emp.emp_id = 'psmith';
 SELECT rate_code, lr.base_rate FROM t1 AS emp LEFT JOIN t2 AS lr USING (siteid, rate_code) WHERE emp.emp_id = 'psmith' AND siteid = 'rivercats';
 SELECT rate_code, lr.base_rate FROM t1 AS emp LEFT JOIN t2 AS lr USING (siteid, rate_code) WHERE siteid = 'rivercats' AND emp.emp_id = 'psmith';
 drop table t1,t2;
@@ -315,13 +322,21 @@
 select * from t3 natural right join t2 natural right join t1;
 select * from t3 right join t2 on (t3.i=t2.i) right join t1 on (t2.i=t1.i);
 
+select * from t1,t2 natural left join t3 order by t1.i,t2.i,t3.i;
 select * from t1,t2 natural left join t3 order by 1,2;
 select * from t1,t2 left join t3 on (t2.i=t3.i) order by t1.i,t2.i,t3.i;
+select t1.i,t2.i,t3.i from t2 natural left join t3,t1 order by t1.i,t2.i,t3.i;
 select * from t2 natural left join t3,t1 order by t1.i;
 select t1.i,t2.i,t3.i from t2 left join t3 on (t2.i=t3.i),t1 order by t1.i,t2.i,t3.i;
 
+# TODO: WL#2486 - Unknown column 't2.i' in 'order clause'
+# extended name resolution for coalesced columns needs more work
+#select * from t1,t2 natural right join t3 order by t1.i,t2.i,t3.i;
 select * from t1,t2 natural right join t3 order by 1,2;
 select * from t1,t2 right join t3 on (t2.i=t3.i) order by t1.i,t2.i,t3.i;
+# TODO: WL#2486 - Unknown column 't2.i' in 'order clause'
+# extended name resolution for coalesced columns needs more work
+#select t1.i,t2.i,t3.i from t2 natural right join t3,t1 order by t1.i,t2.i,t3.i;
 select * from t2 natural right join t3,t1 order by t1.i;
 select t1.i,t2.i,t3.i from t2 right join t3 on (t2.i=t3.i),t1 order by t1.i,t2.i,t3.i;
 drop table t1,t2,t3;

--- 1.31/mysql-test/t/join_outer.test	2005-08-12 17:57:14 +03:00
+++ 1.32/mysql-test/t/join_outer.test	2005-08-20 02:12:37 +03:00
@@ -19,11 +19,11 @@
 select t1.*,t2.* from t1 left join t2 on (t1.a=t2.a) order by t1.grp,t1.a,t2.c;
 select t1.*,t2.* from { oj t2 left outer join t1 on (t1.a=t2.a) };
 select t1.*,t2.* from t1 as t0,{ oj t2 left outer join t1 on (t1.a=t2.a) } WHERE t0.a=2;
-select * from t1 left join t2 using (a);
-select t1.*,t2.* from t1 left join t2 on t1.a=t2.a;
-select * from t1 left join t2 using (a,c);
-select * from t1 left join t2 using (c);
-select * from t1 natural left outer join t2;
+select t1.*,t2.* from t1 left join t2 using (a);
+select t1.*,t2.* from t1 left join t2 using (a) where t1.a=t2.a;
+select t1.*,t2.* from t1 left join t2 using (a,c);
+select t1.*,t2.* from t1 left join t2 using (c);
+select t1.*,t2.* from t1 natural left outer join t2;
 
 select t1.*,t2.* from t1 left join t2 on (t1.a=t2.a) where t2.id=3;
 select t1.*,t2.* from t1 left join t2 on (t1.a=t2.a) where t2.id is null;
@@ -44,9 +44,9 @@
 select t1.*,t2.*,t3.a from t1 left join t2 on (t3.a=t2.a) left join t1 as t3 on (t2.a=t3.a);
 
 # Test of inner join
-select * from t1 inner join t2 using (a);
+select t1.*,t2.* from t1 inner join t2 using (a);
 select t1.*,t2.* from t1 inner join t2 on (t1.a=t2.a);
-select * from t1 natural join t2;
+select t1.*,t2.* from t1 natural join t2;
 
 drop table t1,t2;
 
@@ -325,7 +325,7 @@
 INSERT INTO t2 VALUES (3,'z');
 
 SELECT t2.id2 FROM t2 LEFT OUTER JOIN t1 ON t1.id2 = t2.id2 WHERE id1 IS NULL;
-SELECT id2 FROM t2 NATURAL LEFT OUTER JOIN t1 WHERE id1 IS NULL;
+SELECT t2.id2 FROM t2 NATURAL LEFT OUTER JOIN t1 WHERE id1 IS NULL;
 
 drop table t1,t2;
 
@@ -430,6 +430,8 @@
 insert into t2 values(2),(3);
 insert into t3 values(2),(4);
 select * from t1 natural left join t2 natural left join t3;
+select * from t1 natural left join t2 where (t2.i is not null)=0;
+select * from t1 natural left join t2 where (t2.i is not null) is not null;
 select * from t1 natural left join t2 where (i is not null)=0;
 select * from t1 natural left join t2 where (i is not null) is not null;
 drop table t1,t2,t3;
@@ -656,6 +658,8 @@
 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;

--- 1.64/mysql-test/t/select.test	2005-08-18 05:56:11 +03:00
+++ 1.65/mysql-test/t/select.test	2005-08-20 02:12:38 +03:00
@@ -1538,6 +1538,11 @@
 #
 insert into t2 (fld1, companynr) values (999999,99);
 
+select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null;
+select count(*) from t2 left join t4 using (companynr) where t4.companynr is not null;
+explain select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null;
+explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr is null;
+
 select companynr,companyname from t2 left join t4 using (companynr) where companynr is null;
 select count(*) from t2 left join t4 using (companynr) where companynr is not null;
 explain select companynr,companyname from t2 left join t4 using (companynr) where companynr is null;
@@ -1547,10 +1552,18 @@
 #
 # Test left join optimization
 
+explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0;
+explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0;
+explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 and t4.companynr > 0;
+
 explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0;
 explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0;
 explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 and companynr > 0;
 # Following can't be optimized
+explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr is null;
+explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0 or t4.companynr > 0;
+explain select t2.companynr,companyname from t4 left join t2 using (companynr) where ifnull(t2.companynr,1)>0;
+
 explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr is null;
 explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0 or companynr > 0;
 explain select companynr,companyname from t4 left join t2 using (companynr) where ifnull(companynr,1)>0;
Thread
bk commit into 5.0 tree (timour:1.1999)timour20 Aug