← Back to team overview

maria-discuss team mailing list archive

Re: MariaDB 10.4: subtle change of result ordering


Hi, Reindl!

On Aug 24, Reindl Harald wrote:
> Am 24.08.20 um 14:23 schrieb Sergei Golubchik:
> > Sorry, I couldn't understand, was 10.3 returning 3..2..1 and 10.4
> > started to return 1..2..3? Or was it the other way around?
> that at a bottom is a unified iff between expected output and what
> happens when trying 10.4.14
> 10.2 and 10.3 returning 1-2-3 for years
> 10.4.14 in the identical environemnt returns 3-2-1
> * they are created 1-2-3
> * timestamps identical because created in same second
> * 'desc' ordering by timestamp gave 1-2-3
> my issue is not that the result as such could be called wrong but that
> it changes after years, the autotests which is part of a bug testsuite
> has the expected HTML output base64 decoded, fires a diff against the
> now created html-output and alerts if there is a byte changed

Yes, I understand that.
But, as I just wrote:

> > I tried your table structure and your test data on both 10.3 and 10.5
> > and in both cases I've got 3..2..1

What happens in my tests - decides to use the comment_key_1 index.
It reads the index backwards, so it naturally gets 3-2-1.

If I delete this index from the CREATE TABLE statement, I get 1-2-3.

For me this happens identically both in 10.3 and 10.4
May be some changes to server defaults, like optimizer_switch or
use_stat_tables caused the optimizer to prefer a different plan in your

I suspect for a large table with lots of rows the plan will be the same
in 10.3 and 10.4, but for a small table when all plans have very similar
(and small) costs you can, probably, expect this kind of instability.

VP of MariaDB Server Engineering
and security@xxxxxxxxxxx