[MDEV-32375] Multiple optimizer related tests mismatch on architecture hppa Created: 2023-10-09  Updated: 2023-11-30

Status: Open
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

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

Issue Links:
Relates
relates to MDEV-30875 main.analyze_stmt_orderby/rowid_filte... Closed

 Description   

This issue is very similar to MDEV-30875 but for architecture hppa.

In the build log of 1:10.11.5-2 in Debian I saw several optimized related result mismatches: https://buildd.debian.org/status/fetch.php?pkg=mariadb&arch=hppa&ver=1%3A10.11.5-2&stamp=1696821455&raw=0

main.analyze_engine_stats 'innodb,slow_query_log_off' w4 [ fail ]
        Test ended at 2023-10-09 02:59:16
 
CURRENT_TEST: main.analyze_engine_stats
--- /<<PKGBUILDDIR>>/mysql-test/main/analyze_engine_stats.result	2023-08-11 06:35:40.000000000 +0000
+++ /<<PKGBUILDDIR>>/mysql-test/main/analyze_engine_stats.reject	2023-10-09 02:59:13.856059303 +0000
@@ -16,9 +16,6 @@
 select '$out' as X;
 X
 {
-  "query_optimization": {
-    "r_total_time_ms": "REPLACED"
-  },
   "query_block": {
     "select_id": 1,
     "r_loops": 1,
@@ -55,9 +52,6 @@
 select '$out' as X;
 X
 {
-  "query_optimization": {
-    "r_total_time_ms": "REPLACED"
-  },
   "query_block": {
     "select_id": 1,
     "r_total_time_ms": "REPLACED",
@@ -88,9 +82,6 @@
 select '$out' as X;
 X
 {
-  "query_optimization": {
-    "r_total_time_ms": "REPLACED"
-  },
   "query_block": {
     "select_id": 1,
     "r_total_time_ms": "REPLACED",
@@ -129,13 +120,9 @@
 ANALYZE FORMAT=JSON SELECT * FROM t1 WHERE a IN (SELECT s FROM t2);
 ANALYZE
 {
-  "query_optimization": {
-    "r_total_time_ms": "REPLACED"
-  },
   "query_block": {
     "select_id": 1,
     "r_loops": 1,
-    "r_total_time_ms": "REPLACED",
     "const_condition": "1",
     "nested_loop": [
       {
 
mysqltest: Result length mismatch
 
 
main.analyze_engine_stats 'innodb,slow_query_log_on' w2 [ fail ]
        Test ended at 2023-10-09 02:59:48
 
CURRENT_TEST: main.analyze_engine_stats
--- /<<PKGBUILDDIR>>/mysql-test/main/analyze_engine_stats.result	2023-08-11 06:35:40.000000000 +0000
+++ /<<PKGBUILDDIR>>/mysql-test/main/analyze_engine_stats.reject	2023-10-09 02:59:45.495776257 +0000
@@ -16,9 +16,6 @@
 select '$out' as X;
 X
 {
-  "query_optimization": {
-    "r_total_time_ms": "REPLACED"
-  },
   "query_block": {
     "select_id": 1,
     "r_loops": 1,
@@ -55,9 +52,6 @@
 select '$out' as X;
 X
 {
-  "query_optimization": {
-    "r_total_time_ms": "REPLACED"
-  },
   "query_block": {
     "select_id": 1,
     "r_total_time_ms": "REPLACED",
@@ -88,9 +82,6 @@
 select '$out' as X;
 X
 {
-  "query_optimization": {
-    "r_total_time_ms": "REPLACED"
-  },
   "query_block": {
     "select_id": 1,
     "r_total_time_ms": "REPLACED",
@@ -129,13 +120,9 @@
 ANALYZE FORMAT=JSON SELECT * FROM t1 WHERE a IN (SELECT s FROM t2);
 ANALYZE
 {
-  "query_optimization": {
-    "r_total_time_ms": "REPLACED"
-  },
   "query_block": {
     "select_id": 1,
     "r_loops": 1,
-    "r_total_time_ms": "REPLACED",
     "const_condition": "1",
     "nested_loop": [
       {
 
mysqltest: Result length mismatch
 
 
main.derived_split_innodb 'innodb'       w4 [ fail ]
        Test ended at 2023-10-09 03:00:18
 
CURRENT_TEST: main.derived_split_innodb
--- /<<PKGBUILDDIR>>/mysql-test/main/derived_split_innodb.result	2023-08-11 06:35:40.000000000 +0000
+++ /<<PKGBUILDDIR>>/mysql-test/main/derived_split_innodb.reject	2023-10-09 03:00:16.395499815 +0000
@@ -365,9 +365,6 @@
 group by grp_id) T on T.grp_id=t1.b;
 ANALYZE
 {
-  "query_optimization": {
-    "r_total_time_ms": "REPLACED"
-  },
   "query_block": {
     "select_id": 1,
     "r_loops": 1,
 
mysqltest: Result length mismatch
 
 
main.cte_recursive 'innodb'              w3 [ fail ]
        Test ended at 2023-10-09 03:00:41
 
CURRENT_TEST: main.cte_recursive
--- /<<PKGBUILDDIR>>/mysql-test/main/cte_recursive.result	2023-08-11 06:35:40.000000000 +0000
+++ /<<PKGBUILDDIR>>/mysql-test/main/cte_recursive.reject	2023-10-09 03:00:39.635291893 +0000
@@ -2460,13 +2460,9 @@
 ) select * from src;
 ANALYZE
 {
-  "query_optimization": {
-    "r_total_time_ms": "REPLACED"
-  },
   "query_block": {
     "select_id": 1,
     "r_loops": 1,
-    "r_total_time_ms": "REPLACED",
     "nested_loop": [
       {
         "table": {
@@ -2500,7 +2496,6 @@
                       "select_id": 3,
                       "operation": "UNION",
                       "r_loops": 10,
-                      "r_total_time_ms": "REPLACED",
                       "nested_loop": [
                         {
                           "table": {
@@ -3871,13 +3866,9 @@
 select a2 from cte);
 ANALYZE
 {
-  "query_optimization": {
-    "r_total_time_ms": "REPLACED"
-  },
   "query_block": {
     "select_id": 1,
     "r_loops": 1,
-    "r_total_time_ms": "REPLACED",
     "nested_loop": [
       {
         "table": {
@@ -3923,7 +3914,6 @@
                     "query_block": {
                       "select_id": 3,
                       "r_loops": 1,
-                      "r_total_time_ms": "REPLACED",
                       "nested_loop": [
                         {
                           "table": {
@@ -3951,7 +3941,6 @@
                       "select_id": 4,
                       "operation": "UNION",
                       "r_loops": 1,
-                      "r_total_time_ms": "REPLACED",
                       "nested_loop": [
                         {
                           "table": {
@@ -4136,9 +4125,6 @@
 select 0 as b FROM dual union all select b FROM r_cte as t;
 ANALYZE
 {
-  "query_optimization": {
-    "r_total_time_ms": "REPLACED"
-  },
   "query_block": {
     "union_result": {
       "query_specifications": [
@@ -4155,7 +4141,6 @@
             "select_id": 4,
             "operation": "UNION",
             "r_loops": 1,
-            "r_total_time_ms": "REPLACED",
             "nested_loop": [
               {
                 "table": {
@@ -4180,7 +4165,6 @@
                             "query_block": {
                               "select_id": 2,
                               "r_loops": 1,
-                              "r_total_time_ms": "REPLACED",
                               "nested_loop": [
                                 {
                                   "table": {
@@ -4204,7 +4188,6 @@
                               "select_id": 3,
                               "operation": "UNION",
                               "r_loops": 1,
-                              "r_total_time_ms": "REPLACED",
                               "nested_loop": [
                                 {
                                   "table": {
@@ -4306,13 +4289,9 @@
 select * from t1 as tt;
 ANALYZE
 {
-  "query_optimization": {
-    "r_total_time_ms": "REPLACED"
-  },
   "query_block": {
     "select_id": 1,
     "r_loops": 1,
-    "r_total_time_ms": "REPLACED",
     "nested_loop": [
       {
         "table": {
 
mysqltest: Result length mismatch
 
 
main.analyze_stmt_orderby 'innodb'       w1 [ fail ]
        Test ended at 2023-10-09 03:05:31
 
CURRENT_TEST: main.analyze_stmt_orderby
--- /<<PKGBUILDDIR>>/mysql-test/main/analyze_stmt_orderby.result	2023-08-11 06:35:40.000000000 +0000
+++ /<<PKGBUILDDIR>>/mysql-test/main/analyze_stmt_orderby.reject	2023-10-09 03:05:29.042701955 +0000
@@ -35,9 +35,6 @@
 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",
@@ -93,9 +90,6 @@
 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",
@@ -145,9 +139,6 @@
 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",
@@ -230,7 +221,6 @@
     "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,
@@ -325,13 +315,9 @@
 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,
     "r_loops": 1,
-    "r_total_time_ms": "REPLACED",
     "nested_loop": [
       {
         "read_sorted_file": {
@@ -339,7 +325,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",
@@ -398,9 +383,6 @@
 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,
     "r_loops": 1,
@@ -408,7 +390,6 @@
     "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",
@@ -468,7 +449,6 @@
   "query_block": {
     "select_id": 1,
     "r_loops": 1,
-    "r_total_time_ms": "REPLACED",
     "duplicate_removal": {
       "temporary_table": {
         "nested_loop": [
@@ -535,17 +515,12 @@
 group by t5.a order by sum limit 1;
 ANALYZE
 {
-  "query_optimization": {
-    "r_total_time_ms": "REPLACED"
-  },
   "query_block": {
     "select_id": 1,
     "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,
@@ -554,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",
@@ -679,13 +653,9 @@
 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,
     "r_loops": 1,
-    "r_total_time_ms": "REPLACED",
     "nested_loop": [
       {
         "table": {
 
mysqltest: Result length mismatch
 
 
main.rowid_filter_innodb 'innodb'        w3 [ fail ]
        Test ended at 2023-10-09 03:05:51
 
CURRENT_TEST: main.rowid_filter_innodb
--- /<<PKGBUILDDIR>>/mysql-test/main/rowid_filter_innodb.result	2023-08-11 06:35:40.000000000 +0000
+++ /<<PKGBUILDDIR>>/mysql-test/main/rowid_filter_innodb.reject	2023-10-09 03:05:49.122522215 +0000
@@ -116,9 +116,6 @@
 l_quantity > 45;
 ANALYZE
 {
-  "query_optimization": {
-    "r_total_time_ms": "REPLACED"
-  },
   "query_block": {
     "select_id": 1,
     "r_loops": 1,
@@ -264,9 +261,6 @@
 l_quantity > 45;
 ANALYZE
 {
-  "query_optimization": {
-    "r_total_time_ms": "REPLACED"
-  },
   "query_block": {
     "select_id": 1,
     "r_loops": 1,
@@ -424,13 +418,9 @@
 o_totalprice between 200000 and 230000;
 ANALYZE
 {
-  "query_optimization": {
-    "r_total_time_ms": "REPLACED"
-  },
   "query_block": {
     "select_id": 1,
     "r_loops": 1,
-    "r_total_time_ms": "REPLACED",
     "nested_loop": [
       {
         "table": {
@@ -561,9 +551,6 @@
 o_totalprice between 200000 and 230000;
 ANALYZE
 {
-  "query_optimization": {
-    "r_total_time_ms": "REPLACED"
-  },
   "query_block": {
     "select_id": 1,
     "r_loops": 1,
@@ -711,9 +698,6 @@
 o_totalprice between 180000 and 230000;
 ANALYZE
 {
-  "query_optimization": {
-    "r_total_time_ms": "REPLACED"
-  },
   "query_block": {
     "select_id": 1,
     "r_loops": 1,
@@ -873,9 +857,6 @@
 o_totalprice between 180000 and 230000;
 ANALYZE
 {
-  "query_optimization": {
-    "r_total_time_ms": "REPLACED"
-  },
   "query_block": {
     "select_id": 1,
     "r_loops": 1,
@@ -1017,13 +998,9 @@
 o_totalprice between 200000 and 230000;
 ANALYZE
 {
-  "query_optimization": {
-    "r_total_time_ms": "REPLACED"
-  },
   "query_block": {
     "select_id": 1,
     "r_loops": 1,
-    "r_total_time_ms": "REPLACED",
     "nested_loop": [
       {
         "table": {
@@ -1186,7 +1163,6 @@
   "query_block": {
     "select_id": 1,
     "r_loops": 1,
-    "r_total_time_ms": "REPLACED",
     "nested_loop": [
       {
         "table": {
@@ -1359,13 +1335,9 @@
 o_totalprice BETWEEN 200000 AND 250000;
 ANALYZE
 {
-  "query_optimization": {
-    "r_total_time_ms": "REPLACED"
-  },
   "query_block": {
     "select_id": 1,
     "r_loops": 1,
-    "r_total_time_ms": "REPLACED",
     "nested_loop": [
       {
         "table": {
@@ -1498,13 +1470,9 @@
 o_totalprice BETWEEN 200000 AND 250000;
 ANALYZE
 {
-  "query_optimization": {
-    "r_total_time_ms": "REPLACED"
-  },
   "query_block": {
     "select_id": 1,
     "r_loops": 1,
-    "r_total_time_ms": "REPLACED",
     "nested_loop": [
       {
         "table": {
@@ -1643,13 +1611,9 @@
 l_shipdate BETWEEN '1996-10-01' AND '1996-12-01';
 ANALYZE
 {
-  "query_optimization": {
-    "r_total_time_ms": "REPLACED"
-  },
   "query_block": {
     "select_id": 1,
     "r_loops": 1,
-    "r_total_time_ms": "REPLACED",
     "nested_loop": [
       {
         "table": {
@@ -1783,13 +1747,9 @@
 l_shipdate BETWEEN '1996-10-01' AND '1996-12-01';
 ANALYZE
 {
-  "query_optimization": {
-    "r_total_time_ms": "REPLACED"
-  },
   "query_block": {
     "select_id": 1,
     "r_loops": 1,
-    "r_total_time_ms": "REPLACED",
     "nested_loop": [
       {
         "table": {
@@ -1931,9 +1891,6 @@
 l_shipdate BETWEEN '1996-10-01' AND '1996-12-01';
 ANALYZE
 {
-  "query_optimization": {
-    "r_total_time_ms": "REPLACED"
-  },
   "query_block": {
     "select_id": 1,
     "r_loops": 1,
@@ -2085,13 +2042,9 @@
 l_shipdate BETWEEN '1996-10-01' AND '1996-12-01';
 ANALYZE
 {
-  "query_optimization": {
-    "r_total_time_ms": "REPLACED"
-  },
   "query_block": {
     "select_id": 1,
     "r_loops": 1,
-    "r_total_time_ms": "REPLACED",
     "nested_loop": [
       {
         "table": {
@@ -2507,13 +2460,9 @@
 select * from t1  where nm like '500%' AND fl2 = 0;
 ANALYZE
 {
-  "query_optimization": {
-    "r_total_time_ms": "REPLACED"
-  },
   "query_block": {
     "select_id": 1,
     "r_loops": 1,
-    "r_total_time_ms": "REPLACED",
     "nested_loop": [
       {
         "table": {
@@ -2561,13 +2510,9 @@
 select * from t1  where nm like '500%' AND fl2 = 0;
 ANALYZE
 {
-  "query_optimization": {
-    "r_total_time_ms": "REPLACED"
-  },
   "query_block": {
     "select_id": 1,
     "r_loops": 1,
-    "r_total_time_ms": "REPLACED",
     "nested_loop": [
       {
         "table": {
@@ -2640,13 +2585,9 @@
 select * from t1  where nm like '75%' AND fl2 = 0;
 ANALYZE
 {
-  "query_optimization": {
-    "r_total_time_ms": "REPLACED"
-  },
   "query_block": {
     "select_id": 1,
     "r_loops": 1,
-    "r_total_time_ms": "REPLACED",
     "nested_loop": [
       {
         "table": {
@@ -2828,7 +2769,6 @@
   "query_block": {
     "select_id": 1,
     "r_loops": 1,
-    "r_total_time_ms": "REPLACED",
     "nested_loop": [
       {
         "table": {
@@ -3926,9 +3866,6 @@
 8518228073041491534,-5420422472375069774);
 ANALYZE
 {
-  "query_optimization": {
-    "r_total_time_ms": "REPLACED"
-  },
   "query_block": {
     "select_id": 1,
     "r_loops": 1,
 
mysqltest: Result length mismatch
 
 
main.explain_json_format_partitions      w4 [ fail ]
        Test ended at 2023-10-09 03:11:49
 
CURRENT_TEST: main.explain_json_format_partitions
--- /<<PKGBUILDDIR>>/mysql-test/main/explain_json_format_partitions.result	2023-08-11 06:35:40.000000000 +0000
+++ /<<PKGBUILDDIR>>/mysql-test/main/explain_json_format_partitions.reject	2023-10-09 03:11:48.779298437 +0000
@@ -29,13 +29,9 @@
 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,
     "r_loops": 1,
-    "r_total_time_ms": "REPLACED",
     "nested_loop": [
       {
         "table": {
@@ -59,12 +55,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,
@@ -74,7 +66,6 @@
         "rows": 10,
         "r_rows": 10,
         "r_filtered": 30,
-        "r_total_time_ms": "REPLACED",
         "r_engine_stats": REPLACED,
         "attached_condition": "t1.a in (2,3,4)"
       }
@@ -84,12 +75,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",
@@ -98,7 +85,6 @@
       "rows": 10,
       "r_rows": 10,
       "r_filtered": 0,
-      "r_total_time_ms": "REPLACED",
       "r_engine_stats": REPLACED,
       "attached_condition": "t1.a in (20,30,40)"
     }
 
mysqltest: Result length mismatch



 Comments   
Comment by Daniel Black [ 2023-11-30 ]

'bool has_timed_statistics() const

{ return cycles > 0; }

' was added in 99bd22605938c42d876194f2ec75b32e658f00f5 MDEV-31558 probably changing the output.

For hppa output on this test to get a consistent result needs a cycles implementation include/my_rdtsc.h or a gethrtime alternative for my_timer_cycles.

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