This is really a good feature! Unlike relational database query, the 'column' name in IoTDB is a string concatenated by the prefix path in FROM clause and the suffix path in SELECT clause. In some cases maybe there are more than 1 output columns. If so, does one simple HAVING clause still work and bring no ambiguity?
E.g. what's the result in the following query statement? SELECT count(s1) from root.sg.* group by([1, 10), 2ms) having count(s1) is not null. If there're two timeseries root.sg.d1.s1 and root.sg.d2.s1, then the result set will contain 2 data columns count(root.sg.d1.s1) and count(root.sg.d2.s1). So what does 'count(s1) is not null' mean? - Choice1: count(root.sg.d1.s1) !=NULL AND count(root.sg.d2.s1) != NULL - Choice2: count(root.sg.d1.s1) !=NULL OR count(root.sg.d2.s1) != NULL - Choice3: A SQL query error is returned. Any wildcard query with HAVING clause causing ambiguity is forbidden. 在 2022/6/29 14:20，“[email protected]”<[email protected]> 写入: 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
