[MDEV-30875] main.analyze_stmt_orderby/rowid_filter_innodb/explain_json_format_partitions (+ 7 more) fails on Launchpad armhf builders Created: 2023-03-19  Updated: 2023-11-30  Resolved: 2023-10-09

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 11.0
Fix Version/s: 11.1.1, 10.11.3, 11.0.2

Type: Bug Priority: Major
Reporter: Otto Kekäläinen Assignee: Unassigned
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Problem/Incident
is caused by MDEV-31558 Add InnoDB engine information to the ... Closed
Relates
relates to MDEV-30411 main.explain_json_format_partitions f... Closed
relates to MDEV-32375 Multiple optimizer related tests mism... Open

 Description   

While testing the health of the 11.0 branch I noticed that the armhf build on Launchpad fails with:

main.analyze_stmt_orderby 'innodb'       w4 [ fail ]
 
        Test ended at 2023-03-19 06:09:29
 
 
 
CURRENT_TEST: main.analyze_stmt_orderby
 
--- /<<PKGBUILDDIR>>/mysql-test/main/analyze_stmt_orderby.result	2021-06-18 13:08:00.000000000 +0000
 
+++ /<<PKGBUILDDIR>>/mysql-test/main/analyze_stmt_orderby.reject	2023-03-19 06:09:29.316720643 +0000
 
@@ -35,15 +35,10 @@
 
 update t2 set b=b+1 order by b limit 5;
 
 ANALYZE
 
 {
 
-  "query_optimization": {
 
-    "r_total_time_ms": "REPLACED"
 
-  },
 
   "query_block": {
 
     "select_id": 1,
 
-    "r_total_time_ms": "REPLACED",
 
     "filesort": {
 
       "r_loops": 1,
 
-      "r_total_time_ms": "REPLACED",
 
       "r_limit": 5,
 
       "r_used_priority_queue": true,
 
       "r_output_rows": 6,
 
@@ -54,8 +49,7 @@
 
         "access_type": "ALL",
 
         "rows": 10000,
 
         "r_rows": 10000,
 
-        "r_filtered": 100,
 
-        "r_total_time_ms": "REPLACED"
 
+        "r_filtered": 100
 
       }
 
     }
 
   }
 
@@ -92,12 +86,8 @@
 
 update t2 set a=a+1 where a<10;
 
 ANALYZE
 
 {
 
-  "query_optimization": {
 
-    "r_total_time_ms": "REPLACED"
 
-  },
 
   "query_block": {
 
     "select_id": 1,
 
-    "r_total_time_ms": "REPLACED",
 
     "buffer": {
 
       "table": {
 
         "update": 1,
 
@@ -110,7 +100,6 @@
 
         "rows": 9,
 
         "r_rows": 10,
 
         "r_filtered": 100,
 
-        "r_total_time_ms": "REPLACED",
 
         "attached_condition": "t2.a < 10"
 
       }
 
     }
 
@@ -143,15 +132,10 @@
 
 delete from t2 order by b limit 5;
 
 ANALYZE
 
 {
 
-  "query_optimization": {
 
-    "r_total_time_ms": "REPLACED"
 
-  },
 
   "query_block": {
 
     "select_id": 1,
 
-    "r_total_time_ms": "REPLACED",
 
     "filesort": {
 
       "r_loops": 1,
 
-      "r_total_time_ms": "REPLACED",
 
       "r_used_priority_queue": false,
 
       "r_output_rows": 10000,
 
       "r_buffer_size": "REPLACED",
 
@@ -162,8 +146,7 @@
 
         "access_type": "ALL",
 
         "rows": 10000,
 
         "r_rows": 10000,
 
-        "r_filtered": 100,
 
-        "r_total_time_ms": "REPLACED"
 
+        "r_filtered": 100
 
       }
 
     }
 
   }
 
@@ -222,18 +205,13 @@
 
 select * from t0,t2 where t2.a=t0.a order by t2.b limit 4;
 
 ANALYZE
 
 {
 
-  "query_optimization": {
 
-    "r_total_time_ms": "REPLACED"
 
-  },
 
   "query_block": {
 
     "select_id": 1,
 
     "cost": "REPLACED",
 
     "r_loops": 1,
 
-    "r_total_time_ms": "REPLACED",
 
     "filesort": {
 
       "sort_key": "t2.b",
 
       "r_loops": 1,
 
-      "r_total_time_ms": "REPLACED",
 
       "r_limit": 4,
 
       "r_used_priority_queue": true,
 
       "r_output_rows": 4,
 
@@ -335,14 +313,10 @@
 
 select * from t0,t2 where t2.a=t0.a order by t0.a limit 4;
 
 ANALYZE
 
 {
 
-  "query_optimization": {
 
-    "r_total_time_ms": "REPLACED"
 
-  },
 
   "query_block": {
 
     "select_id": 1,
 
     "cost": "REPLACED",
 
     "r_loops": 1,
 
-    "r_total_time_ms": "REPLACED",
 
     "nested_loop": [
 
       {
 
         "read_sorted_file": {
 
@@ -350,7 +324,6 @@
 
           "filesort": {
 
             "sort_key": "t0.a",
 
             "r_loops": 1,
 
-            "r_total_time_ms": "REPLACED",
 
             "r_used_priority_queue": false,
 
             "r_output_rows": 10,
 
             "r_buffer_size": "REPLACED",
 
@@ -411,18 +384,13 @@
 
 select MAX(b) from t2 where mod(a,2)=0 group by c;
 
 ANALYZE
 
 {
 
-  "query_optimization": {
 
-    "r_total_time_ms": "REPLACED"
 
-  },
 
   "query_block": {
 
     "select_id": 1,
 
     "cost": "REPLACED",
 
     "r_loops": 1,
 
-    "r_total_time_ms": "REPLACED",
 
     "filesort": {
 
       "sort_key": "t2.c",
 
       "r_loops": 1,
 
-      "r_total_time_ms": "REPLACED",
 
       "r_used_priority_queue": false,
 
       "r_output_rows": 10,
 
       "r_buffer_size": "REPLACED",
 
@@ -477,14 +445,10 @@
 
 select distinct max(t3.b) Q from t0, t3 where t0.a=t3.a group by t0.a order by null;
 
 ANALYZE
 
 {
 
-  "query_optimization": {
 
-    "r_total_time_ms": "REPLACED"
 
-  },
 
   "query_block": {
 
     "select_id": 1,
 
     "cost": "REPLACED",
 
     "r_loops": 1,
 
-    "r_total_time_ms": "REPLACED",
 
     "duplicate_removal": {
 
       "temporary_table": {
 
         "nested_loop": [
 
@@ -550,18 +514,13 @@
 
 group by t5.a order by sum limit 1;
 
 ANALYZE
 
 {
 
-  "query_optimization": {
 
-    "r_total_time_ms": "REPLACED"
 
-  },
 
   "query_block": {
 
     "select_id": 1,
 
     "cost": "REPLACED",
 
     "r_loops": 1,
 
-    "r_total_time_ms": "REPLACED",
 
     "filesort": {
 
       "sort_key": "count(distinct t5.b)",
 
       "r_loops": 1,
 
-      "r_total_time_ms": "REPLACED",
 
       "r_limit": 1,
 
       "r_used_priority_queue": true,
 
       "r_output_rows": 2,
 
@@ -570,7 +529,6 @@
 
         "filesort": {
 
           "sort_key": "t5.a",
 
           "r_loops": 1,
 
-          "r_total_time_ms": "REPLACED",
 
           "r_used_priority_queue": false,
 
           "r_output_rows": 6,
 
           "r_buffer_size": "REPLACED",
 
@@ -699,14 +657,10 @@
 
 select col1 f1, col2 f2, col1 f3 from t2 group by f1;
 
 ANALYZE
 
 {
 
-  "query_optimization": {
 
-    "r_total_time_ms": "REPLACED"
 
-  },
 
   "query_block": {
 
     "select_id": 1,
 
     "cost": "REPLACED",
 
     "r_loops": 1,
 
-    "r_total_time_ms": "REPLACED",
 
     "nested_loop": [
 
       {
 
         "table": {
 
 
 
mysqltest: Result length mismatch
 
 
 
main.rowid_filter_innodb 'innodb'        w1 [ fail ]
 
        Test ended at 2023-03-19 06:09:35
 
 
 
CURRENT_TEST: main.rowid_filter_innodb
 
--- /<<PKGBUILDDIR>>/mysql-test/main/rowid_filter_innodb.result	2021-06-18 13:08:00.000000000 +0000
 
+++ /<<PKGBUILDDIR>>/mysql-test/main/rowid_filter_innodb.reject	2023-03-19 06:09:34.560745151 +0000
 
@@ -119,14 +119,10 @@
 
 l_quantity > 47;
 
 ANALYZE
 
 {
 
-  "query_optimization": {
 
-    "r_total_time_ms": "REPLACED"
 
-  },
 
   "query_block": {
 
     "select_id": 1,
 
     "cost": "REPLACED",
 
     "r_loops": 1,
 
-    "r_total_time_ms": "REPLACED",
 
     "nested_loop": [
 
       {
 
         "table": {
 
@@ -246,14 +242,10 @@
 
 l_quantity > 47;
 
 ANALYZE
 
 {
 
-  "query_optimization": {
 
-    "r_total_time_ms": "REPLACED"
 
-  },
 
   "query_block": {
 
     "select_id": 1,
 
     "cost": "REPLACED",
 
     "r_loops": 1,
 
-    "r_total_time_ms": "REPLACED",
 
     "nested_loop": [
 
       {
 
         "table": {
 
@@ -387,14 +379,10 @@
 
 o_totalprice between 200000 and 230000;
 
 ANALYZE
 
 {
 
-  "query_optimization": {
 
-    "r_total_time_ms": "REPLACED"
 
-  },
 
   "query_block": {
 
     "select_id": 1,
 
     "cost": "REPLACED",
 
     "r_loops": 1,
 
-    "r_total_time_ms": "REPLACED",
 
     "nested_loop": [
 
       {
 
         "table": {
 
@@ -533,14 +521,10 @@
 
 o_totalprice between 200000 and 230000;
 
 ANALYZE
 
 {
 
-  "query_optimization": {
 
-    "r_total_time_ms": "REPLACED"
 
-  },
 
   "query_block": {
 
     "select_id": 1,
 
     "cost": "REPLACED",
 
     "r_loops": 1,
 
-    "r_total_time_ms": "REPLACED",
 
     "nested_loop": [
 
       {
 
         "table": {
 
@@ -692,14 +676,10 @@
 
 o_totalprice between 180000 and 230000;
 
 ANALYZE
 
 {
 
-  "query_optimization": {
 
-    "r_total_time_ms": "REPLACED"
 
-  },
 
   "query_block": {
 
     "select_id": 1,
 
     "cost": "REPLACED",
 
     "r_loops": 1,
 
-    "r_total_time_ms": "REPLACED",
 
     "nested_loop": [
 
       {
 
         "table": {
 
@@ -855,14 +835,10 @@
 
 o_totalprice between 180000 and 230000;
 
 ANALYZE
 
 {
 
-  "query_optimization": {
 
-    "r_total_time_ms": "REPLACED"
 
-  },
 
   "query_block": {
 
     "select_id": 1,
 
     "cost": "REPLACED",
 
     "r_loops": 1,
 
-    "r_total_time_ms": "REPLACED",
 
     "nested_loop": [
 
       {
 
         "table": {
 
@@ -1008,14 +984,10 @@
 
 o_totalprice between 200000 and 230000;
 
 ANALYZE
 
 {
 
-  "query_optimization": {
 
-    "r_total_time_ms": "REPLACED"
 
-  },
 
   "query_block": {
 
     "select_id": 1,
 
     "cost": "REPLACED",
 
     "r_loops": 1,
 
-    "r_total_time_ms": "REPLACED",
 
     "nested_loop": [
 
       {
 
         "table": {
 
@@ -1179,14 +1151,10 @@
 
 o_totalprice between 200000 and 230000;
 
 ANALYZE
 
 {
 
-  "query_optimization": {
 
-    "r_total_time_ms": "REPLACED"
 
-  },
 
   "query_block": {
 
     "select_id": 1,
 
     "cost": "REPLACED",
 
     "r_loops": 1,
 
-    "r_total_time_ms": "REPLACED",
 
     "nested_loop": [
 
       {
 
         "table": {
 
@@ -1366,14 +1334,10 @@
 
 o_totalprice BETWEEN 200000 AND 250000;
 
 ANALYZE
 
 {
 
-  "query_optimization": {
 
-    "r_total_time_ms": "REPLACED"
 
-  },
 
   "query_block": {
 
     "select_id": 1,
 
     "cost": "REPLACED",
 
     "r_loops": 1,
 
-    "r_total_time_ms": "REPLACED",
 
     "nested_loop": [
 
       {
 
         "table": {
 
@@ -1513,14 +1477,10 @@
 
 o_totalprice BETWEEN 200000 AND 250000;
 
 ANALYZE
 
 {
 
-  "query_optimization": {
 
-    "r_total_time_ms": "REPLACED"
 
-  },
 
   "query_block": {
 
     "select_id": 1,
 
     "cost": "REPLACED",
 
     "r_loops": 1,
 
-    "r_total_time_ms": "REPLACED",
 
     "nested_loop": [
 
       {
 
         "table": {
 
@@ -1666,14 +1626,10 @@
 
 l_shipdate BETWEEN '1996-10-01' AND '1996-12-01';
 
 ANALYZE
 
 {
 
-  "query_optimization": {
 
-    "r_total_time_ms": "REPLACED"
 
-  },
 
   "query_block": {
 
     "select_id": 1,
 
     "cost": "REPLACED",
 
     "r_loops": 1,
 
-    "r_total_time_ms": "REPLACED",
 
     "nested_loop": [
 
       {
 
         "table": {
 
@@ -1814,14 +1770,10 @@
 
 l_shipdate BETWEEN '1996-10-01' AND '1996-12-01';
 
 ANALYZE
 
 {
 
-  "query_optimization": {
 
-    "r_total_time_ms": "REPLACED"
 
-  },
 
   "query_block": {
 
     "select_id": 1,
 
     "cost": "REPLACED",
 
     "r_loops": 1,
 
-    "r_total_time_ms": "REPLACED",
 
     "nested_loop": [
 
       {
 
         "table": {
 
@@ -1970,14 +1922,10 @@
 
 l_shipdate BETWEEN '1996-10-01' AND '1996-12-01';
 
 ANALYZE
 
 {
 
-  "query_optimization": {
 
-    "r_total_time_ms": "REPLACED"
 
-  },
 
   "query_block": {
 
     "select_id": 1,
 
     "cost": "REPLACED",
 
     "r_loops": 1,
 
-    "r_total_time_ms": "REPLACED",
 
     "nested_loop": [
 
       {
 
         "table": {
 
@@ -2128,14 +2076,10 @@
 
 l_shipdate BETWEEN '1996-10-01' AND '1996-12-01';
 
 ANALYZE
 
 {
 
-  "query_optimization": {
 
-    "r_total_time_ms": "REPLACED"
 
-  },
 
   "query_block": {
 
     "select_id": 1,
 
     "cost": "REPLACED",
 
     "r_loops": 1,
 
-    "r_total_time_ms": "REPLACED",
 
     "nested_loop": [
 
       {
 
         "table": {
 
@@ -3273,14 +3217,10 @@
 
 8518228073041491534,-5420422472375069774);
 
 ANALYZE
 
 {
 
-  "query_optimization": {
 
-    "r_total_time_ms": "REPLACED"
 
-  },
 
   "query_block": {
 
     "select_id": 1,
 
     "cost": "REPLACED",
 
     "r_loops": 1,
 
-    "r_total_time_ms": "REPLACED",
 
     "nested_loop": [
 
       {
 
         "table": {
 
 
 
mysqltest: Result length mismatch
 
 
 
main.explain_json_format_partitions      w2 [ fail ]
 
        Test ended at 2023-03-19 06:10:32
 
 
 
CURRENT_TEST: main.explain_json_format_partitions
 
--- /<<PKGBUILDDIR>>/mysql-test/main/explain_json_format_partitions.result	2021-06-18 13:08:00.000000000 +0000
 
+++ /<<PKGBUILDDIR>>/mysql-test/main/explain_json_format_partitions.reject	2023-03-19 06:10:32.261024183 +0000
 
@@ -32,14 +32,10 @@
 
 analyze format=json select * from t1 where a in (2,3,4);
 
 ANALYZE
 
 {
 
-  "query_optimization": {
 
-    "r_total_time_ms": "REPLACED"
 
-  },
 
   "query_block": {
 
     "select_id": 1,
 
     "cost": "REPLACED",
 
     "r_loops": 1,
 
-    "r_total_time_ms": "REPLACED",
 
     "nested_loop": [
 
       {
 
         "table": {
 
@@ -64,12 +60,8 @@
 
 analyze format=json update t1 set a=a+10 where a in (2,3,4);
 
 ANALYZE
 
 {
 
-  "query_optimization": {
 
-    "r_total_time_ms": "REPLACED"
 
-  },
 
   "query_block": {
 
     "select_id": 1,
 
-    "r_total_time_ms": "REPLACED",
 
     "buffer": {
 
       "table": {
 
         "update": 1,
 
@@ -79,7 +71,6 @@
 
         "rows": 10,
 
         "r_rows": 10,
 
         "r_filtered": 30,
 
-        "r_total_time_ms": "REPLACED",
 
         "attached_condition": "t1.a in (2,3,4)"
 
       }
 
     }
 
@@ -88,12 +79,8 @@
 
 analyze format=json delete from t1 where a in (20,30,40);
 
 ANALYZE
 
 {
 
-  "query_optimization": {
 
-    "r_total_time_ms": "REPLACED"
 
-  },
 
   "query_block": {
 
     "select_id": 1,
 
-    "r_total_time_ms": "REPLACED",
 
     "table": {
 
       "delete": 1,
 
       "table_name": "t1",
 
@@ -102,7 +89,6 @@
 
       "rows": 10,
 
       "r_rows": 10,
 
       "r_filtered": 0,
 
-      "r_total_time_ms": "REPLACED",
 
       "attached_condition": "t1.a in (20,30,40)"
 
     }
 
   }
 
 
 
mysqltest: Result length mismatch
 
 
 
Too many failed: Failed 10/465 tests, 97.85% were successful.
 
 
 
Failing test(s): main.analyze_stmt_orderby main.rowid_filter_innodb main.explain_json_format_partitions main.cte_recursive main.order_by main.derived_cond_pushdown main.explain_json main.rowid_filter_myisam main.subselect4 main.except

Full log at https://launchpadlibrarian.net/656601231/buildlog_ubuntu-lunar-armhf.mariadb_1%3A11.0.0~ubuntu23.04.1~1679201288.c219501d4ea.ok.10.5.type.test_BUILDING.txt.gz

 Comments   
Comment by Otto Kekäläinen [ 2023-10-09 ]

This issue is very closely related to MDEV-30411, and seems fixed. This particular test is indeed passing in recent build https://launchpadlibrarian.net/690894963/buildlog_ubuntu-mantic-armhf.mariadb_1%3A10.11.5-2~bpo23.10.1~1696728966.194985422e6.debian.latest_BUILDING.txt.gz:

 

main.analyze_stmt_orderby 'innodb' w1 [ pass ] 304
{noformat]

Generated at Thu Feb 08 10:19:33 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.