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