bzr commit into mysql-5.0-bugteam branch (gshchepa:2732) Bug#33461

[Gleb Shchepa]

#At file:///work/bzr/mysql-5.0-bugteam/ based on revid:kgeorge@stripped

 2732 Gleb Shchepa	2008-11-28
      Bug #33461: SELECT ... FROM <view> USE INDEX (...) throws
                  an error
      
      Even after the fix for bug 28701 visible behaviors of
      SELECT FROM a view and SELECT FROM a regular table are
      little bit different:
      
      1. "SELECT FROM regular table USE/FORCE/IGNORE(non
         existent index)" fails with a "ERROR 1176 (HY000):
         Key '...' doesn't exist in table '...'"
      
      2. "SELECT FROM view USING/FORCE/IGNORE(any index)" fails
         with a "ERROR 1221 (HY000): Incorrect usage of
         USE/IGNORE INDEX and VIEW".  OTOH "SHOW INDEX FROM
         view" always returns empty result set, so from the point
         of same behaviour view we trying to use/ignore non
         existent index.
      
      To harmonize the behaviour of USE/FORCE/IGNORE(index)
      clauses in SELECT from a view and from a regular table the
      "ERROR 1221 (HY000): Incorrect usage of USE/IGNORE INDEX
      and VIEW" message has been replaced with the "ERROR 1176
      (HY000): Key '...' doesn't exist in table '...'" message
      like for tables and non existent keys.
modified:
  mysql-test/r/view.result
  mysql-test/t/view.test
  sql/sql_view.cc

per-file messages:
  mysql-test/r/view.result
    Added test case for bug #33461.
    Updated test case for bug 28701.
  mysql-test/t/view.test
    Added test case for bug #33461.
    Updated test case for bug 28701.
  sql/sql_view.cc
    Bug #33461: SELECT ... FROM <view> USE INDEX (...) throws
                an error
    
    To harmonize the behaviour of USE/FORCE/IGNORE(index)
    clauses in SELECT from a view and from a regular table the
    "ERROR 1221 (HY000): Incorrect usage of USE/IGNORE INDEX
    and VIEW" message has been replaced with the "ERROR 1176
    (HY000): Key '...' doesn't exist in table '...'" message
    like for tables and non existent keys.
=== modified file 'mysql-test/r/view.result'
--- a/mysql-test/r/view.result	2008-10-27 10:22:38 +0000
+++ b/mysql-test/r/view.result	2008-11-28 16:13:12 +0000
@@ -625,7 +625,7 @@ drop table t1;
 create table t1 (a int, b int);
 create view v1 as select a, sum(b) from t1 group by a;
 select b from v1 use index (some_index) where b=1;
-ERROR HY000: Incorrect usage of USE INDEX and VIEW
+ERROR HY000: Key 'some_index' doesn't exist in table 'v1'
 drop view v1;
 drop table t1;
 create table t1 (col1 char(5),col2 char(5));
@@ -3567,11 +3567,11 @@ CREATE TABLE t1 (a INT);
 INSERT INTO t1 VALUES (1),(2);
 CREATE VIEW v1 AS SELECT * FROM t1;
 SELECT * FROM v1 USE KEY(non_existant);
-ERROR HY000: Incorrect usage of USE INDEX and VIEW
+ERROR HY000: Key 'non_existant' doesn't exist in table 'v1'
 SELECT * FROM v1 FORCE KEY(non_existant);
-ERROR HY000: Incorrect usage of FORCE INDEX and VIEW
+ERROR HY000: Key 'non_existant' doesn't exist in table 'v1'
 SELECT * FROM v1 IGNORE KEY(non_existant);
-ERROR HY000: Incorrect usage of IGNORE INDEX and VIEW
+ERROR HY000: Key 'non_existant' doesn't exist in table 'v1'
 DROP VIEW v1;
 DROP TABLE t1;
 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT, b INT NOT NULL DEFAULT 0,
@@ -3679,6 +3679,31 @@ DROP VIEW v1;
 
 CREATE VIEW v1 AS SELECT 1;
 DROP VIEW v1;
+CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT, INDEX (c2));
+INSERT INTO t1 VALUES (1,1), (2,2), (3,3);
+SELECT * FROM t1 USE INDEX (PRIMARY) WHERE c1=2;
+c1	c2
+2	2
+SELECT * FROM t1 USE INDEX (c2) WHERE c2=2;
+c1	c2
+2	2
+CREATE VIEW v1 AS SELECT c1, c2 FROM t1;
+SHOW INDEX FROM v1;
+Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
+SELECT * FROM v1 USE INDEX (PRIMARY) WHERE c1=2;
+ERROR HY000: Key 'PRIMARY' doesn't exist in table 'v1'
+SELECT * FROM v1 FORCE INDEX (PRIMARY) WHERE c1=2;
+ERROR HY000: Key 'PRIMARY' doesn't exist in table 'v1'
+SELECT * FROM v1 IGNORE INDEX (PRIMARY) WHERE c1=2;
+ERROR HY000: Key 'PRIMARY' doesn't exist in table 'v1'
+SELECT * FROM v1 USE INDEX (c2) WHERE c2=2;
+ERROR HY000: Key 'c2' doesn't exist in table 'v1'
+SELECT * FROM v1 FORCE INDEX (c2) WHERE c2=2;
+ERROR HY000: Key 'c2' doesn't exist in table 'v1'
+SELECT * FROM v1 IGNORE INDEX (c2) WHERE c2=2;
+ERROR HY000: Key 'c2' doesn't exist in table 'v1'
+DROP VIEW v1;
+DROP TABLE t1;
 # -----------------------------------------------------------------
 # -- End of 5.0 tests.
 # -----------------------------------------------------------------

=== modified file 'mysql-test/t/view.test'
--- a/mysql-test/t/view.test	2008-10-27 10:22:38 +0000
+++ b/mysql-test/t/view.test	2008-11-28 16:13:12 +0000
@@ -510,7 +510,7 @@ drop table t1;
 #
 create table t1 (a int, b int);
 create view v1 as select a, sum(b) from t1 group by a;
---error ER_WRONG_USAGE
+--error ER_KEY_DOES_NOT_EXITS
 select b from v1 use index (some_index) where b=1;
 drop view v1;
 drop table t1;
@@ -3424,11 +3424,11 @@ drop table t1;
 CREATE TABLE t1 (a INT);
 INSERT INTO t1 VALUES (1),(2);
 CREATE VIEW v1 AS SELECT * FROM t1;
---error ER_WRONG_USAGE
+--error ER_KEY_DOES_NOT_EXITS
 SELECT * FROM v1 USE KEY(non_existant);
---error ER_WRONG_USAGE
+--error ER_KEY_DOES_NOT_EXITS
 SELECT * FROM v1 FORCE KEY(non_existant);
---error ER_WRONG_USAGE
+--error ER_KEY_DOES_NOT_EXITS
 SELECT * FROM v1 IGNORE KEY(non_existant);
 
 DROP VIEW v1;
@@ -3568,6 +3568,32 @@ DROP VIEW v1;
 CREATE VIEW v1 AS SELECT 1;
 DROP VIEW v1;
 
+#
+# Bug #33461: SELECT ... FROM <view> USE INDEX (...) throws an error
+#
+
+CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT, INDEX (c2));
+INSERT INTO t1 VALUES (1,1), (2,2), (3,3);
+SELECT * FROM t1 USE INDEX (PRIMARY) WHERE c1=2;
+SELECT * FROM t1 USE INDEX (c2) WHERE c2=2;
+
+CREATE VIEW v1 AS SELECT c1, c2 FROM t1;
+SHOW INDEX FROM v1;
+--error ER_KEY_DOES_NOT_EXITS
+SELECT * FROM v1 USE INDEX (PRIMARY) WHERE c1=2;
+--error ER_KEY_DOES_NOT_EXITS
+SELECT * FROM v1 FORCE INDEX (PRIMARY) WHERE c1=2;
+--error ER_KEY_DOES_NOT_EXITS
+SELECT * FROM v1 IGNORE INDEX (PRIMARY) WHERE c1=2;
+--error ER_KEY_DOES_NOT_EXITS
+SELECT * FROM v1 USE INDEX (c2) WHERE c2=2;
+--error ER_KEY_DOES_NOT_EXITS
+SELECT * FROM v1 FORCE INDEX (c2) WHERE c2=2;
+--error ER_KEY_DOES_NOT_EXITS
+SELECT * FROM v1 IGNORE INDEX (c2) WHERE c2=2;
+
+DROP VIEW v1;
+DROP TABLE t1;
 
 --echo # -----------------------------------------------------------------
 --echo # -- End of 5.0 tests.

=== modified file 'sql/sql_view.cc'
--- a/sql/sql_view.cc	2008-11-14 17:25:57 +0000
+++ b/sql/sql_view.cc	2008-11-28 16:13:12 +0000
@@ -980,13 +980,14 @@ bool mysql_make_view(THD *thd, File_pars
     DBUG_RETURN(0);
   }
 
-  if (table->use_index || table->ignore_index)
-  {
-      my_error(ER_WRONG_USAGE, MYF(0),
-               table->ignore_index ? "IGNORE INDEX" :
-                 (table->force_index ? "FORCE INDEX" : "USE INDEX"), 
-               "VIEW");
-      DBUG_RETURN(TRUE);
+  List<String> *index_list= table->use_index ? table->use_index 
+                                             : table->ignore_index;
+  if (index_list) 
+  {
+    DBUG_ASSERT(index_list->head()); // should never fail
+    my_error(ER_KEY_DOES_NOT_EXITS, MYF(0), index_list->head()->c_ptr_safe(),
+             table->table_name);
+    DBUG_RETURN(TRUE);
   }
 
   /* check loop via view definition */