Details
Description
http://buildbot.askmonty.org/buildbot/builders/kvm-deb-sid-x86/builds/2000/steps/test_5/logs/stdio
main.order_by_optimizer_innodb 'xtradb' w1 [ fail ]
|
Test ended at 2016-07-03 04:07:35
|
|
|
CURRENT_TEST: main.order_by_optimizer_innodb
|
--- /usr/share/mysql/mysql-test/r/order_by_optimizer_innodb.result 2016-07-03 02:45:44.000000000 -0400
|
+++ /dev/shm/var/1/log/order_by_optimizer_innodb.reject 2016-07-03 04:07:35.159355291 -0400
|
@@ -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 137 Using where
|
+1 SIMPLE t2 ref ux_pk1_fd5 ux_pk1_fd5 4 const 137 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
|
|
|
mysqltest: Result length mismatch
|
Attachments
Issue Links
- relates to
-
MDEV-7069 Fix buildbot failures in main server trees
-
- Stalled
-
Activity
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
|
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.testindex 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.outcreate table t0(a int);insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);@@ -33,9 +35,10 @@ fromt1;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 13EXPLAIN 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 whereselect * from information_schema.optimizer_trace into outfile 'abcde.out';Warnings: