diff options
| author | zhzhzoo <zhzhzoo@gmail.com> | 2018-03-17 01:22:53 +0800 |
|---|---|---|
| committer | Vicențiu Ciorbaru <vicentiu@mariadb.org> | 2018-08-25 18:47:14 +0300 |
| commit | 2a361ebe1b9c52eadfe811a219a89df6a0d6e3ea (patch) | |
| tree | c7412974df44d3b7a9f01fe3e9a8a269a057a664 | |
| parent | c826b6b8da3c9ee89ab457155bae96310213e5a1 (diff) | |
| download | mariadb-git-2a361ebe1b9c52eadfe811a219a89df6a0d6e3ea.tar.gz | |
MDEV-15204: lag/lead function order list mandatory
| -rw-r--r-- | mysql-test/r/win.result | 4 | ||||
| -rw-r--r-- | mysql-test/r/win_lead_lag.result | 11 | ||||
| -rw-r--r-- | mysql-test/t/win.test | 4 | ||||
| -rw-r--r-- | mysql-test/t/win_lead_lag.test | 13 | ||||
| -rw-r--r-- | sql/item_windowfunc.h | 2 |
5 files changed, 30 insertions, 4 deletions
diff --git a/mysql-test/r/win.result b/mysql-test/r/win.result index b519b2bb223..e4b2d223a02 100644 --- a/mysql-test/r/win.result +++ b/mysql-test/r/win.result @@ -3219,8 +3219,8 @@ DROP TABLE fv_test, fv_result; # CREATE TABLE t1 (a int); INSERT INTO t1 VALUES (0),(1),(2); -SELECT LEAD(a) OVER (PARTITION BY a) as lead, -a AND LEAD(a) OVER (PARTITION BY a) AS a_and_lead_part +SELECT LEAD(a) OVER (PARTITION BY a ORDER BY a) as lead, +a AND LEAD(a) OVER (PARTITION BY a ORDER BY a) AS a_and_lead_part FROM t1; lead a_and_lead_part NULL 0 diff --git a/mysql-test/r/win_lead_lag.result b/mysql-test/r/win_lead_lag.result index 5ead58fa11a..f3c0b8f7ef2 100644 --- a/mysql-test/r/win_lead_lag.result +++ b/mysql-test/r/win_lead_lag.result @@ -226,4 +226,15 @@ pk a b a+b lag(a + b) over (partition by a order by pk) + pk 9 2 2 4 12 10 2 0 2 14 11 2 10 12 13 +# +# MDEV-15204 - LAG function doesn't require ORDER BY in OVER clause +# +select pk, +lag(pk, 1) over () +from t1; +ERROR HY000: No order list in window specification for 'lag' +select pk, +lead(pk, 1) over () +from t1; +ERROR HY000: No order list in window specification for 'lead' drop table t1; diff --git a/mysql-test/t/win.test b/mysql-test/t/win.test index b354a55d0d6..f47237ee59e 100644 --- a/mysql-test/t/win.test +++ b/mysql-test/t/win.test @@ -2000,8 +2000,8 @@ DROP TABLE fv_test, fv_result; CREATE TABLE t1 (a int); INSERT INTO t1 VALUES (0),(1),(2); -SELECT LEAD(a) OVER (PARTITION BY a) as lead, - a AND LEAD(a) OVER (PARTITION BY a) AS a_and_lead_part +SELECT LEAD(a) OVER (PARTITION BY a ORDER BY a) as lead, + a AND LEAD(a) OVER (PARTITION BY a ORDER BY a) AS a_and_lead_part FROM t1; SELECT a OR LEAD(a) OVER (ORDER BY a) AS a_or_lead_order diff --git a/mysql-test/t/win_lead_lag.test b/mysql-test/t/win_lead_lag.test index 2824f83789c..d154244ecd0 100644 --- a/mysql-test/t/win_lead_lag.test +++ b/mysql-test/t/win_lead_lag.test @@ -107,4 +107,17 @@ select pk, a, b, a+b, 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; diff --git a/sql/item_windowfunc.h b/sql/item_windowfunc.h index e5afb9e1555..21270733051 100644 --- a/sql/item_windowfunc.h +++ b/sql/item_windowfunc.h @@ -792,6 +792,8 @@ public: case Item_sum::DENSE_RANK_FUNC: case Item_sum::PERCENT_RANK_FUNC: case Item_sum::CUME_DIST_FUNC: + case Item_sum::LAG_FUNC: + case Item_sum::LEAD_FUNC: return true; default: return false; |