maria-discuss team mailing list archive

[Reindl Harald <h.reindl@xxxxxxxxxxxxx>]

Am 24.08.20 um 12:23 schrieb Reindl Harald:
> 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

forgot the table structure

cms1_comments | CREATE TABLE `cms1_comments` (
  `kid` mediumint(7) unsigned NOT NULL AUTO_INCREMENT,
  `ksid` mediumint(7) unsigned NOT NULL DEFAULT 0,
  `ks2id` mediumint(7) unsigned NOT NULL DEFAULT 0,
  `ktimestamp` int(10) unsigned NOT NULL DEFAULT 0,
  `kip` varchar(50) COLLATE latin1_german1_ci NOT NULL,
  `kname` varchar(100) COLLATE latin1_german1_ci NOT NULL,
  `kherkunft` varchar(100) COLLATE latin1_german1_ci NOT NULL,
  `kemail` varchar(80) COLLATE latin1_german1_ci NOT NULL,
  `khomepage` varchar(255) COLLATE latin1_german1_ci NOT NULL,
  `kkommentar` text COLLATE latin1_german1_ci NOT NULL,
  `kaktiv` tinyint(1) unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY (`kid`),
  KEY `comment_key_1` (`ksid`,`kaktiv`,`ktimestamp`),
  KEY `comment_key_2` (`ks2id`,`kaktiv`,`ktimestamp`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci
PACK_KEYS=0 DELAY_KEY_WRITE=1

and thats how the test records are generated

$data_template =
[
 'ksid'       => 0,
 'ks2id'      => 0,
 'kname'      => 'CMS-Autotest [count]',
 'kemail'     => 'server-admins@xxxxxxxxxxxxx',
 'kkommentar' => trim(str_repeat('Test ', 10)),
 'kherkunft'  => 'Wien',
 'khomepage'  => 'www.thelounge.net',
 'kativ'      => 1,
];

for($count=1; $count<=3; $count++)
{
 $data = str_replace('[count]', $count, $data_template);
 $data['ksid'] = 1;
 $insert_id = $this->add($data);
 if(empty($insert_id))
 {
  $cl_autotests->trigger_error('check failed');
 }
}

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