# Standard SQL Operators
'' ( '''' )
RETURNS ''''
LANGUAGE SQL
[ SPECIFIC '''' | [NOT] DETERMINISTIC ]
CONTAINS SQL
[ READS SQL DATA ]
[ MODIFIES SQL DATA ]
[ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
RETURN ''''
```
SPECIFIC assigns a specific function name that is unique within the application. Note that the regular function name does not need to be unique (two or more functions may share the same name, as long as they are distinguishable by their parameter list).
DETERMINISTIC / NOT DETERMINISTIC indicates whether a function will always return the same result for a given set of parameter values. This may be used by your application for query optimization.
READS SQL DATA and MODIFIES SQL DATA indicate whether the function potentially reads or modifies SQL data, respectively. If a function attempts to read data from tables or streams without READS SQL DATA being specified, or insert to a stream or modify a table without MODIFIES SQL DATA being specified, an exception will be raised.
RETURNS NULL ON NULL INPUT and CALLED ON NULL INPUT indicate whether the function is defined as returning null if any of its parameters are null. If left unspecified, the default is CALLED ON NULL INPUT.
A SQL-defined function body consists only of a single RETURN statement.
## Examples
[ DESCRIPTION '' ] AS
```
where streaming-insert is an insert statement such as:
```
INSERT INTO ''stream-name'' SELECT "columns" FROM
```
# INSERT
:=
INSERT [ EXPEDITED ]
INTO [ ( insert-column-specification ) ]
< query >
:= < simple-identifier-list >
:=
[ , < simple-identifier-list > ]
```
For a discussion of VALUES, see [SELECT statement](sql-reference-select.md).
## Pump Stream Insert
:=
| [ ALL ]
| VALUES { , }...
| '(' ')'
:=
EXCEPT
| INTERSECT
| UNION
:=
[ ROW ] ( { , }... )
```
## select
:=
SELECT [ STREAM] [ DISTINCT | ALL ]
FROM
[ ]
[ ]
[ ]
[ ]
[ ]
```
## The STREAM keyword and the principle of streaming SQL
uses the following items after the STREAM keyword:
```
:=
{ , }...
:=
[ [ AS ] ]
:=
|
:=
. * | * |
```
## Expressions
:=
FROM { , }...
:=
[ ] [ ]
|
:=
:= OVER
:=
(
|
| ORDER BY
|
)
:=
{ ROWS | RANGE }
{ BETWEEN
{ UNBOUNDED PRECEDING
| CURRENT ROW
| { PRECEDING | FOLLOWING }
}
AND
{ UNBOUNDED FOLLOWING
| CURRENT ROW
| { PRECEDING | FOLLOWING }
}
| { UNBOUNDED { PRECEDING | FOLLOWING }
| CURRENT ROW
| { PRECEDING | FOLLOWING }
}
}
```
For charts on window-specification and windowing-clause, see the [WINDOW Clause (Sliding Windows)](sql-reference-window-clause.md) under the Window statement.
```
:=
[ AS ] [ '(' { , }... ')' ]
:=
CROSS JOIN
| NATURAL JOIN
| JOIN
[ USING '(' { , }... ')'
| ON
]
:=
INNER
| [ OUTER ]
:=
LEFT
| RIGHT
| FULL
```
, where:
+ the expression can be an aggregate; and,
+ any column name used in the GROUP BY clause must also be in the SELECT statement.
Additionally, a column that is not named in or derivable from the GROUP BY clause cannot appear in the SELECT statement except within aggregations, such as SUM (allOrdersValue).
What derivable means is that a column specified in the GROUP BY clause enables access to the column you want to include in the SELECT clause. If a column is derivable, the SELECT statement can specify it even though it is not explicitly named in the GROUP BY clause.
Example: If the key to a table is in the GROUP BY clause, then any of that table's columns can appear in the select-list because, given that key, such columns are considered accessible.
The GROUP BY clause groups selected rows based on the value of the grouping expressions, returning a single summary row of information for each group of rows that have identical values in all columns.
Note that for these purposes, the value NULL is considered equal to itself and not equal to any other value. These are the same semantics as for the IS NOT DISTINCT FROM operator.
## Streaming GROUP BY
,`
``
where any column name used in the GROUP BY clause needs to be in the SELECT statement; the expression can be an aggregate. Additionally, a column name that does not appear in the GROUP BY clause cannot appear in the SELECT statement except within aggregations, or if, as above, access to the column can be created from column that you specify in the GROUP BY clause.
For example, the following query, which computes the product counts per hour, uses the monotonic expression FLOOR(Orders.ROWTIME TO HOUR) is therefore valid:
```
SELECT STREAM FLOOR(Orders.ROWTIME TO HOUR) AS theHour, prodId, COUNT(*)
FROM Orders
GROUP BY FLOOR(Orders.ROWTIME TO HOUR), prodId
```
One of the expressions in the GROUP BY must be monotonic or time-based. For example GROUP BY FLOOR(S.ROWTIME) TO HOUR will yield one output row per hour for the previous hour's input rows. The GROUP BY can specify additional partitioning terms. For example, GROUP BY FLOOR(S.ROWTIME) TO HOUR, USERID will yield one output row per hour per USERID value. If you know for a fact that an expression is monotonic, you can declare it so by using the [Monotonic Function](sql-reference-monotonic.md). If the actual data are not monotonic, the resulting system behavior is indeterminate: results may not be as expected or desired.
See the topic [Monotonic Function](sql-reference-monotonic.md) in this guide for more details.
Duplicate rowtimes can occur in a stream, and as long as the ROWTIME value is the same, the GROUP BY operation will keep accumulating rows. In order to emit a row, the ROWTIME value has to change at some point.
# WHERE clause
WITHIN
```
**Restrictions**
The T-sort has the following restrictions:
+ The datatype of the ORDER BY expression must be timestamp.
+ The partially-ordered expression must be present in the select list of the query with the alias ROWTIME.
+ The leading expression of the ORDER BY clause must not contain the ROWTIME function and must not use the DESC keyword.
+ The ROWTIME column needs to be fully qualified. For example:
+ `ORDER BY FLOOR(ROWTIME TO MINUTE), ...` fails.
+ `ORDER BY FLOOR(s.ROWTIME TO MINUTE), ...` works.
If any of these requirements are not met, the statement will fail with errors.
Additional notes:
+ You cannot use incoming rowtimebounds. These are ignored by the system.
+ If evaluates to NULL, the corresponding row is discarded.
# ROWTIME