bk commit into 5.0 tree (malff:1.2393) BUG#12976

[marc.alff]

Below is the list of changes that have just been committed into a local
5.0 repository of marcsql. When marcsql 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@stripped, 2007-02-06 16:01:22-07:00, malff@weblab.(none) +3 -0
  Bug#12976 (stored procedures local variables of type bit)
  
  Before this change, a local variables in stored procedures / stored functions
  or triggers, when declared with a type of bit(N), would not evaluate their
  value properly.
  
  The problem was that the data was incorrectly typed as a string,
  causing for example bit b'1', implemented as a byte 0x01, to be interpreted
  as a string starting with the character 0x01. This later would cause
  implicit conversions to integers or booleans to fail.
  
  The root cause of this problem was an incorrect translation between field
  types, like bit(N), and internal types used when representing values in Item
  objects.
  
  Also, before this change, the function HEX() would sometime print extra "0"
  characters when invoked with bit(N) values.
  
  With this fix, the type translation (sp_map_result_type, sp_map_item_type)
  has been changed so that bit(N) fields are represented with integer values.
  
  A consequence is that, for the function HEX(), when called with a stored
  procedure local variable of type bit(N) as argument, HEX() is provided with an
  integer instead of a string, and therefore does not print "0" padding.
  
  A test case for Bug 12976 was present in the test suite, and has been updated.

  mysql-test/r/sp-vars.result@stripped, 2007-02-06 16:01:20-07:00, malff@weblab.(none) +55 -7
    Local stored procedure variables of type bit(N) are integer values.

  mysql-test/t/sp-vars.test@stripped, 2007-02-06 16:01:20-07:00, malff@weblab.(none) +58 -7
    Local stored procedure variables of type bit(N) are integer values.

  sql/sp_head.cc@stripped, 2007-02-06 16:01:20-07:00, malff@weblab.(none) +2 -0
    Local stored procedure variables of type bit(N) are integer values.

# 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:	malff
# Host:	weblab.(none)
# Root:	/home/marcsql/TREE/mysql-5.0-12976_b

--- 1.5/mysql-test/r/sp-vars.result	2007-02-06 16:01:27 -07:00
+++ 1.6/mysql-test/r/sp-vars.result	2007-02-06 16:01:27 -07:00
@@ -431,17 +431,17 @@ SELECT HEX(v10);
 END|
 CALL p1();
 HEX(v1)
-01
+1
 HEX(v2)
-00
+0
 HEX(v3)
-05
+5
 HEX(v4)
 5555555555555555
 HEX(v5)
-07
+7
 HEX(v6)
-0000000000000005
+5
 HEX(v7)
 80
 HEX(v8)
@@ -748,12 +748,60 @@ HEX(b)	b = 0	b = FALSE	b IS FALSE	b = 1	
 1	0	0	0	1	1	1
 call p2();
 HEX(vb)	vb = 0	vb = FALSE	vb IS FALSE	vb = 1	vb = TRUE	vb IS TRUE
-00	1	1	1	0	0	0
+0	1	1	1	0	0	0
 HEX(vb)	vb = 0	vb = FALSE	vb IS FALSE	vb = 1	vb = TRUE	vb IS TRUE
-01	0	0	1	1	1	0
+1	0	0	0	1	1	1
 DROP TABLE t1;
 DROP PROCEDURE p1;
 DROP PROCEDURE p2;
+DROP TABLE IF EXISTS table_12976_a;
+DROP TABLE IF EXISTS table_12976_b;
+DROP PROCEDURE IF EXISTS proc_12976_a;
+DROP PROCEDURE IF EXISTS proc_12976_b;
+CREATE TABLE table_12976_a (val bit(1));
+CREATE TABLE table_12976_b(
+appname varchar(15),
+emailperm bit not null default 1,
+phoneperm bit not null default 0);
+insert into table_12976_b values ('A', b'1', b'1'), ('B', b'0', b'0');
+CREATE PROCEDURE proc_12976_a()
+BEGIN
+declare localvar bit(1);
+SELECT val INTO localvar FROM table_12976_a;
+SELECT coalesce(localvar, 1)+1, coalesce(val, 1)+1 FROM table_12976_a;
+END||
+CREATE PROCEDURE proc_12976_b(
+name varchar(15),
+out ep bit,
+out msg varchar(10))
+BEGIN
+SELECT emailperm into ep FROM table_12976_b where (appname = name);
+IF ep is true THEN
+SET msg = 'True';
+ELSE
+SET msg = 'False';
+END IF;
+END||
+INSERT table_12976_a VALUES (0);
+call proc_12976_a();
+coalesce(localvar, 1)+1	coalesce(val, 1)+1
+1	1
+UPDATE table_12976_a set val=1;
+call proc_12976_a();
+coalesce(localvar, 1)+1	coalesce(val, 1)+1
+2	2
+call proc_12976_b('A', @ep, @msg);
+select @ep, @msg;
+@ep	@msg
+1	True
+call proc_12976_b('B', @ep, @msg);
+select @ep, @msg;
+@ep	@msg
+0	False
+DROP TABLE table_12976_a;
+DROP TABLE table_12976_b;
+DROP PROCEDURE proc_12976_a;
+DROP PROCEDURE proc_12976_b;
 
 ---------------------------------------------------------------
 BUG#9572

--- 1.4/mysql-test/t/sp-vars.test	2007-02-06 16:01:27 -07:00
+++ 1.5/mysql-test/t/sp-vars.test	2007-02-06 16:01:27 -07:00
@@ -500,8 +500,6 @@ DROP PROCEDURE p1;
 #
 # Test case for BUG#12976: Boolean values reversed in stored procedures?
 #
-# TODO: test case failed.
-#
 ###########################################################################
 
 --echo
@@ -566,13 +564,8 @@ BEGIN
 END|
 delimiter ;|
 
-# The expected and correct result.
-
 call p1();
 
-# The wrong result. Note that only hex(vb) works, but is printed with two
-# digits for some reason in this case.
-
 call p2();
 
 #
@@ -582,6 +575,64 @@ call p2();
 DROP TABLE t1;
 DROP PROCEDURE p1;
 DROP PROCEDURE p2;
+
+# Additional tests for Bug#12976
+
+--disable_warnings
+DROP TABLE IF EXISTS table_12976_a;
+DROP TABLE IF EXISTS table_12976_b;
+DROP PROCEDURE IF EXISTS proc_12976_a;
+DROP PROCEDURE IF EXISTS proc_12976_b;
+--enable_warnings
+
+CREATE TABLE table_12976_a (val bit(1));
+
+CREATE TABLE table_12976_b(
+  appname varchar(15),
+  emailperm bit not null default 1,
+  phoneperm bit not null default 0);
+
+insert into table_12976_b values ('A', b'1', b'1'), ('B', b'0', b'0');
+
+delimiter ||;
+CREATE PROCEDURE proc_12976_a()
+BEGIN
+  declare localvar bit(1);
+  SELECT val INTO localvar FROM table_12976_a;
+  SELECT coalesce(localvar, 1)+1, coalesce(val, 1)+1 FROM table_12976_a;
+END||
+
+CREATE PROCEDURE proc_12976_b(
+  name varchar(15),
+  out ep bit,
+  out msg varchar(10))
+BEGIN
+  SELECT emailperm into ep FROM table_12976_b where (appname = name);
+  IF ep is true THEN
+    SET msg = 'True';
+  ELSE
+    SET msg = 'False';
+  END IF;
+END||
+
+delimiter ;||
+
+INSERT table_12976_a VALUES (0);
+call proc_12976_a();
+UPDATE table_12976_a set val=1;
+call proc_12976_a();
+
+call proc_12976_b('A', @ep, @msg);
+select @ep, @msg;
+
+call proc_12976_b('B', @ep, @msg);
+select @ep, @msg;
+
+DROP TABLE table_12976_a;
+DROP TABLE table_12976_b;
+DROP PROCEDURE proc_12976_a;
+DROP PROCEDURE proc_12976_b;
+
 
 ###########################################################################
 #

--- 1.230/sql/sp_head.cc	2007-02-06 16:01:27 -07:00
+++ 1.231/sql/sp_head.cc	2007-02-06 16:01:27 -07:00
@@ -36,6 +36,7 @@ Item_result
 sp_map_result_type(enum enum_field_types type)
 {
   switch (type) {
+  case MYSQL_TYPE_BIT:
   case MYSQL_TYPE_TINY:
   case MYSQL_TYPE_SHORT:
   case MYSQL_TYPE_LONG:
@@ -58,6 +59,7 @@ Item::Type
 sp_map_item_type(enum enum_field_types type)
 {
   switch (type) {
+  case MYSQL_TYPE_BIT:
   case MYSQL_TYPE_TINY:
   case MYSQL_TYPE_SHORT:
   case MYSQL_TYPE_LONG: