From 930db43eb08bd167f018ce0aea7b3e044829824e Mon Sep 17 00:00:00 2001
From: "bell@sanja.is.com.ua" <>
Date: Thu, 5 Feb 2004 12:32:22 +0200
Subject: correct processing of rand() in subqueries with static tables
 (BUG#2645)

---
 mysql-test/r/subselect.result | 8 ++++++++
 mysql-test/t/subselect.test   | 8 ++++++++
 sql/item_func.cc              | 1 +
 sql/item_func.h               | 1 -
 sql/sql_select.cc             | 8 ++++++--
 5 files changed, 23 insertions(+), 3 deletions(-)

diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index c5050cf5f27..ee4b0d1f431 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -1625,3 +1625,11 @@ PIPPO
 1
 NULL
 DROP TABLE t1, t2;
+create table t1 (a int);
+insert into t1 values (1);
+explain select benchmark(1000, (select a from t1 where a=sha(rand())));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNCACHEABLE SUBQUERY	t1	system	NULL	NULL	NULL	NULL	1	
+drop table t1;
+
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index c4392186055..599c229b406 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -1075,3 +1075,11 @@ s.id_cns = cns.id ORDER BY s.anno_dep DESC LIMIT 1) AS PIPPO FROM
 t2 AS cns;
 
 DROP TABLE t1, t2;
+
+#
+# Static tables & rund() in subqueries
+#
+create table t1 (a int);
+insert into t1 values (1);
+explain select benchmark(1000, (select a from t1 where a=sha(rand())));
+drop table t1;
diff --git a/sql/item_func.cc b/sql/item_func.cc
index f90fcd5149e..52b1bec75e4 100644
--- a/sql/item_func.cc
+++ b/sql/item_func.cc
@@ -952,6 +952,7 @@ void Item_func_rand::fix_length_and_dec()
 {
   decimals=NOT_FIXED_DEC; 
   max_length=float_length(decimals);
+  used_tables_cache|= RAND_TABLE_BIT;
   if (arg_count)
   {					// Only use argument once in query
     uint32 tmp= (uint32) (args[0]->val_int());
diff --git a/sql/item_func.h b/sql/item_func.h
index be20a9b4fc7..8859311f64b 100644
--- a/sql/item_func.h
+++ b/sql/item_func.h
@@ -504,7 +504,6 @@ public:
   double val();
   const char *func_name() const { return "rand"; }
   bool const_item() const { return 0; }
-  table_map used_tables() const { return RAND_TABLE_BIT; }
   void fix_length_and_dec();
 };
 
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index aeaad4559e2..b6919e35d58 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -586,7 +586,9 @@ JOIN::optimize()
     DBUG_RETURN(1);				// error == -1
   }
   if (const_table_map != found_const_table_map &&
-      !(select_options & SELECT_DESCRIBE))
+      !(select_options & SELECT_DESCRIBE) &&
+      !((conds->used_tables() & RAND_TABLE_BIT) &&
+	select_lex->master_unit() != &thd->lex->unit))// not upper level SELECT
   {
     zero_result_cause= "no matching row in const table";
     DBUG_PRINT("error",("Error: %s", zero_result_cause));
@@ -3387,7 +3389,9 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond)
     table_map used_tables;
     if (join->tables > 1)
       cond->update_used_tables();		// Tablenr may have changed
-    if (join->const_tables == join->tables)
+    if (join->const_tables == join->tables &&
+	join->thd->lex->current_select->master_unit() ==
+	&join->thd->lex->unit)		// not upper level SELECT
       join->const_table_map|=RAND_TABLE_BIT;
     {						// Check const tables
       COND *const_cond=
-- 
cgit v1.2.1