Description:
Why not do calculations on a BIT? Documentation states that BIT is a 'numerical type'. What would you use 'numerical types' for if not for calculation?
It is actully possible to do. But results are unpredictable!
How to repeat:
-- create and populate table like this
DROP TABLE IF EXISTS bittest2;
CREATE TABLE `bittest2` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`b` BIT(2) DEFAULT NULL,
PRIMARY KEY (`id`));
INSERT INTO bittest2 (b) VALUES (b'11'), (b'10'), (b'01'), (b'00');
1)
-- Sometimes a (mystical result) is retrieved. Sometimes nothing.
mysql> SELECT b FROM bittest2 WHERE id = 2/(SELECT b FROM bittest WHERE id = 1);
+------+
| b |
+------+
| ☻ | -- this is a b'10'' - ie. decimal 2.
+------+
1 row in set (0.00 sec)
mysql> SELECT b FROM bittest2 WHERE id = 2/(SELECT b FROM bittest WHERE id =3);
Empty set (0.00 sec)
mysql> SELECT b FROM bittest2 WHERE id = 1/(SELECT b FROM bittest WHERE id =3);
Empty set (0.00 sec)
2)
It obviously returns a BIT type in the first result. But why can I not then add 1, use BIN() or CAST? Documentation says that to display a BIT in readable form I should! But I guess parser fails here!?
mysql> SELECT b FROM bittest2 WHERE id = 2/(SELECT b FROM bittest WHERE id = 1);
+------+
| b |
+------+
| ☻ |
+------+
1 row in set (0.00 sec)
-- trying BIN()
mysql> SELECT BIN(b FROM bittest2 WHERE id = 2/(SELECT b FROM bittest WHERE id =
1));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'FROM
bittest2 WHERE id = 2/(SELECT b FROM bittest WHERE id = 1))' at line 1
-- Trying CAST()
mysql> SELECT CAST((b FROM bittest2 WHERE id = 2/(SELECT b FROM bittest WHERE id
= 1)) AS INTEGER);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'FROM
bittest2 WHERE id = 2/(SELECT b FROM bittest WHERE id = 1)) AS INTEGER)' at line
1
-- Trying to add 1
mysql> SELECT (b FROM bittest2 WHERE id = 2/(SELECT b FROM bittest WHERE id = 1)
) + 1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'FROM
bittest2 WHERE id = 2/(SELECT b FROM bittest WHERE id = 1)) + 1' at line 1
mysql>
3)
Dividing a BIT with an INTEGER
SELECT b + 1 FROM bittest2 WHERE id = 2; -- returns 3
SELECT (SELECT b FROM bittest2 WHERE id = 2)/4; RETURNS 0.5000. 3/4= ½ -:)
Suggested fix:
This report is not as stupid as it seems: It is related to the behavior of AVG aggregate function on BITs discussed here: http://bugs.mysql.com/bug.php?id=63470
Can a BIT be divided by (or multipled with, added to or subtracted from) another BIT? Same question as regards an INTEGER or a decimal number (even negative)?
The server happily calculates anyhing on a BIT no matter if it makes sense or not.
