asterixdb/asterix-doc/src/main/grammar/sqlpp.ebnf

Expr ::= OperatorExpr | QuantifiedExpr

OperatorExpr ::= PathExpr
                 | Operator OperatorExpr
                 | OperatorExpr Operator OperatorExpr?
                 | OperatorExpr "BETWEEN" OperatorExpr "AND" OperatorExpr

QuantifiedExpr::= ( "SOME" | "EVERY" | ( "SOME" "AND" "EVERY" ) ) Variable "IN" Expr ( "," Variable "IN" Expr )* "SATISFIES" Expr ("END")?

PathExpr ::= PrimaryExpr ("." Identifier | "[" Expr (":" (Expr)? )? "]")*

PrimaryExpr ::=  Literal
                |VariableRef
                |ParameterRef
                |ParenthesizedExpr
                |FunctionCall
                |CaseExpr
                |Constructor

Literal ::=  StringLiteral
            |IntegerLiteral
            |FloatLiteral
            |DoubleLiteral
            |"NULL"
            |"MISSING"
            |"TRUE"
            |"FALSE"

ParenthesizedExpr ::= ("(" Expr ")") | Subquery

FunctionCall ::= OrdinaryFunctionCall | AggregateFunctionCall | WindowFunctionCall

OrdinaryFunctionCall ::= (DataverseName ".")? Identifier "(" Expr ("," Expr)* ")"

AggregateFunctionCall ::= Identifier "(" ("DISTINCT")? Expr ")" ("FILTER" "(" "WHERE" Expr ")")?

CaseExpr ::= SimpleCaseExpr | SearchedCaseExpr

SimpleCaseExpr ::= "CASE" Expr ("WHEN" Expr "THEN" Expr)+ ("ELSE" Expr)? "END"

SearchedCaseExpr ::= "CASE" ("WHEN" Expr "THEN" Expr)+ ("ELSE" Expr)? "END"

Constructor ::= ObjectConstructor | ArrayConstructor | ParenthesizedArrayConstructor | MultisetConstructor

ObjectConstructor ::= "{" ( Expr ( ":" Expr )? ( "," Expr ( ":" Expr )? )* )? "}"

ArrayConstructor ::= "[" Expr ("," Expr)* "]"

ParenthesizedArrayConstructor ::= "(" Expr ("," Expr)+ ")"

MultisetConstructor ::= "{{" Expr ("," Expr)* "}}"

Query ::= (Expr | Selection)

Selection ::= (WithClause | LetClause)? QueryBlock UnionOption* OrderByClause? ( LimitClause | OffsetClause )?

QueryBlock ::= SelectClause ( ( LetClause WhereClause? ) | StreamGenerator )?
             | StreamGenerator SelectClause

StreamGenerator::= FromClause LetClause? WhereClause? (GroupByClause LetClause? HavingClause?)?

SelectClause ::= "SELECT" ("DISTINCT" | "ALL")? ( "VALUE" Expr | Projection ("," Projection)*)

Projection ::= (Expr ("AS"? Identifier)?) | (VariableRef "." "*") | "*"

FromClause ::= "FROM" FromTerm ("," FromTerm)*

FromTerm ::= NamedExpr (JoinStep | UnnestStep)*

NamedExpr ::= Expr ("AS"? Variable)?

JoinStep ::= ( ("INNER" | ( ( "LEFT" | "RIGHT" ) "OUTER"?))? "JOIN" NamedExpr "ON" Expr ) |
             ( "CROSS" "JOIN" NamedExpr )

UnnestStep ::= ("INNER" | ( "LEFT" "OUTER"?))? "UNNEST" NamedExpr

LetClause ::= "LET" Variable "=" Expr ("," Variable "=" Expr)*

WhereClause ::= "WHERE" Expr

GroupByClause ::= "GROUP BY" GroupingElement ("," GroupingElement)* GroupAsClause?

GroupingElement ::= OrdinaryGroupingSet
              | ( "GROUPING" "SETS" "(" GroupingElement ("," GroupingElement)* ")" )
              | ( ( "ROLLUP" | "CUBE" ) "(" OrdinaryGroupingSet ( "," OrdinaryGroupingSet )* ")" )
              | ( "(" ")" )

OrdinaryGroupingSet ::= NamedExpr | ( "(" NamedExpr ( "," NamedExpr )* ")")

GroupAsClause ::= "GROUP AS" Variable

HavingClause ::= "HAVING" Expr

Selection ::= WithClause? QueryBlock UnionOption* OrderByClause? ( LimitClause | OffsetClause )?

UnionOption ::= "UNION ALL" (QueryBlock | Subquery)

WithClause ::= "WITH" Variable "AS" Expr ("," Variable "AS" Expr)*

OrderbyClause ::= "ORDER BY" Expr ( "ASC" | "DESC" )? ( "NULLS" ( "FIRST" | "LAST" ) )? ( "," Expr ( "ASC" | "DESC" )? ( "NULLS" ( "FIRST" | "LAST" ) )? )*

LimitClause ::= "LIMIT" Expr OffsetClause?

OffsetClause ::= "OFFSET" Expr

Subquery ::= "(" Selection ")"

WindowFunctionCall ::= WindowFunctionType "(" WindowFunctionArguments ")" WindowFunctionOptions? "OVER" (Variable "AS")? "(" WindowDefinition")"

WindowFunctionType ::= AggregateFunction | WindowFunction

WindowFunctionArguments ::=  ( ("DISTINCT")? Expr | (Expr ("," Expr ("," Expr)? )? )? )

WindowFunctionOptions ::= ("FROM" ( "FIRST" | "LAST" ))? (( "RESPECT" | "IGNORE" ) "NULLS")?

WindowDefinition ::= WindowPartitionClause? (WindowOrderClause (WindowFrameClause WindowFrameExclusion?)?)?

WindowPartitionClause ::= "PARTITION" "BY" Expr ("," Expr)*

WindowOrderClause ::= "ORDER" "BY" Expr ("ASC" | "DESC")? ( "NULLS" ( "FIRST" | "LAST" ) )? ("," Expr ("ASC" | "DESC")? ( "NULLS" ( "FIRST" | "LAST" ) )? )*

WindowFrameClause ::= ("ROWS" | "RANGE" | "GROUPS") WindowFrameExtent

WindowFrameExtent ::= ( ( "UNBOUNDED" | Expr ) "PRECEDING" | "CURRENT" "ROW" ) |
      "BETWEEN" ( "UNBOUNDED" "PRECEDING" | "CURRENT" "ROW" | Expr ( "PRECEDING" | "FOLLOWING" ) )
      "AND" ( "UNBOUNDED" "FOLLOWING" | "CURRENT" "ROW" | Expr ( "PRECEDING" | "FOLLOWING" ) )

WindowFrameExclusion ::= "EXCLUDE" ( "CURRENT" "ROW" | "GROUP" | "TIES" | "NO" "OTHERS" )

Stmnt::= (SingleStmnt ";")+ "EOF"

SingleStmnt ::= UseStmnt
               |SetStmnt
               |FunctionDeclaration
               |Query
               |CreateStmnt
               |DropStmnt
               |LoadStmnt
               |InsertStmnt
               |UpsertStmnt
               |DeleteStmnt

UseStmnt ::= "USE" DataverseName

SetStmnt ::= "SET" Identifier StringLiteral

CreateStmnt ::= CreateDataverse
              | CreateType
              | CreateDataset
              | CreateIndex
              | CreateSynonym
              | CreateFunction
              | CreateView

DataverseName ::= Identifier ("." Identifier)*

QualifiedName ::= (DataverseName ".")? Identifier

DoubleQualifiedName ::= (DataverseName ".")? Identifier "." Identifier

CreateDataverse ::= "CREATE" "DATAVERSE" DataverseName ("IF" "NOT" "EXISTS")?

CreateType ::= "CREATE" "TYPE" QualifiedName ("IF" "NOT" "EXISTS")? "AS" ObjectTypeDef

ObjectTypeDef ::= ("CLOSED" | "OPEN")? "{" ObjectField ("," ObjectField)* "}"

ObjectField ::= Identifier ":" Identifier "?"?

TypeExpr ::= ObjectTypeDef
            | ArrayTypeDef
            | MultisetTypeDef
            | TypeReference

ArrayTypeDef ::= "[" TypeExpr "]"

MultisetTypeDef ::= "{{" TypeExpr "}}"

TypeReference ::= QualifiedName

CreateDataset ::= CreateInternalDataset | CreateExternalDataset

CreateInternalDataset ::= ( "INTERNAL" )? "DATASET" QualifiedName DatasetTypeDef ( "WITH" "META"  DatasetTypeDef )?
                            ("IF" "NOT" "EXISTS")?
                            PrimaryKey
                            ( "HINTS" Properties )?
                            ( "WITH" "FILTER" "ON" NestedField )?
                            ( "WITH" ObjectConstructor )?

CreateExternalDataset ::= "EXTERNAL" "DATASET" QualifiedName DatasetTypeDef
                            ("IF" "NOT" "EXISTS")?
                            "USING" AdapterName Configuration
                            ( "HINTS" Properties )?
                            ( "WITH" ObjectConstructor )?

DatasetTypeDef ::= ( "(" TypeReference ")" )
                | ( "(" DatasetFieldDef ("," DatasetFieldDef )* ")" ( ("CLOSED" | "OPEN") "TYPE" )? )

DatasetFieldDef ::= Identifier TypeReference ("NOT" "UNKNOWN")?

PrimaryKey ::= "PRIMARY" "KEY" NestedField ( "," NestedField )* ("AUTOGENERATED")?

NestedField ::= Identifier ( "." Identifier )*

AdapterName ::= Identifier

Configuration::= "(" (KeyValuePair ("," KeyValuePair)*)? ")"

KeyValuePair ::= "(" StringLiteral "=" StringLiteral ")"

Properties ::= ( "(" Identifier "=" ( StringLiteral | IntegerLiteral ) ( "," Identifier "=" ( StringLiteral | IntegerLiteral ) )* ")" )?

CreateIndex ::= CreateSecondaryIndex | CreatePrimaryKeyIndex

CreateSecondaryIndex ::= "CREATE" "INDEX" Identifier ("IF" "NOT" "EXISTS")? "ON" QualifiedName
                       "(" IndexedElement ( "," IndexedElement )* ")" ("TYPE" IndexType)? ("ENFORCED")?
                       (( "EXCLUDE" | "INCLUDE" ) "UNKNOWN" "KEY")?

CreatePrimaryKeyIndex ::= "CREATE" "PRIMARY" "INDEX" Identifier? ("IF" "NOT" "EXISTS")? "ON" QualifiedName ("TYPE" "BTREE")?

IndexedElement ::= ArrayIndexElement
               | IndexField
               | "(" ( ArrayIndexElement | IndexField ) ")"

ArrayIndexElement ::= "UNNEST" NestedField ( "UNNEST" NestedField )*
                      ( ( ":" TypeReference ) | ( "SELECT" NestedField ( ":" TypeReference )? ( "," NestedField ( ":" TypeReference )? )* ) )?

IndexField ::= NestedField ( ":" TypeReference "?"? )?

IndexType ::= "BTREE"
             |"RTREE"
             |"KEYWORD"
             |"FULLTEXT"
             |"NGRAM" "(" IntegerLiteral ")"

CreateSynonym ::= "CREATE" "SYNONYM" QualifiedName "FOR" QualifiedName ("IF" "NOT" "EXISTS")?

FunctionDeclaration ::= "DECLARE" "FUNCTION" Identifier "(" ( (Identifier ("," Identifier)*) | "..." )? ")" "{" Expr "}"

CreateFunction ::= "CREATE" ("OR" "REPLACE")? "FUNCTION" QualifiedName "(" FunctionParameters? ")" ("IF" "NOT" "EXISTS")?
                  ( ("{" Expr "}") | ExternalFunctionDef )

FunctionParameters ::=  ( Identifier ((":")? TypeExpr)? ("," Identifier ((":")? TypeExpr)? )* ) | "..."

ExternalFunctionDef ::= ("RETURNS" TypeExpr)? "AS" StringLiteral ("," StringLiteral )* "AT" QualifiedName ("WITH" ObjectConstructor)?

CreateView ::= "CREATE" ("OR" "REPLACE")? "VIEW" QualifiedName ("IF" "NOT" "EXISTS")? "AS" Selection

DropStmnt ::= "DROP" ("DATAVERSE" DataverseName
                     | ("TYPE" |"DATASET" |"SYNONYM" |"VIEW") QualifiedName
                     | "INDEX" DoubleQualifiedName
                     | "FUNCTION" FunctionSignature ) ("IF" "EXISTS")?

FunctionSignature ::= QualifiedName ( ( "(" ( FunctionParameters? | IntegerLiteral ) ")" ) | ("@" IntegerLiteral) )

LoadStmnt ::= "LOAD" "DATASET" QualifiedName "USING" AdapterName Configuration ("PRE-SORTED")?

InsertStmnt ::= "INSERT" "INTO" QualifiedName ("AS" Variable)? Query ("RETURNING" Expr)?

UpsertStmnt ::= "UPSERT" "INTO" QualifiedName ("AS" Variable)? Query ("RETURNING" Expr)?

DeleteStmnt ::= "DELETE" "FROM" QualifiedName (("AS")? Variable)? ("WHERE" Expr)?