You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@iotdb.apache.org by "18110526956@163.com" <18...@163.com> on 2022/06/29 06:20:06 UTC

support grammar 'HAVING' in IoTDB

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

Re: support grammar 'HAVING' in IoTDB

Posted by Yuan Tian <ja...@gmail.com>.
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 <er...@hotmail.com> 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，“18110526956@163.com”<18...@163.com> 写入:
>
>     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
>
>
>

Re: support grammar 'HAVING' in IoTDB

Posted by Eric Pai <er...@hotmail.com>.
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，“18110526956@163.com”<18...@163.com> 写入:

    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