MySQL Bugs: #63505: calculations on BIT type

Bug #63505	calculations on BIT type
Submitted:	30 Nov 2011 12:27	Modified:	6 Jan 2012 11:11
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.0+	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
You may calculate (add, subtract, cultiplicate, divide) on BIT values as it is a 'numerical type'. Belew some examples:

How to repeat:
(I am using SQLyog here (contrary to command line it identifies BIT results by displaying them in b'..' format.  I think also Workbench does.  In command line it is not easy to distinguish if the client receives 1 as an INTEGER or 'b'1' as a BIT/BINARY in metadata)

DROP TABLE IF EXISTS bittest2;
CREATE TABLE bittest2 (id INT, b BIT(2));
INSERT INTO bittest2 (b) VALUES (b'00'), (b'01'), (b'10');

SELECT b FROM bittest2 WHERE id = 1; -- returns b'0'
SELECT b FROM bittest2 WHERE id = 2; -- returns b'1'

SELECT ((SELECT b FROM bittest2 WHERE id = 1) + (SELECT b FROM bittest2 WHERE id = 2)); -- returns 1 (b'0'+b'1' is calculated by server and result returned to client as INTEGER in metadata)
SELECT ((SELECT b FROM bittest2 WHERE id = 1) - (SELECT b FROM bittest2 WHERE id = 2));
/*
Error Code: 1690
BIGINT UNSIGNED value is out of range in '((select ''
*/

SELECT ((SELECT b FROM bittest2 WHERE id = 2) * (SELECT b FROM bittest2 WHERE id = 3)); -- returns 2 (1*2)
SELECT ((SELECT b FROM bittest2 WHERE id = 2) * (SELECT b FROM bittest2 WHERE id = 3) * (-1));
/*
Error Code: 1690
BIGINT UNSIGNED value is out of range in '((select ''
*/

Suggested fix:
1) At least use a BIGINT *SIGNED* for addition, subtraction and multiplication -results.

2) And actually I would prefer that addition, subtraction and multiplication of BITs returned a BIT.  I cannot think of those operations between same datatypes returning a different datatype for other types.  That could result in overflow of course, but that is not different from INTEGERs!

3) Why can't we have a BIT(n) SIGNED in order to handle (intermediate) negative results?

I think they all make sense as long as you document BIT as a 'numerical' type.

-- even this fails:
SELECT b*(-1) FROM bittest2 WHERE id = 2;
/*
Error Code: 1690
BIGINT UNSIGNED value is out of range in '(`test`.`bittest2`.`b` * -(1))'
*/

So 1) I consider a plain bug.  

As regards 2) and 3) I realize that it probably cannot be done without without significant changes in the server.  I think I understand that *BIT* applies for storage in a table, but not when loaded into to memory. In memory it will need to be handled as a BINARY, a CHAR or a numerical of some kind (if I am right). But then this is IMHO just another example that *BIT* was rushed through without proper design considerations back in early 5.0 days.

Maybe there is a documentation request here as well? "When used as an operand in an expression or as a parameter to a function a BIT is CAST to a BIGINT SIGNED before evaluation. This also applies to aggregate functions that perform arithmetical calculations on BIT_values (such as AVG)"

Is there something wrong with your script below?(you didn't give the value for id)
INSERT INTO bittest2 (b) VALUES (b'00'), (b'01'), (b'10');

And it don't reappear in version "5.5.15-debug Source distribution",is it only appear in 5.0?

@Left.  'id' is auto_increment.

.. but I missed "PRIMARY KEY AUTO_INCREMENT" for ìd` I see now!

And my proposal 1) will reduce the effective use of BIT(n) from BIT(64) to BIT(63), I think ...

BIT is really a bad design from the beginning!

this is enough to illustrate the problem here:

DROP TABLE IF EXISTS bittest;
CREATE TABLE bittest(b BIT(2));
INSERT INTO bittest VALUES (b'11');
SET @bit = (SELECT b FROM bittest LIMIT 1);
SELECT @bit; -- returns 3 as an integer

.. so with current implementation this report is probably a documentation request ("BIT applies to data stored on disk only. When loaded into memory a BIT is converted to a BIGINT.")

However http://bugs.mysql.com/bug.php?id=63470 is not (only) a documentation request.  Here we have wrong/inconsistent metadata.

This is what I see:

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -uroot -proot -P3310 --column-
type-info test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.1.58-community-log MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> DROP TABLE IF EXISTS bittest;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> CREATE TABLE bittest(b BIT(2));
Query OK, 0 rows affected (0.19 sec)

mysql> INSERT INTO bittest VALUES (b'11');
Query OK, 1 row affected (0.11 sec)

mysql> select b from bittest;
Field   1:  `b`
Catalog:    `def`
Database:   `test`
Table:      `bittest`
Org_table:  `bittest`
Type:       BIT
Collation:  binary (63)
Length:     2
Max_length: 1
Decimals:   0
Flags:      UNSIGNED

+------+
| b    |
+------+
| ♥    |
+------+
1 row in set (0.06 sec)

So, we have correct metadata (and yes, BIT is unsigned, but how you'd like to implement signed operations on bits?). Data type changes only in context (when you try to use BIT with arithmetic operators like "-", or with user variable assignment):

mysql> set @bit=(select b from bittest);
Query OK, 0 rows affected (0.03 sec)

mysql> select @bit;
Field   1:  `@bit`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONGLONG
Collation:  binary (63)
Length:     20
Max_length: 1
Decimals:   0
Flags:      UNSIGNED BINARY NUM

+------+
| @bit |
+------+
|    3 |
+------+
1 row in set (0.03 sec)

So, now we have unsigned number in a user variable. But this is also documented to some extent at http://dev.mysql.com/doc/refman/5.1/en/user-variables.html:

"User variables can be assigned a value from a limited set of data types: integer, decimal, floating-point, binary or nonbinary string, or NULL value. Assignment of decimal and real values does not preserve the precision or scale of the value. A value of a type other than one of the permissible types is converted to a permissible type."

So, what exactly is a bug here, or what exactly you'd like to document in some extra/more clear way? Please, confirm.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".