Adding STRAIGHT_JOIN to certain queries results in loss of returned rows

[Patrick Crews]

Adding STRAIGHT_JOIN to certain queries will result in a change (loss of rows) in the returned results.
I'm still doing research on this, but am documenting it now. Will update this bug with more information as it is discovered.

Results from the test case below:
SELECT COUNT(table1 .`col_int`)
FROM g table1 RIGHT JOIN e table2 LEFT JOIN m table3 LEFT JOIN a table5 ON table5 .`col_int_key` ON table2 .`col_int_key` = table3 .`col_int` ON table2 .`pk` ;
COUNT(table1 .`col_int`)
27
SELECT STRAIGHT_JOIN COUNT(table1 .`col_int`)
FROM g table1 RIGHT JOIN e table2 LEFT JOIN m table3 LEFT JOIN a table5 ON table5 .`col_int_key` ON table2 .`col_int_key` = table3 .`col_int` ON table2 .`pk` /* TRANSFORM_OUTCOME_UNORDERED_MATCH */;
COUNT(table1 .`col_int`)
15

Test_suite test case:
#/* Server0: Drizzle 2010.05.1561 */

--disable_warnings
DROP TABLE IF EXISTS `g`;
CREATE TABLE `g` (
  `pk` int NOT NULL AUTO_INCREMENT,
  `col_int` int DEFAULT NULL,
  PRIMARY KEY (`pk`)
) ENGINE=InnoDB;

ALTER TABLE `g` DISABLE KEYS;
INSERT INTO `g` VALUES (1,NULL),(2,1607860224),(3,NULL),(4,822673408),(5,1907556352),(6,NULL),(7,NULL),(8,NULL);
ALTER TABLE `g` ENABLE KEYS;

DROP TABLE IF EXISTS `e`;
CREATE TABLE `e` (
  `col_int_key` int DEFAULT NULL,
  `pk` int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`pk`),
  KEY `col_int_key` (`col_int_key`)
) ENGINE=InnoDB;

ALTER TABLE `e` DISABLE KEYS;
INSERT INTO `e` VALUES (NULL,1),(1862205440,2),(1,3),(-517472256,4),(1341587456,5),(5,6);
ALTER TABLE `e` ENABLE KEYS;

DROP TABLE IF EXISTS `m`;
CREATE TABLE `m` (
  `col_int` int DEFAULT NULL,
  `pk` int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`pk`)
) ENGINE=InnoDB;

ALTER TABLE `m` DISABLE KEYS;
INSERT INTO `m` VALUES (1,1),(NULL,2),(NULL,3),(-207224832,4),(714801152,5),(-1265106944,6),(2023948288,7),(865599488,8),(1,9),(-1895301120,10),(3,11),(9,12),(5,13),(NULL,14),(NULL,15),(1,16),(-224657408,17),(2,18),(-1219493888,19),(7,20),(1,21),(NULL,22),(NULL,23);
ALTER TABLE `m` ENABLE KEYS;

DROP TABLE IF EXISTS `a`;
CREATE TABLE `a` (
  `col_int_key` int DEFAULT NULL,
  `pk` int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`pk`),
  KEY `col_int_key` (`col_int_key`)
) ENGINE=InnoDB;

ALTER TABLE `a` DISABLE KEYS;
ALTER TABLE `a` ENABLE KEYS;
--enable_warnings

#/* Begin test case for query 0 */

SELECT COUNT(table1 .`col_int`)
FROM g table1 RIGHT JOIN e table2 LEFT JOIN m table3 LEFT JOIN a table5 ON table5 .`col_int_key` ON table2 .`col_int_key` = table3 .`col_int` ON table2 .`pk` ;

#/* Query plan Server 0:
# |1|SIMPLE|table2|index||PRIMARY|4||6||
# |1|SIMPLE|table3|ALL|||||23||
# |1|SIMPLE|table5|index||PRIMARY|4||1||
# |1|SIMPLE|table1|ALL|||||8||
# */

#/* End of test case for query 0 */

#/* Begin test case for query 1 */

SELECT STRAIGHT_JOIN COUNT(table1 .`col_int`)
FROM g table1 RIGHT JOIN e table2 LEFT JOIN m table3 LEFT JOIN a table5 ON table5 .`col_int_key` ON table2 .`col_int_key` = table3 .`col_int` ON table2 .`pk` /* TRANSFORM_OUTCOME_UNORDERED_MATCH */;

#/* Query plan Server 0:
# |1|SIMPLE|table2|index||PRIMARY|4||6||
# |1|SIMPLE|table3|ALL|||||23||
# |1|SIMPLE|table1|ALL|||||8||
# |1|SIMPLE|table5|index||PRIMARY|4||1||
# */

#/* End of test case for query 1 */

DROP TABLE g;
DROP TABLE e;
DROP TABLE m;
DROP TABLE a;

[Patrick Crews (patrick-crews)]

TODO:
* Determine correct result set - I have also seen cases where STRAIGHT_JOIN had the reverse effect - it adds a LOT of duplicate rows (This isn't written up yet, still researching that one as well, will link to this bug once it's logged)
* Code research - I will try to offer up where the buggy code might be when time permits.

[Brian Aker (brianaker)]

Thanks!

On May 25, 2010, at 3:46 PM, Patrick Crews wrote:

> TODO:
> * Determine correct result set - I have also seen cases where STRAIGHT_JOIN had the reverse effect - it adds a LOT of duplicate rows (This isn't written up yet, still researching that one as well, will link to this bug once it's logged)
> * Code research - I will try to offer up where the buggy code might be when time permits.
>
> --
> Adding STRAIGHT_JOIN to certain queries results in loss of returned rows
> https://bugs.launchpad.net/bugs/585628
> You received this bug notification because you are a member of Drizzle-
> developers, which is subscribed to Drizzle.
>
> Status in A Lightweight SQL Database for Cloud and Web: New
>
> Bug description:
> Adding STRAIGHT_JOIN to certain queries will result in a change (loss of rows) in the returned results.
> I'm still doing research on this, but am documenting it now. Will update this bug with more information as it is discovered.
>
> Results from the test case below:
> SELECT COUNT(table1 .`col_int`)
> FROM g table1 RIGHT JOIN e table2 LEFT JOIN m table3 LEFT JOIN a table5 ON table5 .`col_int_key` ON table2 .`col_int_key` = table3 .`col_int` ON table2 .`pk` ;
> COUNT(table1 .`col_int`)
> 27
> SELECT STRAIGHT_JOIN COUNT(table1 .`col_int`)
> FROM g table1 RIGHT JOIN e table2 LEFT JOIN m table3 LEFT JOIN a table5 ON table5 .`col_int_key` ON table2 .`col_int_key` = table3 .`col_int` ON table2 .`pk` /* TRANSFORM_OUTCOME_UNORDERED_MATCH */;
> COUNT(table1 .`col_int`)
> 15
>
>
> Test_suite test case:
> #/* Server0: Drizzle 2010.05.1561 */
>
> --disable_warnings
> DROP TABLE IF EXISTS `g`;
> CREATE TABLE `g` (
> `pk` int NOT NULL AUTO_INCREMENT,
> `col_int` int DEFAULT NULL,
> PRIMARY KEY (`pk`)
> ) ENGINE=InnoDB;
>
> ALTER TABLE `g` DISABLE KEYS;
> INSERT INTO `g` VALUES (1,NULL),(2,1607860224),(3,NULL),(4,822673408),(5,1907556352),(6,NULL),(7,NULL),(8,NULL);
> ALTER TABLE `g` ENABLE KEYS;
>
> DROP TABLE IF EXISTS `e`;
> CREATE TABLE `e` (
> `col_int_key` int DEFAULT NULL,
> `pk` int NOT NULL AUTO_INCREMENT,
> PRIMARY KEY (`pk`),
> KEY `col_int_key` (`col_int_key`)
> ) ENGINE=InnoDB;
>
> ALTER TABLE `e` DISABLE KEYS;
> INSERT INTO `e` VALUES (NULL,1),(1862205440,2),(1,3),(-517472256,4),(1341587456,5),(5,6);
> ALTER TABLE `e` ENABLE KEYS;
>
> DROP TABLE IF EXISTS `m`;
> CREATE TABLE `m` (
> `col_int` int DEFAULT NULL,
> `pk` int NOT NULL AUTO_INCREMENT,
> PRIMARY KEY (`pk`)
> ) ENGINE=InnoDB;
>
> ALTER TABLE `m` DISABLE KEYS;
> INSERT INTO `m` VALUES (1,1),(NULL,2),(NULL,3),(-207224832,4),(714801152,5),(-1265106944,6),(2023948288,7),(865599488,8),(1,9),(-1895301120,10),(3,11),(9,12),(5,13),(NULL,14),(NULL,15),(1,16),(-224657408,17),(2,18),(-1219493888,19),(7,20),(1,21),(NULL,22),(NULL,23);
> ALTER TABLE `m` ENABLE KEYS;
>
> DROP TABLE IF EXISTS `a`;
> CREATE TABLE `a` (
> `col_int_key` int DEFAULT NULL,
> `pk` int NOT NULL AUTO_INCREMENT,
> PRIMARY KEY (`pk`),
> KEY `col_int_key` (`col_int_key`)
> ) ENGINE=InnoDB;
>
> ALTER TABLE `a` DISABLE KEYS;
> ALTER TABLE `a` ENABLE KEYS;
> --enable_warnings
>
> #/* Begin test case for query 0 */
>
>
>
>
> SELECT COUNT(table1 .`col...

[Patrick Crews (patrick-crews)]

Also see:
https://bugs.launchpad.net/drizzle/+bug/585644 - Adding STRAIGHT_JOIN to certain queries results in a large number of duplicate rows

[Patrick Crews (patrick-crews)]

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)

[Patrick Crews (patrick-crews)]

Also present in MySQL 5.1

[PrafullaT (prafulla-t)]

Analysis:

Fix for Bug 585644,585628

There was problem in dependency flags setting in a table which is
used to sort tables automatically in case of straght_join option.
These dependency flags are set in simplify_join method.
For a case like (a join b) LEFT JOIN c, c was not getting set to
depend on both a AND b, it was getting set only for either a or b,
as specified in original query.
Fixed it properly. Both bug queries now works fine and
also tested some subquery tests, all looks clean.

[PrafullaT (prafulla-t)]

Hi Patrick,
I have committed fix for this bug at following branch
https://code.launchpad.net/~prafulla-tekawade/drizzle/drz-bugfix-585644_585628

Could you please take a look at it ?

-
Thanks
Prafulla

[Patrick Crews (patrick-crews)]

Tested the patch with the randgen (result set + execution time comparisons).
Bug is fixed via the patch - randgen testing looked good.