Bug #32124: crash if prepared statements refer to variables in the where clause

                  
The code to get read the value of a system variable was extracting its value 
on PREPARE stage and was substituting the value (as a constant) into the parse tree.
Note that this must be a reversible transformation, i.e. it must be reversed before
each re-execution.
Unfortunately this cannot be reliably done using the current code, because there are
other non-reversible source tree transformations that can interfere with this
reversible transformation.
Fixed by not resolving the value at PREPARE, but at EXECUTE (as the rest of the 
functions operate). Added a cache of the value (so that it's constant throughout
the execution of the query). Note that the cache also caches NULL values.
Updated an obsolete related test suite (variables-big) and the code to test the 
result type of system variables (as per bug 74).

16 files changed, 527 insertions, 251 deletions

diff --git a/mysql-test/extra/rpl_tests/rpl_insert_id.test b/mysql-test/extra/rpl_tests/rpl_insert_id.test
index d8ea267d62b..bd815d9de02 100644
--- a/mysql-test/extra/rpl_tests/rpl_insert_id.test
+++ b/mysql-test/extra/rpl_tests/rpl_insert_id.test
@@ -442,8 +442,6 @@ SELECT f1();
 INSERT INTO t1 VALUES (NULL, f2()), (NULL, LAST_INSERT_ID()),
                       (NULL, LAST_INSERT_ID()), (NULL, f2()), (NULL, f2());
 INSERT INTO t1 VALUES (NULL, f2());
-INSERT INTO t1 VALUES (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID(5)),
-                      (NULL, @@LAST_INSERT_ID);
 # Test replication of substitution "IS NULL" -> "= LAST_INSERT_ID".
 INSERT INTO t1 VALUES (NULL, 0), (NULL, LAST_INSERT_ID());
 UPDATE t1 SET j= -1 WHERE i IS NULL;
diff --git a/mysql-test/r/innodb_data_home_dir_basic.result b/mysql-test/r/innodb_data_home_dir_basic.result
index fb9a0b0bca5..e4bdd79b7c3 100644
--- a/mysql-test/r/innodb_data_home_dir_basic.result
+++ b/mysql-test/r/innodb_data_home_dir_basic.result
@@ -1,7 +1,7 @@
 '#---------------------BS_STVARS_025_01----------------------#'
 SELECT COUNT(@@GLOBAL.innodb_data_home_dir);
 COUNT(@@GLOBAL.innodb_data_home_dir)
-0
+1
 1 Expected
 '#---------------------BS_STVARS_025_02----------------------#'
 SET @@GLOBAL.innodb_data_home_dir=1;
@@ -9,7 +9,7 @@ ERROR HY000: Variable 'innodb_data_home_dir' is a read only variable
 Expected error 'Read only variable'
 SELECT COUNT(@@GLOBAL.innodb_data_home_dir);
 COUNT(@@GLOBAL.innodb_data_home_dir)
-0
+1
 1 Expected
 '#---------------------BS_STVARS_025_03----------------------#'
 SELECT @@GLOBAL.innodb_data_home_dir = VARIABLE_VALUE
@@ -20,7 +20,7 @@ NULL
 1 Expected
 SELECT COUNT(@@GLOBAL.innodb_data_home_dir);
 COUNT(@@GLOBAL.innodb_data_home_dir)
-0
+1
 1 Expected
 SELECT COUNT(VARIABLE_VALUE)
 FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES 
@@ -36,7 +36,7 @@ NULL
 '#---------------------BS_STVARS_025_05----------------------#'
 SELECT COUNT(@@innodb_data_home_dir);
 COUNT(@@innodb_data_home_dir)
-0
+1
 1 Expected
 SELECT COUNT(@@local.innodb_data_home_dir);
 ERROR HY000: Variable 'innodb_data_home_dir' is a GLOBAL variable
@@ -46,7 +46,7 @@ ERROR HY000: Variable 'innodb_data_home_dir' is a GLOBAL variable
 Expected error 'Variable is a GLOBAL variable'
 SELECT COUNT(@@GLOBAL.innodb_data_home_dir);
 COUNT(@@GLOBAL.innodb_data_home_dir)
-0
+1
 1 Expected
 SELECT innodb_data_home_dir = @@SESSION.innodb_data_home_dir;
 ERROR 42S22: Unknown column 'innodb_data_home_dir' in 'field list'
diff --git a/mysql-test/r/innodb_flush_method_basic.result b/mysql-test/r/innodb_flush_method_basic.result
index 836328c5c9b..8c8924cdd86 100644
--- a/mysql-test/r/innodb_flush_method_basic.result
+++ b/mysql-test/r/innodb_flush_method_basic.result
@@ -1,7 +1,7 @@
 '#---------------------BS_STVARS_029_01----------------------#'
 SELECT COUNT(@@GLOBAL.innodb_flush_method);
 COUNT(@@GLOBAL.innodb_flush_method)
-0
+1
 1 Expected
 '#---------------------BS_STVARS_029_02----------------------#'
 SET @@GLOBAL.innodb_flush_method=1;
@@ -9,7 +9,7 @@ ERROR HY000: Variable 'innodb_flush_method' is a read only variable
 Expected error 'Read only variable'
 SELECT COUNT(@@GLOBAL.innodb_flush_method);
 COUNT(@@GLOBAL.innodb_flush_method)
-0
+1
 1 Expected
 '#---------------------BS_STVARS_029_03----------------------#'
 SELECT @@GLOBAL.innodb_flush_method = VARIABLE_VALUE
@@ -20,7 +20,7 @@ NULL
 1 Expected
 SELECT COUNT(@@GLOBAL.innodb_flush_method);
 COUNT(@@GLOBAL.innodb_flush_method)
-0
+1
 1 Expected
 SELECT COUNT(VARIABLE_VALUE)
 FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES 
@@ -36,7 +36,7 @@ NULL
 '#---------------------BS_STVARS_029_05----------------------#'
 SELECT COUNT(@@innodb_flush_method);
 COUNT(@@innodb_flush_method)
-0
+1
 1 Expected
 SELECT COUNT(@@local.innodb_flush_method);
 ERROR HY000: Variable 'innodb_flush_method' is a GLOBAL variable
@@ -46,7 +46,7 @@ ERROR HY000: Variable 'innodb_flush_method' is a GLOBAL variable
 Expected error 'Variable is a GLOBAL variable'
 SELECT COUNT(@@GLOBAL.innodb_flush_method);
 COUNT(@@GLOBAL.innodb_flush_method)
-0
+1
 1 Expected
 SELECT innodb_flush_method = @@SESSION.innodb_flush_method;
 ERROR 42S22: Unknown column 'innodb_flush_method' in 'field list'
diff --git a/mysql-test/r/ps_11bugs.result b/mysql-test/r/ps_11bugs.result
index ebe161f46b3..a298c552806 100644
--- a/mysql-test/r/ps_11bugs.result
+++ b/mysql-test/r/ps_11bugs.result
@@ -162,4 +162,32 @@ a	b
 12	NULL
 drop table t1;
 drop table t2;
+CREATE TABLE t1 (a INT);
+PREPARE stmt FROM 'select 1 from `t1` where `a` = any (select (@@tmpdir))';
+EXECUTE stmt;
+1
+DEALLOCATE PREPARE stmt;
+DROP TABLE t1;
+CREATE TABLE t2 (a INT PRIMARY KEY);
+INSERT INTO t2 VALUES (400000), (400001);
+SET @@sort_buffer_size=400000;
+CREATE FUNCTION p1(i INT) RETURNS INT
+BEGIN
+SET @@sort_buffer_size= i;
+RETURN i + 1;
+END|
+SELECT * FROM t2 WHERE a = @@sort_buffer_size AND p1(@@sort_buffer_size + 1) > a - 1;
+a
+400000
+DROP TABLE t2;
+DROP FUNCTION p1;
+SELECT CONCAT(@@sort_buffer_size);
+CONCAT(@@sort_buffer_size)
+400001
+SELECT LEFT("12345", @@ft_boolean_syntax);
+LEFT("12345", @@ft_boolean_syntax)
+
+Warnings:
+Warning	1292	Truncated incorrect INTEGER value: '+ -><()~*:""&|'
+SET @@sort_buffer_size=DEFAULT;
 End of 5.0 tests.
diff --git a/mysql-test/r/ssl_capath_basic.result b/mysql-test/r/ssl_capath_basic.result
index a2c01aab806..3d161392917 100644
--- a/mysql-test/r/ssl_capath_basic.result
+++ b/mysql-test/r/ssl_capath_basic.result
@@ -1,7 +1,7 @@
 '#---------------------BS_STVARS_046_01----------------------#'
 SELECT COUNT(@@GLOBAL.ssl_capath);
 COUNT(@@GLOBAL.ssl_capath)
-0
+1
 1 Expected
 '#---------------------BS_STVARS_046_02----------------------#'
 SET @@GLOBAL.ssl_capath=1;
@@ -9,7 +9,7 @@ ERROR HY000: Variable 'ssl_capath' is a read only variable
 Expected error 'Read only variable'
 SELECT COUNT(@@GLOBAL.ssl_capath);
 COUNT(@@GLOBAL.ssl_capath)
-0
+1
 1 Expected
 '#---------------------BS_STVARS_046_03----------------------#'
 SELECT @@GLOBAL.ssl_capath = VARIABLE_VALUE
@@ -20,7 +20,7 @@ NULL
 1 Expected
 SELECT COUNT(@@GLOBAL.ssl_capath);
 COUNT(@@GLOBAL.ssl_capath)
-0
+1
 1 Expected
 SELECT COUNT(VARIABLE_VALUE)
 FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES 
@@ -36,7 +36,7 @@ NULL
 '#---------------------BS_STVARS_046_05----------------------#'
 SELECT COUNT(@@ssl_capath);
 COUNT(@@ssl_capath)
-0
+1
 1 Expected
 SELECT COUNT(@@local.ssl_capath);
 ERROR HY000: Variable 'ssl_capath' is a GLOBAL variable
@@ -46,7 +46,7 @@ ERROR HY000: Variable 'ssl_capath' is a GLOBAL variable
 Expected error 'Variable is a GLOBAL variable'
 SELECT COUNT(@@GLOBAL.ssl_capath);
 COUNT(@@GLOBAL.ssl_capath)
-0
+1
 1 Expected
 SELECT ssl_capath = @@SESSION.ssl_capath;
 ERROR 42S22: Unknown column 'ssl_capath' in 'field list'
diff --git a/mysql-test/r/ssl_cipher_basic.result b/mysql-test/r/ssl_cipher_basic.result
index 3c7098e1150..df0fc8b5aad 100644
--- a/mysql-test/r/ssl_cipher_basic.result
+++ b/mysql-test/r/ssl_cipher_basic.result
@@ -1,7 +1,7 @@
 '#---------------------BS_STVARS_048_01----------------------#'
 SELECT COUNT(@@GLOBAL.ssl_cipher);
 COUNT(@@GLOBAL.ssl_cipher)
-0
+1
 1 Expected
 '#---------------------BS_STVARS_048_02----------------------#'
 SET @@GLOBAL.ssl_cipher=1;
@@ -9,7 +9,7 @@ ERROR HY000: Variable 'ssl_cipher' is a read only variable
 Expected error 'Read only variable'
 SELECT COUNT(@@GLOBAL.ssl_cipher);
 COUNT(@@GLOBAL.ssl_cipher)
-0
+1
 1 Expected
 '#---------------------BS_STVARS_048_03----------------------#'
 SELECT @@GLOBAL.ssl_cipher = VARIABLE_VALUE
@@ -20,7 +20,7 @@ NULL
 1 Expected
 SELECT COUNT(@@GLOBAL.ssl_cipher);
 COUNT(@@GLOBAL.ssl_cipher)
-0
+1
 1 Expected
 SELECT COUNT(VARIABLE_VALUE)
 FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES 
@@ -36,7 +36,7 @@ NULL
 '#---------------------BS_STVARS_048_05----------------------#'
 SELECT COUNT(@@ssl_cipher);
 COUNT(@@ssl_cipher)
-0
+1
 1 Expected
 SELECT COUNT(@@local.ssl_cipher);
 ERROR HY000: Variable 'ssl_cipher' is a GLOBAL variable
@@ -46,7 +46,7 @@ ERROR HY000: Variable 'ssl_cipher' is a GLOBAL variable
 Expected error 'Variable is a GLOBAL variable'
 SELECT COUNT(@@GLOBAL.ssl_cipher);
 COUNT(@@GLOBAL.ssl_cipher)
-0
+1
 1 Expected
 SELECT ssl_cipher = @@SESSION.ssl_cipher;
 ERROR 42S22: Unknown column 'ssl_cipher' in 'field list'
diff --git a/mysql-test/r/variables.result b/mysql-test/r/variables.result
index 3f66599751d..aee84c98e4f 100644
--- a/mysql-test/r/variables.result
+++ b/mysql-test/r/variables.result
@@ -157,7 +157,7 @@ explain extended select @@IDENTITY,last_insert_id(), @@identity;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
-Note	1003	select 345 AS `@@IDENTITY`,last_insert_id() AS `last_insert_id()`,345 AS `@@identity`
+Note	1003	select @@IDENTITY AS `@@IDENTITY`,last_insert_id() AS `last_insert_id()`,@@identity AS `@@identity`
 set big_tables=OFF, big_tables=ON, big_tables=0, big_tables=1, big_tables="OFF", big_tables="ON";
 set global concurrent_insert=2;
 show variables like 'concurrent_insert';
diff --git a/mysql-test/suite/rpl/r/rpl_insert_id.result b/mysql-test/suite/rpl/r/rpl_insert_id.result
index 76e405c68de..e171e247b6c 100644
--- a/mysql-test/suite/rpl/r/rpl_insert_id.result
+++ b/mysql-test/suite/rpl/r/rpl_insert_id.result
@@ -398,8 +398,6 @@ f1()
 INSERT INTO t1 VALUES (NULL, f2()), (NULL, LAST_INSERT_ID()),
 (NULL, LAST_INSERT_ID()), (NULL, f2()), (NULL, f2());
 INSERT INTO t1 VALUES (NULL, f2());
-INSERT INTO t1 VALUES (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID(5)),
-(NULL, @@LAST_INSERT_ID);
 INSERT INTO t1 VALUES (NULL, 0), (NULL, LAST_INSERT_ID());
 UPDATE t1 SET j= -1 WHERE i IS NULL;
 INSERT INTO t1 (i) VALUES (NULL);
@@ -422,20 +420,17 @@ i	j
 11	3
 12	3
 13	8
-14	13
-15	5
-16	13
-17	-1
-18	14
-19	0
-20	0
+14	-1
+15	13
+16	0
+17	0
 SELECT * FROM t2 ORDER BY i;
 i
 2
 3
 5
 6
-19
+16
 SELECT * FROM t1;
 i	j
 1	-1
@@ -451,20 +446,17 @@ i	j
 11	3
 12	3
 13	8
-14	13
-15	5
-16	13
-17	-1
-18	14
-19	0
-20	0
+14	-1
+15	13
+16	0
+17	0
 SELECT * FROM t2;
 i
 2
 3
 5
 6
-19
+16
 DROP PROCEDURE p1;
 DROP FUNCTION f1;
 DROP FUNCTION f2;
diff --git a/mysql-test/t/ps_11bugs.test b/mysql-test/t/ps_11bugs.test
index 515bcc03c1a..ccab833e878 100644
--- a/mysql-test/t/ps_11bugs.test
+++ b/mysql-test/t/ps_11bugs.test
@@ -177,4 +177,41 @@ select * from t2;
 drop table t1;
 drop table t2;
 
+#
+# Bug #32124: crash if prepared statements refer to variables in the where
+# clause
+#
+
+CREATE TABLE t1 (a INT);
+PREPARE stmt FROM 'select 1 from `t1` where `a` = any (select (@@tmpdir))';
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+DROP TABLE t1;
+
+CREATE TABLE t2 (a INT PRIMARY KEY);
+INSERT INTO t2 VALUES (400000), (400001);
+
+SET @@sort_buffer_size=400000;
+
+DELIMITER |;
+
+CREATE FUNCTION p1(i INT) RETURNS INT
+BEGIN
+  SET @@sort_buffer_size= i;
+  RETURN i + 1;
+END|
+
+DELIMITER ;|
+
+SELECT * FROM t2 WHERE a = @@sort_buffer_size AND p1(@@sort_buffer_size + 1) > a - 1;
+
+DROP TABLE t2;
+DROP FUNCTION p1;
+
+
+SELECT CONCAT(@@sort_buffer_size);
+SELECT LEFT("12345", @@ft_boolean_syntax);
+
+SET @@sort_buffer_size=DEFAULT;
+
 --echo End of 5.0 tests.
diff --git a/sql/item.cc b/sql/item.cc
index cdb71e86694..5848b7f34a8 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -2380,17 +2380,15 @@ void Item_string::print(String *str, enum_query_type query_type)
 }
 
 
-double Item_string::val_real()
+double 
+double_from_string_with_check (CHARSET_INFO *cs, const char *cptr, char *end)
 {
-  DBUG_ASSERT(fixed == 1);
   int error;
-  char *end, *org_end;
+  char *org_end;
   double tmp;
-  CHARSET_INFO *cs= str_value.charset();
 
-  org_end= (char*) str_value.ptr() + str_value.length();
-  tmp= my_strntod(cs, (char*) str_value.ptr(), str_value.length(), &end,
-                  &error);
+  org_end= end;
+  tmp= my_strntod(cs, (char*) cptr, end - cptr, &end, &error);
   if (error || (end != org_end && !check_if_only_end_space(cs, end, org_end)))
   {
     /*
@@ -2400,26 +2398,28 @@ double Item_string::val_real()
     push_warning_printf(current_thd, MYSQL_ERROR::WARN_LEVEL_WARN,
                         ER_TRUNCATED_WRONG_VALUE,
                         ER(ER_TRUNCATED_WRONG_VALUE), "DOUBLE",
-                        str_value.ptr());
+                        cptr);
   }
   return tmp;
 }
 
 
-/**
-  @todo
-  Give error if we wanted a signed integer and we got an unsigned one
-*/
-longlong Item_string::val_int()
+double Item_string::val_real()
 {
   DBUG_ASSERT(fixed == 1);
+  return double_from_string_with_check (str_value.charset(), str_value.ptr(), 
+                                        (char *) str_value.ptr() + str_value.length());
+}
+
+
+longlong 
+longlong_from_string_with_check (CHARSET_INFO *cs, const char *cptr, char *end)
+{
   int err;
   longlong tmp;
-  char *end= (char*) str_value.ptr()+ str_value.length();
   char *org_end= end;
-  CHARSET_INFO *cs= str_value.charset();
 
-  tmp= (*(cs->cset->strtoll10))(cs, str_value.ptr(), &end, &err);
+  tmp= (*(cs->cset->strtoll10))(cs, cptr, &end, &err);
   /*
     TODO: Give error if we wanted a signed integer and we got an unsigned
     one
@@ -2430,12 +2430,24 @@ longlong Item_string::val_int()
     push_warning_printf(current_thd, MYSQL_ERROR::WARN_LEVEL_WARN,
                         ER_TRUNCATED_WRONG_VALUE,
                         ER(ER_TRUNCATED_WRONG_VALUE), "INTEGER",
-                        str_value.ptr());
+                        cptr);
   }
   return tmp;
 }
 
 
+/**
+  @todo
+  Give error if we wanted a signed integer and we got an unsigned one
+*/
+longlong Item_string::val_int()
+{
+  DBUG_ASSERT(fixed == 1);
+  return longlong_from_string_with_check(str_value.charset(), str_value.ptr(),
+                             (char *) str_value.ptr()+ str_value.length());
+}
+
+
 my_decimal *Item_string::val_decimal(my_decimal *decimal_value)
 {
   return val_decimal_from_string(decimal_value);
diff --git a/sql/item.h b/sql/item.h
index be343e25d3f..9b5062eee88 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -1981,6 +1981,11 @@ private:
 };
 
 
+longlong 
+longlong_from_string_with_check (CHARSET_INFO *cs, const char *cptr, char *end);
+double 
+double_from_string_with_check (CHARSET_INFO *cs, const char *cptr, char *end);
+
 class Item_static_string_func :public Item_string
 {
   const char *func_name;
diff --git a/sql/item_func.cc b/sql/item_func.cc
index d7e6fc1f8f2..4cdf40e18bd 100644
--- a/sql/item_func.cc
+++ b/sql/item_func.cc
@@ -4800,36 +4800,389 @@ Item_func_get_system_var::
 Item_func_get_system_var(sys_var *var_arg, enum_var_type var_type_arg,
                        LEX_STRING *component_arg, const char *name_arg,
                        size_t name_len_arg)
-  :var(var_arg), var_type(var_type_arg), component(*component_arg)
+  :var(var_arg), var_type(var_type_arg), component(*component_arg), 
+   cache_present(0)
 {
   /* set_name() will allocate the name */
   set_name(name_arg, name_len_arg, system_charset_info);
 }
 
 
-bool
-Item_func_get_system_var::fix_fields(THD *thd, Item **ref)
+bool Item_func_get_system_var::is_written_to_binlog()
 {
-  Item *item;
-  DBUG_ENTER("Item_func_get_system_var::fix_fields");
+  return var->is_written_to_binlog(var_type);
+}
 
-  /*
-    Evaluate the system variable and substitute the result (a basic constant)
-    instead of this item. If the variable can not be evaluated,
-    the error is reported in sys_var::item().
-  */
-  if (!(item= var->item(thd, var_type, &component)))
-    DBUG_RETURN(1);                             // Impossible
-  item->set_name(name, 0, system_charset_info); // don't allocate a new name
-  thd->change_item_tree(ref, item);
 
-  DBUG_RETURN(0);
+void Item_func_get_system_var::fix_length_and_dec()
+{
+  maybe_null=0;
+
+  if (var->check_type(var_type))
+  {
+    if (var_type != OPT_DEFAULT)
+    {
+      my_error(ER_INCORRECT_GLOBAL_LOCAL_VAR, MYF(0),
+               var->name, var_type == OPT_GLOBAL ? "SESSION" : "GLOBAL");
+      return;
+    }
+    /* As there was no local variable, return the global value */
+    var_type= OPT_GLOBAL;
+  }
+
+  switch (var->show_type())
+  {
+    case SHOW_LONG:
+    case SHOW_INT:
+    case SHOW_HA_ROWS:
+      unsigned_flag= TRUE;
+      max_length= MY_INT64_NUM_DECIMAL_DIGITS;
+      decimals=0;
+      break;
+    case SHOW_LONGLONG:
+      unsigned_flag= FALSE;
+      max_length= MY_INT64_NUM_DECIMAL_DIGITS;
+      decimals=0;
+      break;
+    case SHOW_CHAR:
+    case SHOW_CHAR_PTR:
+      collation.set(system_charset_info, DERIVATION_SYSCONST);
+      max_length= MAX_BLOB_WIDTH;
+      decimals=NOT_FIXED_DEC;
+      break;
+    case SHOW_MY_BOOL:
+      unsigned_flag= FALSE;
+      max_length= 1;
+      decimals=0;
+      break;
+    case SHOW_DOUBLE:
+      unsigned_flag= FALSE;
+      decimals= 6;
+      max_length= DBL_DIG + 6;
+      break;
+    default:
+      my_error(ER_VAR_CANT_BE_READ, MYF(0), var->name);
+      break;
+  }
+}
+
+
+void Item_func_get_system_var::print(String *str, enum_query_type query_type)
+{
+  str->append(name, name_length);
 }
 
 
-bool Item_func_get_system_var::is_written_to_binlog()
+enum Item_result Item_func_get_system_var::result_type() const
 {
-  return var->is_written_to_binlog(var_type);
+  switch (var->show_type())
+  {
+    case SHOW_MY_BOOL:
+    case SHOW_INT:
+    case SHOW_LONG:
+    case SHOW_LONGLONG:
+    case SHOW_HA_ROWS:
+      return INT_RESULT;
+    case SHOW_CHAR: 
+    case SHOW_CHAR_PTR: 
+      return STRING_RESULT;
+    case SHOW_DOUBLE:
+      return REAL_RESULT;
+    default:
+      my_error(ER_VAR_CANT_BE_READ, MYF(0), var->name);
+      return STRING_RESULT;                   // keep the compiler happy
+  }
+}
+
+
+enum_field_types Item_func_get_system_var::field_type() const
+{
+  switch (var->show_type())
+  {
+    case SHOW_MY_BOOL:
+    case SHOW_INT:
+    case SHOW_LONG:
+    case SHOW_LONGLONG:
+    case SHOW_HA_ROWS:
+      return MYSQL_TYPE_LONGLONG;
+    case SHOW_CHAR: 
+    case SHOW_CHAR_PTR: 
+      return MYSQL_TYPE_VARCHAR;
+    case SHOW_DOUBLE:
+      return MYSQL_TYPE_DOUBLE;
+    default:
+      my_error(ER_VAR_CANT_BE_READ, MYF(0), var->name);
+      return MYSQL_TYPE_VARCHAR;              // keep the compiler happy
+  }
+}
+
+
+#define get_sys_var_safe(type) \
+do { \
+  type value; \
+  pthread_mutex_lock(&LOCK_global_system_variables); \
+  value= *(type*) var->value_ptr(thd, var_type, &component); \
+  pthread_mutex_unlock(&LOCK_global_system_variables); \
+  cache_present |= GET_SYS_VAR_CACHE_LONG; \
+  used_query_id= thd->query_id; \
+  cached_llval= null_value ? 0 : (longlong) value; \
+  cached_null_value= null_value; \
+  return cached_llval; \
+} while (0)
+
+
+longlong Item_func_get_system_var::val_int()
+{
+  THD *thd= current_thd;
+
+  if (thd->query_id == used_query_id)
+  {
+    if (cache_present & GET_SYS_VAR_CACHE_LONG)
+    {
+      null_value= cached_null_value;
+      return cached_llval;
+    } 
+    else if (cache_present & GET_SYS_VAR_CACHE_DOUBLE)
+    {
+      null_value= cached_null_value;
+      cached_llval= (longlong) cached_dval;
+      cache_present|= GET_SYS_VAR_CACHE_LONG;
+      return cached_llval;
+    }
+    else if (cache_present & GET_SYS_VAR_CACHE_STRING)
+    {
+      null_value= cached_null_value;
+      if (!null_value)
+        cached_llval= longlong_from_string_with_check (cached_strval.charset(),
+                                                       cached_strval.c_ptr(),
+                                                       cached_strval.c_ptr() +
+                                                       cached_strval.length());
+      else
+        cached_llval= 0;
+      cache_present|= GET_SYS_VAR_CACHE_LONG;
+      return cached_llval;
+    }
+  }
+
+  switch (var->show_type())
+  {
+    case SHOW_INT:      get_sys_var_safe (uint);
+    case SHOW_LONG:     get_sys_var_safe (ulong);
+    case SHOW_LONGLONG: get_sys_var_safe (longlong);
+    case SHOW_HA_ROWS:  get_sys_var_safe (ha_rows);
+    case SHOW_MY_BOOL:  get_sys_var_safe (my_bool);
+    case SHOW_DOUBLE:
+      {
+        double dval= val_real();
+
+        used_query_id= thd->query_id;
+        cached_llval= (longlong) dval;
+        cache_present|= GET_SYS_VAR_CACHE_LONG;
+        return cached_llval;
+      }
+    case SHOW_CHAR:
+    case SHOW_CHAR_PTR:
+      {
+        String *str_val= val_str(NULL);
+
+        if (str_val && str_val->length())
+          cached_llval= longlong_from_string_with_check (system_charset_info,
+                                                          str_val->c_ptr(), 
+                                                          str_val->c_ptr() + 
+                                                          str_val->length());
+        else
+        {
+          null_value= TRUE;
+          cached_llval= 0;
+        }
+
+        cache_present|= GET_SYS_VAR_CACHE_LONG;
+        return cached_llval;
+      }
+
+    default:            
+      my_error(ER_VAR_CANT_BE_READ, MYF(0), var->name); 
+      return 0;                               // keep the compiler happy
+  }
+}
+
+
+String* Item_func_get_system_var::val_str(String* str)
+{
+  THD *thd= current_thd;
+
+  if (thd->query_id == used_query_id)
+  {
+    if (cache_present & GET_SYS_VAR_CACHE_STRING)
+    {
+      null_value= cached_null_value;
+      return null_value ? NULL : &cached_strval;
+    }
+    else if (cache_present & GET_SYS_VAR_CACHE_LONG)
+    {
+      null_value= cached_null_value;
+      if (!null_value)
+        cached_strval.set (cached_llval, collation.collation);
+      cache_present|= GET_SYS_VAR_CACHE_STRING;
+      return null_value ? NULL : &cached_strval;
+    }
+    else if (cache_present & GET_SYS_VAR_CACHE_DOUBLE)
+    {
+      null_value= cached_null_value;
+      if (!null_value)
+        cached_strval.set_real (cached_dval, decimals, collation.collation);
+      cache_present|= GET_SYS_VAR_CACHE_STRING;
+      return null_value ? NULL : &cached_strval;
+    }
+  }
+
+  str= &cached_strval;
+  switch (var->show_type())
+  {
+    case SHOW_CHAR:
+    case SHOW_CHAR_PTR:
+    {
+      pthread_mutex_lock(&LOCK_global_system_variables);
+      char *cptr= var->show_type() == SHOW_CHAR_PTR ? 
+        *(char**) var->value_ptr(thd, var_type, &component) :
+        (char*) var->value_ptr(thd, var_type, &component);
+      if (cptr)
+      {
+        if (str->copy(cptr, strlen(cptr), collation.collation))
+        {
+          null_value= TRUE;
+          str= NULL;
+        }
+      }
+      else
+      {
+        null_value= TRUE;
+        str= NULL;
+      }
+      pthread_mutex_unlock(&LOCK_global_system_variables);
+      break;
+    }
+
+    case SHOW_INT:
+    case SHOW_LONG:
+    case SHOW_LONGLONG:
+    case SHOW_HA_ROWS:
+    case SHOW_MY_BOOL:
+      str->set (val_int(), collation.collation);
+      break;
+    case SHOW_DOUBLE:
+      str->set_real (val_real(), decimals, collation.collation);
+      break;
+
+    default:
+      my_error(ER_VAR_CANT_BE_READ, MYF(0), var->name);
+      str= NULL;
+      break;
+  }
+
+  cache_present|= GET_SYS_VAR_CACHE_STRING;
+  used_query_id= thd->query_id;
+  cached_null_value= null_value;
+  return str;
+}
+
+
+double Item_func_get_system_var::val_real()
+{
+  THD *thd= current_thd;
+
+  if (thd->query_id == used_query_id)
+  {
+    if (cache_present & GET_SYS_VAR_CACHE_DOUBLE)
+    {
+      null_value= cached_null_value;
+      return cached_dval;
+    }
+    else if (cache_present & GET_SYS_VAR_CACHE_LONG)
+    {
+      null_value= cached_null_value;
+      cached_dval= (double)cached_llval;
+      cache_present|= GET_SYS_VAR_CACHE_DOUBLE;
+      return cached_dval;
+    }
+    else if (cache_present & GET_SYS_VAR_CACHE_STRING)
+    {
+      null_value= cached_null_value;
+      if (!null_value)
+        cached_dval= double_from_string_with_check (cached_strval.charset(),
+                                                    cached_strval.c_ptr(),
+                                                    cached_strval.c_ptr() +
+                                                    cached_strval.length());
+      else
+        cached_dval= 0;
+      cache_present|= GET_SYS_VAR_CACHE_DOUBLE;
+      return cached_dval;
+    }
+  }
+
+  switch (var->show_type())
+  {
+    case SHOW_DOUBLE:
+      pthread_mutex_lock(&LOCK_global_system_variables);
+      cached_dval= *(double*) var->value_ptr(thd, var_type, &component);
+      pthread_mutex_unlock(&LOCK_global_system_variables);
+      used_query_id= thd->query_id;
+      cached_null_value= null_value;
+      if (null_value)
+        cached_dval= 0;
+      cache_present|= GET_SYS_VAR_CACHE_DOUBLE;
+      return cached_dval;
+    case SHOW_CHAR:
+    case SHOW_CHAR_PTR:
+      {
+        char *cptr;
+
+        pthread_mutex_lock(&LOCK_global_system_variables);
+        cptr= var->show_type() == SHOW_CHAR ? 
+          (char*) var->value_ptr(thd, var_type, &component) :
+          *(char**) var->value_ptr(thd, var_type, &component);
+        if (cptr)
+          cached_dval= double_from_string_with_check (system_charset_info, 
+                                                cptr, cptr + strlen (cptr));
+        else
+        {
+          null_value= TRUE;
+          cached_dval= 0;
+        }
+        pthread_mutex_unlock(&LOCK_global_system_variables);
+        used_query_id= thd->query_id;
+        cached_null_value= null_value;
+        cache_present|= GET_SYS_VAR_CACHE_DOUBLE;
+        return cached_dval;
+      }
+    case SHOW_INT:
+    case SHOW_LONG:
+    case SHOW_LONGLONG:
+    case SHOW_HA_ROWS:
+    case SHOW_MY_BOOL:
+        cached_dval= (double) val_int();
+        cache_present|= GET_SYS_VAR_CACHE_DOUBLE;
+        used_query_id= thd->query_id;
+        cached_null_value= null_value;
+        return cached_dval;
+    default:
+      my_error(ER_VAR_CANT_BE_READ, MYF(0), var->name);
+      return 0;
+  }
+}
+
+
+bool Item_func_get_system_var::eq(const Item *item, bool binary_cmp) const
+{
+  /* Assume we don't have rtti */
+  if (this == item)
+    return 1;					// Same item is same.
+  /* Check if other type is also a get_user_var() object */
+  if (item->type() != FUNC_ITEM ||
+      ((Item_func*) item)->functype() != functype())
+    return 0;
+  Item_func_get_system_var *other=(Item_func_get_system_var*) item;
+  return (var == other->var && var_type == other->var_type);
 }
 
 
diff --git a/sql/item_func.h b/sql/item_func.h
index d84abdb6e56..778ffd5757f 100644
--- a/sql/item_func.h
+++ b/sql/item_func.h
@@ -55,7 +55,7 @@ public:
                   NOW_FUNC, TRIG_COND_FUNC,
                   SUSERVAR_FUNC, GUSERVAR_FUNC, COLLATE_FUNC,
                   EXTRACT_FUNC, CHAR_TYPECAST_FUNC, FUNC_SP, UDF_FUNC,
-                  NEG_FUNC };
+                  NEG_FUNC, GSYSVAR_FUNC };
   enum optimize_type { OPTIMIZE_NONE,OPTIMIZE_KEY,OPTIMIZE_OP, OPTIMIZE_NULL,
                        OPTIMIZE_EQUAL };
   enum Type type() const { return FUNC_ITEM; }
@@ -1426,24 +1426,36 @@ public:
 
 /* A system variable */
 
+#define GET_SYS_VAR_CACHE_LONG     1
+#define GET_SYS_VAR_CACHE_DOUBLE   2
+#define GET_SYS_VAR_CACHE_STRING   4
+
 class Item_func_get_system_var :public Item_func
 {
   sys_var *var;
   enum_var_type var_type;
   LEX_STRING component;
+  longlong cached_llval;
+  double cached_dval;
+  String cached_strval;
+  my_bool cached_null_value;
+  query_id_t used_query_id;
+  uchar cache_present;
+
 public:
   Item_func_get_system_var(sys_var *var_arg, enum_var_type var_type_arg,
                            LEX_STRING *component_arg, const char *name_arg,
                            size_t name_len_arg);
-  bool fix_fields(THD *thd, Item **ref);
-  /*
-    Stubs for pure virtual methods. Should never be called: this
-    item is always substituted with a constant in fix_fields().
-  */
-  double val_real()         { DBUG_ASSERT(0); return 0.0; }
-  longlong val_int()        { DBUG_ASSERT(0); return 0; }
-  String* val_str(String*)  { DBUG_ASSERT(0); return 0; }
-  void fix_length_and_dec() { DBUG_ASSERT(0); }
+  enum Functype functype() const { return GSYSVAR_FUNC; }
+  void fix_length_and_dec();
+  void print(String *str, enum_query_type query_type);
+  bool const_item() const { return true; }
+  table_map used_tables() const { return 0; }
+  enum Item_result result_type() const;
+  enum_field_types field_type() const;
+  double val_real();
+  longlong val_int();
+  String* val_str(String*);
   /* TODO: fix to support views */
   const char *func_name() const { return "get_system_var"; }
   /**
@@ -1455,6 +1467,7 @@ public:
     @return true if the variable is written to the binlog, false otherwise.
   */
   bool is_written_to_binlog();
+  bool eq(const Item *item, bool binary_cmp) const;
 };
 
 
diff --git a/sql/set_var.cc b/sql/set_var.cc
index 4259c3a6aaf..f1b9f5e4107 100644
--- a/sql/set_var.cc
+++ b/sql/set_var.cc
@@ -1734,119 +1734,6 @@ err:
 }
 
 
-/**
-  Return an Item for a variable.
-
-  Used with @@[global.]variable_name.
-
-  If type is not given, return local value if exists, else global.
-*/
-
-Item *sys_var::item(THD *thd, enum_var_type var_type, LEX_STRING *base)
-{
-  if (check_type(var_type))
-  {
-    if (var_type != OPT_DEFAULT)
-    {
-      my_error(ER_INCORRECT_GLOBAL_LOCAL_VAR, MYF(0),
-               name, var_type == OPT_GLOBAL ? "SESSION" : "GLOBAL");
-      return 0;
-    }
-    /* As there was no local variable, return the global value */
-    var_type= OPT_GLOBAL;
-  }
-  switch (show_type()) {
-  case SHOW_INT:
-  {
-    uint value;
-    pthread_mutex_lock(&LOCK_global_system_variables);
-    value= *(uint*) value_ptr(thd, var_type, base);
-    pthread_mutex_unlock(&LOCK_global_system_variables);
-    return new Item_uint((ulonglong) value);
-  }
-  case SHOW_LONG:
-  {
-    ulong value;
-    pthread_mutex_lock(&LOCK_global_system_variables);
-    value= *(ulong*) value_ptr(thd, var_type, base);
-    pthread_mutex_unlock(&LOCK_global_system_variables);
-    return new Item_uint((ulonglong) value);
-  }
-  case SHOW_LONGLONG:
-  {
-    longlong value;
-    pthread_mutex_lock(&LOCK_global_system_variables);
-    value= *(longlong*) value_ptr(thd, var_type, base);
-    pthread_mutex_unlock(&LOCK_global_system_variables);
-    return new Item_int(value);
-  }
-  case SHOW_DOUBLE:
-  {
-    double value;
-    pthread_mutex_lock(&LOCK_global_system_variables);
-    value= *(double*) value_ptr(thd, var_type, base);
-    pthread_mutex_unlock(&LOCK_global_system_variables);
-    /* 6, as this is for now only used with microseconds */
-    return new Item_float(value, 6);
-  }
-  case SHOW_HA_ROWS:
-  {
-    ha_rows value;
-    pthread_mutex_lock(&LOCK_global_system_variables);
-    value= *(ha_rows*) value_ptr(thd, var_type, base);
-    pthread_mutex_unlock(&LOCK_global_system_variables);
-    return new Item_int((ulonglong) value);
-  }
-  case SHOW_MY_BOOL:
-  {
-    int32 value;
-    pthread_mutex_lock(&LOCK_global_system_variables);
-    value= *(my_bool*) value_ptr(thd, var_type, base);
-    pthread_mutex_unlock(&LOCK_global_system_variables);
-    return new Item_int(value,1);
-  }
-  case SHOW_CHAR_PTR:
-  {
-    Item *tmp;
-    pthread_mutex_lock(&LOCK_global_system_variables);
-    char *str= *(char**) value_ptr(thd, var_type, base);
-    if (str)
-    {
-      uint length= strlen(str);
-      tmp= new Item_string(thd->strmake(str, length), length,
-                           system_charset_info, DERIVATION_SYSCONST);
-    }
-    else
-    {
-      tmp= new Item_null();
-      tmp->collation.set(system_charset_info, DERIVATION_SYSCONST);
-    }
-    pthread_mutex_unlock(&LOCK_global_system_variables);
-    return tmp;
-  }
-  case SHOW_CHAR:
-  {
-    Item *tmp;
-    pthread_mutex_lock(&LOCK_global_system_variables);
-    char *str= (char*) value_ptr(thd, var_type, base);
-    if (str)
-      tmp= new Item_string(str, strlen(str),
-                           system_charset_info, DERIVATION_SYSCONST);
-    else
-    {
-      tmp= new Item_null();
-      tmp->collation.set(system_charset_info, DERIVATION_SYSCONST);
-    }
-    pthread_mutex_unlock(&LOCK_global_system_variables);
-    return tmp;
-  }
-  default:
-    my_error(ER_VAR_CANT_BE_READ, MYF(0), name);
-  }
-  return 0;
-}
-
-
 bool sys_var_thd_enum::update(THD *thd, set_var *var)
 {
   if (var->type == OPT_GLOBAL)
diff --git a/sql/set_var.h b/sql/set_var.h
index 8ae97c6502d..9681c955a98 100644
--- a/sql/set_var.h
+++ b/sql/set_var.h
@@ -105,7 +105,6 @@ public:
   { return type != INT_RESULT; }		/* Assume INT */
   virtual bool check_default(enum_var_type type)
   { return option_limits == 0; }
-  Item *item(THD *thd, enum_var_type type, LEX_STRING *base);
   virtual bool is_struct() { return 0; }
   virtual bool is_readonly() const { return 0; }
   virtual sys_var_pluginvar *cast_pluginvar() { return 0; }
diff --git a/tests/mysql_client_test.c b/tests/mysql_client_test.c
index 6da7b3e8072..a50b009303a 100644
--- a/tests/mysql_client_test.c
+++ b/tests/mysql_client_test.c
@@ -7201,9 +7201,6 @@ static void test_field_misc()
 {
   MYSQL_STMT  *stmt;
   MYSQL_RES   *result;
-  MYSQL_BIND  my_bind[1];
-  char        table_type[NAME_LEN];
-  ulong       type_length;
   int         rc;
 
   myheader("test_field_misc");
@@ -7246,53 +7243,6 @@ static void test_field_misc()
   mysql_free_result(result);
   mysql_stmt_close(stmt);
 
-  stmt= mysql_simple_prepare(mysql, "SELECT @@table_type");
-  check_stmt(stmt);
-
-  rc= mysql_stmt_execute(stmt);
-  check_execute(stmt, rc);
-
-  bzero((char*) my_bind, sizeof(my_bind));
-  my_bind[0].buffer_type= MYSQL_TYPE_STRING;
-  my_bind[0].buffer= table_type;
-  my_bind[0].length= &type_length;
-  my_bind[0].buffer_length= NAME_LEN;
-
-  rc= mysql_stmt_bind_result(stmt, my_bind);
-  check_execute(stmt, rc);
-
-  rc= mysql_stmt_fetch(stmt);
-  check_execute(stmt, rc);
-  if (!opt_silent)
-    fprintf(stdout, "\n default table type: %s(%ld)", table_type, type_length);
-
-  rc= mysql_stmt_fetch(stmt);
-  DIE_UNLESS(rc == MYSQL_NO_DATA);
-
-  mysql_stmt_close(stmt);
-
-  stmt= mysql_simple_prepare(mysql, "SELECT @@table_type");
-  check_stmt(stmt);
-
-  result= mysql_stmt_result_metadata(stmt);
-  mytest(result);
-  DIE_UNLESS(mysql_stmt_field_count(stmt) == mysql_num_fields(result));
-
-  rc= mysql_stmt_execute(stmt);
-  check_execute(stmt, rc);
-
-  DIE_UNLESS(1 == my_process_stmt_result(stmt));
-
-  verify_prepare_field(result, 0,
-                       "@@table_type", "",   /* field and its org name */
-                       mysql_get_server_version(mysql) <= 50000 ?
-                       MYSQL_TYPE_STRING : MYSQL_TYPE_VAR_STRING,
-                       "", "",              /* table and its org name */
-                       "", type_length, 0);   /* db name, length */
-
-  mysql_free_result(result);
-  mysql_stmt_close(stmt);
-
   stmt= mysql_simple_prepare(mysql, "SELECT @@max_error_count");
   check_stmt(stmt);
 
@@ -7309,7 +7259,8 @@ static void test_field_misc()
                        "@@max_error_count", "",   /* field and its org name */
                        MYSQL_TYPE_LONGLONG, /* field type */
                        "", "",              /* table and its org name */
-                       "", 10, 0);            /* db name, length */
+                       /* db name, length */
+                       "", MY_INT64_NUM_DECIMAL_DIGITS , 0);
 
   mysql_free_result(result);
   mysql_stmt_close(stmt);
@@ -7329,7 +7280,8 @@ static void test_field_misc()
                        "@@max_allowed_packet", "", /* field and its org name */
                        MYSQL_TYPE_LONGLONG, /* field type */
                        "", "",              /* table and its org name */
-                       "", 10, 0);          /* db name, length */
+                       /* db name, length */
+                       "", MY_INT64_NUM_DECIMAL_DIGITS, 0);
 
   mysql_free_result(result);
   mysql_stmt_close(stmt);