SELECT Overview#

Syntax#

SelectStmt#

SelectStmt
         ::= WithClause ( NoTableSelectClause | SelectStmtFromTable) 

WithClause
         ::= 'WITH' non_recursive_cte [, ...]

non_recursive_cte
         ::= cte_name 'AS' '(' SelectStmt ')'

NoTableSelectClause
         ::= 'SELECT' SelectExprList      

SelectStmtFromTable
         ::= SelectStmtBasic 'FROM' TableRefs [WhereClause] [GroupByClause] [HavingClause] [WindowClause] [OrderByClause] [LimitClause]
           
JoinClause
         ::= TableRef JoinType 'JOIN' TableRef [OrderClause] 'ON' Expression 
JoinType ::= 'LAST'           
         
WhereClause
         ::= 'WHERE' Expression
         
GroupByClause
         ::= 'GROUP' 'BY' ByList

HavingClause
         ::= 'HAVING' Expression 
         
WindowClause
         ::= ( 'WINDOW' WindowDefinition ( ',' WindowDefinition )* )   
         
OrderByClause  ::= 'ORDER' 'BY' ByList   

ByList   ::= ByItem ( ',' ByItem )*

ByItem   ::= Expression Order

Order    ::= ( 'ASC' | 'DESC' )?

WindowClauseOptional
         ::= ( 'WINDOW' WindowDefinition ( ',' WindowDefinition )* )?
WindowDefinition
         ::= WindowName 'AS' WindowSpec

WindowSpec
        ::= '(' WindowSpecDetails ')'   

WindowSpecDetails
        ::= [ExistingWindowName] [WindowUnionClause] WindowPartitionClause WindowOrderByClause WindowFrameClause (WindowAttribute)*

WindowUnionClause
	:: = ( 'UNION' TableRefs)

WindowPartitionClause
         ::= ( 'PARTITION' 'BY' ByList ) 

WindowOrderByClause
        ::= ( 'ORDER' 'BY' ByList )

WindowFrameClause
        ::= ( WindowFrameUnits WindowFrameBounds [WindowFrameMaxSize] )

WindowFrameUnits
        ::= 'ROWS'
          | 'ROWS_RANGE'

WindowFrameBounds
        ::= 'BETWEEN' WindowFrameBound 'AND' WindowFrameBound

WindowFrameBound
        ::= ( 'UNBOUNDED' | NumLiteral | IntervalLiteral ) ['OPEN'] 'PRECEDING'
          | 'CURRENT' 'ROW'

WindowAttribute
        ::= WindowExcludeCurrentTime
          | WindowExcludeCurrentRow
          | WindowInstanceNotInWindow

WindowExcludeCurrentTime
        ::= 'EXCLUDE' 'CURRENT_TIME'

WindowExcludeCurrentRow
        ::= 'EXCLUDE' 'CURRENT_ROW'

WindowInstanceNotInWindow
        :: = 'INSTANCE_NOT_IN_WINDOW'

WindowFrameMaxSize
        :: = 'MAXSIZE' NumLiteral

SelectExprList#

SelectExprList
         ::= SelectExpr ( ',' SelectExpr )*
SelectExpr    ::= ( Identifier '.' ( Identifier '.' )? )? '*'
           | ( Expression | '{' Identifier Expression '}' ) ['AS' Identifier]
           
           

TableRefs#

TableRefs
         ::= EscapedTableRef ( ',' EscapedTableRef )*
TableRef ::= TableFactor
           | JoinClause
TableFactor
         ::= TableName [TableAsName]
           | '(' ( ( SelectStmt ) ')' TableAsName | TableRefs ')' )
TableAsName
         ::= 'AS'? Identifier

SELECT Statement#

`SELECT` Statement and Related Clauses	Offline Mode	Online Preview Mode	Online Request Mode	Note
`SELECT Clause`	`✓`	`✓`	`✓`	A list of projection operations, generally including column names, expressions, or ‘*’ for all columns.
`FROM Clause`	`✓`	`✓`	`✓`	The FROM clause indicates the data source. The data source can be one table (`select * from t;`) or multiple tables that LAST JOIN together (see JOIN CLAUSE) or no table ( `select 1+1;`), see NO_TABLE SELECT
`JOIN` Clause	`✓`	`x`	`✓`	The JOIN clause indicates that the data source comes from multiple joined tables. OpenMLDB currently only supports LAST JOIN. For Online Request Mode, please follow the specification of LAST JOIN under Online Request Mode
`WHERE` Clause		`✓`		The WHERE clause is used to set filter conditions, and only the data that meets the conditions will be included in the query result.
`GROUP BY` Clause	`✓`	`✓`		The GROUP BY clause is used to group the query results.The grouping conditions only support simple columns.
`HAVING` Clause	`✓`	`✓`		The HAVING clause is similar to the WHERE clause. The HAVING clause filters data after GROUP BY, and the WHERE clause is used to filter records before aggregation.
`WINDOW` Clause	`✓`	`✓`	`✓`	The WINDOW clause is used to define one or several windows. Windows can be named or anonymous. Users can call aggregate functions on the window to perform analysis (`sql agg_func() over window_name`). For Online Request Mode, please follow the specification of WINDOW Clause under Online Request Mode
`LIMIT` Clause	`✓`	`✓`		The LIMIT clause is used to limit the number of results. OpenMLDB currently only supports one parameter to limit the maximum number of rows of returned data.
`ORDER BY` Clause				Standard SQL also supports the ORDER BY keyword, however OpenMLDB does not support this keyword currently. For example, the query `SELECT * from t1 ORDER BY col1;` is not supported in OpenMLDB.

Warning

The SELECT running in online mode or the stand-alone version may not obtain complete data. The largest number of bytes to scan is limited, namely scan_max_bytes_size, default value is unlimited. But if you set the value of scan_max_bytes_size to a specific value, the SELECT statement will only scan the data within the specified size. If the select results are truncated, the message of reach the max byte ... will be recorded in the tablet’s log, but there will be no error.

Even if the scan_max_bytes_size is set to unlimited, the SELECT statement may failed, e.g. client errors body_size=xxx from xx:xxxx is too large, Fail to parse response from xx:xxxx by baidu_std at client-side. We don’t recommend to use SELECT in online mode or the stand-alone version. If you want to get the count of the online table, please use SELECT COUNT(*) FROM table_name;.

SELECT Overview

Contents