bk commit into 5.0 tree (malff:1.2395) BUG#26093

[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, 2007-02-14 20:42:33-07:00, malff@weblab.(none) +3 -0
  Bug#26093 (SELECT BENCHMARK() for SELECT statements does not produce valid
    results)
  
  Before this fix, the function BENCHMARK() would fail to evaluate expressions
  like "(select avg(a) from t1)" in debug builds (with an assert),
  or would report a time of zero in non debug builds.
  
  The root cause is that evaluation of DECIMAL_RESULT expressions was not
  supported in Item_func_benchmark::val_int().
  
  This is a bug, which has been fixed by this change.
  
  Independently of this fix, the behavior of BENCHMARK() needs to be better
  documented in the user manual to clarify expectations:
  
  "select BENCHMARK(N, expression)" is intended to measure the performance of
  the runtime evaluation of scalar expressions.
  This has major implications:
  
  a)
  Only scalar expressions can be used: a statement like "select * from t1",
  when returning multiple rows, or a more general statement like "create
  table" can not be benchmarked using this function.
  
  b)
    [1] select expression;
    [2] select expression;
    ...
    [N] select expression;
  and
    select BENCHMARK(N, expression)
  have a very different execution profile.
  
  With the former, the parser, the optimizer, table locking, and runtime
  evaluation are each invoked N times.
  With the later, the parser is invoked once, the optimizer is invoked once,
  tables are opened and locked once, and runtime evaluation is executed N
  times, while reusing memory structures already allocated.
  In addition, runtime optimizations, like local caching of results already
  evaluated for aggregate functions like avg(), can alter the results.
  
  There is no expectation that
    time("select BENCHMARK(N, expression)")
  is equivalent to
    N * time("select expression")
  since to the contrary, the purpose of BENCHMARK() is to remove the noise
  created by the network, the parser, the optimizer, etc,
  to measure the performance of the runtime component itself,
  by giving more weight (repeat the evaluation N times) to this part.

  mysql-test/r/func_misc.result@stripped, 2007-02-14 20:38:47-07:00, malff@weblab.(none) +42
-0
    Added support for DECIMAL_RESULT in Item_func_benchmark::val_int()

  mysql-test/t/func_misc.test@stripped, 2007-02-14 20:38:48-07:00, malff@weblab.(none) +54 -0
    Added support for DECIMAL_RESULT in Item_func_benchmark::val_int()

  sql/item_func.cc@stripped, 2007-02-14 20:38:48-07:00, malff@weblab.(none) +4 -0
    Added support for DECIMAL_RESULT in Item_func_benchmark::val_int()

# 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-26093

--- 1.322/sql/item_func.cc	2007-02-14 20:42:38 -07:00
+++ 1.323/sql/item_func.cc	2007-02-14 20:42:38 -07:00
@@ -3456,6 +3456,7 @@ longlong Item_func_benchmark::val_int()
   DBUG_ASSERT(fixed == 1);
   char buff[MAX_FIELD_WIDTH];
   String tmp(buff,sizeof(buff), &my_charset_bin);
+  my_decimal tmp_decimal;
   THD *thd=current_thd;
 
   for (ulong loop=0 ; loop < loop_count && !thd->killed; loop++)
@@ -3469,6 +3470,9 @@ longlong Item_func_benchmark::val_int()
       break;
     case STRING_RESULT:
       (void) args[0]->val_str(&tmp);
+      break;
+    case DECIMAL_RESULT:
+      (void) args[0]->val_decimal(&tmp_decimal);
       break;
     case ROW_RESULT:
     default:

--- 1.26/mysql-test/r/func_misc.result	2007-02-14 20:42:38 -07:00
+++ 1.27/mysql-test/r/func_misc.result	2007-02-14 20:42:38 -07:00
@@ -141,4 +141,46 @@ t1	CREATE TABLE `t1` (
   `a` bigint(21) unsigned default NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1;
+drop table if exists table_26093;
+drop function if exists func_26093_a;
+drop function if exists func_26093_b;
+create table table_26093(a int);
+insert into table_26093 values
+(1), (2), (3), (4), (5),
+(6), (7), (8), (9), (10);
+create function func_26093_a(x int) returns int
+begin
+set @invoked := @invoked + 1;
+return x;
+end||
+create function func_26093_b(x int, y int) returns int
+begin
+set @invoked := @invoked + 1;
+return x;
+end||
+select avg(a) from table_26093;
+avg(a)
+5.5000
+select benchmark(100, (select avg(a) from table_26093));
+benchmark(100, (select avg(a) from table_26093))
+0
+set @invoked := 0;
+select benchmark(100, (select avg(func_26093_a(a)) from table_26093));
+benchmark(100, (select avg(func_26093_a(a)) from table_26093))
+0
+select @invoked;
+@invoked
+10
+set @invoked := 0;
+select benchmark(100, (select avg(func_26093_b(a, rand())) from table_26093));
+benchmark(100, (select avg(func_26093_b(a, rand())) from table_26093))
+0
+select @invoked;
+@invoked
+1000
+select benchmark(100, (select (a) from table_26093));
+ERROR 21000: Subquery returns more than 1 row
+drop table table_26093;
+drop function func_26093_a;
+drop function func_26093_b;
 End of 5.0 tests

--- 1.20/mysql-test/t/func_misc.test	2007-02-14 20:42:38 -07:00
+++ 1.21/mysql-test/t/func_misc.test	2007-02-14 20:42:38 -07:00
@@ -132,4 +132,58 @@ set global query_cache_size=default;
 create table t1 select INET_ATON('255.255.0.1') as `a`;
 show create table t1;
 drop table t1;
+
+#
+# Bug#26093 (SELECT BENCHMARK() for SELECT statements does not produce
+#   valid results)
+#
+
+--disable_warnings
+drop table if exists table_26093;
+drop function if exists func_26093_a;
+drop function if exists func_26093_b;
+--enable_warnings
+
+create table table_26093(a int);
+insert into table_26093 values
+(1), (2), (3), (4), (5),
+(6), (7), (8), (9), (10);
+
+delimiter ||;
+
+create function func_26093_a(x int) returns int
+begin
+  set @invoked := @invoked + 1;
+  return x;
+end||
+
+create function func_26093_b(x int, y int) returns int
+begin
+  set @invoked := @invoked + 1;
+  return x;
+end||
+
+delimiter ;||
+
+select avg(a) from table_26093;
+
+select benchmark(100, (select avg(a) from table_26093));
+
+set @invoked := 0;
+select benchmark(100, (select avg(func_26093_a(a)) from table_26093));
+# Returns only 10, since intermediate results are cached.
+select @invoked;
+
+set @invoked := 0;
+select benchmark(100, (select avg(func_26093_b(a, rand())) from table_26093));
+# Returns 1000, due to rand() preventing caching.
+select @invoked;
+
+--error ER_SUBQUERY_NO_1_ROW
+select benchmark(100, (select (a) from table_26093));
+
+drop table table_26093;
+drop function func_26093_a;
+drop function func_26093_b;
+
 --echo End of 5.0 tests