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;
#enable after fix MDEV-27871
--disable_view_protocol
select pk, a, b,
    ntile((select a from t1 where pk=13)) over (partition by b order by pk)
from t1;

explain
select pk, a, b,
    ntile((select a from t1 where pk=13)) over (partition by b order by pk)
from t1;
--enable_view_protocol

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;