Describe the bug
Cosmos db OLTP connector custom query with TOP or Limit clause return wrong number of document
Query1 =SELECT Top 1 FROM c where c.Aggregation='Raw' ORDER by c.TimeStamp desc
Query2 =SELECT FROM c where c.Aggregation='Raw' ORDER by c.TimeStamp desc OFFSET 0 LIMIT 1
Expected to get one result from this query and same query is giving one result when executed from Cosmosdb Data explorer sql query window. but the query is giving 9 result in Azure databricks with OLTP connctor com.azure.cosmos.spark:azure-cosmos-spark_3-1_2-12:4.2.0
databricks Runtime version is 7.3 LTS (includes Apache Spark 3.0.1, Scala 2.12)
Exception or Stack Trace
NA
Code Snippet
query = "SELECT * FROM c where c.Aggregation='Raw' ORDER by c.TimeStamp desc OFFSET 0 LIMIT 1"
readConfig = {
"spark.cosmos.accountEndpoint" : cosmosUrl,
"spark.cosmos.accountKey" : cosmosKey,
"spark.cosmos.database" : database_id,
"spark.cosmos.container" : "OilOutput",
"spark.cosmos.read.customQuery":query,
"spark.cosmos.read.inferSchema.forceNullableProperties": "true",
"spark.cosmos.read.inferSchema.includeSystemProperties": "true"
Describe the bug Cosmos db OLTP connector custom query with TOP or Limit clause return wrong number of document Query1 =SELECT Top 1 FROM c where c.Aggregation='Raw' ORDER by c.TimeStamp desc Query2 =SELECT FROM c where c.Aggregation='Raw' ORDER by c.TimeStamp desc OFFSET 0 LIMIT 1
Expected to get one result from this query and same query is giving one result when executed from Cosmosdb Data explorer sql query window. but the query is giving 9 result in Azure databricks with OLTP connctor com.azure.cosmos.spark:azure-cosmos-spark_3-1_2-12:4.2.0 databricks Runtime version is 7.3 LTS (includes Apache Spark 3.0.1, Scala 2.12)
Exception or Stack Trace NA
Code Snippet query = "SELECT * FROM c where c.Aggregation='Raw' ORDER by c.TimeStamp desc OFFSET 0 LIMIT 1" readConfig = { "spark.cosmos.accountEndpoint" : cosmosUrl, "spark.cosmos.accountKey" : cosmosKey, "spark.cosmos.database" : database_id, "spark.cosmos.container" : "OilOutput", "spark.cosmos.read.customQuery":query, "spark.cosmos.read.inferSchema.forceNullableProperties": "true", "spark.cosmos.read.inferSchema.includeSystemProperties": "true"
} print(query) df= rspark.read.format("cosmos.oltp").options(**readConfig).load()
display(df)
Expected behavior
query should return only one result Screenshots** Cosmos data explorer Azure Databricks
Setup (please complete the following information):