Usage of the arrayLiteral expression with N1QL

Mobile Couchbase Lite
,
1

Hi, I try to execute the following query (as explained in the section Expressions->Literals->Array at SQL++ Query Strings | Couchbase Docs) :

SELECT COUNT(id) FROM _ WHERE meta.viewType IN ["View1", "View2"]

but I got the following exception :

CouchbaseLiteException{CouchbaseLite,23,'N1QL syntax error near character 48
    (CouchbaseLite Android v3.0.0-192@30 (CE/release, Commit/unofficial@1f4001b9647c Core/3.0.0 (192) at 2022-01-27T02:25:59.415601Z) on Java; Android 5.1.1; SM-T365)'}
    at com.couchbase.lite.N1qlQuery.prepQueryLocked(N1qlQuery.java:54)
    at com.couchbase.lite.AbstractQuery.getC4QueryLocked(AbstractQuery.java:206)
    at com.couchbase.lite.AbstractQuery.getC4Query(AbstractQuery.java:186)
    at com.couchbase.lite.N1qlQuery.compile(N1qlQuery.java:57)
    at com.couchbase.lite.AbstractDatabase.createQuery(AbstractDatabase.java:656)
    at com.couchbase.lite.Database.createQuery(Database.java:30)
    *
    at kotlinx.coroutines.intrinsics.UndispatchedKt.startUndispatchedOrReturn(Undispatched.kt:89)
    at kotlinx.coroutines.BuildersKt__Builders_commonKt.withContext(Builders.common.kt:165)
    at kotlinx.coroutines.BuildersKt.withContext(Unknown Source)
    *
    at kotlin.coroutines.jvm.internal.BaseContinuationImpl.resumeWith(ContinuationImpl.kt:33)
    at kotlinx.coroutines.DispatchedTask.run(DispatchedTask.kt:106)
    at kotlinx.coroutines.internal.LimitedDispatcher.run(LimitedDispatcher.kt:39)
    at kotlinx.coroutines.scheduling.TaskImpl.run(Tasks.kt:95)
    at kotlinx.coroutines.scheduling.CoroutineScheduler.runSafely(CoroutineScheduler.kt:571)
    at kotlinx.coroutines.scheduling.CoroutineScheduler$Worker.executeTask(CoroutineScheduler.kt:750)
    at kotlinx.coroutines.scheduling.CoroutineScheduler$Worker.runWorker(CoroutineScheduler.kt:678)
    at kotlinx.coroutines.scheduling.CoroutineScheduler$Worker.run(CoroutineScheduler.kt:665)
    Caused by: LiteCoreException{1, 23, "N1QL syntax error near character 102"}
    at com.couchbase.lite.internal.core.C4Query.createQuery(Native Method)
    at com.couchbase.lite.internal.core.C4Query.<init>(C4Query.java:68)
    at com.couchbase.lite.internal.core.C4Database.createN1qlQuery(C4Database.java:323)
    at com.couchbase.lite.AbstractDatabase.createN1qlQuery(AbstractDatabase.java:840)
    at com.couchbase.lite.N1qlQuery.prepQueryLocked(N1qlQuery.java:53)
    at com.couchbase.lite.AbstractQuery.getC4QueryLocked(AbstractQuery.java:206) 
    at com.couchbase.lite.AbstractQuery.getC4Query(AbstractQuery.java:186) 
    at com.couchbase.lite.N1qlQuery.compile(N1qlQuery.java:57) 
    at com.couchbase.lite.AbstractDatabase.createQuery(AbstractDatabase.java:656) 
    at com.couchbase.lite.Database.createQuery(Database.java:30) 
    *
    at kotlinx.coroutines.intrinsics.UndispatchedKt.startUndispatchedOrReturn(Undispatched.kt:89) 
    at kotlinx.coroutines.BuildersKt__Builders_commonKt.withContext(Builders.common.kt:165) 
    at kotlinx.coroutines.BuildersKt.withContext(Unknown Source) 
    *
    at kotlin.coroutines.jvm.internal.BaseContinuationImpl.resumeWith(ContinuationImpl.kt:33) 
    at kotlinx.coroutines.DispatchedTask.run(DispatchedTask.kt:106) 
    at kotlinx.coroutines.internal.LimitedDispatcher.run(LimitedDispatcher.kt:39) 
    at kotlinx.coroutines.scheduling.TaskImpl.run(Tasks.kt:95) 
    at kotlinx.coroutines.scheduling.CoroutineScheduler.runSafely(CoroutineScheduler.kt:571) 
    at kotlinx.coroutines.scheduling.CoroutineScheduler$Worker.executeTask(CoroutineScheduler.kt:750) 
    at kotlinx.coroutines.scheduling.CoroutineScheduler$Worker.runWorker(CoroutineScheduler.kt:678) 
    at kotlinx.coroutines.scheduling.CoroutineScheduler$Worker.run(CoroutineScheduler.kt:665)

It seems that the bracket ‘[ ]’ of the array causes trouble…

When I try with the QueryBuilder :

QueryBuilder.select(SelectResult.expression(Function.count(Expression.property("id"))))
     .from(DataSource.database(database))
     .where(Expression.property("meta.viewType").`in`(
               Expression.string("View1"),
               Expression.string("View2"))
      )

I got the correct result.

Did I miss something in my query ?

Thanks !

2

I notice when playing around with the query that replacing the bracket ‘[ ]’ by parentheses ‘( )’ :

SELECT COUNT(id) FROM _ WHERE meta.viewType IN ("View1", "View2")

return the correct result :hushed:

Is there an error in the docs or a bug in ‘createQuery’ ^^ ?

Thanks !

1 Like
3

Yes, need parenthesis after IN, conforming with the standard SQL grammar.

1 Like