Duplicate join condition and missing database_id conditions

[markkeyworth]

Version of the script 4.7 and 20240701

What is the current behavior? Current behavior is that the wrong query_sql_text can be returned by sp_QuickieStore when @get_all_databases = 1

If the current behavior is a bug, please provide the steps to reproduce. Have a query across 2 different databases which causes the query in lines 7008 to 7016 to cross join and the top 1 can pick the wrong query text. There are 2 qsq.query_id = qsp.query_id join conditions and its missing database_id conditions on #query_store_query and #query_store_query_text. The #query_store_query_text filter does exist in a similar version of the syntax on line 7457.

What is the expected behavior? The query text is only returned from the correct object and not another object in another database.

Which versions of SQL Server and which OS are affected by this issue? Did this work in previous versions of our procedures? SQL Server 2019 CU26 Enterprise Edition. OS is Windows Server 2016

IMPORTANT: If you're going to contribute code, please read the contributing guide first. https://github.com/erikdarlingdata/DarlingData/blob/main/CONTRIBUTING.md

[erikdarlingdata]

@markkeyworth thanks for reporting this! It seems like you've identified all the moving parts -- are you planning on contributing a fix for the issue?

[markkeyworth]

@erikdarlingdata the PR #443 should resolve this issue. It looks correct in my limited test data.

[erikdarlingdata]

@markkeyworth awesome, thank you! Really appreciate the help here.