List:Commits« Previous MessageNext Message »
From:kgeorge Date:June 26 2006 11:09am
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/26 14:09:31 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.
  
  Implemented a check that will strip off GROUP BY 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/26 14:09:27 gkodinov@stripped +83 -0
    Bug #16458: Simple SELECT FOR UPDATE causes "Result Set not updatable" error
      - disable GROUP BY if the conditions are met.

  mysql-test/t/distinct.test
    1.18 06/06/26 14:09:27 gkodinov@stripped +24 -0
    Bug #16458: Simple SELECT FOR UPDATE causes "Result Set not updatable" error
      - test case

  mysql-test/r/distinct.result
    1.33 06/06/26 14:09:27 gkodinov@stripped +44 -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-26 14:09:27 +03:00
@@ -114,6 +114,7 @@
 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 unique_index_covers_group(TABLE *table, ORDER *group);
 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 +696,13 @@
     if (old_group_list && !group_list)
       select_distinct= 0;
   }
+  if (group_list && tables - const_tables == 1 && !select_distinct &&
+      !tmp_table_param.sum_func_count &&
+      unique_index_covers_group(join_tab[const_tables].table, group_list))
+  {
+    group_list= 0;
+    group= 0;
+  }
   if (!group_list && group)
   {
     order=0;					// The output has only one row
@@ -7375,6 +7383,81 @@
   }
   return best;
 }
+
+/*
+  Check if GROUP BY can be neglected because the set is already known to
+  be distinct.
+  
+  
+  SYNOPSIS
+    unique_index_covers_group ()
+    table in             The table to operate on.
+    group in             GROUP BY list
+  
+  
+  DESCRIPTION
+    Used in removing the GROUP BY of the following types of statements:
+      SELECT ... FROM <single_table_ref> GROUP BY <primary_key_cols>,...
+
+    If (a,b,c is distinct)
+      then 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 the 'group' list.
+    If the above holds then we can safely remove the GROUP BY, as no
+    result set can be more distinct than an unique key.
+  
+  
+  RETURN VALUE
+    1                    found
+    0                    not found.
+*/
+
+static bool
+unique_index_covers_group(TABLE *table, ORDER *group)
+{
+  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++)
+      {
+        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(key_part->field))
+          {
+            part_found= 1;
+            break;
+          }
+          else if (item->type() != Item::FIELD_ITEM)
+          {
+            /* 
+               cannot use this optimization because expressions
+               can change distinctness
+            */
+            return 0;
+          }
+        }
+        if (!part_found)
+          break;
+      }
+      if (key_part == key_part_end)
+        return 1;
+    }
+  }
+  return 0;
+}
+
 
 /*
   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-26 14:09:27 +03:00
@@ -504,3 +504,47 @@
 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, PRIMARY KEY (a,b));
+INSERT INTO t2 VALUES (1,1,1), (1,2,3), (2,1,3);
+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	
+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-26 14:09:27 +03:00
@@ -349,5 +349,29 @@
 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, PRIMARY KEY (a,b));
+INSERT INTO t2 VALUES (1,1,1), (1,2,3), (2,1,3);
+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;
+
+DROP TABLE t1,t2;
 
 # End of 4.1 tests
Thread
bk commit into 4.1 tree (gkodinov:1.2516) BUG#16458kgeorge26 Jun