maria-discuss team mailing list archive
-
maria-discuss team
-
Mailing list archive
-
Message #05899
Re: MariaDB 10.4: subtle change of result ordering
Hi, Reindl!
1. Is it 10.3 to 10.4 difference? There were some optimizations that
could have such an effect.
https://mariadb.com/kb/en/changes-improvements-in-mariadb-104/#optimizer
Can you share the table structure?
2. Technically, MyISAM only returns rows in the order of insertion if
you insert into an empty table. This was always the case, see this
simple test:
create table t1 (a int) engine=myisam;
insert t1 values (1),(2),(3),(4),(5); select * from t1;
delete from t1 where a<10; select * from t1;
insert t1 values (1),(2),(3),(4),(5); select * from t1;
drop table t1;
I don't think this is your case though.
On Aug 21, Reindl Harald wrote:
> MyISAM:
>
> in all previous versions this test was stable, so as the timestamps are
> identical and ordering is 'desc' the result was ordered by the creation
> time of the the records
>
> now it's reverse which can break all sort of expectations in subtle ways
>
> while we could discuss what is the expected output when order by
> 'ktimestamp' and all are identical it still worries me that something
> which didn't change over years and major versions now comes with the
> reverse ordering
>
> unless someone tells me that's the result of some real peformance
> optimization i would perfer the known result given that it's hard to
> know how much other code depends implicit on the previous behavior
>
> ---------------------------------------------
>
> select SQL_NO_CACHE SQL_CALC_FOUND_ROWS * from `cl_autotest_comments`
> where ksid=1 and kaktiv=1 order by ktimestamp desc limit 0,10
>
> ---------------------------------------------
>
> Array
> (
> [0] => Array
> (
> [kid] => 1
> [kaktiv] => 1
> [ksid] => 1
> [ks2id] => 0
> [ktimestamp] => 1598022502
> [kip] => 127.0.0.1
> [kname] => CMS-Autotest 1
> [kherkunft] => Wien
> [kkommentar] => Test Test Test Test Test Test Test Test Test
> Test
> )
>
> [1] => Array
> (
> [kid] => 2
> [kaktiv] => 1
> [ksid] => 1
> [ks2id] => 0
> [ktimestamp] => 1598022502
> [kip] => 127.0.0.1
> [kname] => CMS-Autotest 2
> [kherkunft] => Wien
> [kkommentar] => Test Test Test Test Test Test Test Test Test
> Test
> )
>
> [2] => Array
> (
> [kid] => 3
> [kaktiv] => 1
> [ksid] => 1
> [ks2id] => 0
> [ktimestamp] => 1598022502
> [kip] => 127.0.0.1
> [kname] => CMS-Autotest 3
> [kherkunft] => Wien
> [kkommentar] => Test Test Test Test Test Test Test Test Test
> Test
> )
>
> )
>
> ---------------------------------------------
>
> diff of the expected application output:
>
> -<html><head><title>Autotest</title></head><body><a
> name="commentpart"></a><div><a
> href="comments_add.php?s2id=1">Verfassen</a><table><tr><td>CMS-Autotest
> 1</td><td></td></tr><tr><td colspan="2">Test Test Test Test Test Test
> Test Test Test Test</td></tr></table><table><tr><td>CMS-Autotest
> 2</td><td></td></tr><tr><td colspan="2">Test Test Test Test Test Test
> Test Test Test Test</td></tr></table><table><tr><td>CMS-Autotest
> 3</td><td></td></tr><tr><td colspan="2">Test Test Test Test Test Test
> Test Test Test
> Test</td></tr></table><table><tr><td></td><td></td></tr></table></div></body>
>
> +<html><head><title>Autotest</title></head><body><a
> name="commentpart"></a><div><a
> href="comments_add.php?s2id=1">Verfassen</a><table><tr><td>CMS-Autotest
> 3</td><td></td></tr><tr><td colspan="2">Test Test Test Test Test Test
> Test Test Test Test</td></tr></table><table><tr><td>CMS-Autotest
> 2</td><td></td></tr><tr><td colspan="2">Test Test Test Test Test Test
> Test Test Test Test</td></tr></table><table><tr><td>CMS-Autotest
> 1</td><td></td></tr><tr><td colspan="2">Test Test Test Test Test Test
> Test Test Test
> Test</td></tr></table><table><tr><td></td><td></td></tr></table></div></body>
>
> _______________________________________________
> Mailing list: https://launchpad.net/~maria-discuss
> Post to : maria-discuss@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help : https://help.launchpad.net/ListHelp
Regards,
Sergei
VP of MariaDB Server Engineering
and security@xxxxxxxxxxx
Follow ups
References
