| Bug #12976 | stored procedures local variables of type bit | ||
|---|---|---|---|
| Submitted: | 4 Sep 2005 3:58 | Modified: | 9 Mar 2007 2:51 |
| Reporter: | mark pitsilos | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Stored Routines | Severity: | S3 (Non-critical) |
| Version: | 5.0.12-beta, 5.0.13-bk | OS: | Windows (Windows XP, Linux) |
| Assigned to: | Marc ALFF | CPU Architecture: | Any |
| Tags: | rt_q1_2007 | ||
[4 Sep 2005 4:25]
mark pitsilos
Edit: Corrected server version
[4 Sep 2005 10:05]
Valeriy Kravchuk
I tried to repeat the problem you described on the newer 5.0.13-BK build on Linux with the following test case:
create table t12976(id integer auto_increment primary key, val boolean);
insert into t12976(val) values (true);
insert into t12976(val) values (false);
delimiter //
create procedure p12976()
begin
declare f boolean default false;
select val into f from t12976 where id = 1;
if f is true then
select 'True' as result;
else
select 'Not true' as result;
end if;
end;
//
mysql> delimiter ;
mysql> call p12976();
+--------+
| result |
+--------+
| True |
+--------+
1 row in set (0,00 sec)
Query OK, 0 rows affected (0,00 sec)
mysql> update t12976 set val = false where id = 1;
Query OK, 1 row affected (0,00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> call p12976();
+----------+
| result |
+----------+
| Not true |
+----------+
1 row in set (0,00 sec)
Query OK, 0 rows affected (0,00 sec)
mysql> select version();
+-------------+
| version() |
+-------------+
| 5.0.13-beta |
+-------------+
1 row in set (0,00 sec)
On 5.1.12-beta-nt I've got the same results.
I hope, I've got your point right? If no, please, provide a repeatable test case (modify mine or provide your real one).
[4 Sep 2005 13:16]
mark pitsilos
Well, here are the contents of the permissions table:
mysql> select * from applevels;
+---------+-------+-----------+-----------+--------+--------+---------+---------
+----------+---------+---------+----------+-----------+---------+----------+
| APPNAME | LEVEL | EMAILPERM | ALTEMPERM | FNPERM | LNPERM | SEXPERM | DOBPERM
| REGTPERM | OCCPERM | LOCPERM | ADDRPERM | PHONEPERM | MOBPERM | CREDPERM |
+---------+-------+-----------+-----------+--------+--------+---------+---------
+----------+---------+---------+----------+-----------+---------+----------+
| e-shop | 1 | ☺ | ☺ | ☺ | ☺ | ☺ | ☺
| ☺ | ☺ | ☺ | ☺ | ☺ | ☺ | ☺ |
+---------+-------+-----------+-----------+--------+--------+---------+---------
+----------+---------+---------+----------+-----------+---------+----------+
1 row in set (0.00 sec)
Here is a simple procedure that checks if EMAILPERM is true:
mysql> delimiter //
mysql> CREATE PROCEDURE test(out ep bit, out msg varchar(10))
-> BEGIN
-> SELECT EMAILPERM INTO ep FROM applevels WHERE level = 1;
-> IF ep IS true THEN
-> SET msg = 'True!';
-> ELSE SET msg = 'False!';
-> END IF;
-> END
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
Now, calling this stored procedure gives:
mysql> call test(@ep, @msg);
Query OK, 0 rows affected (0.00 sec)
mysql> select @ep, @msg;
+------+--------+
| @ep | @msg |
+------+--------+
| ☺ | False! |
+------+--------+
1 row in set (0.00 sec)
As you see, while EMAILPERM is true, the procedure
gives a message value of "False!"...
Any ideas?
[4 Sep 2005 13:20]
mark pitsilos
Btw, I installed version 5.0.12 and the problem persists.
[5 Sep 2005 7:04]
Valeriy Kravchuk
Please, send the results of the "show create table applevels" and statements that were used to populate it with test data. (I am just curious why do you think this "smiles" are representations of true.)
[5 Sep 2005 18:51]
mark pitsilos
Here is the create statement as returned by
"show create statement":
| applevels | CREATE TABLE `applevels` (
`APPNAME` varchar(15) NOT NULL,
`LEVEL` tinyint(4) NOT NULL,
`EMAILPERM` bit(1) NOT NULL default '☺',
`ALTEMPERM` bit(1) NOT NULL default '\0',
`FNPERM` bit(1) NOT NULL default '☺',
`LNPERM` bit(1) NOT NULL default '☺',
`SEXPERM` bit(1) NOT NULL default '\0',
`DOBPERM` bit(1) NOT NULL default '\0',
`REGTPERM` bit(1) NOT NULL default '\0',
`OCCPERM` bit(1) NOT NULL default '\0',
`LOCPERM` bit(1) NOT NULL default '\0',
`ADDRPERM` bit(1) NOT NULL default '\0',
`PHONEPERM` bit(1) NOT NULL default '\0',
`MOBPERM` bit(1) NOT NULL default '\0',
`CREDPERM` bit(1) NOT NULL default '\0',
PRIMARY KEY (`APPNAME`,`LEVEL`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
The original create statement is as follows:
CREATE TABLE APPLEVELS
(
APPNAME VARCHAR(15) NOT NULL REFERENCES WEBAPP(APPNAME) ON DELETE CASCADE,
LEVEL TINYINT NOT NULL,
EMAILPERM BIT NOT NULL DEFAULT true,
ALTEMPERM BIT NOT NULL DEFAULT false,
FNPERM BIT NOT NULL DEFAULT true,
LNPERM BIT NOT NULL DEFAULT true,
SEXPERM BIT NOT NULL DEFAULT false,
DOBPERM BIT NOT NULL DEFAULT false,
REGTPERM BIT NOT NULL DEFAULT false,
OCCPERM BIT NOT NULL DEFAULT false,
LOCPERM BIT NOT NULL DEFAULT false,
ADDRPERM BIT NOT NULL DEFAULT false,
PHONEPERM BIT NOT NULL DEFAULT false,
MOBPERM BIT NOT NULL DEFAULT false,
CREDPERM BIT NOT NULL DEFAULT false,
PRIMARY KEY (APPNAME, LEVEL)
);
"Describe applevels" gives:
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| APPNAME | varchar(15) | NO | PRI | | |
| LEVEL | tinyint(4) | NO | PRI | | |
| EMAILPERM | bit(1) | NO | | ☺ | |
| ALTEMPERM | bit(1) | NO | | | |
| FNPERM | bit(1) | NO | | ☺ | |
| LNPERM | bit(1) | NO | | ☺ | |
| SEXPERM | bit(1) | NO | | | |
| DOBPERM | bit(1) | NO | | | |
| REGTPERM | bit(1) | NO | | | |
| OCCPERM | bit(1) | NO | | | |
| LOCPERM | bit(1) | NO | | | |
| ADDRPERM | bit(1) | NO | | | |
| PHONEPERM | bit(1) | NO | | | |
| MOBPERM | bit(1) | NO | | | |
| CREDPERM | bit(1) | NO | | | |
+-----------+-------------+------+-----+---------+-------+
15 rows in set (0.01 sec)
The statement used to insert test data is:
insert into applevels values ('e-shop', 1, true, true, true, true, true,
true, true, true, true, true, true, true, true);
After insertion, all values corresponding to "true" are displayed
as "smily faces" in the command line interface.
[6 Sep 2005 8:09]
Valeriy Kravchuk
Thak you for the inforation you provided. I wa able to repeat the behaviour you described:
mysql> CREATE TABLE APPLEVELS
-> (
-> APPNAME VARCHAR(15) NOT NULL,
-> LEVEL TINYINT NOT NULL,
-> EMAILPERM BIT NOT NULL DEFAULT true,
-> ALTEMPERM BIT NOT NULL DEFAULT false,
-> FNPERM BIT NOT NULL DEFAULT true,
-> LNPERM BIT NOT NULL DEFAULT true,
-> SEXPERM BIT NOT NULL DEFAULT false,
-> DOBPERM BIT NOT NULL DEFAULT false,
-> REGTPERM BIT NOT NULL DEFAULT false,
-> OCCPERM BIT NOT NULL DEFAULT false,
-> LOCPERM BIT NOT NULL DEFAULT false,
-> ADDRPERM BIT NOT NULL DEFAULT false,
-> PHONEPERM BIT NOT NULL DEFAULT false,
-> MOBPERM BIT NOT NULL DEFAULT false,
-> CREDPERM BIT NOT NULL DEFAULT false,
-> PRIMARY KEY (APPNAME, LEVEL)
-> );
Query OK, 0 rows affected (0.10 sec)
mysql> desc applevels;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| APPNAME | varchar(15) | NO | PRI | | |
| LEVEL | tinyint(4) | NO | PRI | | |
| EMAILPERM | bit(1) | NO | | ☺ | |
| ALTEMPERM | bit(1) | NO | | | |
| FNPERM | bit(1) | NO | | ☺ | |
| LNPERM | bit(1) | NO | | ☺ | |
| SEXPERM | bit(1) | NO | | | |
| DOBPERM | bit(1) | NO | | | |
| REGTPERM | bit(1) | NO | | | |
| OCCPERM | bit(1) | NO | | | |
| LOCPERM | bit(1) | NO | | | |
| ADDRPERM | bit(1) | NO | | | |
| PHONEPERM | bit(1) | NO | | | |
| MOBPERM | bit(1) | NO | | | |
| CREDPERM | bit(1) | NO | | | |
+-----------+-------------+------+-----+---------+-------+
15 rows in set (0.12 sec)
mysql> insert into applevels values ('e-shop', 1, true, true, true, true, true,
-> true, true, true, true, true, true, true, true);
Query OK, 1 row affected (0.06 sec)
mysql> select * from applevels;
+---------+-------+-----------+-----------+--------+--------+---------+---------
+----------+---------+---------+----------+-----------+---------+----------+
| APPNAME | LEVEL | EMAILPERM | ALTEMPERM | FNPERM | LNPERM | SEXPERM | DOBPERM
| REGTPERM | OCCPERM | LOCPERM | ADDRPERM | PHONEPERM | MOBPERM | CREDPERM |
+---------+-------+-----------+-----------+--------+--------+---------+---------
+----------+---------+---------+----------+-----------+---------+----------+
| e-shop | 1 | ☺ | ☺ | ☺ | ☺ | ☺ | ☺
| ☺ | ☺ | ☺ | ☺ | ☺ | ☺ | ☺ |
+---------+-------+-----------+-----------+--------+--------+---------+---------
+----------+---------+---------+----------+-----------+---------+----------+
1 row in set (0.01 sec)
mysql> delimiter //
mysql> CREATE PROCEDURE test(out ep bit, out msg varchar(10))
-> BEGIN
-> SELECT EMAILPERM INTO ep FROM applevels WHERE level = 1;
-> IF ep IS true THEN
-> SET msg = 'True!';
-> ELSE SET msg = 'False!';
-> END IF;
-> END
-> //
Query OK, 0 rows affected (0.14 sec)
mysql> delimiter ;
mysql> call test(@ep, @msg);
Query OK, 0 rows affected (0.02 sec)
mysql> select @ep, @msg;
+------+--------+
| @ep | @msg |
+------+--------+
| ☺ | False! |
+------+--------+
1 row in set (0.00 sec)
But I think it not a bug by itself. You just can not use bit(1) filed as boolean filed (that is stored as tinyint(1), 1 byte - see http://dev.mysql.com/doc/mysql/en/numeric-type-overview.html for details). You just can not use "is true" predicates with bit values. You should use BOOLEAN data type instead, as in my previous example.
Alternatively, you may use numeric predicates with bit values:
mysql> delimiter //
mysql> CREATE PROCEDURE test2(out ep bit, out msg varchar(10))
-> BEGIN
-> SELECT EMAILPERM INTO ep FROM applevels WHERE level = 1;
-> IF ep = b'1' THEN
-> SET msg = 'True!';
-> ELSE SET msg = 'False!';
-> END IF;
-> END
-> //
Query OK, 0 rows affected (0.01 sec)
mysql> delimiter ;
mysql> call test2(@ep, @msg);
Query OK, 0 rows affected (0.00 sec)
mysql> select @ep, @msg;
+------+-------+
| @ep | @msg |
+------+-------+
| ☺ | True! |
+------+-------+
1 row in set (0.00 sec)
See http://dev.mysql.com/doc/mysql/en/bit-field-values.html for details.
I agree that this should be described in documenation clearly, so I changed the category.
[6 Sep 2005 8:26]
mark pitsilos
I see, then I guess I'll change all bit values to booleans. Thank you for your help!
[7 Sep 2005 18:47]
Paul DuBois
Actually, I don't think this is a documentation issue. I think this is a bug. Here's a little test script: create table t (b bit(1)); insert into t (b) values(b'0'),(b'1'); select hex(b), b = 0, b = false, b is false, b = 1, b = true, b is true from t; The result is: +--------+-------+-----------+------------+-------+----------+-----------+ | hex(b) | b = 0 | b = false | b is false | b = 1 | b = true | b is true | +--------+-------+-----------+------------+-------+----------+-----------+ | 0 | 1 | 1 | 1 | 0 | 0 | 0 | | 1 | 0 | 0 | 0 | 1 | 1 | 1 | +--------+-------+-----------+------------+-------+----------+-----------+ This shows that testing a bit(1) value against =0, =false, and is false yields the same result, as does testing the value against =1, =true, and is true. If different results are obtained when the operation takes place within a stored routine, something is wrong. Resetting bug report to "Open" for further analysis.
[8 Sep 2005 11:14]
Per-Erik Martin
Here's a full test case:
create table t (b bit(1));
insert into t (b) values(b'0'),(b'1');
create procedure pbit1()
select hex(b), b = 0, b = false, b is false, b = 1, b = true, b is true
from t;
delimiter //
create procedure pbit2()
begin
declare vb bit;
select b into vb from t where b = 0;
select hex(vb), vb = 0, vb = false, vb is false, vb = 1, vb = true,
vb is true;
select b into vb from t where b = 1;
select hex(vb), vb = 0, vb = false, vb is false, vb = 1, vb = true,
vb is true;
end//
delimiter ;
# The expected and correct result
call pbit1();
# The wrong result. Note that only hex(vb) works, but is printed with two
# digits for some reason in this case.
call pbit2();
[22 Dec 2005 10:25]
Alexander Nozdrin
It's not 'In Progress' now.
[7 Nov 2006 6:15]
Siu Ching Pong (Asuka Kenji)
Simplified test case --- on Linux (Fedora Core 5), MySQL 5.1.12-beta: mysql> SELECT B'0' IS TRUE, B'0' IS FALSE, B'1' IS TRUE, B'1' IS FALSE; +--------------+---------------+--------------+---------------+ | B'0' IS TRUE | B'0' IS FALSE | B'1' IS TRUE | B'1' IS FALSE | +--------------+---------------+--------------+---------------+ | 0 | 1 | 0 | 1 | +--------------+---------------+--------------+---------------+ 1 row in set (0.00 sec) -- Already wrong in this simple test case ... -- The function that compares "BIT(1)" to "BOOLEAN" must have some problems ...
[7 Nov 2006 7:24]
Siu Ching Pong (Asuka Kenji)
I looked at the code, and found that "IS TRUE", "IS FALSE" and "IS UNKNOWN" are not implemented like "IS NULL" and "IS NOT NULL". The implementation is not a "full feature implementation" (ie. using Item_func_istrue, Item_func_isfalse and Item_func_isunknown). Rather, it uses a Macro-like implementation.
I am not familiar with YACC syntax, so I don't know how the bug could be fixed. But I found the place where the function is implemented. The code fragment is copied here for convenience. Hope this helps (I am using the source of MySQL-5.1.12-beta).
.
.
.
sql/sql_yacc.cc --- is_truth_value() --- line 1289 ~ line 1297
sql/sql_yacc.cc --- case 984 ~ case 987 --- line 21715 ~ line 21734
sql/sql_yacc.yy --- is_truth_value() --- line 58 ~ line 66
sql/sql_yacc.yy --- bool_test --- line 5817 ~ line 5825
.
.
.
"libmysqld/sql_yacc.cc" is the same as "sql/sql_yacc.cc"
The two is_truth_value() functions are the same
.
.
.
--- MySQL-5.1.12-beta, sql/sql_yacc.cc, lines 1289 ~ 1297 Start ---
/* Helper for parsing "IS [NOT] truth_value" */
inline Item *is_truth_value(Item *A, bool v1, bool v2)
{
return new Item_func_if(create_func_ifnull(A,
new Item_int((char *) (v2 ? "TRUE" : "FALSE"), v2, 1)),
new Item_int((char *) (v1 ? "TRUE" : "FALSE"), v1, 1),
new Item_int((char *) (v1 ? "FALSE" : "TRUE"),!v1, 1));
}
--- MySQL-5.1.12-beta, sql/sql_yacc.cc, lines 1289 ~ 1297 End ---
--- MySQL-5.1.12-beta, sql/sql_yacc.cc, lines 21715 ~ 21734 Start ---
case 984:
#line 5818 "sql_yacc.yy"
{ (yyval.item)= is_truth_value((yyvsp[-2].item),1,0); }
break;
case 985:
#line 5819 "sql_yacc.yy"
{ (yyval.item)= is_truth_value((yyvsp[-3].item),0,0); }
break;
case 986:
#line 5820 "sql_yacc.yy"
{ (yyval.item)= is_truth_value((yyvsp[-2].item),0,1); }
break;
case 987:
#line 5821 "sql_yacc.yy"
{ (yyval.item)= is_truth_value((yyvsp[-3].item),1,1); }
break;
--- MySQL-5.1.12-beta, sql/sql_yacc.cc, lines 21715 ~ 21734 End ---
--- MySQL-5.1.12-beta, sql/sql_yacc.yy, lines 5817 ~ 5825 Start ---
bool_test:
bool_pri IS TRUE_SYM { $$= is_truth_value($1,1,0); }
| bool_pri IS not TRUE_SYM { $$= is_truth_value($1,0,0); }
| bool_pri IS FALSE_SYM { $$= is_truth_value($1,0,1); }
| bool_pri IS not FALSE_SYM { $$= is_truth_value($1,1,1); }
| bool_pri IS UNKNOWN_SYM { $$= new Item_func_isnull($1); }
| bool_pri IS not UNKNOWN_SYM { $$= new Item_func_isnotnull($1); }
| bool_pri ;
--- MySQL-5.1.12-beta, sql/sql_yacc.yy, lines 5817 ~ 5825 End ---
[6 Feb 2007 21:33]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/19449 ChangeSet@1.2393, 2007-02-06 14:31:55-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.
[6 Feb 2007 23:29]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/19457 ChangeSet@1.2393, 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.
[7 Mar 2007 21:56]
Konstantin Osipov
Pushed into 5.0.38, 5.1.17
[9 Mar 2007 2:51]
Jon Stephens
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.
If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at
http://dev.mysql.com/doc/en/installing-source.html
Documented bugfix in 5.0.38 and 5.1.17 changelogs.
Description: Hello, While testing a stored procedure that returns credentials only if certain permissions (situated in an other table) are true, - otherwise it returns NULL values - I noticed that while all permissions were true, all returned credentials were NULL... When I replaced the "IF permission IS true THEN ..." statement with "IF permission IS false THEN ...", all the credentials where returned... How can this be? The values of all permissions have been set to true ("smily face" in the command prompt). Why did it work correctly only when I reversed the check? How can a "IF true IS false" clause succeed? Doesn't a "smily face" mean "true"? Thank you Mark Pitsilos How to repeat: Check if a boolean field with true value is true in a stored procedure and have it return a message if it is so. Normally, it should return the message but on my system, the message will be NULL indicating that the field was not found to be true...