MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:kgeorge Date:June 27 2006 2:40pm
Subject:bk commit into 4.1 tree (gkodinov:1.2516) BUG#16458
View as plain text  
Below is the list of changes that have just been committed into a local
4.1 repository of kgeorge. When kgeorge 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.2516 06/06/27 17:40:19 gkodinov@stripped +3 -0
  Bug #16458: Simple SELECT FOR UPDATE causes "Result Set not updatable" error
  'SELECT DISTINCT a,b FROM t1' should not use temp table if there is unique 
  index (or primary key) on a.
  There are a number of other similar cases that can be calculated without the
  use of a temp table : multi-part unique indexes, primary keys or using GROUP BY 
  instead of DISTINCT.
  When a GROUP BY/DISTINCT clause contains all key parts of a unique
  index, then it is guaranteed that the fields of the clause will be
  unique, therefore we can optimize away GROUP BY/DISTINCT altogether.
  This optimization has two effects:
  * there is no need to create a temporary table to compute the
     GROUP/DISTINCT operation (or the temporary table will be smaller if only GROUP 
     is removed and DISTINCT stays or if DISTINCT is removed and GROUP BY stays)
  * this causes the statement in effect to become updatable in Connector/Java
  because the result set columns will be direct reference to the primary key of 
  the table (instead to the temporary table that it currently references). 
  
  Implemented a check that will optimize away GROUP BY/DISTINCT for queries like 
  the above.
  Currently it will work only for single non-constant table in the FROM clause.

  sql/sql_select.cc
    1.457 06/06/27 17:40:15 gkodinov@stripped +168 -0
    Bug #16458: Simple SELECT FOR UPDATE causes "Result Set not updatable" error
      - disable GROUP BY if contains the fields of a unique index.

  mysql-test/t/distinct.test
    1.18 06/06/27 17:40:15 gkodinov@stripped +28 -0
    Bug #16458: Simple SELECT FOR UPDATE causes "Result Set not updatable" error
      - test case

  mysql-test/r/distinct.result
    1.33 06/06/27 17:40:15 gkodinov@stripped +51 -0
    Bug #16458: Simple SELECT FOR UPDATE causes "Result Set not updatable" error
      - test case

# 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:	gkodinov
# Host:	rakia.(none)
# Root:	/home/kgeorge/mysql/4.1/B16458

--- 1.456/sql/sql_select.cc	2006-06-19 13:22:38 +03:00
+++ 1.457/sql/sql_select.cc	2006-06-27 17:40:15 +03:00
@@ -114,6 +114,10 @@
 static uint find_shortest_key(TABLE *table, const key_map *usable_keys);
 static bool test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,
 				    ha_rows select_limit, bool no_changes);
+static bool list_contains_unique_index(TABLE *table,
+                          bool (*find_func) (Field *, void *), void *data);
+static bool find_field_in_item_list (Field *field, void *data);
+static bool find_field_in_order_list (Field *field, void *data);
 static int create_sort_index(THD *thd, JOIN *join, ORDER *order,
 			     ha_rows filesort_limit, ha_rows select_limit);
 static int remove_duplicates(JOIN *join,TABLE *entry,List<Item> &fields,
@@ -695,6 +699,36 @@
     if (old_group_list && !group_list)
       select_distinct= 0;
   }
+  /*
+     Check if we can optimize away GROUP BY/DISTINCT.
+     We can do that if there are no aggregate functions and the
+     fields in DISTINCT clause (if present) and/or columns in GROUP BY
+     (if present) contain direct references to all key parts of
+     an unique index (in whatever order).
+     Note that the unique keys for DISTINCT and GROUP BY should not
+     be the same (as long as they are unique).
+
+     The FROM clause must contain a single non-constant table.
+  */
+  if (tables - const_tables == 1 && (group_list || select_distinct) &&
+      !tmp_table_param.sum_func_count)
+  {
+    if (group_list &&
+       list_contains_unique_index(join_tab[const_tables].table,
+                                 find_field_in_order_list,
+                                 (void *) group_list))
+    {
+      group_list= 0;
+      group= 0;
+    }
+    if (select_distinct &&
+       list_contains_unique_index(join_tab[const_tables].table,
+                                 find_field_in_item_list,
+                                 (void *) &fields_list))
+    {
+      select_distinct= 0;
+    }
+  }
   if (!group_list && group)
   {
     order=0;					// The output has only one row
@@ -7375,6 +7409,140 @@
   }
   return best;
 }
+
+
+/*
+  Check if GROUP BY/DISTINCT can be optimized away because the set is 
+  already known to be distinct.
+  
+  SYNOPSIS
+    list_contains_unique_index ()
+    table                The table to operate on.
+    find_func            function to iterate over the list and search
+                         for a field
+  
+  DESCRIPTION
+    Used in removing the GROUP BY/DISTINCT of the following types of
+    statements:
+      SELECT [DISTINCT] <unique_key_cols>... FROM <single_table_ref>
+        [GROUP BY <unique_key_cols>,...]
+
+    If (a,b,c is distinct)
+      then <any combination of a,b,c>,{whatever} is also distinct
+
+    This function checks if all the key parts of any of the unique keys
+    of the table are referenced by a list : either the select list 
+    through find_field_in_item_list or GROUP BY list through
+    find_field_in_order_list.
+    If the above holds then we can safely remove the GROUP BY/DISTINCT,
+    as no result set can be more distinct than an unique key.
+  
+  RETURN VALUE
+    1                    found
+    0                    not found.
+*/
+
+static bool
+list_contains_unique_index(TABLE *table,
+                          bool (*find_func) (Field *, void *), void *data)
+{
+  for (uint keynr= 0; keynr < table->keys; keynr++)
+  {
+    if (keynr == table->primary_key ||
+         (table->key_info[keynr].flags & HA_NOSAME))
+    {
+      KEY *keyinfo= table->key_info + keynr;
+      KEY_PART_INFO *key_part, *key_part_end;
+
+      for (key_part=keyinfo->key_part,
+           key_part_end=key_part+ keyinfo->key_parts;
+           key_part < key_part_end;
+           key_part++)
+      {
+        if (!find_func(key_part->field, data))
+          break;
+      }
+      if (key_part == key_part_end)
+        return 1;
+    }
+  }
+  return 0;
+}
+
+
+/*
+  Helper function for list_contains_unique_index.
+  Find a field reference in a list of ORDER structures.
+  
+  SYNOPSIS
+    find_field_in_order_list ()
+    field                The field to search for.
+    data                 ORDER *.The list to search in
+  
+  DESCRIPTION
+    Finds a direct reference of the Field in the list.
+  
+  RETURN VALUE
+    1                    found
+    0                    not found.
+*/
+
+static bool
+find_field_in_order_list (Field *field, void *data)
+{
+  ORDER *group= (ORDER *) data;
+  bool part_found= 0;
+  for (ORDER *tmp_group= group; tmp_group; tmp_group=tmp_group->next)
+  {
+    Item *item= (*tmp_group->item)->real_item();
+    if (item->type() == Item::FIELD_ITEM &&
+        ((Item_field*) item)->field->eq(field))
+    {
+      part_found= 1;
+      break;
+    }
+  }
+  return part_found;
+}
+
+
+/*
+  Helper function for list_contains_unique_index.
+  Find a field reference in a dynamic list of Items.
+  
+  SYNOPSIS
+    find_field_in_item_list ()
+    field in             The field to search for.
+    data  in             List<Item> *.The list to search in
+  
+  DESCRIPTION
+    Finds a direct reference of the Field in the list.
+  
+  RETURN VALUE
+    1                    found
+    0                    not found.
+*/
+
+static bool
+find_field_in_item_list (Field *field, void *data)
+{
+  List<Item> *fields= (List<Item> *) data;
+  bool part_found= 0;
+  List_iterator<Item> li(*fields);
+  Item *item;
+
+  while ((item= li++))
+  {
+    if (item->type() == Item::FIELD_ITEM &&
+        ((Item_field*) item)->field->eq(field))
+    {
+      part_found= 1;
+      break;
+    }
+  }
+  return part_found;
+}
+
 
 /*
   Test if we can skip the ORDER BY by using an index.

--- 1.32/mysql-test/r/distinct.result	2005-08-19 11:54:51 +03:00
+++ 1.33/mysql-test/r/distinct.result	2006-06-27 17:40:15 +03:00
@@ -504,3 +504,54 @@
 2	2	4
 3	2	5
 DROP TABLE t1,t2;
+CREATE TABLE t1(a INT PRIMARY KEY, b INT);
+INSERT INTO t1 VALUES (1,1), (2,1), (3,1);
+EXPLAIN SELECT DISTINCT a FROM t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	3	Using index
+EXPLAIN SELECT DISTINCT a,b FROM t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+EXPLAIN SELECT DISTINCT t1_1.a, t1_1.b FROM t1 t1_1, t1 t1_2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1_1	ALL	NULL	NULL	NULL	NULL	3	Using temporary
+1	SIMPLE	t1_2	index	NULL	PRIMARY	4	NULL	3	Using index; Distinct
+EXPLAIN SELECT DISTINCT t1_1.a, t1_1.b FROM t1 t1_1, t1 t1_2
+WHERE t1_1.a = t1_2.a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1_1	ALL	PRIMARY	NULL	NULL	NULL	3	Using temporary
+1	SIMPLE	t1_2	eq_ref	PRIMARY	PRIMARY	4	test.t1_1.a	1	Using index; Distinct
+EXPLAIN SELECT a FROM t1 GROUP BY a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	3	Using index
+EXPLAIN SELECT a,b FROM t1 GROUP BY a,b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+EXPLAIN SELECT DISTINCT a,b FROM t1 GROUP BY a,b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+CREATE TABLE t2(a INT, b INT, c INT, d INT, PRIMARY KEY (a,b));
+INSERT INTO t2 VALUES (1,1,1,50), (1,2,3,40), (2,1,3,4);
+EXPLAIN SELECT DISTINCT a FROM t2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	index	NULL	PRIMARY	8	NULL	3	Using index
+EXPLAIN SELECT DISTINCT a,a FROM t2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	index	NULL	PRIMARY	8	NULL	3	Using index; Using temporary
+EXPLAIN SELECT DISTINCT b,a FROM t2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	index	NULL	PRIMARY	8	NULL	3	Using index
+EXPLAIN SELECT DISTINCT a,c FROM t2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	Using temporary
+EXPLAIN SELECT DISTINCT c,a,b FROM t2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	
+EXPLAIN SELECT DISTINCT a,b,d FROM t2 GROUP BY c,b,d;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	Using temporary; Using filesort
+CREATE UNIQUE INDEX c_b_unq ON t2 (c,b);
+EXPLAIN SELECT DISTINCT a,b,d FROM t2 GROUP BY c,b,d;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	
+DROP TABLE t1,t2;

--- 1.17/mysql-test/t/distinct.test	2005-08-19 11:55:24 +03:00
+++ 1.18/mysql-test/t/distinct.test	2006-06-27 17:40:15 +03:00
@@ -349,5 +349,33 @@
 SELECT DISTINCT a, 2, b FROM t2;
 
 DROP TABLE t1,t2;
+#
+# Bug#16458: Simple SELECT FOR UPDATE causes "Result Set not updatable" 
+#   error.
+#
+CREATE TABLE t1(a INT PRIMARY KEY, b INT);
+INSERT INTO t1 VALUES (1,1), (2,1), (3,1);
+EXPLAIN SELECT DISTINCT a FROM t1;
+EXPLAIN SELECT DISTINCT a,b FROM t1;
+EXPLAIN SELECT DISTINCT t1_1.a, t1_1.b FROM t1 t1_1, t1 t1_2;
+EXPLAIN SELECT DISTINCT t1_1.a, t1_1.b FROM t1 t1_1, t1 t1_2
+  WHERE t1_1.a = t1_2.a;
+EXPLAIN SELECT a FROM t1 GROUP BY a;
+EXPLAIN SELECT a,b FROM t1 GROUP BY a,b;
+EXPLAIN SELECT DISTINCT a,b FROM t1 GROUP BY a,b;
+
+CREATE TABLE t2(a INT, b INT, c INT, d INT, PRIMARY KEY (a,b));
+INSERT INTO t2 VALUES (1,1,1,50), (1,2,3,40), (2,1,3,4);
+EXPLAIN SELECT DISTINCT a FROM t2;
+EXPLAIN SELECT DISTINCT a,a FROM t2;
+EXPLAIN SELECT DISTINCT b,a FROM t2;
+EXPLAIN SELECT DISTINCT a,c FROM t2;
+EXPLAIN SELECT DISTINCT c,a,b FROM t2;
+
+EXPLAIN SELECT DISTINCT a,b,d FROM t2 GROUP BY c,b,d;
+CREATE UNIQUE INDEX c_b_unq ON t2 (c,b);
+EXPLAIN SELECT DISTINCT a,b,d FROM t2 GROUP BY c,b,d;
+
+DROP TABLE t1,t2;
 
 # End of 4.1 tests
Thread
bk commit into 4.1 tree (gkodinov:1.2516) BUG#16458kgeorge27 Jun