Issue with "IN" operator?

#1

With the tutorial sample bucket data, I’m seeing something odd with the IN operator.

This query works fine, returning 4 documents:
SELECT *
FROM tutorial
WHERE age IN [46,18,56]

However, if I add another “where” clause, the “IN” no longer seems to work - e.g.:
SELECT *
FROM tutorial
WHERE age IN [46,18,56]
AND email LIKE ‘%@gmail.com

This returns no results, though it ought to return the same 4.

I can work around this by using (age=46 or age=18 or age=56), so not a big deal, but the IN operator would be cleaner.

I’m I doing something wrong here, in my use of “IN”, or is there a bug? Cheers!

#2

Looks like a bug!

This has been fixed in DP4, which be released in a few weeks.

Thanks.

#3

Fixed: http://blog.couchbase.com/n1ql-dp4-is-here

#4

I have a similar issue using the IN operator. I can confirm everything works with a multi-WHERE statement (eg. … WHERE type=“patient” AND org.id IN [207,209]). I’m using DP4.

However, when I create an index on the “type” column:
CREATE INDEX indexType ON default(type);

Then try to execute a N1QL query using a multi-WHERE and IN statement, nothing every returns. The cbq hangs and I have to kill it and come back in.

Here is the example select statement:
SELECT patient.lastName, patient.firstName FROM default WHERE type=“admission” AND org.id IN [207,209];

Separately, if I create an index on org.id:
CREATE INDEX indexOrgId ON default(org.id);

Then run a single WHERE statement, everything works:
SELECT * FROM admission WHERE org.id IN [207,209];

So, it looks like it only involves multi-WHERE clauses that invokes and Index.

Any ideas?

Thanks!
Eric

#5

Hi Eric,

If cbq-engine is crashing, can you please post the output of the console (the terminal from which you are running cbq-engine)?

Thanks,
Gerald

#6

cbq-engine isn’t crashing, it just doesn’t ever return any results when I run the query in cbq. The cbq query just sits there, with no output.

Does that make sense?

#7

Ok, I do actually see an error in the cbq-engine afterall. Here’s the dump (sorry for the length, didn’t know what you needed).

_time=“2015-02-12T17:33:32-05:00” _level=“ERROR” _msg="" panic=runtime error: index out of range stack="goroutine 137 [running]:\ngithub.com/couchbaselabs/query/server.func·003()\n\t/var/root/goproj/src/github.com/couchbaselabs/query/server/server.go:128 +0xa5\nruntime.panic(0x80c320, 0xe7e37c)\n\t/usr/local/go/src/pkg/runtime/panic.c:248 +0x18d\ngithub.com/couchbaselabs/query/planner.constrain(0xc210b89210, 0x0, 0x2, 0xc20ff2c6d0, 0x1, 0x1, 0x0, 0x0, 0x0)\n\t/var/root/goproj/src/github.com/couchbaselabs/query/planner/sarg_and.go:48 +0x43f\ngithub.com/couchbaselabs/query/planner.func·001(0x1097508, 0xc210f36f60, 0xc210b89210, 0x0, 0x2, 0x0, 0x0)\n\t/var/root/goproj/src/github.com/couchbaselabs/query/planner/sarg_and.go:37 +0x26c\ngithub.com/couchbaselabs/query/planner.(*sargBase).VisitField(0xc20ff2c5e8, 0xc210f36f60, 0x0, 0x0, 0x0,

Plus a lot more :slight_smile:

#8

Ok, that helps a lot!

Thanks,
Gerald

#9

I still this issue occurring, Is it fixed ?

#10

Hi, are you using 4.0 or 4.1? And can you again describe exactly what is happening? Thanks.

#11

version is 4.1.0-5005
// this query hangs where type is indexed
select * from bucketName where type=‘address’ and (id in [29]);

// this works fine
select * from bucketName where type=‘address’ and (someField=true and id in [29]);

#12

Ok, thank you. Looks like another edge case for the original bug. We have refiled the bug below.

https://issues.couchbase.com/browse/MB-17339?filter=13615