This is an automated email from the ASF dual-hosted git repository.
cloud-fan pushed a commit to branch branch-4.x
in repository https://gitbox.apache.org/repos/asf/spark.git
The following commit(s) were added to refs/heads/branch-4.x by this push:
new a87f5becb00a [SPARK-57053][SQL][TEST] Expand time-window.sql SQL test
coverage
a87f5becb00a is described below
commit a87f5becb00a17fad16dcbbb38b89b93fe668f10
Author: Vladimir Golubev <[email protected]>
AuthorDate: Tue May 26 17:39:40 2026 +0800
[SPARK-57053][SQL][TEST] Expand time-window.sql SQL test coverage
### What changes were proposed in this pull request?
Adds ~70 new positive and negative test cases to `time-window.sql`,
covering gaps in the existing tumbling/sliding/session window coverage:
- Tumbling and sliding window edge cases: NTZ time column, subsecond
precision, null timestamps, negative/zero/non-literal/unparseable duration,
slide >= duration, startTime > slide, ROLLUP/CUBE/GROUPING SETS variants for
sliding windows.
- `window_time()` function: basic event-time extraction, NTZ, wrong
argument type/arity, and struct-input rejection paths.
- `session_window` parity with tumbling/sliding coverage: basic
aggregation, conditional gap duration (from the docs example), column-valued
gap duration, NTZ, null timestamps, subsecond, negative/zero gap, wrong arg
count, multiple-window error, and acceptance/rejection of session_window in
WHERE / QUALIFY / SELECT *.
- Nested and stacked windows: nested window in GROUP BY, nested literal
windows in SELECT/GROUP BY ALL, nested window via subquery, stacked sibling
windows.
- Window placement: in simple projection, in SELECT with GROUP BY ALL, in
select+group by combinations, in EXISTS subquery, in PIVOT aggregate.
### Why are the changes needed?
`time-window.sql` previously covered only ~23 cases, all GROUP BY variants
of `window()` / `session_window()`. Resolver work on the single-pass analyzer
surfaced multiple uncovered code paths (`window_time()` extraction, NTZ inputs,
struct-input handling, session window with conditional/column gap). This PR
expands the suite so future analyzer changes have a broader regression surface.
### Does this PR introduce _any_ user-facing change?
No.
### How was this patch tested?
Existing test (`time-window.sql` + `time-window.sql_analyzer_test`).
Goldens regenerated with `SPARK_GENERATE_GOLDEN_FILES=1`.
### Was this patch authored or co-authored using generative AI tooling?
Generated-by: Claude Code (claude-opus-4-7)
Closes #56098 from vladimirg-db/vladimirg-db/time-window-tests-import.
Authored-by: Vladimir Golubev <[email protected]>
Signed-off-by: Wenchen Fan <[email protected]>
(cherry picked from commit d61dae7af15ff524f8869174d3f6ad9bb2047b00)
Signed-off-by: Wenchen Fan <[email protected]>
---
.../sql-tests/analyzer-results/time-window.sql.out | 1479 ++++++++++++++++++++
.../resources/sql-tests/inputs/time-window.sql | 547 ++++++++
.../sql-tests/results/time-window.sql.out | 1349 ++++++++++++++++++
3 files changed, 3375 insertions(+)
diff --git
a/sql/core/src/test/resources/sql-tests/analyzer-results/time-window.sql.out
b/sql/core/src/test/resources/sql-tests/analyzer-results/time-window.sql.out
index a6b88d42e58e..c5d1a84e9097 100644
--- a/sql/core/src/test/resources/sql-tests/analyzer-results/time-window.sql.out
+++ b/sql/core/src/test/resources/sql-tests/analyzer-results/time-window.sql.out
@@ -523,3 +523,1482 @@ Sort [start#x ASC NULLS FIRST], true
+- Project [named_struct(start,
precisetimestampconversion(precisetimestampconversion(ts#x, TimestampType,
LongType), LongType, TimestampType), end,
knownnullable(precisetimestampconversion(precisetimestampconversion(cast(ts#x +
cast(5 minutes as interval) as timestamp), TimestampType, LongType), LongType,
TimestampType))) AS session_window#x, ts#x]
+- SubqueryAlias tab
+- LocalRelation [ts#x]
+
+
+-- !query
+CREATE OR REPLACE TEMPORARY VIEW ts_vals AS
+SELECT * FROM VALUES
+ (CAST('2021-01-01 00:00:00' AS TIMESTAMP), 10),
+ (CAST('2021-01-01 00:01:00' AS TIMESTAMP), 20),
+ (CAST('2021-01-01 00:06:00' AS TIMESTAMP), 30)
+AS tab(ts, v)
+-- !query analysis
+CreateViewCommand `ts_vals`, SELECT * FROM VALUES
+ (CAST('2021-01-01 00:00:00' AS TIMESTAMP), 10),
+ (CAST('2021-01-01 00:01:00' AS TIMESTAMP), 20),
+ (CAST('2021-01-01 00:06:00' AS TIMESTAMP), 30)
+AS tab(ts, v), false, true, LocalTempView, UNSUPPORTED, true
+ +- Project [ts#x, v#x]
+ +- SubqueryAlias tab
+ +- LocalRelation [ts#x, v#x]
+
+
+-- !query
+SELECT
+ a,
+ window.start,
+ window.end,
+ count(*) AS cnt
+FROM ts_data
+GROUP BY a, window(ts, '5 minutes')
+ORDER BY a, start
+-- !query analysis
+Sort [a#x ASC NULLS FIRST, start#x ASC NULLS FIRST], true
++- Aggregate [a#x, window#x], [a#x, window#x.start AS start#x, window#x.end AS
end#x, count(1) AS cnt#xL]
+ +- Project [named_struct(start,
knownnullable(precisetimestampconversion(((precisetimestampconversion(ts#x,
TimestampType, LongType) - CASE WHEN (((precisetimestampconversion(ts#x,
TimestampType, LongType) - 0) % 300000000) < cast(0 as bigint)) THEN
(((precisetimestampconversion(ts#x, TimestampType, LongType) - 0) % 300000000)
+ 300000000) ELSE ((precisetimestampconversion(ts#x, TimestampType, LongType) -
0) % 300000000) END) - 0), LongType, TimestampType)), end,
knownnullable(precise [...]
+ +- Filter isnotnull(ts#x)
+ +- SubqueryAlias ts_data
+ +- View (`ts_data`, [a#x, ts#x])
+ +- Project [cast(a#x as string) AS a#x, cast(ts#x as timestamp)
AS ts#x]
+ +- Project [a#x, ts#x]
+ +- SubqueryAlias tab
+ +- LocalRelation [a#x, ts#x]
+
+
+-- !query
+SELECT
+ window.start,
+ window.end,
+ count(*) AS cnt
+FROM ts_data
+GROUP BY window(ts, '1 hour')
+ORDER BY start
+-- !query analysis
+Sort [start#x ASC NULLS FIRST], true
++- Aggregate [window#x], [window#x.start AS start#x, window#x.end AS end#x,
count(1) AS cnt#xL]
+ +- Project [named_struct(start,
knownnullable(precisetimestampconversion(((precisetimestampconversion(ts#x,
TimestampType, LongType) - CASE WHEN (((precisetimestampconversion(ts#x,
TimestampType, LongType) - 0) % 3600000000) < cast(0 as bigint)) THEN
(((precisetimestampconversion(ts#x, TimestampType, LongType) - 0) % 3600000000)
+ 3600000000) ELSE ((precisetimestampconversion(ts#x, TimestampType, LongType)
- 0) % 3600000000) END) - 0), LongType, TimestampType)), end, knownnullable(pre
[...]
+ +- Filter isnotnull(ts#x)
+ +- SubqueryAlias ts_data
+ +- View (`ts_data`, [a#x, ts#x])
+ +- Project [cast(a#x as string) AS a#x, cast(ts#x as timestamp)
AS ts#x]
+ +- Project [a#x, ts#x]
+ +- SubqueryAlias tab
+ +- LocalRelation [a#x, ts#x]
+
+
+-- !query
+SELECT
+ window.start,
+ window.end,
+ count(*) AS cnt
+FROM ts_data
+GROUP BY window(ts, '10 minutes', '5 minutes')
+ORDER BY window.start
+-- !query analysis
+Sort [start#x ASC NULLS FIRST], true
++- Aggregate [window#x], [window#x.start AS start#x, window#x.end AS end#x,
count(1) AS cnt#xL]
+ +- Filter isnotnull(ts#x)
+ +- Expand [[named_struct(start,
knownnullable(precisetimestampconversion(((precisetimestampconversion(ts#x,
TimestampType, LongType) - CASE WHEN (((precisetimestampconversion(ts#x,
TimestampType, LongType) - 0) % 300000000) < cast(0 as bigint)) THEN
(((precisetimestampconversion(ts#x, TimestampType, LongType) - 0) % 300000000)
+ 300000000) ELSE ((precisetimestampconversion(ts#x, TimestampType, LongType) -
0) % 300000000) END) - 0), LongType, TimestampType)), end, knownnullable(prec
[...]
+ +- SubqueryAlias ts_data
+ +- View (`ts_data`, [a#x, ts#x])
+ +- Project [cast(a#x as string) AS a#x, cast(ts#x as timestamp)
AS ts#x]
+ +- Project [a#x, ts#x]
+ +- SubqueryAlias tab
+ +- LocalRelation [a#x, ts#x]
+
+
+-- !query
+SELECT
+ window.start,
+ window.end,
+ count(*) AS cnt
+FROM ts_data
+GROUP BY window(ts, '7 minutes', '3 minutes')
+ORDER BY window.start
+-- !query analysis
+Sort [start#x ASC NULLS FIRST], true
++- Aggregate [window#x], [window#x.start AS start#x, window#x.end AS end#x,
count(1) AS cnt#xL]
+ +- Filter ((ts#x >= window#x.start) AND (ts#x < window#x.end))
+ +- Expand [[named_struct(start,
knownnullable(precisetimestampconversion(((precisetimestampconversion(ts#x,
TimestampType, LongType) - CASE WHEN (((precisetimestampconversion(ts#x,
TimestampType, LongType) - 0) % 180000000) < cast(0 as bigint)) THEN
(((precisetimestampconversion(ts#x, TimestampType, LongType) - 0) % 180000000)
+ 180000000) ELSE ((precisetimestampconversion(ts#x, TimestampType, LongType) -
0) % 180000000) END) - 0), LongType, TimestampType)), end, knownnullable(prec
[...]
+ +- SubqueryAlias ts_data
+ +- View (`ts_data`, [a#x, ts#x])
+ +- Project [cast(a#x as string) AS a#x, cast(ts#x as timestamp)
AS ts#x]
+ +- Project [a#x, ts#x]
+ +- SubqueryAlias tab
+ +- LocalRelation [a#x, ts#x]
+
+
+-- !query
+SELECT
+ window.start,
+ window.end,
+ count(*) AS cnt
+FROM (
+ SELECT TIMESTAMP_NTZ '2021-01-01 00:00:00' AS ts
+ UNION ALL SELECT TIMESTAMP_NTZ '2021-01-01 00:04:30'
+ UNION ALL SELECT TIMESTAMP_NTZ '2021-01-01 00:06:00'
+)
+GROUP BY window(ts, '10 minutes', '5 minutes')
+ORDER BY window.start
+-- !query analysis
+Sort [start#x ASC NULLS FIRST], true
++- Aggregate [window#x], [window#x.start AS start#x, window#x.end AS end#x,
count(1) AS cnt#xL]
+ +- Filter isnotnull(ts#x)
+ +- Expand [[named_struct(start,
knownnullable(precisetimestampconversion(((precisetimestampconversion(ts#x,
TimestampNTZType, LongType) - CASE WHEN (((precisetimestampconversion(ts#x,
TimestampNTZType, LongType) - 0) % 300000000) < cast(0 as bigint)) THEN
(((precisetimestampconversion(ts#x, TimestampNTZType, LongType) - 0) %
300000000) + 300000000) ELSE ((precisetimestampconversion(ts#x,
TimestampNTZType, LongType) - 0) % 300000000) END) - 0), LongType,
TimestampNTZType)), end, kno [...]
+ +- SubqueryAlias __auto_generated_subquery_name
+ +- Union false, false
+ :- Union false, false
+ : :- Project [2021-01-01 00:00:00 AS ts#x]
+ : : +- OneRowRelation
+ : +- Project [2021-01-01 00:04:30 AS TIMESTAMP_NTZ '2021-01-01
00:04:30'#x]
+ : +- OneRowRelation
+ +- Project [2021-01-01 00:06:00 AS TIMESTAMP_NTZ '2021-01-01
00:06:00'#x]
+ +- OneRowRelation
+
+
+-- !query
+SELECT
+ window.start,
+ window.end,
+ count(*) AS cnt
+FROM ts_data
+GROUP BY window(ts, '10 minutes', '5 minutes', '2 minutes')
+ORDER BY window.start
+-- !query analysis
+Sort [start#x ASC NULLS FIRST], true
++- Aggregate [window#x], [window#x.start AS start#x, window#x.end AS end#x,
count(1) AS cnt#xL]
+ +- Filter isnotnull(ts#x)
+ +- Expand [[named_struct(start,
knownnullable(precisetimestampconversion(((precisetimestampconversion(ts#x,
TimestampType, LongType) - CASE WHEN (((precisetimestampconversion(ts#x,
TimestampType, LongType) - 120000000) % 300000000) < cast(0 as bigint)) THEN
(((precisetimestampconversion(ts#x, TimestampType, LongType) - 120000000) %
300000000) + 300000000) ELSE ((precisetimestampconversion(ts#x, TimestampType,
LongType) - 120000000) % 300000000) END) - 0), LongType, TimestampType)), [...]
+ +- SubqueryAlias ts_data
+ +- View (`ts_data`, [a#x, ts#x])
+ +- Project [cast(a#x as string) AS a#x, cast(ts#x as timestamp)
AS ts#x]
+ +- Project [a#x, ts#x]
+ +- SubqueryAlias tab
+ +- LocalRelation [a#x, ts#x]
+
+
+-- !query
+SELECT
+ window.start,
+ window.end,
+ sum(value) AS total
+FROM VALUES
+ (CAST('2016-03-27 09:00:05' AS TIMESTAMP), 1),
+ (CAST('2016-03-27 09:00:32' AS TIMESTAMP), 2),
+ (CAST(NULL AS TIMESTAMP), 3),
+ (CAST(NULL AS TIMESTAMP), 4)
+AS tab(time, value)
+GROUP BY window(time, '7 seconds', '3 seconds')
+ORDER BY window.start
+-- !query analysis
+Sort [start#x ASC NULLS FIRST], true
++- Aggregate [window#x], [window#x.start AS start#x, window#x.end AS end#x,
sum(value#x) AS total#xL]
+ +- Filter ((time#x >= window#x.start) AND (time#x < window#x.end))
+ +- Expand [[named_struct(start,
knownnullable(precisetimestampconversion(((precisetimestampconversion(time#x,
TimestampType, LongType) - CASE WHEN (((precisetimestampconversion(time#x,
TimestampType, LongType) - 0) % 3000000) < cast(0 as bigint)) THEN
(((precisetimestampconversion(time#x, TimestampType, LongType) - 0) % 3000000)
+ 3000000) ELSE ((precisetimestampconversion(time#x, TimestampType, LongType) -
0) % 3000000) END) - 0), LongType, TimestampType)), end, knownnullable(prec
[...]
+ +- SubqueryAlias tab
+ +- LocalRelation [time#x, value#x]
+
+
+-- !query
+SELECT
+ window.start,
+ window.end,
+ count(*) AS cnt
+FROM ts_data
+GROUP BY window(ts, '5 minutes')
+ORDER BY window.start DESC
+-- !query analysis
+Sort [start#x DESC NULLS LAST], true
++- Aggregate [window#x], [window#x.start AS start#x, window#x.end AS end#x,
count(1) AS cnt#xL]
+ +- Project [named_struct(start,
knownnullable(precisetimestampconversion(((precisetimestampconversion(ts#x,
TimestampType, LongType) - CASE WHEN (((precisetimestampconversion(ts#x,
TimestampType, LongType) - 0) % 300000000) < cast(0 as bigint)) THEN
(((precisetimestampconversion(ts#x, TimestampType, LongType) - 0) % 300000000)
+ 300000000) ELSE ((precisetimestampconversion(ts#x, TimestampType, LongType) -
0) % 300000000) END) - 0), LongType, TimestampType)), end,
knownnullable(precise [...]
+ +- Filter isnotnull(ts#x)
+ +- SubqueryAlias ts_data
+ +- View (`ts_data`, [a#x, ts#x])
+ +- Project [cast(a#x as string) AS a#x, cast(ts#x as timestamp)
AS ts#x]
+ +- Project [a#x, ts#x]
+ +- SubqueryAlias tab
+ +- LocalRelation [a#x, ts#x]
+
+
+-- !query
+SELECT
+ window.start,
+ sum(v),
+ count(*) AS cnt
+FROM ts_vals
+GROUP BY window(ts, '5 minutes')
+ORDER BY window.start
+-- !query analysis
+Sort [start#x ASC NULLS FIRST], true
++- Aggregate [window#x], [window#x.start AS start#x, sum(v#x) AS sum(v)#xL,
count(1) AS cnt#xL]
+ +- Project [named_struct(start,
knownnullable(precisetimestampconversion(((precisetimestampconversion(ts#x,
TimestampType, LongType) - CASE WHEN (((precisetimestampconversion(ts#x,
TimestampType, LongType) - 0) % 300000000) < cast(0 as bigint)) THEN
(((precisetimestampconversion(ts#x, TimestampType, LongType) - 0) % 300000000)
+ 300000000) ELSE ((precisetimestampconversion(ts#x, TimestampType, LongType) -
0) % 300000000) END) - 0), LongType, TimestampType)), end,
knownnullable(precise [...]
+ +- Filter isnotnull(ts#x)
+ +- SubqueryAlias ts_vals
+ +- View (`ts_vals`, [ts#x, v#x])
+ +- Project [cast(ts#x as timestamp) AS ts#x, cast(v#x as int)
AS v#x]
+ +- Project [ts#x, v#x]
+ +- SubqueryAlias tab
+ +- LocalRelation [ts#x, v#x]
+
+
+-- !query
+SELECT window(ts, '5 minutes').start AS bucket_start
+FROM ts_data
+ORDER BY bucket_start
+-- !query analysis
+Sort [bucket_start#x ASC NULLS FIRST], true
++- Project [window#x.start AS bucket_start#x]
+ +- Project [named_struct(start,
knownnullable(precisetimestampconversion(((precisetimestampconversion(ts#x,
TimestampType, LongType) - CASE WHEN (((precisetimestampconversion(ts#x,
TimestampType, LongType) - 0) % 300000000) < cast(0 as bigint)) THEN
(((precisetimestampconversion(ts#x, TimestampType, LongType) - 0) % 300000000)
+ 300000000) ELSE ((precisetimestampconversion(ts#x, TimestampType, LongType) -
0) % 300000000) END) - 0), LongType, TimestampType)), end,
knownnullable(precise [...]
+ +- Filter isnotnull(ts#x)
+ +- SubqueryAlias ts_data
+ +- View (`ts_data`, [a#x, ts#x])
+ +- Project [cast(a#x as string) AS a#x, cast(ts#x as timestamp)
AS ts#x]
+ +- Project [a#x, ts#x]
+ +- SubqueryAlias tab
+ +- LocalRelation [a#x, ts#x]
+
+
+-- !query
+SELECT window(ts, '7 minutes', '3 minutes').start AS bucket_start
+FROM ts_data
+ORDER BY bucket_start
+-- !query analysis
+Sort [bucket_start#x ASC NULLS FIRST], true
++- Project [window#x.start AS bucket_start#x]
+ +- Filter ((ts#x >= window#x.start) AND (ts#x < window#x.end))
+ +- Expand [[named_struct(start,
knownnullable(precisetimestampconversion(((precisetimestampconversion(ts#x,
TimestampType, LongType) - CASE WHEN (((precisetimestampconversion(ts#x,
TimestampType, LongType) - 0) % 180000000) < cast(0 as bigint)) THEN
(((precisetimestampconversion(ts#x, TimestampType, LongType) - 0) % 180000000)
+ 180000000) ELSE ((precisetimestampconversion(ts#x, TimestampType, LongType) -
0) % 180000000) END) - 0), LongType, TimestampType)), end, knownnullable(prec
[...]
+ +- SubqueryAlias ts_data
+ +- View (`ts_data`, [a#x, ts#x])
+ +- Project [cast(a#x as string) AS a#x, cast(ts#x as timestamp)
AS ts#x]
+ +- Project [a#x, ts#x]
+ +- SubqueryAlias tab
+ +- LocalRelation [a#x, ts#x]
+
+
+-- !query
+SELECT count(*)
+FROM ts_data
+GROUP BY window(ts, '10 minutes', '20 minutes')
+-- !query analysis
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "DATATYPE_MISMATCH.PARAMETER_CONSTRAINT_VIOLATION",
+ "sqlState" : "42K09",
+ "messageParameters" : {
+ "constraint" : "<=",
+ "leftExprName" : "`slide_duration`",
+ "leftExprValue" : "1200000000L",
+ "rightExprName" : "`window_duration`",
+ "rightExprValue" : "600000000L",
+ "sqlExpr" : "\"window(ts, 600000000, 1200000000, 0)\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 39,
+ "stopIndex" : 76,
+ "fragment" : "window(ts, '10 minutes', '20 minutes')"
+ } ]
+}
+
+
+-- !query
+SELECT count(*)
+FROM ts_data
+GROUP BY window(ts, '10 minutes', '5 minutes', '6 minutes')
+-- !query analysis
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "DATATYPE_MISMATCH.PARAMETER_CONSTRAINT_VIOLATION",
+ "sqlState" : "42K09",
+ "messageParameters" : {
+ "constraint" : "<",
+ "leftExprName" : "`abs(start_time)`",
+ "leftExprValue" : "360000000L",
+ "rightExprName" : "`slide_duration`",
+ "rightExprValue" : "300000000L",
+ "sqlExpr" : "\"window(ts, 600000000, 300000000, 360000000)\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 39,
+ "stopIndex" : 88,
+ "fragment" : "window(ts, '10 minutes', '5 minutes', '6 minutes')"
+ } ]
+}
+
+
+-- !query
+SELECT count(*)
+FROM ts_data
+GROUP BY window(ts, '5 minutes'), window(ts, '10 minutes')
+-- !query analysis
+org.apache.spark.sql.AnalysisException
+{
+ "errorClass" : "_LEGACY_ERROR_TEMP_1039",
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 30,
+ "stopIndex" : 87,
+ "fragment" : "GROUP BY window(ts, '5 minutes'), window(ts, '10 minutes')"
+ } ]
+}
+
+
+-- !query
+SELECT count(*)
+FROM ts_data
+GROUP BY array(window(ts, '5 minutes'), window(ts, '10 minutes'))
+-- !query analysis
+org.apache.spark.sql.AnalysisException
+{
+ "errorClass" : "_LEGACY_ERROR_TEMP_1039",
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 30,
+ "stopIndex" : 94,
+ "fragment" : "GROUP BY array(window(ts, '5 minutes'), window(ts, '10
minutes'))"
+ } ]
+}
+
+
+-- !query
+SELECT count(*)
+FROM VALUES (1), (2) AS tab(x)
+GROUP BY window(x, '5 minutes')
+-- !query analysis
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE",
+ "sqlState" : "42K09",
+ "messageParameters" : {
+ "inputSql" : "\"x\"",
+ "inputType" : "\"INT\"",
+ "paramIndex" : "first",
+ "requiredType" : "(\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\" or
\"STRUCT<start: TIMESTAMP, end: TIMESTAMP>\" or \"STRUCT<start: TIMESTAMP_NTZ,
end: TIMESTAMP_NTZ>\")",
+ "sqlExpr" : "\"window(x, 300000000, 300000000, 0)\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 57,
+ "stopIndex" : 78,
+ "fragment" : "window(x, '5 minutes')"
+ } ]
+}
+
+
+-- !query
+SELECT count(*)
+FROM ts_data
+GROUP BY window(ts, '-5 minutes')
+-- !query analysis
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "DATATYPE_MISMATCH.VALUE_OUT_OF_RANGE",
+ "sqlState" : "42K09",
+ "messageParameters" : {
+ "currentValue" : "-300000000L",
+ "exprName" : "`window_duration`",
+ "sqlExpr" : "\"window(ts, -300000000, -300000000, 0)\"",
+ "valueRange" : "(0, 9223372036854775807]"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 39,
+ "stopIndex" : 62,
+ "fragment" : "window(ts, '-5 minutes')"
+ } ]
+}
+
+
+-- !query
+SELECT
+ window.start,
+ window.end,
+ count(*) AS cnt
+FROM VALUES
+ (CAST('2021-01-01 00:04:59.999999' AS TIMESTAMP)),
+ (CAST('2021-01-01 00:05:00.000001' AS TIMESTAMP)),
+ (CAST('2021-01-01 00:02:30.123456' AS TIMESTAMP))
+AS tab(ts)
+GROUP BY window(ts, '5 minutes')
+ORDER BY start
+-- !query analysis
+Sort [start#x ASC NULLS FIRST], true
++- Aggregate [window#x], [window#x.start AS start#x, window#x.end AS end#x,
count(1) AS cnt#xL]
+ +- Project [named_struct(start,
knownnullable(precisetimestampconversion(((precisetimestampconversion(ts#x,
TimestampType, LongType) - CASE WHEN (((precisetimestampconversion(ts#x,
TimestampType, LongType) - 0) % 300000000) < cast(0 as bigint)) THEN
(((precisetimestampconversion(ts#x, TimestampType, LongType) - 0) % 300000000)
+ 300000000) ELSE ((precisetimestampconversion(ts#x, TimestampType, LongType) -
0) % 300000000) END) - 0), LongType, TimestampType)), end,
knownnullable(precise [...]
+ +- Filter isnotnull(ts#x)
+ +- SubqueryAlias tab
+ +- LocalRelation [ts#x]
+
+
+-- !query
+SELECT
+ window.start,
+ window.end,
+ count(*) AS cnt
+FROM VALUES
+ (CAST('2021-01-01 00:00:00' AS TIMESTAMP_NTZ)),
+ (CAST('2021-01-01 00:06:00' AS TIMESTAMP_NTZ))
+AS tab(ts)
+GROUP BY window(ts, '5 minutes')
+ORDER BY start
+-- !query analysis
+Sort [start#x ASC NULLS FIRST], true
++- Aggregate [window#x], [window#x.start AS start#x, window#x.end AS end#x,
count(1) AS cnt#xL]
+ +- Project [named_struct(start,
knownnullable(precisetimestampconversion(((precisetimestampconversion(ts#x,
TimestampNTZType, LongType) - CASE WHEN (((precisetimestampconversion(ts#x,
TimestampNTZType, LongType) - 0) % 300000000) < cast(0 as bigint)) THEN
(((precisetimestampconversion(ts#x, TimestampNTZType, LongType) - 0) %
300000000) + 300000000) ELSE ((precisetimestampconversion(ts#x,
TimestampNTZType, LongType) - 0) % 300000000) END) - 0), LongType,
TimestampNTZType)), end, knownn [...]
+ +- Filter isnotnull(ts#x)
+ +- SubqueryAlias tab
+ +- LocalRelation [ts#x]
+
+
+-- !query
+SELECT *
+FROM ts_data
+GROUP BY a, window(ts, '5 minutes')
+-- !query analysis
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "MISSING_AGGREGATION",
+ "sqlState" : "42803",
+ "messageParameters" : {
+ "expression" : "\"ts\"",
+ "expressionAnyValue" : "\"any_value(ts)\""
+ }
+}
+
+
+-- !query
+SELECT * FROM (
+ SELECT
+ a,
+ window.start,
+ window.end,
+ count(*) AS cnt
+ FROM ts_data
+ GROUP BY a, window(ts, '5 minutes')
+) sub
+ORDER BY a, start
+-- !query analysis
+Sort [a#x ASC NULLS FIRST, start#x ASC NULLS FIRST], true
++- Project [a#x, start#x, end#x, cnt#xL]
+ +- SubqueryAlias sub
+ +- Aggregate [a#x, window#x], [a#x, window#x.start AS start#x,
window#x.end AS end#x, count(1) AS cnt#xL]
+ +- Project [named_struct(start,
knownnullable(precisetimestampconversion(((precisetimestampconversion(ts#x,
TimestampType, LongType) - CASE WHEN (((precisetimestampconversion(ts#x,
TimestampType, LongType) - 0) % 300000000) < cast(0 as bigint)) THEN
(((precisetimestampconversion(ts#x, TimestampType, LongType) - 0) % 300000000)
+ 300000000) ELSE ((precisetimestampconversion(ts#x, TimestampType, LongType) -
0) % 300000000) END) - 0), LongType, TimestampType)), end, knownnullable(p [...]
+ +- Filter isnotnull(ts#x)
+ +- SubqueryAlias ts_data
+ +- View (`ts_data`, [a#x, ts#x])
+ +- Project [cast(a#x as string) AS a#x, cast(ts#x as
timestamp) AS ts#x]
+ +- Project [a#x, ts#x]
+ +- SubqueryAlias tab
+ +- LocalRelation [a#x, ts#x]
+
+
+-- !query
+SELECT count(*)
+FROM ts_data
+GROUP BY window(ts)
+-- !query analysis
+org.apache.spark.sql.AnalysisException
+{
+ "errorClass" : "WRONG_NUM_ARGS.WITHOUT_SUGGESTION",
+ "sqlState" : "42605",
+ "messageParameters" : {
+ "actualNum" : "1",
+ "docroot" : "https://spark.apache.org/docs/latest";,
+ "expectedNum" : "[2, 3, 4]",
+ "functionName" : "`window`"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 39,
+ "stopIndex" : 48,
+ "fragment" : "window(ts)"
+ } ]
+}
+
+
+-- !query
+SELECT count(*)
+FROM ts_data
+GROUP BY window(ts, 'not_an_interval')
+-- !query analysis
+org.apache.spark.sql.AnalysisException
+{
+ "errorClass" : "CANNOT_PARSE_INTERVAL",
+ "sqlState" : "22006",
+ "messageParameters" : {
+ "intervalString" : "'not_an_interval'"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 39,
+ "stopIndex" : 67,
+ "fragment" : "window(ts, 'not_an_interval')"
+ } ]
+}
+
+
+-- !query
+SELECT count(*)
+FROM ts_data
+GROUP BY window(ts, '2 months')
+-- !query analysis
+org.apache.spark.SparkIllegalArgumentException
+{
+ "errorClass" : "_LEGACY_ERROR_TEMP_3231",
+ "messageParameters" : {
+ "interval" : "2 months"
+ }
+}
+
+
+-- !query
+SELECT count(*)
+FROM ts_data
+GROUP BY window(ts, a)
+-- !query analysis
+org.apache.spark.sql.AnalysisException
+{
+ "errorClass" : "_LEGACY_ERROR_TEMP_1207",
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 39,
+ "stopIndex" : 51,
+ "fragment" : "window(ts, a)"
+ } ]
+}
+
+
+-- !query
+SELECT count(*)
+FROM ts_data
+GROUP BY window(ts, '10 minutes', '-5 minutes')
+-- !query analysis
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "DATATYPE_MISMATCH.VALUE_OUT_OF_RANGE",
+ "sqlState" : "42K09",
+ "messageParameters" : {
+ "currentValue" : "-300000000L",
+ "exprName" : "`slide_duration`",
+ "sqlExpr" : "\"window(ts, 600000000, -300000000, 0)\"",
+ "valueRange" : "(0, 9223372036854775807]"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 39,
+ "stopIndex" : 76,
+ "fragment" : "window(ts, '10 minutes', '-5 minutes')"
+ } ]
+}
+
+
+-- !query
+SELECT count(*)
+FROM ts_data
+GROUP BY window(ts, '0 minutes')
+-- !query analysis
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "DATATYPE_MISMATCH.VALUE_OUT_OF_RANGE",
+ "sqlState" : "42K09",
+ "messageParameters" : {
+ "currentValue" : "0L",
+ "exprName" : "`window_duration`",
+ "sqlExpr" : "\"window(ts, 0, 0, 0)\"",
+ "valueRange" : "(0, 9223372036854775807]"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 39,
+ "stopIndex" : 61,
+ "fragment" : "window(ts, '0 minutes')"
+ } ]
+}
+
+
+-- !query
+SELECT
+ window.start,
+ count(*) AS cnt
+FROM ts_data t1
+WHERE EXISTS (
+ SELECT 1 FROM ts_data t2 WHERE t2.a = t1.a AND t2.a = 'A1'
+)
+GROUP BY window(t1.ts, '5 minutes')
+ORDER BY start
+-- !query analysis
+Sort [start#x ASC NULLS FIRST], true
++- Aggregate [window#x], [window#x.start AS start#x, count(1) AS cnt#xL]
+ +- Project [named_struct(start,
knownnullable(precisetimestampconversion(((precisetimestampconversion(ts#x,
TimestampType, LongType) - CASE WHEN (((precisetimestampconversion(ts#x,
TimestampType, LongType) - 0) % 300000000) < cast(0 as bigint)) THEN
(((precisetimestampconversion(ts#x, TimestampType, LongType) - 0) % 300000000)
+ 300000000) ELSE ((precisetimestampconversion(ts#x, TimestampType, LongType) -
0) % 300000000) END) - 0), LongType, TimestampType)), end,
knownnullable(precise [...]
+ +- Filter isnotnull(ts#x)
+ +- Filter exists#x [a#x]
+ : +- Project [1 AS 1#x]
+ : +- Filter ((a#x = outer(a#x)) AND (a#x = A1))
+ : +- SubqueryAlias t2
+ : +- SubqueryAlias ts_data
+ : +- View (`ts_data`, [a#x, ts#x])
+ : +- Project [cast(a#x as string) AS a#x,
cast(ts#x as timestamp) AS ts#x]
+ : +- Project [a#x, ts#x]
+ : +- SubqueryAlias tab
+ : +- LocalRelation [a#x, ts#x]
+ +- SubqueryAlias t1
+ +- SubqueryAlias ts_data
+ +- View (`ts_data`, [a#x, ts#x])
+ +- Project [cast(a#x as string) AS a#x, cast(ts#x as
timestamp) AS ts#x]
+ +- Project [a#x, ts#x]
+ +- SubqueryAlias tab
+ +- LocalRelation [a#x, ts#x]
+
+
+-- !query
+SELECT
+ window.start,
+ window.end,
+ count(*) AS counts
+FROM VALUES
+ (CAST('2016-03-27 19:39:30' AS TIMESTAMP), 1, 'a'),
+ (CAST('2016-03-27 19:39:25' AS TIMESTAMP), 2, 'a')
+AS tab(time, value, id)
+GROUP BY window(time, '10 seconds', '10 seconds', '-5 seconds')
+ORDER BY window.start
+-- !query analysis
+Sort [start#x ASC NULLS FIRST], true
++- Aggregate [window#x], [window#x.start AS start#x, window#x.end AS end#x,
count(1) AS counts#xL]
+ +- Project [named_struct(start,
knownnullable(precisetimestampconversion(((precisetimestampconversion(time#x,
TimestampType, LongType) - CASE WHEN (((precisetimestampconversion(time#x,
TimestampType, LongType) - -5000000) % 10000000) < cast(0 as bigint)) THEN
(((precisetimestampconversion(time#x, TimestampType, LongType) - -5000000) %
10000000) + 10000000) ELSE ((precisetimestampconversion(time#x, TimestampType,
LongType) - -5000000) % 10000000) END) - 0), LongType, TimestampType)), e [...]
+ +- Filter isnotnull(time#x)
+ +- SubqueryAlias tab
+ +- LocalRelation [time#x, value#x, id#x]
+
+
+-- !query
+SELECT
+ window.start,
+ window.end,
+ count(*) AS counts
+FROM VALUES
+ (CAST('2016-03-27 19:39:34' AS TIMESTAMP), 1, 'a'),
+ (CAST('2016-03-27 19:39:56' AS TIMESTAMP), 2, 'a'),
+ (CAST('2016-03-27 19:39:27' AS TIMESTAMP), 4, 'b')
+AS tab(time, value, id)
+GROUP BY window(time, '10 seconds')
+ORDER BY window.start
+-- !query analysis
+Sort [start#x ASC NULLS FIRST], true
++- Aggregate [window#x], [window#x.start AS start#x, window#x.end AS end#x,
count(1) AS counts#xL]
+ +- Project [named_struct(start,
knownnullable(precisetimestampconversion(((precisetimestampconversion(time#x,
TimestampType, LongType) - CASE WHEN (((precisetimestampconversion(time#x,
TimestampType, LongType) - 0) % 10000000) < cast(0 as bigint)) THEN
(((precisetimestampconversion(time#x, TimestampType, LongType) - 0) % 10000000)
+ 10000000) ELSE ((precisetimestampconversion(time#x, TimestampType, LongType)
- 0) % 10000000) END) - 0), LongType, TimestampType)), end, knownnullable(pre
[...]
+ +- Filter isnotnull(time#x)
+ +- SubqueryAlias tab
+ +- LocalRelation [time#x, value#x, id#x]
+
+
+-- !query
+SELECT
+ id,
+ window.start,
+ window.end,
+ count(*) AS counts
+FROM VALUES
+ (CAST('2016-03-27 19:39:34' AS TIMESTAMP), 1, 'a'),
+ (CAST('2016-03-27 19:39:56' AS TIMESTAMP), 2, 'a'),
+ (CAST('2016-03-27 19:39:27' AS TIMESTAMP), 4, 'b')
+AS tab(time, value, id)
+GROUP BY window(time, '10 seconds', '10 seconds', '5 seconds'), id
+ORDER BY id, window.start
+-- !query analysis
+Sort [id#x ASC NULLS FIRST, start#x ASC NULLS FIRST], true
++- Aggregate [window#x, id#x], [id#x, window#x.start AS start#x, window#x.end
AS end#x, count(1) AS counts#xL]
+ +- Project [named_struct(start,
knownnullable(precisetimestampconversion(((precisetimestampconversion(time#x,
TimestampType, LongType) - CASE WHEN (((precisetimestampconversion(time#x,
TimestampType, LongType) - 5000000) % 10000000) < cast(0 as bigint)) THEN
(((precisetimestampconversion(time#x, TimestampType, LongType) - 5000000) %
10000000) + 10000000) ELSE ((precisetimestampconversion(time#x, TimestampType,
LongType) - 5000000) % 10000000) END) - 0), LongType, TimestampType)), end,
[...]
+ +- Filter isnotnull(time#x)
+ +- SubqueryAlias tab
+ +- LocalRelation [time#x, value#x, id#x]
+
+
+-- !query
+SELECT
+ window.start,
+ window.end,
+ sum(value) AS total
+FROM VALUES
+ (CAST('2016-03-27 09:00:05' AS TIMESTAMP), 1),
+ (CAST('2016-03-27 09:00:32' AS TIMESTAMP), 2),
+ (CAST(NULL AS TIMESTAMP), 3),
+ (CAST(NULL AS TIMESTAMP), 4)
+AS tab(time, value)
+GROUP BY window(time, '10 seconds')
+ORDER BY window.start
+-- !query analysis
+Sort [start#x ASC NULLS FIRST], true
++- Aggregate [window#x], [window#x.start AS start#x, window#x.end AS end#x,
sum(value#x) AS total#xL]
+ +- Project [named_struct(start,
knownnullable(precisetimestampconversion(((precisetimestampconversion(time#x,
TimestampType, LongType) - CASE WHEN (((precisetimestampconversion(time#x,
TimestampType, LongType) - 0) % 10000000) < cast(0 as bigint)) THEN
(((precisetimestampconversion(time#x, TimestampType, LongType) - 0) % 10000000)
+ 10000000) ELSE ((precisetimestampconversion(time#x, TimestampType, LongType)
- 0) % 10000000) END) - 0), LongType, TimestampType)), end, knownnullable(pre
[...]
+ +- Filter isnotnull(time#x)
+ +- SubqueryAlias tab
+ +- LocalRelation [time#x, value#x]
+
+
+-- !query
+SELECT window(ts, '5 minutes').end AS bucket_end, count(*) AS cnt
+FROM ts_data
+GROUP BY ALL
+ORDER BY bucket_end
+-- !query analysis
+Sort [bucket_end#x ASC NULLS FIRST], true
++- Aggregate [window#x.end], [window#x.end AS bucket_end#x, count(1) AS cnt#xL]
+ +- Project [named_struct(start,
knownnullable(precisetimestampconversion(((precisetimestampconversion(ts#x,
TimestampType, LongType) - CASE WHEN (((precisetimestampconversion(ts#x,
TimestampType, LongType) - 0) % 300000000) < cast(0 as bigint)) THEN
(((precisetimestampconversion(ts#x, TimestampType, LongType) - 0) % 300000000)
+ 300000000) ELSE ((precisetimestampconversion(ts#x, TimestampType, LongType) -
0) % 300000000) END) - 0), LongType, TimestampType)), end,
knownnullable(precise [...]
+ +- Filter isnotnull(ts#x)
+ +- SubqueryAlias ts_data
+ +- View (`ts_data`, [a#x, ts#x])
+ +- Project [cast(a#x as string) AS a#x, cast(ts#x as timestamp)
AS ts#x]
+ +- Project [a#x, ts#x]
+ +- SubqueryAlias tab
+ +- LocalRelation [a#x, ts#x]
+
+
+-- !query
+SELECT
+ window(ts, '5 minutes').start AS bucket_start,
+ window(ts, '5 minutes').end AS bucket_end,
+ count(*) AS cnt
+FROM ts_data
+GROUP BY window(ts, '5 minutes')
+ORDER BY bucket_start
+-- !query analysis
+Sort [bucket_start#x ASC NULLS FIRST], true
++- Aggregate [window#x], [window#x.start AS bucket_start#x, window#x.end AS
bucket_end#x, count(1) AS cnt#xL]
+ +- Project [named_struct(start,
knownnullable(precisetimestampconversion(((precisetimestampconversion(ts#x,
TimestampType, LongType) - CASE WHEN (((precisetimestampconversion(ts#x,
TimestampType, LongType) - 0) % 300000000) < cast(0 as bigint)) THEN
(((precisetimestampconversion(ts#x, TimestampType, LongType) - 0) % 300000000)
+ 300000000) ELSE ((precisetimestampconversion(ts#x, TimestampType, LongType) -
0) % 300000000) END) - 0), LongType, TimestampType)), end,
knownnullable(precise [...]
+ +- Filter isnotnull(ts#x)
+ +- SubqueryAlias ts_data
+ +- View (`ts_data`, [a#x, ts#x])
+ +- Project [cast(a#x as string) AS a#x, cast(ts#x as timestamp)
AS ts#x]
+ +- Project [a#x, ts#x]
+ +- SubqueryAlias tab
+ +- LocalRelation [a#x, ts#x]
+
+
+-- !query
+SELECT
+ window(ts, '5 minutes').start AS bucket_start,
+ window(ts, '5 minutes').end AS bucket_end,
+ count(*) AS cnt
+FROM ts_data
+GROUP BY ALL
+ORDER BY bucket_start
+-- !query analysis
+Sort [bucket_start#x ASC NULLS FIRST], true
++- Aggregate [window#x.start, window#x.end], [window#x.start AS
bucket_start#x, window#x.end AS bucket_end#x, count(1) AS cnt#xL]
+ +- Project [named_struct(start,
knownnullable(precisetimestampconversion(((precisetimestampconversion(ts#x,
TimestampType, LongType) - CASE WHEN (((precisetimestampconversion(ts#x,
TimestampType, LongType) - 0) % 300000000) < cast(0 as bigint)) THEN
(((precisetimestampconversion(ts#x, TimestampType, LongType) - 0) % 300000000)
+ 300000000) ELSE ((precisetimestampconversion(ts#x, TimestampType, LongType) -
0) % 300000000) END) - 0), LongType, TimestampType)), end,
knownnullable(precise [...]
+ +- Filter isnotnull(ts#x)
+ +- SubqueryAlias ts_data
+ +- View (`ts_data`, [a#x, ts#x])
+ +- Project [cast(a#x as string) AS a#x, cast(ts#x as timestamp)
AS ts#x]
+ +- Project [a#x, ts#x]
+ +- SubqueryAlias tab
+ +- LocalRelation [a#x, ts#x]
+
+
+-- !query
+SELECT
+ window(ts, '5 minutes').start AS bucket_5,
+ window(ts, '10 minutes').end AS bucket_10,
+ count(*) AS cnt
+FROM ts_data
+GROUP BY window(ts, '5 minutes')
+-- !query analysis
+org.apache.spark.sql.AnalysisException
+{
+ "errorClass" : "_LEGACY_ERROR_TEMP_1039",
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 129,
+ "stopIndex" : 160,
+ "fragment" : "GROUP BY window(ts, '5 minutes')"
+ } ]
+}
+
+
+-- !query
+SELECT window_time(window) AS event_time, count(*) AS cnt
+FROM ts_data
+GROUP BY window(ts, '5 minutes')
+ORDER BY event_time
+-- !query analysis
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "MISSING_AGGREGATION",
+ "sqlState" : "42803",
+ "messageParameters" : {
+ "expression" : "\"window_time(window)\"",
+ "expressionAnyValue" : "\"any_value(window_time(window))\""
+ }
+}
+
+
+-- !query
+WITH ts_ntz_data AS (
+ SELECT TIMESTAMP_NTZ '2021-01-01 00:00:00' AS ts
+ UNION ALL SELECT TIMESTAMP_NTZ '2021-01-01 00:04:30'
+ UNION ALL SELECT TIMESTAMP_NTZ '2021-01-01 00:06:00'
+)
+SELECT window_time(window) AS event_time, count(*) AS cnt
+FROM ts_ntz_data
+GROUP BY window(ts, '5 minutes')
+ORDER BY event_time
+-- !query analysis
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "MISSING_AGGREGATION",
+ "sqlState" : "42803",
+ "messageParameters" : {
+ "expression" : "\"window_time(window)\"",
+ "expressionAnyValue" : "\"any_value(window_time(window))\""
+ }
+}
+
+
+-- !query
+SELECT window_time(ts) FROM ts_data
+-- !query analysis
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE",
+ "sqlState" : "42K09",
+ "messageParameters" : {
+ "inputSql" : "\"ts\"",
+ "inputType" : "\"TIMESTAMP\"",
+ "paramIndex" : "first",
+ "requiredType" : "\"STRUCT\"",
+ "sqlExpr" : "\"window_time(ts)\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 22,
+ "fragment" : "window_time(ts)"
+ } ]
+}
+
+
+-- !query
+SELECT outer_start, outer_end, total_cnt
+FROM (
+ SELECT
+ window.start AS outer_start,
+ window.end AS outer_end,
+ sum(v) AS total_cnt
+ FROM (
+ SELECT
+ named_struct('start', ts, 'end', ts + INTERVAL 1 MINUTE) AS inner_window,
+ v
+ FROM ts_vals
+ )
+ GROUP BY window(inner_window, '5 minutes')
+)
+ORDER BY outer_start
+-- !query analysis
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "_LEGACY_ERROR_TEMP_3101",
+ "messageParameters" : {
+ "windowTime" : "window_time(inner_window#x)"
+ }
+}
+
+
+-- !query
+SELECT outer_window.start AS outer_start, outer_window.end AS outer_end, cnt
+FROM (
+ SELECT window(inner_window, '5 minutes') AS outer_window, sum(cnt) AS cnt
+ FROM (
+ SELECT window(ts, '1 minute') AS inner_window, count(*) AS cnt
+ FROM ts_data
+ GROUP BY window(ts, '1 minute')
+ )
+ GROUP BY window(inner_window, '5 minutes')
+)
+ORDER BY outer_start
+-- !query analysis
+Sort [outer_start#x ASC NULLS FIRST], true
++- Project [outer_window#x.start AS outer_start#x, outer_window#x.end AS
outer_end#x, cnt#xL]
+ +- SubqueryAlias __auto_generated_subquery_name
+ +- Aggregate [window#x], [window#x AS outer_window#x, sum(cnt#xL) AS
cnt#xL]
+ +- Project [named_struct(start,
knownnullable(precisetimestampconversion(((precisetimestampconversion(window_time(inner_window)#x,
TimestampType, LongType) - CASE WHEN
(((precisetimestampconversion(window_time(inner_window)#x, TimestampType,
LongType) - 0) % 300000000) < cast(0 as bigint)) THEN
(((precisetimestampconversion(window_time(inner_window)#x, TimestampType,
LongType) - 0) % 300000000) + 300000000) ELSE
((precisetimestampconversion(window_time(inner_window)#x, Timestamp [...]
+ +- Filter isnotnull(window_time(inner_window)#x)
+ +- Project
[precisetimestampconversion((precisetimestampconversion(inner_window#x.end,
TimestampType, LongType) - 1), LongType, TimestampType) AS
window_time(inner_window)#x, inner_window#x, cnt#xL]
+ +- SubqueryAlias __auto_generated_subquery_name
+ +- Aggregate [window#x], [window#x AS inner_window#x,
count(1) AS cnt#xL]
+ +- Project [named_struct(start,
knownnullable(precisetimestampconversion(((precisetimestampconversion(ts#x,
TimestampType, LongType) - CASE WHEN (((precisetimestampconversion(ts#x,
TimestampType, LongType) - 0) % 60000000) < cast(0 as bigint)) THEN
(((precisetimestampconversion(ts#x, TimestampType, LongType) - 0) % 60000000) +
60000000) ELSE ((precisetimestampconversion(ts#x, TimestampType, LongType) - 0)
% 60000000) END) - 0), LongType, TimestampType)), end, know [...]
+ +- Filter isnotnull(ts#x)
+ +- SubqueryAlias ts_data
+ +- View (`ts_data`, [a#x, ts#x])
+ +- Project [cast(a#x as string) AS a#x,
cast(ts#x as timestamp) AS ts#x]
+ +- Project [a#x, ts#x]
+ +- SubqueryAlias tab
+ +- LocalRelation [a#x, ts#x]
+
+
+-- !query
+SELECT
+ window(window(ts, '1 minute'), '5 minutes').start AS outer_start,
+ count(*) AS cnt
+FROM ts_data
+GROUP BY window(window(ts, '1 minute'), '5 minutes')
+ORDER BY outer_start
+-- !query analysis
+Sort [outer_start#x ASC NULLS FIRST], true
++- Aggregate [window#x], [window#x.start AS outer_start#x, count(1) AS cnt#xL]
+ +- Project [named_struct(start,
knownnullable(precisetimestampconversion(((precisetimestampconversion(window_time(window)#x,
TimestampType, LongType) - CASE WHEN
(((precisetimestampconversion(window_time(window)#x, TimestampType, LongType) -
0) % 300000000) < cast(0 as bigint)) THEN
(((precisetimestampconversion(window_time(window)#x, TimestampType, LongType) -
0) % 300000000) + 300000000) ELSE
((precisetimestampconversion(window_time(window)#x, TimestampType, LongType) -
0) % 3000000 [...]
+ +- Filter isnotnull(window_time(window)#x)
+ +- Project
[precisetimestampconversion((precisetimestampconversion(window#x.end,
TimestampType, LongType) - 1), LongType, TimestampType) AS
window_time(window)#x, window#x, a#x, ts#x]
+ +- Project [named_struct(start,
knownnullable(precisetimestampconversion(((precisetimestampconversion(ts#x,
TimestampType, LongType) - CASE WHEN (((precisetimestampconversion(ts#x,
TimestampType, LongType) - 0) % 60000000) < cast(0 as bigint)) THEN
(((precisetimestampconversion(ts#x, TimestampType, LongType) - 0) % 60000000) +
60000000) ELSE ((precisetimestampconversion(ts#x, TimestampType, LongType) - 0)
% 60000000) END) - 0), LongType, TimestampType)), end, knownnullable(pr [...]
+ +- Filter isnotnull(ts#x)
+ +- SubqueryAlias ts_data
+ +- View (`ts_data`, [a#x, ts#x])
+ +- Project [cast(a#x as string) AS a#x, cast(ts#x as
timestamp) AS ts#x]
+ +- Project [a#x, ts#x]
+ +- SubqueryAlias tab
+ +- LocalRelation [a#x, ts#x]
+
+
+-- !query
+SELECT
+ window(window(ts, '1 minute'), '5 minutes').start AS outer_start,
+ window(window(ts, '1 minute'), '5 minutes').end AS outer_end,
+ count(*) AS cnt
+FROM ts_data
+GROUP BY ALL
+ORDER BY outer_start
+-- !query analysis
+Sort [outer_start#x ASC NULLS FIRST], true
++- Aggregate [window#x.start, window#x.end], [window#x.start AS outer_start#x,
window#x.end AS outer_end#x, count(1) AS cnt#xL]
+ +- Project [named_struct(start,
knownnullable(precisetimestampconversion(((precisetimestampconversion(window_time(window)#x,
TimestampType, LongType) - CASE WHEN
(((precisetimestampconversion(window_time(window)#x, TimestampType, LongType) -
0) % 300000000) < cast(0 as bigint)) THEN
(((precisetimestampconversion(window_time(window)#x, TimestampType, LongType) -
0) % 300000000) + 300000000) ELSE
((precisetimestampconversion(window_time(window)#x, TimestampType, LongType) -
0) % 3000000 [...]
+ +- Filter isnotnull(window_time(window)#x)
+ +- Project
[precisetimestampconversion((precisetimestampconversion(window#x.end,
TimestampType, LongType) - 1), LongType, TimestampType) AS
window_time(window)#x, window#x, a#x, ts#x]
+ +- Project [named_struct(start,
knownnullable(precisetimestampconversion(((precisetimestampconversion(ts#x,
TimestampType, LongType) - CASE WHEN (((precisetimestampconversion(ts#x,
TimestampType, LongType) - 0) % 60000000) < cast(0 as bigint)) THEN
(((precisetimestampconversion(ts#x, TimestampType, LongType) - 0) % 60000000) +
60000000) ELSE ((precisetimestampconversion(ts#x, TimestampType, LongType) - 0)
% 60000000) END) - 0), LongType, TimestampType)), end, knownnullable(pr [...]
+ +- Filter isnotnull(ts#x)
+ +- SubqueryAlias ts_data
+ +- View (`ts_data`, [a#x, ts#x])
+ +- Project [cast(a#x as string) AS a#x, cast(ts#x as
timestamp) AS ts#x]
+ +- Project [a#x, ts#x]
+ +- SubqueryAlias tab
+ +- LocalRelation [a#x, ts#x]
+
+
+-- !query
+SELECT window_time(window, window) FROM ts_data GROUP BY window(ts, '5
minutes')
+-- !query analysis
+org.apache.spark.sql.AnalysisException
+{
+ "errorClass" : "WRONG_NUM_ARGS.WITHOUT_SUGGESTION",
+ "sqlState" : "42605",
+ "messageParameters" : {
+ "actualNum" : "2",
+ "docroot" : "https://spark.apache.org/docs/latest";,
+ "expectedNum" : "1",
+ "functionName" : "`window_time`"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 34,
+ "fragment" : "window_time(window, window)"
+ } ]
+}
+
+
+-- !query
+SELECT window_time(named_struct('start', 1, 'end', 2)) FROM ts_data
+-- !query analysis
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "_LEGACY_ERROR_TEMP_3101",
+ "messageParameters" : {
+ "windowTime" : "window_time(named_struct(start, 1, end, 2))"
+ }
+}
+
+
+-- !query
+SELECT window_time(named_struct('foo', ts, 'bar', ts)) FROM ts_data
+-- !query analysis
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "_LEGACY_ERROR_TEMP_3101",
+ "messageParameters" : {
+ "windowTime" : "window_time(named_struct(foo, ts#x, bar, ts#x))"
+ }
+}
+
+
+-- !query
+SELECT window_time(named_struct('start', ts, 'end', ts + INTERVAL 1 MINUTE))
FROM ts_data
+-- !query analysis
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "_LEGACY_ERROR_TEMP_3101",
+ "messageParameters" : {
+ "windowTime" : "window_time(named_struct(start, ts#x, end, cast(ts#x +
INTERVAL '01' MINUTE as timestamp)))"
+ }
+}
+
+
+-- !query
+WITH x AS (SELECT named_struct('start', ts, 'end', ts + INTERVAL 1 MINUTE) AS
w FROM ts_data)
+SELECT window_time(w) FROM x
+ORDER BY 1
+-- !query analysis
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "_LEGACY_ERROR_TEMP_3101",
+ "messageParameters" : {
+ "windowTime" : "window_time(w#x)"
+ }
+}
+
+
+-- !query
+SELECT
+ window(ts, '5 minutes').start AS bucket_start,
+ window(ts, '5 minutes').end AS bucket_end
+FROM ts_data
+ORDER BY bucket_start
+-- !query analysis
+Sort [bucket_start#x ASC NULLS FIRST], true
++- Project [window#x.start AS bucket_start#x, window#x.end AS bucket_end#x]
+ +- Project [named_struct(start,
knownnullable(precisetimestampconversion(((precisetimestampconversion(ts#x,
TimestampType, LongType) - CASE WHEN (((precisetimestampconversion(ts#x,
TimestampType, LongType) - 0) % 300000000) < cast(0 as bigint)) THEN
(((precisetimestampconversion(ts#x, TimestampType, LongType) - 0) % 300000000)
+ 300000000) ELSE ((precisetimestampconversion(ts#x, TimestampType, LongType) -
0) % 300000000) END) - 0), LongType, TimestampType)), end,
knownnullable(precise [...]
+ +- Filter isnotnull(ts#x)
+ +- SubqueryAlias ts_data
+ +- View (`ts_data`, [a#x, ts#x])
+ +- Project [cast(a#x as string) AS a#x, cast(ts#x as timestamp)
AS ts#x]
+ +- Project [a#x, ts#x]
+ +- SubqueryAlias tab
+ +- LocalRelation [a#x, ts#x]
+
+
+-- !query
+SELECT
+ window(ts, '5 minutes').start AS bucket_5,
+ window(ts, '10 minutes').end AS bucket_10
+FROM ts_data
+-- !query analysis
+org.apache.spark.sql.AnalysisException
+{
+ "errorClass" : "_LEGACY_ERROR_TEMP_1039",
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 1,
+ "stopIndex" : 108,
+ "fragment" : "SELECT\n window(ts, '5 minutes').start AS bucket_5,\n
window(ts, '10 minutes').end AS bucket_10\nFROM ts_data"
+ } ]
+}
+
+
+-- !query
+SELECT count(*)
+FROM ts_data
+WHERE window(ts, '5 minutes').start IS NOT NULL
+-- !query analysis
+Aggregate [count(1) AS count(1)#xL]
++- Filter isnotnull(window#x.start)
+ +- Project [named_struct(start,
knownnullable(precisetimestampconversion(((precisetimestampconversion(ts#x,
TimestampType, LongType) - CASE WHEN (((precisetimestampconversion(ts#x,
TimestampType, LongType) - 0) % 300000000) < cast(0 as bigint)) THEN
(((precisetimestampconversion(ts#x, TimestampType, LongType) - 0) % 300000000)
+ 300000000) ELSE ((precisetimestampconversion(ts#x, TimestampType, LongType) -
0) % 300000000) END) - 0), LongType, TimestampType)), end,
knownnullable(precise [...]
+ +- Filter isnotnull(ts#x)
+ +- SubqueryAlias ts_data
+ +- View (`ts_data`, [a#x, ts#x])
+ +- Project [cast(a#x as string) AS a#x, cast(ts#x as timestamp)
AS ts#x]
+ +- Project [a#x, ts#x]
+ +- SubqueryAlias tab
+ +- LocalRelation [a#x, ts#x]
+
+
+-- !query
+SELECT count(*) AS cnt
+FROM ts_data
+GROUP BY ts
+HAVING window(ts, '5 minutes').start IS NOT NULL
+-- !query analysis
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "MISSING_ATTRIBUTES.RESOLVED_ATTRIBUTE_MISSING_FROM_INPUT",
+ "sqlState" : "XX000",
+ "messageParameters" : {
+ "input" : "\"cnt\"",
+ "missingAttributes" : "\"ts\"",
+ "operator" : "!Project [named_struct(start,
knownnullable(precisetimestampconversion(((precisetimestampconversion(ts#x,
TimestampType, LongType) - CASE WHEN (((precisetimestampconversion(ts#x,
TimestampType, LongType) - 0) % 300000000) < cast(0 as bigint)) THEN
(((precisetimestampconversion(ts#x, TimestampType, LongType) - 0) % 300000000)
+ 300000000) ELSE ((precisetimestampconversion(ts#x, TimestampType, LongType) -
0) % 300000000) END) - 0), LongType, TimestampType)), end, knownnul [...]
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 1,
+ "stopIndex" : 96,
+ "fragment" : "SELECT count(*) AS cnt\nFROM ts_data\nGROUP BY ts\nHAVING
window(ts, '5 minutes').start IS NOT NULL"
+ } ]
+}
+
+
+-- !query
+SELECT count(*) FROM ts_data GROUP BY ts ORDER BY window(ts, '5 minutes').start
+-- !query analysis
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "MISSING_ATTRIBUTES.RESOLVED_ATTRIBUTE_MISSING_FROM_INPUT",
+ "sqlState" : "XX000",
+ "messageParameters" : {
+ "input" : "\"count(1)\"",
+ "missingAttributes" : "\"ts\"",
+ "operator" : "!Project [named_struct(start,
knownnullable(precisetimestampconversion(((precisetimestampconversion(ts#x,
TimestampType, LongType) - CASE WHEN (((precisetimestampconversion(ts#x,
TimestampType, LongType) - 0) % 300000000) < cast(0 as bigint)) THEN
(((precisetimestampconversion(ts#x, TimestampType, LongType) - 0) % 300000000)
+ 300000000) ELSE ((precisetimestampconversion(ts#x, TimestampType, LongType) -
0) % 300000000) END) - 0), LongType, TimestampType)), end, knownnul [...]
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 42,
+ "stopIndex" : 79,
+ "fragment" : "ORDER BY window(ts, '5 minutes').start"
+ } ]
+}
+
+
+-- !query
+SELECT ts, row_number() OVER (ORDER BY ts) AS rn FROM ts_data QUALIFY
window(ts, '5 minutes').start IS NOT NULL
+-- !query analysis
+Filter isnotnull(window#x.start)
++- Project [named_struct(start,
knownnullable(precisetimestampconversion(((precisetimestampconversion(ts#x,
TimestampType, LongType) - CASE WHEN (((precisetimestampconversion(ts#x,
TimestampType, LongType) - 0) % 300000000) < cast(0 as bigint)) THEN
(((precisetimestampconversion(ts#x, TimestampType, LongType) - 0) % 300000000)
+ 300000000) ELSE ((precisetimestampconversion(ts#x, TimestampType, LongType) -
0) % 300000000) END) - 0), LongType, TimestampType)), end,
knownnullable(precisetim [...]
+ +- Filter isnotnull(ts#x)
+ +- Project [ts#x, rn#x]
+ +- Project [ts#x, rn#x, rn#x]
+ +- Window [row_number() windowspecdefinition(ts#x ASC NULLS FIRST,
specifiedwindowframe(RowFrame, unboundedpreceding$(), currentrow$())) AS rn#x],
[ts#x ASC NULLS FIRST]
+ +- Project [ts#x]
+ +- SubqueryAlias ts_data
+ +- View (`ts_data`, [a#x, ts#x])
+ +- Project [cast(a#x as string) AS a#x, cast(ts#x as
timestamp) AS ts#x]
+ +- Project [a#x, ts#x]
+ +- SubqueryAlias tab
+ +- LocalRelation [a#x, ts#x]
+
+
+-- !query
+SELECT * FROM ts_data
+PIVOT (window(ts, '5 minutes').start FOR a IN ('A1', 'A2'))
+-- !query analysis
+org.apache.spark.sql.AnalysisException
+{
+ "errorClass" : "_LEGACY_ERROR_TEMP_1006",
+ "messageParameters" : {
+ "sql" : "window"
+ }
+}
+
+
+-- !query
+SELECT
+ window(window(ts, '1 minute'), '5 minutes').start,
+ window(window(ts, '1 minute'), '10 minutes').end
+FROM ts_data
+-- !query analysis
+org.apache.spark.sql.AnalysisException
+{
+ "errorClass" : "_LEGACY_ERROR_TEMP_1039",
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 1,
+ "stopIndex" : 123,
+ "fragment" : "SELECT\n window(window(ts, '1 minute'), '5
minutes').start,\n window(window(ts, '1 minute'), '10 minutes').end\nFROM
ts_data"
+ } ]
+}
+
+
+-- !query
+SELECT window.start FROM ts_data GROUP BY window(window(ts, '1 minute'), '5
minutes')
+-- !query analysis
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "MISSING_AGGREGATION",
+ "sqlState" : "42803",
+ "messageParameters" : {
+ "expression" : "\"window\"",
+ "expressionAnyValue" : "\"any_value(window)\""
+ }
+}
+
+
+-- !query
+SELECT
+ a,
+ session_window.start,
+ session_window.end,
+ count(*) AS cnt
+FROM ts_data
+GROUP BY a, session_window(ts, '5 minutes')
+ORDER BY a, start
+-- !query analysis
+Sort [a#x ASC NULLS FIRST, start#x ASC NULLS FIRST], true
++- Aggregate [a#x, session_window#x], [a#x, session_window#x.start AS start#x,
session_window#x.end AS end#x, count(1) AS cnt#xL]
+ +- Filter isnotnull(ts#x)
+ +- Project [named_struct(start,
precisetimestampconversion(precisetimestampconversion(ts#x, TimestampType,
LongType), LongType, TimestampType), end,
knownnullable(precisetimestampconversion(precisetimestampconversion(cast(ts#x +
cast(5 minutes as interval) as timestamp), TimestampType, LongType), LongType,
TimestampType))) AS session_window#x, a#x, ts#x]
+ +- SubqueryAlias ts_data
+ +- View (`ts_data`, [a#x, ts#x])
+ +- Project [cast(a#x as string) AS a#x, cast(ts#x as timestamp)
AS ts#x]
+ +- Project [a#x, ts#x]
+ +- SubqueryAlias tab
+ +- LocalRelation [a#x, ts#x]
+
+
+-- !query
+SELECT
+ a,
+ session_window.start,
+ session_window.end,
+ count(*) AS cnt
+FROM VALUES
+ ('A1', CAST('2021-01-01 00:00:00' AS TIMESTAMP)),
+ ('A1', CAST('2021-01-01 00:04:30' AS TIMESTAMP)),
+ ('A1', CAST('2021-01-01 00:10:00' AS TIMESTAMP)),
+ ('A2', CAST('2021-01-01 00:01:00' AS TIMESTAMP)),
+ ('A2', CAST('2021-01-01 00:04:30' AS TIMESTAMP))
+AS tab(a, ts)
+GROUP BY a, session_window(ts, CASE WHEN a = 'A1' THEN '5 minutes'
+ WHEN a = 'A2' THEN '1 minute'
+ ELSE '10 minutes' END)
+ORDER BY a, start
+-- !query analysis
+Sort [a#x ASC NULLS FIRST, start#x ASC NULLS FIRST], true
++- Aggregate [a#x, session_window#x], [a#x, session_window#x.start AS start#x,
session_window#x.end AS end#x, count(1) AS cnt#xL]
+ +- Filter (isnotnull(ts#x) AND (session_window#x.end >
session_window#x.start))
+ +- Project [named_struct(start,
precisetimestampconversion(precisetimestampconversion(ts#x, TimestampType,
LongType), LongType, TimestampType), end,
knownnullable(precisetimestampconversion(precisetimestampconversion(cast(ts#x +
cast(CASE WHEN (a#x = A1) THEN 5 minutes WHEN (a#x = A2) THEN 1 minute ELSE 10
minutes END as interval) as timestamp), TimestampType, LongType), LongType,
TimestampType))) AS session_window#x, a#x, ts#x]
+ +- SubqueryAlias tab
+ +- LocalRelation [a#x, ts#x]
+
+
+-- !query
+SELECT
+ a,
+ session_window.start,
+ session_window.end,
+ count(*) AS cnt
+FROM VALUES
+ ('A1', CAST('2021-01-01 00:00:00' AS TIMESTAMP), '5 minutes'),
+ ('A1', CAST('2021-01-01 00:04:30' AS TIMESTAMP), '5 minutes'),
+ ('A2', CAST('2021-01-01 00:01:00' AS TIMESTAMP), '1 minute')
+AS tab(a, ts, gap)
+GROUP BY a, session_window(ts, gap)
+ORDER BY a, start
+-- !query analysis
+Sort [a#x ASC NULLS FIRST, start#x ASC NULLS FIRST], true
++- Aggregate [a#x, session_window#x], [a#x, session_window#x.start AS start#x,
session_window#x.end AS end#x, count(1) AS cnt#xL]
+ +- Filter (isnotnull(ts#x) AND (session_window#x.end >
session_window#x.start))
+ +- Project [named_struct(start,
precisetimestampconversion(precisetimestampconversion(ts#x, TimestampType,
LongType), LongType, TimestampType), end,
knownnullable(precisetimestampconversion(precisetimestampconversion(cast(ts#x +
cast(gap#x as interval) as timestamp), TimestampType, LongType), LongType,
TimestampType))) AS session_window#x, a#x, ts#x, gap#x]
+ +- SubqueryAlias tab
+ +- LocalRelation [a#x, ts#x, gap#x]
+
+
+-- !query
+SELECT
+ session_window.start,
+ session_window.end,
+ count(*) AS cnt
+FROM VALUES
+ (CAST('2021-01-01 00:00:00' AS TIMESTAMP_NTZ)),
+ (CAST('2021-01-01 00:04:30' AS TIMESTAMP_NTZ)),
+ (CAST('2021-01-01 00:10:00' AS TIMESTAMP_NTZ))
+AS tab(ts)
+GROUP BY session_window(ts, '5 minutes')
+ORDER BY start
+-- !query analysis
+Sort [start#x ASC NULLS FIRST], true
++- Aggregate [session_window#x], [session_window#x.start AS start#x,
session_window#x.end AS end#x, count(1) AS cnt#xL]
+ +- Filter isnotnull(ts#x)
+ +- Project [named_struct(start,
precisetimestampconversion(precisetimestampconversion(ts#x, TimestampNTZType,
LongType), LongType, TimestampNTZType), end,
knownnullable(precisetimestampconversion(precisetimestampconversion(cast(ts#x +
cast(5 minutes as interval) as timestamp_ntz), TimestampNTZType, LongType),
LongType, TimestampNTZType))) AS session_window#x, ts#x]
+ +- SubqueryAlias tab
+ +- LocalRelation [ts#x]
+
+
+-- !query
+SELECT
+ session_window.start,
+ session_window.end,
+ count(*) AS cnt
+FROM VALUES
+ (CAST(NULL AS TIMESTAMP)),
+ (CAST('2021-01-01 00:00:00' AS TIMESTAMP)),
+ (CAST('2021-01-01 00:01:00' AS TIMESTAMP))
+AS tab(ts)
+GROUP BY session_window(ts, '5 minutes')
+ORDER BY start
+-- !query analysis
+Sort [start#x ASC NULLS FIRST], true
++- Aggregate [session_window#x], [session_window#x.start AS start#x,
session_window#x.end AS end#x, count(1) AS cnt#xL]
+ +- Filter isnotnull(ts#x)
+ +- Project [named_struct(start,
precisetimestampconversion(precisetimestampconversion(ts#x, TimestampType,
LongType), LongType, TimestampType), end,
knownnullable(precisetimestampconversion(precisetimestampconversion(cast(ts#x +
cast(5 minutes as interval) as timestamp), TimestampType, LongType), LongType,
TimestampType))) AS session_window#x, ts#x]
+ +- SubqueryAlias tab
+ +- LocalRelation [ts#x]
+
+
+-- !query
+SELECT
+ session_window.start,
+ session_window.end,
+ count(*) AS cnt
+FROM VALUES
+ (CAST('2021-01-01 00:00:00.500' AS TIMESTAMP)),
+ (CAST('2021-01-01 00:00:00.700' AS TIMESTAMP)),
+ (CAST('2021-01-01 00:00:01.200' AS TIMESTAMP))
+AS tab(ts)
+GROUP BY session_window(ts, '500 milliseconds')
+ORDER BY start
+-- !query analysis
+Sort [start#x ASC NULLS FIRST], true
++- Aggregate [session_window#x], [session_window#x.start AS start#x,
session_window#x.end AS end#x, count(1) AS cnt#xL]
+ +- Filter isnotnull(ts#x)
+ +- Project [named_struct(start,
precisetimestampconversion(precisetimestampconversion(ts#x, TimestampType,
LongType), LongType, TimestampType), end,
knownnullable(precisetimestampconversion(precisetimestampconversion(cast(ts#x +
cast(500 milliseconds as interval) as timestamp), TimestampType, LongType),
LongType, TimestampType))) AS session_window#x, ts#x]
+ +- SubqueryAlias tab
+ +- LocalRelation [ts#x]
+
+
+-- !query
+SELECT count(*) FROM ts_data GROUP BY session_window(ts, '-5 minutes')
+-- !query analysis
+Aggregate [session_window#x], [count(1) AS count(1)#xL]
++- Filter (isnotnull(ts#x) AND (session_window#x.end > session_window#x.start))
+ +- Project [named_struct(start,
precisetimestampconversion(precisetimestampconversion(ts#x, TimestampType,
LongType), LongType, TimestampType), end,
knownnullable(precisetimestampconversion(precisetimestampconversion(cast(ts#x +
cast(-5 minutes as interval) as timestamp), TimestampType, LongType), LongType,
TimestampType))) AS session_window#x, a#x, ts#x]
+ +- SubqueryAlias ts_data
+ +- View (`ts_data`, [a#x, ts#x])
+ +- Project [cast(a#x as string) AS a#x, cast(ts#x as timestamp) AS
ts#x]
+ +- Project [a#x, ts#x]
+ +- SubqueryAlias tab
+ +- LocalRelation [a#x, ts#x]
+
+
+-- !query
+SELECT count(*) FROM ts_data GROUP BY session_window(ts, '0 seconds')
+-- !query analysis
+Aggregate [session_window#x], [count(1) AS count(1)#xL]
++- Filter (isnotnull(ts#x) AND (session_window#x.end > session_window#x.start))
+ +- Project [named_struct(start,
precisetimestampconversion(precisetimestampconversion(ts#x, TimestampType,
LongType), LongType, TimestampType), end,
knownnullable(precisetimestampconversion(precisetimestampconversion(cast(ts#x +
cast(0 seconds as interval) as timestamp), TimestampType, LongType), LongType,
TimestampType))) AS session_window#x, a#x, ts#x]
+ +- SubqueryAlias ts_data
+ +- View (`ts_data`, [a#x, ts#x])
+ +- Project [cast(a#x as string) AS a#x, cast(ts#x as timestamp) AS
ts#x]
+ +- Project [a#x, ts#x]
+ +- SubqueryAlias tab
+ +- LocalRelation [a#x, ts#x]
+
+
+-- !query
+SELECT count(*) FROM ts_data GROUP BY session_window(ts)
+-- !query analysis
+org.apache.spark.sql.AnalysisException
+{
+ "errorClass" : "WRONG_NUM_ARGS.WITHOUT_SUGGESTION",
+ "sqlState" : "42605",
+ "messageParameters" : {
+ "actualNum" : "1",
+ "docroot" : "https://spark.apache.org/docs/latest";,
+ "expectedNum" : "2",
+ "functionName" : "`session_window`"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 39,
+ "stopIndex" : 56,
+ "fragment" : "session_window(ts)"
+ } ]
+}
+
+
+-- !query
+SELECT count(*) FROM ts_data
+GROUP BY session_window(ts, '5 minutes'), session_window(ts, '10 minutes')
+-- !query analysis
+org.apache.spark.sql.AnalysisException
+{
+ "errorClass" : "_LEGACY_ERROR_TEMP_1039",
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 30,
+ "stopIndex" : 103,
+ "fragment" : "GROUP BY session_window(ts, '5 minutes'), session_window(ts,
'10 minutes')"
+ } ]
+}
+
+
+-- !query
+SELECT window_time(session_window) AS event_time, count(*) AS cnt
+FROM ts_data
+GROUP BY session_window(ts, '5 minutes')
+ORDER BY event_time
+-- !query analysis
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "MISSING_AGGREGATION",
+ "sqlState" : "42803",
+ "messageParameters" : {
+ "expression" : "\"window_time(session_window)\"",
+ "expressionAnyValue" : "\"any_value(window_time(session_window))\""
+ }
+}
+
+
+-- !query
+SELECT count(*)
+FROM ts_data
+WHERE session_window(ts, '5 minutes').start IS NOT NULL
+-- !query analysis
+Aggregate [count(1) AS count(1)#xL]
++- Filter isnotnull(session_window#x.start)
+ +- Filter isnotnull(ts#x)
+ +- Project [named_struct(start,
precisetimestampconversion(precisetimestampconversion(ts#x, TimestampType,
LongType), LongType, TimestampType), end,
knownnullable(precisetimestampconversion(precisetimestampconversion(cast(ts#x +
cast(5 minutes as interval) as timestamp), TimestampType, LongType), LongType,
TimestampType))) AS session_window#x, a#x, ts#x]
+ +- SubqueryAlias ts_data
+ +- View (`ts_data`, [a#x, ts#x])
+ +- Project [cast(a#x as string) AS a#x, cast(ts#x as timestamp)
AS ts#x]
+ +- Project [a#x, ts#x]
+ +- SubqueryAlias tab
+ +- LocalRelation [a#x, ts#x]
+
+
+-- !query
+SELECT ts, row_number() OVER (ORDER BY ts) AS rn
+FROM ts_data
+QUALIFY session_window(ts, '5 minutes').start IS NOT NULL
+-- !query analysis
+Filter isnotnull(session_window#x.start)
++- Filter isnotnull(ts#x)
+ +- Project [named_struct(start,
precisetimestampconversion(precisetimestampconversion(ts#x, TimestampType,
LongType), LongType, TimestampType), end,
knownnullable(precisetimestampconversion(precisetimestampconversion(cast(ts#x +
cast(5 minutes as interval) as timestamp), TimestampType, LongType), LongType,
TimestampType))) AS session_window#x, ts#x, rn#x]
+ +- Project [ts#x, rn#x]
+ +- Project [ts#x, rn#x, rn#x]
+ +- Window [row_number() windowspecdefinition(ts#x ASC NULLS FIRST,
specifiedwindowframe(RowFrame, unboundedpreceding$(), currentrow$())) AS rn#x],
[ts#x ASC NULLS FIRST]
+ +- Project [ts#x]
+ +- SubqueryAlias ts_data
+ +- View (`ts_data`, [a#x, ts#x])
+ +- Project [cast(a#x as string) AS a#x, cast(ts#x as
timestamp) AS ts#x]
+ +- Project [a#x, ts#x]
+ +- SubqueryAlias tab
+ +- LocalRelation [a#x, ts#x]
+
+
+-- !query
+SELECT * FROM ts_data GROUP BY a, session_window(ts, '5 minutes')
+-- !query analysis
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "MISSING_AGGREGATION",
+ "sqlState" : "42803",
+ "messageParameters" : {
+ "expression" : "\"ts\"",
+ "expressionAnyValue" : "\"any_value(ts)\""
+ }
+}
diff --git a/sql/core/src/test/resources/sql-tests/inputs/time-window.sql
b/sql/core/src/test/resources/sql-tests/inputs/time-window.sql
index 31771ff8d934..967920820a6e 100644
--- a/sql/core/src/test/resources/sql-tests/inputs/time-window.sql
+++ b/sql/core/src/test/resources/sql-tests/inputs/time-window.sql
@@ -229,3 +229,550 @@ FROM VALUES
AS tab(ts)
GROUP BY session_window(ts, '5 minutes')
ORDER BY start;
+
+-- Setup: temp view used by sum/nested-window cases below
+CREATE OR REPLACE TEMPORARY VIEW ts_vals AS
+SELECT * FROM VALUES
+ (CAST('2021-01-01 00:00:00' AS TIMESTAMP), 10),
+ (CAST('2021-01-01 00:01:00' AS TIMESTAMP), 20),
+ (CAST('2021-01-01 00:06:00' AS TIMESTAMP), 30)
+AS tab(ts, v);
+
+-- tumbling_window_basic
+SELECT
+ a,
+ window.start,
+ window.end,
+ count(*) AS cnt
+FROM ts_data
+GROUP BY a, window(ts, '5 minutes')
+ORDER BY a, start;
+
+-- window_1_hour
+SELECT
+ window.start,
+ window.end,
+ count(*) AS cnt
+FROM ts_data
+GROUP BY window(ts, '1 hour')
+ORDER BY start;
+
+-- sliding_window_basic
+SELECT
+ window.start,
+ window.end,
+ count(*) AS cnt
+FROM ts_data
+GROUP BY window(ts, '10 minutes', '5 minutes')
+ORDER BY window.start;
+
+-- sliding_window_non_exact_division
+SELECT
+ window.start,
+ window.end,
+ count(*) AS cnt
+FROM ts_data
+GROUP BY window(ts, '7 minutes', '3 minutes')
+ORDER BY window.start;
+
+-- sliding_window_ntz
+SELECT
+ window.start,
+ window.end,
+ count(*) AS cnt
+FROM (
+ SELECT TIMESTAMP_NTZ '2021-01-01 00:00:00' AS ts
+ UNION ALL SELECT TIMESTAMP_NTZ '2021-01-01 00:04:30'
+ UNION ALL SELECT TIMESTAMP_NTZ '2021-01-01 00:06:00'
+)
+GROUP BY window(ts, '10 minutes', '5 minutes')
+ORDER BY window.start;
+
+-- sliding_window_with_start_time
+SELECT
+ window.start,
+ window.end,
+ count(*) AS cnt
+FROM ts_data
+GROUP BY window(ts, '10 minutes', '5 minutes', '2 minutes')
+ORDER BY window.start;
+
+-- sliding_window_null_timestamps
+SELECT
+ window.start,
+ window.end,
+ sum(value) AS total
+FROM VALUES
+ (CAST('2016-03-27 09:00:05' AS TIMESTAMP), 1),
+ (CAST('2016-03-27 09:00:32' AS TIMESTAMP), 2),
+ (CAST(NULL AS TIMESTAMP), 3),
+ (CAST(NULL AS TIMESTAMP), 4)
+AS tab(time, value)
+GROUP BY window(time, '7 seconds', '3 seconds')
+ORDER BY window.start;
+
+-- window_order_by_desc
+SELECT
+ window.start,
+ window.end,
+ count(*) AS cnt
+FROM ts_data
+GROUP BY window(ts, '5 minutes')
+ORDER BY window.start DESC;
+
+-- window_with_sum_and_count
+SELECT
+ window.start,
+ sum(v),
+ count(*) AS cnt
+FROM ts_vals
+GROUP BY window(ts, '5 minutes')
+ORDER BY window.start;
+
+-- window_in_simple_project_tumbling
+SELECT window(ts, '5 minutes').start AS bucket_start
+FROM ts_data
+ORDER BY bucket_start;
+
+-- window_in_simple_project_sliding
+SELECT window(ts, '7 minutes', '3 minutes').start AS bucket_start
+FROM ts_data
+ORDER BY bucket_start;
+
+-- window_slide_exceeds_duration
+SELECT count(*)
+FROM ts_data
+GROUP BY window(ts, '10 minutes', '20 minutes');
+
+-- window_start_time_exceeds_slide
+SELECT count(*)
+FROM ts_data
+GROUP BY window(ts, '10 minutes', '5 minutes', '6 minutes');
+
+-- window_multiple_windows_error
+SELECT count(*)
+FROM ts_data
+GROUP BY window(ts, '5 minutes'), window(ts, '10 minutes');
+
+-- window_multiple_windows_nested_error
+SELECT count(*)
+FROM ts_data
+GROUP BY array(window(ts, '5 minutes'), window(ts, '10 minutes'));
+
+-- window_wrong_type
+SELECT count(*)
+FROM VALUES (1), (2) AS tab(x)
+GROUP BY window(x, '5 minutes');
+
+-- window_negative_duration
+SELECT count(*)
+FROM ts_data
+GROUP BY window(ts, '-5 minutes');
+
+-- tumbling_window_subsecond
+SELECT
+ window.start,
+ window.end,
+ count(*) AS cnt
+FROM VALUES
+ (CAST('2021-01-01 00:04:59.999999' AS TIMESTAMP)),
+ (CAST('2021-01-01 00:05:00.000001' AS TIMESTAMP)),
+ (CAST('2021-01-01 00:02:30.123456' AS TIMESTAMP))
+AS tab(ts)
+GROUP BY window(ts, '5 minutes')
+ORDER BY start;
+
+-- window_timestamp_ntz
+SELECT
+ window.start,
+ window.end,
+ count(*) AS cnt
+FROM VALUES
+ (CAST('2021-01-01 00:00:00' AS TIMESTAMP_NTZ)),
+ (CAST('2021-01-01 00:06:00' AS TIMESTAMP_NTZ))
+AS tab(ts)
+GROUP BY window(ts, '5 minutes')
+ORDER BY start;
+
+-- window_select_star_error
+SELECT *
+FROM ts_data
+GROUP BY a, window(ts, '5 minutes');
+
+-- window_select_star_subquery
+SELECT * FROM (
+ SELECT
+ a,
+ window.start,
+ window.end,
+ count(*) AS cnt
+ FROM ts_data
+ GROUP BY a, window(ts, '5 minutes')
+) sub
+ORDER BY a, start;
+
+-- window_wrong_arg_count_1
+SELECT count(*)
+FROM ts_data
+GROUP BY window(ts);
+
+-- window_unparseable_interval
+SELECT count(*)
+FROM ts_data
+GROUP BY window(ts, 'not_an_interval');
+
+-- window_month_based_interval
+SELECT count(*)
+FROM ts_data
+GROUP BY window(ts, '2 months');
+
+-- window_non_literal_duration
+SELECT count(*)
+FROM ts_data
+GROUP BY window(ts, a);
+
+-- window_negative_slide_duration
+SELECT count(*)
+FROM ts_data
+GROUP BY window(ts, '10 minutes', '-5 minutes');
+
+-- window_zero_duration
+SELECT count(*)
+FROM ts_data
+GROUP BY window(ts, '0 minutes');
+
+-- window_with_exists_subquery
+SELECT
+ window.start,
+ count(*) AS cnt
+FROM ts_data t1
+WHERE EXISTS (
+ SELECT 1 FROM ts_data t2 WHERE t2.a = t1.a AND t2.a = 'A1'
+)
+GROUP BY window(t1.ts, '5 minutes')
+ORDER BY start;
+
+-- negative_start_time
+SELECT
+ window.start,
+ window.end,
+ count(*) AS counts
+FROM VALUES
+ (CAST('2016-03-27 19:39:30' AS TIMESTAMP), 1, 'a'),
+ (CAST('2016-03-27 19:39:25' AS TIMESTAMP), 2, 'a')
+AS tab(time, value, id)
+GROUP BY window(time, '10 seconds', '10 seconds', '-5 seconds')
+ORDER BY window.start;
+
+-- tumbling_groupby_multi_bucket
+SELECT
+ window.start,
+ window.end,
+ count(*) AS counts
+FROM VALUES
+ (CAST('2016-03-27 19:39:34' AS TIMESTAMP), 1, 'a'),
+ (CAST('2016-03-27 19:39:56' AS TIMESTAMP), 2, 'a'),
+ (CAST('2016-03-27 19:39:27' AS TIMESTAMP), 4, 'b')
+AS tab(time, value, id)
+GROUP BY window(time, '10 seconds')
+ORDER BY window.start;
+
+-- tumbling_with_start_time
+SELECT
+ id,
+ window.start,
+ window.end,
+ count(*) AS counts
+FROM VALUES
+ (CAST('2016-03-27 19:39:34' AS TIMESTAMP), 1, 'a'),
+ (CAST('2016-03-27 19:39:56' AS TIMESTAMP), 2, 'a'),
+ (CAST('2016-03-27 19:39:27' AS TIMESTAMP), 4, 'b')
+AS tab(time, value, id)
+GROUP BY window(time, '10 seconds', '10 seconds', '5 seconds'), id
+ORDER BY id, window.start;
+
+-- null_timestamps
+SELECT
+ window.start,
+ window.end,
+ sum(value) AS total
+FROM VALUES
+ (CAST('2016-03-27 09:00:05' AS TIMESTAMP), 1),
+ (CAST('2016-03-27 09:00:32' AS TIMESTAMP), 2),
+ (CAST(NULL AS TIMESTAMP), 3),
+ (CAST(NULL AS TIMESTAMP), 4)
+AS tab(time, value)
+GROUP BY window(time, '10 seconds')
+ORDER BY window.start;
+
+-- window_in_select_group_by_all
+SELECT window(ts, '5 minutes').end AS bucket_end, count(*) AS cnt
+FROM ts_data
+GROUP BY ALL
+ORDER BY bucket_end;
+
+-- window_in_select_and_group_by
+SELECT
+ window(ts, '5 minutes').start AS bucket_start,
+ window(ts, '5 minutes').end AS bucket_end,
+ count(*) AS cnt
+FROM ts_data
+GROUP BY window(ts, '5 minutes')
+ORDER BY bucket_start;
+
+-- window_multi_in_select_group_by_all
+SELECT
+ window(ts, '5 minutes').start AS bucket_start,
+ window(ts, '5 minutes').end AS bucket_end,
+ count(*) AS cnt
+FROM ts_data
+GROUP BY ALL
+ORDER BY bucket_start;
+
+-- window_different_durations_rejected
+SELECT
+ window(ts, '5 minutes').start AS bucket_5,
+ window(ts, '10 minutes').end AS bucket_10,
+ count(*) AS cnt
+FROM ts_data
+GROUP BY window(ts, '5 minutes');
+
+-- window_time_basic
+SELECT window_time(window) AS event_time, count(*) AS cnt
+FROM ts_data
+GROUP BY window(ts, '5 minutes')
+ORDER BY event_time;
+
+-- window_time_ntz
+WITH ts_ntz_data AS (
+ SELECT TIMESTAMP_NTZ '2021-01-01 00:00:00' AS ts
+ UNION ALL SELECT TIMESTAMP_NTZ '2021-01-01 00:04:30'
+ UNION ALL SELECT TIMESTAMP_NTZ '2021-01-01 00:06:00'
+)
+SELECT window_time(window) AS event_time, count(*) AS cnt
+FROM ts_ntz_data
+GROUP BY window(ts, '5 minutes')
+ORDER BY event_time;
+
+-- window_time_wrong_type
+SELECT window_time(ts) FROM ts_data;
+
+-- nested_window_in_group_by
+SELECT outer_start, outer_end, total_cnt
+FROM (
+ SELECT
+ window.start AS outer_start,
+ window.end AS outer_end,
+ sum(v) AS total_cnt
+ FROM (
+ SELECT
+ named_struct('start', ts, 'end', ts + INTERVAL 1 MINUTE) AS inner_window,
+ v
+ FROM ts_vals
+ )
+ GROUP BY window(inner_window, '5 minutes')
+)
+ORDER BY outer_start;
+
+-- nested_window_via_subquery
+SELECT outer_window.start AS outer_start, outer_window.end AS outer_end, cnt
+FROM (
+ SELECT window(inner_window, '5 minutes') AS outer_window, sum(cnt) AS cnt
+ FROM (
+ SELECT window(ts, '1 minute') AS inner_window, count(*) AS cnt
+ FROM ts_data
+ GROUP BY window(ts, '1 minute')
+ )
+ GROUP BY window(inner_window, '5 minutes')
+)
+ORDER BY outer_start;
+
+-- nested_literal_window_in_group_by
+SELECT
+ window(window(ts, '1 minute'), '5 minutes').start AS outer_start,
+ count(*) AS cnt
+FROM ts_data
+GROUP BY window(window(ts, '1 minute'), '5 minutes')
+ORDER BY outer_start;
+
+-- nested_literal_window_in_group_by_all
+SELECT
+ window(window(ts, '1 minute'), '5 minutes').start AS outer_start,
+ window(window(ts, '1 minute'), '5 minutes').end AS outer_end,
+ count(*) AS cnt
+FROM ts_data
+GROUP BY ALL
+ORDER BY outer_start;
+
+-- window_time_wrong_arity
+SELECT window_time(window, window) FROM ts_data GROUP BY window(ts, '5
minutes');
+
+-- window_time_struct_wrong_inner_type
+SELECT window_time(named_struct('start', 1, 'end', 2)) FROM ts_data;
+
+-- window_time_struct_wrong_field_names
+SELECT window_time(named_struct('foo', ts, 'bar', ts)) FROM ts_data;
+
+-- window_time_struct_inline_rejected
+SELECT window_time(named_struct('start', ts, 'end', ts + INTERVAL 1 MINUTE))
FROM ts_data;
+
+-- window_time_struct_from_cte_rejected
+WITH x AS (SELECT named_struct('start', ts, 'end', ts + INTERVAL 1 MINUTE) AS
w FROM ts_data)
+SELECT window_time(w) FROM x
+ORDER BY 1;
+
+-- window_in_simple_project_repeated_same
+SELECT
+ window(ts, '5 minutes').start AS bucket_start,
+ window(ts, '5 minutes').end AS bucket_end
+FROM ts_data
+ORDER BY bucket_start;
+
+-- window_in_simple_project_different_windows_error
+SELECT
+ window(ts, '5 minutes').start AS bucket_5,
+ window(ts, '10 minutes').end AS bucket_10
+FROM ts_data;
+
+-- window_in_where_clause
+SELECT count(*)
+FROM ts_data
+WHERE window(ts, '5 minutes').start IS NOT NULL;
+
+-- window_in_having_clause_rejected
+SELECT count(*) AS cnt
+FROM ts_data
+GROUP BY ts
+HAVING window(ts, '5 minutes').start IS NOT NULL;
+
+-- window_in_order_by_rejected
+SELECT count(*) FROM ts_data GROUP BY ts ORDER BY window(ts, '5
minutes').start;
+
+-- window_in_qualify
+SELECT ts, row_number() OVER (ORDER BY ts) AS rn FROM ts_data QUALIFY
window(ts, '5 minutes').start IS NOT NULL;
+
+-- window_in_pivot_aggregate_rejected
+SELECT * FROM ts_data
+PIVOT (window(ts, '5 minutes').start FOR a IN ('A1', 'A2'));
+
+-- stacked_sibling_windows_on_same_base_rejected
+SELECT
+ window(window(ts, '1 minute'), '5 minutes').start,
+ window(window(ts, '1 minute'), '10 minutes').end
+FROM ts_data;
+
+-- nested_window_bare_reference_ambiguous
+SELECT window.start FROM ts_data GROUP BY window(window(ts, '1 minute'), '5
minutes');
+
+-- session_window_basic
+SELECT
+ a,
+ session_window.start,
+ session_window.end,
+ count(*) AS cnt
+FROM ts_data
+GROUP BY a, session_window(ts, '5 minutes')
+ORDER BY a, start;
+
+-- session_window_with_conditional_gap
+SELECT
+ a,
+ session_window.start,
+ session_window.end,
+ count(*) AS cnt
+FROM VALUES
+ ('A1', CAST('2021-01-01 00:00:00' AS TIMESTAMP)),
+ ('A1', CAST('2021-01-01 00:04:30' AS TIMESTAMP)),
+ ('A1', CAST('2021-01-01 00:10:00' AS TIMESTAMP)),
+ ('A2', CAST('2021-01-01 00:01:00' AS TIMESTAMP)),
+ ('A2', CAST('2021-01-01 00:04:30' AS TIMESTAMP))
+AS tab(a, ts)
+GROUP BY a, session_window(ts, CASE WHEN a = 'A1' THEN '5 minutes'
+ WHEN a = 'A2' THEN '1 minute'
+ ELSE '10 minutes' END)
+ORDER BY a, start;
+
+-- session_window_with_column_gap
+SELECT
+ a,
+ session_window.start,
+ session_window.end,
+ count(*) AS cnt
+FROM VALUES
+ ('A1', CAST('2021-01-01 00:00:00' AS TIMESTAMP), '5 minutes'),
+ ('A1', CAST('2021-01-01 00:04:30' AS TIMESTAMP), '5 minutes'),
+ ('A2', CAST('2021-01-01 00:01:00' AS TIMESTAMP), '1 minute')
+AS tab(a, ts, gap)
+GROUP BY a, session_window(ts, gap)
+ORDER BY a, start;
+
+-- session_window_ntz
+SELECT
+ session_window.start,
+ session_window.end,
+ count(*) AS cnt
+FROM VALUES
+ (CAST('2021-01-01 00:00:00' AS TIMESTAMP_NTZ)),
+ (CAST('2021-01-01 00:04:30' AS TIMESTAMP_NTZ)),
+ (CAST('2021-01-01 00:10:00' AS TIMESTAMP_NTZ))
+AS tab(ts)
+GROUP BY session_window(ts, '5 minutes')
+ORDER BY start;
+
+-- session_window_null_timestamps
+SELECT
+ session_window.start,
+ session_window.end,
+ count(*) AS cnt
+FROM VALUES
+ (CAST(NULL AS TIMESTAMP)),
+ (CAST('2021-01-01 00:00:00' AS TIMESTAMP)),
+ (CAST('2021-01-01 00:01:00' AS TIMESTAMP))
+AS tab(ts)
+GROUP BY session_window(ts, '5 minutes')
+ORDER BY start;
+
+-- session_window_subsecond
+SELECT
+ session_window.start,
+ session_window.end,
+ count(*) AS cnt
+FROM VALUES
+ (CAST('2021-01-01 00:00:00.500' AS TIMESTAMP)),
+ (CAST('2021-01-01 00:00:00.700' AS TIMESTAMP)),
+ (CAST('2021-01-01 00:00:01.200' AS TIMESTAMP))
+AS tab(ts)
+GROUP BY session_window(ts, '500 milliseconds')
+ORDER BY start;
+
+-- session_window_negative_gap
+SELECT count(*) FROM ts_data GROUP BY session_window(ts, '-5 minutes');
+
+-- session_window_zero_gap
+SELECT count(*) FROM ts_data GROUP BY session_window(ts, '0 seconds');
+
+-- session_window_wrong_arg_count
+SELECT count(*) FROM ts_data GROUP BY session_window(ts);
+
+-- session_window_multiple_error
+SELECT count(*) FROM ts_data
+GROUP BY session_window(ts, '5 minutes'), session_window(ts, '10 minutes');
+
+-- window_time_session_basic_rejected
+SELECT window_time(session_window) AS event_time, count(*) AS cnt
+FROM ts_data
+GROUP BY session_window(ts, '5 minutes')
+ORDER BY event_time;
+
+-- session_window_in_where_clause
+SELECT count(*)
+FROM ts_data
+WHERE session_window(ts, '5 minutes').start IS NOT NULL;
+
+-- session_window_in_qualify
+SELECT ts, row_number() OVER (ORDER BY ts) AS rn
+FROM ts_data
+QUALIFY session_window(ts, '5 minutes').start IS NOT NULL;
+
+-- session_window_select_star_error
+SELECT * FROM ts_data GROUP BY a, session_window(ts, '5 minutes');
diff --git a/sql/core/src/test/resources/sql-tests/results/time-window.sql.out
b/sql/core/src/test/resources/sql-tests/results/time-window.sql.out
index 0e3bca21d4a0..f3ec372005eb 100644
--- a/sql/core/src/test/resources/sql-tests/results/time-window.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/time-window.sql.out
@@ -506,3 +506,1352 @@ ORDER BY start
struct<start:timestamp,end:timestamp,cnt:bigint>
-- !query output
1969-12-31 23:55:00 1970-01-01 00:05:00 3
+
+
+-- !query
+CREATE OR REPLACE TEMPORARY VIEW ts_vals AS
+SELECT * FROM VALUES
+ (CAST('2021-01-01 00:00:00' AS TIMESTAMP), 10),
+ (CAST('2021-01-01 00:01:00' AS TIMESTAMP), 20),
+ (CAST('2021-01-01 00:06:00' AS TIMESTAMP), 30)
+AS tab(ts, v)
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+SELECT
+ a,
+ window.start,
+ window.end,
+ count(*) AS cnt
+FROM ts_data
+GROUP BY a, window(ts, '5 minutes')
+ORDER BY a, start
+-- !query schema
+struct<a:string,start:timestamp,end:timestamp,cnt:bigint>
+-- !query output
+A1 2021-01-01 00:00:00 2021-01-01 00:05:00 2
+A1 2021-01-01 00:05:00 2021-01-01 00:10:00 1
+A2 2021-01-01 00:00:00 2021-01-01 00:05:00 1
+
+
+-- !query
+SELECT
+ window.start,
+ window.end,
+ count(*) AS cnt
+FROM ts_data
+GROUP BY window(ts, '1 hour')
+ORDER BY start
+-- !query schema
+struct<start:timestamp,end:timestamp,cnt:bigint>
+-- !query output
+2021-01-01 00:00:00 2021-01-01 01:00:00 4
+
+
+-- !query
+SELECT
+ window.start,
+ window.end,
+ count(*) AS cnt
+FROM ts_data
+GROUP BY window(ts, '10 minutes', '5 minutes')
+ORDER BY window.start
+-- !query schema
+struct<start:timestamp,end:timestamp,cnt:bigint>
+-- !query output
+2020-12-31 23:55:00 2021-01-01 00:05:00 3
+2021-01-01 00:00:00 2021-01-01 00:10:00 4
+2021-01-01 00:05:00 2021-01-01 00:15:00 1
+
+
+-- !query
+SELECT
+ window.start,
+ window.end,
+ count(*) AS cnt
+FROM ts_data
+GROUP BY window(ts, '7 minutes', '3 minutes')
+ORDER BY window.start
+-- !query schema
+struct<start:timestamp,end:timestamp,cnt:bigint>
+-- !query output
+2020-12-31 23:54:00 2021-01-01 00:01:00 1
+2020-12-31 23:57:00 2021-01-01 00:04:00 2
+2021-01-01 00:00:00 2021-01-01 00:07:00 4
+2021-01-01 00:03:00 2021-01-01 00:10:00 2
+2021-01-01 00:06:00 2021-01-01 00:13:00 1
+
+
+-- !query
+SELECT
+ window.start,
+ window.end,
+ count(*) AS cnt
+FROM (
+ SELECT TIMESTAMP_NTZ '2021-01-01 00:00:00' AS ts
+ UNION ALL SELECT TIMESTAMP_NTZ '2021-01-01 00:04:30'
+ UNION ALL SELECT TIMESTAMP_NTZ '2021-01-01 00:06:00'
+)
+GROUP BY window(ts, '10 minutes', '5 minutes')
+ORDER BY window.start
+-- !query schema
+struct<start:timestamp_ntz,end:timestamp_ntz,cnt:bigint>
+-- !query output
+2020-12-31 23:55:00 2021-01-01 00:05:00 2
+2021-01-01 00:00:00 2021-01-01 00:10:00 3
+2021-01-01 00:05:00 2021-01-01 00:15:00 1
+
+
+-- !query
+SELECT
+ window.start,
+ window.end,
+ count(*) AS cnt
+FROM ts_data
+GROUP BY window(ts, '10 minutes', '5 minutes', '2 minutes')
+ORDER BY window.start
+-- !query schema
+struct<start:timestamp,end:timestamp,cnt:bigint>
+-- !query output
+2020-12-31 23:52:00 2021-01-01 00:02:00 2
+2020-12-31 23:57:00 2021-01-01 00:07:00 4
+2021-01-01 00:02:00 2021-01-01 00:12:00 2
+
+
+-- !query
+SELECT
+ window.start,
+ window.end,
+ sum(value) AS total
+FROM VALUES
+ (CAST('2016-03-27 09:00:05' AS TIMESTAMP), 1),
+ (CAST('2016-03-27 09:00:32' AS TIMESTAMP), 2),
+ (CAST(NULL AS TIMESTAMP), 3),
+ (CAST(NULL AS TIMESTAMP), 4)
+AS tab(time, value)
+GROUP BY window(time, '7 seconds', '3 seconds')
+ORDER BY window.start
+-- !query schema
+struct<start:timestamp,end:timestamp,total:bigint>
+-- !query output
+2016-03-27 09:00:00 2016-03-27 09:00:07 1
+2016-03-27 09:00:03 2016-03-27 09:00:10 1
+2016-03-27 09:00:27 2016-03-27 09:00:34 2
+2016-03-27 09:00:30 2016-03-27 09:00:37 2
+
+
+-- !query
+SELECT
+ window.start,
+ window.end,
+ count(*) AS cnt
+FROM ts_data
+GROUP BY window(ts, '5 minutes')
+ORDER BY window.start DESC
+-- !query schema
+struct<start:timestamp,end:timestamp,cnt:bigint>
+-- !query output
+2021-01-01 00:05:00 2021-01-01 00:10:00 1
+2021-01-01 00:00:00 2021-01-01 00:05:00 3
+
+
+-- !query
+SELECT
+ window.start,
+ sum(v),
+ count(*) AS cnt
+FROM ts_vals
+GROUP BY window(ts, '5 minutes')
+ORDER BY window.start
+-- !query schema
+struct<start:timestamp,sum(v):bigint,cnt:bigint>
+-- !query output
+2021-01-01 00:00:00 30 2
+2021-01-01 00:05:00 30 1
+
+
+-- !query
+SELECT window(ts, '5 minutes').start AS bucket_start
+FROM ts_data
+ORDER BY bucket_start
+-- !query schema
+struct<bucket_start:timestamp>
+-- !query output
+2021-01-01 00:00:00
+2021-01-01 00:00:00
+2021-01-01 00:00:00
+2021-01-01 00:05:00
+
+
+-- !query
+SELECT window(ts, '7 minutes', '3 minutes').start AS bucket_start
+FROM ts_data
+ORDER BY bucket_start
+-- !query schema
+struct<bucket_start:timestamp>
+-- !query output
+2020-12-31 23:54:00
+2020-12-31 23:57:00
+2020-12-31 23:57:00
+2021-01-01 00:00:00
+2021-01-01 00:00:00
+2021-01-01 00:00:00
+2021-01-01 00:00:00
+2021-01-01 00:03:00
+2021-01-01 00:03:00
+2021-01-01 00:06:00
+
+
+-- !query
+SELECT count(*)
+FROM ts_data
+GROUP BY window(ts, '10 minutes', '20 minutes')
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "DATATYPE_MISMATCH.PARAMETER_CONSTRAINT_VIOLATION",
+ "sqlState" : "42K09",
+ "messageParameters" : {
+ "constraint" : "<=",
+ "leftExprName" : "`slide_duration`",
+ "leftExprValue" : "1200000000L",
+ "rightExprName" : "`window_duration`",
+ "rightExprValue" : "600000000L",
+ "sqlExpr" : "\"window(ts, 600000000, 1200000000, 0)\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 39,
+ "stopIndex" : 76,
+ "fragment" : "window(ts, '10 minutes', '20 minutes')"
+ } ]
+}
+
+
+-- !query
+SELECT count(*)
+FROM ts_data
+GROUP BY window(ts, '10 minutes', '5 minutes', '6 minutes')
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "DATATYPE_MISMATCH.PARAMETER_CONSTRAINT_VIOLATION",
+ "sqlState" : "42K09",
+ "messageParameters" : {
+ "constraint" : "<",
+ "leftExprName" : "`abs(start_time)`",
+ "leftExprValue" : "360000000L",
+ "rightExprName" : "`slide_duration`",
+ "rightExprValue" : "300000000L",
+ "sqlExpr" : "\"window(ts, 600000000, 300000000, 360000000)\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 39,
+ "stopIndex" : 88,
+ "fragment" : "window(ts, '10 minutes', '5 minutes', '6 minutes')"
+ } ]
+}
+
+
+-- !query
+SELECT count(*)
+FROM ts_data
+GROUP BY window(ts, '5 minutes'), window(ts, '10 minutes')
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+{
+ "errorClass" : "_LEGACY_ERROR_TEMP_1039",
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 30,
+ "stopIndex" : 87,
+ "fragment" : "GROUP BY window(ts, '5 minutes'), window(ts, '10 minutes')"
+ } ]
+}
+
+
+-- !query
+SELECT count(*)
+FROM ts_data
+GROUP BY array(window(ts, '5 minutes'), window(ts, '10 minutes'))
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+{
+ "errorClass" : "_LEGACY_ERROR_TEMP_1039",
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 30,
+ "stopIndex" : 94,
+ "fragment" : "GROUP BY array(window(ts, '5 minutes'), window(ts, '10
minutes'))"
+ } ]
+}
+
+
+-- !query
+SELECT count(*)
+FROM VALUES (1), (2) AS tab(x)
+GROUP BY window(x, '5 minutes')
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE",
+ "sqlState" : "42K09",
+ "messageParameters" : {
+ "inputSql" : "\"x\"",
+ "inputType" : "\"INT\"",
+ "paramIndex" : "first",
+ "requiredType" : "(\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\" or
\"STRUCT<start: TIMESTAMP, end: TIMESTAMP>\" or \"STRUCT<start: TIMESTAMP_NTZ,
end: TIMESTAMP_NTZ>\")",
+ "sqlExpr" : "\"window(x, 300000000, 300000000, 0)\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 57,
+ "stopIndex" : 78,
+ "fragment" : "window(x, '5 minutes')"
+ } ]
+}
+
+
+-- !query
+SELECT count(*)
+FROM ts_data
+GROUP BY window(ts, '-5 minutes')
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "DATATYPE_MISMATCH.VALUE_OUT_OF_RANGE",
+ "sqlState" : "42K09",
+ "messageParameters" : {
+ "currentValue" : "-300000000L",
+ "exprName" : "`window_duration`",
+ "sqlExpr" : "\"window(ts, -300000000, -300000000, 0)\"",
+ "valueRange" : "(0, 9223372036854775807]"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 39,
+ "stopIndex" : 62,
+ "fragment" : "window(ts, '-5 minutes')"
+ } ]
+}
+
+
+-- !query
+SELECT
+ window.start,
+ window.end,
+ count(*) AS cnt
+FROM VALUES
+ (CAST('2021-01-01 00:04:59.999999' AS TIMESTAMP)),
+ (CAST('2021-01-01 00:05:00.000001' AS TIMESTAMP)),
+ (CAST('2021-01-01 00:02:30.123456' AS TIMESTAMP))
+AS tab(ts)
+GROUP BY window(ts, '5 minutes')
+ORDER BY start
+-- !query schema
+struct<start:timestamp,end:timestamp,cnt:bigint>
+-- !query output
+2021-01-01 00:00:00 2021-01-01 00:05:00 2
+2021-01-01 00:05:00 2021-01-01 00:10:00 1
+
+
+-- !query
+SELECT
+ window.start,
+ window.end,
+ count(*) AS cnt
+FROM VALUES
+ (CAST('2021-01-01 00:00:00' AS TIMESTAMP_NTZ)),
+ (CAST('2021-01-01 00:06:00' AS TIMESTAMP_NTZ))
+AS tab(ts)
+GROUP BY window(ts, '5 minutes')
+ORDER BY start
+-- !query schema
+struct<start:timestamp_ntz,end:timestamp_ntz,cnt:bigint>
+-- !query output
+2021-01-01 00:00:00 2021-01-01 00:05:00 1
+2021-01-01 00:05:00 2021-01-01 00:10:00 1
+
+
+-- !query
+SELECT *
+FROM ts_data
+GROUP BY a, window(ts, '5 minutes')
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "MISSING_AGGREGATION",
+ "sqlState" : "42803",
+ "messageParameters" : {
+ "expression" : "\"ts\"",
+ "expressionAnyValue" : "\"any_value(ts)\""
+ }
+}
+
+
+-- !query
+SELECT * FROM (
+ SELECT
+ a,
+ window.start,
+ window.end,
+ count(*) AS cnt
+ FROM ts_data
+ GROUP BY a, window(ts, '5 minutes')
+) sub
+ORDER BY a, start
+-- !query schema
+struct<a:string,start:timestamp,end:timestamp,cnt:bigint>
+-- !query output
+A1 2021-01-01 00:00:00 2021-01-01 00:05:00 2
+A1 2021-01-01 00:05:00 2021-01-01 00:10:00 1
+A2 2021-01-01 00:00:00 2021-01-01 00:05:00 1
+
+
+-- !query
+SELECT count(*)
+FROM ts_data
+GROUP BY window(ts)
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+{
+ "errorClass" : "WRONG_NUM_ARGS.WITHOUT_SUGGESTION",
+ "sqlState" : "42605",
+ "messageParameters" : {
+ "actualNum" : "1",
+ "docroot" : "https://spark.apache.org/docs/latest";,
+ "expectedNum" : "[2, 3, 4]",
+ "functionName" : "`window`"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 39,
+ "stopIndex" : 48,
+ "fragment" : "window(ts)"
+ } ]
+}
+
+
+-- !query
+SELECT count(*)
+FROM ts_data
+GROUP BY window(ts, 'not_an_interval')
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+{
+ "errorClass" : "CANNOT_PARSE_INTERVAL",
+ "sqlState" : "22006",
+ "messageParameters" : {
+ "intervalString" : "'not_an_interval'"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 39,
+ "stopIndex" : 67,
+ "fragment" : "window(ts, 'not_an_interval')"
+ } ]
+}
+
+
+-- !query
+SELECT count(*)
+FROM ts_data
+GROUP BY window(ts, '2 months')
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.SparkIllegalArgumentException
+{
+ "errorClass" : "_LEGACY_ERROR_TEMP_3231",
+ "messageParameters" : {
+ "interval" : "2 months"
+ }
+}
+
+
+-- !query
+SELECT count(*)
+FROM ts_data
+GROUP BY window(ts, a)
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+{
+ "errorClass" : "_LEGACY_ERROR_TEMP_1207",
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 39,
+ "stopIndex" : 51,
+ "fragment" : "window(ts, a)"
+ } ]
+}
+
+
+-- !query
+SELECT count(*)
+FROM ts_data
+GROUP BY window(ts, '10 minutes', '-5 minutes')
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "DATATYPE_MISMATCH.VALUE_OUT_OF_RANGE",
+ "sqlState" : "42K09",
+ "messageParameters" : {
+ "currentValue" : "-300000000L",
+ "exprName" : "`slide_duration`",
+ "sqlExpr" : "\"window(ts, 600000000, -300000000, 0)\"",
+ "valueRange" : "(0, 9223372036854775807]"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 39,
+ "stopIndex" : 76,
+ "fragment" : "window(ts, '10 minutes', '-5 minutes')"
+ } ]
+}
+
+
+-- !query
+SELECT count(*)
+FROM ts_data
+GROUP BY window(ts, '0 minutes')
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "DATATYPE_MISMATCH.VALUE_OUT_OF_RANGE",
+ "sqlState" : "42K09",
+ "messageParameters" : {
+ "currentValue" : "0L",
+ "exprName" : "`window_duration`",
+ "sqlExpr" : "\"window(ts, 0, 0, 0)\"",
+ "valueRange" : "(0, 9223372036854775807]"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 39,
+ "stopIndex" : 61,
+ "fragment" : "window(ts, '0 minutes')"
+ } ]
+}
+
+
+-- !query
+SELECT
+ window.start,
+ count(*) AS cnt
+FROM ts_data t1
+WHERE EXISTS (
+ SELECT 1 FROM ts_data t2 WHERE t2.a = t1.a AND t2.a = 'A1'
+)
+GROUP BY window(t1.ts, '5 minutes')
+ORDER BY start
+-- !query schema
+struct<start:timestamp,cnt:bigint>
+-- !query output
+2021-01-01 00:00:00 2
+2021-01-01 00:05:00 1
+
+
+-- !query
+SELECT
+ window.start,
+ window.end,
+ count(*) AS counts
+FROM VALUES
+ (CAST('2016-03-27 19:39:30' AS TIMESTAMP), 1, 'a'),
+ (CAST('2016-03-27 19:39:25' AS TIMESTAMP), 2, 'a')
+AS tab(time, value, id)
+GROUP BY window(time, '10 seconds', '10 seconds', '-5 seconds')
+ORDER BY window.start
+-- !query schema
+struct<start:timestamp,end:timestamp,counts:bigint>
+-- !query output
+2016-03-27 19:39:25 2016-03-27 19:39:35 2
+
+
+-- !query
+SELECT
+ window.start,
+ window.end,
+ count(*) AS counts
+FROM VALUES
+ (CAST('2016-03-27 19:39:34' AS TIMESTAMP), 1, 'a'),
+ (CAST('2016-03-27 19:39:56' AS TIMESTAMP), 2, 'a'),
+ (CAST('2016-03-27 19:39:27' AS TIMESTAMP), 4, 'b')
+AS tab(time, value, id)
+GROUP BY window(time, '10 seconds')
+ORDER BY window.start
+-- !query schema
+struct<start:timestamp,end:timestamp,counts:bigint>
+-- !query output
+2016-03-27 19:39:20 2016-03-27 19:39:30 1
+2016-03-27 19:39:30 2016-03-27 19:39:40 1
+2016-03-27 19:39:50 2016-03-27 19:40:00 1
+
+
+-- !query
+SELECT
+ id,
+ window.start,
+ window.end,
+ count(*) AS counts
+FROM VALUES
+ (CAST('2016-03-27 19:39:34' AS TIMESTAMP), 1, 'a'),
+ (CAST('2016-03-27 19:39:56' AS TIMESTAMP), 2, 'a'),
+ (CAST('2016-03-27 19:39:27' AS TIMESTAMP), 4, 'b')
+AS tab(time, value, id)
+GROUP BY window(time, '10 seconds', '10 seconds', '5 seconds'), id
+ORDER BY id, window.start
+-- !query schema
+struct<id:string,start:timestamp,end:timestamp,counts:bigint>
+-- !query output
+a 2016-03-27 19:39:25 2016-03-27 19:39:35 1
+a 2016-03-27 19:39:55 2016-03-27 19:40:05 1
+b 2016-03-27 19:39:25 2016-03-27 19:39:35 1
+
+
+-- !query
+SELECT
+ window.start,
+ window.end,
+ sum(value) AS total
+FROM VALUES
+ (CAST('2016-03-27 09:00:05' AS TIMESTAMP), 1),
+ (CAST('2016-03-27 09:00:32' AS TIMESTAMP), 2),
+ (CAST(NULL AS TIMESTAMP), 3),
+ (CAST(NULL AS TIMESTAMP), 4)
+AS tab(time, value)
+GROUP BY window(time, '10 seconds')
+ORDER BY window.start
+-- !query schema
+struct<start:timestamp,end:timestamp,total:bigint>
+-- !query output
+2016-03-27 09:00:00 2016-03-27 09:00:10 1
+2016-03-27 09:00:30 2016-03-27 09:00:40 2
+
+
+-- !query
+SELECT window(ts, '5 minutes').end AS bucket_end, count(*) AS cnt
+FROM ts_data
+GROUP BY ALL
+ORDER BY bucket_end
+-- !query schema
+struct<bucket_end:timestamp,cnt:bigint>
+-- !query output
+2021-01-01 00:05:00 3
+2021-01-01 00:10:00 1
+
+
+-- !query
+SELECT
+ window(ts, '5 minutes').start AS bucket_start,
+ window(ts, '5 minutes').end AS bucket_end,
+ count(*) AS cnt
+FROM ts_data
+GROUP BY window(ts, '5 minutes')
+ORDER BY bucket_start
+-- !query schema
+struct<bucket_start:timestamp,bucket_end:timestamp,cnt:bigint>
+-- !query output
+2021-01-01 00:00:00 2021-01-01 00:05:00 3
+2021-01-01 00:05:00 2021-01-01 00:10:00 1
+
+
+-- !query
+SELECT
+ window(ts, '5 minutes').start AS bucket_start,
+ window(ts, '5 minutes').end AS bucket_end,
+ count(*) AS cnt
+FROM ts_data
+GROUP BY ALL
+ORDER BY bucket_start
+-- !query schema
+struct<bucket_start:timestamp,bucket_end:timestamp,cnt:bigint>
+-- !query output
+2021-01-01 00:00:00 2021-01-01 00:05:00 3
+2021-01-01 00:05:00 2021-01-01 00:10:00 1
+
+
+-- !query
+SELECT
+ window(ts, '5 minutes').start AS bucket_5,
+ window(ts, '10 minutes').end AS bucket_10,
+ count(*) AS cnt
+FROM ts_data
+GROUP BY window(ts, '5 minutes')
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+{
+ "errorClass" : "_LEGACY_ERROR_TEMP_1039",
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 129,
+ "stopIndex" : 160,
+ "fragment" : "GROUP BY window(ts, '5 minutes')"
+ } ]
+}
+
+
+-- !query
+SELECT window_time(window) AS event_time, count(*) AS cnt
+FROM ts_data
+GROUP BY window(ts, '5 minutes')
+ORDER BY event_time
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "MISSING_AGGREGATION",
+ "sqlState" : "42803",
+ "messageParameters" : {
+ "expression" : "\"window_time(window)\"",
+ "expressionAnyValue" : "\"any_value(window_time(window))\""
+ }
+}
+
+
+-- !query
+WITH ts_ntz_data AS (
+ SELECT TIMESTAMP_NTZ '2021-01-01 00:00:00' AS ts
+ UNION ALL SELECT TIMESTAMP_NTZ '2021-01-01 00:04:30'
+ UNION ALL SELECT TIMESTAMP_NTZ '2021-01-01 00:06:00'
+)
+SELECT window_time(window) AS event_time, count(*) AS cnt
+FROM ts_ntz_data
+GROUP BY window(ts, '5 minutes')
+ORDER BY event_time
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "MISSING_AGGREGATION",
+ "sqlState" : "42803",
+ "messageParameters" : {
+ "expression" : "\"window_time(window)\"",
+ "expressionAnyValue" : "\"any_value(window_time(window))\""
+ }
+}
+
+
+-- !query
+SELECT window_time(ts) FROM ts_data
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE",
+ "sqlState" : "42K09",
+ "messageParameters" : {
+ "inputSql" : "\"ts\"",
+ "inputType" : "\"TIMESTAMP\"",
+ "paramIndex" : "first",
+ "requiredType" : "\"STRUCT\"",
+ "sqlExpr" : "\"window_time(ts)\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 22,
+ "fragment" : "window_time(ts)"
+ } ]
+}
+
+
+-- !query
+SELECT outer_start, outer_end, total_cnt
+FROM (
+ SELECT
+ window.start AS outer_start,
+ window.end AS outer_end,
+ sum(v) AS total_cnt
+ FROM (
+ SELECT
+ named_struct('start', ts, 'end', ts + INTERVAL 1 MINUTE) AS inner_window,
+ v
+ FROM ts_vals
+ )
+ GROUP BY window(inner_window, '5 minutes')
+)
+ORDER BY outer_start
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "_LEGACY_ERROR_TEMP_3101",
+ "messageParameters" : {
+ "windowTime" : "window_time(inner_window#x)"
+ }
+}
+
+
+-- !query
+SELECT outer_window.start AS outer_start, outer_window.end AS outer_end, cnt
+FROM (
+ SELECT window(inner_window, '5 minutes') AS outer_window, sum(cnt) AS cnt
+ FROM (
+ SELECT window(ts, '1 minute') AS inner_window, count(*) AS cnt
+ FROM ts_data
+ GROUP BY window(ts, '1 minute')
+ )
+ GROUP BY window(inner_window, '5 minutes')
+)
+ORDER BY outer_start
+-- !query schema
+struct<outer_start:timestamp,outer_end:timestamp,cnt:bigint>
+-- !query output
+2021-01-01 00:00:00 2021-01-01 00:05:00 3
+2021-01-01 00:05:00 2021-01-01 00:10:00 1
+
+
+-- !query
+SELECT
+ window(window(ts, '1 minute'), '5 minutes').start AS outer_start,
+ count(*) AS cnt
+FROM ts_data
+GROUP BY window(window(ts, '1 minute'), '5 minutes')
+ORDER BY outer_start
+-- !query schema
+struct<outer_start:timestamp,cnt:bigint>
+-- !query output
+2021-01-01 00:00:00 3
+2021-01-01 00:05:00 1
+
+
+-- !query
+SELECT
+ window(window(ts, '1 minute'), '5 minutes').start AS outer_start,
+ window(window(ts, '1 minute'), '5 minutes').end AS outer_end,
+ count(*) AS cnt
+FROM ts_data
+GROUP BY ALL
+ORDER BY outer_start
+-- !query schema
+struct<outer_start:timestamp,outer_end:timestamp,cnt:bigint>
+-- !query output
+2021-01-01 00:00:00 2021-01-01 00:05:00 3
+2021-01-01 00:05:00 2021-01-01 00:10:00 1
+
+
+-- !query
+SELECT window_time(window, window) FROM ts_data GROUP BY window(ts, '5
minutes')
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+{
+ "errorClass" : "WRONG_NUM_ARGS.WITHOUT_SUGGESTION",
+ "sqlState" : "42605",
+ "messageParameters" : {
+ "actualNum" : "2",
+ "docroot" : "https://spark.apache.org/docs/latest";,
+ "expectedNum" : "1",
+ "functionName" : "`window_time`"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 34,
+ "fragment" : "window_time(window, window)"
+ } ]
+}
+
+
+-- !query
+SELECT window_time(named_struct('start', 1, 'end', 2)) FROM ts_data
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "_LEGACY_ERROR_TEMP_3101",
+ "messageParameters" : {
+ "windowTime" : "window_time(named_struct(start, 1, end, 2))"
+ }
+}
+
+
+-- !query
+SELECT window_time(named_struct('foo', ts, 'bar', ts)) FROM ts_data
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "_LEGACY_ERROR_TEMP_3101",
+ "messageParameters" : {
+ "windowTime" : "window_time(named_struct(foo, ts#x, bar, ts#x))"
+ }
+}
+
+
+-- !query
+SELECT window_time(named_struct('start', ts, 'end', ts + INTERVAL 1 MINUTE))
FROM ts_data
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "_LEGACY_ERROR_TEMP_3101",
+ "messageParameters" : {
+ "windowTime" : "window_time(named_struct(start, ts#x, end, cast(ts#x +
INTERVAL '01' MINUTE as timestamp)))"
+ }
+}
+
+
+-- !query
+WITH x AS (SELECT named_struct('start', ts, 'end', ts + INTERVAL 1 MINUTE) AS
w FROM ts_data)
+SELECT window_time(w) FROM x
+ORDER BY 1
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "_LEGACY_ERROR_TEMP_3101",
+ "messageParameters" : {
+ "windowTime" : "window_time(w#x)"
+ }
+}
+
+
+-- !query
+SELECT
+ window(ts, '5 minutes').start AS bucket_start,
+ window(ts, '5 minutes').end AS bucket_end
+FROM ts_data
+ORDER BY bucket_start
+-- !query schema
+struct<bucket_start:timestamp,bucket_end:timestamp>
+-- !query output
+2021-01-01 00:00:00 2021-01-01 00:05:00
+2021-01-01 00:00:00 2021-01-01 00:05:00
+2021-01-01 00:00:00 2021-01-01 00:05:00
+2021-01-01 00:05:00 2021-01-01 00:10:00
+
+
+-- !query
+SELECT
+ window(ts, '5 minutes').start AS bucket_5,
+ window(ts, '10 minutes').end AS bucket_10
+FROM ts_data
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+{
+ "errorClass" : "_LEGACY_ERROR_TEMP_1039",
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 1,
+ "stopIndex" : 108,
+ "fragment" : "SELECT\n window(ts, '5 minutes').start AS bucket_5,\n
window(ts, '10 minutes').end AS bucket_10\nFROM ts_data"
+ } ]
+}
+
+
+-- !query
+SELECT count(*)
+FROM ts_data
+WHERE window(ts, '5 minutes').start IS NOT NULL
+-- !query schema
+struct<count(1):bigint>
+-- !query output
+4
+
+
+-- !query
+SELECT count(*) AS cnt
+FROM ts_data
+GROUP BY ts
+HAVING window(ts, '5 minutes').start IS NOT NULL
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "MISSING_ATTRIBUTES.RESOLVED_ATTRIBUTE_MISSING_FROM_INPUT",
+ "sqlState" : "XX000",
+ "messageParameters" : {
+ "input" : "\"cnt\"",
+ "missingAttributes" : "\"ts\"",
+ "operator" : "!Project [named_struct(start,
knownnullable(precisetimestampconversion(((precisetimestampconversion(ts#x,
TimestampType, LongType) - CASE WHEN (((precisetimestampconversion(ts#x,
TimestampType, LongType) - 0) % 300000000) < cast(0 as bigint)) THEN
(((precisetimestampconversion(ts#x, TimestampType, LongType) - 0) % 300000000)
+ 300000000) ELSE ((precisetimestampconversion(ts#x, TimestampType, LongType) -
0) % 300000000) END) - 0), LongType, TimestampType)), end, knownnul [...]
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 1,
+ "stopIndex" : 96,
+ "fragment" : "SELECT count(*) AS cnt\nFROM ts_data\nGROUP BY ts\nHAVING
window(ts, '5 minutes').start IS NOT NULL"
+ } ]
+}
+
+
+-- !query
+SELECT count(*) FROM ts_data GROUP BY ts ORDER BY window(ts, '5 minutes').start
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "MISSING_ATTRIBUTES.RESOLVED_ATTRIBUTE_MISSING_FROM_INPUT",
+ "sqlState" : "XX000",
+ "messageParameters" : {
+ "input" : "\"count(1)\"",
+ "missingAttributes" : "\"ts\"",
+ "operator" : "!Project [named_struct(start,
knownnullable(precisetimestampconversion(((precisetimestampconversion(ts#x,
TimestampType, LongType) - CASE WHEN (((precisetimestampconversion(ts#x,
TimestampType, LongType) - 0) % 300000000) < cast(0 as bigint)) THEN
(((precisetimestampconversion(ts#x, TimestampType, LongType) - 0) % 300000000)
+ 300000000) ELSE ((precisetimestampconversion(ts#x, TimestampType, LongType) -
0) % 300000000) END) - 0), LongType, TimestampType)), end, knownnul [...]
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 42,
+ "stopIndex" : 79,
+ "fragment" : "ORDER BY window(ts, '5 minutes').start"
+ } ]
+}
+
+
+-- !query
+SELECT ts, row_number() OVER (ORDER BY ts) AS rn FROM ts_data QUALIFY
window(ts, '5 minutes').start IS NOT NULL
+-- !query schema
+struct<window:struct<start:timestamp,end:timestamp>,ts:timestamp,rn:int>
+-- !query output
+{"start":2021-01-01 00:00:00,"end":2021-01-01 00:05:00} 2021-01-01
00:00:00 1
+{"start":2021-01-01 00:00:00,"end":2021-01-01 00:05:00} 2021-01-01
00:01:00 2
+{"start":2021-01-01 00:00:00,"end":2021-01-01 00:05:00} 2021-01-01
00:04:30 3
+{"start":2021-01-01 00:05:00,"end":2021-01-01 00:10:00} 2021-01-01
00:06:00 4
+
+
+-- !query
+SELECT * FROM ts_data
+PIVOT (window(ts, '5 minutes').start FOR a IN ('A1', 'A2'))
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+{
+ "errorClass" : "_LEGACY_ERROR_TEMP_1006",
+ "messageParameters" : {
+ "sql" : "window"
+ }
+}
+
+
+-- !query
+SELECT
+ window(window(ts, '1 minute'), '5 minutes').start,
+ window(window(ts, '1 minute'), '10 minutes').end
+FROM ts_data
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+{
+ "errorClass" : "_LEGACY_ERROR_TEMP_1039",
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 1,
+ "stopIndex" : 123,
+ "fragment" : "SELECT\n window(window(ts, '1 minute'), '5
minutes').start,\n window(window(ts, '1 minute'), '10 minutes').end\nFROM
ts_data"
+ } ]
+}
+
+
+-- !query
+SELECT window.start FROM ts_data GROUP BY window(window(ts, '1 minute'), '5
minutes')
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "MISSING_AGGREGATION",
+ "sqlState" : "42803",
+ "messageParameters" : {
+ "expression" : "\"window\"",
+ "expressionAnyValue" : "\"any_value(window)\""
+ }
+}
+
+
+-- !query
+SELECT
+ a,
+ session_window.start,
+ session_window.end,
+ count(*) AS cnt
+FROM ts_data
+GROUP BY a, session_window(ts, '5 minutes')
+ORDER BY a, start
+-- !query schema
+struct<a:string,start:timestamp,end:timestamp,cnt:bigint>
+-- !query output
+A1 2021-01-01 00:00:00 2021-01-01 00:11:00 3
+A2 2021-01-01 00:01:00 2021-01-01 00:06:00 1
+
+
+-- !query
+SELECT
+ a,
+ session_window.start,
+ session_window.end,
+ count(*) AS cnt
+FROM VALUES
+ ('A1', CAST('2021-01-01 00:00:00' AS TIMESTAMP)),
+ ('A1', CAST('2021-01-01 00:04:30' AS TIMESTAMP)),
+ ('A1', CAST('2021-01-01 00:10:00' AS TIMESTAMP)),
+ ('A2', CAST('2021-01-01 00:01:00' AS TIMESTAMP)),
+ ('A2', CAST('2021-01-01 00:04:30' AS TIMESTAMP))
+AS tab(a, ts)
+GROUP BY a, session_window(ts, CASE WHEN a = 'A1' THEN '5 minutes'
+ WHEN a = 'A2' THEN '1 minute'
+ ELSE '10 minutes' END)
+ORDER BY a, start
+-- !query schema
+struct<a:string,start:timestamp,end:timestamp,cnt:bigint>
+-- !query output
+A1 2021-01-01 00:00:00 2021-01-01 00:09:30 2
+A1 2021-01-01 00:10:00 2021-01-01 00:15:00 1
+A2 2021-01-01 00:01:00 2021-01-01 00:02:00 1
+A2 2021-01-01 00:04:30 2021-01-01 00:05:30 1
+
+
+-- !query
+SELECT
+ a,
+ session_window.start,
+ session_window.end,
+ count(*) AS cnt
+FROM VALUES
+ ('A1', CAST('2021-01-01 00:00:00' AS TIMESTAMP), '5 minutes'),
+ ('A1', CAST('2021-01-01 00:04:30' AS TIMESTAMP), '5 minutes'),
+ ('A2', CAST('2021-01-01 00:01:00' AS TIMESTAMP), '1 minute')
+AS tab(a, ts, gap)
+GROUP BY a, session_window(ts, gap)
+ORDER BY a, start
+-- !query schema
+struct<a:string,start:timestamp,end:timestamp,cnt:bigint>
+-- !query output
+A1 2021-01-01 00:00:00 2021-01-01 00:09:30 2
+A2 2021-01-01 00:01:00 2021-01-01 00:02:00 1
+
+
+-- !query
+SELECT
+ session_window.start,
+ session_window.end,
+ count(*) AS cnt
+FROM VALUES
+ (CAST('2021-01-01 00:00:00' AS TIMESTAMP_NTZ)),
+ (CAST('2021-01-01 00:04:30' AS TIMESTAMP_NTZ)),
+ (CAST('2021-01-01 00:10:00' AS TIMESTAMP_NTZ))
+AS tab(ts)
+GROUP BY session_window(ts, '5 minutes')
+ORDER BY start
+-- !query schema
+struct<start:timestamp_ntz,end:timestamp_ntz,cnt:bigint>
+-- !query output
+2021-01-01 00:00:00 2021-01-01 00:09:30 2
+2021-01-01 00:10:00 2021-01-01 00:15:00 1
+
+
+-- !query
+SELECT
+ session_window.start,
+ session_window.end,
+ count(*) AS cnt
+FROM VALUES
+ (CAST(NULL AS TIMESTAMP)),
+ (CAST('2021-01-01 00:00:00' AS TIMESTAMP)),
+ (CAST('2021-01-01 00:01:00' AS TIMESTAMP))
+AS tab(ts)
+GROUP BY session_window(ts, '5 minutes')
+ORDER BY start
+-- !query schema
+struct<start:timestamp,end:timestamp,cnt:bigint>
+-- !query output
+2021-01-01 00:00:00 2021-01-01 00:06:00 2
+
+
+-- !query
+SELECT
+ session_window.start,
+ session_window.end,
+ count(*) AS cnt
+FROM VALUES
+ (CAST('2021-01-01 00:00:00.500' AS TIMESTAMP)),
+ (CAST('2021-01-01 00:00:00.700' AS TIMESTAMP)),
+ (CAST('2021-01-01 00:00:01.200' AS TIMESTAMP))
+AS tab(ts)
+GROUP BY session_window(ts, '500 milliseconds')
+ORDER BY start
+-- !query schema
+struct<start:timestamp,end:timestamp,cnt:bigint>
+-- !query output
+2021-01-01 00:00:00.5 2021-01-01 00:00:01.7 3
+
+
+-- !query
+SELECT count(*) FROM ts_data GROUP BY session_window(ts, '-5 minutes')
+-- !query schema
+struct<count(1):bigint>
+-- !query output
+
+
+
+-- !query
+SELECT count(*) FROM ts_data GROUP BY session_window(ts, '0 seconds')
+-- !query schema
+struct<count(1):bigint>
+-- !query output
+
+
+
+-- !query
+SELECT count(*) FROM ts_data GROUP BY session_window(ts)
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+{
+ "errorClass" : "WRONG_NUM_ARGS.WITHOUT_SUGGESTION",
+ "sqlState" : "42605",
+ "messageParameters" : {
+ "actualNum" : "1",
+ "docroot" : "https://spark.apache.org/docs/latest";,
+ "expectedNum" : "2",
+ "functionName" : "`session_window`"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 39,
+ "stopIndex" : 56,
+ "fragment" : "session_window(ts)"
+ } ]
+}
+
+
+-- !query
+SELECT count(*) FROM ts_data
+GROUP BY session_window(ts, '5 minutes'), session_window(ts, '10 minutes')
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+{
+ "errorClass" : "_LEGACY_ERROR_TEMP_1039",
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 30,
+ "stopIndex" : 103,
+ "fragment" : "GROUP BY session_window(ts, '5 minutes'), session_window(ts,
'10 minutes')"
+ } ]
+}
+
+
+-- !query
+SELECT window_time(session_window) AS event_time, count(*) AS cnt
+FROM ts_data
+GROUP BY session_window(ts, '5 minutes')
+ORDER BY event_time
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "MISSING_AGGREGATION",
+ "sqlState" : "42803",
+ "messageParameters" : {
+ "expression" : "\"window_time(session_window)\"",
+ "expressionAnyValue" : "\"any_value(window_time(session_window))\""
+ }
+}
+
+
+-- !query
+SELECT count(*)
+FROM ts_data
+WHERE session_window(ts, '5 minutes').start IS NOT NULL
+-- !query schema
+struct<count(1):bigint>
+-- !query output
+4
+
+
+-- !query
+SELECT ts, row_number() OVER (ORDER BY ts) AS rn
+FROM ts_data
+QUALIFY session_window(ts, '5 minutes').start IS NOT NULL
+-- !query schema
+struct<session_window:struct<start:timestamp,end:timestamp>,ts:timestamp,rn:int>
+-- !query output
+{"start":2021-01-01 00:00:00,"end":2021-01-01 00:05:00} 2021-01-01
00:00:00 1
+{"start":2021-01-01 00:01:00,"end":2021-01-01 00:06:00} 2021-01-01
00:01:00 2
+{"start":2021-01-01 00:04:30,"end":2021-01-01 00:09:30} 2021-01-01
00:04:30 3
+{"start":2021-01-01 00:06:00,"end":2021-01-01 00:11:00} 2021-01-01
00:06:00 4
+
+
+-- !query
+SELECT * FROM ts_data GROUP BY a, session_window(ts, '5 minutes')
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "MISSING_AGGREGATION",
+ "sqlState" : "42803",
+ "messageParameters" : {
+ "expression" : "\"ts\"",
+ "expressionAnyValue" : "\"any_value(ts)\""
+ }
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
