2 Replies Latest reply on Dec 3, 2019 9:22 PM by gdiamond_uen

    CTE WITH clause is not working

    gdiamond_uen

      I am trying to write an SWQL query that uses a CTE WITH clause.  I know it should be possible as I've found many examples on Thwack showing that it's possible.  For the life of me, I cannot get any of the examples to work in either SWQL Studio or the web-based Report Writer.  When I run an example I get the errors "mismatched input 'WITH' expecting 'SELECT'" and "* Query is not valid", respectively.

       

      Thwack examples

      Custom SQL Report - Top Alerts, Top Offender

      SQL Report Queries - can use CTEs?

      Using Common Table Expressions (CTE) in SQL Reports

       

      My test environment

      Windows Server 2019 Datacenter Evaluation

      VirtualBox

      Orion Platform HF1, NPM: 2019.4 (Evaluation)

      SWQL Studio 2.6.0.227

       

      I have come up with a trivial example.  In this query, I am selecting from a subquery instead of a table.  This works but does not use WITH.

      SELECT
          AllNodes.DisplayName
      FROM
          (
              SELECT
                  data.Caption AS DisplayName
              FROM
                  orion.nodes AS data ) AS AllNodes;
      
      
      

       

      Which returns using SWQL Studio

      DisplayName
      vagrant

       

      I then rewrite the example using WITH. And yes, it starts with a semicolon.

      ;WITH
          AllNodes
          (
              DisplayName
          ) AS
          (
              SELECT
                  data.Caption AS DisplayName
              FROM
                  orion.nodes As data
          )
      SELECT
          AllNodes.DisplayName
      FROM
          AllNodes;

      SWQL Studio displays

      Error:  mismatched input 'WITH' expecting 'SELECT'

      and logs

      2019-11-26 14:38:25,674 [72] ERROR SolarWinds.InformationService.Core.InformationService - SWQL Studio (null) Exception running query:
      ;WITH
          AllNodes
          (
              DisplayName
          ) AS
          (
              SELECT
                  data.Caption AS DisplayName
              FROM
                  orion.nodes AS data
          )
      SELECT
          AllNodes.DisplayName
      FROM
          AllNodes;
       RETURN XML RAW
      2019-11-26 14:38:25,674 [72] ERROR SolarWinds.InformationService.Core.InformationService - SWQL Studio (null) Exception for Operation: <s:Envelope xmlns:s="http://www.w3.org/2003/05/soap-envelope" xmlns:a="http://www.w3.org/2005/08/addressing" xmlns:u="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd">
        <s:Header>
          <a:Action s:mustUnderstand="1">http://schemas.solarwinds.com/2007/08/informationservice/InformationService/QueryXml</a:Action>
          <a:MessageID>urn:uuid:a45b57ed-7116-46ab-aed7-ff4f05792c45</a:MessageID>
          <a:ReplyTo>
            <a:Address>http://www.w3.org/2005/08/addressing/anonymous</a:Address>
          </a:ReplyTo>
          <i18n xmlns="http://schemas.solarwinds.com/2007/08/informationservice" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
            <Culture>en-US</Culture>
          </i18n>
          <SwisSettings xmlns="http://schemas.solarwinds.com/2007/08/informationservice" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
            <AppendErrors>true</AppendErrors>
            <ApplicationTag>SWQL Studio</ApplicationTag>
            <DataProviderTimeout>PT30S</DataProviderTimeout>
          </SwisSettings>
          <SwisProtocolVersion xmlns="http://schemas.solarwinds.com/2007/08/informationservice">2</SwisProtocolVersion>
          <IsBase64EncodingAccepted xmlns="http://schemas.solarwinds.com/2007/08/informationservice">true</IsBase64EncodingAccepted>
          <a:To s:mustUnderstand="1">net.tcp://localhost:17777/SolarWinds/InformationService/v3/Orion/ssl</a:To>
          <o:Security s:mustUnderstand="1" xmlns:o="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd">
            <u:Timestamp u:Id="_0">
              <u:Created>2019-11-26T22:38:25.660Z</u:Created>
              <u:Expires>2019-11-26T22:43:25.660Z</u:Expires>
            </u:Timestamp>
            <c:SecurityContextToken u:Id="uuid-5a226932-69d5-479e-b174-b4b9a6f1a6be-4" xmlns:c="http://schemas.xmlsoap.org/ws/2005/02/sc">
              <c:Identifier>urn:uuid:f6ec8c11-f691-43b6-9780-e6f562ca6465</c:Identifier>
            </c:SecurityContextToken>
            <Signature xmlns="http://www.w3.org/2000/09/xmldsig#">
              <SignedInfo>
                <CanonicalizationMethod Algorithm="http://www.w3.org/2001/10/xml-exc-c14n#">
                </CanonicalizationMethod>
                <SignatureMethod Algorithm="http://www.w3.org/2000/09/xmldsig#hmac-sha1">
                </SignatureMethod>
                <Reference URI="#_0">
                  <Transforms>
                    <Transform Algorithm="http://www.w3.org/2001/10/xml-exc-c14n#">
                    </Transform>
                  </Transforms>
                  <DigestMethod Algorithm="http://www.w3.org/2000/09/xmldsig#sha1">
                  </DigestMethod>
                  <DigestValue>uLT+lm8roVZpwyq79tSa+z8sVyU=</DigestValue>
                </Reference>
              </SignedInfo>
              <SignatureValue>0Sgq/gDRbF/UP13VtIUAWaN5Qog=</SignatureValue>
              <KeyInfo>
                <o:SecurityTokenReference>
                  <o:Reference URI="#uuid-5a226932-69d5-479e-b174-b4b9a6f1a6be-4">
                  </o:Reference>
                </o:SecurityTokenReference>
              </KeyInfo>
            </Signature>
          </o:Security>
        </s:Header>
        <s:Body>
          <QueryXml xmlns="http://schemas.solarwinds.com/2007/08/informationservice">
            <query>;WITH
          AllNodes
          (
              DisplayName
          ) AS
          (
              SELECT
                  data.Caption AS DisplayName
              FROM
                  orion.nodes AS data
          )
      SELECT
          AllNodes.DisplayName
      FROM
          AllNodes;
       RETURN XML RAW</query>
            <parameters>
            </parameters>
          </QueryXml>
        </s:Body>
      </s:Envelope>
      2019-11-26 14:38:25,674 [72] ERROR SolarWinds.InformationService.Core.InformationService - SWQL Studio (null) Exception caught in method SolarWinds.InformationService.Core.InformationService.RunQuery
      SolarWinds.Data.SWQL.SWQLSyntaxException: mismatched input 'WITH' expecting 'SELECT' ---> Antlr.Runtime.MismatchedTokenException: A recognition error occurred.
         at SolarWinds.Data.SWQL.Parser.SWQLParser.MismatchIsUnwantedToken(IIntStream input, Int32 ttype)
         at Antlr.Runtime.BaseRecognizer.RecoverFromMismatchedToken(IIntStream input, Int32 ttype, BitSet follow)
         at Antlr.Runtime.BaseRecognizer.Match(IIntStream input, Int32 ttype, BitSet follow)
         at SolarWinds.Data.SWQL.Parser.SWQLParser.selectClause()
         --- End of inner exception stack trace ---
         at SolarWinds.Data.SWQL.Parser.SWQLParser.selectClause()
         at SolarWinds.Data.SWQL.Parser.SWQLParser.selectFrom()
         at SolarWinds.Data.SWQL.Parser.SWQLParser.queryRule()
         at SolarWinds.Data.SWQL.Parser.SWQLParser.selectStatement()
         at SolarWinds.Data.SWQL.Parser.SWQLParser.statement()
         at SolarWinds.Data.SWQL.SWQLParser.Parse(String query)
         at SolarWinds.Data.Query.Engine.QueryProcessor.CompileQueryExecutionPlan(String query, IQueryExecutionContext context)
         at SolarWinds.InformationService.Core.QueryPlanCache.GetQueryPlan(String query, String username, Boolean federationEnabled, Func`1 buildQueryPlan)
         at SolarWinds.InformationService.Core.InformationService.GetQueryExecutionPlan(String query, IIdentity currentIdentity, Boolean federationEnabled, IQueryExecutionContext queryExecutionContext)
         at SolarWinds.InformationService.Core.InformationService.RunQuery(String query, IDictionary`2 parameters, IDataSerializer serializer, IMessageFormatterStrategy messageFormatterStrategy)

       

      I have also created an SQL Fiddle on https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=b7b7773b50eedaa07ef0ebdaf6960597 to show that my examples are valid SQL and produce the same output.

       

      Can anyone shed light on what I am doing wrong?

       

      Message was edited by: Gary Diamond Added CTE to the title. Updated subqueries to select data.Caption instead of data.displayName. Added anchor "data" to "node" in subquery. Updated the fiddle with the same changes.