N1QL Index with an OR statement

SQL++
1

I have been testing indexes in N1QL with OR statements and have unexpected results:

I have objects in the database, all which contain a “tId” field, and I have created an index as:

create index tId_IX on default(tId);

When I run EXPLAIN on a statement like

select * from default where tId = 'xyz';

I get expected results with the tId_IX being used. When I make the query more complex, it continues to work, including using function modifiers (UPPER()), string compares (like ‘%…%’), etc. However, as soon as I put an “OR” statement in there, the query reverts to using the “Primary Index”.

For instance:

select * from default where tId = 'xyz' AND _type = 'board' OR _type = 'card';

It gets even worse if I encapsulate the OR statement such as:

select * from default where tId = 'xyz' AND (_type = 'board' OR _type = 'card');

From this, both the query and the explain completely bomb out and there are no results. However, I do get an error message logged that I’ve copied below.

My ultimate goal is to create index on tId, _type, and name fields, but that continues to elude me, possibly until this issue is worked out. For instance, my query would be like this, any advice on setting up the index is appreciated:

SELECT _id, name FROM default where tId = 'xyz' AND (_type = 'abc' OR _type = 'def') AND LOWER(name) LIKE '%abc%';

Thanks a lot for your help.

Chris

[UPDATE]
I get the same error when I use an IN statement, e.g.

select * from default where tId = 'xyz' AND _type IN ['board','card'];

-------- ERROR

goroutine 156 [running]:
github.com/couchbaselabs/query/server.func·003()
	/var/root/goproj/src/github.com/couchbaselabs/query/server/server.go:128 +0xa5
runtime.panic(0x80c320, 0xe7e37c)
	/usr/local/go/src/pkg/runtime/panic.c:248 +0x18d
github.com/couchbaselabs/query/planner.constrain(0xc2259e12a0, 0x0, 0x2, 0xc21d1e2f08, 0x1, 0x1, 0x0, 0x0, 0x0)
	/var/root/goproj/src/github.com/couchbaselabs/query/planner/sarg_and.go:48 +0x43f
github.com/couchbaselabs/query/planner.func·001(0x10970e0, 0xc21af31e60, 0xc2259e12a0, 0x0, 0x2, 0x0, 0x0)
	/var/root/goproj/src/github.com/couchbaselabs/query/planner/sarg_and.go:37 +0x26c
github.com/couchbaselabs/query/planner.(*sargBase).VisitField(0xc21d1e2e68, 0xc21af31e60, 0x0, 0x0, 0x0, 0x0)
	/var/root/goproj/src/github.com/couchbaselabs/query/planner/sarg.go:215 +0x93
github.com/couchbaselabs/query/expression.(*Field).Accept(0xc21af31e60, 0x109c348, 0xc21d1e2e68, 0x0, 0x0, 0x0, 0x0)
	/var/root/goproj/src/github.com/couchbaselabs/query/expression/nav_field.go:48 +0x63
github.com/couchbaselabs/query/planner.SargFor(0x1096b40, 0xc20e5efd10, 0x10970e0, 0xc21af31e60, 0x0, 0x0, 0x0)
	/var/root/goproj/src/github.com/couchbaselabs/query/planner/sarg.go:38 +0xe8
github.com/couchbaselabs/query/plan.(*builder).selectScan(0xc209a6fcb0, 0x109b8f0, 0xc208912f00, 0xc20e5ee500, 0x0, 0x0, 0x0, 0x0)
	/var/root/goproj/src/github.com/couchbaselabs/query/plan/build_scan.go:112 +0x775
github.com/couchbaselabs/query/plan.(*builder).VisitKeyspaceTerm(0xc209a6fcb0, 0xc20e5ee500, 0x0, 0x0, 0x0, 0x0)
	/var/root/goproj/src/github.com/couchbaselabs/query/plan/build_select.go:232 +0x6a5
github.com/couchbaselabs/query/algebra.(*KeyspaceTerm).Accept(0xc20e5ee500, 0x1097208, 0xc209a6fcb0, 0x0, 0x0, 0x0, 0x0)
	/var/root/goproj/src/github.com/couchbaselabs/query/algebra/from.go:94 +0x60
github.com/couchbaselabs/query/plan.(*builder).VisitSubselect(0xc209a6fcb0, 0xc20e5eeff0, 0x0, 0x0, 0x0, 0x0)
	/var/root/goproj/src/github.com/couchbaselabs/query/plan/build_select.go:90 +0x1b46
github.com/couchbaselabs/query/algebra.(*Subselect).Accept(0xc20e5eeff0, 0x1097208, 0xc209a6fcb0, 0x0, 0x0, 0x0, 0x0)
	/var/root/goproj/src/github.com/couchbaselabs/query/algebra/select.go:232 +0x60
github.com/couchbaselabs/query/plan.(*builder).VisitSelect(0xc209a6fcb0, 0xc215ac82c0, 0x0, 0x0, 0x0, 0x0)
	/var/root/goproj/src/github.com/couchbaselabs/query/plan/build_select.go:34 +0x15d
github.com/couchbaselabs/query/algebra.(*Select).Accept(0xc215ac82c0, 0x1097180, 0xc209a6fcb0, 0x0, 0x0, 0x0, 0x0)
	/var/root/goproj/src/github.com/couchbaselabs/query/algebra/select.go:52 +0x60
github.com/couchbaselabs/query/plan.(*builder).VisitExplain(0xc209a6fcb0, 0xc2259e0ca0, 0x0, 0x0, 0x0, 0x0)
	/var/root/goproj/src/github.com/couchbaselabs/query/plan/build_explain.go:17 +0xd8
github.com/couchbaselabs/query/algebra.(*Explain).Accept(0xc2259e0ca0, 0x1097180, 0xc209a6fcb0, 0x0, 0x0, 0x0, 0x0)
	/var/root/goproj/src/github.com/couchbaselabs/query/algebra/explain.go:38 +0x60
github.com/couchbaselabs/query/plan.Build(0x1096c68, 0xc2259e0ca0, 0x1095750, 0xc20802c070, 0x10961a8, 0xc20805a000, 0x8f67d0, 0x7, 0x1096800, 0x0, ...)
	/var/root/goproj/src/github.com/couchbaselabs/query/plan/build.go:23 +0x187
github.com/couchbaselabs/query/plan.BuildPrepared(0x1096c68, 0xc2259e0ca0, 0x1095750, 0xc20802c070, 0x10961a8, 0xc20805a000, 0x8f67d0, 0x7, 0x4ab4e00, 0x315b5, ...)
	/var/root/goproj/src/github.com/couchbaselabs/query/plan/prepared.go:25 +0xb7
github.com/couchbaselabs/query/server.(*Server).getPrepared(0xc208046120, 0x1096658, 0xc219c46c60, 0x8f67d0, 0x7, 0x7, 0x0, 0x0)
	/var/root/goproj/src/github.com/couchbaselabs/query/server/server.go:189 +0x167
github.com/couchbaselabs/query/server.(*Server).serviceRequest(0xc208046120, 0x1096658, 0xc219c46c60)
	/var/root/goproj/src/github.com/couchbaselabs/query/server/server.go:144 +0x11b
github.com/couchbaselabs/query/server.(*Server).doServe(0xc208046120)
	/var/root/goproj/src/github.com/couchbaselabs/query/server/server.go:119 +0x94
created by github.com/couchbaselabs/query/server.func·002
	/var/root/goproj/src/github.com/couchbaselabs/query/server/server.go:112 +0x4a````
2

Hello,

Please try this out with the Couchbase Server 4.0 Developer Preview (integrated CB Server, not N1QL DP4). We fixed similar issues as you are reporting. If you still see the same issues, please reply here, and we’ll investigate.

Thanks,
Gerald

3

Hey Gerald,

Are you expecting that this change will be made as well in the cbq-beta
release for Couchbase 3.0? Unfortunately, because I connect with the Node
SDK, I don’t think I can update to Couchbase 4.0 right now. I just gave it
a whirl and ran into some early issues that I don’t really want to start
building workarounds for right now, until CB 4 is further along.

If necessary, I can go back to using views for some of these queries, but
if we expect the’ll be resolved in the next standalone release of N1QL, I
can wait for that.

Thanks.
Chris

4

Hi Chris,

Even if we provide standalone N1QL binaries, they may not work with CB 3.0, and that combination will not be officially supported. So yes, the standalone binaries will have all the fixes, but you may not be able to use them with CB 3.0.

Thanks,
Gerald

5

Ok, so is N1QL DP4 the last one that CB 3.0 supports?

6

Yes, and there are technical reasons for that.

7

Hi Chris,

You could try building the standalone binary of the latest code base and give it a whirl. Have a look how to clone and locally build the N1QL standalone binary in the Readme file : https://github.com/couchbase/query

If that doesn’t work for you, I can create a binary and send it to you. Since you are anyway using VIEW indexes the latest N1QL builds should work fine against couhbase 3.0

Cheers,
Manik

8

I think we can probably support you with an npm install from the latest node client to allow you to test with Couchbase 4.0 DP. It’d be hugely helpful to us if you can try everything integrated. @brett19 can you identify what npm git sha would be best given what @cmina mentions above?

9

@ingenthr and @manik,

I’d be happy to try either a new NPM install against CB 4.0 DP, or the N1QL standalone against CB 3.0.

Whichever you think would be A) most helpful to you and B) most helpful to me, just let me know and send over the resources. Not sure if you can see my email through the forums or not, if you need it, let me know.

Thanks.
Chris

10

Hello,

I’ve also been playing around with Indexes recently and it seems that this issue still exists in the newest version of n1ql. For example, if I have three indexes on three expressions and they all work fine when the query is of the form:

SELECT * FROM default
WHERE expression1 = 'this’
AND expression2 = 'that’
AND expression3 = ‘other’

but if I write a query that looks like this:

SELECT * FROM default
WHERE expression1 = 'this’
AND expression2 = 'that’
AND (expression3 = ‘something’ OR expression3 = ‘somethingElse’)

then none of the three indexes are used and the query only uses the Primary Index.

Is this something that will be fixed in the near future? Thanks

Mike

11

Hi @MikeMaik,

If you post the 3 index definitions and the queries you are using, all exact, we can test on the upcoming 4.5 beta and let you know. We have made improvements in this area.

Gerald

12

Hi Gerald,

so here are my three very basic Indexes that all work just fine:
CREATE INDEX payer_idx ON data (type, payer)
CREATE INDEX payment_idx ON data (type, paymentType)
CREATE INDEX status_idx ON data (type, status)

Now if write a query like this:

EXPLAIN SELECT * FROM data
WHERE type = 'transaction’
AND payer = 'SOMEPAYER’
AND status = 'CLOSED_PAID_OUT’
AND paymentType = ‘CREDITCARD’;

all 3 indexes are used. However if I write a query like this:

EXPLAIN SELECT * FROM data
WHERE type = 'transaction’
AND payer = 'SOMEPAYER’
AND status = 'CLOSED_PAID_OUT’
AND (paymentType = ‘CREDITCARD’ OR paymentType = ‘DEBITCARD’);

it will not use any of the above mentioned indexes.

I hope this helps.

Mike

13

Hello,

In upcoming 4.5 beta second query uses the the one of the index not all three due to OR clause and composite index.

By changing index definition to use partial index allows both queries to use all three indexes.

CREATE INDEX payer_idx ON data (payer) where type = ‘transaction’;
CREATE INDEX payment_idx ON data (paymentType) where type = ‘transaction’;
CREATE INDEX status_idx ON data (status) where type = ‘transaction’;

1 Like
14

Thank you so much. That did the trick.