diff options
| -rw-r--r-- | client/mysql.cc | 1 | ||||
| -rw-r--r-- | mysql-test/r/last_value.result | 73 | ||||
| -rw-r--r-- | mysql-test/t/last_value.test | 43 | ||||
| -rw-r--r-- | sql/item_func.cc | 59 | ||||
| -rw-r--r-- | sql/item_func.h | 21 | ||||
| -rw-r--r-- | sql/lex.h | 1 | ||||
| -rw-r--r-- | sql/sql_yacc.yy | 8 |
7 files changed, 206 insertions, 0 deletions
diff --git a/client/mysql.cc b/client/mysql.cc index d39e765a99a..5aab3e1098f 100644 --- a/client/mysql.cc +++ b/client/mysql.cc @@ -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, ""}, diff --git a/mysql-test/r/last_value.result b/mysql-test/r/last_value.result new file mode 100644 index 00000000000..6222eacd4f0 --- /dev/null +++ b/mysql-test/r/last_value.result @@ -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,@last_b:=b,@last_c:=c,1), d=4211 WHERE c=0 ORDER BY b DESC LIMIT 1; +SELECT @last_a, @last_b, @last_c; +@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,@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,@last_b:=1) 8 1 1 N 32897 0 63 +LAST_VALUE(@last_a:=1,@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 +@last_b +1 +SELECT LAST_VALUE(@last_a:=1,@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,@last_b:=1.0) 246 4 3 N 32897 1 63 +LAST_VALUE(@last_a:=1,@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 +@last_b +1.0 +SELECT LAST_VALUE(@last_a:=1,@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,@last_b:="hello") 253 5 5 N 1 31 8 +LAST_VALUE(@last_a:=1,@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 +@last_b +hello +SELECT date(LAST_VALUE(@last_a:=1,@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,@last_b:="2001-02-03")) 10 10 10 Y 128 0 63 +date(LAST_VALUE(@last_a:=1,@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 +@last_b +2001-02-03 +SELECT LAST_VALUE(@last_a:=1,@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,@last_b:="2001-02-03",NULL) 6 0 0 Y 32896 0 63 +LAST_VALUE(@last_a:=1,@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 +@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 diff --git a/mysql-test/t/last_value.test b/mysql-test/t/last_value.test new file mode 100644 index 00000000000..ee793f879da --- /dev/null +++ b/mysql-test/t/last_value.test @@ -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,@last_b:=b,@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,@last_b:=1); +select @last_b; +SELECT LAST_VALUE(@last_a:=1,@last_b:=1.0); +select @last_b; +SELECT LAST_VALUE(@last_a:=1,@last_b:="hello"); +select @last_b; +SELECT date(LAST_VALUE(@last_a:=1,@last_b:="2001-02-03")); +select @last_b; +SELECT LAST_VALUE(@last_a:=1,@last_b:="2001-02-03",NULL); +select @last_b; +--disable_metadata +--error ER_PARSE_ERROR +SELECT LAST_VALUE(); diff --git a/sql/item_func.cc b/sql/item_func.cc index 7a7cdd4ba02..1e891f06f0b 100644 --- a/sql/item_func.cc +++ b/sql/item_func.cc @@ -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; +} diff --git a/sql/item_func.h b/sql/item_func.h index 111479c8e52..586444e0e4e 100644 --- a/sql/item_func.h +++ b/sql/item_func.h @@ -2003,6 +2003,27 @@ public: } }; + +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); diff --git a/sql/lex.h b/sql/lex.h index 9f4369630a0..9bf4c439cb6 100644 --- a/sql/lex.h +++ b/sql/lex.h @@ -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)}, diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 220eeb5b9a7..555efaf366d 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -1064,6 +1064,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize); %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 @@ function_call_conflict: 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 @@ keyword_sp: | ISSUER_SYM {} | INSERT_METHOD {} | KEY_BLOCK_SIZE {} + | LAST_VALUE {} | LAST_SYM {} | LEAVES {} | LESS_SYM {} |