Bug #102778 Hypergraph optimizer crashes when explaining TPC-H Q1
Submitted: 2 Mar 2021 9:19 Modified: 14 Oct 2021 13:55
Reporter: Hope Lee (OCA) Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S6 (Debug Builds)
Version:8.0.23 OS:CentOS
Assigned to: CPU Architecture:Any

[2 Mar 2021 9:19] Hope Lee 
Description:
When using hypergraph optimizer and explaining TPC-H Q1, the server's assertion failed.

How to repeat:
CREATE TABLE lineitem (
	l_orderkey integer NOT NULL,
	l_partkey integer NOT NULL,
	l_suppkey integer NOT NULL,
	l_linenumber integer NOT NULL,
	l_quantity decimal(15, 2) NOT NULL,
	l_extendedprice decimal(15, 2) NOT NULL,
	l_discount decimal(15, 2) NOT NULL,
	l_tax decimal(15, 2) NOT NULL,
	l_returnflag char(1) NOT NULL,
	l_linestatus char(1) NOT NULL,
	l_shipdate date NOT NULL,
	l_commitdate date NOT NULL,
	l_receiptdate date NOT NULL,
	l_shipinstruct char(25) NOT NULL,
	l_shipmode char(10) NOT NULL,
	l_comment varchar(44) NOT NULL,
	PRIMARY KEY (l_orderkey, l_linenumber)
);

SET optimizer_switch="hypergraph_optimizer=on";

root@localhost:test 8.0.23-dev-debug> EXPLAIN SELECT l_returnflag, l_linestatus, sum(l_quantity) AS sum_qty
	, sum(l_extendedprice) AS sum_base_price
	, sum(l_extendedprice * (1 - l_discount)) AS sum_disc_price
	, sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) AS sum_charge
	, avg(l_quantity) AS avg_qty, avg(l_extendedprice) AS avg_price
	, avg(l_discount) AS avg_disc, count(*) AS count_order
FROM lineitem
WHERE l_shipdate <= date_sub('1998-12-01', INTERVAL '96' DAY)
GROUP BY l_returnflag, l_linestatus
ORDER BY l_returnflag, l_linestatus;

ERROR 2013 (HY000): Lost connection to MySQL server during query

Thread 43 "mysqld" received signal SIGABRT, Aborted.
[Switching to Thread 0x7fffe6745700 (LWP 75456)]
0x00007ffff5def277 in raise () from /lib64/libc.so.6
(gdb) bt
#0  0x00007ffff5def277 in raise () from /lib64/libc.so.6
#1  0x00007ffff5df0968 in abort () from /lib64/libc.so.6
#2  0x00007ffff5de8096 in __assert_fail_base () from /lib64/libc.so.6
#3  0x00007ffff5de8142 in __assert_fail () from /lib64/libc.so.6
#4  0x00000000031c8f2c in down_cast<SortingIterator*, RowIterator> (arg=0x7fff38abbe88) at /mysql-server/include/template_utils.h:99
#5  0x00000000031bc777 in JOIN::destroy (this=0x7fff380ea3e8) at /mysql-server/sql/sql_select.cc:1727
#6  0x0000000003262d8b in SELECT_LEX::cleanup (this=0x7fff38ad19b8, thd=0x7fff38000da0, full=true) at /mysql-server/sql/sql_union.cc:1528
#7  0x0000000003262477 in SELECT_LEX_UNIT::cleanup (this=0x7fff38ad1268, thd=0x7fff38000da0, full=true) at /mysql-server/sql/sql_union.cc:1307
#8  0x000000000306083e in LEX::cleanup (this=0x7fff38003d40, thd=0x7fff38000da0, full=true) at /mysql-server/sql/sql_lex.h:3970
#9  0x0000000003143d1f in mysql_execute_command (thd=0x7fff38000da0, first_level=true) at /mysql-server/sql/sql_parse.cc:4586
#10 0x000000000314509a in dispatch_sql_command (thd=0x7fff38000da0, parser_state=0x7fffe6743aa0) at /mysql-server/sql/sql_parse.cc:4988
#11 0x000000000313b6ec in dispatch_command (thd=0x7fff38000da0, com_data=0x7fffe6744b40, command=COM_QUERY) at /mysql-server/sql/sql_parse.cc:1836
#12 0x0000000003139b27 in do_command (thd=0x7fff38000da0) at /mysql-server/sql/sql_parse.cc:1320
#13 0x000000000330dfc5 in handle_connection (arg=0x87a6810) at /mysql-server/sql/conn_handler/connection_handler_per_thread.cc:301
#14 0x0000000004f4788c in pfs_spawn_thread (arg=0xa3b08c0) at /mysql-server/storage/perfschema/pfs.cc:2900
#15 0x00007ffff7bc6e25 in start_thread () from /lib64/libpthread.so.0
#16 0x00007ffff5eb6f1d in clone () from /lib64/libc.so.6
[2 Mar 2021 9:25] MySQL Verification Team 
Hello Lee,

Thank you for the report and test case.
Observed that 8.0.23 debug build is affected.

regards,
Umesh
[3 Mar 2021 11:23] Steinar Gunderson 
Hi,

As the message says, the hypergraph optimizer is highly experimental and for
MySQL developers only :-) And thus, not supported. But this bug has already
been fixed; current trunk does not die on these queries. I don't know what
release the fix will be in; in general, the hypergraph optimizer does not
care much about release cutoffs (a release just gets whatever was in trunk at
the time), given the lack of support.
[18 Jan 2023 9:41] Frederic Descamps 
In 8.0.32, when hypegraph is enabled, explain with traditional format will return this error:

ERROR: 3999 (42000): The hypergraph optimizer does not yet support 'EXPLAIN with TRADITIONAL format'