[#MDEV-21044] Wrong result when using a smaller size for sort buffer

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

[MDEV-21044] Wrong result when using a smaller size for sort buffer Created: 2019-11-13 Updated: 2019-11-19 Resolved: 2019-11-18
Status:	Closed
Project:	MariaDB Server
Component/s:	Optimizer
Affects Version/s:	10.1, 10.2, 10.3, 10.4
Fix Version/s:	10.1.44, 10.2.30, 10.3.21, 10.4.11