Simple where clause giving grief

N1QL
1

I will admit I am a little rusty on my SQL skills, however I am fairly certain this should work, but it doesn’t.

select distinct _type from Stats; returns 4 values like this _type "match" "game" "gamestats" "timeline"
So I’d think the following should work: select count(*) from Stats where _type = match;, unfortunately that returns nothing and I am stomped.

2 
select count(*) 
from Stats
 where _type = "match";

OR

select _type, count(1)  AS cnt
from Stats
GROUP BY _type;
3

The first queries returns 0 rows, and the second returns 3131 for match.

4 
 explain select count(*) 
from Stats
 where _type = "match";

Also post output of second query

5

Second query output: |_type|cnt| |"game"|2934| |"gamestats"|2941| |"timeline"|2935| |"match"|3131|

6

[
{
“plan”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “PrimaryScan3”,
“index”: “#primary”,
“index_projection”: {
“primary_key”: true
},
“keyspace”: “Stats”,
“namespace”: “default”,
“using”: “gsi”
},
{
“#operator”: “Fetch”,
“keyspace”: “Stats”,
“namespace”: “default”
},
{
“#operator”: “Parallel”,
“~child”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “Filter”,
“condition”: “((‘Stats’.’_type’) = “match”)”
},
{
“#operator”: “InitialGroup”,
“aggregates”: [
“count()"
],
“group_keys”: []
}
]
}
},
{
“#operator”: “IntermediateGroup”,
“aggregates”: [
"count()”
],
“group_keys”:
},
{
“#operator”: “FinalGroup”,
“aggregates”: [
“count()"
],
“group_keys”: []
},
{
“#operator”: “Parallel”,
“~child”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “InitialProject”,
“result_terms”: [
{
“expr”: "count()”
}
]
},
{
“#operator”: “FinalProject”
}
]
}
}
]
},
“text”: “select count(*) \nfrom Stats\n where _type = “match”;”
}
]

7

Plan looks fine. You can take look Last Tab of UI and post that. Make sure you don’t have control characters in “match”

8

There is nothing special about the Query Monitor page, and there are no control characters in the field _type, it only has straight up 1 of 4 string values.

9

After executing query post out put from “Plain Text” Tab

10

Ok this is weird… things started to work just now, no changes no nothing, could it be that the primary index hadn’t finished entirely or something else?

11

Possible. “Plain Text” has info each stage #ItemsIn, #ItemsOut you will know where it went wrong.

If you are doing aggregates check this out https://blog.couchbase.com/understanding-index-grouping-aggregation-couchbase-n1ql-query/

https://blog.couchbase.com/n1ql-practical-guide-second-edition/