MySQL Bugs: #63583: A BOOL does not behave as a true BOOL with + operator

Bug #63583	A BOOL does not behave as a true BOOL with + operator
Submitted:	4 Dec 2011 20:42	Modified:	5 Dec 2011 15:17
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S3 (Non-critical)
Version:	any	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
BOOL/BOLEAN is a synonym for TINYINT(1) in MySQL.  It should then work as a BOOL but it does not.

How to repeat:
DROP TABLE IF EXISTS `test`.`booltest`;
CREATE TABLE `test`.`booltest`(`b` BOOL);
-- I know very well that SHOW CREATE will return TINYINT(1) for the column
INSERT INTO `test`.`booltest` VALUES (TRUE);
SELECT (SELECT b FROM booltest) OR TRUE; -- 1
SELECT (SELECT b FROM booltest) AND TRUE; -- 1
SELECT (SELECT b FROM booltest) * TRUE; -- 1
SELECT (SELECT b FROM booltest) + TRUE; -- 2

Suggested fix:
Can we have a true BOOL type soon? Or at least if TRUE|FALSE is specified as operator perform boolean algebra on a INTEGER within range 0..1 - also with * and + operators that are commonly used in boolean algebra as synonyms with OR and AND.

I am in doubt if this should be in categories 'types' 'optimizer' or 'parser'.  Chose parser. Parser should be smart enough to see in 3) and 4) statement that this is a boolean operation.

-- no table storage is required acutally:
SELECT 1 + TRUE; -- 2

-- also here the parser should be smart enough to understand that boolean algebra is required
SELECT TRUE + TRUE; -- 2

-- and this is plain nonsense to me.  It should return a plain error "operand(s) outside scope of boolean" or similar.
SELECT 7 + TRUE; -- 8

Please check for duplicate: http://bugs.mysql.com/bug.php?id=8485. Thanks.

I don't think it is quite a duplicate.  
1) the reqeust for a true BOOL implementation is
2) but as long as BOOL is a synonym for TINYINT(1) I'd expect smarter behaviour from the parser if TRUE|FALSE is specified as an operand.

Now look at this: 
SELECT 7 AND TRUE -- returns "1"
SELECT 7 + TRUE -- returns "8"
 .. they should both return an error IMHO. You cannot perform boolean algebra on "7" in a meaningful way.

.. but if you want you can mark as duplicate.  After all this is limitation with current implementation.

I think it's no matter about the field is bool
what matters is that the operation between the two number.
if the operation is 'AND' OR 'OR', it's a bool expression ,
so the result is '0' or '1'

and if the operation is '+' OR '-', it's Arithmetic operators, 
so the result is real.

In my opinion, it makes sense!

Well, I think this is easy. "+" operator is NOT defined on BOOL datatype. It is defined on numbers. So, whenever you use BOOL with "+" or other numeric operators it is converted to number (double, or just TINYINT is used when other operand is integer, not 100% sure) and then calculation is performed. 

This is what I expect based on our manual's explanation about implicit datatype conversions...

http://en.wikipedia.org/wiki/Boolean_algebra

"Some operations of ordinary algebra, in particular multiplication xy, addition x + y, and negation −x, have their counterparts in Boolean algebra, respectively the Boolean operations AND, OR, and NOT, also called conjunction x∧y, or Kxy, disjunction x∨y, or Axy, and negation or complement ¬x, Nx, or sometimes !x. Some authors use instead the same arithmetic operations as ordinary algebra reinterpreted for Boolean algebra, treating xy as synonymous with x∧y and x+y with x∨y."

The aritmetic operators are simply more convenient and also more readable IMHO (but maybe I am brain-damaged due to spending time programming industrial control equipment (PLC's) many years ago) and I think a TRUE BOOLEAN should handle them all. And I don't see why the optimizer cannot replace + with AND when it is obvoius that a boolean operation is requested. Just like a TRUE BOOLEAN should return an error on any operand not being "0" or "1".

You already support "SELECT !1" btw as a synonym of "SELECT NOT 1".

also ..

once a TRUE BOOLEAN is implemented I would expect it to be the result type for statments like "SELECT 1=1" and "SELECT ((SELECT 1=1) + (SELECT 0=0))" should return 1|TRUE.

But that requires that BOLEAN is implemented not only as a "data type for storage" but also as a "datatype for values in memory" (the latter was missed for BIT types 6 years ago).

Anyway .. please mark as a duplicate as Miguel suggested.

@Peter Laursen
as wiki says:
 "SOME AUTHORS" use instead the same arithmetic operations as ordinary algebra

In order to distinguish Boolean algebra from Ordinary algebra, so MySQL use "AND"
and "+", so the grammy is different in internal:
1. expr and expr %prec AND_SYM (bool)
2. bit_expr '+' bit_expr %prec '+'(arithmetic)

And in SQL92 ISO, there is even not mention the type of BOOL....
I Guess other Database , such as oracle , mssql, may use the say method to deal with Bool ^_^

SQL92 is a 20 year old standard, I think.  What I know is this:

http://en.wikipedia.org/wiki/Boolean_data_type

"For instance the ISO SQL 1999 standard defined a Boolean value as being either true, false, or unknown (SQL null). Although this convention violates the law of excluded middle, it is often useful in programming."

http://en.wikipedia.org/wiki/SQL:1999

"The SQL:1999 standard calls for a Boolean type[2], but many commercial SQL Servers (Microsoft SQL Server 2005, Oracle 9i, IBM DB2) do not support it as a column type, variable type or allow it in the results set. MySQL interprets "BOOLEAN" as a synonym for TINYINT (8-bit signed integer).[3]"

@Mr. Left (whoever you are) 

I think your latest consideration should rather be discussed in http://bugs.mysql.com/bug.php?id=8485 as you question the validity of this report. It is currently considered valid and 'verified'.

Personally I see here a feature request to redefine "+" and "*" operators to work on BOOL in non-arithmetic way. This is a valid feature request, why not, but not a bug, as current manual clearly says they are arithmetic operators. 

Please, check http://dev.mysql.com/doc/refman/5.5/en/arithmetic-functions.html#operator_plus

@Peter Laursen
I'm just a common man care about MySQL.
You are right , SQL99 declare the type of BOOLEAN,I'm out of date for i'm reading sql92,I'm sorry for that.
What i want to say is just that the question the report said is not a bug,
just so so ...

I have added a reference to this in http://bugs.mysql.com/bug.php?id=8485.

That should be enough.  I am closing here.