It has been determined that adding the STRAIGHT_JOIN to the query is producing an incorrect result set.
I removed table `a` (table 5) from the query above and the result sets are identical:
SELECT COUNT(table1 .`col_int`)FROM g table1 RIGHT JOIN e table2 LEFT JOIN m table3 ON table2 .`col_int_key` = table3 .`col_int` ON table2 .`pk` /* TRANSFORM_OUTCOME_UNORDERED_MATCH */;
COUNT(table1 .`col_int`)
27
SELECT STRAIGHT_JOIN COUNT(table1 .`col_int`)
FROM g table1 RIGHT JOIN e table2 LEFT JOIN m table3 ON table2 .`col_int_key` = table3 .`col_int` ON table2 .`pk` /* TRANSFORM_OUTCOME_UNORDERED_MATCH */;
COUNT(table1 .`col_int`)
27
Also, as table `a` is empty and we are doing a LEFT JOIN against it, we should end up with 27 rows still. Thus the STRAIGHT_JOIN addition is causing the issues.
As noted in the referenced MySQL bug, this is Innodb-only.
Next steps will be to provide a code slice html report that highlights the difference in executed code between these two queries (using STRAIGHT_JOIN vs. not using it)
It has been determined that adding the STRAIGHT_JOIN to the query is producing an incorrect result set.
I removed table `a` (table 5) from the query above and the result sets are identical:
SELECT COUNT(table1 .`col_int`)FROM g table1 RIGHT JOIN e table2 LEFT JOIN m table3 ON table2 .`col_int_key` = table3 .`col_int` ON table2 .`pk` /* TRANSFORM_ OUTCOME_ UNORDERED_ MATCH */; OUTCOME_ UNORDERED_ MATCH */;
COUNT(table1 .`col_int`)
27
SELECT STRAIGHT_JOIN COUNT(table1 .`col_int`)
FROM g table1 RIGHT JOIN e table2 LEFT JOIN m table3 ON table2 .`col_int_key` = table3 .`col_int` ON table2 .`pk` /* TRANSFORM_
COUNT(table1 .`col_int`)
27
Also, as table `a` is empty and we are doing a LEFT JOIN against it, we should end up with 27 rows still. Thus the STRAIGHT_JOIN addition is causing the issues.
As noted in the referenced MySQL bug, this is Innodb-only.
Next steps will be to provide a code slice html report that highlights the difference in executed code between these two queries (using STRAIGHT_JOIN vs. not using it)