maria-discuss team mailing list archive

[Sergei Golubchik <serg@xxxxxxxxxxx>]

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
case.

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.

Regards,
Sergei
VP of MariaDB Server Engineering
and security@xxxxxxxxxxx