From: kgeorge Date: January 16 2007 11:44am Subject: bk commit into 5.1 tree (gkodinov:1.2392) BUG#6172 List-Archive: http://lists.mysql.com/commits/18177 X-Bug: 6172 Message-Id: <20070116114426.8354EBCF9E0@macbook.gmz> Below is the list of changes that have just been committed into a local 5.1 repository of kgeorge. When kgeorge 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-01-16 13:44:06+02:00, gkodinov@stripped +4 -0 Bug #6172: RAND(a) should only accept constant values as arguments RAND() must accept scalar expressions regardless of their kind. That includes both constant expressions and expressions that depend on column values. When the expression is constant the random seed can be initialized at compile time. However when the expression is not constant the random seed must be initialized at each invocation (while it still can be allocated at compile time). Implemented the above rules by extending Item_func_rand::val_real() to initialize the random seed at the correct place. mysql-test/r/func_math.result@stripped, 2007-01-16 13:43:58+02:00, gkodinov@stripped +23 -5 Bug #6172: RAND(a) should only accept constant values as arguments - extened the test case mysql-test/t/func_math.test@stripped, 2007-01-16 13:43:59+02:00, gkodinov@stripped +14 -5 Bug #6172: RAND(a) should only accept constant values as arguments - extened the test case sql/item_func.cc@stripped, 2007-01-16 13:44:00+02:00, gkodinov@stripped +24 -19 Bug #6172: RAND(a) should only accept constant values as arguments - allow specifying non-const expressions as RAND() arguments sql/item_func.h@stripped, 2007-01-16 13:44:00+02:00, gkodinov@stripped +2 -0 Bug #6172: RAND(a) should only accept constant values as arguments - allow specifying non-const expressions as RAND() arguments # 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: gkodinov # Host: macbook.gmz # Root: /Users/kgeorge/mysql/work/B6172-5.1-opt --- 1.345/sql/item_func.cc 2007-01-10 12:06:17 +02:00 +++ 1.346/sql/item_func.cc 2007-01-16 13:44:00 +02:00 @@ -2042,6 +2042,25 @@ my_decimal *Item_func_round::decimal_op( } +void Item_func_rand::seed_random(Item *arg) +{ + /* + PARAM_ITEM is returned if we're in statement prepare and consequently + no placeholder value is set yet. + */ + if (arg->type() != PARAM_ITEM) + { + /* + TODO: do not do reinit 'rand' for every execute of PS/SP if + args[0] is a constant. + */ + uint32 tmp= (uint32) arg->val_int(); + randominit(rand, (uint32) (tmp*0x10001L+55555555L), + (uint32) (tmp*0x10000001L)); + } +} + + bool Item_func_rand::fix_fields(THD *thd,Item **ref) { if (Item_real_func::fix_fields(thd, ref)) @@ -2049,11 +2068,6 @@ bool Item_func_rand::fix_fields(THD *thd used_tables_cache|= RAND_TABLE_BIT; if (arg_count) { // Only use argument once in query - if (!args[0]->const_during_execution()) - { - my_error(ER_WRONG_ARGUMENTS, MYF(0), "RAND"); - return TRUE; - } /* Allocate rand structure once: we must use thd->stmt_arena to create rand in proper mem_root if it's a prepared statement or @@ -2065,20 +2079,9 @@ bool Item_func_rand::fix_fields(THD *thd if (!rand && !(rand= (struct rand_struct*) thd->stmt_arena->alloc(sizeof(*rand)))) return TRUE; - /* - PARAM_ITEM is returned if we're in statement prepare and consequently - no placeholder value is set yet. - */ - if (args[0]->type() != PARAM_ITEM) - { - /* - TODO: do not do reinit 'rand' for every execute of PS/SP if - args[0] is a constant. - */ - uint32 tmp= (uint32) args[0]->val_int(); - randominit(rand, (uint32) (tmp*0x10001L+55555555L), - (uint32) (tmp*0x10000001L)); - } + + if (args[0]->const_during_execution()) + seed_random (args[0]); } else { @@ -2108,6 +2111,8 @@ void Item_func_rand::update_used_tables( double Item_func_rand::val_real() { DBUG_ASSERT(fixed == 1); + if (arg_count && !args[0]->const_during_execution()) + seed_random (args[0]); return my_rnd(rand); } --- 1.160/sql/item_func.h 2007-01-10 12:06:17 +02:00 +++ 1.161/sql/item_func.h 2007-01-16 13:44:00 +02:00 @@ -679,6 +679,8 @@ public: bool const_item() const { return 0; } void update_used_tables(); bool fix_fields(THD *thd, Item **ref); +private: + void seed_random (Item * val); }; --- 1.39/mysql-test/r/func_math.result 2006-11-02 20:01:48 +02:00 +++ 1.40/mysql-test/r/func_math.result 2007-01-16 13:43:58 +02:00 @@ -177,11 +177,29 @@ drop table t1; select abs(-2) * -2; abs(-2) * -2 -4 -create table t1 (i int); -insert into t1 values (1); -select rand(i) from t1; -ERROR HY000: Incorrect arguments to RAND -drop table t1; +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2); +SELECT CAST(RAND(2) * 1000 AS UNSIGNED), CAST(RAND(a) * 1000 AS UNSIGNED) +FROM t1; +CAST(RAND(2) * 1000 AS UNSIGNED) CAST(RAND(a) * 1000 AS UNSIGNED) +656 405 +122 656 +SELECT CAST(RAND(2) * 1000 AS UNSIGNED), CAST(RAND(a) * 1000 AS UNSIGNED) +FROM t1 WHERE a = 1; +CAST(RAND(2) * 1000 AS UNSIGNED) CAST(RAND(a) * 1000 AS UNSIGNED) +656 405 +INSERT INTO t1 VALUES (3); +SELECT CAST(RAND(2) * 1000 AS UNSIGNED), CAST(RAND(a) * 1000 AS UNSIGNED) +FROM t1; +CAST(RAND(2) * 1000 AS UNSIGNED) CAST(RAND(a) * 1000 AS UNSIGNED) +656 405 +122 656 +645 906 +SELECT CAST(RAND(2) * 1000 AS UNSIGNED), CAST(RAND(a) * 1000 AS UNSIGNED) +FROM t1 WHERE a = 1; +CAST(RAND(2) * 1000 AS UNSIGNED) CAST(RAND(a) * 1000 AS UNSIGNED) +656 405 +DROP TABLE t1; create table t1 (a varchar(90), ts datetime not null, index (a)) engine=innodb default charset=utf8; insert into t1 values ('http://www.foo.com/', now()); select a from t1 where a='http://www.foo.com/' order by abs(timediff(ts, 0)); --- 1.28/mysql-test/t/func_math.test 2006-11-02 20:01:49 +02:00 +++ 1.29/mysql-test/t/func_math.test 2007-01-16 13:43:59 +02:00 @@ -115,11 +115,20 @@ select abs(-2) * -2; # # Bug #6172 RAND(a) should only accept constant values as arguments # -create table t1 (i int); -insert into t1 values (1); ---error 1210 -select rand(i) from t1; -drop table t1; +CREATE TABLE t1 (a INT); + +INSERT INTO t1 VALUES (1),(2); +SELECT CAST(RAND(2) * 1000 AS UNSIGNED), CAST(RAND(a) * 1000 AS UNSIGNED) + FROM t1; +SELECT CAST(RAND(2) * 1000 AS UNSIGNED), CAST(RAND(a) * 1000 AS UNSIGNED) + FROM t1 WHERE a = 1; +INSERT INTO t1 VALUES (3); +SELECT CAST(RAND(2) * 1000 AS UNSIGNED), CAST(RAND(a) * 1000 AS UNSIGNED) + FROM t1; +SELECT CAST(RAND(2) * 1000 AS UNSIGNED), CAST(RAND(a) * 1000 AS UNSIGNED) + FROM t1 WHERE a = 1; + +DROP TABLE t1; # # Bug #14009: use of abs() on null value causes problems with filesort