In-memory join hangs forever when primary table has rows and secondary table has no rows

[andremarianiello]

Title explains the entire issue. I think the root cause is

go-mysql-server/sql/plan/join.go

Lines 390 to 392 in 42581cb

	if len(i.secondaryRows.Get()) == 0 {
	return io.EOF
	}

. When EOF is returned by that line loadSecondary immediately returns that EOF without resetting primaryRow or pos. This causes the joinIter to get stuck on this row forever and fetch the secondary table RowIter infinite times. I'm not sure why that EOF behavior is necessary. Additionally, even if that were fixed, the secondary table RowIter would be fetch once per primary row, which it shouldn't have to do. This is happening because the number of rows in the row cache is being used to determine if the row cache has been filled yet, but if the cache was filled with an empty table it behaves as if it was uninitialized, so it keeps trying to fill it over and over. This can be expensive for tables that fetch rows via slow operations, like network requests, even if the number of rows is 0.

[zachmu]

Thanks for the report, @andremarianiello. Sorry for not responding sooner!

This is definitely a bug. We'll fix in the next release.