4.5 beta. with group by query, there is at least one result

#1

using group by statement, i expect empty result, but there is one result.
the value of all data fields is null, and key field is empty.

#2

@donovan.kim can you please share some sample data from your bucket, the exact query, the result you get and the result you want?

#3

i wanna same result whether group by or not.

cbq> SELECT h.tableId, p.sessionId
   > FROM HandHistory AS h USE INDEX (`HANDHISTORY_GPID_IDX`)
   > UNNEST h.players AS p
   > WHERE (any t in h.players satisfies t.userName = "HyPJr6VlyGXCNi3rivWohEmxt" end  )
   > and p.userName = "HyPJr6VlyGXCNi3rivWohEmxt"
   > AND h.timestamp >= "1462723200000" AND h.timestamp <= "1462809599999"
   > AND h.playType = "1" AND h.gameBuyInType = "2"
   > LIMIT 5;
{
    "requestID": "534dfd62-5584-48d7-ac17-0c4e9ba94878",
    "signature": {
        "sessionId": "json",
        "tableId": "json"
    },
    "results": [
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "13.526621ms",
        "executionTime": "13.445603ms",
        "resultCount": 0,
        "resultSize": 0
    }
}
cbq>
cbq> SELECT h.tableId, p.sessionId, max(h.timestamp) as time
   > FROM HandHistory AS h USE INDEX (`HANDHISTORY_GPID_IDX`)
   > UNNEST h.players AS p
   > WHERE (any t in h.players satisfies t.userName = "HyPJr6VlyGXCNi3rivWohEmxt" end  )
   > and p.userName = "HyPJr6VlyGXCNi3rivWohEmxt"
   > AND h.timestamp >= "1462723200000" AND h.timestamp <= "1462809599999"
   > AND h.playType = "1" AND h.gameBuyInType = "2"
   > group by h.tableId, p.sessionId
   > LIMIT 5;
{
    "requestID": "ac332310-ab57-4bd7-957a-35fe4c751c1b",
    "signature": {
        "sessionId": "json",
        "tableId": "json",
        "time": "json"
    },
    "results": [
        {
            "time": null
        }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "8.441502ms",
        "executionTime": "8.387997ms",
        "resultCount": 1,
        "resultSize": 36
    }
}
cbq>
#4

i can solve this problem by having and is not missing.
i am sorry to bother you.

1 Like
#5

Not a bother. SQL for JSON :slight_smile: