MySQL Bugs: #113046: MTR tests for SELECT fail with ICP, MRR, possibly other flags

Bug #113046	MTR tests for SELECT fail with ICP, MRR, possibly other flags
Submitted:	10 Nov 2023 14:58	Modified:	22 Dec 2023 4:40
Reporter:	Laurynas Biveinis (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	8.0.35, 8.2.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Some of the optimizer flag combinations fail the MTR tests that use include/select.inc.

For example,

[ 92%] main.select_icp_mrr_bka_nobnl            w5  [ fail ]
        Test ended at 2023-11-10 16:48:54

CURRENT_TEST: main.select_icp_mrr_bka_nobnl
--- /Users/laurynas/vilniusdb/mysql-8.0.35/mysql-test/r/select_icp_mrr_bka_nobnl.result	2023-10-26 09:13:44
+++ /Users/laurynas/vilniusdb/mysql-8.0.35/_build-release/mysql-test/var/5/log/select_icp_mrr_bka_nobnl.reject	2023-11-10 17:48:54
@@ -233,11 +233,11 @@
 188505	fitting
 198006	furthermore
 202301	Fitzpatrick
-208101	fiftieth
 208113	freest
 218008	finishers
-218022	feed
+218008	finishers
 218401	faithful
+218401	faithful
 226205	foothill
 226209	furnishings
 228306	forthcoming

mysqltest: Result content mismatch

How to repeat:
$ mtr --force --max-test-fail=0 --retry-failure=0 --suite=main --do-test=select
...
Completed: Failed 6/14 tests, 57.14% were successful.

Failing test(s): main.select_icp_mrr_bka main.select_icp_mrr_bka_nobnl main.select_all_bka main.select_all_bka_nobnl main.select_all main.select_icp_mrr

All the --do-test=join_cache tests fail as well. Not sure if this the same or separate issue. Example:

[ 40%] main.join_cache_bnl                      w2  [ fail ]
        Test ended at 2023-11-10 17:00:17

CURRENT_TEST: main.join_cache_bnl
--- /Users/laurynas/vilniusdb/mysql-8.0.35/mysql-test/r/join_cache_bnl.result	2023-10-26 09:13:43
+++ /Users/laurynas/vilniusdb/mysql-8.0.35/_build-release/mysql-test/var/2/log/join_cache_bnl.reject	2023-11-10 18:00:16
@@ -1169,18 +1169,19 @@
 Beirut	Lebanon	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
 Bengasi	Libyan Arab Jamahiriya	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
 Daugavpils	Latvia	xxxxxxxxxxxxxxxxxxxxxxxx
-Kaunas	Lithuania	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
-Klaipeda	Lithuania	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+Daugavpils	Latvia	xxxxxxxxxxxxxxxxxxxxxxxx
+Daugavpils	Latvia	xxxxxxxxxxxxxxxxxxxxxxxx
+Daugavpils	Latvia	xxxxxxxxxxxxxxxxxxxxxxxx
 Maseru	Lesotho	xxxxxxxxxxxxxxxxxxxxxx
 Misrata	Libyan Arab Jamahiriya	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
 Monrovia	Liberia	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
-Panevezys	Lithuania	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
 Riga	Latvia	xxxxxxxxxxxxxxxxxxxxxxxx
+Riga	Latvia	xxxxxxxxxxxxxxxxxxxxxxxx
+Riga	Latvia	xxxxxxxxxxxxxxxxxxxxxxxx
+Riga	Latvia	xxxxxxxxxxxxxxxxxxxxxxxx
 Tripoli	Lebanon	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
 Tripoli	Libyan Arab Jamahiriya	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
 Vientiane	Laos	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
-Vilnius	Lithuania	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
-Šiauliai	Lithuania	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
 set join_buffer_size=256;
 SELECT city.Name, country.Name, country.PopulationBar FROM city,country
 WHERE city.country=country.Code AND

A subset of --do-test=subquery tests fails too:

Failing test(s): main.subquery_nomat_nosj_bka main.subquery_sj_all_bka main.subquery_sj_all_bka_nobnl

A sample failure:

[ 47%] main.subquery_nomat_nosj_bka             w11 [ fail ]
        Test ended at 2023-11-10 17:11:00

CURRENT_TEST: main.subquery_nomat_nosj_bka
--- /Users/laurynas/vilniusdb/mysql-8.0.35/mysql-test/r/subquery_nomat_nosj_bka.result	2023-10-26 09:13:44
+++ /Users/laurynas/vilniusdb/mysql-8.0.35/_build-release/mysql-test/var/11/log/subquery_nomat_nosj_bka.reject	2023-11-10 18:10:59
@@ -8302,7 +8302,6 @@
 HAVING field1 != 'r'
 ORDER BY CONCAT(alias2.col_varchar_nokey, alias2.col_varchar_nokey) DESC, field1;
 field1
-c
 c
 c
 c
@@ -8320,7 +8319,6 @@
 p
 p
 p
-p
 q
 q
 q

mysqltest: Result content mismatch

Hello Laurynas,

Thank you for the report and feedback!
I quickly tried using https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-8.0.35-macos13-arm64.tar but not seeing any issues on MacOS(Ventura 13.5.1) and even on OL7(generic binary tarball build) but not observed any issues which is seen in your report. Is there anything I'm missing here? Thank you.

--
./mtr --force --max-test-fail=0 --retry-failure=0 --suite=main --do-test=select               
Logging: ./mtr  --force --max-test-fail=0 --retry-failure=0 --suite=main --do-test=select
MySQL Version 8.0.35
Checking supported features
Using suite(s): main
Collecting tests
Checking leftover processes
Removing old var directory
Creating var directory '/Users/umshastr/Downloads/mysql-8.0.35/mysql-test/var'
Installing system database
Using parallel: 1

==============================================================================
                  TEST NAME                       RESULT  TIME (ms) COMMENT
------------------------------------------------------------------------------
[  7%] main.select_count                         [ pass ]     46
[ 14%] main.select_for_update                    [ pass ]   8159
[ 21%] main.select_all                           [ pass ]   1913
[ 28%] main.select_all_bka                       [ pass ]   2025
[ 35%] main.select_all_bka_nobnl                 [ pass ]   2141
[ 42%] main.select_found                         [ pass ]     83
[ 50%] main.select_icp_mrr                       [ pass ]   2325
[ 57%] main.select_icp_mrr_bka                   [ pass ]   2555
[ 64%] main.select_icp_mrr_bka_nobnl             [ pass ]   1831
[ 71%] main.select_none                          [ pass ]   1740
[ 78%] main.select_none_bka                      [ pass ]   1737
[ 85%] main.select_none_bka_nobnl                [ pass ]   1896
[ 92%] main.select_safe                          [ pass ]     42
[100%] shutdown_report                           [ pass ]       
------------------------------------------------------------------------------
The servers were restarted 2 times
The servers were reinitialized 0 times
Spent 26.493 of 35 seconds executing testcases

Completed: All 14 tests were successful.

regards,
Umesh

Adding -ffp-contract=off appears to fix main.subquery_nomat_nosj_bka. All the other failures remain

Thank you, Laurynas.
Verified as described.

Sincerely,
Umesh

Tested a few different compilers.
Failing: XCode 15.0.0, LLVM 15, 16, 17 from Homebrew
Passing: LLVM 14 from Homebrew.

Posted by developer:
 
set optimizer_switch='batched_key_access=on,block_nested_loop=off,mrr_cost_based=off';

# DROP TABLE IF EXISTS t2;
CREATE TABLE t2 (
  auto int not null auto_increment,
  fld1 int unsigned DEFAULT 0 NOT NULL,
  companynr tinyint unsigned DEFAULT 0 NOT NULL,
  fld3 char(30) DEFAULT ' NOT NULL,
  fld4 char(35) DEFAULT ' NOT NULL,
  fld5 char(35) DEFAULT ' NOT NULL,
  fld6 char(4) DEFAULT ' NOT NULL,
  UNIQUE fld1 (fld1),
  KEY fld3 (fld3),
  PRIMARY KEY (auto)
) charset utf8mb4;  

--disable_query_log
INSERT INTO t2 VALUES (56,018007,37,'fanatic','cupboard','pithed',');
INSERT INTO t2 VALUES (65,018017,37,'featherweight','emergency','offload','FAS');
INSERT INTO t2 VALUES (110,018103,37,'flint','Abraham','groupings','FAS');
INSERT INTO t2 VALUES (111,018104,37,'flopping','Hawaii','dissociate',');
INSERT INTO t2 VALUES (145,036002,37,'funereal','riser','insolence','A');
INSERT INTO t2 VALUES (164,038017,37,'fetched','schemer','Chicana','W');
INSERT INTO t2 VALUES (174,038205,37,'firearm','praised','lore',');
INSERT INTO t2 VALUES (336,088303,37,'feminine','accruing','label','FAS');
INSERT INTO t2 VALUES (634,186002,37,'freakish','dehydrate','parametrized','A');
INSERT INTO t2 VALUES (640,188007,37,'flurried','furthermore','hoarder',');
INSERT INTO t2 VALUES (661,188505,37,'fitting','buckboards','participated',');
INSERT INTO t2 VALUES (680,198006,37,'furthermore','Lizzy','infallibly','A');
INSERT INTO t2 VALUES (757,208101,37,'fiftieth','embassies','teem',');
INSERT INTO t2 VALUES (765,208113,37,'freest','teem','denounces',');
INSERT INTO t2 VALUES (803,218008,37,'finishers','reporters','Arabia',');
INSERT INTO t2 VALUES (809,218022,37,'feed','despot','handy','FAS');
INSERT INTO t2 VALUES (822,218401,37,'faithful','inaccuracy','sporty','FAS');
INSERT INTO t2 VALUES (840,226205,37,'foothill','severely','gradually',');
INSERT INTO t2 VALUES (844,226209,37,'furnishings','vanish','hooker',');
INSERT INTO t2 VALUES (882,228306,37,'forthcoming','Butterfield','meditation','A');
INSERT INTO t2 VALUES (887,228311,37,'fated','inmate','youthfulness',');
INSERT INTO t2 VALUES (939,231315,37,'freezes','omnisciently','jarred',');
INSERT INTO t2 VALUES (940,232102,37,'forgivably','Britannic','scolds',');
INSERT INTO t2 VALUES (967,238007,37,'filial','Gandhian','afloat','A');
INSERT INTO t2 VALUES (968,238008,37,'fixedly','stratified','goodness','A');
INSERT INTO t2 VALUES (993,248002,41,'fibrosities','explorers','accessed','A');
--enable_query_log
ANALYZE TABLE t2;

--sorted_result
select fld1,fld3 from t2 where fld3 like 'f%';

DROP TABLE t2;

set optimizer_switch=default;

With 8.2.0, select* & join_cache* fail (and pass) the same with the same compiler versions as 8.0.35.

subquery tests behave differently:
XCode 15, LLVM 15, 16: Failing test(s): main.subquery_sj_all_bka main.subquery_sj_all_bka_nobnl
LLVM 17: Failing test(s): main.subquery_all main.subquery_all_bka main.subquery_all_bka_nobnl main.subquery_nomat_nosj main.subquery_nomat_nosj_bka main.subquery_nomat_nosj_bka_nobnl main.subquery_sj_all main.subquery_sj_all_bka main.subquery_sj_all_bka_nobnl

LLVM 14, which passed all tests on 8.0.35, now fails one test:

[ 56%] main.subquery_sj_mat                     w2  [ fail ]
        Test ended at 2023-11-28 17:39:33

CURRENT_TEST: main.subquery_sj_mat
--- /Users/laurynas/vilniusdb/mysql-8.2.0/mysql-test/r/subquery_sj_mat.result	2023-10-27 12:28:33
+++ /Users/laurynas/vilniusdb/mysql-8.2.0/_build-debug-llvm-14/mysql-test/var/2/log/subquery_sj_mat.reject	2023-11-28 18:39:33
@@ -131,7 +131,7 @@
 Flattened because of dependency, t10=func(t1)
 explain select * from t1 where a in (select pk from t10);
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
 1	SIMPLE	t10	NULL	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	100.00	Using index
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t10` join `test`.`t1` where (`test`.`t10`.`pk` = `test`.`t1`.`a`)
...
(Lots more output difference)

Fixed in MySQL 8.0.36 and 8.3.0.

Appears to affect testing only.

Closed.

Since Tor commented on bug 113049 (closed as a duplicate of this one) that "Recent versions of Clang have changed their implementation of std::sort(), and our own 'varlen_sort()' function returns wrong results.", this appears to be more than a test-only change, but something along the lines of "Certain SELECT queries would result incorrect results on a server built with recent versions of LLVM".