Using Raw SQL with inner query with where condition

[tidusjar]

When using Raw Sql with the following query:

SELECT * FROM(SELECT 
    c.id,
        c.SomeProperty,
        c.CreatedBy,
    c.CosmosEntityName
FROM c WHERE c.SomeProperty= @SomeProperty) AS c 
WHERE (CONTAINS(LOWER(c.CreatedBy), LOWER(@CreatedBy0))
 order by c.id desc

This will be executed as

SELECT * FROM(SELECT 
    c.id,
        c.SomeProperty,
        c.CreatedBy,
    c.CosmosEntityName
FROM c where c.CosmosEntityName = 'entityName' and c.SomeProperty= @SomeProperty) AS c

We seem to lose the other where clause and also the order by.

This seems to be down to how we are splitting up the query to inject the CosmosEntityName condition.

[tidusjar]

You can see my change here: https://github.com/tidusjar/Cosmonaut/commit/c911f451df853ee00c73577551f81d015ed22b3e

This fixes my issue, but It doesn't seem right.

[Elfocrash]

Wait a second. I'm pretty sure you cannot have nested select queries in a Cosmos DB SQL query.

[tidusjar]

Nested select queries does seem to work fine on the Azure Portal and returns the expected output. And it also works fine with Cosmonaut with the exception of this current issue

[Elfocrash]

This seems to be a new feature. It wasn’t working some months ago and it isn’t documented anywhere. I will probably have to address this.

[Elfocrash]

Yeah your fix wouldn't work in any other scenario. The fix will have to detect all the queries and inject the appropriate strings with recursion.

[tidusjar]

Not sure if this helps: https://stackoverflow.com/questions/51042600/cosmosdb-subdocument-delselecting-linq-query/51121262#51121262

He is from Cosmos DB Engineering team

[Elfocrash]

Ok so I started looking into this. Can you give me a few example nested select queries without the CosmosEntityName where clause and how you want them to be after the sql parsing?

[tidusjar]

So all the issue really is, is in the nested query we have a where clause, and then in the outer query also has a where clause.

Another example

SELECT * FROM(SELECT 
    c.id,
        c.SomeProperty,
        c.CreatedBy,
        c.UpdateDate,
    c.CosmosEntityName
FROM c WHERE c.CreatedBy = @CreatedBy) AS c 
WHERE c.UpdatedDate >= udf.now()
order by c.id

where the user defined function is:

function now(){
    return new Date();
}

The reason why I am doing things like this is because our internal library (Primary written for MSSQL) is able to apply advanced concepts e.g. multiple column filtering/sorting and be able to generate the SQL for it. So I am using a slightly tweaked version of that internal lib to be able to work with Cosmos DB.

[tidusjar]

Here is another example (More complex):

Original Query:

SELECT *
FROM(
    SELECT
    DISTINCT(c.id),
    c.ReportArea,
    c.ReportConfiguration,
    c.CreatedBy,
    c.CreatedOn,
    c.ReportInstanceName,
    c.ReportName,
    c.ReportType,
    c.StoredReports,
    c.ReportFormat,
    c.BatchId,
    c.CosmosEntityName,
      ARRAY(
          SELECT
          StoredReport.ReportName, 
          StoredReport.CreatedOn, 
          StoredReport.URI, 
          StoredReport.Format, 
          StoredReport.BatchId, 
          StoredReport.ReportInstanceName
          FROM StoredReport in c.StoredReports 
           WHERE (StoredReport.CreatedOn  >= @CreatedOn0 AND StoredReport.CreatedOn  < @CreatedOn1)) AS ReportSchedules 
    FROM c WHERE c.CreatedBy = @Username)
AS c order by c.id

Expected Output:

SELECT *
FROM(
    SELECT
    DISTINCT(c.id),
    c.ReportArea,
    c.ReportConfiguration,
    c.CreatedBy,
    c.CreatedOn,
    c.ReportInstanceName,
    c.ReportName,
    c.ReportType,
    c.StoredReports,
    c.ReportFormat,
    c.BatchId,
    c.CosmosEntityName,
      ARRAY(
          SELECT
          StoredReport.ReportName, 
          StoredReport.CreatedOn, 
          StoredReport.URI, 
          StoredReport.Format, 
          StoredReport.BatchId, 
          StoredReport.ReportInstanceName
          FROM StoredReport in c.StoredReports 
            WHERE (StoredReport.CreatedOn  >= @CreatedOn0 AND StoredReport.CreatedOn  < @CreatedOn1)) AS ReportSchedules 
    FROM c  WHERE c.CosmosEntityName = 'reportschedules' AND c.CreatedBy = @Username)
AS c order by c.id