Bug in Suggestions

MrJonezMrJonez Posts: 11 Bronze 1
in SQL Prompt
Hi,
It's something with SQL Prompts suggestions (intellisense) with the below query.
The Suggestions works in another document/tab without restarting anything. The only suggestions is from snippets.
Below I have started to write LEFT OUTER JOIN on line 77, but it's impossible to write anything since I cannot press space without the snippets to be inserted. So I have to press ESC the whole time when I write on the query, or turn off the suggestions (which I did), to be able to write anything.


I have of course, restarted SSMS (many times), but the same problem occurs directly from start.

I can send the query on request.
SQL Prompt 10.11.5.26913
SSMS 18.10

Best Regards John


Tagged:
· Share on Twitter

Answers

  • Dan_JDan_J Posts: 459 Silver 2
    Hi @MrJonez

    Thanks for reaching out to us regarding this!

    I've so far not been able to reproduce what you are seeing here, however I am continuing to try to.

    In the mean time, can you confirm if this happens every time you start to type LEFT OUTER JOIN? Or only at certain points in your code? For example, did it happen on line 38 also?
    Kind regards

    Dan Jary | Redgate Software
    Have you visited our Help Center?
    · Share on Twitter
  • MrJonezMrJonez Posts: 11 Bronze 1
    Hi,
    I just tried, and if I start typing after line 38 it works.
    It seems like its the CROSS APPLY start starts at line 39 that does this.
    If I try after the CROSS APPLY is ended, it has stopped to work.

    The CROSS APPLY SQL:
    <div>CROSS APPLY (</div><div>	SELECT TOP 1</div><div>		T2.testIncId,t2.testSqlId,t2.testCode,t2.testName,t2.flags32,T2.isHeadOfClusterTest,</div><div>		P2.partnerCode,P2.externalName externalPartnerName,</div><div>		TPWD2.testPartnerWhoDoIncId,TPWD2.testPartnerWhoDoSqlId</div><div>	FROM dbo.Tests AS T2 WITH (NOLOCK)</div><div>	JOIN dbo.TestsPartnersWhoDo AS TPWD2 WITH (NOLOCK) ON TPWD2.testIncId = T2.testIncId AND TPWD2.testSqlId = T2.testSqlId AND TPWD2.isDeleted=0x0</div><div>	JOIN dbo.Partners AS P2 WITH (NOLOCK) ON P2.partnerIncId = TPWD2.partnerIncId AND P2.partnerSqlId = TPWD2.partnerSqlId</div><div>	WHERE t2.testIncId=tr.testIncId AND t2.testSqlId=tr.testSqlId AND T2.isDeleted=0x0</div><div>		AND ISNULL(t2.isHeadOfClusterTest,0)=0</div><div>	ORDER BY CASE WHEN P2.partnerCode IN ('EUSELI2','EUAA88','EUSE02','EUSEOCX','EUSEUP','EUSEST') THEN 0 ELSE 1 END</div><div><br></div><div>	UNION</div><div><br></div><div>	SELECT TOP 1</div><div>		T2.testIncId,t2.testSqlId,t2.testCode,t2.testName,t2.flags32,T2.isHeadOfClusterTest,</div><div>		P2.partnerCode,P2.externalName externalPartnerName,</div><div>		TPWD2.testPartnerWhoDoIncId,TPWD2.testPartnerWhoDoSqlId</div><div>	FROM dbo.TestsTestsClusters AS TTCl WITH (NOLOCK)</div><div>	JOIN dbo.Tests AS T2 WITH (NOLOCK) ON T2.testIncId = TTCl.testIncId AND T2.testSqlId = TTCl.testSqlId AND T2.isDeleted=0x0</div><div>	JOIN dbo.TestsPartnersWhoDo AS TPWD2 WITH (NOLOCK) ON TPWD2.testIncId = T2.testIncId AND TPWD2.testSqlId = T2.testSqlId AND TPWD2.isDeleted=0x0</div><div>	JOIN dbo.Partners AS P2 WITH (NOLOCK) ON P2.partnerIncId = TPWD2.partnerIncId AND P2.partnerSqlId = TPWD2.partnerSqlId</div><div>	WHERE ttcl.headOfClusterTestIncId=Tr.testIncId AND TTCl.headOfClusterTestSqlId=Tr.testSqlId AND TTCl.isDeleted=0x0</div><div>		AND ISNULL(t2.isHeadOfClusterTest,0)=0</div><div>	ORDER BY CASE WHEN P2.partnerCode IN ('EUSELI2','EUAA88','EUSE02','EUSEOCX','EUSEUP','EUSEST') THEN 0 ELSE 1 END	</div><div>) T</div><br>

    · Share on Twitter
  • MrJonezMrJonez Posts: 11 Bronze 1
    CROSS APPLY (
    SELECT TOP 1
    T2.testIncId,t2.testSqlId,t2.testCode,t2.testName,t2.flags32,T2.isHeadOfClusterTest,
    P2.partnerCode,P2.externalName externalPartnerName,
    TPWD2.testPartnerWhoDoIncId,TPWD2.testPartnerWhoDoSqlId
    FROM dbo.Tests AS T2 WITH (NOLOCK)
    JOIN dbo.TestsPartnersWhoDo AS TPWD2 WITH (NOLOCK) ON TPWD2.testIncId = T2.testIncId AND TPWD2.testSqlId = T2.testSqlId AND TPWD2.isDeleted=0x0
    JOIN dbo.Partners AS P2 WITH (NOLOCK) ON P2.partnerIncId = TPWD2.partnerIncId AND P2.partnerSqlId = TPWD2.partnerSqlId
    WHERE t2.testIncId=tr.testIncId AND t2.testSqlId=tr.testSqlId AND T2.isDeleted=0x0
    AND ISNULL(t2.isHeadOfClusterTest,0)=0
    ORDER BY CASE WHEN P2.partnerCode IN ('EUSELI2','EUAA88','EUSE02','EUSEOCX','EUSEUP','EUSEST') THEN 0 ELSE 1 END

    UNION

    SELECT TOP 1
    T2.testIncId,t2.testSqlId,t2.testCode,t2.testName,t2.flags32,T2.isHeadOfClusterTest,
    P2.partnerCode,P2.externalName externalPartnerName,
    TPWD2.testPartnerWhoDoIncId,TPWD2.testPartnerWhoDoSqlId
    FROM dbo.TestsTestsClusters AS TTCl WITH (NOLOCK)
    JOIN dbo.Tests AS T2 WITH (NOLOCK) ON T2.testIncId = TTCl.testIncId AND T2.testSqlId = TTCl.testSqlId AND T2.isDeleted=0x0
    JOIN dbo.TestsPartnersWhoDo AS TPWD2 WITH (NOLOCK) ON TPWD2.testIncId = T2.testIncId AND TPWD2.testSqlId = T2.testSqlId AND TPWD2.isDeleted=0x0
    JOIN dbo.Partners AS P2 WITH (NOLOCK) ON P2.partnerIncId = TPWD2.partnerIncId AND P2.partnerSqlId = TPWD2.partnerSqlId
    WHERE ttcl.headOfClusterTestIncId=Tr.testIncId AND TTCl.headOfClusterTestSqlId=Tr.testSqlId AND TTCl.isDeleted=0x0
    AND ISNULL(t2.isHeadOfClusterTest,0)=0
    ORDER BY CASE WHEN P2.partnerCode IN ('EUSELI2','EUAA88','EUSE02','EUSEOCX','EUSEUP','EUSEST') THEN 0 ELSE 1 END
    ) T
    · Share on Twitter
  • Dan_JDan_J Posts: 459 Silver 2
    Hi @MrJonez

    My apology for the delay in coming back to you on this!

    I am continuing to try and replicate what you are seeing here (using the code you have provided), but have so far been unsuccessful.

    By default the letters 'loj' are assigned to call the LEFT OUTER JOIN snippet, is this the case for you? 

    Also, you should have to press the TAB key in order to insert a snippet, however you are reporting that the snippet is inserted when you press SPACE. 

    Finally, did this issue only start to occur since you updated SQL Prompt? And, do you continue to see this issue in the latest version of SQL Prompt: https://download.red-gate.com/checkforupdates/SQLPrompt/SQLPrompt_10.11.6.27098.exe? 
    Kind regards

    Dan Jary | Redgate Software
    Have you visited our Help Center?
    · Share on Twitter
  • MrJonezMrJonez Posts: 11 Bronze 1
    Hi,
    I tried again with the latest version (10.11.6.27098), but it's the exact same behaviour.
    This is the first time I experience this bug, and only for this query (attached).

    The loj snippet exists for me, but I have added my own that starts with the word "left".
    But I dont think that matters here. And the settings was that space bar inserts the suggestion, so no bug here. The remaining problem is that the suggestions only includes snippets.



    Thanks for your help
    BR John
    · Share on Twitter
  • Dan_JDan_J Posts: 459 Silver 2
    Hi @MrJonez

    I'm continuing in my attempts to replicate the behavior you are seeing here, however after multiple attempts I have been unable to. I'm sorry this issue continues for you!

    I do however just need to clarify on the issue here, you initially reported that, when typing LEF, you were unable to press space bar without it inserting the selected suggestion, however in your latest update you have mentioned that the issue is that only snippets are suggested. 

    Regarding the initial issue you mentioned, having space bar selected within the suggestion behavior settings (as shown in the screenshot from your most recent update) will be the cause of this. Please do correct me if I am misunderstanding this.

    Finally, are there any other examples of syntax where you see this issue?

    Any clarification you can give on this would be greatly appreciated.
    Kind regards

    Dan Jary | Redgate Software
    Have you visited our Help Center?
    · Share on Twitter
  • MrJonezMrJonez Posts: 11 Bronze 1
    Hi,
    Sorry for the missunderstanding. My issue is that the Suggestions stop working and only snippets are suggested. For me, this has only happened with this specific query.

    I had problems to attach the .sql file in my previous post. Nothing happened after I selected the file, so I paste the query below.
    Thanks
    Best Regards John

    SELECT 
    QuoSG.quotationSubGroupName,QuoL.quotationLineText,
    Pck.packageCode,
    THEad.testCode testCluster,
    T.testCode, ISNULL(ttr.testTranslationName,T.testName) testName,
    Par.parameterCode,COALESCE(ParTTr.parameterTestTranslationName,ParT.parameterTestName,ParTr.parameterTranslationName,Par.parameterName) parameterName,
    ParT.limitOfQuantification LOQ,
    dbo.eufn_e5_eLIMS_SE_FormatUnit(ParT.detectionAndQuantificationUnitNumeratorSqlId,ParT.detectionAndQuantificationUnitNumeratorIncId, ParT.detectionAndQuantificationUnitDenominatorSqlId,ParT.detectionAndQuantificationUnitDenominatorIncId,123,1) LOQUnit,

    STUFF((SELECT '; ' + ISNULL(ProTr.productTranslationName, Pro.productName) + ': ' + REPLACE(CAST(ParTPro.limitOfQuantification AS NVARCHAR(30)),'.',',') 
    + ' ' + dbo.eufn_e5_eLIMS_SE_FormatUnit(ParTPro.detectionAndQuantificationUnitNumeratorSqlId,ParTPro.detectionAndQuantificationUnitNumeratorIncId, ParTPro.detectionAndQuantificationUnitDenominatorSqlId,ParTPro.detectionAndQuantificationUnitDenominatorIncId,123,1)
    FROM dbo.ParametersTestsProducts AS ParTPro WITH (NOLOCK)
    JOIN dbo.Products AS Pro WITH (NOLOCK) ON Pro.productIncId = ParTPro.productIncId AND Pro.productSqlId = ParTPro.productSqlId AND Pro.isDeleted=0x0
    LEFT OUTER JOIN dbo.ProductsTranslations AS ProTr WITH (NOLOCK) ON ProTr.productIncId = Pro.productIncId AND ProTr.productSqlId = Pro.productSqlId AND ProTr.isDeleted=0x0
    AND ProTr.languageIncId=1 AND ProTr.languageSqlId=123
    JOIN dbo.TreesOfProducts AS TOPro WITH (NOLOCK) ON TOPro.treeOfProductIncId = Pro.treeOfProductIncId AND TOPro.treeOfProductSqlId = Pro.treeOfProductSqlId
    WHERE ParTPro.parameterTestIncId=ParT.parameterTestIncId AND ParTPro.parameterTestSqlId=ParT.parameterTestSqlId AND ParTPro.isDeleted=0x0
    AND TOPro.treeOfProductCode='F002'
    AND ParTPro.limitOfQuantification != ParT.limitOfQuantification
    ORDER BY  ISNULL(ProTr.productTranslationName, Pro.productName)
    FOR XML PATH ('')),1,2,'') matrisLOQ,
    ISNULL(StdTr.standardTranslationName, std.standardName) method,
    t.externalPartnerName performedBy
    FROM dbo.Quotations AS Quo WITH (NOLOCK)
    JOIN dbo.QuotationsSubGroups AS QuoSG WITH (NOLOCK) ON QuoSG.quotationIncId = Quo.quotationIncId AND QuoSG.quotationSqlId = Quo.quotationSqlId AND QuoSG.isDeleted=0x0
    JOIN dbo.QuotationsSubGroupsQuotationsLines AS QuoSGQL WITH (NOLOCK) ON QuoSGQL.quotationSubGroupIncId = QuoSG.quotationSubGroupIncId AND QuoSGQL.quotationSubGroupSqlId = QuoSG.quotationSubGroupSqlId AND QuoSGQL.isDeleted=0x0
    JOIN dbo.QuotationsLines AS QuoL WITH (NOLOCK) ON QuoL.quotationLineIncId = QuoSGQL.quotationLineIncId AND QuoL.quotationLineSqlId = QuoSGQL.quotationLineSqlId AND QuoL.isDeleted=0x0
    LEFT OUTER JOIN dbo.Packages AS Pck WITH (NOLOCK) ON Pck.packageIncId = QuoL.packageIncId AND Pck.packageSqlId = QuoL.packageSqlId
    CROSS APPLY (
    SELECT QuoL.testIncId, QuoL.testSqlId
    WHERE QuoL.testIncId IS NOT NULL
    UNION
    SELECT TF.testIncId, TF.testSqlId
    FROM dbo.PackagesTestsFractions AS PckTF WITH (NOLOCK)
    JOIN dbo.TestsFractions AS TF WITH (NOLOCK) ON TF.testFractionIncId = PckTF.testFractionIncId AND TF.testFractionSqlId = PckTF.testFractionSqlId AND TF.isDeleted=0x0
    WHERE PckTF.packageIncId=QuoL.packageIncId AND PckTF.packageSqlId=QuoL.packageSqlId AND PckTF.isDeleted=0x0
    ) Tr
    LEFT OUTER JOIN dbo.Tests AS THEad WITH (NOLOCK) ON THEad.testIncId=Tr.testIncId AND thead.testSqlId=Tr.testSqlId AND THEad.isDeleted=0x0 AND thead.isHeadOfClusterTest=1
    CROSS APPLY (
    SELECT TOP 1
    T2.testIncId,t2.testSqlId,t2.testCode,t2.testName,t2.flags32,T2.isHeadOfClusterTest,
    P2.partnerCode,P2.externalName externalPartnerName,
    TPWD2.testPartnerWhoDoIncId,TPWD2.testPartnerWhoDoSqlId
    FROM dbo.Tests AS T2 WITH (NOLOCK)
    JOIN dbo.TestsPartnersWhoDo AS TPWD2 WITH (NOLOCK) ON TPWD2.testIncId = T2.testIncId AND TPWD2.testSqlId = T2.testSqlId AND TPWD2.isDeleted=0x0
    JOIN dbo.Partners AS P2 WITH (NOLOCK) ON P2.partnerIncId = TPWD2.partnerIncId AND P2.partnerSqlId = TPWD2.partnerSqlId
    WHERE t2.testIncId=tr.testIncId AND t2.testSqlId=tr.testSqlId AND T2.isDeleted=0x0
    AND ISNULL(t2.isHeadOfClusterTest,0)=0
    ORDER BY CASE WHEN P2.partnerCode IN ('EUSELI2','EUAA88','EUSE02','EUSEOCX','EUSEUP','EUSEST') THEN 0 ELSE 1 END

    UNION

    SELECT TOP 1
    T2.testIncId,t2.testSqlId,t2.testCode,t2.testName,t2.flags32,T2.isHeadOfClusterTest,
    P2.partnerCode,P2.externalName externalPartnerName,
    TPWD2.testPartnerWhoDoIncId,TPWD2.testPartnerWhoDoSqlId
    FROM dbo.TestsTestsClusters AS TTCl WITH (NOLOCK)
    JOIN dbo.Tests AS T2 WITH (NOLOCK) ON T2.testIncId = TTCl.testIncId AND T2.testSqlId = TTCl.testSqlId AND T2.isDeleted=0x0
    JOIN dbo.TestsPartnersWhoDo AS TPWD2 WITH (NOLOCK) ON TPWD2.testIncId = T2.testIncId AND TPWD2.testSqlId = T2.testSqlId AND TPWD2.isDeleted=0x0
    JOIN dbo.Partners AS P2 WITH (NOLOCK) ON P2.partnerIncId = TPWD2.partnerIncId AND P2.partnerSqlId = TPWD2.partnerSqlId
    WHERE ttcl.headOfClusterTestIncId=Tr.testIncId AND TTCl.headOfClusterTestSqlId=Tr.testSqlId AND TTCl.isDeleted=0x0
    AND ISNULL(t2.isHeadOfClusterTest,0)=0
    ORDER BY CASE WHEN P2.partnerCode IN ('EUSELI2','EUAA88','EUSE02','EUSEOCX','EUSEUP','EUSEST') THEN 0 ELSE 1 END
    ) T

    LEFT OUTER JOIN dbo.TestsTranslations AS TTr WITH (NOLOCK) ON TTr.testIncId = T.testIncId AND TTr.testSqlId = T.testSqlId AND TTr.isDeleted=0x0
    AND ttr.languageIncId=1 AND TTr.languageSqlId=123
    JOIN dbo.ParametersTests AS ParT WITH (NOLOCK) ON ParT.testIncId = T.testIncId AND ParT.testSqlId = T.testSqlId AND ParT.isDeleted=0x0
    LEFT OUTER JOIN dbo.ParametersTestsTranslations AS ParTTr WITH (NOLOCK) ON ParTTr.parameterTestIncId = ParT.parameterTestIncId AND ParTTr.parameterTestSqlId = ParT.parameterTestSqlId AND ParTTr.isDeleted=0x0
    AND ParTTr.languageIncId=1 AND ParTTr.languageSqlId=123
    JOIN dbo.[Parameters] AS Par WITH (NOLOCK) ON Par.parameterIncId = ParT.parameterIncId AND Par.parameterSqlId = ParT.parameterSqlId AND Par.isDeleted=0x0
    LEFT OUTER JOIN dbo.ParametersTranslations AS ParTr WITH (NOLOCK) ON ParTr.parameterIncId = Par.parameterIncId AND ParTr.parameterSqlId = Par.parameterSqlId AND ParTr.isDeleted=0x0
    AND ParTr.languageIncId=1 AND ParTr.languageSqlId=123
    LEFT OUTER JOIN TestsPartnersWhoDoStandards TPWDStd WITH (NOLOCK) ON TPWDStd.testPartnerWhoDoIncId=T.testPartnerWhoDoIncId AND TPWDStd.testPartnerWhoDoSqlId=T.testPartnerWhoDoSqlId AND TPWDStd.isDeleted=0
    LEFT OUTER JOIN Standards Std WITH (NOLOCK) ON Std.standardIncId=TPWDStd.standardIncId AND Std.standardSqlId=TPWDStd.standardSqlId AND Std.isDeleted=0
    LEFT OUTER JOIN StandardsTranslations StdTr WITH (NOLOCK) ON StdTr.standardIncId=Std.standardIncId AND StdTr.standardSqlId=Std.standardSqlId AND StdTr.isDeleted=0 
    AND StdTr.languageIncId=1 and StdTr.languageSqlId=123
    WHERE Quo.quotationCode='DPMASC22000401'
    AND NOT ((t.flags32&(POWER(2,0)   --Analytical Method Prerequisite Test
    + POWER(2,15) --Logistic or Administrative Service Test
    + POWER(2,16) --Preperation Test
    ))>0)
    ORDER BY QuoSG.orderNumber,QuoL.quotationLineOrder
    · Share on Twitter
  • Dan_JDan_J Posts: 459 Silver 2
    Hi @MrJonez

    Thank you for your last update on this, and for your patience with us!

    We have finally been able to reproduce the behavior you are describing here. I have escalated this to our development team for their input and will come back to you as soon as I have another update for you.
    Kind regards

    Dan Jary | Redgate Software
    Have you visited our Help Center?
    · Share on Twitter
  • MrJonezMrJonez Posts: 11 Bronze 1
    Hi!
    Lovely, thanks. Looking forward to a fix 😊
    · Share on Twitter
  • Dan_JDan_J Posts: 459 Silver 2
    Hi @MrJonez

    Thank you for your patience with us on this while our development team reviewed the bug you highlighted to us. Unfortunately, due to the prioritization of current open issues, the available resources, and looking at the overall impact, on this occasion they are unable to work on a fix for this bug.

    There is a workaround by way of adding a semi-colon to the previous line that causes the suggestions to populate as expected. This appears to work in all cases.

    I'm sorry this isn't the outcome you were looking for here, we do regret that it isn't always possible for us to provide a fix every bug that is highlighted to us.

    Please don’t hesitate in reaching out to us again if you have any questions regarding this. We very much appreciate your understanding on this.
    Kind regards

    Dan Jary | Redgate Software
    Have you visited our Help Center?
    · Share on Twitter
  • MrJonezMrJonez Posts: 11 Bronze 1
    Hi, Thanks for your update. It's no problems. This bug hasn't appeared since this issue was created.
    So I can wait ....  :)
    · Share on Twitter
Sign In or Register to comment.