| Bug #84571 | Assert `!table->has_null_row()' failed in join_read_key on multi-table join | ||
|---|---|---|---|
| Submitted: | 19 Jan 2017 22:22 | Modified: | 24 Jan 2017 17:59 |
| Reporter: | John Embretsen | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 8.0.1 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[22 Jan 2017 21:08]
John Embretsen
Posted by developer:
The following SQL (works as an MTR test) seems to reproduce the issue reliably:
CREATE TABLE E (
pk INT NOT NULL AUTO_INCREMENT,
col_int_key INT DEFAULT NULL,
col_varchar_10_latin1_key VARCHAR(10) DEFAULT NULL,
col_varchar_255_utf8 VARCHAR(255) CHARACTER SET utf8 DEFAULT NULL,
col_int INT DEFAULT NULL,
col_datetime_key datetime DEFAULT NULL,
col_varchar_255_latin1_key VARCHAR(255) DEFAULT NULL,
col_date_key date DEFAULT NULL,
col_datetime datetime DEFAULT NULL,
PRIMARY KEY (pk),
KEY col_date_key (col_date_key)
) ENGINE=MyISAM;
INSERT INTO E VALUES (
8,9,'h','FDUMQ',-1356726272,
'2007-09-02 05:48:23','she','2002-04-02','2002-12-20 17:54:07');
CREATE TABLE G (
pk INT NOT NULL AUTO_INCREMENT,
col_int INT DEFAULT NULL,
col_int_key INT DEFAULT NULL,
PRIMARY KEY (pk)
) ENGINE=MyISAM;
CREATE TABLE L (
col_int INT DEFAULT NULL,
col_int_key INT DEFAULT NULL,
pk INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (pk),
KEY test_idx (col_int_key,pk,col_int)
) ENGINE=InnoDB;
INSERT INTO L VALUES (NULL,9, 41);
INSERT INTO L VALUES (NULL,-1596719104, 48);
INSERT INTO L VALUES (-1068105728,9, 49);
CREATE TABLE M (
col_varchar_255_latin1_key VARCHAR(255) DEFAULT NULL,
pk INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (pk)
) ENGINE=MyISAM;
INSERT INTO M VALUES ('RUXDY',8);
SELECT
alias1 . pk AS field5
FROM L AS alias1
LEFT JOIN M AS alias2
LEFT JOIN M AS alias3
LEFT JOIN E AS alias4
LEFT JOIN G AS alias5
ON alias4 . pk = alias5 . col_int_key
ON alias3 . pk = alias5 . col_int
ON alias2 . col_varchar_255_latin1_key = alias4 . col_varchar_10_latin1_key
RIGHT JOIN E AS alias6
ON alias4 . pk = alias6 . pk
RIGHT JOIN M AS alias7
ON alias6 . pk = alias7 . pk
ON alias1 . col_int_key = alias6 . col_int_key
WHERE
alias1 . col_int > 5
OR alias5 . col_int > 5
;
---
There may be simpler ways to reproduce. This scenario was picked randomly among a larger number of crashes based on randomly generated queries and data with two different test tools (RQG + Gator), and then simplified.
It is perhaps noteworthy that some columns of table E seem to need to be present although they are not used in the query itself.
This example seems to require some use of MyISAM, but the same issue is likely observed in tests using InnoDB only as well.
[24 Jan 2017 17:59]
Paul DuBois
Posted by developer: Fixed in 8.0.1. Bug affects no released version. No changelog entry needed.
Description: A debug compiled server asserts as follows when executing a SELECT statement with multi-table JOIN: sql/sql_executor.cc:2399: int join_read_key(QEP_TAB*): Assertion `!table->has_null_row()' failed. 21:31:57 UTC - mysqld got signal 6 ; #2 in handle_fatal_signal (sig=6) at sql/signal_handler.cc:231 #3 <signal handler called> #4 in raise () from /lib64/libc.so.6 #5 in abort () from /lib64/libc.so.6 #6 in __assert_fail_base () from /lib64/libc.so.6 #7 in __assert_fail () from /lib64/libc.so.6 #8 in join_read_key (tab=0x7f262cdc0f78) at sql/sql_executor.cc:2399 #9 in sub_select (join=0x7f262cdb7fe8, qep_tab=0x7f262cdc0f78, end_of_records=false) at sql/sql_executor.cc:1502 #10 in evaluate_join_record (join=0x7f262cdb7fe8, qep_tab=0x7f262cdc0df8) at sql/sql_executor.cc:1871 #11 in sub_select (join=0x7f262cdb7fe8, qep_tab=0x7f262cdc0df8, end_of_records=false) at sql/sql_executor.cc:1523 #12 in evaluate_join_record (join=0x7f262cdb7fe8, qep_tab=0x7f262cdc0c78) at sql/sql_executor.cc:1871 #13 in sub_select (join=0x7f262cdb7fe8, qep_tab=0x7f262cdc0c78, end_of_records=false) at sql/sql_executor.cc:1523 #14 in do_select (join=0x7f262cdb7fe8) at sql/sql_executor.cc:1156 #15 in JOIN::exec (this=0x7f262cdb7fe8) at sql/sql_executor.cc:257 #16 in Sql_cmd_dml::execute_inner (this=0x7f262c0e7b88, thd=0x7f262c46bc90) at sql/sql_select.cc:736 #17 in Sql_cmd_dml::execute (this=0x7f262c0e7b88, thd=0x7f262c46bc90) at sql/sql_select.cc:627 #18 in mysql_execute_command (thd=0x7f262c46bc90, first_level=true) at sql/sql_parse.cc:4437 This issue was first observed in some RQG tests in Pushbuild and Gator tests in Hudson using the daily-trunk build from 2017-01-18. A git bisect run suggests the following culprit commit: d79582c2809ac2f548a78c96b1b9995749993690 is the first bad commit commit d79582c2809ac2f548a78c96b1b9995749993690 Author: Roy Lyseng <roy.lyseng@oracle.com> Date: Wed Jan 18 14:25:44 2017 +0100 Bug#23130819: Refactor state changes for the row buffer in class TABLE No issue is seen with a non-debug build. How to repeat: Crashing query: SELECT alias1 . `col_varchar_10_latin1` AS field5 FROM L AS alias1 LEFT JOIN M AS alias2 LEFT JOIN M AS alias3 LEFT JOIN E AS alias4 LEFT JOIN G AS alias5 ON alias4 . `pk` = alias5 . `col_int_key` ON alias3 . `pk` = alias5 . `col_int` ON alias2 . `col_varchar_255_latin1_key` = alias4 . `col_varchar_10_latin1_key` RIGHT JOIN E AS alias6 ON alias4 . `pk` = alias6 . `pk` RIGHT JOIN M AS alias7 ON alias6 . `pk` = alias7 . `pk` ON alias1 . `col_int_key` = alias6 . `col_int_key` WHERE alias1 . `col_int` > 5 OR alias5 . `col_int` > 5 ; A full MTR test case will be added later.