bk commit into 5.0 tree (malff:1.2310) BUG#24532

[marc.alff]

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, <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.

  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 <code>X IS [NOT] (TRUE | FALSE)</code>
+  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 <code>X IS [NOT] TRUE</code>,
+    false for <code>X IS [NOT] FALSE</code> predicates.
+  */
+  const bool m_value;
+  /**
+    True for <code>X IS Y</code>, false for <code>X IS NOT
Y</code> predicates.
+  */
+  const bool m_affirmative;
+};
+
+
+/**
+  This Item represents a <code>X IS TRUE</code> 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 <code>X IS NOT TRUE</code> 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 <code>X IS FALSE</code> 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 <code>X IS NOT FALSE</code> 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.