Hello everyone,
We want to support having clause in IoTDB.
1.Background
Replace grammar ‘without null any/all()’ to help users easier to understand and
use.
2.Specification
2.1Current query grammar
selectStatement
: TRACING? selectClause intoClause? fromClause whereClause? specialClause?
;
whereClause
: WHERE expression
;
expression
: LR_BRACKET unaryInBracket=expression RR_BRACKET
| constant
| time=(TIME | TIMESTAMP)
| fullPathInExpression
| functionName LR_BRACKET expression (COMMA expression)* RR_BRACKET
| (PLUS | MINUS | OPERATOR_NOT) expressionAfterUnaryOperator=expression
| leftExpression=expression (STAR | DIV | MOD) rightExpression=expression
| leftExpression=expression (PLUS | MINUS) rightExpression=expression
| leftExpression=expression (OPERATOR_GT | OPERATOR_GTE | OPERATOR_LT |
OPERATOR_LTE | OPERATOR_SEQ | OPERATOR_DEQ | OPERATOR_NEQ)
rightExpression=expression
| unaryBeforeRegularOrLikeExpression=expression (REGEXP | LIKE)
STRING_LITERAL
| unaryBeforeIsNullExpression=expression OPERATOR_IS OPERATOR_NOT?
NULL_LITERAL
| unaryBeforeInExpression=expression OPERATOR_NOT? (OPERATOR_IN |
OPERATOR_CONTAINS) LR_BRACKET constant (COMMA constant)* RR_BRACKET
| leftExpression=expression OPERATOR_AND rightExpression=expression
| leftExpression=expression OPERATOR_OR rightExpression=expression
;
‘where s1 is not null’ can only filter raw data, so it can be a replacement
for 'without null' for raw data;
But we also need a replacement for ‘without null’ for data after ‘group by’, so
we want to support ‘having’ clause.
2.2Having clause grammar
selectStatement
: TRACING? selectClause intoClause? fromClause whereClause? specialClause?
;
: specialLimit #specialLimitStatement
| orderByTimeClause specialLimit? #orderByTimeStatement
| groupByTimeClause orderByTimeClause? specialLimit? #groupByTimeStatement
| groupByFillClause orderByTimeClause? specialLimit? #groupByFillStatement
| groupByLevelClause orderByTimeClause? specialLimit? #groupByLevelStatement
| fillClause orderByTimeClause? specialLimit? #fillStatement
;
specialLimit
: limitClause slimitClause? alignByDeviceClauseOrDisableAlign?
#limitStatement
| slimitClause limitClause? alignByDeviceClauseOrDisableAlign?
#slimitStatement
| withoutNullClause limitClause? slimitClause?
alignByDeviceClauseOrDisableAlign? #withoutNullStatement
| havingClause? #havingStatement
| alignByDeviceClauseOrDisableAlign
#alignByDeviceClauseOrDisableAlignStatement
;
havingClause
: HAVING expression;
The previous example can be resolved using the following sql:
select count(s1) from root.sg.d group by([1, 10), 2ms) having count(s1) is not
null
3.Restriction
Having clause must be used with group by together and predicate expression must
be related to some aggregation result instead of raw data, otherwise we will
throw exception in anaylizing stage. (For example, we use ‘having count(s1) is
not null’ instead of ’having s1 is not null' in previous sql example)
Thanks,
—————————————————
Weihao Li
Timecho
