From: Date: December 13 2006 6:27pm Subject: bk commit into 5.0 tree (malff:1.2310) BUG#24532 List-Archive: http://lists.mysql.com/commits/16897 X-Bug: 24532 Message-Id: <20061213172701.6A9928E18B3@weblab.mysql.com> Below is the list of changes that have just been committed into a local 5.0 repository of marcsql. When marcsql does a push these changes will be propagated to the main repository and, within 24 hours after the push, to the public repository. For information on how to access the public repository see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html ChangeSet@stripped, 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, ), , ) 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. mysql-test/r/sp-vars.result@stripped, 2006-12-13 10:25:34-07:00, malff@weblab.(none) +1 -1 Implement 'X IS TRUE' as a boolean predicate. mysql-test/r/view.result@stripped, 2006-12-13 10:25:34-07:00, malff@weblab.(none) +87 -0 Implement 'X IS TRUE' as a boolean predicate. mysql-test/t/view.test@stripped, 2006-12-13 10:25:34-07:00, malff@weblab.(none) +63 -0 Implement 'X IS TRUE' as a boolean predicate. sql/item_cmpfunc.cc@stripped, 2006-12-13 10:25:34-07:00, malff@weblab.(none) +48 -0 Implement 'X IS TRUE' as a boolean predicate. sql/item_cmpfunc.h@stripped, 2006-12-13 10:25:34-07:00, malff@weblab.(none) +86 -0 Implement 'X IS TRUE' as a boolean predicate. sql/sql_yacc.yy@stripped, 2006-12-13 10:25:34-07:00, malff@weblab.(none) +8 -13 Implement 'X IS TRUE' as a boolean predicate. # This is a BitKeeper patch. What follows are the unified diffs for the # set of deltas contained in the patch. The rest of the patch, the part # that BitKeeper cares about, is below these diffs. # User: malff # Host: weblab.(none) # Root: /home/marcsql/TREE/mysql-5.0-12976 --- 1.224/sql/item_cmpfunc.cc 2006-12-13 10:27:01 -07:00 +++ 1.225/sql/item_cmpfunc.cc 2006-12-13 10:27:01 -07:00 @@ -721,6 +721,54 @@ int Arg_comparator::compare_e_row() return 1; } +void Item_func_truth::fix_length_and_dec() +{ + maybe_null= 0; + null_value= 0; + decimals= 0; + max_length= 1; +} + +void Item_func_truth::print(String *str) +{ + str->append('('); + args[0]->print(str); + str->append(STRING_WITH_LEN(" is ")); + if (! m_affirmative) + str->append(STRING_WITH_LEN("not ")); + if (m_value) + str->append(STRING_WITH_LEN("true")); + else + str->append(STRING_WITH_LEN("false")); + str->append(')'); +} + +bool Item_func_truth::val_bool() +{ + bool val= args[0]->val_bool(); + if (args[0]->null_value) + { + /* + NULL val IS {TRUE, FALSE} --> FALSE + NULL val IS NOT {TRUE, FALSE} --> TRUE + */ + return (! m_affirmative); + } + + if (m_affirmative) + { + /* {TRUE, FALSE} val IS {TRUE, FALSE} m_value */ + return (val == m_value); + } + + /* {TRUE, FALSE} val IS NOT {TRUE, FALSE} m_value */ + return (val != m_value); +} + +longlong Item_func_truth::val_int() +{ + return (val_bool() ? 1 : 0); +} bool Item_in_optimizer::fix_left(THD *thd, Item **ref) { --- 1.133/sql/item_cmpfunc.h 2006-12-13 10:27:01 -07:00 +++ 1.134/sql/item_cmpfunc.h 2006-12-13 10:27:01 -07:00 @@ -99,6 +99,92 @@ public: uint decimal_precision() const { return 1; } }; + +/** + Abstract Item class, to represent X IS [NOT] (TRUE | FALSE) + boolean predicates. +*/ + +class Item_func_truth : public Item_bool_func +{ +public: + virtual bool val_bool(); + virtual longlong val_int(); + virtual void fix_length_and_dec(); + virtual void print(String *str); + +protected: + Item_func_truth(Item *a, bool value, bool affirmative) + : Item_bool_func(a), m_value(value), m_affirmative(affirmative) + {} + + ~Item_func_truth() + {} +private: + /** + True for X IS [NOT] TRUE, + false for X IS [NOT] FALSE predicates. + */ + const bool m_value; + /** + True for X IS Y, false for X IS NOT Y predicates. + */ + const bool m_affirmative; +}; + + +/** + This Item represents a X IS TRUE boolean predicate. +*/ + +class Item_func_istrue : public Item_func_truth +{ +public: + Item_func_istrue(Item *a) : Item_func_truth(a, true, true) {} + ~Item_func_istrue() {} + virtual const char* func_name() const { return "istrue"; } +}; + + +/** + This Item represents a X IS NOT TRUE boolean predicate. +*/ + +class Item_func_isnottrue : public Item_func_truth +{ +public: + Item_func_isnottrue(Item *a) : Item_func_truth(a, true, false) {} + ~Item_func_isnottrue() {} + virtual const char* func_name() const { return "isnottrue"; } +}; + + +/** + This Item represents a X IS FALSE boolean predicate. +*/ + +class Item_func_isfalse : public Item_func_truth +{ +public: + Item_func_isfalse(Item *a) : Item_func_truth(a, false, true) {} + ~Item_func_isfalse() {} + virtual const char* func_name() const { return "isfalse"; } +}; + + +/** + This Item represents a X IS NOT FALSE boolean predicate. +*/ + +class Item_func_isnotfalse : public Item_func_truth +{ +public: + Item_func_isnotfalse(Item *a) : Item_func_truth(a, false, false) {} + ~Item_func_isnotfalse() {} + virtual const char* func_name() const { return "isnotfalse"; } +}; + + class Item_cache; class Item_in_optimizer: public Item_bool_func { --- 1.495/sql/sql_yacc.yy 2006-12-13 10:27:01 -07:00 +++ 1.496/sql/sql_yacc.yy 2006-12-13 10:27:01 -07:00 @@ -59,15 +59,6 @@ const LEX_STRING null_lex_str={0,0}; YYABORT; \ } -/* 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)); -} - #ifndef DBUG_OFF #define YYDEBUG 1 #else @@ -4194,10 +4185,14 @@ bool_factor: | bool_test ; 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 TRUE_SYM + { $$= new (YYTHD->mem_root) Item_func_istrue($1); } + | bool_pri IS not TRUE_SYM + { $$= new (YYTHD->mem_root) Item_func_isnottrue($1); } + | bool_pri IS FALSE_SYM + { $$= new (YYTHD->mem_root) Item_func_isfalse($1); } + | bool_pri IS not FALSE_SYM + { $$= new (YYTHD->mem_root) Item_func_isnotfalse($1); } | bool_pri IS UNKNOWN_SYM { $$= new Item_func_isnull($1); } | bool_pri IS not UNKNOWN_SYM { $$= new Item_func_isnotnull($1); } | bool_pri ; --- 1.5/mysql-test/r/sp-vars.result 2006-12-13 10:27:01 -07:00 +++ 1.6/mysql-test/r/sp-vars.result 2006-12-13 10:27:01 -07:00 @@ -750,7 +750,7 @@ call p2(); HEX(vb) vb = 0 vb = FALSE vb IS FALSE vb = 1 vb = TRUE vb IS TRUE 00 1 1 1 0 0 0 HEX(vb) vb = 0 vb = FALSE vb IS FALSE vb = 1 vb = TRUE vb IS TRUE -01 0 0 1 1 1 0 +01 0 0 0 1 1 1 DROP TABLE t1; DROP PROCEDURE p1; DROP PROCEDURE p2; --- 1.184/mysql-test/r/view.result 2006-12-13 10:27:01 -07:00 +++ 1.185/mysql-test/r/view.result 2006-12-13 10:27:01 -07:00 @@ -3014,4 +3014,91 @@ i j 6 3 DROP VIEW v1, v2; DROP TABLE t1; +drop view if exists view_24532; +drop table if exists table_24532; +create table table_24532 ( +a int, +b bigint, +c int(4), +d bigint(48) +); +create view view_24532 as +select +a IS TRUE, +a IS NOT TRUE, +a IS FALSE, +a IS NOT FALSE, +a IS UNKNOWN, +a IS NOT UNKNOWN, +a is NULL, +a IS NOT NULL, +ISNULL(a), +b IS TRUE, +b IS NOT TRUE, +b IS FALSE, +b IS NOT FALSE, +b IS UNKNOWN, +b IS NOT UNKNOWN, +b is NULL, +b IS NOT NULL, +ISNULL(b), +c IS TRUE, +c IS NOT TRUE, +c IS FALSE, +c IS NOT FALSE, +c IS UNKNOWN, +c IS NOT UNKNOWN, +c is NULL, +c IS NOT NULL, +ISNULL(c), +d IS TRUE, +d IS NOT TRUE, +d IS FALSE, +d IS NOT FALSE, +d IS UNKNOWN, +d IS NOT UNKNOWN, +d is NULL, +d IS NOT NULL, +ISNULL(d) +from table_24532; +describe view_24532; +Field Type Null Key Default Extra +a IS TRUE int(1) NO 0 +a IS NOT TRUE int(1) NO 0 +a IS FALSE int(1) NO 0 +a IS NOT FALSE int(1) NO 0 +a IS UNKNOWN int(1) NO 0 +a IS NOT 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(1) NO 0 +b IS NOT TRUE int(1) NO 0 +b IS FALSE int(1) NO 0 +b IS NOT FALSE int(1) NO 0 +b IS UNKNOWN int(1) NO 0 +b IS NOT 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(1) NO 0 +c IS NOT TRUE int(1) NO 0 +c IS FALSE int(1) NO 0 +c IS NOT FALSE int(1) NO 0 +c IS UNKNOWN int(1) NO 0 +c IS NOT 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(1) NO 0 +d IS NOT TRUE int(1) NO 0 +d IS FALSE int(1) NO 0 +d IS NOT FALSE int(1) NO 0 +d IS UNKNOWN int(1) NO 0 +d IS NOT 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 +drop view view_24532; +drop table table_24532; End of 5.0 tests. --- 1.170/mysql-test/t/view.test 2006-12-13 10:27:01 -07:00 +++ 1.171/mysql-test/t/view.test 2006-12-13 10:27:01 -07:00 @@ -2959,5 +2959,68 @@ SELECT * FROM t1; DROP VIEW v1, v2; DROP TABLE t1; +# +# Bug#24532: The return data type of IS TRUE is different from similar +# operations +# + +--disable_warnings +drop view if exists view_24532; +drop table if exists table_24532; +--enable_warnings + +create table table_24532 ( + a int, + b bigint, + c int(4), + d bigint(48) +); + +create view view_24532 as +select + a IS TRUE, + a IS NOT TRUE, + a IS FALSE, + a IS NOT FALSE, + a IS UNKNOWN, + a IS NOT UNKNOWN, + a is NULL, + a IS NOT NULL, + ISNULL(a), + b IS TRUE, + b IS NOT TRUE, + b IS FALSE, + b IS NOT FALSE, + b IS UNKNOWN, + b IS NOT UNKNOWN, + b is NULL, + b IS NOT NULL, + ISNULL(b), + c IS TRUE, + c IS NOT TRUE, + c IS FALSE, + c IS NOT FALSE, + c IS UNKNOWN, + c IS NOT UNKNOWN, + c is NULL, + c IS NOT NULL, + ISNULL(c), + d IS TRUE, + d IS NOT TRUE, + d IS FALSE, + d IS NOT FALSE, + d IS UNKNOWN, + d IS NOT UNKNOWN, + d is NULL, + d IS NOT NULL, + ISNULL(d) +from table_24532; + +describe view_24532; + +drop view view_24532; +drop table table_24532; + + --echo End of 5.0 tests.