Hi, Good catch, in previous `without null any/all`, any stands for `and`(your choice 1); all stands for `or`(your choice 2);
I think in our having clause, we can keep consistent with where clause. In our where clause, if you write select s1 from root.sg.* where s1 > 1, we will write it as select s1 from root.sg.* where root.sg.d1.s1 > 1 and root.sg.d2.s1 > 1 which is your choice 1. If user really want to use `or`, he can also write sql completely like SELECT count(s1) from root.sg.* group by([1, 10), 2ms) having count(root.sg.d1.s1) !=NULL OR count(root.sg.d2.s1) != NULL Best, -------------------- Yuan Tian On Wed, Jun 29, 2022 at 2:39 PM Eric Pai <[email protected]> wrote: > > 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 > > >
