maria-discuss team mailing list archive
-
maria-discuss team
-
Mailing list archive
-
Message #05900
Re: MariaDB 10.4: subtle change of result ordering
Am 24.08.20 um 09:46 schrieb Sergei Golubchik:
> 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?
it is a 10.3 to 10.4 interface, hence the 10.4 in the subject
> 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.
given that tjis is an autotest that table is *always* empty at the begin
of the autotest suite
> 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>
Follow ups
References
