Hashed LEFT JOIN would miss outer tuples with no inner match if the join

tglsfdc · tglsfdc · commit 44ed69c8f3b3 · 2004-09-17T18:29:40.000Z
was large enough to be batched and the tuples fell into a batch where
there were no inner tuples at all.  Thanks to Xiaoyu Wang for finding a
test case that exposed this long-standing bug.
diff --git a/src/backend/executor/nodeHashjoin.c b/src/backend/executor/nodeHashjoin.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/executor/nodeHashjoin.c,v 1.39 2001/10/25 05:49:28 momjian Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/executor/nodeHashjoin.c,v 1.39.2.1 2004/09/17 18:29:40 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -569,12 +569,14 @@ ExecHashJoinNewBatch(HashJoinState *hjstate)
 	}
 
 	/*
-	 * We can skip over any batches that are empty on either side. Release
-	 * associated temp files right away.
+	 * Normally we can skip over any batches that are empty on either side
+	 * --- but for JOIN_LEFT, can only skip when left side is empty.
+	 * Release associated temp files right away.
 	 */
 	while (newbatch <= nbatch &&
-		   (innerBatchSize[newbatch - 1] == 0L ||
-			outerBatchSize[newbatch - 1] == 0L))
+		   (outerBatchSize[newbatch - 1] == 0L ||
+			(innerBatchSize[newbatch - 1] == 0L &&
+			 hjstate->js.jointype != JOIN_LEFT)))
 	{
 		BufFileClose(hashtable->innerBatchFile[newbatch - 1]);
 		hashtable->innerBatchFile[newbatch - 1] = NULL;

Original file line number	Diff line number	Diff line change
`@@ -8,7 +8,7 @@`
`8`	`8`	`*`
`9`	`9`	`*`
`10`	`10`	`* IDENTIFICATION`
`11`		`- * $Header: /cvsroot/pgsql/src/backend/executor/nodeHashjoin.c,v 1.39 2001/10/25 05:49:28 momjian Exp $`
	`11`	`+ * $Header: /cvsroot/pgsql/src/backend/executor/nodeHashjoin.c,v 1.39.2.1 2004/09/17 18:29:40 tgl Exp $`
`12`	`12`	`*`
`13`	`13`	`*-------------------------------------------------------------------------`
`14`	`14`	`*/`
`@@ -569,12 +569,14 @@ ExecHashJoinNewBatch(HashJoinState *hjstate)`
`569`	`569`	`}`
`570`	`570`
`571`	`571`	`/*`
`572`		`- * We can skip over any batches that are empty on either side. Release`
`573`		`- * associated temp files right away.`
	`572`	`+ * Normally we can skip over any batches that are empty on either side`
	`573`	`+ * --- but for JOIN_LEFT, can only skip when left side is empty.`
	`574`	`+ * Release associated temp files right away.`
`574`	`575`	`*/`
`575`	`576`	`while (newbatch <= nbatch &&`
`576`		`- (innerBatchSize[newbatch - 1] == 0L \|\|`
`577`		`- outerBatchSize[newbatch - 1] == 0L))`
	`577`	`+ (outerBatchSize[newbatch - 1] == 0L \|\|`
	`578`	`+ (innerBatchSize[newbatch - 1] == 0L &&`
	`579`	`+ hjstate->js.jointype != JOIN_LEFT)))`
`578`	`580`	`{`
`579`	`581`	`BufFileClose(hashtable->innerBatchFile[newbatch - 1]);`
`580`	`582`	`hashtable->innerBatchFile[newbatch - 1] = NULL;`