Spring Data Couchbase| Prepared Statements does not support Date operations in new upgraded SDK

cholinajay97 · October 28, 2021, 3:03am

Hi,
I’m running the queries as below,

Context

String statement = “SELECT …”;

JsonObject placeholderValues which contains - Jsonarray, strings, LocalDate (converted to String in format “2021-10-28”)

QueryOptions queryOptions = QueryOptions.queryOptions().adhoc(false)
.parameters(placeHolderValues)
.scanConsistency(QueryScanConsistency.REQUEST_PLUS);

cluster.query(bucketName, queryOptions);

Question
Date operations like Operation bucket.LocalDate >= “2021-10-28” is not working.
(LocalDate - java.time.*)
Can someone assist on this?

(couchbase java client - 3.0.10 ; spring data couchbase - 4.1.6)
@AV25242

Thanks,
Ajay

AV25242 · October 28, 2021, 3:58pm

Hi @cholinajay97 welcome to the forums.

Are you getting any kind of errors or are you saying the generated query is not giving back any results and if so does the query runs successfully returning back the data when using Couchbase UI.

@mreiche any other suggestions or thoughts ?

mreiche · October 28, 2021, 4:18pm

Hi - Can you please show the actual example of the code that doesn’t work and details of how it doesn’t work? (i.e. wrong answer or throws an exception), and one document that you expect to be selected? You mention you are using spring-data-couchbase, but the query just uses cluster from the Java SDK - spring-data-couchbase is not involved. I can suggest looking for N1QL examples that select on dates.

There is an open spring-data-couchbase issue regarding spring-data-couchbase LocalDate compatibility with the Java SDK. LocalDateTime converter compatible with Java SDK · Issue #1203 · spring-projects/spring-data-couchbase · GitHub

Also - I highly recommend using a later version of spring-data-couchbase. 4.2.6 or 4.3.0-RC1

Mike

cholinajay97 · October 28, 2021, 6:43pm

Thanks, @AV25242 for the response.
Yes, the query does successfully fetch the results in Couchbase UI but doesn’t through code.

When the LocalDate filter is added to the query, Cluster. query() returns with empty rows.

cholinajay97 · October 28, 2021, 7:10pm

Hi @mreiche , appreciate your response.

Issue when using Couchbase SDK java client

The query does not throw any exception as such but fails to apply the Date filter and end up returning empty rows.
(i.e QueryResult.rows is empty)

Thanks for the recommendation with the spring version, I will discuss it with my team,

Q1) For the existing issue on LocalDate, do we have any workaround for that? The same Query was working fine earlier with the spring-data-Couhbase repository using @Query.

Issue when used Spring-Data-Couchbase which uses @Query

Regarding spring-data-couchbase, the same Query when run using the @Query with @params like below, throw a different exception. It was working in the older version of spring-data-couchbase. I got to know we need to modify and use __cas and __id to avoid NPE (from your recommendations) and I did the respective changes and I get this new error as below.

Query

@Query("SELECT META(alias).id AS __id, META(alias).cas AS __cas, alias_2.*  "
        + "FROM #{#n1ql.bucket} alias"
        + "UNNEST alias.dataArray AS alias_2"
        + "WHERE REGEXP_LIKE(META(alias).id, $docIdPrefix) "
        + "AND alias_2.LocalDate(date1) <= "2021-12-31" **(which is $end)**
        + "AND alias_2.LocalDate(date2)  >= "2021-10-28 " *(which is $start)*
        + "AND ANY day IN alias_2.integerArray SATISFIES day IN [1,2,3,4,5,6,7] *(which is $frequency )* END "
        + "ORDER BY alias_2.LocalDate(date1) ")
List<Schedule> findAllDataByIdLikeAndDate(@Param("docIdPrefix") String docIdPrefix,
                                                     @Param("start") LocalDate start,
                                                     @Param("end") LocalDate end,
                                                     @Param("frequency") JsonArray frequency);

Exception

“illegal argument exception java property must not be null”

for other Query,
2) " [java.lang.IllegalArgumentException: No enum constant " (same Query was working fine with defined Writer and Reader convertors in Couchbase Config)

Q2) Any idea on this Exception?
(This seems to be a different issue but I can open up a different chat for that - do let me know)

mreiche · October 28, 2021, 7:22pm

Please open an issue Sign in to GitHub · GitHub

The more complete information you provide, the less time I’ll spend reproducing and the more time I’ll have to troubleshoot. (i.e. provide the whole repository class, the whole Schedule class and the code that is calling this). And the stack trace of the exceptoin.
The spring-data query won’t work if the Java SDK query doesn’t work as spring-data-couchbase calls the java sdk.
Also - it’s not possible to know if the query should select documents without knowing what the documents are.

" (same Query was working fine with defined Writer and Reader convertors in Couchbase Config)

So why not use those converters?

alias_2.LocalDate(date1) <= “2021-12-31” (which is $end)

I don’t understand. $end would be $end. “2021-12-13” is “2021-12-31”.

The query as provided does not compile (even after replacing the hard-coded values with their parameters).
There needs to be a space before the closing quotes on lines 2 and 3.
alias_2.LocalDate(date1) does not parse. I’m not sure what the intention is.

vsr1 · October 28, 2021, 7:49pm

I am not sure above one is right N1QL doesn’t know about LocalDate. Also you can’t use dot function name unless it is udf function on specific namespace in the N1QL. one can use like func(alias2.date1)

if you need date1 to compare with “2021-12-31”

AND alias_2.date1 <= “2021-12-31”

AND alias_2.date1 <= $end named parameter end bind as string by converting the java LocalDate

mreiche · October 28, 2021, 8:27pm

This works fine on 4.3.0-RC1. If your LocalDates were stored in documents as Strings, you’ll need to convert your parameters like: alias_2.date2 >= MILLIS_TO_STR($start)

	List<User> users = userRepository.findAllDataByIdLikeAndDate("1*", LocalDate.of(2020, 10, 1),
			LocalDate.of(2021, 10, 31), JsonArray.create().add(1).add(2));
	assertEquals(1, users.size());


@Query("SELECT META(alias).id AS __id, META(alias).cas AS __cas, alias_2.*  "
		+ "FROM #{#n1ql.bucket} alias "
		+ "UNNEST alias.dataArray AS alias_2 "
		+ "WHERE REGEXP_LIKE(META(alias).id, $docIdPrefix) "
		+ "AND alias_2.date1 <= $end "
		+ "AND alias_2.date2  >= $start "
		+ "AND ANY day IN alias_2.integerArray SATISFIES day IN $frequency  END "
				+ "ORDER BY alias_2.date2 ")
List<User> findAllDataByIdLikeAndDate(
	@Param("docIdPrefix") String docIdPrefix,
	@Param("start") LocalDate start,
	@Param("end") LocalDate end,
	@Param("frequency") JsonArray frequency);

{
  "firstname": "Dave",
  "createdDate": 1577865600000,
  "lastModifiedDate": 1577865600000,
  "_class": "org.springframework.data.couchbase.domain.User",
  "dataArray": [
    {
      "date1": 1633071600001,
      "date2": 1633071600001,
      "integerArray": [
        1,
        5
      ]
    }
  ]
}

cholinajay97 · November 2, 2021, 6:28pm

Hi @mreiche , Thanks for the response.

Note - We store the dates in String (LocalDate Object) in couchbase and not epoch time (more details as below)

Regarding the spring version, at the moment we don’t have the feasibility to upgrade to 4.3.X .
Is there any way that we can make this Query work without upgrading the version?

Complete Query

@Query("SELECT META(prep).id AS __id, META(prep).cas AS __cas, prep.* "
+ "FROM #{n1ql.bucket} prep "
+ "UNNEST prep.schedules AS sch "
+ "WHERE REGEXP_LIKE(META(prep).id, $docIdPrefix) "
+ "AND sch.periodOfOperation.startDate <= $endDate "
+ "AND sch.periodOfOperation.endDate >= $startDate "
+ "AND ANY day IN sch.daysOfOperation SATISFIES day IN $frequencies END "
+ “ORDER BY sch.periodOfOperation.startDate”)
List findAllSchedulesByIdLikeAndDatePeriod(@Param(“docIdPrefix”) String docIdPrefix,
@Param(“startDate”) LocalDate startDate,
@Param(“endDate”) LocalDate endDate,
@Param(“frequencies”) JsonArray frequencies);

Model JSON as stored in couchbase
{
“schedules”: [
{
“daysOfOperation”: [
1,2,3,4,5,6,7
],
“periodOfOperation”: {
“endDate”: “2025-03-28”,
“startDate”: “2025-03-01”
}
},
{
“daysOfOperation”: [
1,2,3,4,5,6,7
],
“periodOfOperation”: {
“endDate”: “2025-03-28”,
“startDate”: “2025-03-01”
}
}
]

}

Model Object

public class Instruction 
{
@Id
private String id;

private List <Schedule>  schedules;

}

public class Schedule implements Serializable  
{

    private PeriodOfOperation periodOfOperation;

    private List<Integer> daysOfOperation;
}

public class PeriodOfOperation implements Serializable {

    private LocalDate startDate;

    private LocalDate endDate;
}

Convertors Used in Couchbase Config

@WritingConverter
    private static class LocalDateStringConverter implements Converter<LocalDate, String> {
        @Override
        public String convert(@NotNull LocalDate localDate) {
            return localDate.format(DATE_FORMATTER);
        }

@ReadingConverter
    private static class StringLocalDateConverter implements Converter<String, LocalDate> {
        @Override
        public LocalDate convert(@NotNull String string) {
            return LocalDate.parse(string, DATE_FORMATTER);
        }
    }

cholinajay97 · November 2, 2021, 6:40pm

Same Query works flawlessly in Couchbase Console

cholinajay97 · November 2, 2021, 6:44pm

Hi @vsr1 please find the complete query as below.

@Query("SELECT META(prep).id AS __id, META(prep).cas AS __cas, prep.* "

"FROM #{n1ql.bucket} prep "
"UNNEST prep.schedules AS sch "
"WHERE REGEXP_LIKE(META(prep).id, $docIdPrefix) "
"AND sch.periodOfOperation.startDate <= $endDate "
"AND sch.periodOfOperation.endDate >= $startDate "
"AND ANY day IN sch.daysOfOperation SATISFIES day IN $frequencies END "
“ORDER BY sch.periodOfOperation.startDate”)
List findAllSchedulesByIdLikeAndDatePeriod(@Param(“docIdPrefix”) String docIdPrefix,
@Param(“startDate”) LocalDate startDate,
@Param(“endDate”) LocalDate endDate,
@Param(“frequencies”) JsonArray frequencies);

Can you clarify this statement “AND alias_2.date1 <= $end named parameter end bind as string by converting the java LocalDate”? I didn’t quite get it.

Thanks
Ajay

vsr1 · November 2, 2021, 6:57pm

I am not expertise in spring data

"AND sch.periodOfOperation.endDate >= $startDate "
what i am saying startDate convert to string and try as named parameter
@Param(“startDate”) String startDate, ====> @Param(“startDate”) String “2021-01-01”

mreiche · November 2, 2021, 9:20pm

Same Query works flawlessly in Couchbase Console

But that’s not the same query. That uses string literals instead of LocalDate $start and $end parameters. And that’s why it works.
Change your $start and $end parameters to String, and that will work as well. Like @vsr1 said.
(to use parameters in the query console, specify them in Run-time Preferences (the gear in the top-right beside IMPORT EXPORT).

cholinajay97 · February 1, 2022, 10:58am

Thanks, @mreiche @ @vsr1 for your support. This issue is now resolved.

Topic		Replies	Views
Zero result with n1ql query with java sdk, where rest api gives results SQL++	4	1423	April 24, 2017
Java implementation of N1qlQuery.parameterized(query, json object) Java SDK java , n1ql	5	3871	January 28, 2019
Urgent inquiry: Spring Data Couchbase N1QL based query not working Java SDK	3	6709	April 10, 2018
How to translate Couchbase query into java code Java SDK	2	622	March 2, 2021
How to execute dynamic n1ql query with Spring data CouchbaseTemplate Java SDK	3	2771	May 2, 2022

Spring Data Couchbase| Prepared Statements does not support Date operations in new upgraded SDK

Issue when using Couchbase SDK java client

Issue when used Spring-Data-Couchbase which uses @Query

Query

Exception

Related topics