Query annotation with ARRAY_CONTAINS not working in 3.26

[SethuSaravanan]

Hello Team,

We were using azure-spring-data-cosmos 3.25.0 for our api.

        <dependency>
            <groupId>com.azure</groupId>
            <artifactId>azure-spring-data-cosmos</artifactId>
            <version>3.25.0</version>
        </dependency>

and below query was yielding expected results with out any issues for given tenantId , one or more region string as input.

        @Query(value = "SELECT * FROM at "
                  + "WHERE at.tenantId = @tenantId "
                  + "AND (@regions = null or ARRAY_CONTAINS(@regions, at.region)) ")
        List<ArchiveTransaction> findAllArchiveTransByRegions(
                  @Param("tenantId") String tenantId,
                  @Param("regions") List<String> regions);

But when we upgraded our version to 3.26.0 it is returning data only for given tenentId and no region. It is NOT returning any data for given tenentId and one or more region as input.

        @Query(value = "SELECT * FROM at "
                  + "WHERE at.tenantId = @tenantId "
                  + "AND (@regions = null or ARRAY_CONTAINS([@regions], at.region)) ")
        List<ArchiveTransaction> findAllArchiveTransByRegions(
                  @Param("tenantId") String tenantId,
                  @Param("regions") List<String> regions);

we had to change the query to include square brackets around @regions like [@regions] for it to work. Even with that change it works only for a single input tenentId and single input of region , when we input two or more region as input the query is failing with error " Syntax error, incorrect syntax near ','. " .

{
  "status": "BAD_REQUEST",
  "message": "Error occurred",
  "errors": [
    "{\"innerErrorMessage\":\"Message: {\\\"errors\\\":[{\\\"severity\\\":\\\"Error\\\",\\\"location\\\":{\\\"start\\\":61,\\\"end\\\":62},\\\"code\\\":\\\"SC1001\\\",\\\"message\\\":\\\"Syntax error, incorrect syntax near ','.\\\"}]}\\r\\nActivityId: cc85d97a-9e14-46cd-9f02-b9f8f4b45641, Microsoft.Azure.Documents.Common/2.14.0, StatusCode: BadRequest\",\"cosmosDiagnostics\":{\"userAgent\":\"azsdk-java-cosmos/4.35.0 MacOSX/10.16 JRE/1.8.0_181\",\"activityId\":\"cc85d97a-9e14-46cd-9f02-b9f8f4b45641\",\"requestLatencyInMs\":893,\"requestStartTimeUTC\":\"2022-09-19T02:56:48.555Z\",\"requestEndTimeUTC\":\"2022-09-19T02:56:49.448Z\",\"responseStatisticsList\":[],\"supplementalResponseStatisticsList\":[],\"addressResolutionStatistics\":{},\"regionsContacted\":[\"west us 2\"],\"retryContext\":{\"statusAndSubStatusCodes\":null,\"retryCount\":0,\"retryLatency\":0},\"metadataDiagnosticsContext\":{\"metadataDiagnosticList\":null},\"serializationDiagnosticsContext\":{\"serializationDiagnosticsList\":null},\"gatewayStatistics\":{\"sessionToken\":null,\"operationType\":\"QueryPlan\",\"resourceType\":\"Document\",\"statusCode\":400,\"subStatusCode\":0,\"requestCharge\":0.0,\"requestTimeline\":[{\"eventName\":\"connectionCreated\",\"startTimeUTC\":\"2022-09-19T02:56:48.556Z\",\"durationInMilliSecs\":808.0},{\"eventName\":\"connectionConfigured\",\"startTimeUTC\":\"2022-09-19T02:56:49.364Z\",\"durationInMilliSecs\":0.0},{\"eventName\":\"requestSent\",\"startTimeUTC\":\"2022-09-19T02:56:49.364Z\",\"durationInMilliSecs\":1.0},{\"eventName\":\"transitTime\",\"startTimeUTC\":\"2022-09-19T02:56:49.365Z\",\"durationInMilliSecs\":82.0},{\"eventName\":\"received\",\"startTimeUTC\":\"2022-09-19T02:56:49.447Z\",\"durationInMilliSecs\":0.0}],\"partitionKeyRangeId\":null,\"exceptionMessage\":\"Message: {\\\"errors\\\":[{\\\"severity\\\":\\\"Error\\\",\\\"location\\\":{\\\"start\\\":61,\\\"end\\\":62},\\\"code\\\":\\\"SC1001\\\",\\\"message\\\":\\\"Syntax error, incorrect syntax near ','.\\\"}]}\\r\\nActivityId: cc85d97a-9e14-46cd-9f02-b9f8f4b45641, Microsoft.Azure.Documents.Common/2.14.0, StatusCode: BadRequest\",\"exceptionResponseHeaders\":\"{Content-Type=application/json, Date=Mon, 19 Sep 2022 02:56:51 GMT, Server=Microsoft-HTTPAPI/2.0, Strict-Transport-Security=max-age=31536000, Transfer-Encoding=chunked, x-ms-activity-id=cc85d97a-9e14-46cd-9f02-b9f8f4b45641, x-ms-gatewayversion=version=2.14.0}\"},\"systemInformation\":{\"usedMemory\":\"289122 KB\",\"availableMemory\":\"3439262 KB\",\"systemCpuLoad\":\"(2022-09-19T02:56:23.516Z 20.3%), (2022-09-19T02:56:28.513Z 32.5%), (2022-09-19T02:56:33.513Z 32.8%), (2022-09-19T02:56:38.515Z 24.2%), (2022-09-19T02:56:43.515Z 30.6%), (2022-09-19T02:56:48.514Z 29.3%)\",\"availableProcessors\":8},\"clientCfgs\":{\"id\":1,\"machineId\":\"uuid:73773743-6b1c-413d-bc67-f26d3e17859c\",\"connectionMode\":\"DIRECT\",\"numberOfClients\":2,\"connCfg\":{\"rntbd\":\"(cto:PT5S, nrto:PT5S, icto:PT0S, ieto:PT1H, mcpe:130, mrpc:30, cer:true)\",\"gw\":\"(cps:1000, nrto:PT1M, icto:PT1M, p:false)\",\"other\":\"(ed: true, cs: false)\"},\"consistencyCfg\":\"(consistency: Strong, mm: true, prgns: [])\"}}}"
  ]
}

Looks like this bug was introduced in 3.26.0 , please fix it.

[chenrujun]

Hi, @SethuSaravanan , thanks for reaching out.

azure-spring-data-cosmos is hosting in azure-sdk-for-java repo. Could you please move this issue to there?

Refs: https://github.com/Azure/azure-sdk-for-java/blob/azure-spring-data-cosmos_3.27.0/sdk/cosmos/azure-spring-data-cosmos/CHANGELOG.md

[SethuSaravanan]

Thanks @chenrujun , I will move this there.