[Commits] Rev 3522: Added function last_value() which returns the last value but evalutes all arguments as a side effect. in lp:maria/5.5

Michael Widenius michael.widenius at gmail.com
Wed Sep 5 18:23:52 EEST 2012


At lp:maria/5.5

------------------------------------------------------------
revno: 3522
revision-id: monty at askmonty.org-20120905152351-541yqyb4wzz8xmd7
parent: sergii at pisem.net-20120902170917-27bctokwledvq8p8
committer: Michael Widenius <monty at askmonty.org>
branch nick: maria-5.5
timestamp: Wed 2012-09-05 18:23:51 +0300
message:
  Added function last_value() which returns the last value but evalutes all arguments as a side effect.
  Original patch by Eric Herman
-------------- next part --------------
=== modified file 'client/mysql.cc'
--- a/client/mysql.cc	2012-08-09 15:22:00 +0000
+++ b/client/mysql.cc	2012-09-05 15:23:51 +0000
@@ -897,6 +897,7 @@ static COMMANDS commands[] = {
   { "LAST_INSERT_ID", 0, 0, 0, ""},
   { "ISSIMPLE", 0, 0, 0, ""},
   { "LAST_DAY", 0, 0, 0, ""},
+  { "LAST_VALUE", 0, 0, 0, ""},
   { "LCASE", 0, 0, 0, ""},
   { "LEAST", 0, 0, 0, ""},
   { "LENGTH", 0, 0, 0, ""},

=== added file 'mysql-test/r/last_value.result'
--- a/mysql-test/r/last_value.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/r/last_value.result	2012-09-05 15:23:51 +0000
@@ -0,0 +1,73 @@
+drop table if exists t1;
+drop database if exists mysqltest;
+CREATE TABLE t1 (a INT, b INT, c INT, d INT);
+INSERT INTO t1 VALUES (1,3,0,NULL),(2,2,0,NULL),(3,4,0,NULL),(4,2,0,NULL);
+SELECT * FROM t1;
+a       b       c       d
+1       3       0       NULL
+2       2       0       NULL
+3       4       0       NULL
+4       2       0       NULL
+UPDATE t1 SET c=LAST_VALUE(@last_a:=a, at last_b:=b, at last_c:=c,1), d=4211 WHERE c=0 ORDER BY b DESC LIMIT 1;
+SELECT @last_a, @last_b, @last_c;
+ at last_a @last_b @last_c
+3       4       0
+SELECT * FROM t1;
+a       b       c       d
+1       3       0       NULL
+2       2       0       NULL
+3       4       1       4211
+4       2       0       NULL
+DROP TABLE t1;
+SELECT LAST_VALUE(@last_a:=1, at last_b:=1);
+Catalog Database        Table   Table_alias     Column  Column_alias    Type    Length  Max length      Is_null Flags   Decimals        Charsetnr
+def                                     LAST_VALUE(@last_a:=1, at last_b:=1)       8       1       1       N       32897   0       63
+LAST_VALUE(@last_a:=1, at last_b:=1)
+1
+select @last_b;
+Catalog Database        Table   Table_alias     Column  Column_alias    Type    Length  Max length      Is_null Flags   Decimals        Charsetnr
+def                                     @last_b 8       20      1       Y       32896   0       63
+ at last_b
+1
+SELECT LAST_VALUE(@last_a:=1, at last_b:=1.0);
+Catalog Database        Table   Table_alias     Column  Column_alias    Type    Length  Max length      Is_null Flags   Decimals        Charsetnr
+def                                     LAST_VALUE(@last_a:=1, at last_b:=1.0)     246     4       3       N       32897   1       63
+LAST_VALUE(@last_a:=1, at last_b:=1.0)
+1.0
+select @last_b;
+Catalog Database        Table   Table_alias     Column  Column_alias    Type    Length  Max length      Is_null Flags   Decimals        Charsetnr
+def                                     @last_b 246     83      3       Y       32896   30      63
+ at last_b
+1.0
+SELECT LAST_VALUE(@last_a:=1, at last_b:="hello");
+Catalog Database        Table   Table_alias     Column  Column_alias    Type    Length  Max length      Is_null Flags   Decimals        Charsetnr
+def                                     LAST_VALUE(@last_a:=1, at last_b:="hello") 253     5       5       N       1       31      8
+LAST_VALUE(@last_a:=1, at last_b:="hello")
+hello
+select @last_b;
+Catalog Database        Table   Table_alias     Column  Column_alias    Type    Length  Max length      Is_null Flags   Decimals        Charsetnr
+def                                     @last_b 250     16777215        5       Y       0       31      8
+ at last_b
+hello
+SELECT date(LAST_VALUE(@last_a:=1, at last_b:="2001-02-03"));
+Catalog Database        Table   Table_alias     Column  Column_alias    Type    Length  Max length      Is_null Flags   Decimals        Charsetnr
+def                                     date(LAST_VALUE(@last_a:=1, at last_b:="2001-02-03"))      10      10      10      Y       128     0       63
+date(LAST_VALUE(@last_a:=1, at last_b:="2001-02-03"))
+2001-02-03
+select @last_b;
+Catalog Database        Table   Table_alias     Column  Column_alias    Type    Length  Max length      Is_null Flags   Decimals        Charsetnr
+def                                     @last_b 250     16777215        10      Y       0       31      8
+ at last_b
+2001-02-03
+SELECT LAST_VALUE(@last_a:=1, at last_b:="2001-02-03",NULL);
+Catalog Database        Table   Table_alias     Column  Column_alias    Type    Length  Max length      Is_null Flags   Decimals        Charsetnr
+def                                     LAST_VALUE(@last_a:=1, at last_b:="2001-02-03",NULL)       6       0       0       Y       32896   0       63
+LAST_VALUE(@last_a:=1, at last_b:="2001-02-03",NULL)
+NULL
+select @last_b;
+Catalog Database        Table   Table_alias     Column  Column_alias    Type    Length  Max length      Is_null Flags   Decimals        Charsetnr
+def                                     @last_b 250     16777215        10      Y       0       31      8
+ at last_b
+2001-02-03
+SELECT LAST_VALUE();
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1

=== added file 'mysql-test/t/last_value.test'
--- a/mysql-test/t/last_value.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/t/last_value.test	2012-09-05 15:23:51 +0000
@@ -0,0 +1,43 @@
+#
+# Tests for the LAST_VALUE function
+#
+
+--disable_warnings
+drop table if exists t1;
+drop database if exists mysqltest;
+--enable_warnings
+
+# CREATE TABLE `queue` (
+#  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+#  `priority` int(11) DEFAULT NULL,
+#  `state` int(11) DEFAULT NULL,
+#  `pid` int(10) unsigned DEFAULT NULL,
+#  `dat` varbinary(200) DEFAULT NULL,
+#  PRIMARY KEY (`id`)
+# )
+
+CREATE TABLE t1 (a INT, b INT, c INT, d INT);
+INSERT INTO t1 VALUES (1,3,0,NULL),(2,2,0,NULL),(3,4,0,NULL),(4,2,0,NULL);
+SELECT * FROM t1;
+UPDATE t1 SET c=LAST_VALUE(@last_a:=a, at last_b:=b, at last_c:=c,1), d=4211 WHERE c=0 ORDER BY b DESC LIMIT 1;
+SELECT @last_a, @last_b, @last_c;
+SELECT * FROM t1;
+DROP TABLE t1;
+
+#
+# Test with different types
+#
+--enable_metadata
+SELECT LAST_VALUE(@last_a:=1, at last_b:=1);
+select @last_b;
+SELECT LAST_VALUE(@last_a:=1, at last_b:=1.0);
+select @last_b;
+SELECT LAST_VALUE(@last_a:=1, at last_b:="hello");
+select @last_b;
+SELECT date(LAST_VALUE(@last_a:=1, at last_b:="2001-02-03"));
+select @last_b;
+SELECT LAST_VALUE(@last_a:=1, at last_b:="2001-02-03",NULL);
+select @last_b;
+--disable_metadata
+--error ER_PARSE_ERROR
+SELECT LAST_VALUE();

=== modified file 'sql/item_func.cc'
--- a/sql/item_func.cc	2012-08-24 13:29:01 +0000
+++ b/sql/item_func.cc	2012-09-05 15:23:51 +0000
@@ -6816,3 +6816,62 @@ longlong Item_func_uuid_short::val_int()
   mysql_mutex_unlock(&LOCK_short_uuid_generator);
   return (longlong) val;
 }
+
+
+/**
+  Last_value - return last argument.
+*/
+
+void Item_func_last_value::evaluate_sideeffects()
+{
+  DBUG_ASSERT(fixed == 1 && arg_count > 0);
+  for (uint i= 0; i < arg_count-1 ; i++)
+    args[i]->val_int();
+}
+
+String *Item_func_last_value::val_str(String *str)
+{
+  String *tmp;
+  evaluate_sideeffects();
+  tmp= last_value->val_str(str);
+  null_value= last_value->null_value;
+  return tmp;
+}
+
+longlong Item_func_last_value::val_int()
+{
+  longlong tmp;
+  evaluate_sideeffects();
+  tmp= last_value->val_int();
+  null_value= last_value->null_value;
+  return tmp;
+}
+
+double Item_func_last_value::val_real()
+{
+  double tmp;
+  evaluate_sideeffects();
+  tmp= last_value->val_real();
+  null_value= last_value->null_value;
+  return tmp;
+}
+
+my_decimal *Item_func_last_value::val_decimal(my_decimal *decimal_value)
+{
+  my_decimal *tmp;
+  evaluate_sideeffects();
+  tmp= last_value->val_decimal(decimal_value);
+  null_value= last_value->null_value;
+  return tmp;
+}
+
+
+void Item_func_last_value::fix_length_and_dec()
+{
+  last_value=          args[arg_count -1];
+  decimals=            last_value->decimals;
+  max_length=          last_value->max_length;
+  collation.set(last_value->collation.collation);
+  maybe_null=          last_value->maybe_null;
+  unsigned_flag=       last_value->unsigned_flag;
+}

=== modified file 'sql/item_func.h'
--- a/sql/item_func.h	2012-08-24 13:29:01 +0000
+++ b/sql/item_func.h	2012-09-05 15:23:51 +0000
@@ -2003,6 +2003,27 @@ class Item_func_uuid_short :public Item_
   }
 };
 
+
+class Item_func_last_value :public Item_func
+{
+protected:
+  Item *last_value;
+public:
+  Item_func_last_value(List<Item> &list) :Item_func(list) {}
+  double val_real();
+  longlong val_int();
+  String *val_str(String *);
+  my_decimal *val_decimal(my_decimal *);
+  void fix_length_and_dec();
+  enum Item_result result_type () const { return last_value->result_type(); }
+  const char *func_name() const { return "last_value"; }
+  table_map not_null_tables() const { return 0; }
+  enum_field_types field_type() const { return last_value->field_type(); }
+  bool const_item() const { return 0; }
+  void evaluate_sideeffects();
+};
+
+
 Item *get_system_var(THD *thd, enum_var_type var_type, LEX_STRING name,
                      LEX_STRING component);
 extern bool check_reserved_words(LEX_STRING *name);

=== modified file 'sql/lex.h'
--- a/sql/lex.h	2012-03-11 22:45:18 +0000
+++ b/sql/lex.h	2012-09-05 15:23:51 +0000
@@ -302,6 +302,7 @@ static SYMBOL symbols[] = {
   { "KILL",             SYM(KILL_SYM)},
   { "LANGUAGE",         SYM(LANGUAGE_SYM)},
   { "LAST",             SYM(LAST_SYM)},
+  { "LAST_VALUE",       SYM(LAST_VALUE)},
   { "LEADING",          SYM(LEADING)},
   { "LEAVE",            SYM(LEAVE_SYM)},
   { "LEAVES",           SYM(LEAVES)},

=== modified file 'sql/sql_yacc.yy'
--- a/sql/sql_yacc.yy	2012-08-31 12:15:52 +0000
+++ b/sql/sql_yacc.yy	2012-09-05 15:23:51 +0000
@@ -1064,6 +1064,7 @@ bool my_yyoverflow(short **a, YYSTYPE **
 %token  KILL_SYM
 %token  LANGUAGE_SYM                  /* SQL-2003-R */
 %token  LAST_SYM                      /* SQL-2003-N */
+%token  LAST_VALUE
 %token  LE                            /* OPERATOR */
 %token  LEADING                       /* SQL-2003-R */
 %token  LEAVES
@@ -8890,6 +8891,12 @@ rule: <-- starts at col 1
             if ($$ == NULL)
               MYSQL_YYABORT;
           }
+        | LAST_VALUE '(' expr_list ')'
+          {
+            $$= new (YYTHD->mem_root) Item_func_last_value(* $3);
+            if ($$ == NULL)
+              MYSQL_YYABORT;
+          }
         | MICROSECOND_SYM '(' expr ')'
           {
             $$= new (YYTHD->mem_root) Item_func_microsecond($3);
@@ -13088,6 +13095,7 @@ select_var_ident:
         | ISSUER_SYM               {}
         | INSERT_METHOD            {}
         | KEY_BLOCK_SIZE           {}
+        | LAST_VALUE               {}
         | LAST_SYM                 {}
         | LEAVES                   {}
         | LESS_SYM                 {}



More information about the commits mailing list