MySQL Bugs: #24532: The return data type of IS TRUE is different from similar operations

Bug #24532	The return data type of IS TRUE is different from similar operations
Submitted:	23 Nov 2006 3:58	Modified:	15 Mar 2007 15:13
Reporter:	Siu Ching Pong (Asuka Kenji) (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.1.12-beta, 5.0 BK	OS:	Linux (Linux (Fedora Core 5))
Assigned to:	Marc ALFF	CPU Architecture:	Any
Tags:	boolean, BOOLEAN Related, Data Type, IS FALSE, IS TRUE, predicate, rt_q1_2007

Description:
The return type of "IS TRUE" and "IS FALSE" is different from other comparison operations, as shown in the "How to repeat" section.

File Downloaded:
mysql-5.1.12-beta-linux-x86_64-icc-glibc23.tar.gz
　
Note:
I have already read http://dev.mysql.com/doc/refman/5.1/en/numeric-type-overview.html and I know that INT(1) and INT(2) differs only in display width, but I still think that I should report this issue.

How to repeat:
Statements:

DROP VIEW IF EXISTS test.v1;
DROP TABLE IF EXISTS test.t1;

CREATE TABLE test.t1 (
    a INT,
    b INT,
    c BIGINT,
    d BIGINT
);

CREATE VIEW test.v1 AS
SELECT      a IS TRUE,
            a IS FALSE,
            a IS UNKNOWN,
            a IS NULL,
            a IS NOT NULL,
            ISNULL(a),

            b IS TRUE,
            b IS FALSE,
            b IS UNKNOWN,
            b IS NULL,
            b IS NOT NULL,
            ISNULL(b),

            c IS TRUE,
            c IS FALSE,
            c IS UNKNOWN,
            c IS NULL,
            c IS NOT NULL,
            ISNULL(c),

            d IS TRUE,
            d IS FALSE,
            d IS UNKNOWN,
            d IS NULL,
            d IS NOT NULL,
            ISNULL(d)
FROM        test.t1;

DESC test.v1;

Result:

+---------------+--------+------+-----+---------+-------+
| Field         | Type   | Null | Key | Default | Extra |
+---------------+--------+------+-----+---------+-------+
| a IS TRUE     | int(2) | NO   |     | 0       |       |
| a IS FALSE    | int(2) | NO   |     | 0       |       |
| a IS UNKNOWN  | int(1) | NO   |     | 0       |       |
| a IS NULL     | int(1) | NO   |     | 0       |       |
| a IS NOT NULL | int(1) | NO   |     | 0       |       |
| ISNULL(a)     | int(1) | NO   |     | 0       |       |
| b IS TRUE     | int(2) | NO   |     | 0       |       |
| b IS FALSE    | int(2) | NO   |     | 0       |       |
| b IS UNKNOWN  | int(1) | NO   |     | 0       |       |
| b IS NULL     | int(1) | NO   |     | 0       |       |
| b IS NOT NULL | int(1) | NO   |     | 0       |       |
| ISNULL(b)     | int(1) | NO   |     | 0       |       |
| c IS TRUE     | int(2) | NO   |     | 0       |       |
| c IS FALSE    | int(2) | NO   |     | 0       |       |
| c IS UNKNOWN  | int(1) | NO   |     | 0       |       |
| c IS NULL     | int(1) | NO   |     | 0       |       |
| c IS NOT NULL | int(1) | NO   |     | 0       |       |
| ISNULL(c)     | int(1) | NO   |     | 0       |       |
| d IS TRUE     | int(2) | NO   |     | 0       |       |
| d IS FALSE    | int(2) | NO   |     | 0       |       |
| d IS UNKNOWN  | int(1) | NO   |     | 0       |       |
| d IS NULL     | int(1) | NO   |     | 0       |       |
| d IS NOT NULL | int(1) | NO   |     | 0       |       |
| ISNULL(d)     | int(1) | NO   |     | 0       |       |
+---------------+--------+------+-----+---------+-------+
24 rows in set (0.00 sec)

Suggested fix:
Make the return data type of all the above operations to BOOLEAN, i.e. TINYINT(1) in 5.1.12-beta.

Thank you for the report.

Verified as described.

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/16897

ChangeSet@1.2310, 2006-12-13 10:26:56-07:00, malff@weblab.(none) +6 -0
  Bug#12976 (Boolean values reversed in stored procedures?)
  Bug#24532 (The return data type of IS TRUE is different from similar
    operations)
  
  Before this change, the boolean predicates:
  - X IS TRUE,
  - X IS NOT TRUE,
  - X IS FALSE,
  - X IS NOT FALSE
  were implemented by expanding the Item tree in the parser, by using a
  construct like:
  Item_func_if(Item_func_ifnull(X, <value>), <value>, <value>)
  
  This caused several issues:
  
  a) When "X" is an item of a coercible type, like variables used in stored
  procedures, this implementation would cause the if to evaluate a boolean,
  which cause the ifnull to evaluate a value ... which cause the item to
  evaluate itself based on it's intrinsic type.
  For stored procedures, that resulted in evaluating what should be a bit(1)
  into a string, causing the bug 12976.
  
  b) With regards to types, the resulting type of this expression is not a
  boolean predicate, but rather has the same type as X, causing X IS NULL to
  evaluate sometimes as an int(2), as seen with bug 24532.
  
  c) With views, transforming the Item tree during parsing and printing it
  later in the view storage file cause the semantic of the view to be altered.
  What was described by the user as "X IS TRUE" is stored as if(isnull(X), ...
  
  With this change:
  
  - Expanding the item tree in the parser has been removed. Instead, dedicated
  Item classes have been created to correctly represent the boolean
  predicates.
  
  - Each predicate is typed as int(1), which is the current implementation for
  booleans. The SQL 'BOOLEAN' type is currently not implemented in MySQL,
  and is the subject of an enhancement unrelated to this fix.
  
  - Views using 'X IS TRUE' and similar predicates are affected, the following
    way:
  
  1) For views which have been created prior to this fix, the view will
     continue to be supported, with no changes. In particular, the exact type
     of an 'X IS TRUE' column in such a view will be unchanged, and might not
     be int(1).
     In other words, there is no incompatible change introduced.
  
  2) For views created after this fix, an 'X IS TRUE' column will be stored
     and restored as such, and will evaluate as a boolean predicate (int(1)).
  
  3) For existing views to be affected by this fix for point b) and c),
     the user will have to DROP the view, and CREATE the view again ...
     and this is a critical point : create the view from the original script,
     not from the result of SHOW CREATE VIEW.

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/19583

ChangeSet@1.2394, 2007-02-08 13:12:28-07:00, malff@weblab.(none) +7 -0
  Bug#24532 (The return data type of IS TRUE is different from similar
    operations)
  
  Before this change, the boolean predicates:
  - X IS TRUE,
  - X IS NOT TRUE,
  - X IS FALSE,
  - X IS NOT FALSE
  were implemented by expanding the Item tree in the parser, by using a
  construct like:
  Item_func_if(Item_func_ifnull(X, <value>), <value>, <value>)
  
  Each <value> was a constant integer, either 0 or 1.
  
  A bug in the implementation of the function IF(a, b, c), in
  Item_func_if::fix_length_and_dec(), would cause the following :
  
  When the arguments b and c are both unsigned, the result type of the
  function was signed, instead of unsigned.
  
  When the result of the if function is signed, space for the sign could be
  counted twice (in the max() expression for a signed argument, and in the
  total), causing the member max_length to be too high.
  
  An effect of this is that the final type of IF(x, int(1), int(1)) would be
  int(2) instead of int(1).
  
  With this fix, the problems found in Item_func_if::fix_length_and_dec()
  have been fixed.
  
  While it's semantically correct to represent 'X IS TRUE' with
  Item_func_if(Item_func_ifnull(X, <value>), <value>, <value>),
  there are however more problems with this construct.
  
  a)
  Building the parse tree involves :
  - creating 5 Item instances (3 ints, 1 ifnull, 1 if),
  - creating each Item calls my_pthread_getspecific_ptr() once in the operator
    new(size), and a second time in the Item::Item() constructor, resulting
    in a total of 10 calls to get the current thread.
  Evaluating the expression involves evaluating up to 4 nodes at runtime.
  This representation could be greatly simplified and improved.
  
  b)
  Transforming the parse tree internally with if(ifnull(...)) is fine as long
  as this transformation is internal to the server implementation.
  With views however, the result of the parse tree is later exposed by the
  ::print() functions, and stored as part of the view definition.
  Doing this has long term consequences:
  
  1)
  The original semantic 'X IS TRUE' is lost, and replaced by the
  if(ifnull(...)) expression. As a result, SHOW CREATE VIEW does not restore
  the original code.
  
  2)
  Should a future version of MySQL implement the SQL BOOLEAN data type for
  example, views created today using 'X IS NULL' can be exported using
  mysqldump, and imported again. Such views would be converted correctly and
  automatically to use a BOOLEAN column in the future version.
  With 'X IS TRUE' and the current implementations, views using these
  "boolean" predicates would not be converted during the export/import, and
  would use integer columns instead.
  The difference traces back to how SHOW CREATE VIEW preserves 'X IS NULL' but
  does not preserve the 'X IS TRUE' semantic.
  
  With this fix, internal representation of 'X IS TRUE' booleans predicates
  has changed, so that:
  - dedicated Item classes are created for each predicate,
  - only 1 Item is created to represent 1 predicate
  - my_pthread_getspecific_ptr() is invoked 1 time instead of 10
  - SHOW CREATE VIEW preserves the original semantic, and prints 'X IS TRUE'.
  
  Note that, because of the fix in Item_func_if, views created before this fix
  will:
  - correctly use a int(1) type instead of int(2) for boolean predicates,
  - incorrectly print the if(ifnull(...), ...) expression in SHOW CREATE VIEW,
  since the original semantic (X IS TRUE) has been lost.
  - except for the syntax used in SHOW CREATE VIEW, these views will operate
  properly, no action is needed.
  
  Views created after this fix will operate correctly, and will preserve the
  original code semantic in SHOW CREATE VIEW.

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/19733

ChangeSet@1.2394, 2007-02-12 13:59:29-07:00, malff@weblab.(none) +7 -0
  Bug#24532 (The return data type of IS TRUE is different from similar
    operations)
  
  Before this change, the boolean predicates:
  - X IS TRUE,
  - X IS NOT TRUE,
  - X IS FALSE,
  - X IS NOT FALSE
  were implemented by expanding the Item tree in the parser, by using a
  construct like:
  Item_func_if(Item_func_ifnull(X, <value>), <value>, <value>)
  
  Each <value> was a constant integer, either 0 or 1.
  
  A bug in the implementation of the function IF(a, b, c), in
  Item_func_if::fix_length_and_dec(), would cause the following :
  
  When the arguments b and c are both unsigned, the result type of the
  function was signed, instead of unsigned.
  
  When the result of the if function is signed, space for the sign could be
  counted twice (in the max() expression for a signed argument, and in the
  total), causing the member max_length to be too high.
  
  An effect of this is that the final type of IF(x, int(1), int(1)) would be
  int(2) instead of int(1).
  
  With this fix, the problems found in Item_func_if::fix_length_and_dec()
  have been fixed.
  
  While it's semantically correct to represent 'X IS TRUE' with
  Item_func_if(Item_func_ifnull(X, <value>), <value>, <value>),
  there are however more problems with this construct.
  
  a)
  Building the parse tree involves :
  - creating 5 Item instances (3 ints, 1 ifnull, 1 if),
  - creating each Item calls my_pthread_getspecific_ptr() once in the operator
    new(size), and a second time in the Item::Item() constructor, resulting
    in a total of 10 calls to get the current thread.
  Evaluating the expression involves evaluating up to 4 nodes at runtime.
  This representation could be greatly simplified and improved.
  
  b)
  Transforming the parse tree internally with if(ifnull(...)) is fine as long
  as this transformation is internal to the server implementation.
  With views however, the result of the parse tree is later exposed by the
  ::print() functions, and stored as part of the view definition.
  Doing this has long term consequences:
  
  1)
  The original semantic 'X IS TRUE' is lost, and replaced by the
  if(ifnull(...)) expression. As a result, SHOW CREATE VIEW does not restore
  the original code.
  
  2)
  Should a future version of MySQL implement the SQL BOOLEAN data type for
  example, views created today using 'X IS NULL' can be exported using
  mysqldump, and imported again. Such views would be converted correctly and
  automatically to use a BOOLEAN column in the future version.
  With 'X IS TRUE' and the current implementations, views using these
  "boolean" predicates would not be converted during the export/import, and
  would use integer columns instead.
  The difference traces back to how SHOW CREATE VIEW preserves 'X IS NULL' but
  does not preserve the 'X IS TRUE' semantic.
  
  With this fix, internal representation of 'X IS TRUE' booleans predicates
  has changed, so that:
  - dedicated Item classes are created for each predicate,
  - only 1 Item is created to represent 1 predicate
  - my_pthread_getspecific_ptr() is invoked 1 time instead of 10
  - SHOW CREATE VIEW preserves the original semantic, and prints 'X IS TRUE'.
  
  Note that, because of the fix in Item_func_if, views created before this fix
  will:
  - correctly use a int(1) type instead of int(2) for boolean predicates,
  - incorrectly print the if(ifnull(...), ...) expression in SHOW CREATE VIEW,
  since the original semantic (X IS TRUE) has been lost.
  - except for the syntax used in SHOW CREATE VIEW, these views will operate
  properly, no action is needed.
  
  Views created after this fix will operate correctly, and will preserve the
  original code semantic in SHOW CREATE VIEW.

Pushed into 5.0.38 and 5.1.17.

Noted in 5.0.38, 5.1.17 changelogs.

Predicates of the form IS [NOT] {TRUE|FALSE} were evaluated to a
result with a data type of INT(2), not INT(1) as for other
comparisons. This has been corrected, but for views there is an
implication: Existing views that use the affected construct will
continue to return INT(2) values. If this is an issue, drop the view
and create it again from the original definition (do not create it
again by using the output of SHOW CREATE VIEW).

Updated changelog entry:

IF(expr, unsigned_expr, unsigned_expr) was evaluated to a signed
result, not unsigned. This has been corrected. The fix also affects
constructs of the form IS [NOT] {TRUE|FALSE}, which were transformed
internally into IF() expressions that evaluated to a signed result.

For existing views that were defined using IS [NOT] {TRUE|FALSE} 
constructs, there is a related implication. The definitions of such
views were stored using the IF() expression, not the original
construct. This is manifest in that SHOW CREATE VIEW shows the
transformed IF() expression, not the original one. Existing views
will evaluate correctly after the fix, but if you want SHOW CREATE
VIEW to display the original construct, you must drop the view and
re-create it using its original definition. New views will retain the
construct in their definition.