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.


mysql-test/r/distinct.result:
  Bug #16458: Simple SELECT FOR UPDATE causes "Result Set not updatable" error
    - test case
mysql-test/t/distinct.test:
  Bug #16458: Simple SELECT FOR UPDATE causes "Result Set not updatable" error
    - test case
sql/sql_select.cc:
  Bug #16458: Simple SELECT FOR UPDATE causes "Result Set not updatable" error
    - disable GROUP BY if contains the fields of a unique index.

1 files changed, 28 insertions, 0 deletions

diff --git a/mysql-test/t/distinct.test b/mysql-test/t/distinct.test
index f2fe1ec6372..8ca6f350b8d 100644
--- a/mysql-test/t/distinct.test
+++ b/mysql-test/t/distinct.test
@@ -349,5 +349,33 @@ SELECT DISTINCT 2, a, b FROM t2;
 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