From: konstantin
Date: December 8 2005 9:59pm
Subject: bk commit into 5.0 tree (konstantin:1.1992) BUG#5967
List-Archive: http://lists.mysql.com/commits/50
X-Bug: 5967
Message-Id: <20051208215909.D405A6B50@dragonfly.local>

Below is the list of changes that have just been committed into a local
5.0 repository of kostja. When kostja 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.1992 05/12/09 00:58:59 konstantin@stripped +5 -0
  A fix and a test case for Bug#15441 "Running SP causes Server 
  to Crash": the bug was that due to non-standard name
  resolution precedence in stored procedures (See Bug#5967)
  a stored procedure variable took precedence over a table column
  when the arguments for VALUES() function were resolved.
  The implementation of VALUES() function was not designed to work
  with Item_splocal and crashed.
  VALUES() function is non-standard. It can refer to, and
  is meaningful for, table columns only. The patch disables SP 
  variables as possible arguments of VALUES() function.

  sql/sql_yacc.yy
    1.447 05/12/09 00:58:48 konstantin@stripped +1 -1
    Actual fix for Bug#15441 "Running SP causes Server to Crash":
    we should not allow VALUES() function to reference SP variables.

  sql/item.h
    1.187 05/12/09 00:58:47 konstantin@stripped +10 -0
    Add a comment for Item_insert_value.

  sql/item.cc
    1.208 05/12/09 00:58:46 konstantin@stripped +7 -0
    Cleanup Item_insert_value::fix_fields().

  mysql-test/t/sp.test
    1.167 05/12/09 00:58:45 konstantin@stripped +62 -12
    Add a test case for Bug#15441 "Running SP causes Server to Crash".

  mysql-test/r/sp.result
    1.178 05/12/09 00:58:45 konstantin@stripped +59 -23
    Test results fixed (Bug#15441).
    Also make sure that the recently added test cases follow sp.test
    internal tests standard.

# 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:	konstantin
# Host:	dragonfly.local
# Root:	/opt/local/work/mysql-5.0-15441

--- 1.207/sql/item.cc	2005-12-07 17:02:39 +03:00
+++ 1.208/sql/item.cc	2005-12-09 00:58:46 +03:00
@@ -5148,10 +5148,17 @@
     Item_ref *ref= (Item_ref *)arg;
     if (ref->ref[0]->type() != FIELD_ITEM)
     {
+      my_error(ER_BAD_FIELD_ERROR, MYF(0), "", "VALUES() function");
       return TRUE;
     }
     arg= ref->ref[0];
   }
+  /*
+    According to our SQL grammar, VALUES() function can reference
+    only to a column.
+  */
+  DBUG_ASSERT(arg->type() == FIELD_ITEM);
+
   Item_field *field_arg= (Item_field *)arg;
 
   if (field_arg->field->table->insert_values)

--- 1.186/sql/item.h	2005-12-07 17:02:39 +03:00
+++ 1.187/sql/item.h	2005-12-09 00:58:47 +03:00
@@ -2056,6 +2056,16 @@
   }
 };
 
+/*
+  Item_insert_value -- an implementation of VALUES() function.
+  You can use the VALUES(col_name) function in the UPDATE clause
+  to refer to column values from the INSERT portion of the INSERT
+  ... UPDATE statement. In other words, VALUES(col_name) in the
+  UPDATE clause refers to the value of col_name that would be
+  inserted, had no duplicate-key conflict occurred.
+  In all other places this function returns NULL.
+*/
+
 class Item_insert_value : public Item_field
 {
 public:

--- 1.446/sql/sql_yacc.yy	2005-12-07 17:02:40 +03:00
+++ 1.447/sql/sql_yacc.yy	2005-12-09 00:58:48 +03:00
@@ -4438,7 +4438,7 @@
 	    }
 	    $$= new Item_default_value(Lex->current_context(), $3);
 	  }
-	| VALUES '(' simple_ident ')'
+	| VALUES '(' simple_ident_nospvar ')'
 	  { $$= new Item_insert_value(Lex->current_context(), $3); }
 	| FUNC_ARG0 '(' ')'
 	  {

--- 1.177/mysql-test/r/sp.result	2005-12-07 17:02:37 +03:00
+++ 1.178/mysql-test/r/sp.result	2005-12-09 00:58:45 +03:00
@@ -4110,23 +4110,23 @@
 x
 4711
 drop procedure bug14376|
-drop procedure if exists p1|
-drop table if exists t1|
-create table t1 (a varchar(255))|
-insert into t1 (a) values ("a - table column")|
-create procedure p1(a varchar(255))
+drop procedure if exists bug5967|
+drop table if exists t3|
+create table t3 (a varchar(255))|
+insert into t3 (a) values ("a - table column")|
+create procedure bug5967(a varchar(255))
 begin
 declare i varchar(255);
-declare c cursor for select a from t1;
+declare c cursor for select a from t3;
 select a;
-select a from t1 into i;
+select a from t3 into i;
 select i as 'Parameter takes precedence over table column';                     open c;
 fetch c into i;
 close c;
 select i as 'Parameter takes precedence over table column in cursors';
 begin
 declare a varchar(255) default 'a - local variable';
-declare c1 cursor for select a from t1;
+declare c1 cursor for select a from t3;
 select a as 'A local variable takes precedence over parameter';
 open c1;
 fetch c1 into i;
@@ -4134,9 +4134,9 @@
 select i as 'A local variable takes precedence over parameter in cursors';
 begin
 declare a varchar(255) default 'a - local variable in a nested compound statement';
-declare c2 cursor for select a from t1;
+declare c2 cursor for select a from t3;
 select a as 'A local variable in a nested compound statement takes precedence over a local variable in the outer statement';
-select a from t1 into i;
+select a from t3 into i;
 select i as  'A local variable in a nested compound statement takes precedence over table column';
 open c2;
 fetch c2 into i;
@@ -4145,7 +4145,7 @@
 end;
 end;
 end|
-call p1("a - stored procedure parameter")|
+call bug5967("a - stored procedure parameter")|
 a
 a - stored procedure parameter
 Parameter takes precedence over table column
@@ -4162,7 +4162,7 @@
 a - local variable in a nested compound statement
 A local variable in a nested compound statement takes precedence over table column in cursors
 a - local variable in a nested compound statement
-drop procedure p1|
+drop procedure bug5967|
 drop procedure if exists bug13012|
 create procedure bug13012()
 BEGIN
@@ -4190,17 +4190,17 @@
 Table	Op	Msg_type	Msg_text
 test.t1	repair	status	OK
 test.t2	repair	status	OK
-test.t3	repair	error	Table 'test.t3' doesn't exist
+test.t3	repair	status	OK
 test.v1	repair	error	'test.v1' is not BASE TABLE
 Table	Op	Msg_type	Msg_text
 test.t1	optimize	status	OK
 test.t2	optimize	status	OK
-test.t3	optimize	error	Table 'test.t3' doesn't exist
+test.t3	optimize	status	OK
 test.v1	optimize	error	'test.v1' is not BASE TABLE
 Table	Op	Msg_type	Msg_text
 test.t1	analyze	status	Table is already up to date
 test.t2	analyze	status	Table is already up to date
-test.t3	analyze	error	Table 'test.t3' doesn't exist
+test.t3	analyze	status	Table is already up to date
 test.v1	analyze	error	'test.v1' is not BASE TABLE
 Warnings:
 Error	1347	'test.v1' is not BASE TABLE
@@ -4208,17 +4208,17 @@
 Table	Op	Msg_type	Msg_text
 test.t1	repair	status	OK
 test.t2	repair	status	OK
-test.t3	repair	error	Table 'test.t3' doesn't exist
+test.t3	repair	status	OK
 test.v1	repair	error	'test.v1' is not BASE TABLE
 Table	Op	Msg_type	Msg_text
 test.t1	optimize	status	OK
 test.t2	optimize	status	OK
-test.t3	optimize	error	Table 'test.t3' doesn't exist
+test.t3	optimize	status	OK
 test.v1	optimize	error	'test.v1' is not BASE TABLE
 Table	Op	Msg_type	Msg_text
 test.t1	analyze	status	Table is already up to date
 test.t2	analyze	status	Table is already up to date
-test.t3	analyze	error	Table 'test.t3' doesn't exist
+test.t3	analyze	status	Table is already up to date
 test.v1	analyze	error	'test.v1' is not BASE TABLE
 Warnings:
 Error	1347	'test.v1' is not BASE TABLE
@@ -4226,25 +4226,34 @@
 Table	Op	Msg_type	Msg_text
 test.t1	repair	status	OK
 test.t2	repair	status	OK
-test.t3	repair	error	Table 'test.t3' doesn't exist
+test.t3	repair	status	OK
 test.v1	repair	error	'test.v1' is not BASE TABLE
 Table	Op	Msg_type	Msg_text
 test.t1	optimize	status	OK
 test.t2	optimize	status	OK
-test.t3	optimize	error	Table 'test.t3' doesn't exist
+test.t3	optimize	status	OK
 test.v1	optimize	error	'test.v1' is not BASE TABLE
 Table	Op	Msg_type	Msg_text
 test.t1	analyze	status	Table is already up to date
 test.t2	analyze	status	Table is already up to date
-test.t3	analyze	error	Table 'test.t3' doesn't exist
+test.t3	analyze	status	Table is already up to date
 test.v1	analyze	error	'test.v1' is not BASE TABLE
 Warnings:
 Error	1347	'test.v1' is not BASE TABLE
 drop procedure bug13012|
 drop view v1;
 select * from t1|
-a
-a - table column
+id	data
+aa	0
+aa	1
+aa	2
+aa	3
+aa	4
+aa	5
+aa	6
+aa	7
+aa	8
+aa	9
 drop schema if exists mysqltest1|
 Warnings:
 Note	1008	Can't drop database 'mysqltest1'; database doesn't exist
@@ -4284,4 +4293,31 @@
 drop schema if exists mysqltest2|
 drop schema if exists mysqltest3|
 use test|
+drop table if exists t3|
+drop procedure if exists bug15441|
+create table t3 (id int not null primary key, county varchar(25))|
+insert into t3 (id, county) values (1, 'York')|
+create procedure bug15441(c varchar(25))
+begin
+update t3 set id=2, county=values(c);
+end|
+call bug15441('county')|
+ERROR 42S22: Unknown column 'c' in 'field list'
+drop procedure bug15441|
+create procedure bug15441(county varchar(25))
+begin
+declare c varchar(25) default "hello";
+insert into t3 (id, county) values (1, county)
+on duplicate key update county= values(county);
+select * from t3;
+update t3 set id=2, county=values(id);
+select * from t3;
+end|
+call bug15441('Yale')|
+id	county
+1	Yale
+id	county
+2	NULL
+drop table t3|
+drop procedure bug15441|
 drop table t1,t2;

--- 1.166/mysql-test/t/sp.test	2005-12-07 17:02:38 +03:00
+++ 1.167/mysql-test/t/sp.test	2005-12-09 00:58:45 +03:00
@@ -4914,24 +4914,24 @@
 #
 
 --disable_warnings
-drop procedure if exists p1|
-drop table if exists t1|
+drop procedure if exists bug5967|
+drop table if exists t3|
 --enable_warnings
-create table t1 (a varchar(255))|
-insert into t1 (a) values ("a - table column")|
-create procedure p1(a varchar(255))
+create table t3 (a varchar(255))|
+insert into t3 (a) values ("a - table column")|
+create procedure bug5967(a varchar(255))
 begin
   declare i varchar(255);
-  declare c cursor for select a from t1;
+  declare c cursor for select a from t3;
   select a;
-  select a from t1 into i;
+  select a from t3 into i;
   select i as 'Parameter takes precedence over table column';                     open c;
   fetch c into i;
   close c;
   select i as 'Parameter takes precedence over table column in cursors';
   begin
     declare a varchar(255) default 'a - local variable';
-    declare c1 cursor for select a from t1;
+    declare c1 cursor for select a from t3;
     select a as 'A local variable takes precedence over parameter';
     open c1;
     fetch c1 into i;
@@ -4939,9 +4939,9 @@
     select i as 'A local variable takes precedence over parameter in cursors';
     begin
       declare a varchar(255) default 'a - local variable in a nested compound statement';
-      declare c2 cursor for select a from t1;
+      declare c2 cursor for select a from t3;
       select a as 'A local variable in a nested compound statement takes precedence over a local variable in the outer statement';
-      select a from t1 into i;
+      select a from t3 into i;
       select i as  'A local variable in a nested compound statement takes precedence over table column';
       open c2;
       fetch c2 into i;
@@ -4950,8 +4950,8 @@
     end;
   end;
 end|
-call p1("a - stored procedure parameter")|
-drop procedure p1|
+call bug5967("a - stored procedure parameter")|
+drop procedure bug5967|
 
 #
 # Bug#13012 "SP: REPAIR/BACKUP/RESTORE TABLE crashes the server"
@@ -5027,6 +5027,56 @@
 drop schema if exists mysqltest2|
 drop schema if exists mysqltest3|
 use test|
+
+#
+# Bug#15441 "Running SP causes Server to Crash": check that an SP variable
+# can not be used in VALUES() function.
+#
+--disable_warnings
+drop table if exists t3|
+drop procedure if exists bug15441|
+--enable_warnings
+create table t3 (id int not null primary key, county varchar(25))|
+insert into t3 (id, county) values (1, 'York')|
+
+# First check that a stored procedure that refers to a parameter in VALUES()
+# function won't parse.
+
+create procedure bug15441(c varchar(25))
+begin
+  update t3 set id=2, county=values(c);
+end|
+--error ER_BAD_FIELD_ERROR
+call bug15441('county')|
+drop procedure bug15441|
+
+# Now check the case when there is an ambiguity between column names
+# and stored procedure parameters: the parser shall resolve the argument
+# of VALUES() function to the column name.
+
+# It's hard to deduce what county refers to in every case (INSERT statement):
+# 1st county refers to the column
+# 2nd county refers to the procedure parameter
+# 3d and 4th county refers to the column, again, but
+# for 4th county it has the value of SP parameter
+
+# In UPDATE statement, just check that values() function returns NULL for
+# non- INSERT...UPDATE statements, as stated in the manual.
+
+create procedure bug15441(county varchar(25))
+begin
+  declare c varchar(25) default "hello";
+
+  insert into t3 (id, county) values (1, county)
+  on duplicate key update county= values(county);
+  select * from t3;
+
+  update t3 set id=2, county=values(id);
+  select * from t3;
+end|
+call bug15441('Yale')|
+drop table t3|
+drop procedure bug15441|
 
 #
 # BUG#NNNN: New bug synopsis