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