Comment 2 for bug 585628

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_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;
>
>