[Maria-discuss] MariaDB 10.4: subtle change of result ordering
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>
‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐ On Friday, August 21, 2020 12:18 PM, Reindl Harald <h.reindl@thelounge.net> 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
I would say that the actual ordering of the records below obtained by the select command shown is undefined. I can very well change for whatever subtle change in code. AFAIU, if you need repeatable results, you need to write a select command with a deterministic result. If this test has been presenting repeatable results for a long time, that's just chance. But that's just the opinion of a MariaDB user. I'm no developer. I would like to know the opinion of the developers. Regards, Rodrigo Severo
Am 21.08.20 um 17:44 schrieb Rodrigo Severo:
‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐ On Friday, August 21, 2020 12:18 PM, Reindl Harald <h.reindl@thelounge.net> 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
I would say that the actual ordering of the records below obtained by the select command shown is undefined. I can very well change for whatever subtle change in code.
AFAIU, if you need repeatable results, you need to write a select command with a deterministic result.
If this test has been presenting repeatable results for a long time, that's just chance.
But that's just the opinion of a MariaDB user. I'm no developer. I would like to know the opinion of the developers.
without explicit ordering the behavior of MyISAM is that you get results in der ordering the are created and so 'desc' can be and was deterministic you can even "later table" with ordering MyISAM tables so that the records are phyisally stored in the ordering they are typically fetched for the workload
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@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org
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>
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@thelounge.net', '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>
Hi, Reindl! 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? 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 On Aug 21, Reindl Harald wrote:
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
...
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>
Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org
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
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
On Aug 21, Reindl Harald wrote:
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
...
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>
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@mariadb.org
participants (3)
-
Reindl Harald -
Rodrigo Severo -
Sergei Golubchik