mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 19:06:14 +01:00
1084 lines
21 KiB
Text
1084 lines
21 KiB
Text
--echo #
|
|
--echo # The following entries are meant for testing the parser, ensuring
|
|
--echo # the right values are passed down to the executor, for all possible
|
|
--echo # syntax combinations.
|
|
--echo #
|
|
--echo # Test basic syntax.
|
|
--echo #
|
|
|
|
create table t1 (a int);
|
|
create table t_keyword (`offset` int);
|
|
|
|
insert into t1 values (1), (1), (2), (3), (2);
|
|
insert into t_keyword values (1), (1), (2), (3), (2);
|
|
|
|
--echo #
|
|
--echo # Make sure the FETCH clause addition didn't introduce problems with
|
|
--echo # the offset keyword.
|
|
--echo #
|
|
#enable after fix MDEV-29645
|
|
--disable_view_protocol
|
|
select * from t1
|
|
order by a
|
|
offset 2 rows;
|
|
|
|
|
|
--sorted_result
|
|
select * from t1
|
|
offset 2 rows;
|
|
--enable_view_protocol
|
|
|
|
--echo #
|
|
--echo # Offset is now a reserved keyword. Column names can not have that name
|
|
--echo # without escaping the identifier.
|
|
--echo #
|
|
--error 1064
|
|
select * from t_keyword
|
|
order by offset;
|
|
|
|
select * from t_keyword
|
|
order by `offset`;
|
|
|
|
|
|
--echo #
|
|
--echo # Test syntax without a specific limit. (implicit one row)
|
|
--echo # Test the alias between row / rows and first / next.
|
|
--echo # Test ONLY vs WITH TIES.
|
|
--echo #
|
|
select * from t1
|
|
fetch first row only;
|
|
|
|
select * from t1
|
|
fetch first rows only;
|
|
|
|
select * from t1
|
|
fetch next row only;
|
|
|
|
select * from t1
|
|
fetch next rows only;
|
|
|
|
--echo #
|
|
--echo # Row / rows are mandatory after offset <n>
|
|
--echo #
|
|
--error 1064
|
|
select * from t1
|
|
order by a
|
|
offset 2
|
|
fetch first row only;
|
|
|
|
--error 1064
|
|
select * from t1
|
|
order by a
|
|
offset 2;
|
|
|
|
select * from t1
|
|
order by a
|
|
offset 2 row
|
|
fetch first row only;
|
|
|
|
select * from t1
|
|
order by a
|
|
offset 2 rows
|
|
fetch first row only;
|
|
|
|
--echo #
|
|
--echo # Include offset before fetch clause.
|
|
--echo #
|
|
select * from t1
|
|
order by a
|
|
offset 2 rows
|
|
fetch first row only;
|
|
|
|
select * from t1
|
|
order by a
|
|
offset 2 rows
|
|
fetch first rows only;
|
|
|
|
select * from t1
|
|
offset 2 rows
|
|
fetch next row only;
|
|
|
|
select * from t1
|
|
offset 2 rows
|
|
fetch next rows only;
|
|
|
|
|
|
--echo #
|
|
--echo # Repeat the tests, but now with WITH TIES.
|
|
--echo # WITH TIES requires order by.
|
|
--echo #
|
|
|
|
--error ER_WITH_TIES_NEEDS_ORDER
|
|
select * from t1
|
|
fetch first row with ties;
|
|
|
|
--error ER_WITH_TIES_NEEDS_ORDER
|
|
select * from t1
|
|
fetch first rows with ties;
|
|
|
|
--error ER_WITH_TIES_NEEDS_ORDER
|
|
select * from t1
|
|
fetch next row with ties;
|
|
|
|
--error ER_WITH_TIES_NEEDS_ORDER
|
|
select * from t1
|
|
fetch next rows with ties;
|
|
|
|
select * from t1
|
|
order by a
|
|
fetch first row with ties;
|
|
|
|
select * from t1
|
|
order by a
|
|
fetch first rows with ties;
|
|
|
|
select * from t1
|
|
order by a
|
|
fetch next row with ties;
|
|
|
|
select * from t1
|
|
order by a
|
|
fetch next rows with ties;
|
|
|
|
--echo #
|
|
--echo # Include offset before fetch clause.
|
|
--echo #
|
|
|
|
select * from t1
|
|
order by a
|
|
offset 2 rows
|
|
fetch first row with ties;
|
|
|
|
select * from t1
|
|
order by a
|
|
offset 2 rows
|
|
fetch first rows with ties;
|
|
|
|
select * from t1
|
|
order by a
|
|
offset 2 rows
|
|
fetch next row with ties;
|
|
|
|
select * from t1
|
|
order by a
|
|
offset 2 rows
|
|
fetch next rows with ties;
|
|
|
|
|
|
--echo #
|
|
--echo # Test syntax with a specific limit
|
|
--echo #
|
|
select * from t1
|
|
fetch first 3 row only;
|
|
|
|
select * from t1
|
|
fetch first 3 rows only;
|
|
|
|
select * from t1
|
|
fetch next 3 row only;
|
|
|
|
select * from t1
|
|
fetch next 3 rows only;
|
|
|
|
--echo #
|
|
--echo # Include offset before fetch clause.
|
|
--echo #
|
|
select * from t1
|
|
order by a
|
|
offset 2 rows
|
|
fetch first 3 row only;
|
|
|
|
select * from t1
|
|
order by a
|
|
offset 2 rows
|
|
fetch first 3 rows only;
|
|
|
|
select * from t1
|
|
offset 2 rows
|
|
fetch next 3 row only;
|
|
|
|
select * from t1
|
|
offset 2 rows
|
|
fetch next 3 rows only;
|
|
|
|
|
|
--echo #
|
|
--echo # Repeat the tests, but now with WITH TIES.
|
|
--echo # WITH TIES requires order by.
|
|
--echo #
|
|
|
|
--error ER_WITH_TIES_NEEDS_ORDER
|
|
select * from t1
|
|
fetch first 3 row with ties;
|
|
|
|
--error ER_WITH_TIES_NEEDS_ORDER
|
|
select * from t1
|
|
fetch first 3 rows with ties;
|
|
|
|
--error ER_WITH_TIES_NEEDS_ORDER
|
|
select * from t1
|
|
fetch next 3 row with ties;
|
|
|
|
--error ER_WITH_TIES_NEEDS_ORDER
|
|
select * from t1
|
|
fetch next 3 rows with ties;
|
|
|
|
select * from t1
|
|
order by a
|
|
fetch first 3 row with ties;
|
|
|
|
select * from t1
|
|
order by a
|
|
fetch first 3 rows with ties;
|
|
|
|
select * from t1
|
|
order by a
|
|
fetch next 3 row with ties;
|
|
|
|
select * from t1
|
|
order by a
|
|
fetch next 3 rows with ties;
|
|
|
|
--echo #
|
|
--echo # Include offset before fetch clause.
|
|
--echo #
|
|
|
|
--error ER_WITH_TIES_NEEDS_ORDER
|
|
select * from t1
|
|
offset 2 rows
|
|
fetch first row with ties;
|
|
|
|
select * from t1
|
|
order by a
|
|
offset 2 rows
|
|
fetch first 3 row with ties;
|
|
|
|
select * from t1
|
|
order by a
|
|
offset 2 rows
|
|
fetch first 3 rows with ties;
|
|
|
|
select * from t1
|
|
order by a
|
|
offset 2 rows
|
|
fetch next 3 row with ties;
|
|
|
|
select * from t1
|
|
order by a
|
|
offset 2 rows
|
|
fetch next 3 rows with ties;
|
|
|
|
|
|
drop table t1;
|
|
drop table t_keyword;
|
|
|
|
--echo #
|
|
--echo # Test behaviour with a simple select.
|
|
--echo #
|
|
|
|
create table t1 (id int, first_name varchar(100), last_name varchar(100), score double);
|
|
insert into t1 values
|
|
(1, 'Alice', 'Fowler', 5),
|
|
(2, 'John', 'Doe', 6),
|
|
(3, 'John', 'Smith', 6),
|
|
(4, 'John', 'Smith', 6),
|
|
(5, 'John', 'Smith', 7),
|
|
(6, 'John', 'Elton', 8.1),
|
|
(7, 'Bob', 'Trasc', 9),
|
|
(8, 'Silvia', 'Ganush', 10);
|
|
|
|
create table t2
|
|
(id int,
|
|
location varchar(100),
|
|
fk int,
|
|
constraint `fk_t1`
|
|
FOREIGN KEY (fk) REFERENCES t1 (id)
|
|
ON DELETE CASCADE
|
|
ON UPDATE RESTRICT);
|
|
insert into t2 values
|
|
(1, 'L1', 1),
|
|
(2, 'L2', 2),
|
|
(3, 'L3', 3),
|
|
(4, 'L3', 4),
|
|
(5, 'L4', 5),
|
|
(6, 'L4', 6),
|
|
(7, 'L4', 7),
|
|
(7, null, 8);
|
|
|
|
|
|
select * from t1
|
|
order by id
|
|
fetch first 3 rows only;
|
|
|
|
select * from t1
|
|
order by id
|
|
fetch first 3 rows with ties;
|
|
|
|
select * from t1
|
|
order by first_name
|
|
fetch first 3 rows only;
|
|
|
|
select * from t1
|
|
order by first_name
|
|
fetch first 3 rows with ties;
|
|
|
|
--echo #
|
|
--echo # Test multi-part order by.
|
|
--echo #
|
|
select * from t1
|
|
order by first_name, last_name
|
|
fetch first 3 rows with ties;
|
|
|
|
select * from t1
|
|
order by first_name, last_name
|
|
fetch first 4 rows with ties;
|
|
|
|
|
|
select * from t1
|
|
order by first_name, last_name
|
|
offset 1 rows
|
|
fetch first 3 rows with ties;
|
|
|
|
select * from t1
|
|
order by first_name, last_name
|
|
offset 1 rows
|
|
fetch first 3 rows only;
|
|
|
|
select * from t1
|
|
order by first_name, last_name
|
|
offset 1 rows
|
|
fetch first 3 rows with ties;
|
|
|
|
select * from t1
|
|
order by first_name, last_name
|
|
offset 2 rows
|
|
fetch first 3 rows only;
|
|
|
|
select * from t1
|
|
order by first_name, last_name
|
|
offset 2 rows
|
|
fetch first 3 rows with ties;
|
|
|
|
select * from t1
|
|
order by first_name, last_name
|
|
offset 3 rows
|
|
fetch first 3 rows only;
|
|
|
|
select * from t1
|
|
order by first_name, last_name
|
|
offset 3 rows
|
|
fetch first 3 rows with ties;
|
|
|
|
select * from t1
|
|
order by first_name, last_name
|
|
offset 4 rows
|
|
fetch first 3 rows only;
|
|
|
|
select * from t1
|
|
order by first_name, last_name
|
|
offset 4 rows
|
|
fetch first 3 rows with ties;
|
|
|
|
--echo #
|
|
--echo # Test offset crossing into a new peer-group.
|
|
--echo #
|
|
select * from t1
|
|
order by first_name, last_name
|
|
offset 5 rows
|
|
fetch first 3 rows with ties;
|
|
|
|
select * from t1
|
|
order by first_name, last_name
|
|
offset 5 rows
|
|
fetch first 3 rows only;
|
|
|
|
--echo #
|
|
--echo # Simple join with 2 tables, order by without columns in the
|
|
--echo # second table and also with columns in the second table.
|
|
--echo #
|
|
--echo # Cover both only and with ties.
|
|
--echo #
|
|
select t1.id, t1.first_name, t1.last_name, t1.score, t2.location
|
|
from t1 inner join t2 on t1.id = t2.fk
|
|
order by t1.first_name, t1.last_name
|
|
fetch first 3 rows only;
|
|
|
|
select t1.id, t1.first_name, t1.last_name, t1.score, t2.location
|
|
from t1 inner join t2 on t1.id = t2.fk
|
|
order by t2.location, t1.first_name, t1.last_name
|
|
fetch first 3 rows only;
|
|
|
|
select t1.id, t1.first_name, t1.last_name, t1.score, t2.location
|
|
from t1 inner join t2 on t1.id = t2.fk
|
|
order by t1.first_name, t1.last_name
|
|
fetch first 3 rows with ties;
|
|
|
|
select t1.id, t1.first_name, t1.last_name, t1.score, t2.location
|
|
from t1 inner join t2 on t1.id = t2.fk
|
|
order by t2.location, t1.first_name, t1.last_name
|
|
fetch first 3 rows with ties;
|
|
|
|
--echo #
|
|
--echo # Test descending order by.
|
|
--echo #
|
|
select t1.id, t1.first_name, t1.last_name, t1.score, t2.location
|
|
from t1 inner join t2 on t1.id = t2.fk
|
|
order by t2.location desc, t1.first_name, t1.last_name
|
|
fetch first 3 rows only;
|
|
|
|
--sorted_result
|
|
select * from t2
|
|
order by t2.location desc
|
|
fetch first 2 rows with ties;
|
|
|
|
--sorted_result
|
|
select * from t2
|
|
order by t2.location desc
|
|
offset 1 rows
|
|
fetch first 2 rows with ties;
|
|
|
|
--sorted_result
|
|
select * from t2
|
|
order by t2.location desc
|
|
offset 2 rows
|
|
fetch first 2 rows with ties;
|
|
|
|
--echo #
|
|
--echo # Test a join with descending order by.
|
|
--echo #
|
|
select t1.id, t1.first_name, t1.last_name, t1.score, t2.location
|
|
from t1 inner join t2 on t1.id = t2.fk
|
|
order by t2.location desc, t1.first_name, t1.last_name
|
|
fetch first 3 rows with ties;
|
|
|
|
--echo #
|
|
--echo # Test subqueries.
|
|
--echo #
|
|
select * from (
|
|
select * from t2
|
|
order by t2.location desc
|
|
offset 2 rows
|
|
fetch first 2 rows with ties
|
|
) temp;
|
|
|
|
|
|
select * from t2
|
|
order by t2.location desc
|
|
offset 0 rows
|
|
fetch first 2 rows with ties;
|
|
|
|
create view v1 as (
|
|
select * from t2
|
|
order by t2.location desc
|
|
offset 0 rows
|
|
fetch first 2 rows with ties
|
|
);
|
|
|
|
create view v2 as (
|
|
select * from t2
|
|
order by t2.location desc
|
|
offset 1 rows
|
|
fetch first 2 rows with ties
|
|
);
|
|
|
|
create view v3 as (
|
|
select * from t2
|
|
order by t2.location desc
|
|
offset 2 rows
|
|
fetch first row with ties
|
|
);
|
|
|
|
|
|
|
|
select * from v1;
|
|
select * from v2;
|
|
select * from v3;
|
|
|
|
show create view v1;
|
|
show create view v2;
|
|
show create view v3;
|
|
|
|
--echo #
|
|
--echo # Test joins with views and order by referenced from the view.
|
|
--echo #
|
|
|
|
--sorted_result
|
|
select *
|
|
from t1 inner join v1 on t1.id = v1.fk
|
|
order by v1.location desc, t1.first_name
|
|
offset 1 rows
|
|
fetch first 1 rows with ties;
|
|
|
|
select first_name, last_name, sum(score)
|
|
from t1
|
|
group by first_name, last_name
|
|
order by first_name;
|
|
|
|
--echo #
|
|
--echo # Test with ties with group by. Make sure that if order by is a less
|
|
--echo # specific sort of group by, that WITH TIES still gets executed.
|
|
--echo #
|
|
|
|
explain
|
|
select first_name, last_name, sum(score)
|
|
from t1
|
|
group by first_name, last_name
|
|
order by first_name
|
|
offset 1 rows
|
|
fetch first 2 rows with ties;
|
|
|
|
--sorted_result
|
|
select first_name, last_name, sum(score)
|
|
from t1
|
|
group by first_name, last_name
|
|
order by first_name
|
|
offset 1 rows
|
|
fetch first 2 rows with ties;
|
|
|
|
|
|
|
|
drop table t1;
|
|
drop table t2;
|
|
drop view v1;
|
|
drop view v2;
|
|
drop view v3;
|
|
|
|
--echo #
|
|
--echo # Test with ties when an index is used to provide the order by.
|
|
--echo #
|
|
create table t1 (id int, first_name varchar(100), last_name varchar(100), score double);
|
|
|
|
create index t1_name on t1 (first_name, last_name);
|
|
|
|
insert into t1 values
|
|
(1, 'Alice', 'Fowler', 5),
|
|
(2, 'John', 'Doe', 6),
|
|
(3, 'John', 'Smith', 6),
|
|
(4, 'John', 'Smith', 6),
|
|
(5, 'John', 'Smith', 7),
|
|
(6, 'John', 'Elton', 8.1),
|
|
(7, 'Bob', 'Trasc', 9),
|
|
(8, 'Silvia', 'Ganush', 10);
|
|
|
|
explain select first_name, last_name
|
|
from t1
|
|
order by first_name
|
|
offset 1 rows
|
|
fetch first 2 rows with ties;
|
|
|
|
explain select first_name, last_name
|
|
from t1
|
|
order by first_name desc
|
|
fetch first 2 rows with ties;
|
|
|
|
|
|
select first_name, last_name
|
|
from t1
|
|
order by first_name
|
|
offset 1 rows
|
|
fetch first 2 rows with ties;
|
|
|
|
select first_name, last_name
|
|
from t1
|
|
order by first_name desc
|
|
fetch first 2 rows with ties;
|
|
|
|
|
|
select first_name, last_name
|
|
from t1
|
|
order by first_name desc
|
|
offset 1 rows
|
|
fetch first 2 rows with ties;
|
|
|
|
select first_name, last_name
|
|
from t1
|
|
order by first_name desc
|
|
offset 4 rows
|
|
fetch first 2 rows with ties;
|
|
|
|
select first_name, last_name
|
|
from t1
|
|
order by first_name desc
|
|
offset 4 rows
|
|
fetch first 3 rows with ties;
|
|
|
|
explain select first_name, last_name
|
|
from t1
|
|
where first_name != 'John'
|
|
order by first_name
|
|
fetch first 2 rows with ties;
|
|
|
|
select first_name, last_name
|
|
from t1
|
|
where first_name != 'John'
|
|
order by first_name
|
|
fetch first 2 rows with ties;
|
|
|
|
|
|
|
|
select first_name, last_name
|
|
from t1
|
|
where first_name != 'John'
|
|
group by first_name, last_name
|
|
order by first_name
|
|
fetch first 2 rows with ties;
|
|
|
|
|
|
--echo #
|
|
--echo # Test CTE support.
|
|
--echo #
|
|
explain with temp_table as (
|
|
select first_name, last_name
|
|
from t1
|
|
where first_name != 'John'
|
|
group by first_name, last_name
|
|
order by first_name
|
|
fetch first 2 rows with ties
|
|
)
|
|
select * from temp_table
|
|
order by first_name, last_name;
|
|
|
|
with temp_table as (
|
|
select first_name, last_name
|
|
from t1
|
|
group by first_name, last_name
|
|
order by first_name
|
|
fetch first 3 rows with ties
|
|
)
|
|
select * from temp_table
|
|
order by first_name, last_name;
|
|
|
|
with temp_table as (
|
|
select first_name, last_name
|
|
from t1
|
|
group by first_name, last_name
|
|
order by first_name
|
|
fetch first 3 rows with ties
|
|
)
|
|
select * from temp_table
|
|
order by last_name
|
|
fetch first 3 rows with ties;
|
|
|
|
--sorted_result
|
|
with temp_table as (
|
|
select first_name, last_name
|
|
from t1
|
|
group by first_name, last_name
|
|
order by first_name
|
|
fetch first 3 rows with ties
|
|
)
|
|
select * from temp_table
|
|
order by first_name
|
|
fetch first 3 rows with ties;
|
|
|
|
--sorted_result
|
|
with temp_table as (
|
|
select first_name, last_name
|
|
from t1
|
|
group by first_name, last_name
|
|
order by first_name
|
|
fetch first 3 rows with ties
|
|
)
|
|
select * from temp_table
|
|
order by first_name
|
|
offset 1 rows
|
|
fetch first 2 rows with ties;
|
|
|
|
#enable after fix MDEV-29645
|
|
--disable_view_protocol
|
|
select first_name, row_number() over () rn
|
|
from t1
|
|
order by rn
|
|
offset 1 rows
|
|
fetch first 2 rows with ties;
|
|
|
|
select first_name, row_number() over () rn
|
|
from t1
|
|
order by rn desc
|
|
offset 1 rows
|
|
fetch first 2 rows with ties;
|
|
--enable_view_protocol
|
|
|
|
select first_name, score, rank() over (ORDER BY score)
|
|
from t1
|
|
order by rank() over (ORDER BY score)
|
|
fetch first 3 rows with ties;
|
|
|
|
select first_name, score, rank() over (ORDER BY score)
|
|
from t1
|
|
order by rank() over (ORDER BY score)
|
|
offset 1 rows
|
|
fetch first 2 rows with ties;
|
|
|
|
select first_name, score, rank() over (ORDER BY score)
|
|
from t1
|
|
order by rank() over (ORDER BY score)
|
|
fetch first 6 rows with ties;
|
|
|
|
|
|
--echo #
|
|
--echo # Test nulls.
|
|
--echo #
|
|
create table t2 (id int, location varchar(100), time datetime, value int, fk int);
|
|
|
|
insert into t2 values (1, 'home', '2020-01-01 10:00', 10, 1);
|
|
insert into t2 values (2, 'home', '2020-01-01 11:00', 11, 2);
|
|
insert into t2 values (3, 'home', '2020-01-01 12:00', 12, 3);
|
|
insert into t2 values (4, 'home', '2020-01-01 13:00', 13, 3);
|
|
insert into t2 values (5, 'home', '2020-01-01 14:00', 13, 3);
|
|
insert into t2 values (6, 'home', '2020-01-01 15:00', 13, 2);
|
|
insert into t2 values (7, 'home', '2020-01-01 16:00', 13, 6);
|
|
insert into t2 values (8, 'outside', '2020-01-01 17:00', 17, 6);
|
|
insert into t2 values (9, 'outside', '2020-01-01 18:00', 17, 6);
|
|
insert into t2 values (10, 'outside', '2020-01-01 19:00', 17, 8);
|
|
insert into t2 values (11, 'outside', '2020-01-01 20:00', 16, 7);
|
|
insert into t2 values (12, 'outside', '2020-01-01 21:00', 16, 7);
|
|
|
|
--sorted_result
|
|
select *
|
|
from t1 left join t2 on t1.id = t2.fk
|
|
order by t2.location;
|
|
|
|
--sorted_result
|
|
select *
|
|
from t1 left join t2 on t1.id = t2.fk
|
|
order by t2.location
|
|
fetch first 1 row with ties;
|
|
|
|
--sorted_result
|
|
select *
|
|
from t1 left join t2 on t1.id = t2.fk
|
|
order by t2.location
|
|
fetch first 2 row with ties;
|
|
|
|
--sorted_result
|
|
select *
|
|
from t1 left join t2 on t1.id = t2.fk
|
|
order by t2.location
|
|
fetch first 3 row with ties;
|
|
|
|
--sorted_result
|
|
select *
|
|
from t1 left join t2 on t1.id = t2.fk
|
|
order by t2.location
|
|
offset 2 rows
|
|
fetch first 1 row with ties;
|
|
|
|
|
|
--echo #
|
|
--echo # Test union-like operator with multiple fetch first clauses.
|
|
--echo #
|
|
select * from t1 order by 1 fetch first 3 rows with ties;
|
|
select * from t1 order by first_name desc fetch first 3 rows with ties;
|
|
|
|
--sorted_result
|
|
(select * from t1 order by 1 fetch first 3 rows with ties)
|
|
intersect
|
|
(select * from t1 order by first_name desc fetch first 3 rows with ties)
|
|
order by first_name
|
|
fetch first 1 row with ties;
|
|
|
|
--sorted_result
|
|
(select * from t1 order by 1 fetch first 3 rows with ties)
|
|
except
|
|
(select * from t1 order by first_name desc fetch first 3 rows with ties)
|
|
order by first_name
|
|
fetch first 1 row with ties;
|
|
|
|
--sorted_result
|
|
(select * from t1 order by 1 fetch first 3 rows with ties)
|
|
except
|
|
(select * from t1 order by first_name desc fetch first 3 rows with ties)
|
|
order by first_name
|
|
offset 1 rows
|
|
fetch first 1 row with ties;
|
|
|
|
--sorted_result
|
|
select sum(score)
|
|
from t1
|
|
order by 1
|
|
fetch first 2 rows with ties;
|
|
|
|
--sorted_result
|
|
select sum(score)
|
|
from t1
|
|
group by id
|
|
order by 1
|
|
fetch first 2 rows with ties;
|
|
|
|
drop table t1;
|
|
drop table t2;
|
|
|
|
--echo #
|
|
--echo # Test SQL_CALC_FOUND_ROWS
|
|
--echo #
|
|
create table t1 (a int, b int, index (a,b));
|
|
insert into t1 values (1,1), (1,2), (1,3), (2,1), (2,2), (3,1);
|
|
|
|
#enable after fix MDEV-29645
|
|
--disable_view_protocol
|
|
select SQL_CALC_FOUND_ROWS a, b, count(*)
|
|
from t1
|
|
group by a, b
|
|
order by a
|
|
fetch first 1 rows with ties;
|
|
--disable_ps2_protocol
|
|
SELECT FOUND_ROWS();
|
|
--enable_ps2_protocol
|
|
|
|
--sorted_result
|
|
select SQL_CALC_FOUND_ROWS a, b
|
|
from t1
|
|
order by a
|
|
fetch first 1 rows with ties;
|
|
--disable_ps2_protocol
|
|
SELECT FOUND_ROWS();
|
|
--enable_ps2_protocol
|
|
--enable_view_protocol
|
|
|
|
--echo #
|
|
--echo # Test index read optimization with ORDER BY as sub part of GROUP BY
|
|
--echo #
|
|
explain select a, b, count(*)
|
|
from t1
|
|
group by a, b
|
|
order by a;
|
|
|
|
explain select a, b, count(*)
|
|
from t1
|
|
group by a, b
|
|
order by a
|
|
fetch first 1 rows with ties;
|
|
|
|
--echo #
|
|
--echo # Test FETCH ... WITH TIES / ONLY with prepared statements
|
|
--echo #
|
|
|
|
prepare s from "select a, b from t1 order by a fetch first ? rows with ties";
|
|
|
|
set @a='qwe';
|
|
execute s using @a;
|
|
|
|
set @a=-1;
|
|
--error ER_WRONG_ARGUMENTS
|
|
execute s using @a;
|
|
|
|
prepare s2 from "select a, b from t1 order by a offset ? rows fetch first ? rows with ties";
|
|
set @offset=1;
|
|
set @limit_count= 2;
|
|
execute s2 using @offset, @limit_count;
|
|
|
|
execute s2 using @offset, 3;
|
|
|
|
--error ER_WRONG_ARGUMENTS
|
|
execute s2 using -1, 3;
|
|
|
|
--echo #
|
|
--echo # Test with ties with a constant order by.
|
|
--echo #
|
|
select a, b
|
|
from t1
|
|
order by 'x'
|
|
fetch first 2 rows with ties;
|
|
|
|
select b, sum(a)
|
|
from t1
|
|
group by b
|
|
order by 'x', 'y', 'z'
|
|
fetch first 2 rows with ties;
|
|
|
|
--echo #
|
|
--echo # Test with ties when group by is used and order by gets eliminated.
|
|
--echo #
|
|
--sorted_result
|
|
select b, sum(a)
|
|
from t1
|
|
group by b
|
|
order by 'x', 'y', 'z'
|
|
fetch first 2 rows with ties;
|
|
|
|
--echo #
|
|
--echo # Test an ill formed SELECT using MySQL/MariaDBs group by extension
|
|
--echo # of not requiring all non-aggregate fields to be part of group
|
|
--echo # by.
|
|
--echo #
|
|
--sorted_result
|
|
select b, a, sum(a)
|
|
from t1
|
|
group by a
|
|
order by b, 'x', 'y', 'z'
|
|
fetch first 2 rows with ties;
|
|
|
|
delete from t1;
|
|
insert into t1 values (1,1), (1,2), (1,3), (2,1), (2,2), (3,2), (3, 3);
|
|
|
|
--sorted_result
|
|
select b, a, sum(a)
|
|
from t1
|
|
group by a
|
|
order by b, 'x', 'y', 'z'
|
|
fetch first 1 rows with ties;
|
|
|
|
--sorted_result
|
|
select b, a, sum(a)
|
|
from t1
|
|
group by a
|
|
order by 'x', b, 'x', 'y', 'z'
|
|
fetch first 1 rows with ties;
|
|
|
|
--sorted_result
|
|
select b, a, sum(a)
|
|
from t1
|
|
group by a
|
|
order by 'x', 'y', b
|
|
fetch first 1 rows with ties;
|
|
|
|
|
|
--echo #
|
|
--echo # Test with ties when order by a const column is used.
|
|
--echo #
|
|
select a, b
|
|
from t1
|
|
order by a, 'a'
|
|
fetch first 2 rows with ties;
|
|
|
|
--echo #
|
|
--echo # Order by aggregates.
|
|
--echo #
|
|
--sorted_result
|
|
select a, b, sum(a)
|
|
from t1
|
|
group by a, b
|
|
order by sum(a)
|
|
fetch first 1 rows with ties;
|
|
|
|
--echo # Test different sum function in order by, not present in select list.
|
|
--sorted_result
|
|
select a, b, sum(a)
|
|
from t1
|
|
group by a, b
|
|
order by sum(b)
|
|
fetch first 1 rows with ties;
|
|
|
|
--echo # Test aggregates with constant columns in order by.
|
|
--sorted_result
|
|
select a, b, sum(a)
|
|
from t1
|
|
group by a, b
|
|
order by a
|
|
fetch first 1 rows with ties;
|
|
|
|
select a, b, sum(a)
|
|
from t1
|
|
group by a, b
|
|
order by a, 'x'
|
|
fetch first 1 rows with ties;
|
|
|
|
select a, b, sum(a)
|
|
from t1
|
|
group by a, b
|
|
order by a, b
|
|
fetch first 1 rows with ties;
|
|
|
|
select a, b, sum(a)
|
|
from t1
|
|
group by a, b
|
|
order by a, b
|
|
fetch first 2 rows with ties;
|
|
|
|
select a, b, sum(a)
|
|
from t1
|
|
group by a, b
|
|
order by a, b, 'x'
|
|
fetch first 1 rows with ties;
|
|
|
|
select a, b, sum(a)
|
|
from t1
|
|
group by a, 'x', b
|
|
order by a, b, 'x'
|
|
fetch first 1 rows with ties;
|
|
|
|
select a, b, sum(a)
|
|
from t1
|
|
group by 'x', a, b
|
|
order by a, b, 'x'
|
|
fetch first 1 rows with ties;
|
|
|
|
|
|
select a, b, sum(a)
|
|
from t1
|
|
group by a, b
|
|
order by 'x', a, 'x', b, 't', b
|
|
fetch first 1 rows with ties;
|
|
|
|
--sorted_result
|
|
select a, b, sum(a)
|
|
from t1
|
|
group by a, b
|
|
order by b, 't', b
|
|
fetch first 1 rows with ties;
|
|
|
|
--echo #
|
|
--echo # Test with subqueries in order by
|
|
--echo #
|
|
select a, b, sum(a)
|
|
from t1
|
|
group by a, b
|
|
order by (select 1)
|
|
fetch first 1 rows with ties;
|
|
|
|
select a, b, sum(a)
|
|
from t1
|
|
group by a, b
|
|
order by (select 1), a
|
|
fetch first 1 rows with ties;
|
|
|
|
select a, b, sum(a)
|
|
from t1
|
|
group by a, b
|
|
order by (select 1), a
|
|
fetch first 1 rows with ties;
|
|
|
|
drop table t1;
|
|
|
|
--echo #
|
|
--echo # MDEV-25441
|
|
--echo # WITH TIES is not respected with SQL_BUFFER_RESULT and constant in ORDER BY
|
|
--echo #
|
|
CREATE TABLE t1 (a INT);
|
|
INSERT INTO t1 values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
|
|
|
|
explain SELECT SQL_BUFFER_RESULT 1 AS f FROM t1 ORDER BY f FETCH NEXT 2 ROW WITH TIES;
|
|
SELECT SQL_BUFFER_RESULT 1 AS f FROM t1 ORDER BY f FETCH NEXT 2 ROW WITH TIES;
|
|
SELECT 1 AS f FROM t1 ORDER BY f FETCH NEXT 2 ROW WITH TIES;
|
|
|
|
drop table t1;
|
|
|
|
--echo #
|
|
--echo # MDEV-25481: Memory leak upon query WITH TIES involving a blob
|
|
--echo #
|
|
CREATE TABLE t (a TEXT);
|
|
INSERT INTO t VALUES ('foo'),('bar');
|
|
SELECT a FROM t ORDER BY a FETCH FIRST 2 ROWS WITH TIES;
|
|
|
|
# Cleanup
|
|
DROP TABLE t;
|
|
|
|
--echo #
|
|
--echo # MDEV-30324: Wrong result upon SELECT DISTINCT .. WITH TIES using index
|
|
--echo #
|
|
CREATE TABLE t1 (a int, b char(3), KEY (a));
|
|
INSERT INTO t1 VALUES (2,'foo'),(3,'bar'),(3,'bar'),(3,'zzz');
|
|
|
|
EXPLAIN SELECT DISTINCT a, b FROM t1 ORDER BY a FETCH FIRST 1 ROWS WITH TIES;
|
|
--sorted_result
|
|
SELECT DISTINCT a, b FROM t1 ORDER BY a FETCH FIRST 1 ROWS WITH TIES;
|
|
EXPLAIN SELECT DISTINCT a, b FROM t1 ORDER BY a FETCH FIRST 2 ROWS WITH TIES;
|
|
--sorted_result
|
|
SELECT DISTINCT a, b FROM t1 ORDER BY a FETCH FIRST 2 ROWS WITH TIES;
|
|
EXPLAIN SELECT DISTINCT a, b FROM t1 ORDER BY a FETCH FIRST 3 ROWS WITH TIES;
|
|
--sorted_result
|
|
SELECT DISTINCT a, b FROM t1 ORDER BY a FETCH FIRST 3 ROWS WITH TIES;
|
|
|
|
# Cleanup
|
|
DROP TABLE t1;
|