Details
Description
DATASET
create table t1(a varchar(255), b varchar(255), c varchar(255),d int); |
insert into t1 values ("jkl","mno", "pqr",1),("abc","def","ghi",2); |
insert into t1 values ("jkl","mno", "pqr",1),("abc","def","ghi",2); |
insert into t1 values ("jkl","mno", "pqr",1),("abc","def","ghi",2); |
insert into t1 values ("jkl","mno", "pqr",1),("abc","def","ghi",2); |
insert into t1 values ("jkl","mno", "pqr",1),("abc","def","ghi",2); |
insert into t1 values ("jkl","mno", "pqr",1),("abc","def","ghi",2); |
insert into t1 values ("jkl","mno", "pqr",1),("abc","def","ghi",2); |
insert into t1 values ("jkl","mno", "pqr",1),("abc","def","ghi",2); |
MariaDB [test]> select * from t1 order by d;
|
+------+------+------+------+
|
| a | b | c | d |
|
+------+------+------+------+
|
| jkl | mno | pqr | 1 |
|
| jkl | mno | pqr | 1 |
|
| jkl | mno | pqr | 1 |
|
| jkl | mno | pqr | 1 |
|
| jkl | mno | pqr | 1 |
|
| jkl | mno | pqr | 1 |
|
| jkl | mno | pqr | 1 |
|
| jkl | mno | pqr | 1 |
|
| abc | def | ghi | 2 |
|
| abc | def | ghi | 2 |
|
| abc | def | ghi | 2 |
|
| abc | def | ghi | 2 |
|
| abc | def | ghi | 2 |
|
| abc | def | ghi | 2 |
|
| abc | def | ghi | 2 |
|
| abc | def | ghi | 2 |
|
+------+------+------+------+
|
16 rows in set (0.01 sec)
|
|
MariaDB [test]> set sort_buffer_size=1024;
|
Query OK, 0 rows affected (0.00 sec)
|
|
MariaDB [test]> select * from t1 order by d;
|
+------+------+------+------+
|
| a | b | c | d |
|
+------+------+------+------+
|
| jkl | mno | pqr | 1 |
|
| jkl | mno | pqr | 1 |
|
| jkl | mno | pqr | 1 |
|
| jkl | mno | pqr | 1 |
|
| jkl | mno | pqr | 1 |
|
| jkl | mno | pqr | 1 |
|
| jkl | mno | pqr | 1 |
|
| jkl | mno | pqr | 1 |
|
| abc | def | ghi | 2 |
|
| abc | def | ghi | 2 |
|
| abc | def | ghi | 2 |
|
| abc | def | ghi | 2 |
|
| abc | def | ghi | 2 |
|
| abc | def | ghi | 2 |
|
+------+------+------+------+
|
14 rows in set (0.00 sec)
|
|
With smaller sort_buffer_size we get incorrect results, actual expected rows is 16