mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 02:46:29 +01:00
214 lines
4 KiB
Text
214 lines
4 KiB
Text
create table t1 (
|
|
pk int primary key,
|
|
a int,
|
|
b int
|
|
);
|
|
|
|
|
|
insert into t1 values
|
|
(11 , 0, 10),
|
|
(12 , 0, 10),
|
|
(13 , 1, 10),
|
|
(14 , 1, 10),
|
|
(18 , 2, 10),
|
|
(15 , 2, 20),
|
|
(16 , 2, 20),
|
|
(17 , 2, 20),
|
|
(19 , 4, 20),
|
|
(20 , 4, 20);
|
|
|
|
# TODO Try invalid queries too.
|
|
|
|
--error ER_INVALID_NTILE_ARGUMENT
|
|
select pk, a, b, ntile(-1) over (order by a)
|
|
from t1;
|
|
|
|
--error ER_INVALID_NTILE_ARGUMENT
|
|
select pk, a, b,
|
|
ntile(0) over (order by a)
|
|
from t1;
|
|
|
|
--sorted_result
|
|
select pk, a, b,
|
|
ntile(1) over (order by pk)
|
|
from t1;
|
|
|
|
--sorted_result
|
|
select pk, a, b,
|
|
ntile(2) over (order by pk)
|
|
from t1;
|
|
|
|
--sorted_result
|
|
select pk, a, b,
|
|
ntile(3) over (order by pk)
|
|
from t1;
|
|
|
|
--sorted_result
|
|
select pk, a, b,
|
|
ntile(4) over (order by pk)
|
|
from t1;
|
|
|
|
--sorted_result
|
|
select pk, a, b,
|
|
ntile(5) over (order by pk)
|
|
from t1;
|
|
|
|
--sorted_result
|
|
select pk, a, b,
|
|
ntile(6) over (order by pk)
|
|
from t1;
|
|
|
|
--sorted_result
|
|
select pk, a, b,
|
|
ntile(7) over (order by pk)
|
|
from t1;
|
|
|
|
--sorted_result
|
|
select pk, a, b,
|
|
ntile(8) over (order by pk)
|
|
from t1;
|
|
|
|
--sorted_result
|
|
select pk, a, b,
|
|
ntile(9) over (order by pk)
|
|
from t1;
|
|
|
|
--sorted_result
|
|
select pk, a, b,
|
|
ntile(10) over (order by pk)
|
|
from t1;
|
|
|
|
--sorted_result
|
|
select pk, a, b,
|
|
ntile(11) over (order by pk)
|
|
from t1;
|
|
|
|
--sorted_result
|
|
select pk, a, b,
|
|
ntile(20) over (order by pk)
|
|
from t1;
|
|
|
|
|
|
select pk, a, b,
|
|
ntile(1) over (partition by b order by pk)
|
|
from t1;
|
|
|
|
select pk, a, b,
|
|
ntile(2) over (partition by b order by pk)
|
|
from t1;
|
|
|
|
select pk, a, b,
|
|
ntile(3) over (partition by b order by pk)
|
|
from t1;
|
|
|
|
select pk, a, b,
|
|
ntile(4) over (partition by b order by pk)
|
|
from t1;
|
|
|
|
select pk, a, b,
|
|
ntile(5) over (partition by b order by pk)
|
|
from t1;
|
|
|
|
select pk, a, b,
|
|
ntile(6) over (partition by b order by pk)
|
|
from t1;
|
|
|
|
select pk, a, b,
|
|
ntile(7) over (partition by b order by pk)
|
|
from t1;
|
|
|
|
select pk, a, b,
|
|
ntile(8) over (partition by b order by pk)
|
|
from t1;
|
|
|
|
select pk, a, b,
|
|
ntile(9) over (partition by b order by pk)
|
|
from t1;
|
|
|
|
select pk, a, b,
|
|
ntile(10) over (partition by b order by pk)
|
|
from t1;
|
|
|
|
select pk, a, b,
|
|
ntile(11) over (partition by b order by pk)
|
|
from t1;
|
|
|
|
select pk, a, b,
|
|
ntile(20) over (partition by b order by pk)
|
|
from t1;
|
|
|
|
select pk, a, b,
|
|
ntile(1 + 3) over (partition by b order by pk)
|
|
from t1;
|
|
|
|
select pk, a, b,
|
|
ntile((select 4)) over (partition by b order by pk)
|
|
from t1;
|
|
|
|
select t1.a from t1 where pk = 11;
|
|
--error ER_INVALID_NTILE_ARGUMENT
|
|
select pk, a, b,
|
|
ntile((select a from t1 where pk=11)) over (partition by b order by pk)
|
|
from t1;
|
|
|
|
select t1.a from t1 where pk = 13;
|
|
select pk, a, b,
|
|
ntile((select a from t1 where pk=13)) over (partition by b order by pk) as ntile
|
|
from t1;
|
|
|
|
explain
|
|
select pk, a, b,
|
|
ntile((select a from t1 where pk=13)) over (partition by b order by pk) as ntile
|
|
from t1;
|
|
|
|
select a from t1;
|
|
--error ER_SUBQUERY_NO_1_ROW
|
|
select pk, a, b,
|
|
ntile((select a from t1)) over (partition by b order by pk)
|
|
from t1;
|
|
|
|
|
|
drop table t1;
|
|
|
|
--echo #
|
|
--echo # MDEV-9911 NTILE must return an error when parameter is not stable
|
|
--echo #
|
|
|
|
create table t1 (
|
|
pk int primary key,
|
|
c1 nvarchar(10),
|
|
c2 nvarchar(10),
|
|
c3 int
|
|
);
|
|
|
|
insert into t1 values
|
|
(1, 'Mark', 'Male', 5),
|
|
(2, 'John', 'Male', 5),
|
|
(3, 'Pam', 'Female', 6),
|
|
(4, 'Sara', 'Female', 6),
|
|
(5, 'Todd', 'Male', 5),
|
|
(6, 'Mary', 'Female', 6),
|
|
(7, 'Ben', 'Male', 5),
|
|
(8, 'Jodi', 'Female', 6),
|
|
(9, 'Tom', 'Male', 5),
|
|
(10, 'Lucky', 'Male', 5),
|
|
(11, 'Mark', 'Male', 5),
|
|
(12, 'John', 'Male', 5),
|
|
(13, 'Pam', 'Female', 6),
|
|
(14, 'Sara', 'Female', 6),
|
|
(15, 'Todd', 'Male', 5),
|
|
(16, 'Mary', 'Female', 6),
|
|
(17, 'Ben', 'Male', 5),
|
|
(18, 'Jodi', 'Female', 6),
|
|
(19, 'Tom', 'Male', 5),
|
|
(20, 'Lucky', 'Male', 5);
|
|
# Correct usage of NTILE with a fix argument NTILE(6).
|
|
select c1, c2, c3, ntile(6) over (partition by c2 order by pk) from t1;
|
|
# Correct usage - constant NTILE (argument) in each partition.
|
|
select c1, c2, c3, ntile(c3) over (partition by c2 order by pk) from t1;
|
|
|
|
update t1 set c3= 1 where pk = 1;
|
|
--error ER_INVALID_NTILE_ARGUMENT
|
|
select c1, c2, c3, ntile(c3) over (partition by c2 order by pk) from t1;
|
|
|
|
drop table t1;
|