|
Still happens:
http://buildbot.askmonty.org/buildbot/builders/kvm-zyp-opensuse423-amd64/builds/370
|
|
I've got main.order_by_optimizer_innodb failed with current 10.4 from GitHub today on Fedora 27 when running the entire --suite=main, then several attempts to run just this test succeeded.
|
|
Still fails..
|
|
All branches
|
|
http://buildbot.askmonty.org/buildbot/builders/kvm-fulltest/builds/28480/steps/mtr_nm/logs/stdio
|
10.5 baddbaa0e328396d70d75c2c30e52fa3bd9e91b9 and a minor recovery change
|
CURRENT_TEST: main.order_by_optimizer_innodb
|
--- /mnt/buildbot/build/mariadb-10.5.10/mysql-test/main/order_by_optimizer_innodb.result 2021-03-10 01:20:13.000000000 -0500
|
+++ /mnt/buildbot/build/mariadb-10.5.10/mysql-test/main/order_by_optimizer_innodb.reject 2021-03-10 02:21:28.103497189 -0500
|
@@ -40,7 +40,7 @@
|
# The following should use range(ux_pk1_fd5), two key parts (key_len=5+8=13)
|
EXPLAIN SELECT * FROM t2 USE INDEX(ux_pk1_fd5) WHERE pk1=9 AND fd5 < 500 ORDER BY fd5 DESC LIMIT 10;
|
id select_type table type possible_keys key key_len ref rows Extra
|
-1 SIMPLE t2 range ux_pk1_fd5 ux_pk1_fd5 13 NULL 138 Using where
|
+1 SIMPLE t2 ref ux_pk1_fd5 ux_pk1_fd5 4 const 138 Using where
|
# This also must use range, not ref. key_len must be 13
|
EXPLAIN SELECT * FROM t2 WHERE pk1=9 AND fd5 < 500 ORDER BY fd5 DESC LIMIT 10;
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
|
Very similar thing happened on armhf builder in Ubuntu Launchpad:
main.order_by_innodb 'innodb' w3 [ fail ]
|
Test ended at 2023-02-09 05:32:47
|
|
CURRENT_TEST: main.order_by_innodb
|
--- /<<PKGBUILDDIR>>/mysql-test/main/order_by_innodb.result 2022-11-14 18:10:21.000000000 +0000
|
+++ /<<PKGBUILDDIR>>/mysql-test/main/order_by_innodb.reject 2023-02-09 05:32:46.693080266 +0000
|
@@ -250,7 +250,7 @@
|
id select_type table type possible_keys key key_len ref rows Extra
|
1 PRIMARY t1 index NULL PRIMARY 4 NULL # Using index
|
1 PRIMARY t2 eq_ref PRIMARY,id2 PRIMARY 4 func # Using where
|
-2 DEPENDENT SUBQUERY dd range id2,for_latest_sort for_latest_sort 6 NULL # Using where
|
+2 DEPENDENT SUBQUERY dd ref id2,for_latest_sort id2 4 test.t1.id # Using where; Using filesort
|
drop table t1,t2,t3;
|
# End of 10.2 tests
|
https://launchpadlibrarian.net/650538095/buildlog_ubuntu-lunar-armhf.mariadb_1%3A10.11.1-4~ubuntu23.04.1~1675916227.55de3ae0905.1030604.autopkgtest.migration_BUILDING.txt.gz
I also saw this on mipsel and mipsel64 builders on Debian buildd, details in https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=1029164
|
|
Relater MR from Daniel Black to trace test: https://salsa.debian.org/mariadb-team/mariadb-server/-/merge_requests/35
|
|
Ok I've modified the test like so
diff --git a/mysql-test/main/order_by_optimizer_innodb.test b/mysql-test/main/order_by_optimizer_innodb.test
|
index 33f67e522ad..0f0d201066d 100644
|
--- a/mysql-test/main/order_by_optimizer_innodb.test
|
+++ b/mysql-test/main/order_by_optimizer_innodb.test
|
@@ -6,6 +6,8 @@ SET GLOBAL innodb_stats_persistent=OFF;
|
--echo #
|
--echo # MDEV-6402: Optimizer doesn't choose best execution plan when composite key is used
|
--echo #
|
+set optimizer_trace=1;
|
+--system rm -rf $MYSQLTEST_VARDIR/mysqld.1/data/test/abcde.out
|
create table t0(a int);
|
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
|
|
@@ -33,9 +35,10 @@ from
|
t1;
|
|
select pk1, count(*) from t2 group by pk1;
|
-
|
--echo # The following should use range(ux_pk1_fd5), two key parts (key_len=5+8=13)
|
EXPLAIN SELECT * FROM t2 USE INDEX(ux_pk1_fd5) WHERE pk1=9 AND fd5 < 500 ORDER BY fd5 DESC LIMIT 10;
|
+
|
+select * from information_schema.optimizer_trace into outfile 'abcde.out';
|
--echo # This also must use range, not ref. key_len must be 13
|
EXPLAIN SELECT * FROM t2 WHERE pk1=9 AND fd5 < 500 ORDER BY fd5 DESC LIMIT 10;
|
|
and was able to observe with --repeat and many workers:
CURRENT_TEST: main.order_by_optimizer_innodb
|
--- /optane/dev-git2/10.6-dbg/mysql-test/main/order_by_optimizer_innodb.result 2023-10-05 15:47:34.965999548 +0300
|
+++ /optane/dev-git2/10.6-dbg/mysql-test/main/order_by_optimizer_innodb.reject 2023-10-05 15:47:53.166642829 +0300
|
@@ -42,7 +42,7 @@
|
# The following should use range(ux_pk1_fd5), two key parts (key_len=5+8=13)
|
EXPLAIN SELECT * FROM t2 USE INDEX(ux_pk1_fd5) WHERE pk1=9 AND fd5 < 500 ORDER BY fd5 DESC LIMIT 10;
|
id select_type table type possible_keys key key_len ref rows Extra
|
-1 SIMPLE t2 range ux_pk1_fd5 ux_pk1_fd5 13 NULL 138 Using where
|
+1 SIMPLE t2 ref ux_pk1_fd5 ux_pk1_fd5 4 const 138 Using where
|
select * from information_schema.optimizer_trace into outfile 'abcde.out';
|
Warnings:
|
Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead
|
|
|
diffing the traces:
diff -u good-trace.json bad-trace.json
|
--- good-trace.json 2023-10-05 16:25:05.627062941 +0300
|
+++ bad-trace.json 2023-10-05 16:25:12.099279316 +0300
|
@@ -60,8 +60,8 @@
|
"table": "t2",
|
"range_analysis": {
|
"table_scan": {
|
- "rows": 1034,
|
- "cost": 237.8
|
+ "rows": 650,
|
+ "cost": 162
|
},
|
"potential_range_indexes": [
|
{
|
@@ -86,7 +86,8 @@
|
"index_only": false,
|
"rows": 138,
|
"cost": 165.7803738,
|
- "chosen": true
|
+ "chosen": false,
|
+ "cause": "cost"
|
}
|
],
|
"analyzing_roworder_intersect": {
|
@@ -97,17 +98,6 @@
|
"group_index_range": {
|
"chosen": false,
|
"cause": "no group by or distinct"
|
- },
|
- "chosen_range_access_summary": {
|
- "range_access_plan": {
|
- "type": "range_scan",
|
- "index": "ux_pk1_fd5",
|
- "rows": 138,
|
- "ranges": ["(9,NULL) < (pk1,fd5) < (9,500)"]
|
- },
|
- "rows_for_plan": 138,
|
- "cost_for_plan": 165.7803738,
|
- "chosen": true
|
}
|
}
|
},
|
|
|
Ok, in bad-trace.json, InnoDB reports rows:650 instead of normal 1034.
Then, range scan on ux_pk1_fd5 is not picked.
|
|
@@ -181,16 +171,11 @@
|
},
|
{
|
"attaching_conditions_to_tables": {
|
- "attached_conditions_computation": [
|
- {
|
- "ref_to_range": true,
|
- "cause": "range uses longer key"
|
- }
|
- ],
|
+ "attached_conditions_computation": [],
|
"attached_conditions_summary": [
|
{
|
"table": "t2",
|
- "attached": "t2.pk1 = 9 and t2.fd5 < 500"
|
+ "attached": "t2.fd5 < 500"
|
}
|
]
|
}
|
then, range_uses_longer_key is not applied because we don't have a range plan.
|
|
FYI, latest run of MariaDB 10.11.5 on Lanchpad armhf builder passed fine:
main.order_by_innodb 'innodb' w1 [ pass ] 773
|
Log: https://launchpadlibrarian.net/690710785/buildlog_ubuntu-mantic-armhf.mariadb_1%3A10.11.5-2~bpo23.10.1~1696611906.bd60149118c.feature.re.enable.all.tests_BUILDING.txt.gz
|