Bug #94483 | Execution Plan doesn't work when the connection uses a defaults file | ||
---|---|---|---|
Submitted: | 26 Feb 2019 23:11 | Modified: | 20 Mar 2019 4:17 |
Reporter: | Yoseph Phillips | Email Updates: | |
Status: | Can't repeat | Impact on me: | |
Category: | MySQL Workbench | Severity: | S3 (Non-critical) |
Version: | 8.0.15 | OS: | Windows |
Assigned to: | CPU Architecture: | Any | |
Tags: | execution plan, readDefaultFile |
[26 Feb 2019 23:11]
Yoseph Phillips
[15 Mar 2019 22:52]
MySQL Verification Team
Thank you for the bug report. I could not repeat with my own database and query, so I will appreciate if you could attach here a complete dump file (create table + data insert + query) which is repeatable on your side. Thanks in advance.
[20 Mar 2019 4:17]
Yoseph Phillips
The following causes the issue every time for us when the connection is using the defaults file. No data required. Notice that some of the index names here are very long as making them too short doesn't reproduce the issue. CREATE TABLE IF NOT EXISTS test_table_1( testField1 VARCHAR(191) NOT NULL, testField2 INT NOT NULL, testField3 VARCHAR(10000), testField4 FLOAT, testField5 DATETIME, testField6 BOOLEAN, PRIMARY KEY (testField1) ); CREATE TABLE IF NOT EXISTS test_table_2( testField1 VARCHAR(191) NOT NULL, testField2 VARCHAR(191) NOT NULL, PRIMARY KEY (testField1, testField2) ); CREATE TABLE IF NOT EXISTS test_table_3( id INT UNSIGNED AUTO_INCREMENT NOT NULL, testField1 VARCHAR(191) NOT NULL, PRIMARY KEY (id), UNIQUE INDEX index1(testField1) ); CREATE TABLE IF NOT EXISTS test_table_4( testField1 int(11) NOT NULL, testField2 varchar(191) NOT NULL, testField3 int(11) NOT NULL, testField4 int(11) NOT NULL, testField5 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, testField6 int(11) DEFAULT NULL, testField7 bit(1) DEFAULT NULL, testField8 int(11) NOT NULL, testField9 int(11) NOT NULL, testField10 int(11) DEFAULT NULL, PRIMARY KEY (testField1), INDEX index2(testField2), INDEX index3(testField3), INDEX index4(testField4), INDEX index5(testField5), INDEX index6(testField6), INDEX index7(testField8), INDEX index8(testField9) ); CREATE TABLE IF NOT EXISTS test_table_5( id INT UNSIGNED NOT NULL AUTO_INCREMENT, testField1 INT(11) NOT NULL, testField2 INT(10) UNSIGNED NOT NULL, testField3 VARCHAR(10000), INDEX index9(testField2), INDEX index10(testField1, testField2), PRIMARY KEY (id) ); CREATE TABLE IF NOT EXISTS test_table_6( id INT UNSIGNED NOT NULL AUTO_INCREMENT, testField1 INT(11) NOT NULL, testField2 INT(10) UNSIGNED NOT NULL, testField3 float DEFAULT NULL, INDEX a_very_long_index_name_1(testField2), INDEX a_very_long_index_name_2(testField1, testField2), PRIMARY KEY (id) ); CREATE TABLE IF NOT EXISTS test_table_7( id INT UNSIGNED NOT NULL AUTO_INCREMENT, testField1 INT(11) NOT NULL, testField2 INT(10) UNSIGNED NOT NULL, testField3 DATETIME DEFAULT NULL, INDEX an_extremely_long_index_name_which_will_help_to_cause_an_issue_1(testField2), INDEX an_extremely_long_index_name_which_will_help_to_cause_an_issue_2(testField1, testField2), PRIMARY KEY (id) ); CREATE TABLE IF NOT EXISTS test_table_8( id INT UNSIGNED NOT NULL AUTO_INCREMENT, testField1 INT(11) NOT NULL, testField2 INT(10) UNSIGNED NOT NULL, testField3 BOOLEAN DEFAULT NULL, INDEX an_extremely_long_index_name_which_will_help_to_cause_an_issue_3(testField2), INDEX an_extremely_long_index_name_which_will_help_to_cause_an_issue_4(testField1, testField2), PRIMARY KEY (id) ); EXPLAIN FORMAT=JSON SELECT * FROM test_table_1 t1 INNER JOIN test_table_2 t2 ON t2.testField1 = t1.testField1 INNER JOIN test_table_3 t3 ON t3.testField1 = t1.testField1 INNER JOIN test_table_4 t4 ON t4.testField2 = t2.testField2 AND t4.testField5 < now() LEFT JOIN test_table_5 t5 ON t1.testField2 = 1 AND t5.testField1 = t4.testField1 AND t5.testField2 = t3.id LEFT JOIN test_table_6 t6 ON t1.testField2 = 2 AND t6.testField1 = t4.testField1 AND t6.testField2 = t3.id LEFT JOIN test_table_7 t7 ON t1.testField2 = 3 AND t7.testField1 = t4.testField1 AND t7.testField2 = t3.id LEFT JOIN test_table_8 t8 ON t1.testField2 = 4 AND t8.testField1 = t4.testField1 AND t8.testField2 = t3.id GROUP BY t1.testField1;