QueryCriteria TRUE() to N1QL is behaving differently in version 4.4.5

[amitnimbhorkar]

The SpEl query extraction/preparation is behaving wrong after upgrade from spring-data-couchbase:3.2.6.RELESE to spring-data-couchbase:4.4.5. The keyword IsTrue/True is expected to be transformed as 'field = TRUE', but it does not. Instead it just adds the field to the query as shown below.

This causes queries perform badly since they don’t pick expected indexes plus possible invalid results.

Before :
Optional findByIdAndStatusAndActiveDocumentTrue(String id, String status);

query: SELECT _class, id, META(bucket).id AS __id,…. FROM bucket WHERE _class = "com.blah.UserDocument" AND id = $1 and status = $2 and activeDocument = true

After:
Optional findByIdAndStatusAndActiveDocumentTrue(String id, String status);

query: SELECT _class, id, META(bucket).id AS __id,…. FROM bucket WHERE _class = "com.blah.UserDocument" AND id = $1 and status = $2 and activeDocument

[mikereiche]

[ Edit: The queries are not the same - the query plan is different; and the results will be different when the field is not boolean- see comment below for explanation from N1QL team. ]

For the first description ( activeDocument = true ) versus (activeDocument). Logically, the queries are the same. To test, I added a property "selectMe" : true to airline_137 in travel-sample.inventory.airline and ran the queries below and got the same answer for both. Can you please provide examples that show the behavior you are describing so I can forward the issue to the query team? Use "Explain Plan" in the query UI to show the different query plans.

SELECT * from default:travel-sample.inventory.airline, default:travel-sample.inventory.route where route.airlineid = meta(airline).id and airline.selectMe = true

and

SELECT * from default:travel-sample.inventory.airline, default:travel-sample.inventory.route where route.airlineid = meta(airline).id and airline.selectMe

To get an equality for a boolean value - Remove the 'True' from the method name and change the the method signature to take a boolean argument:

Optional findByIdAndStatusAndActiveDocument(String id, String status, Boolean isActiveDocument);

Note to self: This is the implementation of QueryCriteria.TRUE()

[lbarmako]

For the first description ( activeDocument = true ) versus (activeDocument). Logically, the queries are the same. To test, I added a property "selectMe" : true to airline_137 in travel-sample.inventory.airline and ran the queries below and got the same answer for both. Can you please provide examples that show the behavior you are describing so I can forward the issue to the query team? Use "Explain Plan" in the query UI to show the different query plans.

SELECT * from default:travel-sample.inventory.airline, default:travel-sample.inventory.route where route.airlineid = meta(airline).id and airline.selectMe = true

and

SELECT * from default:travel-sample.inventory.airline, default:travel-sample.inventory.route where route.airlineid = meta(airline).id and airline.selectMe

The problem is that it picks a completely different query plan which greatly impacts performance. I realise that it's possible to modify the indexes but why should we have to?! I believe it needs to be fixed and work as described in reference documentation.

To get an equality for a boolean value - Remove the 'True' from the method name and change the the method signature to take a boolean argument:

Optional findByIdAndStatusAndActiveDocument(String id, String status, Boolean isActiveDocument);

Note to self: This is the implementation of QueryCriteria.TRUE()

[mikereiche]

The difference is explained below. It would be easy enough to change QueryCriteria.TRUE() to produce "and activeDocument = true", but then there is no way to represent "and activeDocument"

"as described in reference documentation."

What's the link for the reference documentation for True()?

The N1QL folks explain the difference as follows using the analogous in MySQL :

Both are different meaning and different statements.
First one can get only true values from index. second one will get almost all values from the index (except null, false) unless someFlag is boolean
You should be more explicit what you want (WHERE someFlag =true).
If you don't specify relation condition any defined non-false value is true (i.e. except NULL or MISSING, false) in condition expression.
Try this you will know
select d FROM [{"a":1}, {"a":true}, {"a":null}, {"b":20}, {"a":false}] AS d where d.a;
select d FROM [{"a":1}, {"a":true}, {"a":null}, {"b":20}, {"a":false}] AS d where d.a = true;

It looks like My SQL expression is not boolean it implicit cast to integer and non-zero value treats as true  (N1QL there is no implicit cast,  any value is considered as true)
https://www.w3schools.com/MySQL/trymysql.asp?filename=trysql_select_all
SELECT * FROM Customers where CustomerID  ;  -- gives 91
SELECT * FROM Customers where CustomerID = true; -- gives 1
SELECT * FROM Customers where ContactName  ; -- gives 0
Best practice might be explicit condition.

[lbarmako]

@mikereiche

Here is a link to the doco: https://docs.spring.io/spring-data/couchbase/docs/current/reference/html/#couchbase.repository.n1ql

It says the following in the table:

IsTrue,True	findByFieldIsTrue	field = TRUE
IsFalse,False	findByFieldFalse	field = FALSE

Which release will the fix be in?

---

[mikereiche]

Next release - January 13. 5.0.1