[MDEV-10683] main.order_by_optimizer_innodb fails in buildbot - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.1(EOL), 10.3(EOL), 10.4(EOL), 10.5
Fix Version/s: 10.4.32, 10.5.23, 10.6.16, 10.10.7, 10.11.6, 11.0.4, 11.1.3, 11.2.2, 11.3.1
Component/s: Tests
Labels:
None

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

Ascending order - Click to sort in descending order

Elena Stepanova created issue - 2016-08-27 13:38

Elena Stepanova made changes - 2016-08-27 13:38

Field	Original Value	New Value
Link		This issue relates to MDEV-7069 [ MDEV-7069 ]

Elena Stepanova added a comment - 2018-09-24 22:41

Still happens:
http://buildbot.askmonty.org/buildbot/builders/kvm-zyp-opensuse423-amd64/builds/370

Elena Stepanova added a comment - 2018-09-24 22:41 Still happens: http://buildbot.askmonty.org/buildbot/builders/kvm-zyp-opensuse423-amd64/builds/370

Valerii Kravchuk added a comment - 2019-02-18 14:49

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.

Valerii Kravchuk added a comment - 2019-02-18 14:49 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.

Valerii Kravchuk made changes - 2019-02-18 14:49

Affects Version/s

10.4 [ 22408 ]

Michael Widenius added a comment - 2019-09-01 23:24

Still fails..

Michael Widenius added a comment - 2019-09-01 23:24 Still fails..

Michael Widenius made changes - 2019-09-01 23:25

Assignee

Sergei Petrunia [ psergey ]

Aleksey Midenkov made changes - 2019-11-19 12:26

Fix Version/s		10.3 [ 22126 ]
Fix Version/s	10.1 [ 16100 ]
Affects Version/s		10.3 [ 22126 ]
Affects Version/s		10.5 [ 23123 ]

Aleksey Midenkov added a comment - 2019-11-19 12:27 - edited

All branches

Aleksey Midenkov added a comment - 2019-11-19 12:27 - edited All branches

Aleksey Midenkov made changes - 2019-11-19 12:34

Fix Version/s		10.1 [ 16100 ]
Fix Version/s	10.3 [ 22126 ]

Marko Mäkelä added a comment - 2021-03-10 09:19

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

Marko Mäkelä added a comment - 2021-03-10 09:19 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

Marko Mäkelä made changes - 2021-03-10 09:19

Fix Version/s		10.2 [ 14601 ]
Fix Version/s		10.3 [ 22126 ]
Fix Version/s		10.4 [ 22408 ]
Fix Version/s		10.5 [ 23123 ]
Fix Version/s		10.6 [ 24028 ]
Priority	Minor [ 4 ]	Major [ 3 ]

Sergei Golubchik made changes - 2021-05-25 12:50

Fix Version/s

10.6 [ 24028 ]

Sergei Golubchik made changes - 2021-12-06 21:32

Workflow

MariaDB v3 [ 76826 ]

MariaDB v4 [ 140072 ]

Sergei Golubchik made changes - 2022-04-20 08:53

Fix Version/s

10.1 [ 16100 ]

Ralf Gebhardt made changes - 2022-08-04 09:20

Fix Version/s

10.2 [ 14601 ]

Otto Kekäläinen added a comment - 2023-02-09 16:29

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

Otto Kekäläinen added a comment - 2023-02-09 16:29 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

Otto Kekäläinen added a comment - 2023-02-10 16:45

Relater MR from Daniel Black to trace test: https://salsa.debian.org/mariadb-team/mariadb-server/-/merge_requests/35

Otto Kekäläinen added a comment - 2023-02-10 16:45 Relater MR from Daniel Black to trace test: https://salsa.debian.org/mariadb-team/mariadb-server/-/merge_requests/35

Julien Fritsch made changes - 2023-04-27 14:20

Fix Version/s

10.3 [ 22126 ]

Sergei Petrunia added a comment - 2023-10-05 13:24

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

Sergei Petrunia added a comment - 2023-10-05 13:24 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

Sergei Petrunia added a comment - 2023-10-05 13:27

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

},

Sergei Petrunia added a comment - 2023-10-05 13:27 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 } } },

Sergei Petrunia added a comment - 2023-10-05 13:28

Ok, in bad-trace.json, InnoDB reports rows:650 instead of normal 1034.
Then, range scan on ux_pk1_fd5 is not picked.

Sergei Petrunia added a comment - 2023-10-05 13:28 Ok, in bad-trace.json, InnoDB reports rows:650 instead of normal 1034. Then, range scan on ux_pk1_fd5 is not picked.

Sergei Petrunia added a comment - 2023-10-05 13:30

@@ -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.

Sergei Petrunia added a comment - 2023-10-05 13:30 @@ -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.

Sergei Petrunia made changes - 2023-10-05 13:38

Status

Open [ 1 ]

In Progress [ 3 ]

Sergei Petrunia made changes - 2023-10-05 14:24

Fix Version/s		10.4.32 [ 29300 ]
Fix Version/s		10.5.23 [ 29012 ]
Fix Version/s		10.6.16 [ 29014 ]
Fix Version/s		10.10.7 [ 29018 ]
Fix Version/s		10.11.6 [ 29020 ]
Fix Version/s		11.0.4 [ 29021 ]
Fix Version/s		11.1.3 [ 29023 ]
Fix Version/s		11.2.2 [ 29035 ]
Fix Version/s		11.3.1 [ 29416 ]
Fix Version/s	10.4 [ 22408 ]
Fix Version/s	10.5 [ 23123 ]
Resolution		Fixed [ 1 ]
Status	In Progress [ 3 ]	Closed [ 6 ]

Otto Kekäläinen added a comment - 2023-10-07 05:47

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

Otto Kekäläinen added a comment - 2023-10-07 05:47 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

People

Assignee:: Sergei Petrunia

Reporter:: Elena Stepanova

Votes:: 1 Vote for this issue

Watchers:: 7 Start watching this issue

Dates

Created:: 2016-08-27 13:38

Updated:: 2023-10-11 12:06

Resolved:: 2023-10-05 14:24

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration