1# 2# Tests for the LAST_VALUE function 3# 4 5--disable_warnings 6drop table if exists t1; 7drop database if exists mysqltest; 8--enable_warnings 9 10# CREATE TABLE `queue` ( 11# `id` int(10) unsigned NOT NULL AUTO_INCREMENT, 12# `priority` int(11) DEFAULT NULL, 13# `state` int(11) DEFAULT NULL, 14# `pid` int(10) unsigned DEFAULT NULL, 15# `dat` varbinary(200) DEFAULT NULL, 16# PRIMARY KEY (`id`) 17# ) 18 19CREATE TABLE t1 (a INT, b INT, c INT, d INT); 20INSERT INTO t1 VALUES (1,3,0,NULL),(2,2,0,NULL),(3,4,0,NULL),(4,2,0,NULL); 21SELECT * FROM t1; 22UPDATE 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; 23SELECT @last_a, @last_b, @last_c; 24SELECT * FROM t1; 25DROP TABLE t1; 26 27# 28# Test with different types 29# 30# PS protocol gives slightly different metadata for the length 31--enable_metadata 32--disable_ps_protocol 33SELECT LAST_VALUE(@last_a:=1,@last_b:=1); 34select @last_b; 35--enable_ps_protocol 36SELECT LAST_VALUE(@last_a:=1,@last_b:=1.0); 37select @last_b; 38SELECT LAST_VALUE(@last_a:=1,@last_b:="hello"); 39select @last_b; 40SELECT date(LAST_VALUE(@last_a:=1,@last_b:="2001-02-03")); 41select @last_b; 42SELECT LAST_VALUE(@last_a:=1,@last_b:="2001-02-03",NULL); 43select @last_b; 44--disable_metadata 45--error ER_PARSE_ERROR 46SELECT LAST_VALUE(); 47