AS expressions are not "caught" by the GROUP BY enforcement

n1ql

#1

Why

SELECT COUNT(*), SUBSTR(t, 0, 10) AS date, did, type
FROM sync_gateway WHERE type='bcn_scan' AND date BETWEEN '2016-11-01' AND '2016-12-15' AND array_length(bcn)==0
GROUP BY did, SUBSTR(t, 0, 10), type
ORDER BY did ASC, date ASC;

is allowed to run, whereas

SELECT COUNT(*), SUBSTR(t, 0, 10) AS date, did, type
FROM sync_gateway WHERE type='bcn_scan' AND date BETWEEN '2016-11-01' AND '2016-12-15' AND array_length(bcn)==0
GROUP BY did, date, type
ORDER BY did ASC, date ASC;

(SUBSTR(t, 0, 10) substituted with date)

gives

"errors": [
    {
        "code": 4210,
        "msg": "Expression must be a group key or aggregate: substr((`sync_gateway`.`t`), 0, 10)"
    }
],

#2

It is not because of the AS. Only aggregates and GROUP BY keys are allowed in the SELECT list.


#3

I am using a placeholder for the expression (or variable in “programming”)

Shouldn’t SUBSTR(t, 0, 10) AS date enable both SUBSTR(t, 0, 10) and date as valid representations of the “same symbol” in the GROUP BY clause?


#4

You can use a LET if you want that behavior.


#5

My results

cbq> LET date = SUBSTR(t, 0, 10);
{
    "requestID": "bca44454-c08a-4f31-a256-58e42b57bd10",
    "errors": [
        {
            "code": 3000,
            "msg": "syntax error - at LET"
        }
    ],
    "status": "fatal",
    "metrics": {
        "elapsedTime": "682.275µs",
        "executionTime": "631.13µs",
        "resultCount": 0,
        "resultSize": 0,
        "errorCount": 1
    }
}

or

cbq> LET date = SUBSTR(t, 0, 10) SELECT COUNT(*) AS non_zero_instances, date, did FROM sync_gateway GROUP BY did, date HAVING type='bcn_scan' AND t BETWEEN '2016-11-01' AND '2016-12-15' AND array_length(bcn)<>0 ORDER BY date ASC, did ASC;
{
    "requestID": "8be31634-b6ed-499a-9e52-97cbb4c810f8",
    "errors": [
        {
            "code": 3000,
            "msg": "syntax error - at LET"
        }
    ],
    "status": "fatal",
    "metrics": {
        "elapsedTime": "477.894µs",
        "executionTime": "442.169µs",
        "resultCount": 0,
        "resultSize": 0,
        "errorCount": 1
    }
}

I am sorry to waste precious time in these stuff, but I have limited SQL (and the like) experience


#6

No worries. Please see the N1QL documentation first.


#7

I tried to do that already.

I even went to “New Documentation”, which, I don’t know why walking around different versions is so hard (I have to re-navigate to my article)

I also tried to look-up SQL definition of let, which is the ancestor of the language. It seems to be a variable-declaration, so I tried these:

cbq> LET date = SUBSTR(t, 0, 10);
{
    "requestID": "a443d04e-1138-4b89-9676-88fe882b590e",
    "errors": [
        {
            "code": 3000,
            "msg": "syntax error - at LET"
        }
    ],
    "status": "fatal",
    "metrics": {
        "elapsedTime": "621.238µs",
        "executionTime": "559.75µs",
        "resultCount": 0,
        "resultSize": 0,
        "errorCount": 1
    }
}
cbq> LET date = SUBSTR(t, 0, 10); SELECT COUNT(*) AS non_zero_instances, date, did FROM sync_gateway GROUP BY did, date HAVING type='bcn_scan' AND t BETWEEN '2016-11-01' AND '2016-12-15' AND array_length(bcn)<>0 ORDER BY date ASC, did ASC;
{
    "requestID": "633edb43-8e82-416f-8a3c-78a4b4a1177c",
    "errors": [
        {
            "code": 3000,
            "msg": "syntax error - at LET"
        }
    ],
    "status": "fatal",
    "metrics": {
        "elapsedTime": "704.102µs",
        "executionTime": "644.982µs",
        "resultCount": 0,
        "resultSize": 0,
        "errorCount": 1
    }
}
cbq>

Still no luck.

Also, it would be usefull if cbq reported a bigger context of where the error occured (or # of char where the error is located)


#8

Yes, multiple doc versions is a real problem.

LET is a clause, not a statement. Try this.

SELECT COUNT(*), date, did, type
FROM sync_gateway
LET date = SUBSTR(t, 0, 10)
WHERE type='bcn_scan' AND date BETWEEN '2016-11-01' AND '2016-12-15' AND array_length(bcn)==0
GROUP BY did, date, type
ORDER BY did ASC, date ASC;

#9

Thank you for your time (obviously it works)

However, I do think that documentation could be at least complete with examples (versioning needs work too, but I guess that would be a really big project).
I know that only begginers would appreciate that, but we do exist :smiley:


#10

Thank you @sntentos. Copying @prasad re documentation and examples, for beginners and veterans alike :slight_smile: