From 3f619478f796eddbba6e39502fe941b285dd97b1 Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Sat, 4 May 2024 20:00:34 +0200 Subject: Adding upstream version 1:10.11.6. Signed-off-by: Daniel Baumann --- mysql-test/main/win_lead_lag.test | 123 ++++++++++++++++++++++++++++++++++++++ 1 file changed, 123 insertions(+) create mode 100644 mysql-test/main/win_lead_lag.test (limited to 'mysql-test/main/win_lead_lag.test') diff --git a/mysql-test/main/win_lead_lag.test b/mysql-test/main/win_lead_lag.test new file mode 100644 index 00000000..d154244e --- /dev/null +++ b/mysql-test/main/win_lead_lag.test @@ -0,0 +1,123 @@ +create table t1 ( + pk int primary key, + a int, + b int, + c char(10), + d decimal(10, 3), + e real +); + +insert into t1 values +( 1, 0, 1, 'one', 0.1, 0.001), +( 2, 0, 2, 'two', 0.2, 0.002), +( 3, 0, 3, 'three', 0.3, 0.003), +( 4, 1, 2, 'three', 0.4, 0.004), +( 5, 1, 1, 'two', 0.5, 0.005), +( 6, 1, 1, 'one', 0.6, 0.006), +( 7, 2, NULL, 'n_one', 0.5, 0.007), +( 8, 2, 1, 'n_two', NULL, 0.008), +( 9, 2, 2, NULL, 0.7, 0.009), +(10, 2, 0, 'n_four', 0.8, 0.010), +(11, 2, 10, NULL, 0.9, NULL); + +select pk, + lead(pk) over (order by pk), + lead(pk, 1) over (order by pk), + lead(pk, 2) over (order by pk), + lead(pk, 0) over (order by pk), + lead(pk, -1) over (order by pk), + lead(pk, -2) over (order by pk) +from t1 +order by pk asc; + +select pk, + lag(pk) over (order by pk), + lag(pk, 1) over (order by pk), + lag(pk, 2) over (order by pk), + lag(pk, 0) over (order by pk), + lag(pk, -1) over (order by pk), + lag(pk, -2) over (order by pk) +from t1 +order by pk asc; + +select pk, pk - 2, + lag(pk, pk - 2) over (order by pk), + lead(pk, pk - 2) over (order by pk) +from t1 +order by pk asc; + +select pk, pk - 2, + lag(pk, pk + 2) over (order by pk), + lead(pk, pk + 2) over (order by pk) +from t1 +order by pk asc; + +select pk, a, + lead(pk) over (partition by a order by pk), + lead(pk, 1) over (partition by a order by pk), + lead(pk, 2) over (partition by a order by pk), + lead(pk, 0) over (partition by a order by pk), + lead(pk, -1) over (partition by a order by pk), + lead(pk, -2) over (partition by a order by pk) +from t1 +order by pk asc; + +select pk, a, + lag(pk) over (partition by a order by pk), + lag(pk, 1) over (partition by a order by pk), + lag(pk, 2) over (partition by a order by pk), + lag(pk, 0) over (partition by a order by pk), + lag(pk, -1) over (partition by a order by pk), + lag(pk, -2) over (partition by a order by pk) +from t1 +order by pk asc; + +select pk, a, pk - 2, + lag(pk, pk - 2) over (partition by a order by pk), + lead(pk, pk - 2) over (partition by a order by pk), + lag(pk, a - 2) over (partition by a order by pk), + lead(pk, a - 2) over (partition by a order by pk) +from t1 +order by pk asc; + +select pk, a, pk - 2, + lag(pk, pk + 2) over (partition by a order by pk), + lead(pk, pk + 2) over (partition by a order by pk), + lag(pk, a + 2) over (partition by a order by pk), + lead(pk, a + 2) over (partition by a order by pk) +from t1 +order by pk asc; + +select pk, a, b, c, d, e, + lag(a) over (partition by a order by pk), + lag(b) over (partition by a order by pk), + lag(c) over (partition by a order by pk), + lag(d) over (partition by a order by pk), + lag(e) over (partition by a order by pk) +from t1 +order by pk asc; + +select pk, a, b, a+b, + lag(a + b) over (partition by a order by pk) +from t1 +order by pk asc; + +select pk, a, b, a+b, + lag(a + b) over (partition by a order by pk) + pk +from t1 +order by pk asc; + +--echo # +--echo # MDEV-15204 - LAG function doesn't require ORDER BY in OVER clause +--echo # +--error ER_NO_ORDER_LIST_IN_WINDOW_SPEC +select pk, + lag(pk, 1) over () +from t1; + +--error ER_NO_ORDER_LIST_IN_WINDOW_SPEC +select pk, + lead(pk, 1) over () +from t1; + +drop table t1; -- cgit v1.2.3