When I use ">=" operation, consistency is not valid

n1ql

#1

N1QL View Index create :
CREATE INDEX ix_default_2 on default(COL_1,COL_4,COL_5,COL_3) USING VIEW;
CREATE INDEX ix_default_5 on default(COL_1, COL_2) USING VIEW;

(1) This query result is valid. (COL_3 between 0 and 3)
Select count(*) from default
where COL_1=‘000’ and COL_2 like 'YYY%'
and COL_3 between 0 and 3 and COL_4=true and COL_5=false
order by length(COL_2) ASC, COL_6 ASC;
=> Count Result is 6000. It is valid.

(2) This query result is not valid. ( COL_3 >= 0)
Select count(*) from default
where COL_1=‘000’ and COL_2 like 'YYY%'
and COL_3 >= 0 and COL_4=true and COL_5=false
order by length(COL_2) ASC, COL_6 ASC;
=> Count Result is 2000. It is not valid.

Are there any difference?
Is there anyone who knows similar issues?


#2

What indexes are the two queries using? If you go to CBQ and prepend each query with "EXPLAIN ", it will print out the query plan.


#3

Thank you for reply.

query explain

(1)
"#operator": “IntersectScan”,
“scans”: [
{
"#operator": “IndexScan”,
“index”: “ix_default_5”,
“keyspace”: “default”,
“limit”: 9223372036854776000,
“namespace”: “default”,
“spans”: [
{
“Range”: {
“High”: [
"“000"”,
"“YYY0"”
],
“Inclusion”: 1,
“Low”: [
"“000"”,
"“YYY”"
]
},
“Seek”: null
}
],
“using”: “view”
},
{
"#operator": “IndexScan”,
“index”: “ix_default_2”,
“keyspace”: “default”,
“limit”: 9223372036854776000,
“namespace”: “default”,
“spans”: [
{
“Range”: {
“High”: [
"“000"”,
“false”,
“true”,
“3”
],
“Inclusion”: 3,
“Low”: [
"“000"”,
“false”,
“true”,
“0”
]
},
“Seek”: null
}
],
“using”: “view”
}

(2)

#operator”: “IntersectScan”,
“scans”: [
{
"#operator": “IndexScan”,
“index”: “ix_default_2”,
“keyspace”: “default”,
“limit”: 9223372036854776000,
“namespace”: “default”,
“spans”: [
{
“Range”: {
“High”: [
"“000"”,
“false”,
“successor(true)”
],
“Inclusion”: 1,
“Low”: [
"“000"”,
“false”,
“true”,
“0”
]
},
“Seek”: null
}
],
“using”: “view”
},
{
"#operator": “IndexScan”,
“index”: “ix_default_5”,
“keyspace”: “default”,
“limit”: 9223372036854776000,
“namespace”: “default”,
“spans”: [
{
“Range”: {
“High”: [
"“000"”,
"“YYY0"”
],
“Inclusion”: 1,
“Low”: [
"“000"”,
"“YYY”"
]
},
“Seek”: null
}
],
“using”: “view”
}


#4

The plan for the second query is wrong. It’s using wrong predicates for the index scan. Based on preliminary investigation, the problem occurred in 4.1.0, but is fixed in 4.5.0-DP.

What version of Couchbase are you running? Could you give this a try in 4.5.0-DP?


#5

The version of Couchbase is 4.0.0 (Community).
I will try it in 4.5.0-DP.

Thank you very much.